Excel формул в google docs

Время на прочтение
13 мин

Количество просмотров 396K

Обычно мы пишем про хостинги, в частности про зарубежный shared хостинг в США. Но чтобы писать, нужно иметь аналитические данные под рукой. Вот как раз тут требуется помощь Google Docs, если файл получится предположительно меньше 400 000 строк.

За несколько месяцев работы с таблицами Google пришлось много раз анализировать посредством формул разного рода данные. Как и ожидалось — то, что можно было решить в MS Excel, можно реализовать и в Google таблицах. Но многочисленные попытки решить проблемы с помощью любимого поисковика приводили только к новым вопросам и почти к нулевым ответам.
Посему, было решено облегчить жизни другим и прославить себя.

Кратко о главном

Для того чтоб Excel, либо spreadsheet (таблица Google) поняли что написанное — это формула, необходимо поставить знак «=» в строку формул (Рисунок 1).

Рисунок 1
Далее, начинаем писать формулу с клавиатуры либо выделяем мышкой те ячейки, с которыми мы собираемся работать.

Для определения ячейки может быть использовано 2 вида обозначений:

• буквенно — цифровое (БУКВА = СТОЛБЕЦ; ЦИФРА = СТРОКА) например «А1».
• стилем R1C1, в системе R1C1 и строки и столбцы обозначаются цифрами.

Адрес ячейки «B3» в такой системе будет выглядеть как R3C2 (R=row=строка, C=column=столбец). Для скриптов, например, используются оба стиля.
Там, где мы напишем «= формула», например, =SUM (A1:A10) и будет выводиться наше значение.
Общий принцип работы формул RC показан на Рисунке 2.

Рисунок 2
Как видно из Рисунка 3, значения ячеек идут относительно той ячейки, в которой будет написана формула со знаком равно. Для сохранения эстетичного вида формул, в них прописаны символы [0], которые можно и не писать: R[0]C[1] = RC[1].

Рисунок 3
Отличие Рисунка 2 от Рисунка 3 в том, что Рисунок 3 — это универсальная формулировка, не привязанная к строкам и столбцам (смотрите на значения строк и столбцов), чего не скажешь о рисунке 2. Но стиль RC в spreadsheet, в основном, используется для написания скриптов javascript.

Типы ссылок (типы адресации)

Для обращения к ячейкам используются ссылки, которые бывают 3-х типов:

• Относительные ссылки (пример, A1);
• Абсолютные ссылки (пример, $A$1);
• Смешанные ссылки (пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Знак $ здесь как раз и указывает на тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.

Относительные ссылки

Относительная ссылка «запоминает», на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили «=» (смещение в строках и столбцах). Затем потянуть вниз за маркер автозаполнения, и эта формула скопируется во все ячейки, через которые мы протянули.

Абсолютные ссылки

Как было сказано выше, если потянуть за маркер автозаполнения формулу, содержащую относительные ссылки, Таблица пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывают на одну и ту же ячейку.
Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом spreadsheet сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной типа A$1, если третий раз — $A1, если в четвертый раз — ссылка снова станет относительной. И так по кругу.

Смешанные ссылки

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будут вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки ведут себя как относительные, то есть spreadsheet начнет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится буквенное значение столбца (A, B, C…).

Посмотрим на пример суммирования ячеек с умножением на некий коэффициент.

Данный пример предусматривает наличие значения коэффициента в каждой вычисляемой ячейке (ячейки D8, D9,D10…E8,F8…). (Рисунок 4).
Красные стрелки показывают направление растягивания маркером заполнения формулы, которая находится в ячейки С2. В формуле обратите внимание на изменение ячейки D8. При растягивании вниз меняется лишь число символизирующее строку. При растягивании вправо изменяется лишь столбец.

Рисунок 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).

Рисунок 8

О том, как пишутся формулы

В формулировке формул в справочнике и в формулах, которые используются для работы на данный момент, присутствуют отличия. Они заключаются в том, что вместо «запятой», которая использовалась раньше во многих формулах, уже используется «точка с запятой» (изменения произошли более полугода назад).
Для того чтобы посмотреть, на что ссылается формула на данной странице (Рисунок 9), необходимо щелкнуть мышкой в строке формул справа от надписи Fx (Fx находится под основным меню, слева).

Рисунок 9
ВАЖНО: Для правильного функционирования формул, они должны быть написаны ЛАТИНСКИМИ буквами. Русская (кириллическая) “А” или “С” и латинская “А” или “С” для формулы — это 2 разные буквы.

Формулы

Арифметические формулы.

Расписывать, конечно, вечные операции сложения, вычитания и т.д., никто не будет, но они помогут понять сами азы. На нескольких примерах вы поймете, как они работают в этой среде. В документе, ссылка на который дана в конце статьи, приведены все формулы, мы же просто остановимся на скриншотах.

Сложение, вычитание, умножение, деление.

• Описание: формулы сложения, вычитания, умножения и деления.
• Вид формулы: “Ячейка_1+Ячейка_2”, “Ячейка_1-Ячейка_2”, “Ячейка_1*Ячейка_2”, “Ячейка_1/Ячейка_2”
• Сама формула: =E22+F22, =E23-F23, =E24*F24, =E25/F25.

Имеем начальные данные в диапазоне E22:H25, а результат в столбце D. На Рисунке 10 показана шапка, для всех данных, которые будут использоваться.

Рисунок 10

Прогрессия.

• Описание: формула для увеличения всех последующих ячеек на единицу (нумерация строк и столбцов).
• Вид формулы: =Предыдущая ячейка + 1.
• Сама формула: =D26+1

Напомним, если Вы хотите использовать диапазон, он будет суммировать все ячейки подряд, а если Вам нужно просуммировать ячейки в определенном порядке, то их нужно указать через “;” в нужном порядке. Имеем начальные данные для прогрессии в ячейке D26, а результат в ячейках E26:H26 (Рисунок 11) Используется для нумерации строк и столбцов.

Рисунок 11

Округление.

• Описание: формула для округления числа в ячейке.
• Вид формулы: =ROUND(ячейка с числом); счетчик (сколько цифр надо округлить после запятой).
• Сама формула: =ROUND(E28;2).

Имеем начальные данные в ячейке E28, а результат в ячейке D28 (Рисунок 12)

Рисунок 12
Округление “ROUND” происходит по математическим законам, если после запятой стоит цифра 5 или больше, то целая часть увеличивается на единицу, если 4 и меньше, то остается неизменной, также округление можно сделать с помощью меню ФОРМАТ — > Числа -> «1000,12» 2 десятичных знака (Рисунок 13). Если же вам необходимо большее количество знаков, то нужно нажать ФОРМАТ — > Числа -> Персонализированные десятичные -> И указать количество знаков.

Рисунок 13

Сумма, если ячейки идут не последовательно.

Наверное, самая знакомая функция

• Описание: суммирование чисел, которые находятся в разных ячейках.
• Вид формулы: =SUM(число_1; число_2;… число_30).
• Сама формула: «=SUM(E30;H30)» пишем через «;» если разные ячейки.

Имеем начальные данные в ячейках E30 и H30, а результат в ячейке D30

(Рисунок 14).
Сумма, если ячейки идут последовательно.

• Описание: суммирование чисел, которые идут друг за другом (последовательно).
• Вид формулы: =SUM(число_1: число_N).
• Сама формула: =SUM (E31:H31)» пишем через «:» если это непрерывный диапазон.
• Имеем начальные данные в диапазоне ячеек E31:H31, а результат в ячейке D31 (Рисунок15).

Рисунок 15

Среднее арифметическое.

• Описание: суммируется диапазон чисел и делится на количество ячеек в диапазоне.
• Вид формулы: =AVERAGE (ячейка с числом либо число_1; ячейка с числом либо число_2;… ячейка с числом либо число_30).
• Сама формула: =AVERAGE(E32:H32)

Имеем начальные данные в диапазоне ячеек E32:H32, а результат в ячейке D32 (Рисунок 16).

Рисунок 16
Конечно, есть и другие, но мы идем дальше.

Текстовые формулы.

Из великого количества текстовых формул, с помощью которых можно сделать все, что угодно с текстом, самая востребованная, на мой взгляд — это формула для «склеивания» текстовых значений. Существует несколько вариантов ее исполнения:

Склеивание текстовых значений (формулой).

• Описание: «склеивание» текстовых значений (вариант А).
• Вид формулы: =CONCATENATE(ячейка с числом/текстом либо текст_1; ячейка с числом/текстом либо текст_2; …, ячейка с числом/текстом либо текст_30).
• Сама формула: =CONCATENATE(E36;F36;G36;H36).

Имеем начальные данные в диапазоне ячеек E36:H36, а результат в ячейке D36 (Рисунок 17).
С помощью Google документов часто проводят опросы сотрудников или составляют социологические опросы через Google Forms (это специальные формы, которые можно создать через меню Вставка->Форма. После заполнения формы данные представляются в таблице. А далее, используют различные формулы для работы с данными, например, для склеивания Ф.И.О.).

Рисунок 17

Склеивание числовых значений.

• Описание: “склеивание” текстовых значений руками, без использования специальных функций (вариант B — ручное написание формулы, сложность формулы любая.).
• Вид формулы: =ячейка с числом/текстом 1&» «&ячейка с числом/текстом 2&» «&ячейка с числом/текстом 3&» «& ячейка с числом/текстом 4 (» » — пробел, знак & означает склеивание, все текстовые значения пишутся в кавычках “”).
• Сама формула: =E37&» «&F37&» «&G37&» «&H37.

Имеем начальные данные в диапазоне ячеек E37:H37, а результат в ячейке D36 (Рисунок 18 — склеенные числа).

Рисунок 18

Склеивание числовых и текстовых значений.

• Описание:«склеивание» текстовых значений руками, без использования специальных функций (вариант С — смешанный тип, сложность формулы любая).
• Вид формулы: = «текст_1 » &ячейка_1&«текст_2»&ячейка_2&«текст_3»&ячейка_3
• Важно: весь текст, который будет написан в “” будет неизменным для формулы.
• Сама формула: =«Еще 1 » &E38&» использования «&F38&» как НАМ «&G38.

Имеем начальные данные “Еще 1”, “использования”, “как НАМ” и в диапазоне ячеек E38:G38, поэтому целесообразно использовать такой вид формулы, а результат в ячейке D36 (Рисунок 19).
Склеиваем текст и числовые значения.

Рисунок 19

ЛОГИЧЕСКИЕ И ПРОЧИЕ

Перенос данных из любых листов одного и того же файла.

Мы подошли к самым интересным, на мой взгляд, функциям: ЛОГИЧЕСКИЕ И ПРОЧИЕ.
Одна из самых нужных формул:

• Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
• Вид формулы: = «Название_Листа»! ячейка_1
• Сама формула:=Data!A15 (Data — лист, А15 — ячейка на том листе).

Имеем начальные данные на листе Data ячейка А15 (Рисунок 20), а результат на листе Formula в ячейке D41 (Рисунок 20.1).

Рисунок 20

Рисунок 20.1

Массив формул.

Большинство программ для работы с таблицами содержат два типа формул массива: «для нескольких ячеек» и «для одной ячейки».
Таблицы Google разделяют эти типы на две функции: CONTINUE (ПРОДОЛЖИТЬ) и ARRAYFORMULA.
Формулы массива для нескольких ячеек позволяют формуле возвращать несколько значений. Вы можете использовать их, даже не зная этого, просто вводя формулу, возвращающую несколько значений.
Формулы массива «в одной ячейке» позволяют записывать формулы с помощью ввода массива, а не выходных данных. При заключении формулы в состав функции =ARRAYFORMULA можно передать массивы или диапазоны функциям и операторам, которые, как правило, используют только аргументы, не принадлежащие массивам. Данные функции и операторы будут применяться по одному для каждой записи в массиве, и возвращать новый массив со всеми выходными данными.
Если вы хотите изучить вопрос более детально, вам следует посетить support.google.
Говоря простыми словами, для работы с формулами, которые возвращают массивы данных, во избежание синтаксических ошибок, необходимо заключать их в массив формул.

Суммирование ячеек с условием ЕСЛИ.

Для того чтобы оперировать логическими формулами, а они обычно содержат большие массивы данных, их помещают в массив формул ARRAYFORMULA (формула).

• Описание: суммирование ячеек с условием ЕСЛИ (формула SUMIF).
• Вид формулы: = SUMIF(‘Лист’! диапазон; критерии; ‘Лист’! суммарный_диапазон)

Для объяснения формулы подробно разберем пример: 3-м покупателям было поручено купить продукты по списку, но оплатить одной суммой. После того, как продукты пробили на кассе, получился список продуктов (Рисунок 21) в столбце А, а их количество в столбце B.
Задача, какой вид будет иметь фискальный чек, после распечатки (попросту нужно сложить продукты 3-х покупателей и узнать кол-во продуктов в сумме по каждой позиции)?

Рисунок 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)))

Имеем начальные данные на листе Data (Рисунок 23).

Рисунок 23
Допустим, что на листе Formula, в ячейке В53 (критерий_1 = Пиво) должно быть название напитка, а ячейка С53 (критерий_2 = 2), это количество друзей, которые принесут Пиво. В итоге в ячейке D53 окажется результат, что нам нужно докупить 15 бутылок пива. (Рисунок 23.1) то есть, формула определит сумму по двум критериям — пиво и количество друзей.

Рисунок 23.1
Если таких позиций будет больше, строки 16 и 21(Рисунок 24), то количество пузырей в колонке G суммируется (Рисунок 24.1).

Рисунок 24
Итого:

Рисунок 24.1

Теперь приведем более интересный пример:

Ха… вечеринка продолжается, и вы вспоминаете, что нужен торт, но непростой, а супер – мега торт, с разными специями, которые, как назло, еще и зашифрованы под цифровые обозначения. Задача состоит в том, чтобы купить специи в нужном количестве пакетиков каждой из специи. Нужное количество повар зашифровал в таблицу (Рисунок. 25.1), столбцы A и B (в соседних столбцах делаем наши вычисления).
Каждая специя имеет свой порядковый номер: 1,2,3,4. (Рисунок 25).

Рисунок 25
Наша задача посчитать количество повторяющихся значений, в нашем случае, это числа от 1 до 4 в столбце B и определить сколько процентов приходится на каждую из специй.

• Описание: подсчет количества одинаковых цифр в больших массивах при дополнительных условиях.
• Вид формулы: СЧИТАТЬ ЕСЛИ(‘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)

Имеем начальные данные в диапазоне A55:B61, условие отбора выбираем по ячейке F55 и E59:E62, а результат в диапазоне ячеек F59:F62 (подсчет количества повторов числовых значений при совпадении условий).

• Описание: вычисление процента специй.
• Вид формулы: Количество*100%/Общее_количество
• Сама формула: =F58*$G$56/F$56

Рисунок 25.1
В конечном итоге мы имеем сумму повторов и процент.
Для правильного написания формулы, вы должны полностью представлять, что вы ИМЕЕТЕ, что ХОТИТЕ ПОЛУЧИТЬ и в каком виде. Возможно, для этого вам предстоит изменить вид начальных данных.
Переходим к следующему примеру

Подсчет значений в объединенных ячеек.

Если в формулах используются значения в «объединенных ячейках», то указывается первая ячейка для объединенных данных, в нашем случае это столбец F, а ячейка F65 (Рисунок 26)

• Описание: формула для подсчета значений, в которых присутствует символ @.
• Вид формулы: СЧИТАТЬ ЕСЛИ(В столбце F листа “Formula” есть текст с содержимым @).
• Сама формула: =COUNTIF(‘Formula’!F65:F68; «*@*»).

  

Рисунок 26.
И наконец мы добрались до самых ужасных формул.

Подсчитывает количество чисел в списке аргументов.

Существует несколько видов таких подсчетов, они подходят для больших таблиц, в которых нужно считать количество одинаковых слов либо количество чисел. Но при правильном понимании этих формул с ними можно творить такие чудеса как, например: подсчет слов без учета слов исключений. Примеры ниже.

• Описание: подсчет количества ячеек, содержащих цифры без текстовых переменных.
• Вид формулы: COUNT(значение_1; значение_2; … значение_30)
• Сама формула: =COUNT(E45;F45;G45;H45)

Имеем начальные данные в диапазоне ячеек E70:H70, а результат в ячейке D70 (Рисунок 27 — подсчет ячеек, содержащих числовые значения в диапазоне, в котором имеются ячейки с текстом).

Рисунок 27.
Ячейки, содержащие текст и цифры также не считаются.

Рисунок 27.1.

Подсчет количества ячеек содержащих цифры с текстовыми переменными.

• Описание: подсчет количества ячеек, содержащих цифры с текстовыми переменными.
• Вид формулы: COUNTA(значение_1; значение_2; … значение_30)
• Сама формула: =COUNTA(E46:H46)

Имеем начальные данные в диапазоне ячеек E71:H71, а результат в ячейке D71 (Рисунок 28 — подсчет всех значений в диапазоне).

Рисунок 28.
Также, формула считает ячейки, содержащие только знаки препинания, табуляции, но не считает пустые ячейки.

Рисунок 28.1

Подстановка значений при условиях.

• Описание: подстановка значений при условиях.
• Вид формулы: «=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)»
• Сама формула: «=IF(AND((F73=5);(H73=5));0;1)»

Имеем начальные данные в ячейках F73 и H73, а результат в ячейке D73 (Если F73=5 и H73 =5 то D73=0 во всех остальных случаях 1) (Рисунок 29).

Рисунок 29.

Рисунок 29.1
Усложним пример.
Посчитать количество ячеек, в которых написаны временные рамки без учета слов «автоответ», «занято», «-«.

• Вид формулы:»=COUNTA(Диапазон_А)-COUNTIF(Диапазон_А; «автоответ»)-COUNTIF(Диапазон_А; «-«)-COUNTIF(Диапазон_А; «занято»)»

• Сама формула: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; «автоответ»)-COUNTIF($E74:$H75; «-«)-COUNTIF($E74:$H75; «занято»)

Имеем начальные данные в диапазоне ячеек E74:H75, а результат в ячейке D74(Рисунок 30).

Рисунок 30
Вот мы и подошли к концу нашего маленького ликбеза по формулам в Google SpreadSheet и у меня большие надежды, что я пролил свет на некоторые аспекты аналитической работы с формулами.
Формулы, честно говоря, были в прямом смысле выстраданы. Каждая из них создавалась в течение долгого времени. Надеюсь, вам понравилась моя статья и примеры, приведенные в ней.
И в завершение, в качестве подарка. И да простят меня разработчики!

Формула «УБИЙЦА ДОКУМЕНТА».

Если Вам необходимо скрыть документ от чужих глаз навсегда, то эта формула для Вас.
Сама формула:»=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))». $H:$H регулирует распространение формулы. После того как фомлулу запустите (Рисунок 31), ниже в ячейках она начнет размножать следующую функцию CONTINUE(ячейка; строка; столбец).

Рисунок 31
Формула циклически добавляет в весь столбец формулы. Для того чтобы убить документ нужно немножко постараться, создать N-ое количество ячеек и прописать формулу в первых ячейках N-го количества столбцов. Все! Документ больше ни кто исправить и проверить не сможет!
Вот что говорит страница помощи гугла о загруженности и ограничениях — http://support.google.com/drive/bin/answer.py?hl=ru&p=spreadsheets_timeout&answer=2505921
Обещанный документ «Талмуд» по формулам в Google SpreadSheet шел как основа.

До новых встреч, с уважением

Антон Пилюганов.

Как настроить формулы в Google таблицах: подробный гайд

Перевод текстов, импорт данных, графики… Формулы сделают все за вас.

В нашем подробном гайде по Google Таблицам для новичков мы затронули работу с формулами (функциями), но не остановились на ней подробнее, потому что это тема для отдельной статьи.

Формулы нужны, в первую очередь, для удобства работы с таблицами – они помогают делать автоматический подсчет различных показателей, структурировать данные, и даже переводить текст с одного языка на другой, объединять между собой разные массивы данных и так далее. При работе с большими объемами данных работать без формул просто невозможно – об этом вам расскажут на курсах интернет-маркетинга.

Здесь же мы поговорим о самых нужных для digital-специалиста формулах Гугл Таблиц.

Как вводить формулы

Есть два способа. Можно вводить формулы вручную:

1. Выберите ячейку, в которой хотите сделать формулу. Наберите = и начинайте вводить формулу:

Сервис сам предложит варианты формул

1. После того, как вы ввели формулу, заполните ее параметры. У разных формул они, соответственно, разные:

У некоторых функций нужно будет заполнить параметры и условия

1. Дальше нужно выбрать диапазон (ячейку, столбец, строку), который формула будет использовать, чтобы преобразовывать данные:

Диапазон можно выбрать, открыв скобки и щелкнув на нужную ячейку, строку или столбец. А если нужно выбрать несколько отдельно стоящих ячеек/столбцов/строк подряд, надо зажать Shift и нажимать на них

Если все сделано правильно, формула заработает. Если нет, то появится значок ошибки, при нажатии на который можно получить расшифровку и инструкцию, как исправить ошибку:

Есть десятки возможных ошибок, все можно исправить, прочитав расшифровку

Есть еще один способ ввести формулу – перейти в «Вставка» → «Функции» и выбрать формулу из списка:

Здесь представлены все возможные формулы Гугл-таблиц

В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров

Обзор важных функций для digital-специалиста

Формул и функций здесь очень много. Не будем останавливаться на простых (например, формула для суммирования), но расскажем о более сложных, но не менее нужных.

Список всех доступных функций – в справке Google.

Подсчет символов

=ДЛСТР(ячейка)

Эта формула считает количество символов в ячейке. Многие оформляют контент-планы в Google-таблицах. Учитывая то, что у большинства соцсетей есть ограничения по количеству символов в посте, эта формула очень кстати.

Работа формулы =ДЛСТР

Перевод текста из верхнего регистра в нижний (и наоборот)

=СТРОЧН(диапазон)

Формула переводит текст, написанный с использованием Caps Lock, в нижний регистр.

Работа формулы =СТРОЧН(диапазон)

=ПРОПИСН(диапазон)

Эта формула, наоборот, переводит текст в верхний регистр.

Работа формулы =ПРОПИСН(диапазон)

Эти формулы могут пригодиться во время, например, изучения аудитории конкурентов – если вы скопировали список имен и фамилий из сервиса, который парсит аккаунты, а он в верхнем регистре (что бывает), с помощью формулы можно будет легко перевести текст в нижний регистр.

Pepper Ninja, Popsters, Adinblog, Spywords. этими сервисами стоит пользоваться каждому предпринимателю

Импорт данных из других источников

=IMPORTHTML(«ссылка»; «table»; 0; «rus_RU»)

С помощью этой формулы можно перенести любую таблицу или список в свою Google Таблицу. Для этого нужно вставить в формулу ссылку, которая ведет на страницу, где содержится контент, который нужно импортировать.

Таким образом удастся импортировать тот контент, который изначально сделан в виде таблицы или списка – например, рейтинг спортивных команд

Чтобы импортировать список и сохранить его формат в виде списка, нужно в параметрах выбрать не «table», а «list». Все остальные параметры нужно оставить, как есть.

Ваша заявка принята.
Мы свяжемся с вами в ближайшее время.

Перевод текста

=GOOGLETRANSLATE(ячейка; «исходный_язык» или «auto»; «целевой_язык»)

Формула переводит с любого языка. И на любой язык. Большинство языков даже не нужно указывать в качестве исходного – сервис автоматически определяет язык оригинала, для чего нужно вписать команду «auto».

Трюк не сработал с чувашским языком, которого нет в сервисе Google Переводчик. Вероятно, с другими не очень популярными языками будет похожая история

Объединение ячеек

=CЦЕПИТЬ(диапазон;диапазон)

Формула позволяет объединить текст из двух ячеек в одной.

Чтобы текст не «склеился», поставьте между объединяемыми диапазонами аргумент “ “

Эта формула может пригодиться в разных ситуациях. Например, когда у вас есть список имен и номеров, которые нужно объединить в одну ячейку.

Еще эта функция пригодится для комбинирования разных ключевых фраз.

Как использовать ключевые слова в заголовках статей

Разделение фраз

=SPLIT(ячейка; «разделитель»; [тип_разделителя]; [выполнять_ли_удаление_пустых_ячеек])

То же самое, что объединение данных из ячеек, только наоборот. Если вы спарсили список контактов откуда-то, то в одной строке может оказаться несколько параметров: имя, место жительства, телефон. Формула поможет все разделить и избавиться от ненужных символов (запятые, цифры, пробелы).

Нужно указать тип разделителя, который будет удаляться, в нашем примере это “,”

Удаление лишних пробелов

=СЖПРОБЕЛЫ(ячейка)

Эта формула удаляет только лишние пробелы, оставляя нужные. Пригодится, тем, кто набирает большие объемы текста вручную.

На скриншоте можно заметить, что в некоторых местах текста было по 2-3 пробела, которые формула успешно удалила, оставив только один

Работа с графиками

=SPARKLINE(диапазон:диапазон)

Формула позволяет создавать мини-графики прямо в Таблицах. Это пригодится, если вы занимаетесь отслеживанием тех или иных показателей.

Нужно разместить все показатели горизонтально, слева направо, и в последней ячейке сгенерировать формулу

Чтобы поменять внешний вид формулы, нужно указать параметры. Например, здесь мы поменяли цвет на красный:

Можно указать любой другой цвет: indigo, black, grey, blue, purple, pink, yellow, green, orange

Также можно менять тип диаграммы – для этого нужно менять параметр «charttype». Поменяем «line» на «bar»:

А теперь поменяем на «column»:

Получается столбчатая диаграмма

Обо всех возможностях формулы читайте в Справке.

Работа с изображениями

=IMAGE(“ссылка”)

Формула позволяет вставить любую картинку из интернета в ячейку. Для этого не придется сохранять картинку к себе на компьютер:

Здесь мы вставили картинку с Unsplash

С помощью параметров можно менять размер и ориентацию картинки, чтобы она вписывалась в ячейку так, как нужно вам.

Обо всех возможностях этой формулы читайте в этом разделе Справки.

17 фишек Excel, с которыми будет проще запустить рекламу

Источник

20+ продвинутых функций Google Таблиц (Spreadsheets)

Для работы с текстом, визуализации данных, парсинга сайтов и других задач. Не все, но многие функции поддерживаются и в старом-добром Microsoft Excel.

Не только СУММ и СЦЕПИТЬ: Google Таблицы (или Google Spreadsheets) намного функциональнее и мощнее, чем это может показаться при поверхностном знакомстве.

На конкретных примерах разбираем полезные и интересные фичи, которые могут пригодиться в работе самым разным людям: владельцам бизнеса, руководителям, специалистам.

Этот обзор — только часть полезного образовательного контента от центра CyberMarketing. Вас ждут статьи, вебинары и курсы по интернет-маркетингу: SEO, PPC, SMM, веб-аналитике и другим важным тематикам.

IMPORTRANGE

IMPORTRANGE (русскоязычного названия нет) — функция, которая загружает данные из одной Google Таблицы в другую. Принимает два параметра: URL таблицы и диапазон, откуда нужно импортировать данные. Например: =IMPORTRANGE(«1iufABCDBDfT5BtDq1RJJw968xEDUWH80uM3u9ByATdoE»;»Декабрь 2017!A:B»)

Ссылку на таблицу можно вставить целиком или же взять лишь ее уникальный ID. Еще обратите внимание на второй аргумент: кириллическое название листа — без одинарных кавычек, хотя мы используем их, когда ссылаемся на такой лист в таблице.

Главное преимущество по сравнению с элементарным «Копировать → Вставить» — автоматическая загрузка новых данных. И эти новые данные легко сразу же использовать в других функциях или сводных таблицах благодаря возможности Google Spreadsheets задавать открытые диапазоны (к примеру, A2:B вместо A2:B20).

А еще IMPORTRANGE можно вложить в ВПР или QUERY, о которых речь пойдет дальше, или в другие функции, которые работают с диапазонами. Тогда можно будет не содержать дополнительный лист специально под импорт.

IMPORTHTML и IMPORTXML

Google Таблицы могут извлекать данные не только из таблиц, но и прямо с сайтов, то есть парсить их. Всего таких функций четыре, но больше пригождаются IMPORTHTML и IMPORTXML (у них тоже нет русскоязычных названий).

IMPORTHTML — функция, которая может импортировать данные с веб-страницы, если они представлены в виде таблицы или списка. Например, она может выглядеть так: =IMPORTHTML(«http://cbr.ru/key-indicators/»;»table»;2), где:

1. URL или ссылка на ячейку с адресом сайта.
2. Запрос, у которого только два варианта: «table» и «list» для таблиц и списков соответственно.
3. Индекс, порядковый номер элемента. (Не всегда цифра очевидна, придется методом перебора выяснять, под каким именно номером на странице будут нужные данные.)

В данном случае функция выводит таблицу с ценами на драгоценные металлы — это информация с сайта Банка России:

IMPORTXML тоже принимает первым параметром адрес страницы, а вторым — запрос XPath (это специальный язык для работы с XML-документами). Среди прочего эту функцию можно использовать для парсинга метатегов. Так, чтобы получить заголовок страницы, нужно вставить в ячейку текст вида: =IMPORTXML(«https://www.ozon.ru/category/tehnika-dlya-krasoty-i-zdorovya-10737/»;»//title»)

Если взять запрос «//meta[@name=’description’]/@content», Google Таблицы извлекут описание (дескрипшн), а если «//h1» — заголовок первого уровня соответственно. Чтобы выгрузить список ссылок со страницы, подойдет «//a/@href»:

Еще есть IMPORTDATA, которая работает с данными в формате CSV (значения, разделенные запятыми) или TSV (значения, разделенные табуляцией), и IMPORTFEED, которая загружает фид RSS или Atom. Но на практике они используются гораздо реже.

Конечно, есть и более удобные инструменты для парсинга метатегов и заголовков, например, Click.ru. Тем более этим функциональность не ограничивается: специалисты активно используют кластеризацию запросов, генерацию объявлений из YML, медиапланирование, создание отчетов и др. Бонус: вознаграждение до 18 % с рекламного оборота.

ВПР (VLOOKUP) и ГПР (GLOOKUP)

ВПР (VLOOKUP) — незаменимая функция для объединения данных из разных источников: листов и даже таблиц (если использовать вложенный IMPORTRANGE). Синтаксис: =ВПР(A2; ‘Отчет’!$A$2:$C; 4; 0), где:

1. запрос, по которому нужно искать (здесь он будет взят из указанной ячейки);
2. диапазон, в первом столбце которого нужно искать;
3. номер столбца (от начала диапазона, а не листа), откуда нужно взять значение;
4. дополнительный параметр, который настраивает точность поиска (по умолчанию 1, но лучше ставить 0, тогда будет возвращаться только точное совпадение).

Допустим, есть два листа: на одном список URL с названиями страниц, на другом — тоже список URL, но с показателями по продажам или трафику. С помощью ВПР легко объединить эти данные в один отчёт.

• Использовать абсолютные ссылки на диапазон (со знаками доллара), иначе при протягивании ВПР они тоже будут меняться, в результате поиск может работать некорректно.
• Третьим параметром передавать номер столбца от начала диапазона, а не от начала листа. (Необязательно ссылаться на весь лист — нужные ячейки могут лежать не в A:B, а в E:F, например).
• ВПР ищет совпадения только в первом столбце диапазона и берет значения только справа от него. В остальных случаях по умолчанию эта функция не справится, но хорошо, что есть другие варианты.

Чтобы функция ВПР возвращала значения не только правее, но и левее первого столбца диапазона, есть лайфхак с использованием массива. Суть: создать виртуальную таблицу, где столбцы будут расположены в порядке, необходимом для корректной работы VLOOKUP.

Например =VLOOKUP(C2;<‘Лист2’!D:D ‘Лист2’!B:B ‘Лист2’!C:C>;2;0) успешно произведет поиск по четвертому столбцу и передаст данные из второго. Потому что в массиве значения диапазона D:D идут первым столбцом — нет никаких противоречий.

Функция-побратим — ГПР (HLOOKUP) — работает похожим образом, только ищет по строкам, а не столбцам. На практике это может понадобиться гораздо реже.

ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX)

Совместное использование ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) — еще один способ обойти ограничение функций ВПР (VLOOKUP) и ГПР (HLOOKUP), которые ищут только по первому столбцу или первой строке диапазона.

Алгоритм такой: MATCH находит значение в диапазоне (строка или столбец) и возвращает его порядковый номер, а INDEX — передает содержимое ячейки, у которой такой же порядковый номер, просто она находится в соседней строке или столбце.

Пример: =INDEX(‘Лист2′!$B$2:$B;MATCH(C3;’Лист2’!$D$2:$D;0)). Сначала запускается MATCH: находит значение из C3 на другом листе в столбце D, затем возвращает порядковый номер. INDEX берет этот номер и ищет по нему уже в столбце B, затем возвращает результат:

• ПОИСКПОЗ (MATCH) может работать только с одной строкой или с одним столбцом. Если попытаться отправить многомерный массив вроде A:D, функция выдаст #Н/Д! Третий параметр функции — метод поиска. Ноль требует точный поиск, показывает, что диапазон никак не отсортирован.
• ИНДЕКС (INDEX) может работать с любыми диапазонами, но в сочетании с ПОИСКПОЗ понадобится только поиск по столбцу. Поэтому третий параметр не используется — в ИНДЕКС передаются только диапазон (столбец, откуда нужно взять значение) и номер строки (его возвращает ПОИСКПОЗ).
• ИНДЕКС и ПОИСКПОЗ оперируют номерами строк/столбцов именно заданных диапазонов, а не листов — важно помнить об этом при работе.

SPARKLINE

Спарклайн — интересный инструмент визуализации, который не требует много места: диаграмма умещается в одну ячейку. Аргументов два: диапазон или массив данных и набор опций (необязательный). В последнем можно задать, например:

• Тип диаграммы (charttype) — по умолчанию line (график), но можно поменять на bar (гистограмму) или column (столбчатую диаграмму).
• Цвет линии или столбцов диаграммы (color) — зеленый (green), желтый (yellow) и любой другой по шестнадцатеричному коду.
• Максимальное (max, ymax) и минимальное (min, ymin) значения по горизонтальной или вертикальной оси.

Такие дополнительные параметры можно передать массивом, — вставив его прямо в функцию — или сослаться на ячейки, где в первом столбце будет название параметра, а во втором — его значение.

Допустим, есть задача: изучить динамику трафика на страницы по месяцам. Если таких страниц сотни, бессмысленно для каждой из них строить большой график или диаграмму. А если оставить просто цифры, придется долго их считывать, чтобы разобраться. Тут на помощь и приходит функция SPARKLINE (русскоязычного названия нет).

Синтаксис: =SPARKLINE(B2:E2;<«charttype»»column»;»color»»green»>) где первым параметром идет диапазон с данными для визуализации, а вторым — массив с набором опций, который в данном случае указывает рисовать столбчатую диаграмму, а не график по умолчанию, и покрасить ее в зеленый цвет:

Но посмотрите внимательно на данные и сами диаграммы на этом примере. Сейчас кажется, что страница №3 сильнее всех просела по трафику в марте, хотя потеря составила всего 721 визит. Тогда как страница №1 потеряла целых 8956 визитов. Чтобы решить такую проблему, нужно как-то связать данные — например, с помощью опций ymin и ymax, которые передают максимальное и минимальное значение по всем страницам: =SPARKLINE(B2:E2;<«charttype»»column»;»color»»green»;»ymax»MAX($B$2:$E$4);»ymin»MIN($B$2:$E$4)>) Тогда получается гораздо нагляднее и реалистичнее:

Кстати, если вы увлекаетесь инвестициями, комбинация SPARKLINE и GOOGLEFINANCE поможет изучать динамику котировок акций и курсов валют. На скриншоте — визуализация изменения стоимости акций Google за прошедший год:

ТРАНСП (TRANSPOSE)

ТРАНСП (TRANSPOSE) пригодится, когда нужно транспонировать таблицу (матрицу), то есть поменять строки и столбцы местами. В качестве аргумента можно передать диапазон или массив, например, так: =ТРАНСП(A35:G40)

Допустим, вы выгружаете из Яндекс.Метрики отчет с данными графика — чтобы посмотреть динамику трафика по определенным разделам:

Голые цифры считываются плохо, гораздо нагляднее будет сделать визуализацию с помощью спарклайнов — диаграмм, которые умещаются в ячейку. Но для этих целей нужно расположить визиты по конкретному разделу в одну строку. Тогда сразу будет понятно, в какой временной период трафик просел или взлетел:

Чтобы функция создала транспонированную таблицу, необходимые для этого ячейки должны быть свободны от значений — иначе будет ошибка.

Конечно, есть и альтернативное решение без использования этой функции: скопировать нужный диапазон, кликнуть правой кнопкой мыши и выбрать «Специальная вставка → Вставить с изменением положения строк и столбцов».

IFS (множественное IF)

IFS (русскоязычного аналога нет) — расширенная версия функции ЕСЛИ (IF), которая позволяет оценивать сразу несколько условий. Возвращает то значение, которое соответствует первому истинному условию (TRUE). То есть сначала проверяет первое условие (слева), если оно истинно — отправляет первое значение, если ложно — идет дальше вправо. Синтаксис: =IFS(условие1; значение1; условие2; значение2; …) Если все условия ложные, вернёт #Н/Д!

Допустим, вы выгрузили (из системы аналитики или CMS) список URL с какими-то дополнительными данными: названиями, датами публикаций, количеством визитов, продажами и т. д. Например, такой:

И для удобства работы и отчетности хотите создать дополнительный столбец, где будут просто и понятно указаны категории (типы), извлеченные из адресов страниц. Чтобы легко можно было отсортировать или отфильтровать таблицу, посчитать сумму показателей по конкретной категории и т. п.

Есть разные варианты решений. Например, правее можно прописать и протянуть функцию =SPLIT(B2;»/») — она разложит URL на составляющие. Далее достаточно посмотреть, в какой ячейке лежит нужная часть адреса, и составить формулу вида: =IFS(I5=»green_tea»;»Зеленый чай»;I5=»ulun»;»Улун»;I2=»travyanoy-chay»;»Травяной чай») Недостаток такого подхода — множество лишних «технических» ячеек, они могут мешать, их придется скрывать.

Другой способ — вложить в IFS несколько других функций: НЕ (NOT), ЕОШИБКА (ISERROR), НАЙТИ (FIND). Тогда формула примет более сложный вид, но зато не нужны будут никакие дополнительные ячейки: =IFS(NOT(ISERROR(FIND(«/green_tea/»;B2)));»Зеленый чай»;NOT(ISERROR(FIND(«/ulun/»;B2)));»Улун»;NOT(ISERROR(FIND(«/travyanoy-chay/»;B2)));»Травяной чай»)

Почему такая сложная конструкция? Дело в том, что FIND возвращает #Н/Д, если не находит запрос в тексте, а это прерывает проверку всех условий в IFS. Поэтому приходится использовать ISERROR, что возвращает TRUE, если функция FIND выдает ошибку. Но TRUE опять прервет выполнение IFS — ведь условие должно наоборот быть ложным, чтобы начать проверять следующее условие. Поэтому приходится усложнять и добавлять NOT, которая поменяет TRUE на FALSE.

Есть и другой вариант реализации — через регулярные выражения и соответствующие функции Google Таблиц.

Эти три функции Google Таблиц предназначены для работы с регулярными выражениями (специальный язык для работы со строками и символами). REGEXMATCH ищет соответствия, REGEXEXTRACT извлекает нужный фрагмент, а REGEXREPLACE заменяет одну часть текста на другую. Синтаксис похожий: первый аргумент — текст, а второй — само регулярное выражение; в REGEXREPLACE есть еще третий — текст, который нужно вставить.

Допустим, нужно из URL конкретной страницы извлечь название сайта. Для этой цели можно использовать такой вариант: =REGEXEXTRACT(C23;»https://(.*?)/») Функция возьмет все символы, что находятся между «https://» и следующим слешем, включая дефисы и точки. Поэтому нормально будут экстрагироваться и домены второго уровня:

С помощью REGEX можно также решить задачу с категориями из предыдущего раздела про IFS. Тогда получится так: =IFS(REGEXEXTRACT(C2;»/catalog/([^/]+)»)=»travyanoy-chay»;»Травяной чай»;REGEXEXTRACT(C2;»/catalog/([^/]+)»)=»ulun»;»Улун»;REGEXEXTRACT(C2;»/catalog/([^/]+)»)=»green_tea»;»Зеленый чай»)

Почему такой вариант, и как он работает? «/catalog/» — общая часть у всех URL, поэтому можно смело начинать поиск совпадений с нее. Далее нужно взять все символы, что находятся между «/catalog/» и следующим слешем. Конструкция ([^/]+) как раз за это отвечает. Получается, функция ищет любое число любых символов, кроме слеша, на котором она и остановится. ‘^’ здесь используется как оператор отрицания, ‘+’ задаёт 1 или более повторений символов, а круглые скобки — что нужно брать только эту группу, не включая остальные части текста.

ARRAYFORMULA

ARRAYFORMULA (русскоязычного названия нет) — функция для работы с массивами. В качестве параметра принимает формулу массива или другую функцию.

Допустим, справа от основной таблицы нужно создать столбец с каким-то вычисляемым показателем, например, чтобы тот считал разницу между другими. Конечно, это можно сделать через обычное протягивание формулы, но если таблица постоянно пополняется новыми строками — придется постоянно протягивать ее вручную все ниже и ниже. ARRAYFORMULA же позволяет автоматизировать процесс: за счет вычитания одного массива с открытым диапазоном из другого:

Единственное, что в данном случае формула будет заполнять ячейки до самого конца таблицы — а лишние нули это не очень красиво. Решение — дополнительно использовать IF: =ARRAYFORMULA(IF(D2:D<>«»;E2:E-D2:D;»»)) которое сообщает следующее: если в ячейке D пусто, то и вычитание не нужно, оставить ячейку пустой.

Аналогичным способом ARRAYFORMULA можно использовать вместе с ВПР(VLOOKUP), к примеру: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A;feb!$A:$D;2;0);»»)) Только здесь от лишних #N/A до конца таблицы спасает функция ЕСЛИОШИБКА (IFERROR).

Увлечение ARRAYFORMULA (особенно если еще в большом количестве используются такие функции, как VLOOKUP, MATCH, INDEX, QUERY) может существенно замедлять работу Google Таблицы. Ускориться помогает удаление лишних строк (по умолчанию их 1 000, сотни могут совсем не использоваться и только зря обрабатываться функцией ARRAYFORMULA).

SORTN

SORTN — расширенная версия функции SORT, которая может не только сортировать данные по нескольким столбцам, но и ограничивать количество возвращаемых результатов. Параметры:

1. Диапазон для сортировки и вывода. (Впрочем, столбцы, по которому данные сортируются, можно не включать в этот диапазон, указать их отдельно в четвертом параметре.)
2. Количество возвращаемых элементов. (Можно сделать топ-3, топ-5 и т. д.)
3. Режим показа совпадений. (По умолчанию ноль. Единица, например, будет выводить дополнительные строки, — больше, чем указано во втором параметре — если в столбце для сортировки найдутся повторяющиеся значения.)
4. Столбец для сортировки. (Может быть вне диапазона, указанного в первом параметре.)
5. Способ сортировки столбца. ИСТИНА (TRUE) сортирует данные по возрастанию (от меньшего к большему), а ЛОЖЬ (FALSE) – по убыванию (от большего к меньшему).

(Если нужно, дальше можно также задать дополнительные столбцы и варианты сортировки.)

Допустим, есть таблица с показателями трафика за несколько месяцев. И нужно подготовить топ лучших или худших страниц по динамике за последние два. Для этого как раз хорошо подходит функция SORTN.

Пример: =SORTN(A2:F7;3;0;6;TRUE), которая выводит данные из A2:F7, но только первые три строки, отсортированные по шестому столбцу (F) по возрастанию:

Если столбец для сортировки не входит в первый диапазон, нужно передать его четвертым параметром (главное условие — такое же количество элементов, как у первого). Пример: =SORTN(A2:E7;3;0;F2:F7;TRUE)

FILTER

FILTER (опять без русского аналога) — мощная функция Google Таблиц, которая выводит только те строки и столбцы, которые соответствуют заданным условиям. Первым аргументом принимает диапазон, вторым и последующими — условия для фильтрации.

Допустим, есть стандартный отчет по поисковым фразам и поведению пользователей, которые пришли по ним на сайт. (Первый столбец — сами запросы, второй — визиты, дальше отказы, глубина просмотра и время на сайте, в конце — достижения любой цели.) И нужно узнать наиболее приоритетные ключи для продвижения. Например, выбрать те, что дали больше 50 визитов и больше 50 конверсий за отчетный период.

Здесь подойдет такой вариант: =FILTER(‘Запросы’!A2:G;’Запросы’!B2:B>50;’Запросы’!G2:G>50), где мы сначала указываем диапазон данных для фильтрации и вывода, затем условия — во-первых, значения в столбце B должны быть больше 50, во-вторых, значения в столбце G тоже должны быть больше 50.

Столбцы или строки, по которым фильтруются данные, не обязаны входить в первый диапазон. Например, нет смысла в столбце, где все значения будут повторяться — а так и будет, если FILTER отбирает данные по какой-то одной единственной категории. Если в этом примере формулы поменять Запросы!A2:G на Запросы!A2:A, ничего не сломается — просто будет выводиться только первый столбец.

Теперь другой, более сложный пример использования FILTER. Допустим, вы сделали копию прайс-листа поставщика, потому что так с данными удобнее работать, но нужно периодически проверять оригинальную таблицу — что нового там появилось и стоит ли обновить свою. И нужно проверять не все позиции, а самые приоритетные и прибыльные. Это можно осуществить, сочетая FILTER с IMPORTRANGE, MATCH и ISERROR. Например, так:

=FILTER(IMPORTRANGE(«1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY»;»Каталог!A2:E»);IMPORTRANGE(«1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY»;»Каталог!A2:A»)=1;(ISERROR(MATCH(IMPORTRANGE(«1znX3hxN9cKEvZyh_0XOj7gHpjYze8p-40cZchiDvTxY»;»Каталог!B2:B»);B3:B7;0))))

Что здесь происходит? Первый параметр — диапазон внешней таблицы A2:E, взятый с листа «Каталог». Второй — условие: значение в столбце A должно быть равно 1 (самые приоритетные позиции). Третий — подробнее:

• IMPORTRANGE подгружает столбец B из листа «Каталог».
• MATCH ищет совпадения между импортируемыми и имеющимися данными (между названиями товаров в скопированном и оригинальном прайс-листах).
• ISERROR вернет FALSE, когда MATCH найдет совпадения, и, соответственно, вернет TRUE, если таких совпадений не будет.

Получается, FILTER выдаст только те позиции с приоритетом №1, которые есть во внешнем документе, но которых нет в этой таблице.

Еще несколько моментов:

• FILTER фильтрует или строки, или столбцы. Чтобы фильтровать их одновременно, можно вложить одну функцию в другую — то есть одна FILTER будет обрабатывать выходные данные из другой FILTER.
• Не очень удобно постоянно копировать и вставлять заголовки из одной таблицы в другую. Но благодаря массиву можно подгружать их автоматически и в правильном порядке. Немного усовершенствованный предыдущий пример: =<‘Запросы’!A1:G1;FILTER(‘Запросы’!A2:G;’Запросы’!B2:B>50;’Запросы’!G2:G>50)>

БДСУММ(DSUM), БСЧЁТА(DCOUNTA), БИЗВЛЕЧЬ(DGET), ДСРЗНАЧ (DAVERAGE)…

Функции БД — серьезные инструменты, когда нужно работать с большим количеством данных и условий, — и стандартные FILTER, СУММЕСЛИ, СРЗНАЧЕСЛИ, ВПР и другие не справляются или не очень удобны в использовании.

К примеру, есть подробная база публикаций в соцсетях с указанием тематик и типов контента, названиями и датами, количеством лайков, комментариев и шеров. И интересно узнать, какая в среднем вовлеченность у постов с видео по сравнению с более текстовыми форматами.

Здесь лучше всего подойдет ДСРЗНАЧ (DAVERAGE). Синтаксис у этой и остальных Д-функций похожий:

1. Массив или диапазон данных — в общем, таблица, с которой нужно работать. (Первая строка обязательно должна содержать заголовки столбцов!)
2. Столбец, в котором находятся нужные данные. (Можно передать номер столбца, адрес ячейки или даже просто название столбца текстом в кавычках.)
3. Критерии, условия для фильтрации — можно передать их как массивом, так и диапазоном. (Важно: первый элемент должен соответствовать заголовку столбца с искомыми данными, что указан во втором параметре.)

Для начала на отдельном листе нужно подготовить критерии — список типов контента, по которым нужно рассчитать показатели. Затем уже использовать, немного модифицируя, такую формулу: =DAVERAGE(Book!A4:J;8;B1:B13). Она считает среднее арифметическое по всем значениям из столбца №8 диапазона Book!A4:J, которые соответствуют данным из диапазона B1:B13. (Напоминаем: в обоих диапазонах первыми строками идут заголовки. А вместо номера столбца — 8 — можно сослаться на ячейку его заголовка — Book!H4 — или просто передать название текстом — «лайки»).

Репосты и комментарии считаются аналогично, меняется только номер столбца (8→9→10). Ну а среднее число вовлечений легко получить через обычный =AVERAGE (C2:E2).

Показатели для текстовых типов контента можно получить точно так же, единственное — нужно будет снова передавать название заголовка. Писать его ниже необязательно, можно просто добавить через массив: =DAVERAGE(Book!$A$4:$J;8;<«тип контента»;$B$15:$B$38>)

Другие функции баз данных работают аналогично, разница в функциональности: так, БСЧЁТА — считает количество числовых и текстовых значений, БДСУММ — соответственно, сумму, БДПРОИЗВЕД — произведение, БИЗВЛЕЧЬ(DGET) — извлекает нужные данные из таблицы.

• Не забывать про заголовки в столбцах/массивах — именно они являются «мостиком» между данными и позволяют находить и считать нужное.
• Нет ограничений по количеству столбцов — можно задать несколько условий для фильтрации (например, не только типы контента, но и тематики). Главное — правильно написать заголовки.
• Не использовать открытый диапазон в критериях — Д-функции не будут игнорировать пустые ячейки, будут искать по ним тоже, что драматично исказит результаты.
• В БСЧЁТ и БСЧЁТА можно указать любой столбец — ведь эти функции считают общее количество, а не производят математические операции с конкретными цифрами.

QUERY

Если FILTER — просто мощная функция, то QUERY — мощнейшая. Она выполняет запросы на языке аналогичном SQL, позволяет строить самые разные отчеты и сводные таблицы, в том числе интерактивные дашборды. Вообще по QUERY стоит писать отдельный большой гайд, поэтому тут рассмотрим лишь часть возможностей.

1. Диапазон ячеек, собственно, база данных. (Можно импортировать из другой таблицы через IMPORTRANGE.)
2. Запрос, записанный на языке API визуализации Google (аналог SQL). Передается в текстовом формате — можно написать в кавычках внутри функции или взять из ячейки.
3. Заголовки — количество строк в верхней части раздела данных, необязательный параметр. (Заголовки можно присоединять и через массив).

QUERY очень чувствительна к синтаксису и порядку написания кляуз — так называют отдельные части запроса, которые отделяются между собой пробелами:

1. SELECT — указывает нужные столбцы и их порядок. Например, » SELECT A, B, D « Здесь сразу же можно создать пользовательский столбец, допустим: » SELECT A, B, C, H+I+J « Если же нужно просто вывести все столбцы, какие есть в исходном диапазоне, достаточно прописать » SELECT * « (Нюанс: если QUERY обрабатывает массив или импортируемый диапазон, нужно в SELECT указывать номер столбца (Col1), а не название (A).)

2. WHERE — задает условия для фильтрации данных. Можно написать » WHERE B > 50 AND D 0. Другой пример: » WHERE F IS NOT NULL OR G IS NULL «, который говорит: «Взять данные, где в столбце F есть какое-то значение или, наоборот, G — пустой». Для сравнения текстовых строк есть свои операторы: например, matches ищет соответствия регулярному выражению, contains — содержание в любом месте строки, starts with — в начале… Пример: » WHERE A=’Маркетинг’ AND B starts with ‘Статья’ « (Строки внутри запроса QUERY передаются в одинарных кавычках.)

В нашем подробном гайде по Google Таблицам для новичков мы затронули работу с формулами (функциями), но не остановились на ней подробнее, потому что это тема для отдельной статьи.

Формулы нужны, в первую очередь, для удобства работы с таблицами – они помогают делать автоматический подсчет различных показателей, структурировать данные, и даже переводить текст с одного языка на другой, объединять между собой разные массивы данных и так далее. При работе с большими объемами данных работать без формул просто невозможно – об этом вам расскажут на курсах интернет-маркетинга.

Здесь же мы поговорим о самых нужных для digital-специалиста формулах Гугл Таблиц.

Как вводить формулы

Есть два способа. Можно вводить формулы вручную:

1. Выберите ячейку, в которой хотите сделать формулу. Наберите = и начинайте вводить формулу:

Сервис сам предложит варианты формул

1. После того, как вы ввели формулу, заполните ее параметры. У разных формул они, соответственно, разные:

У некоторых функций нужно будет заполнить параметры и условия

1. Дальше нужно выбрать диапазон (ячейку, столбец, строку), который формула будет использовать, чтобы преобразовывать данные:

Диапазон можно выбрать, открыв скобки и щелкнув на нужную ячейку, строку или столбец. А если нужно выбрать несколько отдельно стоящих ячеек/столбцов/строк подряд, надо зажать Shift и нажимать на них

Если все сделано правильно, формула заработает. Если нет, то появится значок ошибки, при нажатии на который можно получить расшифровку и инструкцию, как исправить ошибку:

Есть десятки возможных ошибок, все можно исправить, прочитав расшифровку

Есть еще один способ ввести формулу – перейти в «Вставка» → «Функции» и выбрать формулу из списка:

Здесь представлены все возможные формулы Гугл-таблиц

Обзор важных функций для digital-специалиста

Формул и функций здесь очень много. Не будем останавливаться на простых (например, формула для суммирования), но расскажем о более сложных, но не менее нужных.

Список всех доступных функций – в справке Google.

Подсчет символов

=ДЛСТР(ячейка)

Эта формула считает количество символов в ячейке. Многие оформляют контент-планы в Google-таблицах. Учитывая то, что у большинства соцсетей есть ограничения по количеству символов в посте, эта формула очень кстати.

Работа формулы =ДЛСТР

Перевод текста из верхнего регистра в нижний (и наоборот)

=СТРОЧН(диапазон)

Формула переводит текст, написанный с использованием Caps Lock, в нижний регистр.

Работа формулы =СТРОЧН(диапазон)

=ПРОПИСН(диапазон)

Эта формула, наоборот, переводит текст в верхний регистр.

Работа формулы =ПРОПИСН(диапазон)

Эти формулы могут пригодиться во время, например, изучения аудитории конкурентов – если вы скопировали список имен и фамилий из сервиса, который парсит аккаунты, а он в верхнем регистре (что бывает), с помощью формулы можно будет легко перевести текст в нижний регистр.

Pepper Ninja, Popsters, Adinblog, Spywords…: этими сервисами стоит пользоваться каждому предпринимателю

Импорт данных из других источников

=IMPORTHTML(«ссылка»; «table»; 0; «rus_RU»)

С помощью этой формулы можно перенести любую таблицу или список в свою Google Таблицу. Для этого нужно вставить в формулу ссылку, которая ведет на страницу, где содержится контент, который нужно импортировать.

Таким образом удастся импортировать тот контент, который изначально сделан в виде таблицы или списка – например, рейтинг спортивных команд

Чтобы импортировать список и сохранить его формат в виде списка, нужно в параметрах выбрать не «table», а «list». Все остальные параметры нужно оставить, как есть.

Перевод текста

=GOOGLETRANSLATE(ячейка; «исходный_язык» или «auto»; «целевой_язык»)

Формула переводит с любого языка. И на любой язык. Большинство языков даже не нужно указывать в качестве исходного – сервис автоматически определяет язык оригинала, для чего нужно вписать команду «auto».

Трюк не сработал с чувашским языком, которого нет в сервисе Google Переводчик. Вероятно, с другими не очень популярными языками будет похожая история

Объединение ячеек

=CЦЕПИТЬ(диапазон;диапазон)

Формула позволяет объединить текст из двух ячеек в одной.

Чтобы текст не «склеился», поставьте между объединяемыми диапазонами аргумент “ “

Эта формула может пригодиться в разных ситуациях. Например, когда у вас есть список имен и номеров, которые нужно объединить в одну ячейку.

Еще эта функция пригодится для комбинирования разных ключевых фраз.

Как использовать ключевые слова в заголовках статей

Разделение фраз

=SPLIT(ячейка; «разделитель»; [тип_разделителя]; [выполнять_ли_удаление_пустых_ячеек])

То же самое, что объединение данных из ячеек, только наоборот. Если вы спарсили список контактов откуда-то, то в одной строке может оказаться несколько параметров: имя, место жительства, телефон… Формула поможет все разделить и избавиться от ненужных символов (запятые, цифры, пробелы).

Нужно указать тип разделителя, который будет удаляться, в нашем примере это “,”

Удаление лишних пробелов

=СЖПРОБЕЛЫ(ячейка)

Эта формула удаляет только лишние пробелы, оставляя нужные. Пригодится, тем, кто набирает большие объемы текста вручную.

На скриншоте можно заметить, что в некоторых местах текста было по 2-3 пробела, которые формула успешно удалила, оставив только один

Работа с графиками

=SPARKLINE(диапазон:диапазон)

Формула позволяет создавать мини-графики прямо в Таблицах. Это пригодится, если вы занимаетесь отслеживанием тех или иных показателей.

Нужно разместить все показатели горизонтально, слева направо, и в последней ячейке сгенерировать формулу

Чтобы поменять внешний вид формулы, нужно указать параметры. Например, здесь мы поменяли цвет на красный:

=SPARKLINE(диапазон:диапазон; {«charttype»»line»; «color»»red»; «linewidth»2})

Можно указать любой другой цвет: indigo, black, grey, blue, purple, pink, yellow, green, orange

Также можно менять тип диаграммы – для этого нужно менять параметр «charttype». Поменяем «line» на «bar»:

Получается гистограмма

А теперь поменяем на «column»:

Получается столбчатая диаграмма

Обо всех возможностях формулы читайте в Справке.

Работа с изображениями

=IMAGE(“ссылка”)

Формула позволяет вставить любую картинку из интернета в ячейку. Для этого не придется сохранять картинку к себе на компьютер:

Здесь мы вставили картинку с Unsplash

С помощью параметров можно менять размер и ориентацию картинки, чтобы она вписывалась в ячейку так, как нужно вам.

Обо всех возможностях этой формулы читайте в этом разделе Справки.

17 фишек Excel, с которыми будет проще запустить рекламу