15+ полезных фишек Excel для контекстной рекламы
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» → «Формулы» → «Заменить формулы на значения» → галочка на «Все формулы».
Важно! Если есть вероятность, что вам придется менять какие-то значения дальше, на основе которых работают формулы — не заменяйте их! Иначе пересчет придется делать вручную — потеряете больше времени.
Найти дубликаты в списке ключей
Проблема: ну у кого в семантике не попадались дубли?
Решение:
- Выделить столбец, в котором нужно найти дубли (или не выделяйте ничего, если нужно искать по всему листу).
- На вкладке «Главная» выбрать «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».
- Все повторяющиеся значения будут выделены красной заливкой.
Условное форматирование позволяет выделить ячейки, которые содержат определенное слово/значение или данные больше определенного значения. Так можно, к примеру, найти в списке ключей все фразы со словом «купить» или все ключи с частотностью выше/ниже нужной вам.
Удалить дубликаты
Проблема: если вам нужно не просто найти, а найти и удалить дубликаты, прошлый вариант не вполне удобен.
Решение: вкладка «Данные» → «Удалить дубликаты».
А какими формулами и фишками Excel пользуетесь для контекстной рекламы вы? Поделитесь в комментариях!
Получить 7 писем о том,
как улучшить свой AdWords аккаунт
+ PPC статьи
Улучшить свой AdWords
Получите 7 писем, о том, как можно улучшить свой аккаунт в AdWords
Подписаться