Привет! Мы — Penguin-team, агентство контекстной рекламы, а это — наш блог по бизнесу, маркетингу и РРС. Каждый месяц мы выпускаем гайды, статьи и инструкции о том, как работать с eCommerce, брендировать, настраивать Google Рекламу и другие каналы трафика.
Наш блог читают маркетологи, SEM-специалисты и предприниматели; его рекомендуют на SEMConf и других конференциях.
Еще больше уникальных материалов — в рассылке 👇 Подписывайтесь!
Получать еще больше уникальных материалов 👉🏻
Спасибо за подписку!
15+ полезных фишек Excel для контекстной рекламы
6.02.2022
13 мин.
easy
Минимум слов. Максимум дела.
В одном письме в месяц
7-дневный курс по Google Ads (Junior+)
Excel — очень недооцененная программа, которая на самом деле обладает крутым функционалом. Сегодня РРСшники агентства Penguin-team о том, как этот функционал применить в работе.
Установить надстройку — Excel в контекстной рекламе
Обычно в подобных подборках для большинства задач советуют использовать формулы Excel. Формулы — это классно, но можно добиться того же результата проще, установив надстройку Ёxcel. Это экономит еще больше времени, да и формулы запоминать не надо.
Как это сделать:
- Скачать саму надстройку.
- Распаковать архив.
- Через контекстное меню открыть свойства файла SETUP, на вкладке «Общие» поставить галочку возле «разблокировано».
- Перенести папку со скачанными файлами из «Загрузок» туда, где она будет храниться постоянно, чтобы надстройка работала.
- Запустить файл SETUP — надстройка установится.
Как удалить лишние пробелы в Excel — Excel в контекстной рекламе
Проблема 1: ну кто из нас не пропускал лишние пробелы во время написания объявлений?
Решение: Ctrl + H. В «Найти» ввести два пробела, в «Заменить на» — один пробел.
Если нужно убрать пробелы на всех листах файла, нажмите «Параметры >>», выберите «Искать» → «В книге».
Проблема 2: после удаления модификатора широкого соответствия в начале ячейки остается один лишний пробел. Через «Найти и заменить» он не удаляется, но оставлять его в начале тоже нельзя.
Решение: формула =СЖПРОБЕЛЫ(область выполнения)
Решить проблемы с лишними пробелами поможет и функция в Ёxcel «Сжать пробелы»:
Как удалить цифры в Excel — Excel в контекстной рекламе
Проблема: если скопировать ключи из Яндекс.Вордстат, к ним «прилепится» частотность запросов.
Решение:
- В нужном файле Excel выделить столбец, в котором нужно удалить числа.
- Открыть вкладку Ёxcel → «Ячейки»→ «Редактировать» → «Оставить в ячейке только текст».
3. Готово!
P. S. Если частотность нужно оставить, но в отдельной колонке, то скопируйте исходную колонку со «склеенными» ключе-частотностями. В одной колонке удалите числа, во второй — буквы.
Как добавить одинаковые UTM-метки к URL в Excel — Excel в контекстной рекламе
Проблема: есть много URL, к ним нужно добавить одинаковые UTM-метки. Вручную добавлять одно и то же — слив времени.
Решение:
- Скопировать все URL в один столбец.
- В соседнем столбце поставить нужную UTM-метку.
- «Растянуть» UTM-метку на все столбцы: для этого выделить ячейку с меткой и потянуть за квадратик в правом нижнем углу.
- Когда возле каждого URL стоит UTM-метка, перейти на вкладку «Ёxcel» → «Ячейки» → «Объединить ячейки без потери данных».
- Наслаждаться и гордиться собой.
P. S. Сцеплять можно любое количество ячеек. К примеру, объединять все ключи с дополнительными словами «купить» и «цена».
Как объединить данные из нескольких ячеек в одну в Excel — Excel в контекстной рекламе
Проблема: у нас есть список ключей, но к ним еще нужно добавить города. Как это сделать не вручную?
Решение: =(ячейка с ключом)&(ячейка с городом).
Пошагово: ввести в строку формул = → кликнуть на ячейку с ключом → напечатать символ & → кликнуть на ячейку с городом → Enter.
Чтобы распространить формулу на весь столбец, сделайте так в одной ячейке, а потом «растяните» ее вниз на весь столбец.
Как разбить данные из одной ячейки на несколько — Excel в контекстной рекламе
Проблема: как разнести данные из одной ячейки на несколько, ничего не удаляя?
Решение:
- Выделить столбец/ячейку, где нужно разнести значения одной ячейки по нескольким.
- Выбрать вкладку «Данные» → «Текст по столбцам».
- В открывшемся окне задать разделитель — символ, который будет сигнализировать программе, с какого момента вырезать содержимое ячейки. В нашем случае это пробел.
- После этого первое слово останется в той же ячейке, второе перенесется в соседнюю ячейку справа, третье — в следующую за ней и так далее.
Как объединить данные из двух таблиц в одну — Excel в контекстной рекламе
Проблема: в одной таблице собраны id товаров и статистика по ним из Google Shopping. В другой — id товаров, их названия и маржинальность. Количество id в таблицах отличается и все они вперемешку.
К примеру, вот наши две таблицы:
Решение: раздел «Формулы» → «Ссылки и массивы» → «ВПР».
Пошагово:
- Присвоить имя диапазону, откуда будем брать данные, чтобы дальше было легче искать данные. Для этого выделить вторую таблицу (без шапки) → Ctrl+ F3 → кнопка «Создать» → введите имя для обозначения этой части данных.
- Кликнуть на ячейку, в которую нужно перенести данные. В нашем случае нужно перенести название товара «кроссовки» из G2 в С16. Кликаем на С16, переходим на вкладку «Формулы» → «Ссылки и массивы» → «ВПР».
- В открывшемся окне заполнить все поля:
- искомое значение: ID, по которому нужно найти данные во второй таблице;
- таблица: название второй таблицы, которое вы задали через Ctrl+F3 раньше;
- номер столбца: порядковый номер (не буква!) столбца во второй таблице, из которого нужно взять данные. У нас во второй таблице первый столбец ID, а названия — второй столбец. Ставим номер столбца «2»;
- интервальный просмотр: «ЛОЖЬ» — поиск точного совпадения, «ИСТИНА» — поиск примерного совпадения. Если мы объединяем данные по ID, нам нужны только точные совпадения — пишем «ЛОЖЬ».
- Кликаем «ОК» — готово! В ячейку С16 скопировалось название «кроссовки». Для этого программа взяла ID из заданного искомого значения, нашла такой же ID в заданной таблице и перенесла название из второго столбца той таблицы на указанное место.
Чтобы распространить этот результат, как обычно, «растяните» его на все ячейки.
Как посчитать слова в ячейке — Excel в контекстной рекламе
Проблема: в Google размер ключевой фразы ограничен 10 словами, в Яндексе — 7. Чтобы выбрать из семантического ядра только подходящие ключи и не пересчитывать слова в них вручную, РРСшники Penguin-team используют автоподсчет слов.
Читайте пошаговую инструкцию, как собирать ключевые слова, в статье «Как составить семантическое ядро для контекстной рекламы»
Решение:
- Возле столбца с ключами создать пустой столбец под количество слов.
- В пустую ячейку возле первого ключа ввести формулу:
=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1)
Где все три «А1» заменить на фактическую ячейку с ключом. В нашем случае это В2:
- Нажать Enter → в столбце появится количество слов в первой ключевой фразе. Дальше «растянуть» формулу на все строки, чтобы подсчитать количество слов во всех фразах.
- Чтобы отобрать только фразы подходящей длины, выделите весь столбец «Слов» → вкладка «Главная» → «Сортировка и фильтр» → «Сортировка по убыванию».
- Вверху таблицы сразу отобразятся самые длинные ключи, которые теперь можно удалить, если они превышают допустимое количество слов рекламных систем.
Как подсчитать символы в Excel — Excel в контекстной рекламе
Проблема: в Google Ads есть ограничения по размеру текста — заголовков, описаний.
Решение: формула =Длстр(область выполнения)
Как изменить регистр в Excel — Excel в контекстной рекламе
Проблема: как массово изменить регистр с прописных букв на строчные? А наоборот? А сделать правильное правописание в текстах объявлений: первое слово с прописной буквы, остальные строчными? Менять это руками — слив времени в никуда.
Решение:
- Выделить область, где нужно внести изменения.
- Перейти на вкладку «Ёxcel» → «Ячейки» → «Регистр».
- Выбрать нужный формат написания.
Кстати! Если выбрать вариант «Предложение», то все первые слова будут написаны с большой буквы, а остальные — строчными.
Плюс ко всему, в настройках можно автоматически добавить в конец текста в ячейках восклицательный знак, знак вопроса и другие символы.
Как удалить минус-слова в Excel — Excel в контекстной рекламе
Проблема: при выгрузке работающей кампании в Яндекс.Директе могут встретиться минус-слова на уровне фраз. А нам нужен только список ключей, а минус-слова не нужны.
Решение:
- Выделить столбец с нужными данными.
- Открыть окно поиска-замены — Ctrl + H.
- В поле «Поиск» ввести «-*» — это значит, что программа будет искать комбинации, которые начинаются с минуса, а после минуса идет что угодно. Поле «Заменить на» оставить пустым. Готово!
Как отфильтровать, чтобы внести изменения выборочно — Excel в контекстной рекламе
Проблема: нужно внести изменения не во всю таблицу, а, к примеру, только в данные одной кампании.
Решение: отфильтровать данные, в которых нужно вносить изменения.
Пошагово:
- К примеру, нам нужно внести изменения только в заголовки объявлений в группе «1-home builder».
- Выделить столбец с названиями групп объявлений.
- На вкладке «Главная» кликнуть на «Сортировка и фильтр» → «Фильтр».
- Возле названия колонки появится флажок с выпадающим окошком фильтра.
- Нажать на него, поставить галочку возле нужного названия группы. В нашем случае это «1-home builder».
- Теперь в таблице будут скрыты (не удалены, а просто скрыты) строки всех объявлений, кроме объявлений в группе «1-house builder». Можно спокойно менять данные автовыделением, не боясь случайно внести изменения в другие группы.
Полезно! Также через «Сортировку и фильтры» можно использовать настраиваемую сортировку для более узкого выделения данных. Для этого кликните на «Сортировка и фильтры» → «Настраиваемая сортировка» → задайте нужные параметры.
К примеру, использовать многоуровневую сортировку по столбцам и их значениям: сначала отсортировать от А до Я по столбцу Headline, потом по значению ячеек по столбцу Длина, потом еще по какому-то параметру…
Как скопировать результат подсчета через формулы — Excel в контекстной рекламе
Проблема: если считать что-то через формулы, эти данные потом невозможно просто выделить, скопировать и вставить в другой файл — скопируется не сам результат подсчета, а формула.
Решение:
- Выделить то, что нужно скопировать, скопировать через Ctrl+C.
- Поставить курсор на ту ячейку, куда надо перенести данные.
- На вкладке «Главная» выбрать «Вставить» → «Специальная вставка» или контекстное меню → «Специальная вставка».
- В открывшемся окне выбрать «Вставить» → «Значения».
- Значения формул скопируются в ту область, которую вы выделили после копирования.
Как заменить все формулы на значения в Excel — Excel в контекстной рекламе
Проблема: если таблица большая или нужно заменить формулы на значения во всех вкладках, использовать «Специальную вставку» — это морока и, опять же, слив времени.
Решение:
- Выделить нужную зону, в которой хотите преобразовать все формулы в значения (не выделяйте ничего, если нужно сделать это на всем листе/документе).
- Открыть вкладку «Ёxcel» → «Формулы» → «Заменить формулы на значения» → галочка на «Все формулы».
Кроме того, можно скопировать ячейки, а при вставке в контекстном меню выбрать «Значения»:
Важно! Если есть вероятность, что вам придется менять какие-то значения дальше, на основе которых работают формулы — не заменяйте их! Иначе пересчет придется делать вручную — потеряете больше времени.
Как найти дубликаты в списке ключей в Excel — Excel в контекстной рекламе
Проблема: ну у кого в семантике не попадались дубли?
Решение:
- Выделить столбец, в котором нужно найти дубли (или не выделяйте ничего, если нужно искать по всему листу).
- На вкладке «Главная» выбрать «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».
- Все повторяющиеся значения будут выделены красной заливкой.
Условное форматирование позволяет выделить ячейки, которые содержат определенное слово/значение или данные больше определенного значения. Так можно, к примеру, найти в списке ключей все фразы со словом «купить» или все ключи с частотностью выше/ниже нужной вам.
Как удалить дубликаты в Excel — Excel в контекстной рекламе
Проблема: если вам нужно не просто найти, а найти и удалить дубликаты, прошлый вариант не вполне удобен.
Решение: вкладка «Данные» → «Удалить дубликаты».
Как извлечь слова в Excel — Excel в контекстной рекламе
Проблема: если при больших объемах ключевых слов вам для ориентировочной разбивки на группы или потенциальные минус-слова надо знать, какие слова часто повторяются, используйте функцию извлечения слов.
Это поможет отобрать и подсчитать уникальные слова.
Решение: в Ёxcel выделите область со словами → перейдите во вкладку «Ячейки» → «Извлечь слова»:
А какими формулами и фишками Excel пользуетесь для контекстной рекламы вы? Поделитесь в комментариях!
103293
79
11 минут
49451
35
6 минут
43977
42
10 минут
43416
58
8 минут
41605
30
12 минут