Эксель для маркетологов

Возможности Excel, без которых не обойтись специалисту по контекстной рекламе

Зачем Excel интернет-маркетологу?

Каждый опытный директолог знает, что создание новой рекламной кампании начинается с XLS-шаблонов. В Яндекс.Директ и Google AdWords можно выгрузить готовые шаблоны для создания объявлений с помощью Excel. Они значительно упрощают работу специалиста.

Редактирование рекламных кампаний

Довольно часто возникает потребность в корректировке и дополнении рекламных объявлений. Вносить массовые изменения можно с помощью специализированных программ, таких как Директ Коммандер или AdWords Editor. Но зная специальные функции Excel, внесение правок в объявления займет еще меньше времени.

Анализ статистики и построение отчетов

В Excel доступно множество фильтров и сортировок. Эти возможности создавать более структурированные отчеты удобны для восприятия информации.

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

Горячие клавиши и команды в Microsoft Excel

Не будем рассматривать базовые команды, такие как Ctrl+C, Ctrl+V, Delete и Ctrl+Z. Думаю, с ними знаком каждый.

Горячие клавиши

Описание и назначение

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

Функции Excel

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

Самая первая функция, которую вы могли уже видеть в шаблонах рекламных кампаний,– подсчет количества символов в ячейке: =ДЛСТР(A6).

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

Следующая, не менее полезная функция склеивания текстовых строк: =СЦЕПИТЬ(A7;B7). Упрощенная ее формула через амперсанд: =A4&B4. Если между склеиваемыми значениями нужен пробел, ставим его в кавычках: =A5&» «&B5.

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

Преобразование букв из строчных в прописные и наоборот. За это отвечают следующие функции:

=СТРОЧН(A9) – преобразует все буквы в строчные;
=ПРОПИСН(A11) – преобразует все буквы в прописные;
=ПРОПНАЧ(A10) – преобразует первую букву каждого слова в заглавную, остальные в строчные.

Для чего нужны данные функции? Одна из них пригодится при создании рекламных объявлений по схеме «1 фраза = 1 объявление». Копируем запросы в столбец с заголовками и применяем к новым заголовкам функцию ПРОПНАЧ.

Но если вы обратили внимание, функция преобразует в прописные символы первые буквы каждого слова в ячейке. Такая схема приемлема только для создания объявлений под систему Google Adwords. Для объявлений для Яндекс.Директ воспользуемся усложненной формулой:
=ЗАМЕНИТЬ(B4;1;1;СИМВОЛ(КОДСИМВ(ПРОПНАЧ(B4))))

Это формула преобразует в прописную букву только первый символ ячейки. В формуле мы использовали вспомогательные функции КОДСИМВ, которая возвращает числовой код первого символа в текстовой строке, и функцию СИМВОЛ, которая переводит обратно код символа в символ.

Кстати, в OpenOffice есть встроенный функционал написания с прописной буквы только первого слова ячейки. Для этого необходимо перейти в меню «Формат» → «Регистр» и выбрать формат «Как в предложениях».

При работе с рекламными кампаниями и анализе статистики может возникнуть необходимость в логических функциях. Рассмотрим пример с самой распространенной логической функцией ЕСЛИ:

=ЕСЛИ(C6>33;»Превышен лимит»;»Все ок»).

В примере функция применяется для проверки длины заголовка. Если значение в ячейке больше 33, выводится сообщение «Превышен лимит», если меньше – «Все ок».

Напоминаем, для выхода из режима редактирования формул надо нажать Esc.

Другие возможности Excel

Помимо формул, есть и другие методы оптимизации рекламных кампаний в Excel.

Замена формул в ячейках на текст

После применения функций в ячейках остаются формулы, которые привязаны к другим ячейкам. Чтоб оставить только результат применения формул, нужно воспользоваться специальной вставкой значений. Для этого копируем измененные ячейки, кликаем правой кнопкой мыши и в меню выбираем «Вставить» → «Специальная вставка» → «Значения». Ячейки с формулами можно удалить.

Зачистка спецсимволов и минус-слов в заголовках объявлений

При копировании ключевых слов в заголовки остается много «мусора». Убрать все лишние символы и слова можно с помощью функции поиска и замены. Чтобы удалить минус-слова, воспользуемся комбинацией клавиш Ctrl+F, в модальном окне на вкладке «Заменить» в поле «Найти» вводим знак — и после него ставим *. Поле «Заменить» оставляем пустым.

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

Кстати, с помощью функции Поиска и замены можно не только менять или удалять значения, но и выделять цветом ячейки с определенным содержимым. Для этого зайдите в расширенные параметры функции замены и настройте формат. В новом окошке выберите заливку цветом. Так можно подсвечивать объявления с нужными ключевыми словами.

При сборе большого количества ключевых слов можно упустить дубли. Как правило, удаление дубликатов и пересечений ключевых слов делается через специализированные программы, но для удобства воспользуйтесь и встроенным функционалом Excel. Выделяем область данных, заходим в Меню «Данные» → «Удалить дубликаты».

Сортировка и фильтры

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

Чтобы включить режим работы с фильтрами, нужно зайти в пункт меню «Данные» и нажать на значок «Фильтры».

С помощью текстовых фильтров можно сортировать текстовые значения в ячейках. Допустим, мы хотим посмотреть статистику только по тем запросам, которые содержат слово «сайт», но не содержат «раскрутка».

Кликаем на всплывающее меню рядом с названием столбца, выбираем пункт «Текстовые фильтры» → «Настраиваемый фильтр».

Далее указываем условия:

Фильтр выведет на экран только те строки, которые соответствуют нашему условию.

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

© 1PS.RU, при полном или частичном копировании материала ссылка на первоисточник обязательна.

Руководитель отдела Маркетинга сервиса 1PS.RU

Понравилась статья?

Спасибо, мы старались!

Кстати, вы подписаны на нашу рассылку? Если нет, то самое время познакомиться с Катей.

Сожалеем, что не оправдали ваши ожидания ((
Возможно, вам понравятся другие статьи блога.

Пошаговое руководство по самостоятельному продвижению сайта

В помощь начинающему интернет-маркетологу : полезные формулы Excel

Чему научимся:

• удалять лишние пробелы;
• делать первую букву в строке заглавной;
• считать количество знаков в строке;
• сокращать текст до нужного количества знаков;
• объединять содержимое нескольких ячеек в одну;
• делать все буквы в строке строчными;
• извлекать необходимое количество символов из текста;
• добавлять знак рубля.

1. Удаляем лишние пробелы

Случается, что при создании текстов (например, объявлений для Директа или Ads) мы случайно добавляем лишние пробелы. Быстро справиться с этим поможет формула «СЖПРОБЕЛЫ».

2. Делаем первую букву в строке заглавной

Чтобы сэкономить время и не зажимать клавишу Shift каждый раз, когда мы пишем новый текст (например, заголовок рекламного объявления), можно писать, не думая о регистре, а после автоматически преобразовать первые буквы в заглавные при помощи специальной формулы.

Общий вид формулы такой: =ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1)))

3. Считаем количество знаков в строке

Такой подсчет бывает необходим при создании текстов, в которых есть ограничения по количеству знаков, например, заголовков и текстов рекламных объявлений. Для подсчета воспользуемся функцией «ДЛСТР». Учтите, что знаки считаются вместе с пробелами.

4. Выбираем из текста необходимое количество знаков

При создании рекламных объявлений каждый из нас сталкивается с ограничениями по количеству символов. Если вдруг вы понимаете, что текст слишком длинный, то вы можете сократить его до нужного количества знаков при помощи формулы «ЛЕВСИМВ».

5. Объединяем текстовое содержимое нескольких ячеек в одну

Если вам необходимо объединить содержимое нескольких ячеек в одну, то формула «Сцепить» – ваш незаменимый помощник. Например, может пригодиться, если хотите добавить к текстам объявлений общую концовку (город, призыв к действию и др.).

6. Делаем все буквы в строке строчными

Когда текстов много, так легко случайно поставить где-нибудь не на месте прописную букву. Если вы уверены, что заглавные буквы вам не нужны, можно легко сделать все буквы строчными, воспользовавшись формулой «СТРОЧН».

7. Формула «ПСТР»

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

8. Формула «Рубль»

С помощью формулы можно добавить знак рубля, чтобы не писать полностью «рублей» или сокращенно «руб.». Например, Яндекс.Директ поддерживает этот символ, а вы экономите знаки и можете сделать свое рекламное объявление более объемным.

Ещё больше полезных знаний — на нашем бесплатном видеокурсе «Контекстная реклама: от основ до повышения эффективности». Регистрируйтесь!

Базовый Excel для маркетолога, специалиста по контекстной рекламе (вебинар от 19,01,17)

By Евгений Юдин

Максимум полезной информации в сжатые строки. То, чем мы постоянно пользуемся в своей работе. Фильтры, сводные таблицы, горячие клавиши, условное форматирование, формулы и т.д.

Как обычно, по четвергам мы проводим крутые вебинары. Стараемся уложить в максимально сжатые сроки большой объем полезной информации.

В этот раз руки дошли до Excel — одной из базовых программ в арсенале интернет-маркетолога (и многих других профессий).

В вебинаре мы рассмотрели, как я уже указал выше, следующие возможности:

• Фильтры (текстовые, числовые, по дате)
• Сводные таблицы
• Горячие клавиши (только те, которыми часто пользуемся сами)
• Формулы (часто применяемые нами)
• Условное форматирование

Все разбирали на примерах. На элементарных задачах, которые возникают в нашей работе. Надеюсь вам понравиться:

Далее идет урезанная текстовая версия вебинара. От себя добавлю, что лучше один раз посмотреть на живые примеры в ролике, чем несколько раз прочитать текст.

Ребят, сразу хочу сказать по поводу используемых мной формул. Я привык к формулам на русском. Поэтому разбор будет основан на формулах на русском языке. В любом случае можно очень легко, «загуглив» русское название, найти подходящую альтернативу на латинице.

Фильтры

Рассмотрим фильтры на примере эксельки — таблички с отчетом по фразам.

Что такое фильтры? Фильтры позволяют нам скрыть часть строк нашей области данных. Область данных это приведенная выше табличка.

Фильтры бывают нескольких видов, например:

1. Текстовые фильтры
2. Числовые
3. Фильтры по дате

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

Кликнув на эту кнопку, мы видим выпадающее меню.

Если мы введем что-то в поле «поиск», то сработает фильтр по-умолчанию «содержит». Вот так это работает:

Останутся только фразы, содержащие указанное сочетание символов. Самый интересный фильтр — настраиваемый. Мы можем указать 2 фильтра и указать как они должны применяться.

Результатом использования этого фильтра, будет:

Числовые фильтры часто мы применяем для фильтрации столбца с CTR-ом. Например для того чтобы найти фразы с CTR у которых ниже определенного значения.

Так же фильтры по особенному работают с датами. Если со строками и числами, при открытии меню фильтра у вас отображаются все значения подряд по списку, то при работе с датами значения группируются. Например:

Соответственно мы можем фильтровать даты по принадлежности к определенному месяцу и году. Или, например, в области поиска мы можем ввести 15 число и у нас останутся только все 15-е числа месяца.

Так же, обратите внимание, что если вы кликните еще раз на кнопку «фильтры» на вкладке данные (кнопка, которую мы использовали чтобы отобразить фильтры), то фильтры сбросятся и вы увидите исходную (не отфильтрованную) область данных.

Возвращаемся к презентации. Дальше у нас по списку «сводные таблицы».

Сводные таблицы Excel

Рассмотрим сводные таблицы на примере построения сводной таблички с данными по дням недели.

Обратите внимание что название дней недели у нас повторяются в строках.

Так вот, чтобы объединить каким-то образом значения по дням недели (например, посчитать суммарные расходы или количество кликов по дням недели), мы можем построить сводную таблицу.

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

При клике на эту кнопку у нас открывается меню создание сводной таблицы. В качестве области «данных для анализа» автоматически выделяется текущая область данных.

Ненадолго отвлечемся, чтобы понять как выделяется область данных.

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

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

Т.к. название столбца для «фыв» не задано, она не присоединилась к области данных

Указали название столбца и наша ячейка автоматически попала в область данных

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

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

Создадим сводную таблицу на новом листе.

У нас появилась область справа, необходимая для заполнения сводной таблички.

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

Добавление производится простым перетаскиванием названия столбца в определенную область.

В итоге у нас получится вот такая табличка.

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

Цель достигнута. Но я предлагаю немного усложнить нашу табличку.

Построим табличку с количеством кликов по дням недели и по месяцам.

Для этого попробуйте добавить в поле колонны столбец «дата».

Пока наша табличка выглядит не совсем так как нужно. У нас есть данные по датам и дням недели.

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

Для этого кликаем правой кнопкой по любой ячейке с датой. И жмем на пункт «группировать».

В появившемся меню группирования выбираем пункт «месяцы» (кстати, можно выделять одновременно несколько пунктов) и жмем «ок».

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

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

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

Горячие клавиши

Далее приведу горячие клавиши которые мы рассмотрели в вебинаре.

• Быстрое выделение таблички: Ctrl + A
• Быстрое перемещение к краю: Ctrl +(стрелки)
• Быстрое перемещение к краю с выделением: Ctrl + Shift + (стрелки)
• Сохранить как: Alt + F2
• Перетаскивая выделенный диапазон, удерживаем Ctrl для копирования.
• Для смещения диапазона, перетаскивая удерживаем Shift.
• Для вставки диапазона со смещением, удерживаем Ctrl + Shift.

Во время набора текста, пригодятся:

• Для перехода к соседней ячейке справа: Tab
• Для перехода к соседней ячейке слева: Shift + Tab
• Для перехода на следующую ячейку: Enter
• Для перехода к предыдущей ячейке: Shift + Enter
• Правка содержимого активной ячейки: F2

А так же, комбинации от Паши Семенова:

• Сохранить: Ctrl + S
• Сохранить как: F12
• Копировать: Ctrl + C
• Вставить: Ctrl + V
• Вставить на новый лист: Alt + Shift + F1
• Повторить последнюю команду: F4

Для тех, кто хочет найти момент с горячими клавишами в видео, мы начали их рассматривать на 16-й минуте.

Формулы Excel для маркетолога

Если вы только начинаете свою работу с формулами, вам может пригодится статья, где я более подробно и доступно показываю как работать с формулами в Excel.

Полезные для маркетинга формулы в Excel

Маркетинг предполагает работу с большим массивом данных: списки, планы, аналитика – всё это нужно где-то хранить и как-то обрабатывать. Для этих целей прекрасно подойдёт программа Microsoft Excel. Кроме удобной сетки, здесь есть возможность упростить множество действий с помощью встроенных функций. В этой статье мы собрали самые полезные для маркетологов формулы в Excel.

Правка текста

Убираем ненужные пробелы

Больше никаких дыр в тексте. Если вы пишете тексты в Excel, например, для объявлений, вы можете случайно оставить лишние пробелы. Здесь нельзя включить отображение знаков форматирования. Но формула «СЖПРОБЕЛЫ» легко удалит пробелы, которые не заметил ваш замыленный глаз.

Играем с размером букв

Радуйтесь, теперь можно не зажимать Shift, чтобы начать строчку с заглавной буквы. Пишите тексты в одном столбце, не отвлекаясь на размер, а потом замените всё разом. Формула выглядит так: =ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1))) Вставляем её в строчку справа от той, в которой нужно увеличить первую букву. Первая готова. А теперь берём эту ячейку, тянем за правый нижний угол, пока не выделите весь столбик напротив заполненных строчек. Отпускаем. Наслаждаемся.

Обратная ситуация, когда вам заглавные буквы не нужны совсем, а вы их бережно расставили, может быть исправлена формулой «СТРОЧН».

Ячейки, объединяйтесь

Если вам нужно прикрепить ко всем текстам одну концовку, одинаковое начало или перетасовать фразы, для этого тоже найдётся инструмент. Сделать фразы из двух или нескольких ячеек одним текстом поможет формула «СЦЕПИТЬ» . Только не забудьте проставить пробелы после первой части.

Быстрая чистка

Кроме формул, в таблице Excel полезным будет подстановочный знак *. Так можно, например, сократить ссылки до адреса главной страницы или убрать все минус слова. Откройте меню «Заменить». в поле «Найти» введите символ, который стоит перед ненужным текстом и «*». Если вы хотите избавиться и от первого символа знака, тогда оставьте поле «Заменить на» пустым. А если вы хотите, чтобы на месте вырезанных знаков появилось что-то другое, впечатайте это сюда.

Анализ данных

Количество имеет значение

Создать текст на определённое количество знаков – стандартнее задание не придумаешь. Но в этой программе нет автосчётчика. Зато есть в Excel формула «ДЛСТР» , которая выводит количество знаков в ячейку. Правда, так вы не сможете узнать количество знаков без пробела, только общую цифру.

А если выяснилось, что вы перебрали со знаками, вы всегда можете использовать функцию «ЛЕВСИМВ» или «ПРАВСИМВ» и сократить текст до нужного количества символов. Первый вариант обрезает конец текста, а второй – начало. Также можно подстричь текст с середины функцией «ПСТР» . Здесь нужно только выбрать номер первого символа в сокращённом тексте. Если вы готовы отказаться от первых двух предложений из 220 символов, и вам нужно сократить весь текст до 370 знаков, то функция отрежет эти 220 с начала, отсчитает 370 и отрежет всё с 592 знака.

Ищейка

Когда у вас много ключей, глазу сложно выцепить нужную информацию. Представьте, что вам нужно найти конкретное слово в определённой форме. Есть формула в таблице Excel и для этого. Создайте новый столбец, введите формулу НАЙТИ и растяните её до конца списка. Во втором столбике вы увидите много чередующихся ошибок и цифр. Ошибка значит, что в строчке нет этого слова. А цифра показывает с какого по порядку символа начинается нужное слово.