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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ищейка

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

Возможности 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

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

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

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

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

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

10 фишек Excel для экономии времени Маркетолога

Хотите достичь увеличения эффективности вашей работы в Microsoft Excel в РАЗЫ?

10 фишек Excel для экономии времени Маркетолога

Представляем для вас 10 фишек Экселя!

Подсказка Excel 1. При нажатии двух кнопок: «ALT + =» будет расчитана сумма значений Столбца или Колонки!!

Фишки Excel: автоматическое суммирование значений столбца или строчки — SUM()

Подсказка Excel 2. Изменение форматов значения ячейки (денежный/числовой и т.д.): нажатие трех кнопок «CTRL+SHIFT+цифра(1-5).

Фишки Excel: смена формата ячейки на денежный/числовой/дату и т.д.

Подсказка Excel 3. При нажатии двух кнопок: «CTRL+`» можно увидеть как формируются формулы! Повторное нажатие приведет таблицу к обычному виду.

Фишки Excel: изучаем все формулы на листе

Подсказка Excel 4. При нажатии двух кнопок: «CTRL+ ВВЕРХ» или «CTRL+ВНИЗ», можно перемещать курсор кверху или книзу значения выбранного столбца (а при нажатой клавиши SHIFT еще и выделять все значения).

Фишки Excel: прыгаем курсором кверху или книзу выбранного столбца

Подсказка Excel 5. Дублирование формулы во все ячейки (по вертикали и по горизонтали). Создайте формулу в первой ячейке, затем подведите курсор к нижнему правому углу учейки с формулой, и когда курсор превратится в «+», нажмите двойной клик мышкой. В результате формула продублируется во все строчки столбца!

Фишки Excel: автоматическое применение формулы на весь столбец/строку

Подсказка Excel 6. Добавляйте новые столбцы и строчки с помощью нажатия трех кнопок: «CTRL+SHIFT++». Удаляйте существующие — с помощью двух кнопок «CTRL+-«.

Фишки Excel: добавление и удаление столбцов и строк через клавиатуру!

Подсказка Excel 7. Путем выделения одной или нескольких колонок и нажатия двойным кликом на их правой части можно автоматически привести ширину колонок в соответствие с шириной текста в колонках!

Фишки Excel: ширина колонок «по ширине ячеек» за 1 дабл-клик мышки

Подсказка Excel 8. Копирование шаблона двух первых ячеек на остальные! Для этого требуется заполнить первые две ячейки шаблоном и потянуть вниз за уголок ячеек!

Фишки Excel: копирование шаблонов ячеек, в том числе дат!

Подсказка Excel 9. Перемещение между листами осуществляется с помощью нажатия всего лишь двух кнопок: «CTRL+PageDown» или «CTRL+PageUp»!

Фишки Excel: перемещение между разными листами с помощью нажатия двух клавиш!

Подсказка Excel 10. Копирование формата ячеек на другие ячейки с помощью нескольких кликов мышкой!

Фишки Экселя: применение форматов одной ячейки к нескольким другим!

Базовый 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.

6 способов использования Excel в маркетинге

Часто Excel воспринимается маркетологами, как программа, предназначенная лишь для составления таблиц и расчета формул, не более того. Однако, если проявить фантазию и креативность, таблицы могут стать вашим верным помощником в деле организации и планирования маркетинговой стратегии, считают в компании HubSpot.

Так как же маркетологу использовать таблицы Excel для облегчения собственной жизни?

Таблицы в качестве календаря публикаций

Ведение блога – крайне важная вещь для привлечения аудитории на сайт. Однако часто довольно трудно контролировать поток креатива для того, чтобы писать посты, полностью соответствующие маркетинговой стратегии, и соблюдать при этом сроки. И вот здесь таблицы Excel могут реально помочь.

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

Таблицы как шаблон для SEO

Разработка и исполнение эффективной SEO-стратегии – занятие не из легких. На это уходит много времени сил и ресурсов, но грамотно составленная таблица может помочь и здесь.

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

Таблицы как средство мониторинга контекстных кампаний

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

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

Таблицы в качестве календаря для публикаций в SocialMedia

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

С помощью Excel вы можете создать листы для каждой соцсети и использовать их в качестве репозитория контента для публикации. Особенно полезен Excel в случае Twitter т.к. он автоматически подсчитывает количество символов.

Таблицы в качестве калькулятора трафика и лидов

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

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

Таблицы для ежемесячного отчета по метрикам

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

* Шаблоны, использованные в статье, созданы компанией HubSpot.