Комментарии 81
Спасибо за столь подробную статью! Не ожидал, но нашел и для себя новую информацию)
Около 70 часов ее писали, даже 1/5 накопленного опыта не успели изложить, но аудитория как-то вяло реагирует, поэтому видимо ошиблись темой.
Просто люди на работе. А Ваш материал требует основательного изучения, поэтому реакция сообщества будет, будет она, когда население сообщества закончит работу, уйдёт на перерыв или когда появится достаточно времени на подробное изучение статьи. Много кто добавил статью в закладки (я тоже) потому что эта статья (как мне кажется) очень подходит и в качестве справочного материала который будет востребован по мере необходимости.
Не ошиблись. По Google SpreadSheet очень мало такого рода информации (с одной стороны, учебной, а с другой — не только для начинающих). Ждем продолжения.
Я думаю, что о том, полетела ли статья, можно будет судить по количеству людей, которые добавят её в избранное.
Очень хорошая статья и тема актуальная, но не всем она нужна прямо сейчас. Кроме того, после индексации поисковиками она наверняка привлечет очень неплохой трафик и будет полезна многим. Так что время потрачено не зря.
НЛО прилетело и опубликовало эту надпись здесь
Никогда не мог нормально понять и оперировать с A1. При каждой возможности переключаю Excel в режим RC.
Хм. Никто из моих знакомых с RC не работает — только A1. И сам не понимаю RC. Неудобно же читать.
Я умер на таком действии:
формула=предыдущее+1.
Когда растягиваешь формулу по ячейкам, получается либо
=RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1
либо
=A1+1 =B1+1 =C1+1 =D1+1 =E1+1 =F1+1
То есть одна и та же формула выглядит по разному в разных ячейках.
Это основная причина моей ненависти к экселю до тех пор пока я не обнаружил режим R1C1
формула=предыдущее+1.
Когда растягиваешь формулу по ячейкам, получается либо
=RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1
либо
=A1+1 =B1+1 =C1+1 =D1+1 =E1+1 =F1+1
То есть одна и та же формула выглядит по разному в разных ячейках.
Это основная причина моей ненависти к экселю до тех пор пока я не обнаружил режим R1C1
спасибо, узнал кое-что новое, чего раньше делал более сложными методами.
Скажите пожалуйста, а есть ли в Гуглотаблицах возможность делать подстрочные и надстрочные символы? Раньше точно не было, за исключением цифорвых значений (0-9), да и то, их приходилось копировать с какой-нибудь html-страницы. Я вот не понимаю логику Google — неужели так сложно добавить поддержку под(над-)сточных символов? Тем более в таблицах! Чтобы писать в заголовке столбца нормальное nλ=840нм а не n(λ=840нм) или вообще nλ=840нм.
Отсутствие этой функции, наравне с отстутствием возможности сделать отступ у картинки в текстовом редакторе Google (опять-таки, наверное это капец как сложно сделать...), пока отталкивает меня от повсеместного использования их продуктов. Так, время от времени…
Это был маленький крик души, к качеству статьи вообще не относящийся! Статья вообще супер, если бы у вас была возможность дописать остальные 4/5 накопленного опыта, то это стал бы самый большой талмуд в рунете! И очень полезный.
Спасибо за ваш труд.
Отсутствие этой функции, наравне с отстутствием возможности сделать отступ у картинки в текстовом редакторе Google (опять-таки, наверное это капец как сложно сделать...), пока отталкивает меня от повсеместного использования их продуктов. Так, время от времени…
Это был маленький крик души, к качеству статьи вообще не относящийся! Статья вообще супер, если бы у вас была возможность дописать остальные 4/5 накопленного опыта, то это стал бы самый большой талмуд в рунете! И очень полезный.
Спасибо за ваш труд.
ид формулы: "=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)"
Кажется должно быть наоборот, 1 — если оба условия верны.
в эстетическом плане да Вы правы, должно быть наоборот, но вообще может быть написано что угодно.
Да не что угодно, значения возвращаются конкретные. Там синтаксис такой:
Если значения опущены, возвращается 1 в случае, если условие выполняется, и 0 — если нет.
условие записывается так:
Формулу вообще можно без IF написать:
вернёт либо G33, либо 0 (если не выполнится одно из условий)
IF(условие; значение_если_верно; значение_если_не_верно)Если значения опущены, возвращается 1 в случае, если условие выполняется, и 0 — если нет.
условие записывается так:
AND(условие1; условие2; условиеN)OR(условие1; условие2; условиеN)Формулу вообще можно без IF написать:
=AND(C33>0;C34>0;C35>0)*G33вернёт либо G33, либо 0 (если не выполнится одно из условий)
Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
В spreadsheet тоже: support.google.com/drive/bin/answer.py?hl=ru&answer=155183
да согласен, в spreadsheet тоже можно переносить, но Вы забываете про ограничения Количество формул ImportRange: 50 формул перекрестных ссылок в книгах.
И при любом документе с большим количеством информации, функция импортирования теряет свое первоначальное восхищение. Перенос больших документов производится с помощью скриптов.
Формулы, предполагающие импорт данных (например, IMPORTRANGE), периодически пересчитываются и повышают уровень сложности, а значит большее время для пересчетов.
И при любом документе с большим количеством информации, функция импортирования теряет свое первоначальное восхищение. Перенос больших документов производится с помощью скриптов.
Формулы, предполагающие импорт данных (например, IMPORTRANGE), периодически пересчитываются и повышают уровень сложности, а значит большее время для пересчетов.
Спасибо. Просто отличная статья. Жду когда реализуется что-то типа ВПР (VLOOKUP по-моему в английской версии) и тогда полностью перейду на решение от Google. Пока же приходится частенько возвращаться к дорогому и избыточному Excel
А можно вопрос задать?
Надо из диапазона ячеек выбрать самую первую не пустую снизу. Вот например:
Надо вернуть 123. Если удалить 123, должно возвращаться 222, и т.д.
Как реализовать?
Надо из диапазона ячеек выбрать самую первую не пустую снизу. Вот например:
Надо вернуть 123. Если удалить 123, должно возвращаться 222, и т.д.
Как реализовать?
Я вижу много путей, но скажу самый простой (с одной формулой). Пусть все данные у нас в столбце А
1. В B1 ставим формулу
2. Растягиваем формулу на весь столбец B (кроме последней ячейки)
3. Ответ в B1
1. В B1 ставим формулу
=IF(B2<>"";B2;IF(A1<>"";A1;""))2. Растягиваем формулу на весь столбец B (кроме последней ячейки)
3. Ответ в B1
=INDEX(Лист1!A:A;MAX(IF(NOT(ISBLANK (Лист1!A:A));ROW(Лист1!A:A)))) где Лист1! А: А это лист с нужной колонкой.
Крут. Всегда знал, что настоящие ниндзя excel существуют и находятся среди нас.
К своей радости из твоей формулы узнал 3 (!) новые функции.
К своей радости из твоей формулы узнал 3 (!) новые функции.
Ага, спасибо за пример. Попробую сделать по аналогии.
Я немного неточно вопрос задал: мне нужно в данном случае из 123 вычесть 222 (пропустив пустые строки), положив результат справа от 123. Головоломка, однако :)
Я немного неточно вопрос задал: мне нужно в данном случае из 123 вычесть 222 (пропустив пустые строки), положив результат справа от 123. Головоломка, однако :)
Если у Вас пустые ячейки идут с определенной последовательностью, например, через одну, то Вы можете написать так =INDEX(Лист1!A:A;MAX(IF(NOT(ISBLANK (Лист1!A:A));ROW(Лист1!A:A)))-2), самая главная часть это -2 в конце, поэксперементируйте).
Для возрастающего списка =LARGE(A1:A18;1)-LARGE(A1:A22;2)
Универсальная формула =ARRAYFORMULA((INDEX(A1:A10;LARGE((A1:A10>0)*ROW(A1:A10);1)))-(INDEX(A1:A10;LARGE((A1:A10>0)*ROW(A1:A10);2)))) где A1:A10 нужный диапазон поиска.
Да, всё получилось, спасибо. Обрамил несколькими условиями и сделал ячейки со сдвигом, кроме первой ячейки диапазона:
А не подскажете, как можно поставить защиту на ячейки от самого себя? Чтобы случайно не повредить формулу? А то там на диапазон ставится только доступ для других пользователей…
=IF(C29<>"";IFERROR(LARGE(FILTER(C24:C29;C24:C29<>"");1)-LARGE(FILTER(C24:C29;C24:C29<>"");2);"");"")=IF(C30<>"";IFERROR(LARGE(FILTER(C24:C30;C24:C30<>"");1)-LARGE(FILTER(C24:C30;C24:C30<>"");2);"");"")=IF(C31<>"";IFERROR(LARGE(FILTER(C24:C31;C24:C31<>"");1)-LARGE(FILTER(C24:C31;C24:C31<>"");2);"");"")А не подскажете, как можно поставить защиту на ячейки от самого себя? Чтобы случайно не повредить формулу? А то там на диапазон ставится только доступ для других пользователей…
пока знаю только извратный способ — сделать владельцем документа другой свой гугло-акк и защитить диапазон от остальных. Найдете способ лучше — маякните плиз :)
Так и не нашел.
Согласитесь, кот на клавиатуре бед может больше наделать, чем другой пользователь :)
Согласитесь, кот на клавиатуре бед может больше наделать, чем другой пользователь :)
соглашусь. В моем случае правда вместо кота выстапает годовалая дочь :)
кстати, в гуглодокументах есть фича «файл — посмотреть историю изменений» — что позволяет востановить документ на любую не очень далекую дату. Правда не так удобна она конечно. Как прикрутить свой бекап по нормальному я тоже не нашел :(
сами бьемся над проблемой бекапа, пока глухо.
Я когда ухожу от компа всегда сворачиваю все документы. Чтоб дете часом не залезло.
Я когда ухожу от компа всегда сворачиваю все документы. Чтоб дете часом не залезло.
Да, про историю знаю. Просто найти там что-то бывает проблематично, особенно когда проблема обнаруживается не сразу. Например, работаешь спокойно с документом, а в какой-то ячейке нет результата. Смотришь — формула попорчена. В историю — там работа, работа, работа… а когда удалил формулу непонятно. Может неделю назад, может месяц. По времени её проще заново написать.
В нативных документах всегда всё блокировал, кроме того, куда можно вводить данные.
В нативных документах всегда всё блокировал, кроме того, куда можно вводить данные.
Спасибо за
Из недавнего опыта поделюсь функцией
Спасибо за интересный материал!
sumif с множеством условий. Я вот обычно новые столбцы завожу, где ставлю большие if-ы, но чувствую что это – не комильфо. Из недавнего опыта поделюсь функцией
INDIRECT. Если нужно сослаться на другой лист, а название этого листа содержится в ячейке (например А1), то можно написать что-то вроде =INDIRECT("'"&A1&"'!G2"). Это даст значение ячейки G2 на листе с именем, которое указано в A1.Спасибо за интересный материал!
Очень круто, спасибо!
Как раз думал засесть почитать про табличные редакторы — и тут статья в руку
Как раз думал засесть почитать про табличные редакторы — и тут статья в руку
Мне пришлось применять формулы подобного уровня для решения задачи по ведению домашней бухгалтерии в spreadsheets:
=SUM(IFERROR(FILTER(INDIRECT(B$11&"!$G2:$G500"),INDIRECT(B$11&"!$E2:$E500")=$A13,
INDIRECT(B$11&"!$C2:$C500")=$T$5)),"")
Для «суммирования ячеек с условием ЕСЛИ» (то, что на рис. 21) — в данном конкретном случае мне кажется удобнее задействовать query.
=QUERY(A1:B8;«SELECT A, SUM(B) GROUP BY A»)
=QUERY(A1:B8;«SELECT A, SUM(B) GROUP BY A»)
А есть ли в GDocs функция склейки массивов?
Хотелось бы всунуть в функцию QUERY
Хотелось бы всунуть в функцию QUERY
Что Вы имеете ввиду под склейкой? И почему использовать именно query?
Пример забыли указать, вот и возникают вопросы.
Пример забыли указать, вот и возникают вопросы.
Вот пример таблицы
docs.google.com/spreadsheet/ccc?key=0Ajn0Q1IsENSedGhaUnhEWGxiaGhBZzFqZHEzNHFEYnc&usp=sharing
Есть Исходные данные (A1:G12), и есть развернутые данные (A19:G28), по которым можно строить отчеты.
Множество [A19:G28] получается из множества [A1:G12], как объединение [A, B, C, E, F] и [A, B, D, -1*E, F]
Вопрос: можно ли получить получить напрямую нужное мне множество?
P.S. С т.з. SQL мне нужен оператор «UNION»
docs.google.com/spreadsheet/ccc?key=0Ajn0Q1IsENSedGhaUnhEWGxiaGhBZzFqZHEzNHFEYnc&usp=sharing
Есть Исходные данные (A1:G12), и есть развернутые данные (A19:G28), по которым можно строить отчеты.
Множество [A19:G28] получается из множества [A1:G12], как объединение [A, B, C, E, F] и [A, B, D, -1*E, F]
Вопрос: можно ли получить получить напрямую нужное мне множество?
P.S. С т.з. SQL мне нужен оператор «UNION»
Благодаря любимому форуму planetaexcel, вот ответ на Ваш вопрос, для excel правда.
Sub t()
Dim sCon$, rs As Object, cn As Object
Dim sSQL$
Set rs = CreateObject(«ADODB.Recordset»)
Set cn = CreateObject(«ADODB.Connection»)
Select Case CLng(Split(Application.Version, ".")(0))
Case Is < 12
sCon = «Provider=Microsoft.Jet.OLEDB.4.0;Data Source=» & ThisWorkbook.FullName _
& ";Extended Properties="«Excel 8.0;HDR=Yes;IMEX=1»";"
Case Is >= 12
sCon = «Provider=Microsoft.ACE.OLEDB.12.0;Data Source=» & ThisWorkbook.FullName _
& ";Extended Properties="«Excel 12.0;HDR=Yes;IMEX=1»";"
End Select ' thnx R Dmitry
sSQL = «SELECT [N], [Дата], [Откуда], -1, [Вид расхода], [Сумма]» _
& " FROM [Лист1$A1:F8] WHERE [Откуда]>""""" _
& " UNION" _
& " SELECT [N], [Дата], [Куда], 1, [Вид расхода], [Сумма]" _
& " FROM [Лист1$A1:F8] WHERE [Куда]>""""" _
& " ORDER BY [N]"
cn.Open sCon
Set rs = cn.Execute(sSQL)
[a30].CopyFromRecordset rs
End Sub
Sub t()
Dim sCon$, rs As Object, cn As Object
Dim sSQL$
Set rs = CreateObject(«ADODB.Recordset»)
Set cn = CreateObject(«ADODB.Connection»)
Select Case CLng(Split(Application.Version, ".")(0))
Case Is < 12
sCon = «Provider=Microsoft.Jet.OLEDB.4.0;Data Source=» & ThisWorkbook.FullName _
& ";Extended Properties="«Excel 8.0;HDR=Yes;IMEX=1»";"
Case Is >= 12
sCon = «Provider=Microsoft.ACE.OLEDB.12.0;Data Source=» & ThisWorkbook.FullName _
& ";Extended Properties="«Excel 12.0;HDR=Yes;IMEX=1»";"
End Select ' thnx R Dmitry
sSQL = «SELECT [N], [Дата], [Откуда], -1, [Вид расхода], [Сумма]» _
& " FROM [Лист1$A1:F8] WHERE [Откуда]>""""" _
& " UNION" _
& " SELECT [N], [Дата], [Куда], 1, [Вид расхода], [Сумма]" _
& " FROM [Лист1$A1:F8] WHERE [Куда]>""""" _
& " ORDER BY [N]"
cn.Open sCon
Set rs = cn.Execute(sSQL)
[a30].CopyFromRecordset rs
End Sub
А можно в одной ячейке объединить строку и какую-то динамическую формулу (т.е. не просто указать ссылку на ячейку с результатом вычисления, а написать формулу прямо в ячейке вместе с текстом)?
Псевдотекст: [Это сумма SUM(B3:B7) / а тут еще какой-то текст и формула]
Псевдотекст: [Это сумма SUM(B3:B7) / а тут еще какой-то текст и формула]
Есть ли возможность сравнить два массива А и В, и получить те данные из массива А, которые отсутствуют в массиве В?
НЛО прилетело и опубликовало эту надпись здесь
Здравствуйте!
Да конечно можно.
В екселе это делается так:
=ДВССЫЛ(СЦЕПИТЬ(ПОДСТАВИТЬ(АДРЕС(1; ЧИСЛСТОЛБ(A1:E1);4);1;"");1)) — выведет данные из ячейки Е1
Ну а дальше уже делайте с ней что необходимо.
Для GD вариант будет такой:
INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1)) — если в GD например 8 столбцов, то мы получим значение ячейки H1
Но как выяснилось использовать данную формулу можно только как самостоятельную часть, т.е.
например формула =SUM(A1;INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложит А1+Н1
но не сработает в случае =SUM(A1:INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложения диапазона А1: Н1
Для того чтоб вернуть ее к жизни нужно нашу большую формулу поместить в массив, т.е. заключить ее в круглые фигурные скобки {}
=SUM(A1:{INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))})
Удачи!
Да конечно можно.
В екселе это делается так:
=ДВССЫЛ(СЦЕПИТЬ(ПОДСТАВИТЬ(АДРЕС(1; ЧИСЛСТОЛБ(A1:E1);4);1;"");1)) — выведет данные из ячейки Е1
Ну а дальше уже делайте с ней что необходимо.
Для GD вариант будет такой:
INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1)) — если в GD например 8 столбцов, то мы получим значение ячейки H1
Но как выяснилось использовать данную формулу можно только как самостоятельную часть, т.е.
например формула =SUM(A1;INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложит А1+Н1
но не сработает в случае =SUM(A1:INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложения диапазона А1: Н1
Для того чтоб вернуть ее к жизни нужно нашу большую формулу поместить в массив, т.е. заключить ее в круглые фигурные скобки {}
=SUM(A1:{INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))})
Удачи!
А расширенный список отличий от Excel будет? Просто за все время работы с Sheets столкнулся только с отсутствием некоторого функционала.
А что именно Вас интересует? Особо если честно не заморачивались, функционал в таблицах понемногу растет, хотя довольно многого не хватает. Но впринципе решить можно все.
Если сможете, подскажите, пожалуйста, ответ.
Есть набор данных в формате: 2 буквы + 6 чисел. Мне нужно вывести максимальное число из этого набора. Меня, вроде бы, устраивает обычный =SUBSTITUTE() и =MAX(). Но вот подружить это при помощи =ARRAYFORMULA() с наскока не удалось. Задача не так важна, чтобы тратить на нее больше получаса, но узнать решение интересно.
Есть набор данных в формате: 2 буквы + 6 чисел. Мне нужно вывести максимальное число из этого набора. Меня, вроде бы, устраивает обычный =SUBSTITUTE() и =MAX(). Но вот подружить это при помощи =ARRAYFORMULA() с наскока не удалось. Задача не так важна, чтобы тратить на нее больше получаса, но узнать решение интересно.
Коллеги, нужна помощь, подскажите, пожалуйста:
Пользуюсь функцией filter() — и не нашел, как в условии отбора добавить подзапрос.
Если предметно:
Выбери из списка 1 все значения, которых нет в списке 2.
Если подскажете, как это сделать с помощью filter, буду очень благодарен. Если другими способами (вычитанием массивов, query, ...) тоже буду благодарен, но очень хочется через filter() это сделать :-)
Пользуюсь функцией filter() — и не нашел, как в условии отбора добавить подзапрос.
Если предметно:
Выбери из списка 1 все значения, которых нет в списке 2.
Если подскажете, как это сделать с помощью filter, буду очень благодарен. Если другими способами (вычитанием массивов, query, ...) тоже буду благодарен, но очень хочется через filter() это сделать :-)
Кто-нибудь знает как в гугл таблицах реализован подбор параметра?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Талмуд по формулам в Google SpreadSheet