21 полезная функция Excel для интернет-маркетологов

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

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

1. Синтаксис. Это составные части функции и то, как она пишется и в какой последовательности строятся эти компоненты. Вообще синтаксис любой функции делится на две части: само ее имя и аргументы – те переменные, которые функция принимает для того, чтобы получить результат или выполнить определенное действие. Прежде чем написать формулу, необходимо поставить знак равенства, который в Excel обозначает характер ее ввода.
2. Аргументы могут быть записаны как в числовом, так и в текстовом формате. Более того, в качестве аргументов можно использовать и другие операторы, что позволяет писать полноценные алгоритмы в Excel. Аргумент, принявший значение, называется параметром функции. Но очень часто эти два слова используются как синонимы. Но на самом деле разница между ними есть. Блок аргументов начинается с открытой скобки, разделенной точкой с запятой, а блок аргументов заканчивается закрытой скобкой.

Пример готовой функции — =СУММ(A1:A5). Ну что, начнём?

Функция ВПР

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

1. Желаемое значение. Это значение, по которому будет осуществляться поиск информации, которая нам понадобится. Он служит адресом ячейки или значения либо сам по себе, либо возвращается другой формулой.
2. Стол. Это диапазон, в котором нужно искать информацию. Требуемое значение должно находиться в первом столбце таблицы. Возвращаемое значение может находиться абсолютно в любой ячейке, входящей в этот диапазон.
3. Номер столбца. Это порядковый номер (внимание – не адрес, а порядковый номер) столбца, содержащего значение.
4. Интервальный просмотр. Это логическое значение (то есть здесь нужно ввести формулу или значение, которое дает ИСТИНА or ЛЕЖА), что указывает на то, насколько структурированной должна быть информация. Если вы передадите этому аргументу значение ИСТИНА, то содержимое ячеек необходимо упорядочить одним из двух способов: по алфавиту или по возрастанию. В этом случае формула найдет значение, наиболее похожее на искомое. Если вы укажете в качестве аргумента ЛЕЖА, то будет искаться только точное значение. В этой ситуации сортировка данных столбца не так важна.

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

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

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

Здесь мы ввели число 900000, и формула автоматически нашла ближайшее к этому значение и выдала запрос «купить машину». Как мы видим, аргумент «интервальный поиск» содержит значение ИСТИНА. Если мы выполняем поиск с тем же аргументом, который имеет значение FALSE, то нам нужно записать точное число в качестве значения поиска, как на этом снимке экрана.

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

Функция IF

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

1. Прямое логическое выражение. Это критерий, который необходимо проверить. Например, минусовая погода на улице или нет.
2. Данные для обработки, если критерий верен. Формат может быть не только числовым. Вы также можете написать текстовую строку, которая будет возвращена в другую формулу или записана в ячейку. Кроме того, если значение истинно, вы можете использовать формулу, которая выполнит дополнительные вычисления. Вы также можете использовать функции ЕСЛИ, которые записываются как аргументы другой функции IF. В этом случае мы можем задать полноценный алгоритм: если критерий соответствует условию, то выполняем действие 1, если нет, то проверяем соответствие критерию 2. В свою очередь, тоже имеет место ветвление. Если таких цепочек много, то пользователь может запутаться. Поэтому для написания сложных алгоритмов по-прежнему рекомендуется использовать макросы.
3. Значение, если оно ложно. Это то же самое, только если выражение не соответствует критериям, указанным в первом аргументе. В этом случае вы можете использовать точно такие же аргументы, как и в предыдущем случае.

Для иллюстрации приведем небольшой пример.

Формула, показанная на этом снимке экрана, проверяет, превышает ли ежедневный доход 30000 XNUMX. Если да, то в ячейке отображается информация о том, что план выполнен. Если это значение меньше или равно, то выводится уведомление о том, что план не выполнен. Обратите внимание, что мы всегда заключаем текстовые строки в кавычки. То же правило применимо и ко всем остальным формулам. Теперь давайте приведем пример, показывающий, как использовать несколько вложенных функций. IF.

Мы видим, что есть три возможных результата использования этой формулы. Максимальное количество результатов, которым ограничена формула с вложенными функциями. ЕСЛИ - 64. Вы также можете проверить, пуста ли ячейка. Для выполнения такого типа проверки существует специальная формула, называемая ЭПУСТО. Позволяет заменить длинную функцию IF, который проверяет, пуста ли ячейка, с помощью одной простой формулы. В этом случае формула будет такой:

Функция ISBLANK return принимает ячейку в качестве аргумента и всегда возвращает логическое значение. Функция IF лежит в основе множества других функций, которые мы рассмотрим дальше, поскольку они играют очень большую роль в маркетинге. На самом деле их очень много, но мы сегодня рассмотрим три: СУММЕСЛИ, COUNTIF, IFERROR.

Функции СУММЕСЛИ и СУММЕСЛИ

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

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

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

Существует также вторая версия этой функции, которая записывается как СУММЕСЛИМН. С его помощью можно учесть сразу несколько критериев. Его синтаксис гибок и зависит от количества используемых аргументов. Общая формула выглядит так: =СУММИФС(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2], …). Необходимо указать первые три аргумента, а дальше все зависит от того, сколько критериев человек хочет задать.

Функции СЧЁТЕСЛИ и СЧЁТЕСЛИ

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

1. Диапазон. Это набор данных, который будет проверен и подсчитан.
2. Критерий. Это условие, которому должны соответствовать данные.

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

Максимально возможное количество критериев в этой функции — одно условие. Но аналогично предыдущему варианту вы можете использовать функцию COUNTIFSчтобы установить больше критериев. Синтаксис этой функции: COUNTIFS(диапазон_условия1, условие1, [диапазон_условия2, условие2], …).

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

Функция ОШИБКА

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

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

И пример для иллюстрации. Предположим, у нас есть такая таблица.

Мы видим, что здесь не работает счетчик, значит посетителей нет, а покупок было совершено 32. Естественно, такая ситуация не может произойти в реальной жизни, поэтому нам необходимо обработать эту ошибку. Мы так и сделали. Мы забили в функции IFERROR аргумент в виде формулы деления количества покупок на количество посетителей. А если возникает ошибка (а в данном случае это деление на ноль), то в формуле пишется «перепроверить». Эта функция знает, что деление на ноль невозможно, поэтому возвращает соответствующее значение.

СЛЕВА функция

С помощью этой функции пользователь может получить желаемое количество символов текстовой строки, которые расположены с левой стороны. Функция содержит два аргумента. В целом формула выглядит следующим образом: =ЛЕВО(текст,[количество_символов]).

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

В данном случае мы выбрали 60 символов слева от строки, содержащейся в ячейке A5. Мы хотели проверить, как будет выглядеть лаконичный заголовок.

PTR-функция

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

1. Текстовая строка. Чисто теоретически здесь можно написать строку напрямую, но гораздо эффективнее давать ссылки на ячейки.
2. Начальная позиция. Это символ, с которого начинается отсчет количества символов, описанных в третьем аргументе.
3. Количество символов. Аргумент, аналогичный аргументу предыдущей функции.

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

В нашем случае мы удалили их только с самого начала.

ВЕРХНЯЯ функция

Если вам необходимо сделать так, чтобы все слова в текстовой строке, расположенной в определенной ячейке, были написаны заглавными буквами, то вы можете использовать функцию НОРМАТИВНЫЙ. Требуется только один аргумент — текстовая строка, которую нужно сделать большой. Его можно забить как непосредственно в кронштейн, так и в ячейку. В последнем случае необходимо предоставить ссылку на него.

НИЖНИЙ функция

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

ПОИСК функция

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

1. Желаемое значение. Это текстовая строка, число, которое следует искать в диапазоне данных.
2. Просматриваемый массив. Набор данных, по которым осуществляется поиск значения, содержащегося в предыдущем аргументе.
3. Тип отображения. Этот аргумент является необязательным. С его помощью можно более точно найти данные. Существует три типа сравнения: 1 – значение меньше или равно (речь идет о числовых данных, а сам массив должен быть отсортирован по возрастанию), 2 – точное совпадение, -1 – значение больше или равно.

Для наглядности небольшой пример. Здесь мы попытались понять, какой из запросов имеет количество переходов меньше или равное 900.

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

Функция ДЛСТР

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

СЦЕПИТЬ функция

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

Функция ПРОП

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

ФУНКЦИЯ ФУНКЦИЯ

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

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

Функция ОБРЕЗАТЬ

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

НАЙТИ функция

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

1. Желаемый текст. Это строка, которая ищется.
2. Искомый текст представляет собой диапазон, в котором выполняется поиск.
3. Начальная позиция — это необязательный аргумент, указывающий первый символ, по которому осуществляется поиск.

ИНДЕКС функция

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

1. Множество. Диапазон анализируемых данных.
2. Номер строки. Порядковый номер строки в этом диапазоне. Внимание! Не адрес, а номер строки.
3. Номер столбца. То же, что и предыдущий аргумент, только для столбца. Этот аргумент можно оставить пустым.

ТОЧНАЯ функция

Этот оператор можно использовать для определения того, являются ли две текстовые строки одинаковыми. Если они идентичны, возвращается значение ИСТИНА. Если они разные – ЛЕЖА.

функция ИЛИ

Эта функция позволяет установить выбор условия 1 или условия 2. Если хотя бы одно из них истинно, то возвращаемое значение – ИСТИНА. Вы можете указать до 255 логических значений.

Функция И

Функция возвращает значение ИСТИНАесли все его аргументы возвращают одно и то же значение.

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

Функция СМЕЩЕНИЕ

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

Заключение

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