15+ полезных фишек Excel для контекстной рекламы

3732

Excel — очень недооцененная программа, которая на самом деле обладает крутым функционалом. Сегодня о том, как этот функционал применить в РРС.

До старта: установить надстройку

Обычно в подобных подборках для большинства задач советуют использовать формулы Excel. Формулы — это классно, но можно добиться того же результата проще, установив надстройку Ёxcel. Это экономит еще больше времени, да и формулы запоминать не надо.

Как это сделать:

Скачать саму надстройку.
Распаковать архив.
Через контекстное меню открыть свойства файла SETUP, на вкладке «Общие» поставить галочку возле «разблокировано».
Перенести папку со скачанными файлами из «Загрузок» туда, где она будет храниться постоянно. Т.к. если установить надстройку и перенести файлы, надстройка не будет работать.
Запустить файл SETUP — надстройка установится.

В подборке «Что читают РРСшники» мы упоминали Дмитрия Тумайкина — он создатель еще одной полезной надстройки для Excel и контекстной рекламы. Мы еще не успели опробовать ее в действии, но слышал много хорошего на конференции 8Р! Почитать о ней можно в статье РРС World «Надстройка !SEMTools для Excel: новый уровень». Уже пробовали? Поделитесь самыми крутыми фишками в комментариях!

Удалить лишние пробелы

Вариант 1. Проблема: ну кто из нас не пропускал лишние пробелы во время написания объявлений?

Решение: Ctrl + H. В «Найти» ввести два пробела, в «Заменить на» — один пробел.

Если нужно убрать пробелы на всех листах файла, нажмите «Параметры >>», выберите «Искать» → «В книге».

Вариант 2. Проблема: после удаления модификатора широкого соответствия в начале ячейки остается один лишний пробел. Через «Найти и заменить» он не удаляется, но оставлять его в начале тоже нельзя.

Решение: формула =СЖПРОБЕЛЫ(область выполнения)

Удалить цифры

Проблема: если скопировать ключи из Яндекс.Вордстат, к ним «прилепится» частотность запросов.

В нужном файле Excel выделить столбец, в котором нужно удалить числа.
Открыть вкладку Ёxcel → Ячейки → Редактировать → Оставить в ячейке только текст.

3. Готово!

P.s. Если нужно частотность оставить, но в отдельной колонке, то скопируйте исходную колонку с «склеенными» ключе-частотностями. В одной колонке удалите числа, во второй — буквы.

Добавить одинаковые UTM-метки к URL

Проблема: есть много URL, к ним нужно добавить одинаковые UTM-метки. Вручную добавлять одно и то же — слив времени.

Решение:

Скопировать все URL в один столбец.
В соседнем столбце поставить нужную UTM-метку.

«Растянуть» UTM-метку на все столбцы: для этого выделить ячейку с меткой и потянуть за квадратик в правом нижнем углу.

Когда возле каждого URL стоит UTM-метка, перейти на вкладку «Ёxcel» → «Ячейки» → «Объединить ячейки без потери данных».

Наслаждаться и гордиться собой.

P.s. сцеплять можно любое количество ячеек. Так можно, к примеру, объединять все ключи с доп. словами «купить» и «цена».

Объединить данные из нескольких ячеек в одну

Проблема: у нас есть список ключей, но к ним еще нужно добавить города. Как это сделать не вручную?

Решение: =(ячейка с ключом)&(ячейка с городом).

Пошагово:

Ввести в строку формул =.
Кликнуть на ячейку с ключом.
Напечатать символ &.
Кликнуть на ячейку с городом.
Enter.

Чтобы распространить формулу на весь столбец, сделайте так в одной ячейке, а потом «растяните» ее вниз на весь столбец.

Разбить данные из одной ячейки на несколько

Проблема: как разнести данные из одной ячейки на несколько, ничего не удаляя?

Решение:

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

Выбирать вкладку «Данные» → «Текст по столбцам».

В открывшемся окне задать разделитель — символ, который будет сигнализировать программе, с какого момента вырезать содержимое ячейки. В нашем случае это пробел.

После этого первое слово останется в той же ячейке, второе перенесется в соседнюю ячейку справа, третье — в следующую за ней и так далее.

Объединить данные из двух таблиц в одну

Проблема: в одной таблице собраны id товаров и статистика по ним из Google Shopping. В другой — id товаров, их названия и маржинальность. Количество id в таблицах отличается и все они вперемешку.

К примеру, вот наши две таблицы:

Решение: раздел «Формулы» → «Ссылки и массивы» → «ВПР».

Пошагово:

Присвоить имя диапазону, откуда будем брать данные, чтобы дальше было легче искать данные. Для этого выделить вторую таблицу (без шапки) → Ctrl+ F3 → кнопка «Создать» → введите понятное вам имя для обозначения этой части данных.

Кликнуть на ячейку, в которую нужно перенести данные. В нашем случае нужно перенести название товара «кроссовки» из G2 в С16. Кликаем на С16, переходим на вкладку «Формулы» → «Ссылки и массивы» → «ВПР».

В открывшемся окне заполнить все поля:

искомое значение: Id, по которому нужно найти данные во второй таблице;
таблица: название второй таблицы, которое вы задали через ctrl+f3 раньше;
номер столбца: порядковый номер (не буква!) столбца во второй таблице, из которого нужно взять данные. У нас во второй таблице первый столбец id, а названия — второй столбец. Ставим номер столбца «2»;
интервальный просмотр: «ЛОЖЬ» — поиск точного совпадения, «ИСТИНА» — поиск примерного совпадения. Если мы объединяем данные по ID, нам нужны только точные совпадения — пишем «ЛОЖЬ».

Кликаем «ОК» — готово! В ячейку С16 скопировалось название «кроссовки». Для этого программа взяла ID из заданного искомого значения, нашла такой же ID в заданной таблице и перенесла название из второго столбца той таблицы на указанное место.

Чтобы распространить этот результат, как обычно, «растяните» его на все ячейки.

Посчитать слова в ячейке

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

Читайте пошаговую инструкцию, как собирать ключевые слова, в статье «Как составить семантическое ядро для контекстной рекламы»

Решение:

Возле столбца с ключами создать пустой столбец под количество слов.
В пустую ячейку возле первого ключа ввести формулу:

=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1)

Где все три «А1» заменить на фактическую ячейку с ключом. В нашем случае это В2:

Нажать Enter → в столбце появится количество слов в первой ключевой фразе. Дальше «растянуть» формулу на все строки, чтобы подсчитать количество слов во всех фразах.

Чтобы отобрать только фразы подходящей длины, выделите весь столбец «Слов» → вкладка «Главная» → «Сортировка и фильтр» → «Сортировка по убыванию».

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

Подсчитать символы

Проблема: в Google Ads есть ограничения по размеру текста — заголовков, описаний.

Решение: формула =Длстр(область выполнения)

Изменить регистр

Проблема: как массово изменить регистр с прописных букв на строчные? А наоборот? А сделать правильное правописание в текстах объявлений: первое слово с прописной буквы, остальные строчными? Менять это руками — слив времени в никуда.

Решение:

Выделить нужную область, где нужно внести изменения.
Перейти на вкладку «Ёxcel» → «Ячейки» → «Регистр».
Выбрать нужный формат написания.

Кстати! Если выбрать вариант «Предложение», то все первые слова будут написаны с большой буквы, а остальные — строчными.

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

Удалить минус-слова

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

Решение:

Выделить столбец с нужными данными.
Открыть окно поиска-замены — Ctrl + H.
В поле «Поиск» ввести «-*» — это значит, что программа будет искать комбинации, которые начинаются с минуса, а после минуса идет что угодно. Поле «Заменить на» оставить пустым. Готово!

Отфильтровать, чтобы внести изменения выборочно

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

Решение: отфильтровать данные, в которых нужно вносить изменения.

Пошагово:

К примеру, нам нужно внести изменения только в заголовки объявлений в группе «1-home builder».

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

На вкладке «Главная» кликнуть на «Сортировка и фильтр» → «Фильтр».

Возле названия колонки появится флажок с выпадающим окошком фильтра.

Нажать на него, поставить галочку возле нужного названия группы. В нашем случае это «1-home builder».

Теперь в таблице будут скрыты (не удалены, а просто скрыты) строки всех объявлений, кроме объявлений в группе «1-house builder». Можно спокойно менять данные автовыделением, не боясь случайно внести изменения в другие группы.

Полезно! Также через «Сортировку и фильтры» можно использовать настраиваемую сортировку для более узкого выделения данных. Для этого кликните на «Сортировка и фильтры» → «Настраиваемая сортировка» → задайте нужные параметры.

К примеру, использовать многоуровневую сортировку по столбцам и их значениям: сначала отсортировать от А до Я по столбцу Headline, потом по значению ячеек по столбцу Длина, потом еще по какому-то параметру…

Скопировать результат подсчета через формулы

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

Решение:

Выделить то, что нужно скопировать, скопировать через Ctrl+C.

Поставить курсор на ту ячейку, куда надо перенести данные.

На вкладке «Главная» выбрать «Вставить» → «Специальная вставка» или контекстное меню → «Специальная вставка».

В открывшемся окне выбрать «Вставить» → «Значения».

Значения формул скопируются в ту область, которую вы выделили после копирования.

Заменить все формулы на значения

Проблема: если таблица большая или нужно заменить формулы на значения во всех вкладках, использовать «Специальную вставку» — это морока и, опять же, слив времени.

Решение:

Выделить нужную зону, в которой хотите преобразовать все формулы в значения (не выделяйте ничего, если нужно сделать это на всем листе/документе).
Открыть вкладку «Ёxcel» → «Формулы» → «Заменить формулы на значения» → галочка на «Все формулы».

Важно! Если есть вероятность, что вам придется менять какие-то значения дальше, на основе которых работают формулы — не заменяйте их! Иначе пересчет придется делать вручную — потеряете больше времени.

Найти дубликаты в списке ключей

Проблема: ну у кого в семантике не попадались дубли?

Решение:

Выделить столбец, в котором нужно найти дубли (или не выделяйте ничего, если нужно искать по всему листу).
На вкладке «Главная» выбрать «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».

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

Условное форматирование позволяет выделить ячейки, которые содержат определенное слово/значение или данные больше определенного значения. Так можно, к примеру, найти в списке ключей все фразы со словом «купить» или все ключи с частотностью выше/ниже нужной вам.