Excel

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

By in Excel

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


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

В этот раз руки дошли до 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.

Арифметические функции

Формула Результат выполнения Аргумент 1 Аргумент 2
=C3+D3 7 3 4
=C4-D4 -1 3 4
=C5*D5 12 3 4
=C6/D6 0,75 3 4

Работа со строками

Формула Результат выполнения Аргумент 1 Аргумент 2
=C11&D11 ТестируемФОРМУЛЫ Тестируем ФОРМУЛЫ
=СЦЕПИТЬ(C12;D12) ТестируемФОРМУЛЫ Тестируем ФОРМУЛЫ
=C14&» «&D14 Тестируем ФОРМУЛЫ Тестируем ФОРМУЛЫ
=СЦЕПИТЬ(C15;» «;D15) Тестируем ФОРМУЛЫ Тестируем ФОРМУЛЫ
=СТРОЧН(C17) тестируем формулы Тестируем ФОРМУЛЫ
=ПРОПНАЧ(C18) Тестируем Формулы Тестируем ФОРМУЛЫ
=ПРОПИСН(C19) ТЕСТИРУЕМ ФОРМУЛЫ Тестируем ФОРМУЛЫ

Условие

Формула Результат выполнения Аргумент 1
=ЕСЛИ(C20>10;»Число больше 10″;»Число меньше 10″) Число больше 10 17

Так же в вебинаре мы рассмотрели формулу:

=ПОИСКПОЗ(C23;D23:D26;0)

С ее помощью можно найти в диапазоне ячеек ту, которую вам нужно.

В примере выше:

  • C23 — ячейка, которую мы ищем
  • D23:D26 — диапазон ячеек, в котором мы ищем ячейку C23
  • 0 — это то, как мы ищем (нуль означает, что мы ищем ячейку внутри которой будет такой же текст, как в ячейке C23)

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

Помимо этих формул, я так же показал что такое «Фиксация столбца/строки в ячейке в формуле».

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

Лучше 1 раз увидеть, чем 100 раз услышать =)

Вот тот момент, где я рассказываю о том, что такое фиксация столбца и/или строки в формуле:

Помимо того, что я описал в этой статье, в вебинаре мы успели обсудить еще…

Условное форматирование

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

Для этого можно использовать условное форматирование. Но на этом его возможности не заканчиваются.

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

Спасибо что прочитали статью до конца! До скорых встреч!

  • Эдда

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

    • Согласен. Фильтры постарались разобрать в рамках данного вебинара. Насколько я помню, в видео-версии вебинара так же есть примеры использования сортировки.

  • Константин

    Огонь инфа! Спасибо!