11 формул в Google Таблицах для SEO

Перевод статьи Джеки Чу из блога Search Engine Land

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

Управление ключевыми словами

VLOOKUP (ВПР): объединение наборов данных

=ВПР(запрос; диапазон_поиска; номер_столбца_с_возвращаемыми_значениями; [отсортированы_ли_данные в_первом_столбце_диапазона_поиска])

ВПР позволяет объединять по общим значениям два набора данных. Название формулы VLOOKUP (ВПР) означает «vertical lookup», то есть «вертикальный просмотр» или «вертикальный поиск». Эта формула Google Таблиц — одна из тех, которую стоит изучить в первую очередь. Можно сказать, что это примитивная версия оператора JOIN из языка SQL.

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

Последний параметр формулы (в квадратных скобках) принимает значения ИСТИНА или ЛОЖЬ. При помощи этого параметра можно указать, какой тип сопоставления значений нужен:

• ИСТИНА — неполное совпадение;

• ЛОЖЬ — только полные совпадения.

Совет: зафиксируйте диапазон поиска, используя символ $ ($E$4:$E$6 в примере выше), чтобы растянуть одну и ту же формулу на несколько строк.

О формуле ВПР в Google Справке

CONCATENATE (CЦЕПИТЬ): объединение строк и символов

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

СЦЕПИТЬ может применяться для разных задач, включая создание списков ключевых слов (объединение двух и более переменных), создание строк запроса или для массовой шаблонизации метаданных.

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

Если вам нужен пробел между ключевыми словами (скорее всего, он нужен, потому что иначе все слова склеятся в одно), то его нужно добавлять вручную — в качестве аргумента " ". Например:

О формуле «СЦЕПИТЬ» в Google Справке

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

=ВЫРОВНЯТЬ(диапазон:диапазон)

После того, как вы объедините несколько тысяч ключевых слов с помощью формулы «СЦЕПИТЬ», вам, вероятно, нужно загрузить эти ключевые слова в свой инструмент для отслеживания позиций — через пользовательский интерфейс или CSV-файл.

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

С помощью функции «ВЫРОВНЯТЬ» вы выбираете диапазон с нужными данными и в результате получаете один столбец, в котором собраны все ключевые слова, благодаря чему их очень легко куда-нибудь скопировать.

О формуле «ВЫРОВНЯТЬ» в Google Справке

LOWER (СТРОЧН): перевод в нижний регистр

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

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

Есть и противоположная по смыслу формула — ПРОПИСН, которая пригодится, если вам нужно автоматически сделать все буквы заглавными.

О формуле «СТРОЧН» в Google Справке

COUNTIF (СЧЁТЕСЛИ): подсчёт ячеек по определённому условию

=СЧЁТЕСЛИ(диапазон; "текст_условия_или_функция")

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

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

Её также можно использовать для сопоставления значений, например, подсчитывать те ключевые слова, у которых CPC > $10,00 или частота > 100.

О формуле «СЧЁТЕСЛИ» в Google Справке

SUMIF (СУМЕСЛИ): суммирование данных по определенному условию

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

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

О формуле «СУМЕСЛИ» в Google справке

Перевод: GOOGLETRANSLATE

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

• "исходный язык" — это либо двухбуквенный код языка оригинала (например, RU), либо "auto" — в этом случае Google будет пытаться определить язык автоматически;

• "целевой язык" — это двухбуквенный код языка перевода, например, ES для перевода на испанский язык.

Google не поддерживает перевод абсолютно на все языки, например, на канадский французский. Но поддерживает такие языки, как европейский португальский (PT‑PT) и бразильский португальский (PT‑BR), а также китайские языки, включая традиционный китайский (ZH‑TW) и упрощенный китайский (ZH‑CN).

О формуле GOOGLETRANSLATE в Google Справке

Управление контентом и URL‑адресами

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

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

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

Хотя на панели инструментов в меню «Данные» есть функция «Разделить текст на столбцы», вы также можете разделить текст, разграниченный определённым символом, словом или даже пробелами, на отдельные столбцы с помощью функции SPLIT.

О формуле SPLIT в Google Справке

LEN (ДЛСТР): подсчёт символов

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

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

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

О формуле «ДЛСТР» в Google Справке

REGEXREPLACE: «очистка» адресов

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

Регулярные выражения — мощный инструмент интеллектуального анализа данных при работе с крупными сайтами.

REGEXREPLACE используют при очистке или обрезке URL‑адресов на рабочем листе, когда нужно получить только путь без доменного имени или управлять редиректами.

О формуле REGEXREPLACE в Google Справке

Создание дашбордов: SPARKLINE

=SPARKLINE(B3:G3)

Функция SPARKLINE позволяет создавать простые дашборды в Google Таблицах. Многие типы данных, связанные с SEO и сайтами, отлично смотрятся в виде временных рядов, и в Google Таблицах можно легко создавать их.

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

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

Линейные диаграммы (временные ряды)

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

У этой функции есть параметры: "linewidth" (толщина линии), "charttype" (тип диаграммы) и "color" (цвет). Их можно не указывать, формула будет работать. Но лучше использовать во временных рядах линии немного увеличенной толщины и контрастный цвет, привлекающий внимание к диаграмме.

=SPARKLINE(B3:G3; {"charttype""line"; "color""indigo"; "linewidth"2}) — вариант вызова этой функции с цветом индиго и более жирной линией.

Столбчатые диаграммы и гистограммы

Чтобы сделать такой тип диаграммы, просто измените тип на "column" для создания столбчатой диаграммы, как показано ниже:

А чтобы сделать гистограмму, поменяйте тип на "bar":

О формуле SPARKLINE в Google Справке

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

Интересное по теме:

Отслеживаем влияние апдейтов Google на метрики сайта с помощью Google Data Studio

57 бесплатных инструментов для SEO‑специалиста