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

Талмуд по формулам в 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).

Рисунок 8

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

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

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

Формулы

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

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

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

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

Рисунок 10

Прогрессия.

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

Рисунок 11

Округление.

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

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

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

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

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

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

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

Рисунок 17

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

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

Рисунок 18

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

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

Рисунок 19

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

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

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

Рисунок 20

Рисунок 20.1

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

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

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

• Описание: суммирование ячеек с условием ЕСЛИ (формула 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
Итого:

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

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

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

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

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

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

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

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

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

Рисунок 27.1.

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

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

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

Рисунок 28.1

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

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

Рисунок 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 Sheets, о которых должен знать каждый SEO-специалист

Какими инструментами воспользоваться для анализа эффективности поисковой оптимизации? Большинство подобных сервисов платные, причем подписка на них стоит недешево. Но у вас остается бесплатный вариант с широким набором функций. Зная основные формулы Google Sheets для SEO, вы сможете создавать собственные базы данных, формы для анализа и отчеты. Итак, разбираемся в полезных функциях облачного сервиса.

Введение в Google Sheets для SEO

Онлайн-приложение очень похоже на Microsoft Excel. Оно имеет схожие интерфейс, средства редактирования и форматирования, а также поддержку формул и скриптов. Если вы уже работали в Excel, вам нужно только привыкнуть к другому синтаксису. А новичкам, которые впервые будут иметь дело с электронными таблицами, следует ознакомиться с базовыми понятиями:

• Ячейка — единица содержимого информации, поле, в которое вводятся данные.
• Строка — горизонтальный массив ячеек.
• Столбик — вертикальный массив ячеек.
• Таблица — прямоугольный массив, содержащий определенное количество столбиков и строк.
• Функция — операция с данными в ячейках, призванная дать конкретный результат.
• Ряд — часть таблицы, к которой обращается функция.
• Формула — комбинация из функций и рядов, на которые они ссылаются. Как в Microsoft Excel, так и в Google Sheets, формулы начинаются со знака равенства “=”.

Чтобы сделать эти понятия нагляднее, приведем простой пример:

Базовые формулы Google Sheets для SEO

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

1. IF для проверки соблюдения условий

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

Синтаксис: =IF(condition, value_if_true, value_if_false), где

• condition — условие, выраженное в форме математической функции;
• value_if_true — значение, если данные в ячейке соответствуют условию;
• value_if_false — значение, если условие не соблюдено.

К примеру, мы хотим проверить, превышает ли месячный объем органического трафика 100 тысяч запросов. Наша SEO-формула в Google Sheets будет выглядеть так:

В элементе condition могут использоваться разные математические операторы:

• точно равно (=);
• меньше ( <);
• больше (>);
• меньше или равно ( <=);
• больше или равно (>=);
• не равно (<>).

Обратите внимание также на синтаксис написания элементов value_if. Чтобы вывести в ячейке текст, обязательно берите его в кавычки. Если этого не сделать, Google Sheets будет воспринимать значение как формулу. Простой текст в таком случае будет распознан как ошибочная функция.

2. LEN, чтобы подсчитать количество знаков в ячейке

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

Синтаксис: =LEN(insertcell), где

• insertcell — ссылка на ячейку.

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

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

3. SPLIT, чтобы разделить информацию на несколько ячеек

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

Синтаксис: =SPLIT(Text, Delimiter), где

• Text — ссылка на ячейку, содержащую нужный текст;
• Delimiter — разделительный знак.

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

Обратите внимание: знак, по которому происходит разделение текста, обязательно берется в кавычки. После выполнения функции формулы останутся только в крайних левых ячейках массива — остальное будет записано текстом. Однако при изменении данных они обновятся автоматически.

4. CONCATENATE для объединения данных

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

Синтаксис: =CONCATENATE(range1;range2;…), где

• range — ссылка на ячейки или ряды данных. Вместо нее можно добавлять собственный текст, беря его в кавычки.

К примеру, мы хотим объединить поисковый запрос и объем органического трафика, разделив их знаком подчеркивания «_». Формула будет выглядеть так:

Полезный совет: эта формула идеально подходит для слияния адреса страницы с именем домена и префиксом HTTP или HTTPS. Она значительно упрощает построение URL из фрагментов, выгруженных некоторыми SEO-сервисами.

5. COUNTIF для подсчета количества ячеек, соответствующих определенным условиям

Принцип действия: в отличие от IF, проверяет соблюдение условия не в отдельной ячейке, а в ряду. Выдает количество ячеек, в котором она придерживается.

Синтаксис: =COUNTIF(range, criteria), где

• range — ссылка на ряд данных;
• criteria — условие, выраженное математической функцией.

К примеру, мы хотим подсчитать количество ключей со стоимостью клика в контекстной рекламе до 2 долларов. Формула будет выглядеть так:

Не забывайте брать условие в кавычки. Также помните, что можно использовать все математические операторы равенства или неравенства.

6. UPPER/LOWER/PROPER для выбора нужного регистра

Принцип действия: изменяет регистр на верхний, нижний или верхний в первой букве, нижний — в других.

Синтаксис: =UPPER(text), =LOWER(text), =PROPER(text), где

• text — ссылка на ячейку или введенный вручную фрагмент текста.

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

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

7. UNIQUE для поиска и удаления дубликатов

Принцип действия: просматривает столбик — если в нем есть ячейки с повторяющимися данными, выводит их только один раз.

Синтаксис: =UNIQUE(range), где

• range — просматриваемый ряд данных.

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

Полезный совет: функцию UNIQUE также можно использовать для удаления пустых ячеек. В таком случае вы можете даже давать ссылку на столбик. SEO-формула Google Sheets будет выглядеть так: = UNIQUE(B:B).

8. SUMIF, чтобы добавить значения ячеек, соответствующих определенным критериям

Принцип действия: проверяет, соблюдается ли условие в этой ячейке или в другом параллельном ряду данных. Если да, добавляет ее в общую сумму, если нет — опускает.

Синтаксис: =SUMIF(range, criterion, [sum_range]), где

• range — ряд, в котором проверяется соответствие условию;
• criterion — условие, заданное математической функцией;
• [sum_range] — ряд добавления. Необязательный параметр — вводится только в том случае, если на соответствие условию проверяется параллельный ряд.

К примеру, нам нужно проверить, сколько органического трафика дают высокочастотные ключи, имеющие более 100 тысяч просмотров. Формула будет выглядеть так:

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

9. IFERROR, чтобы заменить ошибки

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

Синтаксис: =IFERROR(original_formula, value_if_error), где

• original_formula — формула, которую вы проверяете на наличие ошибок;
• value_if_error — действие, которое необходимо выполнить при обнаружении ошибки.

Например, CTR для некоторых ключей недоступен из-за ограниченного функционала программы или отсутствия данных в поисковике. Вместо цифры в этих ячейках проставлены риски. Попытавшись произвести расчет, мы получим сообщение об ошибке. Чтобы заменить его на текст «NO DATA», понадобится следующая формула:

=IFERROR(C3*D3; “NO DATA”).

На скриншоте в ячейке G8 можно увидеть сообщение об ошибке. Формула IFERROR может также работать с ошибками типа #DIV/0! и #ERROR!

10. TODAY, чтобы добавить текущую дату

Принцип действия: выводит текущую дату в формате, установленном облачным сервисом Google Sheets по умолчанию.

Синтаксис =TODAY(). Обратите внимание: функция не имеет параметров и не ссылается на ячейки или ряды.

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

11. SEARCH для поиска текстового фрагмента в ячейке

Принцип действия: проверяет, содержится ли заданный текстовый фрагмент в определенной ячейке. Если да, возвращает номер знака, с которого он начинается.

Синтаксис: =SEARCH(search_query, text_to_search), где

• search_query — фрагмент текста, который нужно найти, или ссылка на ячейку, в которой он содержится;
• text_to_search — ссылка на ячейку, в которой производится поиск.

Эта функция может использоваться для проверки ячеек на содержимое определенного текста. Например, нам нужно найти ключевую фразу, в которой содержится слово «Google». Чтобы избавиться от сообщений об ошибках, следует добавить рассмотренную выше формулу IFERROR. Конечный результат будет выглядеть так:

12. SORT, чтобы быстро сортировать ячейки

Принцип действия: сортирует ячейки определенного ряда по значениям, содержащимся в этом же или параллельном ряду.

Синтаксис: =SORT(range, sort_column, is_ascending), где

• range — сортируемый ряд;
• sort_column — ряд с признаком, по которому осуществляется сортировка. Может совпадать с предыдущим;
• is_ascending — способ сортировки. Если она выполняется по увеличению, указывайте TRUE, по убыванию — FALSE.

К примеру, нам нужно отсортировать ключи по стоимости клика в контекстной рекламе. Формула для сортировки по уменьшению будет выглядеть так:

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

SEO-формулы Google Sheets для профессионалов

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

1. IMPORTRANGE для быстрого импорта данных из другой таблицы

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

Синтаксис: =IMPORTRANGE(spreadsheet_url, range_string), где

• spreadsheet_url — ссылка на нужный файл;
• range_string — адресация ряда данных, содержащая название страницы и массива ячеек.

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

Обратите внимание на несколько нюансов:

• URL и адрес массива обязательно берутся в кавычки;
• название страницы отделяется восклицательным знаком;
• Если необходимо импортировать несколько столбцов полностью, указывайте только их название без номеров. К примеру, “Workplace!A:C”.

2. VLOOKUP для удобного подбора данных

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

Синтаксис: =VLOOKUP(search_key, range, index, is_sorted), где

• search_key — ссылка на ячейку, содержащую нужное значение;
• range — массив данных, в котором выполняется поиск. Обратите внимание: формула ищет значение в крайнем левом столбце массива;
• index — номер столбца, из которого будет подтягиваться информация;
• is_sorted — тип просмотра. Рекомендуем всегда использовать точный подбор, который задается параметрами «FALSE» или «0». Если необходимо примерное соответствие, можно установить «TRUE».

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

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

В таком случае все ссылки будут автоматически скорректированы. Это удобно для выбора требуемого значения. Однако массив для поиска должен быть постоянным. Обратите внимание: чтобы сохранить его в таком виде мы обозначили названия столбцов и номера строк знаком доллара. Чтобы сделать это, наведите по адресу массива в формуле и нажмите кнопку F4 на клавиатуре.

3. QUERY для интеграции SQL-запроса

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

Синтаксис: =QUERY(range, sql_query), где

• range — ряд, в котором осуществляется проверка;
• sql_query — код запроса.

Например, у нас есть массив URL с разным типом контента. Нас интересуют сообщения для блога, помеченные как «Blog Post».

Чтобы отобрать только нужные нам URL, воспользуемся такой формулой с SQL-запросом:

=QUERY(DATA!A:B,”select A where B = ‘Blog Post’”).

4. ARRAYFORMULA, чтобы применить одну формулу ко многим ячейкам

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

Синтаксис: =ARRAYFORMULA(array_formula), где

• array_formula — формула, которую нужно применить к массиву.

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

Обратите внимание: на этот раз в формуле указаны не ячейки, а ряды данных, которые обрабатываются функцией.

5. REGEXEXTRACT, чтобы выбрать нужные фрагменты текста

Принцип действия: применяет регулярные выражения к массивам данных — так же, как при автозамене в текстовом редакторе или программировании.

Синтаксис: =REGEXEXTRACT(text, regular_expression), где

• text — ссылка на ячейку;
• regular_expression — выражение, которое необходимо применить.

К примеру, у нас есть массив URL, из которых мы хотим вытащить адрес базового домена. Для этого подходит регулярное выражение с синтаксисом «^(?:https?:\/\/)?(?:[^@\n][email protected])?(?:www\.)?([^:\/\n ]+)». Чтобы избежать ошибок, применим функцию IFERROR. Конечная SEO-формула для Google Sheets будет выглядеть так:

Полезный совет: если вы незнакомы с регулярными выражениями, воспользуйтесь ресурсом RegexR, где подробно описан их синтаксис.

6. IMPORTXML для краулинга веб-сайтов

Принцип действия: функция переходит по ссылкам и использует язык запросов XPath для получения требуемой информации.

Синтаксис: =IMPORTXML(url, xpath_query), где

• url — адрес ячейки, содержащий ссылку;
• xpath_query — запрос XPath.

К примеру, мы хотим использовать XML-формулу Google Sheets для SEO-рангов страниц, вытащив из них метатеги. Чтобы получить Title, потребуется следующий запрос:

Полезный совет: рекомендуем учебник для изучения синтаксиса XPath. Вы также можете получить необходимые запросы в режиме разработчика Google Chrome. Для этого нужно нажать правой кнопкой мыши на нужный вам элемент и выбрать такое действие:

Формулы Google Sheets — мощный инструмент для бесплатной SEO-аналитики

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

Часто задаваемые вопросы

Какие базовые SEO-формулы доступны в Google Sheets?

Очень полезные функции — COUNTIF и SUMIF для подсчета и добавления по условиям, IF — для проверки соблюдения условия, SPLIT — для разделения ячеек, CONCATENATE — для слияния.

Можно ли использовать SEO-формулы Microsoft Excel в Google Sheets?

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

Как создать SEO-формулу в Google Sheets?

Выбрать ячейку, ввести знак «=», затем необходимую функцию и ее параметры. Для ссылки на ячейки или ряды следует ввести адреса в соответствующей части формулы.

Как скопировать формулу в Google таблицах на весь столбец?

По логике структуры, во всех вариантах отработки формул — они расположены в столбцах друг под другом. На практике постоянно нужно копировать формулы в соседние ячейки, другие строки или протягивать их на весь столбец.
Рассмотрим 7 вариантов протянуть формулу на весь столбец в Google таблицах:

Авто-протягивание формул на весь столбец в Google таблицах

Данная встроенная функция гугл таблиц активирована по умолчанию, но, если вдруг, у вас она отключена:

1. В меню Google таблиц кликаем на пункт «Инструменты».
2. Наводим курсор мыши на «Автозаполнение».
3. В модальном меню кликаем на подпункт «Включить автозаполнение».
4. После активации рядом с функцией появится галочка.

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

На примере 2 столбца с данными, которые нужно просуммировать между собой. Введена формула суммы. После нажатия на Enter, таблица предложит распространить формулу на весь столбец до конца диапазона суммируемых данных. Остается согласиться или отказаться.

Горячие клавиши — как применить формулу ко всему столбцу в Google таблицах?

Сочетание клавиш: Ctrl+пробел+Enter

Второй способ растянуть формулу на весь столбец — комбинация горячих клавиш: Ctrl+пробел+Enter. Обращаю ваше внимание, что данный вариант применить формулу ко всему столбцу будет работать в том случае, если сверху над формулой не будет объединенных ячеек.

Сочетание клавиш: Ctrl+C и Ctrl+V (копировать — вставить)

1. Вводим в ячейку формулу.
2. Выбираем ячейку с введенной формулой и прожимаем комбинацию клавиш: Ctrl+C (копировать).
3. Не кликая по соседним ячейкам, зажимаем клавишу Shift.
4. Переводим курсор мыши с зажатой клавишей Shift в конец диапазона (задаем нужный нам диапазон, в котором будет протянута формула).
5. Кликаем на ячейку конца диапазона левой кнопкой мыши — будет подсвечен весь диапазон.
6. Прожимаем комбинацию клавиш: Ctrl+V (вставить).

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

Обратите внимание, Google таблицы, перед тем как вы скопируете формулы, даст возможность предварительно выбрать тип копирования:

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

Автокопирование формулы через двойной клик на маркер ячейки

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

Правила автозаполнения (как и при активации встроенной функции Google таблиц) копируют формулы до первой незаполненной ячейки в соседнем диапазоне (столбце).

Протягивание формул на весь столбец через механическую протяжку за маркер ячейки

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

1. Вводим формулу в ячейку.
2. Наводим курсор мыши на маркер ячейки (черный квадрат в правом нижнем углу ячейки).
3. Зажимаем левую кнопку мыши и тянем за маркер формулу до конца нужного нам диапазона с данными.
4. Отпускаем кнопку — формулы скопировались.

Копирование формул на весь столбец при помощи массива

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

Копирование формул на весь столбец при помощи SQL запроса функции QUERY

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

Оба эти варианта отрабатывают логику (на примере сумма и умножение) даже если на пути встретятся пустые ячейки в столбцах.

7 вариантов копирования формул на весь столбец в Google таблицах

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

Автор: Александр Солунин

Сертифицированный специалист по Google таблицам — Обучение | Курсы | Консультации Показать все статьи автора: Александр Солунин →

Прощайте, кракозябры: работаем с формулами в google docs и другие полезные сервисы

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

Формулы в Гугл Докс

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

Чтобы не прописывать все это вручную, делаем так:

1. Открываем меню «Вставка»
2. Выбираем пункт «Формула»
3. Нажимаем пункт «Новая формула» и начинаем писать.

Меню предложит вам целый ряд символов, из которых вы просто выбираете нужный вам. При выборе символ тут же отображается в тексте.

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

Прочие полезные инструменты

В рамках сервиса Google Docs есть и другие полезные инструменты, которые могут вас заинтересовать. Все они расположены в Инструментах в меню.

Опишем их для вас более подробно:

Статистика. Отличный инструмент для копирайтеров. Здесь можно просмотреть основные данные: количество страниц документа, символов, слов. Это меню можно вызвать не только через панель инструментов, но и горячими клавишами: Ctrl+Shift+C

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

Голосовой ввод. Просто невероятно удобный инструмент, который позволит на время дать отдохнуть вашим глазкам и ручкам. Конечно, для мега-сложной информации этот сервис не подойдет, но для несложной диктовки вполне сгодится. Работу осуществляет специальный робот, который распознает русскую речь на слух. Он также легко распознает команды «точка», «запятая», «абзац», «новая строка», «вопросительный/восклицательный знак». Можно запускать при помощи панели инструментов или путем нажатия горячих клавиш: Ctrl+Shift+S

Когда видите красный значок с микрофоном – это значит, что идет запись. Хотите остановиться и передохнуть – достаточно просто нажать на этот знак.

Как проставить сноски, колонтитулы, номера страниц и оглавление

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

Для добавления номера страницы зайдите в меню «Вставки» и выберите нужное расположение. Программа по умолчанию проставляет номер страницы в нижнем правом углу, но его легко можно перенести в нужное место вручную – просто нажмите и перетяните в нужное место.

Для сноски выберите опять же меню «Вставка» и найдите там пункт «Сноска» (или комбинация горячих клавиш Ctrl+Shift+F). У слова, которое вы хотите пояснить, появится порядковый номер, а внизу страницы под этим номером просто напечатайте нужный комментарий. Для удаления сноски нужно удалить номер у поясняемого слова, а не комментарий внизу страницы.

Оглавление, как правило, создается в виде нумерованного списка, каждый пункт которого представлен в виде активной ссылки на содержимое.

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

Ну а если работа с этим сервисом для вас слишком сложна, или у вас просто физически не хватает времени и сил, обращайтесь к нам – наши авторы выполнят все быстро, дешево и качественно!