15+ корисних фішок Excel для контекстної реклами

11818

Привіт! Ми — Penguin-team, агентство контекстної реклами, а це — наш блог про бізнес, маркетинг та РРС. Щомісяця ми випускаємо гайди, статті та інструкції про те, як працювати з eCommerce, брендувати, налаштовувати Google Рекламу та інші канали трафіку.

Наш блог читають маркетологи, SEM-фахівці та підприємці: його рекомендують на SEMConf та інших конференціях.

Ще більше унікальних матеріалів — в розсилці 👇 Підписуйтесь!

Отримувати ще більше унікальних матеріалів 👉🏻

Дякуємо за підписку!

Зміст

11818

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» → «Осередки» → «Об’єднати осередки без втрати даних».

5. Насолоджуватися і пишатися собою.

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 виділіть область зі словами → перейдіть у вкладку «Осередки» → «Витягнути слова»: