Введение в 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 будет выглядеть так:

=IF(C3>100000;”YES”;”NO”).

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

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

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

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

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

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

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

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

=LEN(B3).

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

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

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

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

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

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

=SPLIT(B3;” “).

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

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

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

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

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

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

=CONCATENATE(B3;”_”;C3).

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

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

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

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

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

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

=COUNTIF(E3:E12; “<2”).

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

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

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

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

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

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

=LOWER(B3),

=UPPER(G3),

=PROPER(H3).

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

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

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

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

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

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

=UNIQUE(B3:B12).

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

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

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

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

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

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

=SUMIF(C3:C12;”>100000″).

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

=SUMIF(F3:F12;”<=50″;C3:C12).

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(). Обратите внимание: функция не имеет параметров и не ссылается на ячейки или ряды.

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

=TODAY()-14;

=TODAY()+7.

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

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

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

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

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

=IFERROR(SEARCH(“Google”;B3);” “).

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

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

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

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

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

=SORT(B3:B12;E3:E12;FALSE).

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

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

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

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

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

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

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

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

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/AAAAAAAAAA”, “Workplace!A1:C100”).

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

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

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

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

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

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

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

=VLOOKUP(G3;$B$3:$F$12;5;FALSE).

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

В таком случае все ссылки будут автоматически скорректированы. Это удобно для выбора требуемого значения. Однако массив для поиска должен быть постоянным. Обратите внимание: чтобы сохранить его в таком виде мы обозначили названия столбцов и номера строк знаком доллара. Чтобы сделать это, наведите по адресу массива в формуле и нажмите кнопку 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. Чтобы это сделать для всего массива, необязательно повторять одну формулу много раз. Можно использовать следующую функцию:

=ARRAYFORMULA(C3:C12*D3:D12).

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

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

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

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

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

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

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?)(?:[^@\n][email protected])?(?:www\.)?([^: \/\n]+)”)),””).

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

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

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

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

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

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

=IMPORTXMLA2,”//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?

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