Google Таблицы – универсальный инструмент, который будет полезен в том числе для работы с семантикой. Он поможет быстро очистить ключевики от лишних символов, убрать дубли и даже спарсить содержимое тегов с посадочных страниц.
В статье мы разобрали 16 формул Google Таблиц для решения разных задач, с которыми приходится сталкиваться при настройке и ведении контекстной рекламы.
СЖПРОБЕЛЫ – убираем лишние пробелы в начале и конце ключевых фраз
Сложность: низкая.
Функция СЖПРОБЕЛЫ (или TRIM) применяется для удаления лишних пробелов в ячейке.
Обратите внимание! Функция удаляет не все пробелы, а только в начале и конце ячейки. Пробелы между словами остаются.
Применение
При сборе ключевиков в список семантики попали фразы, у которых могут быть лишние пробелы в начале или конце фразы. Чтобы быстро очистить весь список от таких пробелов, используем формулу СЖПРОБЕЛЫ.
Затем выделяем столбец с примененной формулой, жмем Ctrl+C и вставляем в первый столбец сочетанием клавиш Ctrl+Shift+V (специальная вставка, только значения).
Обратите внимание! При копировании ячеек с формулой копируется именно формула, а не значения. Если вставить скопированные ячейки обычным способом (без специальной вставки – Ctrl+V), то будут вставлены формулы, а значения потеряются. Поэтому при копипасте значений в ячейках с формулами используйте именно специальную вставку.
Как убрать двойные пробелы между словами
Это можно сделать с помощью обычной замены текста. Выделите нужный диапазон ячеек. Нажмите Ctrl+H. Во всплывающем окне в поле «Найти» введите два пробела, в поле «Заменить на» – один пробел.
Затем нажмите «Заменить все».
Инструмент заменит все двойные пробелы между словами на одинарные.
О том, как в один клик удалить лишние пробелы, спецсимволы и дубли слов в СЯ, читайте здесь.
ПОДСТАВИТЬ – замена фрагментов текста
Сложность: низкая.
Функция ПОДСТАВИТЬ (или SUBSTITUTE) позволяет заменять фрагменты текста в ячейках.
Синтаксис:
=ПОДСТАВИТЬ(текст; искать; замена; [номер_соответствия]).
- текст – исходный текст, в котором нужно выполнить замену. Как правило, здесь необходимо ссылаться на ячейку с текстом;
- искать – фрагмент текста, который нужно заменить;
- замена – текст, на который будет заменен найденный фрагмент;
- номер_соответствия – необязательный параметр. Номер соответствия, для которого выполняется замена. По умолчанию функция заменяет все соответствия, найденные в тексте.
Применение
С помощью функции ПОДСТАВИТЬ можно быстро убрать из списка фраз лишние символы. Например, вы спарсили семантику из Вордстата, и у части слов в списке есть модификатор «+». Для загрузки в Google Ads вы хотите почистить ключевые слова и загрузить их в чистом виде.
Поэтому напротив ячейки с ключевой фразой вводим формулу =ПОДСТАВИТЬ(А2; «+»; "";).
Функция заменит все символы «+», которые найдет в ячейке, на пустой символ. Проще говоря, просто уберет все плюсы.
Затем копируем ячейку с функцией, выделяем весь столбец напротив ключевых слов и жмем «Вставить». Функция применится для каждой ячейки.
Затем копируем весь столбец и с помощью «Специальной вставки» вставляем только значения в свободный столбец. Теперь их можно загружать в Google Ads или использовать для других целей.
Точно так же можно сделать и обратное действие: добавить модификатор «+», если он вам нужен в кампании. Например, вы хотите, чтобы перед предлогом «для» всегда стоял модификатор. Для этого необходимо использовать функцию =ПОДСТАВИТЬ с такими параметрами:
=ПОДСТАВИТЬ([номер ячейки]; "для"; "+для").
СТРОЧН – переводим буквы из верхнего регистра в нижний
Сложность: низкая.
Функция СТРОЧН переводит все символы в исходной ячейке в нижний регистр.
Синтаксис:
=СТРОЧН(ячейка)
Применение
При сборе ключевиков слова в списке могут быть в разном регистре. Для удобства работы с ядром нужно привести все слова к одному формату (хотя бы для того, чтобы с таблицей визуально проще было работать).
Для этого собираем все ключевики в одном столбце Google Таблицы. В соседнем столбце используем функцию =СТРОЧН(А1).
Копируем формулу в остальные ячейки столбца. Затем копируем столбец с преобразованными ключевиками и вставляем его в первый столбец с помощью специальной вставки (напоминаем – Ctrl+Shift+V).
ЗАМЕНИТЬ – делаем первую букву заголовка заглавной
Сложность: низкая.
Функция ЗАМЕНИТЬ (или REPLACE) позволяет заменять фрагмент текста в строке, который начинается с определенного символа и имеет заданную длину.
Синтаксис:
=ЗАМЕНИТЬ(текст; начало; длина; замена)
- текст – исходный текст (ячейка), в котором нужно произвести замену;
- начало – номер символа, с которого начинается заменяемый отрезок текста. Номер первого символа в строке – 1;
- длина – количество символов в отрезке, который нужно заменить;
- замена – текст, который нужно поместить вместо заменяемого отрезка.
Применение
У нас есть список ключевых слов, которые хотим использовать в качестве заголовков объявлений. Для этого нужно преобразовать первую букву каждого ключевика из строчной в заглавную. Сделать это можно с помощью функции ЗАМЕНИТЬ:
Вот что содержит эта формула:
- А1 – первый аргумент функции. Обозначает ячейку, в которой необходимо произвести замену;
- 1 – порядковый номер символа, с которого начинается заменяемый фрагмент;
- 1 – здесь указано количество символов в заменяемом фрагменте;
- ЛЕВСИМВ – функция, которая возвращает левый символ в ячейке (то есть первую букву);
- ПРОПНАЧ – еще одна функция, преобразует первые буквы слов в заглавные.
ДЛСТР – считаем длину текста в ячейке
Сложность: низкая.
Функция ДЛСТР (или LEN) определяет длину строки текста в ячейке.
Синтаксис:
=ДЛСТР(ячейка)
Применение
С помощью этой функции нельзя сделать какие-то преобразования, но она полезна при подготовке текстов для объявлений контекстной рекламы. С ее помощью можно быстро проверить, соответствует ли длина заголовка или текста ограничениям рекламной системы.
С помощью условного форматирования можно настроить подсветку цветом ячеек, в которых указано количество символов, превышающее допустимый лимит.
UNIQUE – избавляемся от дублей в списке
Сложность: низкая.
Функция UNIQUE возвращает уникальные строки в указанном диапазоне. Проще говоря, позволяет отфильтровать дубли из списка.
Синтаксис:
=UNIQUE(диапазон).
Применение
Есть список ключевиков, которые мы собрали для семантического ядра из разных источников. Чтобы не искать дубли вручную (это нереально, если в списке несколько тысяч фраз), воспользуемся функцией UNIQUE.
Для этого выгружаем все ключевики списком в Google Таблицу. В первой ячейке соседнего столбца указываем формулу:
=UNIQUE(A1:A).
Во втором столбце функция выведет список уникальных ключевиков, убрав все дубли.
Рекомендация! Даже если вы думаете, что в вашем списке нет дублей, все равно проверьте это с помощью функции UNIQUE. Это займет не больше минуты, зато вы будете на 100% уверены в чистоте списка.
Для устранения дублей, лишних пробелов и спецсимволов в СЯ можно также воспользоваться бесплатным Нормализатором слов от Click.ru. Достаточно загрузить список ключевиков и задать настройки нормализации. Система все сделает за вас – вам останется выгрузить очищенное СЯ.
ВПР – сравниваем два списка ключевиков и находим упущенные фразы
Сложность: средняя.
Функция ВПР (или VLOOKUP) применяется для сравнения данных из двух диапазонов и вывода нужных значений (или поиска несоответствий).
Синтаксис:
=ВПР(запрос; диапазон поиска; номер_столбца; [отсортировано])
Применение
У нас запущены рекламные кампании в Яндекс.Директе и Google Ads. Задача – найти ключевые слова, которые используются в Google Ads, но не добавлены в кампании Директа.
Для этого выгружаем в Google Таблицу ключевики с Google Ads (столбец А) и Директа (столбец Е).
С помощью функции ВПР произведем поиск ключевиков из первого списка по второму списку. В параметрах функции указываем:
- ячейку, содержимое которой будем искать во втором столбце;
- диапазон, по которому будем производить поиск;
- номер столбца в диапазоне данных, по которому производим поиск. С ячеек этого столбца функция будет возвращать значения при совпадении поиска;
- отсортирован ли диапазон поиска (этот параметр необязателен, но мы указываем значение «0» – не отсортирован).
Функция будет последовательно брать значение ячейки в первом столбце и искать совпадение с ним в столбце Е. Если совпадение найдено, функция выведет это значение в ячейке столбца С. Если совпадений нет, отобразит «#Н/Д». Это значит, что данное ключевое слово используется в Google Ads, но отсутствует в Яндекс.Директе.
После этого нам останется лишь отфильтровать данные по значению «#Н/Д» в столбце С и добавить все полученные ключевые слова в Яндекс.Директ.
ЕСЛИОШИБКА – разбиваем ключевые слова на группы (ищем определенные слова в фразах)
Сложность: средняя.
Функция ЕСЛИОШИБКА (или IFERROR) возвращает значение первого аргумента, если в нем нет ошибки. Если в первом аргументе ошибка – возвращает значение второго аргумента (или пустое значение, если второй аргумент не указан).
Звучит сложно, но сейчас покажем, чем полезна функция на практике.
Синтаксис:
=ЕСЛИОШИБКА(значение; [значение_при_ошибке])
Применение
Мы хотим разбить список ключевых фраз на группы в зависимости от содержания определенных слов в фразах. Для поиска содержания слов мы можем использовать следующую формулу:
=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("полимер"; A1)>0;"в группу с полимерами"; "0"))
Разберемся, что происходит внутри этой конструкции:
- ПОИСК("полимер"; A1) – функция ПОИСК ищет, есть ли в ячейке А1 слово «полимер». Если слово есть, функция возвращает значение TRUE (1), если слова нет – значение FALSE (0).
- Функция ЕСЛИ проверяет результат функции ПОИСК. Если функция ПОИСК возвращает любое значение больше 0, то функция ЕСЛИ выводит текст «в группу с полимерами». В противном случае возвращает значение «0».
- Функция ЕСЛИОШИБКА проверяет результат функции ЕСЛИ. Если результат этой функции не является ошибкой (то есть не равен FALSE, 0) – выводится текст «в группу с полимерами».
Дальше мы можем отсортировать список ключевиков по столбцу с названиями групп и продолжить работу с семантикой.
SPLIT – раскладываем фразы на отдельные слова для удобного подбора минус-слов
Сложность: низкая.
Функция SPLIT позволяет разбить фразу на отдельные слова и разнести их по отдельным ячейкам в строке.
Синтаксис:
SPLIT(текст; разделитель; [тип_разделителя]; [удаление_пустых_ячеек])
Обязательных параметров всего 2:
- текст – конкретный текст или ссылка на ячейку с фразой, которую нужно разделить;
- разделитель – символ, разделяющий фрагменты текста (обычно – пробел).
Применение
Допустим, мы спарсили ключевые слова по нашей тематике из Вордстата. Чтобы удобнее было найти и выбрать минус-слова, разделим все фразы на отдельные слова. Для этого как раз понадобится функция SPLIT.
Выгружаем все фразы в Google Таблицу, в один столбец. В соседнем столбце прописываем функцию SPLIT в таком виде: =SPLIT(A1; " ").
Протягиваем функцию до конца списка. Дальше алгоритм действий следующий:
- чистим список слов от дублей (для этого к каждому столбцу можно применить функцию UNIQUE);
- сортируем уникальные слова по алфавиту;
- отбираем слова, которые будем использовать в качестве минус-слов.
Вот подробный гайд по работе с минус-словами в Яндексе и Google.
СЦЕПИТЬ – используем функцию объединения строк для генерации UTM-меток
Сложность: низкая.
Функция СЦЕПИТЬ предназначена для объединения нескольких строк.
Синтаксис:
=СЦЕПИТЬ("текст1"; "текст2";...)
Применение
С помощью функции СЦЕПИТЬ можно перемножать списки слов, создавая всевозможные комбинации фраз для семантического ядра. А также создавать шаблоны для автоматического добавления параметров к URL. Например, UTM-меток.
Допустим, в кампании есть несколько ключевых слов, под каждое из которых мы хотим подготовить ссылку с уникальной UTM-меткой. Для всех ссылок метки utm_source, utm_medium и utm_campaign будут одинаковыми. Отличаться будет только метка utm_term – в нее необходимо подставить транслитерацию исходного ключевого слова.
Готовим таблицу:
- в первом столбце – транслит ключевиков;
- во втором столбце – URL посадочной страницы;
- в третьем – шаблон utm-меток.
Прописываем формулу СЦЕПИТЬ таким образом: сначала к URL страницы добавляем шаблон utm-меток, затем – транслит ключевого слова (подставится как значение метки utm_term):
Более простой способ перемножать списки слов – с помощью Комбинатора ключевых слов от Click.ru. Задаете слова, которые нужно перемножить, и система выдает все возможные их комбинации. Инструмент бесплатный для всех.
REGEXEXTRACT – извлекаем нужный фрагмент текста с помощью регулярных выражений
Сложность: для продвинутых пользователей.
Функция REGEXEXTRACT предназначена для извлечения текста из ячеек с помощью регулярных выражений.
Синтаксис:
=REGEXEXTRACT(текст; регулярное_выражение)
Применение
У нас есть список посадочных страниц конкурентов. Для настройки таргетинга по «Особым аудиториям» в Google Ads нам нужны домены конкурентов (то есть не полные адреса страниц, а лишь домены).
Если конкурентов много, вручную собирать домены будет проблематично.
Поэтому проще выгрузить список URL в таблицу и извлечь из них домены с помощью REGEXEXTRACT. Для этого в формуле нужно указать такое регулярное выражение:
(?:https?:\/\/)?(?:[ @\n]+@)?(?:www\.)?([ :\/\n]+)
Протягиваем формулу для всего списка URL. Затем с помощью функции UNIQUE получаем уникальные имена доменов (без дублей). Итоговый список можем использовать для настройки таргетинга в Google Ads.
IMPORTRANGE – подтягиваем данные из других таблиц или листов
Сложность: средняя.
Функция IMPORTRANGE предназначена для импорта данных из одной Google-таблицы в другую.
Синтаксис:
=IMPORTRANGE(url_таблицы; диапазон )
С помощью этой функции можно собирать в одном месте данные из разных таблиц или листов: консолидированные отчеты, сводки и т.д.
Применение
У нас есть две таблицы: одна – с отчетом по контекстной рекламе, другая – по таргетированной. Мы хотим, чтобы данные отчета по таргетированной рекламе отображались на одном листе с отчетом по контексту.
Чтобы подтянуть данные из второй таблицы, используем функцию IMPORTRANGE. Первым аргументом функции указываем url таблицы, с которой будем брать данные. Вторым аргументом – название листа в этой таблице и диапазон данных.
Если у вас открыт доступ к таблице, данные сразу подтянутся на нужный лист. Если доступа нет, отобразится уведомление с предложением запросить доступ.
Прелесть такого способа в том, что при изменении данных в исходной таблице функция подтянет изменения и обновит данные в сводном листе.
IMPORTXML – парсим title и h1 с посадочных страниц для проверки корректной заполненности
Сложность: средняя.
Функция IMPORTXML – функция-парсер, с помощью которой можно парсить содержимое практически любых тегов html-страниц.
У функции относительно простой синтаксис:
=IMPORTXML(ссылка; запрос_xpath)
Самое сложное здесь – XPath-запрос (если вы раньше с ними не сталкивались). Однако работать с XPath-запросами не так сложно, как кажется на первый взгляд, и после небольшой практики вы будете использовать их комфортно и привычно.
Применение
Функция IMPORTXML незаменима, если нужно быстро проверить корректность заполнения тегов title и h1 на посадочных страницах (это важно, если вы запускаете, например, автотаргетинг или динамические объявления).
Для проверки нужно спарсить содержимое тегов в таблицу. Это делается просто:
- Выгружаем в таблицу список URL посадочных страниц (1 ячейка – 1 URL).
- В первой ячейке соседнего столбца прописываем формулу IMPORTXML: =IMPORTXML(A2; "//title").
- Затем копируем ячейку с формулой и вставляем во все остальные ячейки.
- Функция подтянет содержимое тегов title по каждой странице.
О том, как массово парсить метатеги и заголовки с любого сайта, читайте здесь.
СУММЕСЛИ – считаем сумму по ячейкам, которые соответствуют заданным условиям
Сложность: низкая.
Функция СУММЕСЛИ позволяет суммировать данные в ячейках, которые соответствуют необходимым условиям.
Синтаксис:
СУММЕСЛИ(диапазон; условие; [сумма_диапазона])
- диапазон – область для поиска нужных ячеек;
- условие – условие, по которому будут отбираться ячейки для суммирования;
- сумма_диапазона – необязательный параметр. С его помощью можно указать отдельный диапазон ячеек, которые необходимо просуммировать. Если не указывать этот параметр, функция суммирует ячейки из первого диапазона.
Применение
Функция СУММЕСЛИ полезна при работе со статистическими данными с определенными разбивками. Например, у нас есть выгрузка по количеству регистраций с разбивкой по типам устройств и рекламных кампаний. Наша задача – быстро узнать:
- сколько пользователей зарегистрировались с мобильных устройств;
- какое количество регистраций принесли поисковые кампании.
Без применения формулы нам пришлось бы фильтровать данные в таблице – сначала по типу кампании, затем по типу устройства.
С использованием СУММЕСЛИ необходимые расчеты можно сделать быстрее и без лишних действий.
Для этого прописываем такую формулу: =СУММЕСЛИ(А1:А17; "Поиск"; D1:D17). Здесь:
- A1:A17 — столбец, в которому указан тип кампании. По нему будет происходить проверка условия.
- «Поиск» – тип кампании, который функция будет искать;
- D1:D17 – диапазон, в котором функция будет суммировать значения, соответствующие типу кампании «Поиск».
Подобным образом прописываем формулу для подсчета регистраций с мобильных (в качестве первого диапазона нужно будет указать второй столбец таблицы).
GOOGLETRANSLATE – переводим ключевики с русского на английский (или любой другой язык)
Сложность: низкая.
Функция GOOGLETRANSLATE – Google Переводчик внутри Google Таблиц.
Синтаксис:
GOOGLETRANSLATE(текст; [язык_оригинала]; [язык_перевода])
Применение
Мы хотим протестировать контекстную рекламу на англоязычном рынке. Чтобы не собирать отдельно англоязычную семантику, можно перевести текущую семантику из кампаний для Рунета с помощью функции GOOGLETRANSLATE.
Для этого выгружаем в Google Таблицу список ключевых слов. В соседнем столбце прописываем формулу для перевода с русского на английский:
=GOOGLETRANSLATE(A1;"ru";"en")
Затем выделяем столбец с переводом, копируем и вставляем в отдельный столбец как значения (Ctrl+Shift+V).
С помощью этой функции можно переводить на любые языки, поддерживаемые Google.
ТРАНСП – меняем местами строки и столбцы
Сложность: низкая.
Функция =ТРАНСП (или =TRANSPOSE) работает с массивами ячеек и меняет местами строки и столбцы.
Синтаксис:
=ТРАНСП(массив_или_диапазон)
Применение
Функция ТРАНСП полезна при работе со сводными таблицами. В некоторых случаях смена отображения данных в таблице улучшает представление данных и с таблицей работать удобнее.
Например, у нас есть таблица с данными по количеству регистраций. Данные разбиты по устройствам и типам кампаний. В строках – устройства, в столбцах – типы кампаний.
Нам нужно проанализировать и сравнить между собой типы кампаний. Удобнее это делать, когда данные по каждому типу кампании расположены в одну строку. Чтобы быстро получить нужный нам вид таблицы, используем функцию =ТРАНСП, в аргументах которой задаем диапазон исходной таблицы):
Теперь типы кампании отображаются в столбцах, и данные при этом не потерялись.
Используйте рассмотренные формулы Google Таблиц, и вы значительно упростите работу с семантикой.