Excel для аналитиков

* Обновление

Excel – программа, которой мы пользуемся практически каждый день, и о том, как она облегчает жизнь большинству пользователей, можно даже не говорить. Но чем же она полезна для интернет-маркетологов? Мы рассмотрим 21 функцию Excel и попробуем ответить на этот вопрос.

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

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

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

Итак, поехали!

1) ВПР

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

Синтаксис

Данная функция состоит из 4 аргументов и представлена следующей формулой:

=ВПР(искомое_значение;таблица;номер_столбца;)

Рассмотрим каждый из аргументов:

• «Искомое значение» указывают в первом столбце рассматриваемого диапазона ячеек. Данный аргумент может являться значением или ссылкой на ячейку.
• «Таблица». Группа ячеек, в которой выполняется поиск искомого значения и возвращаемого. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение – в любом месте.
• «Номер столбца». Номер столбца, содержащий возвращаемое значение.
• «Интервальный просмотр» – необязательный аргумент. Это логическое выражение, определяющее – насколько точное совпадение должна обнаружить функция. В связи с этим условием выделяют 2 функции:

1. ИСТИНА. Эта функция, вводимая по умолчанию, ищет ближайшее к искомому значение. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
2. ЛОЖЬ. Данная функция ищет точное значение в первом столбце.

Примеры

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

Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.

В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.

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

2) ЕСЛИ

Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: 1) Если сравнение истинно; 2) Если сравнение ложно.

Формула функции состоит из трех аргументов и выглядит следующим образом:

=ЕСЛИ(логическое_выражение;»значение_если_истина»;»значение_если_ложь»), где:

• «логическое выражение» – формула;
• «значение если истина» – значение, при котором логическое выражение выполняется;
• «значение если ложь» – значение, при котором логическое выражение не выполняется.

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

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

=ЕСЛИ(B2>30000;»План выполнен»;»План не выполнен»)

Логическое выражение здесь – формула «B2>30000».

«Значение если истина» – «План выполнен».

«Значение если ложь» – «План не выполнен».

Вложенные функции ЕСЛИ

Помимо обычной функции ЕСЛИ, которая выдает всего 2 результата – «истина» и «ложь», существуют вложенные функции ЕСЛИ, выдающие от 3 до 64 результатов. В данном случае формула может вмещать в себя несколько функций.

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

Существует еще один способ использования функции ЕСЛИ – для проверки, пуста ячейка или нет. Для этого ее можно использовать вместе с функцией ЕПУСТО.

В этом случае формула будет такой: =ЕСЛИ(ЕПУСТО(номер ячейки);»Пустая»;»Не пустая».

Вместо функции ЕПУСТО также можно использовать другую формулу: «номер ячейки=»» (ничего).

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

Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧЁТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧЁТЕСЛИ и ЕСЛИОШИБКА.

3) СУММЕСЛИ и СУММЕСЛИМН

Функция СУММЕСЛИ позволяет суммировать данные, соответствующие определенному условию, находящиеся в указанном диапазоне.

Функция состоит из 3 аргументов и имеет формулу:

=СУММЕСЛИ(диапазон;условие;)

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

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

Пример

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

Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.

Формула данной функции имеет следующий вид:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; ; …)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.

4) СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.

Формула функции:

=СЧЁТЕСЛИ(диапазон;критерий)

«Диапазон» – группа ячеек, которые нужно подсчитать.

«Критерий» – условие, согласно которому выбираются ячейки для подсчета.

В приведенном примере функция подсчитала количество ключей, число переходов по которым больше 100000, – в итоге получилось 3 ключа.

В функции СЧЁТЕСЛИ можно использовать только один критерий. Если же нужно сделать подсчет по нескольким условиям, можно применить функцию СЧЁТЕСЛИМН.

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

Формула функции:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;;…)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные же аргументы необязательны. Можно использовать до 127 пар диапазонов и условий.

5) ЕСЛИОШИБКА

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

Функция имеет 2 аргумента и представлена формулой: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:

• «значение» – формула, которая проверяется на наличие ошибки;
• «значение_если_ошибка» – значение, появляющееся в ячейке в том случае, если вычисление в формуле выдало ошибку.

Предположим, что у вас сломался счетчик аналитики, и в ячейке, в которой нужно указать число посетителей, стоит ноль, а число покупок – 32. Как такое может быть? Функция в данном случае указывает на ошибку и вводит значение, соответствующее ей – «перепроверить».

6) ЛЕВСИМВ

Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.

Функция состоит из 2 аргументов и представлена формулой: =ЛЕВСИМВ(текст;), где:

• «текст» – текстовая строка, содержащая знаки, которые необходимо извлечь;
• «число знаков» необязательный аргумент, указывает на количество извлекаемых знаков.

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

К примеру, если вы хотите, чтобы тайтлы были максимально лаконичными и состояли из 60 знаков, функция отсчитает первые 60 символов и покажет, как будет выглядеть тот или иной тайтл. Для этого необходимо составить формулу: =ЛЕВСИМВ(А5;60), где А5 – адрес рассматриваемой ячейки, «60» – число извлекаемых символов.

Пригодится в работе: «Подсчет числа символов в тексте онлайн»

7) ПСТР

Функция ПСТР позволяет извлечь необходимое количество символов внутри текста, начиная с указанной позиции.

Формула функции состоит из 3 аргументов:

=ПСТР(текст;начальная_позиция;число_знаков).

«Текст» – строка, содержащая символы, которые нужно извлечь.

«Начальная позиция» – позиция знака, с которого начинается извлекаемый текст.

«Число знаков» – количество извлекаемых символов.

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

8) ПРОПИСН

Функция ПРОПИСН делает все буквы в тексте прописными.

Формула функции:

=ПРОПИСН(текст)

«Текст» здесь – текстовый элемент или ссылка на ячейку.

9) СТРОЧН

Функция СТРОЧН делает все буквы в тексте строчными.

Формула функции:

=СТРОЧН(текст)

Аргумент «текст» – текстовый элемент или адрес ячейки.

10) ПОИСКПОЗ

Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.

Формула функции:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления).

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

Рассмотрим подробнее аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:

1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);

0 – точное совпадение;

-1 – наименьшее значение, которое больше или равно искомому.

Рассмотрим следующий пример. Здесь я попыталась узнать, какой из запросов в приведенной таблице имеет количество переходов, которое равно или меньше 900.

Формула функции здесь: =ПОИСКПОЗ(900;B2:B6;1). 900 – искомое значение, B2:B6 – просматриваемый массив, 1 – тип сопоставления (меньше или равно искомому). Результат – «3», то есть третья позиция в указанном диапазоне.

11) ДЛСТР

Функция ДЛСТР позволяет определить длину текста, содержащегося в указанной ячейке.

Формула функции имеет всего один аргумент – текст (номер ячейки):

=ДЛСТР(текст)

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

12) СЦЕПИТЬ

Функция СЦЕПИТЬ позволяет объединить несколько текстовых элементов в одну строку. В формуле для объединения элементов указываются как номера ячеек, содержащих текст, так и сам текст. Можно указать до 255 элементов и до 8192 символов.

Для того чтобы объединить текстовые элементы без пробелов, используются следующие формулы:

=СЦЕПИТЬ(текст1;текст2;текст3);

Аргумент «текст» – текстовый элемент или ссылка на ячейку.

В приведенном ниже примере введена следующая формула: =СЦЕПИТЬ(А2;B2;С2)

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

=СЦЕПИТЬ(текст1;» «;текст2;» «;текст3;» «)

В следующем примере функция представлена формулой: =СЦЕПИТЬ(A2;» «;B2;» «;C2)

Существует и другой вариант добавления пробелов в формулу функции – введение слов, заключенных в кавычки вместе с пробелами:

=СЦЕПИТЬ(«текст1 «;»текст2 «;»текст3 «)

13) ПРОПНАЧ

Функция ПРОПНАЧ преобразует заглавные буквы всех слов в тексте в прописные (верхний регистр), а все остальные буквы – в строчные (нижний регистр).

Функция очень проста в использовании и представлена короткой формулой, имеющей всего один аргумент:

=ПРОПНАЧ(текст)

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

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

14) ПЕЧСИМВ

Функция ПЕЧСИМВ позволяет удалить все непечатаемые знаки из текста.

Формула данной функции:

=ПЕЧСИМВ(текст)

В приведенном примере текст в ячейке A1 содержит непечатаемые знаки конца абзаца.

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

15) СЖПРОБЕЛЫ

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

Формула функции проста: =СЖПРОБЕЛЫ(номер_ячейки)

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

16) НАЙТИ

Функция НАЙТИ позволяет обнаружить искомый текст внутри текстовой строки и указывает на начальную позицию этого текста относительно начала просматриваемой строки.

Функция НАЙТИ состоит из 3 аргументов и представлена формулой:

=НАЙТИ(искомый_текст;просматриваемый_текст;);

«Начальная позиция» – необязательный аргумент, обозначающий символ, с которого нужно начать поиск.

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

В данном примере функция представлена следующей формулой: =НАЙТИ(«чай»;A4)

17) ИНДЕКС

Функция ИНДЕКС позволяет возвращать искомое значение.

Формула функции ИНДЕКС имеет следующий вид:

=ИНДЕКС(массив; номер_строки; )

«Номер столбца» – необязательный аргумент.

Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.

18) СОВПАД

Данная функция проверяет идентичность двух текстов, и, если они совпадают, выдает значение ИСТИНА, если же различаются – значение ЛОЖЬ.

Формула функции: =СОВПАД(текст1;текст2)

Пары слов из строк 1 (A1 и B1) и 2 (A2 и B2) различны по написанию, поэтому функция выдает значение ЛОЖЬ, а слова из 3-й строки абсолютно идентичны, поэтому определяются как ИСТИНА.

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

19) ИЛИ

Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один аргумент в формуле имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Формула функции:

=ИЛИ(логическое_значение1;;…)

Здесь «логическое значение1» – обязательный аргумент, остальные аргументы – необязательные. В формулу можно добавлять от 1 до 255 логических значений.

Формула в данном примере выдает значение ИСТИНА, так как 2 из 3 аргументов имеют значение ИСТИНА.

20) И

Функция И возвращает значение ИСТИНА, если все аргументы в формуле имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.

Функция может содержать множество аргументов и имеет формулу:

=И(логическое_значение1;;…)

«Логическое_значение1» – обязательный аргумент, остальные аргументы – необязательные.

В этом примере все аргументы имеют значение ИСТИНА, поэтому и результат ее соответствующий.

Функции И и ИЛИ очень просты в использовании, но если сочетать их вместе или в комбинации с другими функциями (ЕСЛИ и НЕ), можно вывести более сложные и интересные формулы.

21) СМЕЩ

Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или группы ячеек на указанное число строк и столбцов.

Функция состоит из 5-ти аргументов и представлена следующей формулой:

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;)

Рассмотрим каждый из аргументов:

1. «Ссылка». Данный аргумент представляет собой ссылку на ячейку или диапазон ячеек, от которых вычисляется смещение.
2. «Смещение по строкам». Этот аргумент показывает количество строк, которые необходимо отсчитать, чтобы переместить левую верхнюю ячейку массива или одну ячейку в нужное место. Значение аргумента может быть положительным (если отсчет строк ведется вниз) и отрицательным числом (если отсчет строк ведется вверх).
3. «Смещение по столбцам». Здесь указывается количество столбцов, которые нужно отсчитать для того, чтобы переместить ячейку или группу ячеек влево или вправо. Левая верхняя ячейка диапазона при этом должна находиться в указанном месте. Значение аргумента может быть положительным (если отсчет столбца ведется вправо) и отрицательным числом (если отсчет столбца ведется влево).
4. «Высота» – необязательный аргумент. Здесь указывается число строк возвращаемой ссылки. Значение данного аргумента должно быть положительным числом.
5. «Ширина» – необязательный аргумент. Здесь указывается число столбцов возвращаемой ссылки. Значение аргумента должно быть положительным числом.

Рассмотрим пример использования функции СМЕЩ, имеющую следующую формулу: =СМЕЩ(А4;-2;2).

В данной формуле A4 – ссылка на ячейку, от которой вычисляется смещение, С2 – ячейка, на которую ссылается ячейка А4, а в ячейке E2 введена формула с результатом «27» – возвращаемая ссылка.

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

Мы рассмотрели самые интересные и популярные функции Excel. Могут ли они быть полезны интернет-маркетологу? Безусловно. Они помогут при анализе данных страниц сайта, подсчете количества символов в тайтле и description, преобразовании текста, поиске различных элементов в таблице. Несмотря на то, что некоторые из представленных функций очень просты и понятны, это не умаляет их ценности ни для обычного пользователя, ни для интернет-маркетолога.

Вы хотите стать специалистом с хорошей зарплатой и возможностью карьерного роста? Приходите на онлайн-курсы TeachLine от «Текстерры» за новой профессией и новыми перспективами.

Функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

Средства Excel для визуализации данных бизнес-анализа

Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.

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

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

Именно поэтому для экономического (т. е. постфактного) анализа работы компании широко применяются специализированные программы, а вот для целей бизнес-анализа на практике предпочитают пользоваться всем известным табличным редактором Excel. И для этого есть веские основания, которые мы и рассмотрим.

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

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

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

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

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

Работают со сводными таблицами из вкладки меню функции «Анализ» (рис. 1). На этой вкладке также настраиваются параметры сводной таблицы и источники данных (откуда берется информация).

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

Обратите внимание!

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

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

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

К сведению

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

Для начала работы с функцией ВПР ставим курсор в выбранную ячейку конечной таблицы и с помощью мастера функций выбираем значение ВПР — откроется диалоговое окно, в котором можно выбрать параметры исходной таблицы (рис. 2).

Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.

Функция ЕСЛИ, предусмотренная функционалом Excel, также популярна у бизнес-аналитиков, но она применяется чаще всего не при анализе информации, а при построении различного рода прогнозов и сценариев результатов деятельности компании. Суть функции в том, что в заданной ячейке выводится один результат при выполнении определенного условия и другой — при невыполнении этого условия.

MS Excel — универсальный инструмент, для работы с таблицами, который позволяет быстро осуществлять различные экономико-статистические расчеты, использовать графические инструменты и много-много других функций.

Наверняка, 99% (если не все 100%) из вас знакомы с ним. Поэтому, давай пробежимся по основным функциям MS Excel. Для удобства работы будем использовать Google Sheets, аналог Excel, который позволяет работать с таблицами в браузере, но имеет почти идентичный функционал.

Операции с ячейками

Объединение значений ячеек

Бывает такое, что нам необходимо объединить значения двух ячеек в одну. Когда строк в таблице очень много — вручную физически это сделать очень затратно. Да что уж там, даже при наличии 10 строк в таблице.

Для сцепки значений ячеек используется амперсанд & либо формула СЦЕПИТЬ, то есть итоговая формула ячейки может быть такой:

Или такой:

Получение значений из других файлов Google Sheets

Иногда требуется и такое. Данные находятся в разных файлах и одни из них нужно использовать в файле с другими. Для этого достаточно воспользоваться формулой IMPORTRANGE, где нужно указать URL адрес таблицы, лист и ячейку, из которой берутся данные:

Поиск наибольшего и наименьшего значения

Функция МИН возвращает минимальное значение из указанного диапазона. А функция МАКС — наибольшее значение. Форма записи этих функций достаточно проста.

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

Простейшие функции

Суммирование

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

Среднее значение

Чтобы узнать среднее количество транзакций в день, воспользуемся формулой СРЗНАЧ.

Функции с условиями

Сравнение значений

Чтобы выполнить логические сравнения значений, используют функцию ЕСЛИ. Например, план продаж в день составляет 100 транзакций, и необходимо в дополнительном столбце указать был ли выполнен план продаж или нет. По ходу проверки ставим «нет», если не выполнен и «да» — при успешном выполнении плана.

Сравнение по нескольким условиям

К сожалению отдельной формулы, которая поможет быстренько перечислить сразу несколько условий подряд в Excel нет, но это не мешает нам использовать формулу ЕСЛИ немного по другому. Например, нам нужно учитывать, был ли выполнен план транзакций из предыдущего пункта, но при условии, что количество сеансов было не менее 10000 в день.
Для этого в третью переменную функции ЕСЛИ мы прописываем еще одну функцию ЕСЛИ. То есть, если первое условие не выполняется (в данном случае план продаж ниже указанного), то Excel поставит «нет», если план выполняется, Excel перейдет к следующему условию.

Сравнение и суммирование

Далее, нам хочется посчитать, сколько всего транзакций в декабре было в дни с выполненным планом, но дополнительный столбец использовать не хочется. Воспользуемся функцией СУММЕСЛИ. Здесь мы суммируем количество транзакций при условии, что выполнение плана = «да».

Сравнение по нескольким условиям и суммирование

Если нам необходимо добавить еще одно условие при суммировании, например, сеансов не менее 10000, используют функцию СУММЕСЛИМН.

Сравнение и подсчет

Теперь можем посчитать, сколько же всего дней план выполнялся и для этого воспользуемся функцией СЧЁТЕСЛИ.

Сопоставление данных

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

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

Далее в нашей таблице создаем столбец «Новые пользователи», заносим в него формулу ВПР, которая содержит:

• Искомую ячейку;
• Таблицу с данными которые нужно перенести (первый столбец в таблице должен начинаться со столбца с искомыми значениями);
• Номер столбца в этой таблице, значения которого нужно перенести;
• Тип совпадений значений, нам нужно точное совпадение, поэтому ставим «0» или «Ложь».

Ссылка на файл с примером.

Домашнее задание

1. Посмотри полный список доступных функций.
2. Потренируйся применять функции описанные в данном уроке.

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

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

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

Полезные сочетания клавиш

Сэкономить время при работе в программе Excel помогают следующие сочетания клавиш.

Общие действия

Работа с таблицами

• Перемещение к краю таблички: Ctrl (cmd) + (стрелки).
• Перемещение к краю таблички с выделением: Ctrl + Shift + (стрелки).
• Перемещение выделенного диапазона — удерживать Ctrl для копирования.
• Смещение диапазона — перетаскивая, удерживать Shift.
• Вставка диапазона со смещением — удерживать Ctrl + Shift.
• Вставить гиперссылку: Ctrl (Cmd) + K.

Набор текста

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

Шаблоны Excel-таблиц

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

Рассмотрим некоторые из них.

Встроенные Excel-шаблоны для маркетологов

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

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

Бюджет маркетингового плана

Пример от templates.office.com

Стандартный бюджет мероприятия

​Пример от templates.office.com

Бюджет канального маркетинга

​Пример от templates.office.com

Планировщики идей

Еще до того как приступить к бюджетированию маркетинговой кампании, специалисты работают над генерированием идей и постановкой маркетинговых целей.

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

​Пример от templates.office.com

Шаблоны для digital-маркетологов

В сети предлагается множество шаблонов, созданных на базе обычной таблицы Excel и модифицированных под задачи в области digital. Компании Hubspot и Vivial предоставляют их бесплатно при условии регистрации на сайте компании. Ознакомимся с некоторыми из них.

Работу современного маркетолога сложно представить без четкого календарного плана размещения публикаций на сайте.

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

Пример от Hubspot.com

Работая с постами в социальных сетях, маркетологи стремятся не только повысить интерактивность посетителей, но и увеличить их количество.

Шаблон таблицы размещения публикаций в социальных сетях помогает наиболее эффективно распределить посты по дням, часам и медийным каналам. Кстати, благодаря своей функции автоматического подсчета знаков, Excel особенно полезен в работе с социальной сетью Twitter.

Пример от vivial.net

На базе Excel создан и этот вариант единого детального плана для контент-маркетинга на месяц. Шаблон позволяет не только планировать различные формы подачи контента в рамках кампаний, но и одновременно распределять их для размещения на различных медиа-каналах.

Пример от Hubspot.com

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

Пример от Hubspot.com

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

Пример от Hubspot.com

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

Пример от Hubspot.com

Секреты Excel-форматирования

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

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

Рассмотрим некоторые из них.

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

Найти стили для PC можно по следующему пути: Home > Styles > Format as Table, для Mac: Tables > Table Styles

Пример от searchengineland.com

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

Чтобы воспользоваться этой функцией, в разделе Conditional Formatting необходимо выбрать одно из предложенных условий, либо создать свое. Для Mac функция расположена в разделе Format.

Пример от searchengineland.com

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

Сводные таблицы дают возможность фильтровать, группировать и сравнивать данные.

Для того чтобы создать Сводную таблицу на Mac необходимо в разделе Data выбрать функцию Pivot Table. Для PC: Insert — Table — Pivot Table.

Программа предложит создать сводную таблицу либо на листе с исходной таблицей, либо на отдельном листе. Далее на странице появится конструктор сводных таблиц (Pivot Table Builder), предлагающий выбрать нужные для анализа поля и указать анализируемое значение. В списке полей располагаются названия колонок исходной таблицы.

Пример от i-media.ru

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

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

Digital-маркетологи часто используют эту функцию для прогнозирования трафика посещений сайтов или просмотра постов и публикаций. На основе данных сводных таблиц получают информацию, необходимую для запуска наиболее эффективных кампаний в Adwords или Метрике.

Обработка графиков

Разобравшись с таблицами, перейдем к следующим средствам визуализации данных: созданию и обработке графиков.

Отличным подспорьем в работе с графиками является функция «Рекомендованный график». Программа предлагает сравнить несколько версий графической визуализации данных перед тем, как сделать выбор в пользу того или иного варианта. Чтобы воспользоваться этой функцией нужно выбрать Recommended Charts в разделе Insert.

Пример от blogs.office.com

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

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

Также программа позволяет создавать брендированные графики. Все эти функции доступны в разделе Insert — Charts.

Неудачное и удачное расположение легенд от searchengineland.com

Улучшение фона графика

Пример от searchengineland.com

Предварительная сортировка данных

До и после от searchengineland.com

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

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

Рассмотрим пять неочевидных, но интересных для маркетологов формул.

Формула «вертикальный просмотр» (ВПР, VLOOKUP)

Формула позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Она особенно популярна при работе над SEO-оптимизацией и используется для анализа статистики запросов.

Пример от texterra.ru

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

Формула «частота» и построение гистограмм (ЧАСТОТА, FREQUENCY)

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

В первом столбце этой таблицы (bins) указываем большие значения интересующих нас интервалов (то есть для интервала от 80 до 89 в столбце для расчета мы указываем 89). Во втором столбце (Frequency) мы введем формулу «Частота» с указанием диапазона данных для поиска и созданного столбца с интервалами.

Программа автоматически посчитает распределения частот в вертикальном массиве.

После этого нам остается лишь создать еще один столбец с более подробным описанием интервала и создать график в виде гистограммы.

Формула «если» (ЕСЛИ, IF)

Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов:
1) Если сравнение истинно;
2) Если сравнение ложно.

В своем самом простом виде формула выглядит следующем образом: IF(logical_test, value_if_true, value_if_false).

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

Пример от support.office.com

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

Формула «сцепить» (СЦЕПИТЬ, CONCATENATE)

Эта формула позволяет объединить текстовое содержимое нескольких ячеек в одну. Для этого в формуле можно указывать как номера ячеек с нужным текстом, так и сам текст. Всего можно указать до 255 элементов и до 8192 символов.

Пример от distilled.net

Формула «длина строки» (ДЛСТР, LEN)

Формула позволяет определить длину текста в указанной ячейке. Она является незаменимой для работы с рекламными кампаниями в Adwords и Метрике, а также социальной сетью Twitter.

Пример от texterra.ru

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

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации.

Читать еще

• Как настроить сводки, виджеты и сегменты в Google Analytics
• Инструкция: Как оптимизировать сайт на WordPress для SEO
• 44 способа сбора email-адресов

Обучение

• Бесплатный курс «Курс молодого бойца: первые шаги в digital»
• Программа обучения «Excel: инструменты работы с данными для маркетологов и аналитиков»
• Программа обучения «Веб-аналитика: что нужно знать интернет-специалисту»
• Офлайн-курс «Директор по онлайн-маркетингу»
• Офлайн-курс «Руководитель digital-продукта»