15 формул Google Таблиц для контекстной рекламы

Здравствуйте!

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

На помощь приходят Google Таблицы. Мы собрали 15 формул и показали их работу на практических примерах.

СЖПРОБЕЛЫ — удаляем пробелы в начале и конце ячейки

После сбора данных из разных источников они часто оказываются непригодны для сводных таблиц и анализа из-за лишних пробелов. Функция СЖПРОБЕЛЫ быстро приводит текст в порядок.

Синтаксис:

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

или

=TRIM(ячейка)

Пример. Список ключевых слов для сайта по продаже полимеров и полимерного сырья собран из нескольких источников. После удаления лишних символов остались пробелы — их легко убрать с помощью СЖПРОБЕЛЫ.

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

ПОДСТАВИТЬ — заменяем буквы и символы в ячейках

После выгрузки из Wordstat слова часто содержат модификаторы «+». Функция ПОДСТАВИТЬ позволяет быстро удалить или добавить их перед загрузкой в Google Ads.

Синтаксис:

=ПОДСТАВИТЬ("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])

или

=SUBSTITUTE("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])

Пример. Ключевые слова выгружены из Яндекс.Директа и требуют очистки от знака «+» перед импортом в Google Ads.

Во втором столбце применяем =ПОДСТАВИТЬ, указывая ячейку, символ «+» и пустую строку для замены. Протягиваем формулу, копируем и вставляем только значения.

ВПР — сравниваем значения двух диапазонов и находим несоответствия

Функция ВПР помогает быстро выявить различия между двумя списками ключевых слов или объединить данные из разных отчётов.

Синтаксис:

=ВПР(запрос; диапазон поиска; индекс (столбец по счёту из выделенного диапазона); [сортировка])

или

=VLOOKUP(запрос; диапазон поиска; индекс (столбец по счёту из выделенного диапазона); [сортировка])

Пример 1. Сравниваем ключевые слова из Google Ads (столбец А) и Яндекс.Директа (столбец Е), чтобы найти отсутствующие в Директе.

В скобках функции указываем ячейку для сравнения, диапазон, номер столбца и значение 0 (ЛОЖЬ) для точного совпадения. Слова со значением «H/Д» — это упущенные ключи.

Пример 2. Нужно объединить статистику кампаний из Яндекс.Директа с данными по конверсиям из Google Analytics.

Копируем данные конверсий на лист со статистикой Директа, добавляем колонку и применяем ВПР с точным совпадением (0). После протягивания вставляем результат только значениями.

СТРОЧН — переводим буквы в нижний регистр

Функция приводит весь текст в ячейке к строчным буквам.

Синтаксис:

=СТРОЧН(ячейка)

или

=LOWER(ячейка)

Пример. Семантическое ядро собрано в разном регистре. Применяем СТРОЧН во втором столбце и вставляем результат только значениями.

ЗАМЕНИТЬ — делаем из ключевого слова заголовок объявления

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

Синтаксис:

=ЗАМЕНИТЬ("стар_текст";начальная_позиция;число_знаков;"нов_текст")

или

=REPLACE("стар_текст";начальная_позиция;число_знаков;"нов_текст")

Пример. Превращаем список ключевых слов в заголовки, начинающиеся с большой буквы, с помощью комбинации ЗАМЕНИТЬ, ЛЕВСИМВ и ПРОПНАЧ.

ДЛСТР — определяем количество символов в ячейке

Функция показывает длину текста — полезно при проверке лимитов заголовков и текстов объявлений.

Синтаксис:

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

или

=LEN(ячейка)

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

ЕСЛИОШИБКА — находим ключевики в строках для разбивки на группы

Функция проверяет формулу на ошибки и возвращает заданное значение при их наличии.

Синтаксис:

=ЕСЛИОШИБКА(формула;"значение в случае ошибки")

или

=IFERROR(формула;"значение в случае ошибки")

Пример. Разбиваем список фраз на группы с помощью IFERROR и после вставки значений сортируем по названиям групп.

SPLIT — разбиваем ключевые фразы на составные части и ищем минус-слова

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

Синтаксис:

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

Пример. Маски слов из Wordstat нужно разбить, чтобы быстро найти минус-слова. Применяем SPLIT с разделителем-пробелом и удаляем дубликаты.

СЦЕПИТЬ — объединяем текст в ячейках и генерируем UTM-метки

Функция соединяет несколько текстовых элементов в одну строку.

Синтаксис:

=СЦЕПИТЬ("текст1";"текст2";…)

или

=CONCATENATE("текст1";"текст2";…)

Пример. Создаём готовые ссылки с UTM-метками, объединяя URL, параметры и транслит ключевых слов.

REGEXEXTRACT — извлекаем нужный текст из ячеек

Формула извлекает часть текста, соответствующую регулярному выражению.

Синтаксис:

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

GOOGLETRANSLATE — переводим ключевики и другие данные

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

Синтаксис:

GOOGLETRANSLATE(текст; "язык_оригинала"; "язык_перевода")

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

IMPORTRANGE — импортируем данные из других таблиц

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

Синтаксис:

=IMPORTRANGE("ссылка на документ";"ссылка на диапазон данных")

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

IMPORTXML — проверяем заполненность title и h1 на целевых страницах

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

Синтаксис:

=IMPORTXML("URL";"XPath-запрос")

Пример. Проверяем наличие title с помощью XPath-запроса "//title". Функция также позволяет собирать h1–h6, description, keywords и ссылки на соцсети.

СУММЕСЛИ — находим сумму содержимого ячеек по условию

Функция суммирует значения, соответствующие заданному критерию.

Синтаксис:

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

Пример. Быстро считаем сумму регистраций по поисковым кампаниям и мобильным устройствам из отчёта Google Analytics.

ТРАНСП — меняем местами строки и столбцы

Функция транспонирует массив данных.

Синтаксис:

=ТРАНСП(массив_или_диапазон)

или

=TRANSPOSE(массив_или_диапазон)

Пример. Преобразуем строки сводной таблицы в столбцы для удобного анализа.