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

18399

Привет! Мы — Penguin-team, агентство контекстной рекламы, а это — наш блог по бизнесу, маркетингу и РРС. Каждый месяц мы выпускаем гайды, статьи и инструкции о том, как работать с eCommerce, брендировать, настраивать Google Рекламу и другие каналы трафика.

Наш блог читают маркетологи, SEM-специалисты и предприниматели; его рекомендуют на SEMConf и других конференциях.

Еще больше уникальных материалов — в рассылке 👇 Подписывайтесь!

Получать еще больше уникальных материалов 👉🏻

Спасибо за подписку!

Содержание

18399

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 выделите область со словами → перейдите во вкладку «Ячейки» → «Извлечь слова»: