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

13.08.2018
Penguin-team
13 мин.
Контекстная реклама,
1156

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

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

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

 

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

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

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

 

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

 

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

3. Готово!

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

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

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

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

 

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

 

Решение:

 

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

 

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

 

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

 

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

 

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

 

Пошагово:

 

  1. Ввести в строку формул =.
  2. Кликнуть на ячейку с ключом.
  3. Напечатать символ &.
  4. Кликнуть на ячейку с городом.
  5. Enter.
объединить данные
Объединить данные из нескольких ячеек

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

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

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

 

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

 

Решение:

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

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

 

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

 

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

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

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

 

Пошагово:

 

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

 

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

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

 

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

 

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

 

Решение:

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

 

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

 

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

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

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

 

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

 

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

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

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

 

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

 

Решение:

 

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

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

 

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

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

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

 

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

 

Решение:

 

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

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

 

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

 

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

 

Пошагово:

 

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

 

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


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

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

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

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

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

 

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

 

Решение:

 

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

 

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

 

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

 

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

 

Решение:

 

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

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

 

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

 

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

 

Решение:

 

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

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

 

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

 

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

 

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

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

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

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

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

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

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

Подписаться

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




В версии 2.2.0

  • Добавлена поддержка "Google Keyword Planner"
  • Используйте "LeftALT + S" для поиска слов
  • "Показать/скрыть" теперь полностью скрывает расширение с экрана

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

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