15 формул для Google таблиц, полезные SEO специалисту

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

В этой статье — 15 полезных функций Google Таблиц, а также примеры их применения в работе.

• ПРОСМОТРX (XLOOKUP) — продвинутая версия ВПР (VLOOKUP)
• СЦЕПИТЬ (CONCATENATE) — объединение ячеек
• ВЫРОВНЯТЬ (FLATTEN) — делает из таблицы один столбец
• СЧЁТЕСЛИ (COUNTIF) — считает ячейки по условию
• СУМЕСЛИ (SUMIF) — складывает числа по условию
• SPLIT — разделение текста на части
• ДЛСТР (LEN) — длина строки
• TODAY — добавляет текущую дату
• GOOGLETRANSLATE — перевод текста
• REGEXEXTRACT и REGEXREPLACE — работа с регулярными выражениями
• IMPORTXML — импорт данных со страниц
• UNIQUE — удаление дубликатов
• SPARKLINE — мини-графики в ячейках
• Резюме

ПРОСМОТРX (XLOOKUP) — продвинутая версия ВПР (VLOOKUP)

Функция ВПР (VLOOKUP) долгое время была стандартом для поиска данных в таблицах. Умение пользоваться ВПР считалось чуть ли не обязательным для SEO-специалиста. Некоторые работодатели даже проверяли этот навык на собеседованиях. Но теперь есть и более мощная функция — ПРОСМОТРХ (XLOOKUP). Разберем, что умеют обе формулы и чем они отличаются.

ВПР (VLOOKUP) — это функция, которая ищет значение в первом столбце указанного диапазона и возвращает соответствующее значение из столбцов справа. По умолчанию ВПР ищет примерное совпадение, что иногда приводит к ошибкам, если вы этого не ожидаете.

Синтаксис функции:

=ВПР(значение_поиска; диапазон; индекс; [точное_соответствие])

Пример использования:

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

Нам нужно найти позицию ключевого слова «Контент-маркетинг». Формула ВПР будет выглядеть так:

=ВПР("Контент-маркетинг"; A2:B4; 2; ЛОЖЬ)

Здесь:

Результат: Формула вернула значение «7», так как «Контент-маркетинг» находится на 7-й позиции.

Подробнее о функции VLOOKUP в Cправке Google Документов.

ПРОСМОТРX (XLOOKUP) — это улучшенная версия VLOOKUP — она может искать как по вертикали, так и по горизонтали.

Синтаксис:

=ПРОСМОТРX(значение_поиска; диапазон_поиска; диапазон_результатов; [значение_если_не_найдено]; [режим_поиска_совпадения]; [режим_поиска])

Пример использования:

Возьмем ту же таблицу с ключевыми словами и позициями. Если мы хотим найти позицию для ключевого слова «PPC», можно использовать формулу ПРОСМОТРX:

=ПРОСМОТРX("PPC"; A2:A4; B2:B4; "Не найдено")

Здесь:

Результат: Формула вернула «1», так как ключевое слово «PPC» находится на 1-й позиции.

Подробнее о функции XLOOKUP в Cправке Google Документов

Чем XLOOKUP лучше VLOOKUP?

1. Гибкость поиска. ВПР ищет только в первом столбце таблицы. ПРОСМОТРХ может искать где угодно.

2. Устойчивость к изменениям. Если вы добавите новый столбец в таблицу, ВПР может сломаться. ПРОСМОТРХ останется работать как ни в чем не бывало.

3. Поиск в обе стороны. ВПР ищет данные только справа от столбца поиска. ПРОСМОТРХ может искать и слева, и справа. Удобно, правда?

4. Точность по умолчанию. ВПР по умолчанию ищет примерное совпадение. ПРОСМОТРХ ищет точное совпадение, если вы не скажете ему иначе.

5. Поиск сверху и снизу. ВПР всегда ищет сверху вниз. ПРОСМОТРХ может искать и снизу вверх, если нужно найти последнее совпадение.

6. Замена ошибок. С ПРОСМОТРХ легко указать, что вернуть, если данные не найдены. С ВПР для этого нужны дополнительные функции.

7. Работа со строками и столбцами. ВПР работает только со столбцами. Для строк нужна отдельная функция ГПР (HLOOKUP). ПРОСМОТРХ справляется и с тем, и с другим.

СЦЕПИТЬ (CONCATENATE) — объединение ячеек

Функция CONCATENATE объединяет содержимое нескольких ячеек в одну. Можно объединять текст, числа, символы и добавлять свои разделители — пробелы, тире или слэши.

Синтаксис:

=СЦЕПИТЬ(строка1, [строка2, ...])

Пример использования:

=СЦЕПИТЬ(A1; "//"; B1; "/"; C1;"/"; D1)

Результат: Формула объединила значения из ячеек A1, B1, C1 и D1 со слэшами между ними.

Подробнее о CONCATENATE в Google Docs Editors Help.

ВЫРОВНЯТЬ (FLATTEN) — делает из таблицы один столбец

Функция ВЫРОВНЯТЬ (FLATTEN) в Google Таблицах помогает превратить сложные таблицы в упорядоченные одномерные списки. Полезна, когда нужно быстро собрать данные из нескольких столбцов или строк в один столбец. В SEO такие задачи часто возникают при анализе ключевых слов, списков URL или других данных.

Синтаксис:

=FLATTEN(диапазон1, [диапазон2,…])

Пример использования:

 =FLATTEN(B1:E1)

Результат: Функция перенесла цифры из строк в столбец.

Больше о FLATTEN в справке Google Docs.

СЧЕТЁСЛИ (COUNTIF) — считает ячейки по условию

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют заданному условию.

Синтаксис:

=СЧЁТЕСЛИ(диапазон; критерий)

Пример:

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

=СЧЁТЕСЛИ(B2:B11; "404")

Результат: Формула посчитала количество страниц со значением «404».

Подробнее о функции СЧЁТЕСЛИ в руководстве Google Docs.

СУММЕСЛИ (SUMIF) — складывает числа по условию

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

Синтаксис:

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Пример:

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

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

=СУММЕСЛИ(A2:A7; "SEO"; B2:B7)

Здесь:

Результат: Функция суммирует трафик по всем строкам, где ключевое слово равно «SEO". В нашем случае результат будет: 150 + 180 + 170 = 500.

О формуле СУММЕСЛИ — в Справке Google Документов.

SPLIT — разделение текста на части

Функция SPLIT разделяет текст в ячейке на отдельные части по заданному разделителю. Полезна при работе с URL или с выгрузками из систем аналитики.

Синтаксис:

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

Пример использования:

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

=SPLIT(A3; "/")

Результат: Формула SPLIT разделяет URL на части по символу /. Каждая часть URL размещена в отдельной ячейке.

Подробнее о SPLIT — в справке Google Документов.

ДЛСТР (LEN) — длина строки

ДЛСТР считает количество символов в тексте. С ее помощью легко проверить, например, длину тайтлов и мета-описаний.

Синтаксис:

=ДЛСТР(текст)

Пример использования:

=ДЛСТР(A1)

Результат: Формула посчитала символы в ячейке C3.

О функции ДЛСТР — в справке Google.

TODAY — добавляет текущую дату

Функция TODAY вставляет текущую дату. Это удобно для автоматической датировки отчетов или для расчета времени с момента последнего обновления.

Синтаксис:

=TODAY() или =СЕГОДНЯ()

Пример использования:

Результат: Функция возвращает текущую дату.

О функции TODAY в Справке Google.

GOOGLETRANSLATE — перевод текста

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

Синтаксис:

=GOOGLETRANSLATE(текст; исходный_язык; язык_перевода)

Пример использования:

=GOOGLETRANSLATE(C3; "ru"; "en")

Результат: Текст в ячейке переведен с русского на английский.

О функции GOOGLETRANSLATE — в руководстве Google Docs.

REGEXEXTRACT и REGEXREPLACE — работа с регулярными выражениями

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

Функция REGEXEXTRACT позволяет вытащить определенный кусок текста из строки. 

Синтаксис:

=REGEXEXTRACT(текст; регулярное_выражение)

Пример:

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

=REGEXEXTRACT(A21; "https?://([^/]+)")

Результат: Формула вытащила домен из ячейки, где находится URL.

О функции REGEXEXTRACT справке Google Docs.

Функция REGEXREPLACE помогает заменить определенные части текста. 

Синтаксис: 

=REGEXREPLACE(текст; регулярное_выражение; замена)

Пример использования:

У нас таблице есть колонка с URL, содержащими UTM-метки. Применим эту формулу, чтобы получить «чистые» URL без лишних параметров:

=REGEXREPLACE(A2; "\?.*"; "")

Результат: Формула удалила все UTM-метки из URL, находящегося в ячейке A2.

Подробнее о REGEXREPLACE — в справке Google Docs.

IMPORTXML — импорт данных со страниц

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

Синтаксис:

=IMPORTXML(ссылка; запрос_xpath)

Пример:

Нам нужно собрать заголовки title страниц с сайта https://seo.ru. Применяем формулу: 

=IMPORTXML("https://seo.ru"; "//title")

Результат: Формула извлекла содержимое тега <title> с указанной страницы.

О функции IMPORTXML в справке Google Docs.

UNIQUE — удаление дубликатов

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

Синтаксис:

=UNIQUE(диапазон)

Пример:

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

=UNIQUE(A1:A100)

Результат: Все дубли в списке удалены.

Подробнее о UNIQUE — в справке Google Docs.

SPARKLINE — мини-графики в ячейках

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

Синтаксис:

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

Пример использования:

Нужно визуализировать изменение количества посетителей на сайте за несколько недель. Применим формулу:

=SPARKLINE(B3:B14)

Результат: Формула создала мини-график на основе данных из диапазона B3:B14.

О функции SPARKLINE в справке Google.

Резюме

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

Читайте и другие наши статьи с полезными SEO-инструментами:

• 16 расширений браузера для маркетолога и SEO-специалиста
• Подборка лучших букмарклетов для работы SEO-специалиста
• Расширения с ChatGPT для браузера: лучшие бесплатные инструменты

Подписывайтесь на наш Телеграм-канал. Там мы постим новости SEO-сферы, обновления Яндекса и Google, статьи про продвижение сайтов и дайджесты.