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

6.03.2020
13 мин.
Контекстная реклама,
9041

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

Установить надстройку — Excel в контекстной рекламе

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

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

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

Как удалить лишние пробелы в Excel — Excel в контекстной рекламе

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

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

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

Убрать пробелы на всех листах файла

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

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

убрать пробел в начале
Удалить лишний пробел

Решить проблемы с лишними пробелами поможет и функция в Ёxcel «Сжать пробелы»:

Удалить лишние пробелы
Ячейки → Редактировать → Сжать пробелы

Как удалить цифры в Excel  Excel в контекстной рекламе

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

Решение:

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

3. Готово!

только текст в ячейке
Результат — только текст в ячейке

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

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

Как добавить одинаковые UTM-метки к URL в Excel  Excel в контекстной рекламе

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

Решение:

  1. Скопировать все URL в один столбец.
  2. В соседнем столбце поставить нужную UTM-метку.
добавить utm метки
Поставить нужную UTM-метку
  1. «Растянуть» UTM-метку на все столбцы: для этого выделить ячейку с меткой и потянуть за квадратик в правом нижнем углу.
растянуть
Растянуть на все строки
  1. Когда возле каждого URL стоит UTM-метка, перейти на вкладку «Ёxcel» → «Ячейки» → «Объединить ячейки без потери данных».
объединить ячейки
Объединить ячейки с URL и UTM-меткой
  1. Наслаждаться и гордиться собой.

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

Как объединить данные из нескольких ячеек в одну в Excel Excel в контекстной рекламе

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

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

Пошагово: ввести в строку формул = → кликнуть на ячейку с ключом → напечатать символ & → кликнуть на ячейку с городом Enter.

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

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

растянуть данные
Растянуть формулу на все строки

Как разбить данные из одной ячейки на несколько Excel в контекстной рекламе

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

Решение:

  1. Выделить столбец/ячейку, где нужно разнести значения одной ячейки по нескольким.
Ячейки, где значения нужно разделить на несколько
  1. Выбрать вкладку «Данные» → «Текст по столбцам».
текст по столбцам
Выбрать «Текст по столбцам»
  1. В открывшемся окне задать разделитель — символ, который будет сигнализировать программе, с какого момента вырезать содержимое ячейки. В нашем случае это пробел.
Задать разделитель
  1. После этого первое слово останется в той же ячейке, второе перенесется в соседнюю ячейку справа, третье — в следующую за ней и так далее.
перенос слов по ячейкам
Разделенные значения

Как объединить данные из двух таблиц в одну Excel в контекстной рекламе

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

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

объединить данные
Таблицы, которые нужно объединить

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

Пошагово:

  1. Присвоить имя диапазону, откуда будем брать данные, чтобы дальше было легче искать данные. Для этого выделить вторую таблицу (без шапки) → Ctrl+ F3 → кнопка «Создать» → введите имя для обозначения этой части данных.
Присвоить имя диапазону
  1. Кликнуть на ячейку, в которую нужно перенести данные. В нашем случае нужно перенести название товара «кроссовки» из G2 в С16. Кликаем на С16, переходим на вкладку «Формулы» → «Ссылки и массивы» → «ВПР».
перенести данные
Формула для переноса данных
  1. В открывшемся окне заполнить все поля:
Заполнить аргументы функции
  • искомое значение: ID, по которому нужно найти данные во второй таблице;
  • таблица: название второй таблицы, которое вы задали через Ctrl+F3 раньше;
  • номер столбца: порядковый номер (не буква!) столбца во второй таблице, из которого нужно взять данные. У нас во второй таблице первый столбец ID, а названия — второй столбец. Ставим номер столбца «2»;
  • интервальный просмотр: «ЛОЖЬ» — поиск точного совпадения, «ИСТИНА» — поиск примерного совпадения. Если мы объединяем данные по ID, нам нужны только точные совпадения — пишем «ЛОЖЬ».
  1. Кликаем «ОК» — готово! В ячейку С16 скопировалось название «кроссовки». Для этого программа взяла ID из заданного искомого значения, нашла такой же ID в заданной таблице и перенесла название из второго столбца той таблицы на указанное место.
результат переноса данных
Выполнен перенос данных

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

Как посчитать слова в ячейке Excel в контекстной рекламе

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

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

Решение:

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

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

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

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

Как подсчитать символы в Excel Excel в контекстной рекламе

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

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

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

Как изменить регистр в Excel Excel в контекстной рекламе

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

Решение:

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

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

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

добавить символ
Добавить символы

Как удалить минус-слова в Excel Excel в контекстной рекламе

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

Решение:

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

Как отфильтровать, чтобы внести изменения выборочно Excel в контекстной рекламе

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

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

Пошагово:

  1. К примеру, нам нужно внести изменения только в заголовки объявлений в группе «1-home builder».
выборочно внести изменения
Группа, в которой нужно внести изменения
  1. Выделить столбец с названиями групп объявлений.
  1. На вкладке «Главная» кликнуть на «Сортировка и фильтр» → «Фильтр».
применить фильтр
Фильтр данных
  1. Возле названия колонки появится флажок с выпадающим окошком фильтра.
Флажок фильтра
  1. Нажать на него, поставить галочку возле нужного названия группы. В нашем случае это «1-home builder».
выбрать фильтр
Выбрать нужное название группы
  1. Теперь в таблице будут скрыты (не удалены, а просто скрыты) строки всех объявлений, кроме объявлений в группе «1-house builder». Можно спокойно менять данные автовыделением, не боясь случайно внести изменения в другие группы.


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

Настраиваемая сортировка

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

многоуровневая сортировка
Сортировка по нескольким параметрам

Как скопировать результат подсчета через формулы Excel в контекстной рекламе

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

Решение:

  1. Выделить то, что нужно скопировать, скопировать через Ctrl+C.
копировать результат
Копировать результат подсчета
  1. Поставить курсор на ту ячейку, куда надо перенести данные.
  1. На вкладке «Главная» выбрать «Вставить» → «Специальная вставка» или контекстное меню → «Специальная вставка».
специальная вставка
Специальная вставка
  1. В открывшемся окне выбрать «Вставить» → «Значения».
вставить значения
Вставить значения
  1. Значения формул скопируются в ту область, которую вы выделили после копирования.

Как заменить все формулы на значения в Excel Excel в контекстной рекламе

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

Решение:

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

Кроме того, можно скопировать ячейки, а при вставке в контекстном меню выбрать «Значения»:

Вставка значений вместо формул

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

Как найти дубликаты в списке ключей в Excel Excel в контекстной рекламе

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

Решение:

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

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

Как удалить дубликаты в Excel Excel в контекстной рекламе

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

Решение: вкладка «Данные» → «Удалить дубликаты».

удалить дубликаты
Удалить дубликаты

Как извлечь слова в Excel — Excel в контекстной рекламе

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

Это поможет отобрать и подсчитать уникальные слова.

Решение: в Ёxcel выделите область со словами → перейдите во вкладку «Ячейки» → «Извлечь слова»:

Извлечь слова

А какими формулами и фишками Excel пользуетесь для контекстной рекламы вы? Поделитесь в комментариях!

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Будьте в курсе последних обновлений

Присоединиться в

Получить 7 писем о том,
как улучшить свой AdWords аккаунт
+ PPC статьи

Имя
e-mail
Улучшить AdWords

Улучшить свой AdWords

Получите 7 писем, о том, как можно улучшить свой аккаунт в AdWords

Подписаться

Наш сайт использует файлы cookie, чтобы улучшить работу и предоставить максимальное удобство пользователям.

В версии 3.0.0

  • новые сервисы: Ubersuggest (NeilPatel), Spyfu и другие;
  • автосохранение списка минус-слов в рабочей области;
  • авторизация для хранения данных в аккаунте;
  • списки типовых минус-слов;
  • возможность хранить пользовательские списки минус-слова в расширении для дальнейшего применения в аккаунте;
  • англоязычный и русскоязычный интерфейс.

Основные комбинации

  • LeftMouseClick для добавления слова, повторное нажатие - для удаления
  • LeftALT + LeftMouseClick - для сбора фраз
  • LeftALT + S - для поиска слов

In version 3.0.0

  • new services: Ubersuggest (NeilPatel), Spyfu and others;
  • autosave of a negative keywords list in the workspace;
  • authorization for storing data in the account;
  • generic negative keyword lists;
  • the ability to store custom lists of negative keywords in the extension for further use in your account;
  • English and Russian interface.

Key combinations

  • LeftMouseClick to add a word, press again - to delete
  • LeftALT + LeftMouseClick - to collect phrases
  • LeftALT + S - for words searching

В версии 2.0

  • 2 режима выдачи ключевых слов:
  • быстрая выдача - аналогична выдаче Google Planner, но найденные результаты включают каждое слово из введенной в поиск фразы;
  • полная выдача - расширенная выдача, которая включает поиск по дополнительным релевантным фразам, но найденные результаты также включают каждое слово из введенной в поиск фразы.
  • Обновленный файл выгрузки ключевых слов Excel:
  • выгрузка локаций в удобном формате для загрузки в Editor;
  • обновленный шаблон для создания объявлений.
  • Полный список локаций.
  • Группировка отчетов по проектам.
  • Устранена ошибка с задержкой в 30 секунд.
  • Новые фильтры для получения релевантной выдачи.
  • Копирование минус-слов.

In version 2.0:

  • 2 modes of keywords search result:
  • quick search result - is similar to one of Google Planner , but the results found include every word of the phrase entered in the search;
  • full search result - extended search result that includes search on additional relevant phrases, but the results found also include every word of the phrase entered in the search.
  • Updated Exces Keyword Upload File:
  • uploading of locations in a convenient format for uploading to Editor;
  • updated template for creating ads.
  • Full list of locations.
  • Grouping of reports on the projects.
  • Fixed an error with a delay of 30 seconds.
  • New filters for getting relevant search results.
  • Copying of negative keywords.

Несколько шагов до работы в Penguin

Заполните форму

Прикрепите резюме:
1Кликов 259, показов 3 515. Чему равно CTR?
2Заходов на сайт 874, конверсий 16. Чему равен коэффициент конверсии?
3Что такое СРА?
4CTR 4,03%, кликов 240. Чему равно число показов объявления?
5Затрат 20 158, конверсий 63. Чему равна стоимость конверсии?

Несколько шагов до работы в Penguin

Заполните форму

Прикрепите резюме:

Познакомимся?

Денис Березкин

Кем вы работаете?

Выберите один из вариантов