20 формул Google Таблиц, которые экономят часы рутины в контекстной рекламе
Работа с кампанией в Google Ads, Яндекс Директе и аналитикой почти всегда упирается в одно и то же: чистка семантики, сведение данных из разных источников, подготовка отчетов, генерация UTM-меток, проверка посадочных страниц. Большую часть этих задач можно переложить на Google Таблицы — если грамотно использовать формулы.
Ниже — практический гид по 20 функциям Google Таблиц, которые особенно полезны контекстным маркетологам. Формулы указаны в русской локали, в скобках — англоязычные аналоги.
---
1. СЖПРОБЕЛЫ / TRIM — приводим данные в аккуратный вид
Когда семантика собирается из разных источников (Wordstat, рекламные системы, CRM), в ячейках почти всегда остаются лишние пробелы: в начале, в конце, иногда — по два–три подряд внутри фразы. Из‑за этого ломаются сводные таблицы и некорректно работают формулы поиска.
Синтаксис:
```
=СЖПРОБЕЛЫ(A2)
=TRIM(A2)
```
Функция:
- убирает пробелы в начале и конце строки;
- сокращает последовательность пробелов внутри строки до одного.
Практический кейс.
Есть список ключевых слов для сайта по продаже полимеров. Фразы выгружены из разных источников, в них уже удалены лишние символы, но остались «ошметки» пробелов.
1. В соседнем столбце вводим: `=СЖПРОБЕЛЫ(A2)`.
2. Протягиваем формулу до конца списка.
3. Копируем очищенный столбец и вставляем в исходный как «Только значения».
Результат — аккуратный список ключевиков, готовый для дальнейшей обработки и загрузки в рекламные кампании.
---
2. ПОДСТАВИТЬ / SUBSTITUTE — массовая замена символов и модификаторов
Контекстнику часто нужно быстро убрать или наоборот добавить модификаторы:
- удалить «+» у ключевых слов, выгруженных из Wordstat;
- добавить «+» перед словами для широкого соответствия в Google Ads;
- заменить уточняющие символы, скобки, кавычки и т. д.
Синтаксис:
```
=ПОДСТАВИТЬ(текст; стар_текст; нов_текст; [номер_вхождения])
=SUBSTITUTE(text; old_text; new_text; [instance_num])
```
Если параметр `номер_вхождения` не указан, меняются все вхождения.
Пример.
Вы выгрузили ключевые слова из кампании в Яндекс Директе с модификатором «+» и хотите импортировать их в Google Ads уже без модификаторов.
1. В столбце B пишем:
`=ПОДСТАВИТЬ(A2; "+"; "")`
2. Протягиваем формулу на весь диапазон.
3. Копируем столбец B и вставляем в A как значения.
Так же можно:
- заменить запятые на вертикальные черты;
- удалить кавычки;
- заменить старый домен в UTM-метках на новый.
---
3. ВПР / VLOOKUP — сравнение списков и поиск несоответствий
Одна из самых частых задач — понять, какие ключи, объявления или запросы не попали из одной системы в другую: например, какие слова уже есть в Яндекс Директе, но еще не заведены в Google Ads.
Синтаксис:
```
=ВПР(значение; диапазон; номер_столбца; [сортировка])
=VLOOKUP(search_key; range; index; [is_sorted])
```
- `значение` — то, что ищем (ключевое слово, ID, URL);
- `диапазон` — где ищем (например, весь лист с выгрузкой из Google Ads);
- `номер_столбца` — порядковый номер столбца в диапазоне, откуда нужно вернуть результат;
- `сортировка` — ИСТИНА/ЛОЖЬ, чаще для маркетинга безопасней использовать ЛОЖЬ (точное совпадение).
Пример 1: проверяем, какие ключи из Google Ads не заведены в Яндекс Директе.
- В столбце A — список ключевых слов из Google Ads.
- В столбце D:E — выгрузка из Яндекс Директа (в D — ключи, в E —, например, статус).
В столбце B пишем:
```
=ЕСЛИОШИБКА(ВПР(A2; $D$2:$E$1000; 2; ЛОЖЬ); "нет в Директе")
```
Если ключ из столбца A не найден в диапазоне D:E, формула вернет текст «нет в Директе». Так вы получаете список фраз, которые стоит перенести или проанализировать.
Пример 2: подтягиваем ставки или статусы по ключам.
Аналогично можно:
- подтянуть из отдельной таблицы ставки по ключам;
- подставить статусы модерации;
- сопоставить ID кампаний и их названия.
---
4. СТРОЧН / LOWER — приводим регистр к единому виду
Часто семантика приходит в разном регистре: где‑то ВСЕ ЗАГЛАВНЫМИ, где‑то с первой заглавной буквы, где‑то вообще смешанный формат. Для нормальной работы с отчетами удобно привести все к нижнему регистру.
Синтаксис:
```
=СТРОЧН(A2)
=LOWER(A2)
```
Применяется:
- при подготовке ключевых слов к группировке и кластеризации;
- перед поиском дубликатов (чтобы «Полимер», «полимер» и «ПОЛИМЕР» воспринимались как одно и то же слово).
---
5. ЗАМЕНИТЬ / REPLACE — можно ли сделать из ключа заголовок
Если нужно на основе ключевого слова быстро сделать вариант заголовка объявления, иногда достаточно немного подредактировать фразу: удалить лишнюю приставку, заменить хвост, сократить.
Синтаксис:
```
=ЗАМЕНИТЬ(текст; поз_начала; кол_символов; новый_текст)
=REPLACE(text; position; length; new_text)
```
Простой пример: есть ключ «купить полимерное сырье оптом», а в заголовке нужно убрать слово «оптом» и подставить действие.
```
=ЗАМЕНИТЬ(A2; ПОИСК("оптом"; A2); ДЛСТР("оптом"); "")
```
Далее можно дополнительно:
- обрезать хвост, чтобы вписаться в лимит символов;
- подставить бренд или УТП;
- удалить нерелевантные слова.
---
6. ДЛСТР / LEN — контроль длины заголовков и описаний
В контекстной рекламе у каждого поля есть строгие лимиты. Удобно автоматически проверять, не превышают ли ваши заголовки и описания допустимую длину.
Синтаксис:
```
=ДЛСТР(A2)
=LEN(A2)
```
Пример.
В столбце A — заголовки объявлений для Google Ads. В столбце B:
```
=ДЛСТР(A2)
```
Дополнительно можно сделать условное форматирование:
- если `ДЛСТР(A2) > 30` — подсветить ячейку красным;
- если `> 25` — желтым, чтобы видеть «рисковые» заголовки.
---
7. ЕСЛИОШИБКА / IFERROR — чистые таблицы без ошибок и технический тегинг
Любая формула поиска (`ВПР`, `XLOOKUP`, `REGEX`, `ПОИСК`) может возвращать ошибки (`#Н/Д!`, `#ЗНАЧ!` и т. д.). В отчетах и сводных таблицах это неудобно. Для «обёртки» таких формул используется `ЕСЛИОШИБКА`.
Синтаксис:
```
=ЕСЛИОШИБКА(формула; значение_если_ошибка)
=IFERROR(formula; value_if_error)
```
Пример.
Вы ищете в строке наличие определенного ключевого слова, чтобы разбить запросы на группы.
```
=ЕСЛИОШИБКА(ПОИСК("оптом"; A2); 0)
```
Если слово найдено — `ПОИСК` вернет позицию вхождения (число > 0), если нет — по умолчанию была бы ошибка, но `ЕСЛИОШИБКА` подставит 0. Дальше можно использовать эту колонку для фильтрации или дополнительного условия в `ЕСЛИ`.
---
8. SPLIT / РАЗДЕЛИТЬ — разбиваем запросы на слова и выискиваем минус‑слова
Чтобы найти потенциальные минус‑слова, удобно разложить ключевую фразу на отдельные слова и посмотреть, какие лексемы тянут нецелевой трафик.
Синтаксис:
```
=SPLIT(A2; " ")
=РАЗДЕЛИТЬ(A2; " ")
```
Функция разделит строку из ячейки A2 на несколько столбцов по указанному разделителю. Чаще всего это:
- пробел — для разделения по словам;
- запятая — если фразы перечислены через запятую;
- вертикальная черта и другие символы.
Практическая польза:
- Быстро видеть, какие слова чаще всего встречаются в семантике.
- Выделять возможные минус‑слова (например, «бесплатно», «своими руками», «скачать»).
- Упрощать ручной анализ длинных поисковых запросов.
---
9. СЦЕПИТЬ / CONCAT / TEXTJOIN — генерация UTM‑меток и массовое объединение текста
UTM‑метки, длинные заголовки, динамические тексты объявлений — все это зачастую набор кусочков, который нужно объединить.
Классический вариант — `СЦЕПИТЬ`:
```
=СЦЕПИТЬ(A2; "?utm_source="; B2; "&utm_medium="; C2; "&utm_campaign="; D2)
```
Но удобнее использовать более современную функцию `TEXTJOIN` (в русской локали может быть `ТЕКСТСЦЕП`), которая позволяет сразу указать разделитель:
```
=TEXTJOIN("&"; ИСТИНА; "utm_source="&B2; "utm_medium="&C2; "utm_campaign="&D2)
```
Таким образом:
- можно быстро собрать UTM для тысяч ссылок;
- формировать понятные человеку названия групп/кампаний вспомогательными полями (тип трафика + гео + оффер).
---
10. REGEXEXTRACT / REGEXEXTRACT — вытаскиваем нужные куски текста по шаблону
Регулярные выражения в Google Таблицах — мощный инструмент для маркетолога, когда нужно вытащить из строки только определенную часть.
Синтаксис:
```
=REGEXEXTRACT(текст; шаблон)
```
Примеры задач:
- вытащить домен из URL;
- получить параметр UTM из длинной ссылки;
- достать числовое значение из текста отчета.
Например, чтобы вытащить значение `utm_campaign`:
```
=REGEXEXTRACT(A2; "utm_campaign=([^&]+)")
```
Функция вернет все, что попадает в круглые скобки — то есть название кампании до следующего амперсанда.
---
11. GOOGLETRANSLATE — быстрый перевод ключей и объявлений
Если вы запускаете кампании на нескольких языках или собираете семантику по иностранным рынкам, встроенный переводчик в Google Таблицах заметно ускоряет работу.
Синтаксис:
```
=GOOGLETRANSLATE(A2; "ru"; "en")
```
Где:
- `"ru"` — исходный язык;
- `"en"` — язык перевода.
Применение:
- первичный перевод семантики для зарубежных кампаний (после чего перевод обязательно проверяет носитель или профессиональный редактор);
- перевод отчетов или текстов объявлений для согласования с иностранным клиентом.
---
12. IMPORTRANGE — сводим данные из разных таблиц в одну
Часто данные по рекламным кампаниям разбросаны по нескольким файлам: отдельный документ для Google Ads, отдельный — для Яндекс Директа, еще один — для CRM.
IMPORTRANGE позволяет «подтянуть» диапазон из другого файла Google Таблиц.
Синтаксис:
```
=IMPORTRANGE("ID_таблицы"; "Название_листа!A1:Z1000")
```
Где `ID_таблицы` — это идентификатор документа из адресной строки.
Примеры использования:
- свести в одну сводную таблицу расходы и конверсии по всем источникам;
- собрать данные по клиентам или проектам из отдельных файлов в единый дашборд.
---
13. IMPORTXML — проверяем метаданные посадочных страниц
Маркетологу важно оперативно видеть, не потерялись ли тайтлы, описания и H1 на посадочных страницах. Функция `IMPORTXML` позволяет получать эти данные прямо с сайта.
Синтаксис:
```
=IMPORTXML(A2; "//title")
=IMPORTXML(A2; "//h1")
```
Где в A2 — URL страницы.
Вы можете:
- сделать список всех целевых страниц;
- в соседних столбцах подтянуть для каждой страницы `title` и `h1`;
- сравнить длину тайтлов, наличие ключевых слов;
- отметить пустые или дублирующиеся заголовки.
---
14. СУММЕСЛИ / SUMIF и СУММЕСЛИМН / SUMIFS — динамические отчеты по условиям
Финансовая аналитика рекламных кампаний — это почти всегда суммирование по условию: сколько было потрачено на определенную кампанию, площадку, тип устройства и т. д.
СУММЕСЛИ (SUMIF) работает с одним условием:
```
=СУММЕСЛИ(A:A; "Google Ads"; C:C)
```
Это просуммирует столбец C для всех строк, где в столбце A значение равно «Google Ads».
СУММЕСЛИМН (SUMIFS) позволяет задать сразу несколько условий:
```
=СУММЕСЛИМН(C:C; A:A; "Google Ads"; B:B; "поиск")
```
Используйте эти функции:
- для построения табличных отчетов по источникам, кампаниям, гео;
- чтобы сравнивать эффективность разных групп или типов трафика без сложных сводных.
---
15. ТРАНСП / TRANSPOSE — меняем строки и столбцы местами
Иногда данные удобнее анализировать в виде столбцов, иногда — в виде строк.
`ТРАНСП` просто разворачивает таблицу:
```
=ТРАНСП(A1:Z10)
```
Пригодится:
- когда экспорт из системы идет в «неудобном» формате;
- при подготовке отчетов, где метрики должны быть строками, а не столбцами (или наоборот).
---
16. XLOOKUP / ПРОСМОТРX — продвинутый ВПР нового поколения
`XLOOKUP` (в русской версии — `ПРОСМОТРX`) — более гибкая замена `ВПР` и `ГПР`. Функция:
- ищет и слева, и справа (в отличие от ВПР, который ищет только вправо);
- проще в синтаксисе;
- удобнее в больших таблицах.
Синтаксис (обобщенно):
```
=ПРОСМОТРX(что_ищем; где_ищем; что_возвращаем; [если_не_найдено])
```
Пример.
У вас есть:
- в одном листе — список ключевых слов и их ID;
- в другом — статистика по ID.
`ПРОСМОТРX` позволит по ключам быстро подтянуть нужные метрики, даже если ID находится в правом столбце, а ключ в левом — чего ВПР сделать не может без перестройки таблицы.
---
17. FILTER / ФИЛЬТР — отбираем только нужные строки по условиям
`FILTER` — динамический фильтр, который показывает только те строки диапазона, которые подходят под заданные условия.
Синтаксис:
```
=FILTER(A2:D1000; C2:C1000>0; B2:B1000="поиск")
```
Где:
- A2:D1000 — исходный набор данных;
- C2:C1000>0 — условие по конверсиям;
- B2:B1000="поиск" — условие по типу кампании.
Применение:
- быстро собрать отчет только по кампаний с конверсиями;
- выделить запросы с высоким CPA;
- снять выборку только по определенному региону или устройству.
---
18. QUERY — работа с данными как с базой
`QUERY` — одна из самых мощных функций в Google Таблицах. Она позволяет писать SQL‑подобные запросы к диапазону данных: агрегировать, фильтровать, группировать.
Пример запроса:
```
=QUERY(A1:H1000;
"select B, sum(F), sum(G)
where F > 0
group by B
order by sum(G) desc";
1)
```
Здесь:
- `B` — например, название кампании;
- `F` — количество конверсий;
- `G` — расходы;
- `1` — количество строк заголовков в диапазоне.
С помощью `QUERY` можно:
- построить полноценные срезы по кампаниям, объявлениям, ключам;
- агрегировать данные по дням/неделям/месяцам;
- делать отчеты, которые обновляются автоматически при обновлении выгрузок.
---
19. ARRAYFORMULA — автоматизация массовых вычислений
Вместо того чтобы тянуть формулу вручную вниз на сотни строк, можно использовать `ARRAYFORMULA` и применить вычисления сразу ко всему диапазону.
Синтаксис:
```
=ARRAYFORMULA(формула_для_диапазона)
```
Пример: посчитать стоимость конверсии для всех строк:
```
=ARRAYFORMULA(ЕСЛИ(ROW(A:A)=1; "CPA"; IF(G:G>0; F:F/G:G; "")))
```
Где:
- в F — расходы;
- в G — количество конверсий.
Так вы один раз прописываете формулу в верхней ячейке — и она автоматически применяется ко всем строкам, включая новые.
---
20. LAMBDA — создаем собственные функции
Если вы часто повторяете одну и ту же комбинацию формул (например, логику генерации заголовка из ключа, проверки длины и подсветки), имеет смысл вынести ее в отдельную пользовательскую функцию с помощью `LAMBDA`.
Общая идея:
1. Пишете сложную формулу один раз, проверяете, что она работает.
2. Оборачиваете ее в `LAMBDA(параметры; выражение)`.
3. Присваиваете имя через «Именованные диапазоны и функции».
4. В дальнейшем вызываете, как обычную функцию: `=МОЯ_ФУНКЦИЯ(A2)`.
Это особенно удобно в командах, где одни и те же операции повторяются в разных проектах.
---
Как упростить анализ без формул: условное форматирование и сводные
Даже без сложных формул Таблицы помогают визуально навести порядок в данных.
Условное форматирование
- Подсвечивайте ключевые слова с низким CTR красным.
- Помечайте строки, где CPA выше заданного порога.
- Выделяйте фразы с нулевыми показами, чтобы не держать мусорные запросы в активных группах.
Это помогает моментально замечать проблемные места даже в больших массивах данных.
Сводные таблицы
Сводные — быстрый способ:
- посмотреть распределение трат по кампаниям, устройствам, регионам;
- оценить, какие объявления приносят больше всего конверсий;
- найти неэффективные площадки в сетях.
Комбинация: «сырые выгрузки → сводные таблицы → условное форматирование» превращает обычные Google Таблицы в простой, но полезный дашборд для ежедневного контроля.
---
Дополнительные идеи автоматизации для контекстной рекламы
Чтобы выжать из Google Таблиц максимум, имеет смысл настроить несколько типовых решений один раз, а потом просто копировать их из проекта в проект.
1. Шаблон для подготовки семантики
Сделайте отдельный файл, в котором уже настроено:
- очистка ключей (`СЖПРОБЕЛЫ`, `ПОДСТАВИТЬ`, удаление дублей);
- разбор запросов по словам (`SPLIT`, `СТРОЧН`);
- поиск и фиксация минус‑слов;
- группировка ключей по типам (брендовые, общие, коммерческие запросы) через `ЕСЛИ`, `ПОИСК`, `ЕСЛИОШИБКА`.
Такой шаблон сократит время от выгрузки из Wordstat до готовых списков для кампаний.
2. Автоматизированный отчет по кампаниям
Соберите в одном документе:
- листы с сырыми выгрузками из рекламных систем;
- лист с импортом данных из других файлов (`IMPORTRANGE`);
- сводный лист с `СУММЕСЛИМН`, `QUERY` и `FILTER`.
Добавьте условное форматирование для критических показателей (CTR, CPA, доля конверсий), и документ станет ежедневным рабочим инструментом по мониторингу эффективности.
3. Контроль посадочных страниц
В отдельной таблице держите перечень всех посадочных страниц с:
- подтянутыми через `IMPORTXML` заголовками `title` и `h1`;
- длиной заголовков (`ДЛСТР`);
- флажками для страниц, у которых есть проблемы (пустой `title`, дубликаты, слишком длинные заголовки).
Так вы не пропустите технические ошибки, которые бьют по конверсии и качеству объявлений.
4. Массовая генерация объявлений
Используя комбинацию `СЦЕПИТЬ`/`TEXTJOIN`, `ЗАМЕНИТЬ`, `СТРОЧН` и `ДЛСТР`, можно:
- на основе ключевых слов сформировать черновики заголовков и описаний;
- проверить их длину и наличие основных триггеров;
- подготовить выгрузку в нужном формате для импорта в рекламный кабинет.
5. Быстрая сегментация поисковых запросов
Поисковые запросы удобно:
- разбивать на группы по вхождению определенных слов (`ЕСЛИ` + `ПОИСК`);
- выделять сегменты, которые дают высокий CTR и конверсии;
- отмечать «информационные» запросы, которые лучше переводить в отдельные кампании или отключать.
Здесь хорошо работают связка `ЕСЛИОШИБКА`, `ПОИСК`, `FILTER`, `QUERY`.
---
Google Таблицы для контекстного маркетолога — это не просто «Excel в браузере», а гибкий конструктор, который при грамотной настройке снимает львиную долю рутины: от обработки ключевых слов до построения живых дашбордов по эффективности кампаний. Освоив перечисленные 20 функций и объединив их в свои шаблоны, можно освободить время для того, что действительно приносит результат: стратегии, креативов и экспериментов.
