Талмуд по формулам в Google SpreadSheet
Обычно мы пишем про хостинги, в частности про зарубежный shared хостинг в США. Но чтобы писать, нужно иметь аналитические данные под рукой. Вот как раз тут требуется помощь Google Docs, если файл получится предположительно меньше 400 000 строк.
За несколько месяцев работы с таблицами Google пришлось много раз анализировать посредством формул разного рода данные. Как и ожидалось — то, что можно было решить в MS Excel, можно реализовать и в Google таблицах. Но многочисленные попытки решить проблемы с помощью любимого поисковика приводили только к новым вопросам и почти к нулевым ответам. Посему, было решено облегчить жизни другим и прославить себя.
Кратко о главном
- буквенно — цифровое (БУКВА = СТОЛБЕЦ; ЦИФРА = СТРОКА) например «А1».
- стилем R1C1, в системе R1C1 и строки и столбцы обозначаются цифрами.
Рисунок 2 Как видно из Рисунка 3, значения ячеек идут относительно той ячейки, в которой будет написана формула со знаком равно. Для сохранения эстетичного вида формул, в них прописаны символы [0], которые можно и не писать: R[0]C[1] = RC[1].
Рисунок 3 Отличие Рисунка 2 от Рисунка 3 в том, что Рисунок 3 — это универсальная формулировка, не привязанная к строкам и столбцам (смотрите на значения строк и столбцов), чего не скажешь о рисунке 2. Но стиль RC в spreadsheet, в основном, используется для написания скриптов javascript.
Типы ссылок (типы адресации)
- Относительные ссылки (пример, A1);
- Абсолютные ссылки (пример, $A$1);
- Смешанные ссылки (пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Относительная ссылка «запоминает», на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили " https://habrastorage.org/r/w1560/getpro/habr/post_images/419/55a/02d/41955a02d1e46ecf4854c5cd1711bb29.png" alt="ok" data-src="https://habrastorage.org/getpro/habr/post_images/419/55a/02d/41955a02d1e46ecf4854c5cd1711bb29.png"/> Рисунок 4 Упростим пример, применив знак $ (Рисунок 5).
Рисунок 5 Но не всегда нужно закреплять все столбцы и строки, иногда используется закрепление только строки или только столбца.(Рисунок 6)
Рисунок 6 Обо всех формулах можно почитать на официальном сайте support.google.com Важно: Данные, которые необходимо обрабатывать в формулах, не должны находиться в разных документах, это возможно делать только при помощи скриптов.
Ошибки формул
Если вы неправильно напишете формулу, об этом вас известит комментарий о синтаксической ошибке в формуле (Рисунок 7).
Рисунок 7 Хотя ошибки могут быть не только синтаксические, но и, например, математические, такие как деление на 0 (Рисунок 7) и другие (Рисунок 7.1, 7.2, 7.3). Для того чтобы увидеть примечание, в котором показана какая ошибка произошла, наведите курсор на красный треугольник в правом верхнем углу ошибки.
Рисунок 7.1
Рисунок 7.2
Рисунок 7.3 Для удобства восприятия таблицы все ячейки с формулами будем окрашивать в фиолетовый цвет. Для того чтобы увидеть формулы «в живую» необходимо нажать горячую клавишу Ctrl + или выбрать в меню сверху Вид (Просмотр) > Все формулы. (Рисунок 8).
О том, как пишутся формулы
В формулировке формул в справочнике и в формулах, которые используются для работы на данный момент, присутствуют отличия. Они заключаются в том, что вместо «запятой», которая использовалась раньше во многих формулах, уже используется «точка с запятой» (изменения произошли более полугода назад). Для того чтобы посмотреть, на что ссылается формула на данной странице (Рисунок 9), необходимо щелкнуть мышкой в строке формул справа от надписи Fx (Fx находится под основным меню, слева).
Рисунок 9 ВАЖНО: Для правильного функционирования формул, они должны быть написаны ЛАТИНСКИМИ буквами. Русская (кириллическая) “А” или “С” и латинская “А” или “С” для формулы — это 2 разные буквы.
Формулы
Арифметические формулы. Сложение, вычитание, умножение, деление.- Описание: формулы сложения, вычитания, умножения и деления.
- Вид формулы: “Ячейка_1+Ячейка_2”, “Ячейка_1-Ячейка_2”, “Ячейка_1*Ячейка_2”, “Ячейка_1/Ячейка_2”
- Сама формула: =E22+F22, =E23-F23, =E24*F24, =E25/F25.
- Описание: формула для увеличения всех последующих ячеек на единицу (нумерация строк и столбцов).
- Вид формулы: =Предыдущая ячейка + 1.
- Сама формула: =D26+1
- Описание: формула для округления числа в ячейке.
- Вид формулы: =ROUND(ячейка с числом); счетчик (сколько цифр надо округлить после запятой).
- Сама формула: =ROUND(E28;2).
Рисунок 12 Округление “ROUND” происходит по математическим законам, если после запятой стоит цифра 5 или больше, то целая часть увеличивается на единицу, если 4 и меньше, то остается неизменной, также округление можно сделать с помощью меню ФОРМАТ — > Числа -> «1000,12» 2 десятичных знака (Рисунок 13). Если же вам необходимо большее количество знаков, то нужно нажать ФОРМАТ — > Числа -> Персонализированные десятичные -> И указать количество знаков.
Сумма, если ячейки идут не последовательно.- Описание: суммирование чисел, которые находятся в разных ячейках.
- Вид формулы: =SUM(число_1; число_2;… число_30).
- Сама формула: "=SUM(E30;H30)" пишем через ";" если разные ячейки.
- Описание: суммирование чисел, которые идут друг за другом (последовательно).
- Вид формулы: =SUM(число_1: число_N).
- Сама формула: =SUM (E31:H31)" пишем через ":" если это непрерывный диапазон.
- Имеем начальные данные в диапазоне ячеек E31:H31, а результат в ячейке D31 (Рисунок15).
Рисунок 15
Среднее арифметическое.- Описание: суммируется диапазон чисел и делится на количество ячеек в диапазоне.
- Вид формулы: =AVERAGE (ячейка с числом либо число_1; ячейка с числом либо число_2;… ячейка с числом либо число_30).
- Сама формула: =AVERAGE(E32:H32)
Рисунок 16 Конечно, есть и другие, но мы идем дальше.
Текстовые формулы.
Склеивание текстовых значений (формулой).- Описание: «склеивание» текстовых значений (вариант А).
- Вид формулы: =CONCATENATE(ячейка с числом/текстом либо текст_1; ячейка с числом/текстом либо текст_2; …, ячейка с числом/текстом либо текст_30).
- Сама формула: =CONCATENATE(E36;F36;G36;H36).
- Описание: “склеивание” текстовых значений руками, без использования специальных функций (вариант B — ручное написание формулы, сложность формулы любая.).
- Вид формулы: =ячейка с числом/текстом 1&" "&ячейка с числом/текстом 2&" "&ячейка с числом/текстом 3&" "& ячейка с числом/текстом 4 (" " — пробел, знак & означает склеивание, все текстовые значения пишутся в кавычках “”).
- Сама формула: =E37&" "&F37&" "&G37&" "&H37.
- Описание:«склеивание» текстовых значений руками, без использования специальных функций (вариант С — смешанный тип, сложность формулы любая).
- Вид формулы: = «текст_1 » &ячейка_1&«текст_2»&ячейка_2&«текст_3»&ячейка_3
- Важно: весь текст, который будет написан в “” будет неизменным для формулы.
- Сама формула: =«Еще 1 » &E38&" использования "&F38&" как НАМ "&G38.
ЛОГИЧЕСКИЕ И ПРОЧИЕ
Перенос данных из любых листов одного и того же файла.- Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
- Вид формулы: = «Название_Листа»! ячейка_1
- Сама формула:=Data!A15 (Data — лист, А15 — ячейка на том листе).
Рисунок 20
Рисунок 20.1
Массив формул. Суммирование ячеек с условием ЕСЛИ.- Описание: суммирование ячеек с условием ЕСЛИ (формула SUMIF).
- Вид формулы: = SUMIF(‘Лист’! диапазон; критерии; ‘Лист’! суммарный_диапазон)
Рисунок 21 Имеем начальные данные в листе Data (Рисунок 21), а результат на листе Formula в столбце D (Рисунок 22). В столбцах E, F, G показаны аргументы, применяемые в формуле, а в столбце H общий вид формулы, которая находится в столбце D и высчитывает результат.
Рисунок 22 Пример выше показывает общий вид работы формулы “Сумма Если” с одним условием, но чаще всего используется “Сумма ЕСЛИ” (с множеством условий).
Суммирование ячеек ЕСЛИ, множество условий.- Описание: сумма ЕСЛИ (с множеством условий).
- Вид формулы: = SUMIF(‘Data’! диапазон_1&‘Data’! диапазон_2; критерии_1&критерий_2; ‘Data’! суммарный_диапазон).
- Сама формула:=(ARRAYFORMULA(SUMIF((Data!E:E&Data!F:F);(B53&C53);Data!G:G)))
Рисунок 23 Допустим, что на листе Formula, в ячейке В53 (критерий_1 = Пиво) должно быть название напитка, а ячейка С53 (критерий_2 = 2), это количество друзей, которые принесут Пиво. В итоге в ячейке D53 окажется результат, что нам нужно докупить 15 бутылок пива. (Рисунок 23.1) то есть, формула определит сумму по двум критериям — пиво и количество друзей.
Рисунок 23.1 Если таких позиций будет больше, строки 16 и 21(Рисунок 24), то количество пузырей в колонке G суммируется (Рисунок 24.1).
Рисунок 24 Итого:
Теперь приведем более интересный пример:Ха… вечеринка продолжается, и вы вспоминаете, что нужен торт, но непростой, а супер – мега торт, с разными специями, которые, как назло, еще и зашифрованы под цифровые обозначения. Задача состоит в том, чтобы купить специи в нужном количестве пакетиков каждой из специи. Нужное количество повар зашифровал в таблицу (Рисунок. 25.1), столбцы A и B (в соседних столбцах делаем наши вычисления). Каждая специя имеет свой порядковый номер: 1,2,3,4. (Рисунок 25).
- Описание: подсчет количества одинаковых цифр в больших массивах при дополнительных условиях.
- Вид формулы: СЧИТАТЬ ЕСЛИ(‘Formula’! диапазон_A55: А61+’Formula’! диапазон_B55:B61; УсловиеА”Специи”+УсловиеБ”число от 1 до 4”; Лист”Formula’! диапазон_B55:B61)/УсловиеБ ”число от 1 до 4”)
- Сама формула: =((ARRAYFORMULA(SUMIF('Formula'!$A$55:$A$61&'Formula'!$B$55:$B$61; $F$55&$E59;'Formula'!$B$55:$B$61)))/$E59)
- Описание: вычисление процента специй.
- Вид формулы: Количество*100%/Общее_количество
- Сама формула: =F58*$G$56/F$56
Рисунок 25.1 В конечном итоге мы имеем сумму повторов и процент. Для правильного написания формулы, вы должны полностью представлять, что вы ИМЕЕТЕ, что ХОТИТЕ ПОЛУЧИТЬ и в каком виде. Возможно, для этого вам предстоит изменить вид начальных данных. Переходим к следующему примеру
Подсчет значений в объединенных ячеек.- Описание: формула для подсчета значений, в которых присутствует символ @.
- Вид формулы: СЧИТАТЬ ЕСЛИ(В столбце F листа “Formula” есть текст с содержимым @).
- Сама формула: =COUNTIF('Formula'!F65:F68; "*@*").
- Описание: подсчет количества ячеек, содержащих цифры без текстовых переменных.
- Вид формулы: COUNT(значение_1; значение_2; … значение_30)
- Сама формула: =COUNT(E45;F45;G45;H45)
Рисунок 27. Ячейки, содержащие текст и цифры также не считаются.
Подсчет количества ячеек содержащих цифры с текстовыми переменными.- Описание: подсчет количества ячеек, содержащих цифры с текстовыми переменными.
- Вид формулы: COUNTA(значение_1; значение_2; … значение_30)
- Сама формула: =COUNTA(E46:H46)
Рисунок 28. Также, формула считает ячейки, содержащие только знаки препинания, табуляции, но не считает пустые ячейки.
Подстановка значений при условиях.- Описание: подстановка значений при условиях.
- Вид формулы: "=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)"
- Сама формула: "=IF(AND((F73=5);(H73=5));0;1)"
Рисунок 29.
- Вид формулы:"=COUNTA(Диапазон_А)-COUNTIF(Диапазон_А; «автоответ»)-COUNTIF(Диапазон_А; "-")-COUNTIF(Диапазон_А; «занято»)"
- Сама формула: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; «автоответ»)-COUNTIF($E74:$H75; "-")-COUNTIF($E74:$H75; «занято»)
Рисунок 30 Вот мы и подошли к концу нашего маленького ликбеза по формулам в Google SpreadSheet и у меня большие надежды, что я пролил свет на некоторые аспекты аналитической работы с формулами. Формулы, честно говоря, были в прямом смысле выстраданы. Каждая из них создавалась в течение долгого времени. Надеюсь, вам понравилась моя статья и примеры, приведенные в ней. И в завершение, в качестве подарка. И да простят меня разработчики!
Формула «УБИЙЦА ДОКУМЕНТА».Если Вам необходимо скрыть документ от чужих глаз навсегда, то эта формула для Вас. Сама формула:"=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))". $H:$H регулирует распространение формулы. После того как фомлулу запустите (Рисунок 31), ниже в ячейках она начнет размножать следующую функцию CONTINUE(ячейка; строка; столбец).