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

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

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

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

Итак, поехали!
1) ВПР
Функция ВПР позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Аббревиатура ВПР расшифровывается как «вертикальный просмотр».
Синтаксис
Данная функция состоит из 4 аргументов и представлена следующей формулой: =ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Рассмотрим каждый из аргументов:

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

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

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

Результат – значение ячейки, содержащей искомое слово «планшет»: «31325»

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

В данном случае применен интервальный просмотр с функцией ИСТИНА, которая выдает приблизительное значение – «886146» («купить машину») при искомом значении «900000»

Применение интервального просмотра с функцией ЛОЖЬ позволяет найти значение, в точности равное искомому

Функция ВПР является одной из самых популярных функций Excel, достаточно сложной для понимания, но чрезвычайно полезной.
2) ЕСЛИ
Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: 1) Если сравнение истинно; 2) Если сравнение ложно.
Синтаксис
Формула функции состоит из трех аргументов и выглядит следующим образом: =ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»), где:

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

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

Выполнение плана продаж здесь обусловлено наличием суммы выручки больше 30000 рублей

Для того чтобы узнать, кто из продавцов выполнил план, а кто нет, нужно ввести следующую формулу: =ЕСЛИ(B2>30000;«План выполнен»;«План не выполнен»)
Логическое выражение здесь – формула «B2>30000».
«Значение если истина» – «План выполнен».
«Значение если ложь» – «План не выполнен».
Вложенные функции ЕСЛИ
Помимо обычной функции ЕСЛИ, которая выдает всего 2 результата – «истина» и «ложь», существуют вложенные функции ЕСЛИ, выдающие от 3 до 64 результатов.
В данном случае формула может вмещать в себя несколько функций

В этом примере одна функция вложена в другую и всего внесено 3 результата

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

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

Вот простой пример совмещения 2 функций, позволяющего выявить верный результат

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

Формула в данном примере очень проста и не требует добавления других функций

ЕСЛИ – одна из самых популярных функций в Excel, простая и удобная в использовании. Она помогает определить истинность тех или иных значений, получить результаты по разным данным и выявить пустые ячейки, к тому же ее можно использовать в сочетании с другими функциями.
Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧЁТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧЁТЕСЛИ и ЕСЛИОШИБКА.
3) СУММЕСЛИ и СУММЕСЛИМН
Функция СУММЕСЛИ позволяет суммировать данные, соответствующие определенному условию, находящиеся в указанном диапазоне.Синтаксис
Функция состоит из 3 аргументов и имеет формулу: =СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])
«Условие» – аргумент, определяющий какие именно ячейки нужно суммировать. Это может быть текст, число, ссылка на ячейку или функция. Обратите внимание на то, что условия с текстом и математическими знаками необходимо заключать в кавычки.
«Диапазон суммирования» – необязательный аргумент, который позволяет указать на ячейки, данные которых нужно суммировать, если они отличаются от ячеек, входящих в диапазон.Пример
В приведенном ниже примере функция суммировала данные запросов, количество переходов по которым больше 100000.

В данном примере функция вычислила сумму (519414), которая является сложением ячеек (B2:B4), удовлетворяющих условию (>100000). Формула: =СУММЕСЛИ(B2:B6;«>100000»)

Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.
Синтаксис
Формула данной функции имеет следующий вид: =СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.
4) СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.
Синтаксис
Формула функции: =СЧЁТЕСЛИ(диапазон;критерий)
«Диапазон» – группа ячеек, которые нужно подсчитать.
«Критерий» – условие, согласно которому выбираются ячейки для подсчета.
Пример
В приведенном примере функция подсчитала количество ключей, число переходов по которым больше 100000, – в итоге получилось 3 ключа.

Функция подсчитывает количество элементов, не указывая ссылок на них

В функции СЧЁТЕСЛИ можно использовать только один критерий. Если же нужно сделать подсчет по нескольким условиям, можно применить функцию СЧЁТЕСЛИМН.
Синтаксис
Функция позволяет подсчитать количество ячеек, соответствующих нескольким заданным условиям. Каждому условию соответствует один вариант диапазона ячеек.
Формула функции: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные же аргументы необязательны. Можно использовать до 127 пар диапазонов и условий.
5) ЕСЛИОШИБКА
Данная функция возвращает указанное значение, если вычисление по формуле дает ошибочный результат, правильный же результат формулы она оставляет.
Синтаксис
Функция имеет 2 аргумента и представлена формулой: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:

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

Примеры

Формула в данном примере выдает правильный результат

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

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

6) ЛЕВСИМВ
Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.Синтаксис
Функция состоит из 2 аргументов и представлена формулой: =ЛЕВСИМВ(текст;[число_знаков]), где:

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

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

Формула в данном случае следующая: =ЛЕВСИМВ(А5;60)

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

Начальная позиция – «9» (то есть 9-й символ в ячейке А3 – «К»), а число знаков – 100

8) ПРОПИСН
Функция ПРОПИСН делает все буквы в тексте прописными.
Синтаксис
Формула функции: =ПРОПИСН(текст)
«Текст» здесь – текстовый элемент или ссылка на ячейку.

9) СТРОЧН
Функция СТРОЧН делает все буквы в тексте строчными.
Синтаксис
Формула функции: =СТРОЧН(текст)
Аргумент «текст» – текстовый элемент или адрес ячейки.
Пример

Формула: =СТРОЧН(B1) позволила быстро превратить прописные буквы в строчные

Продолжение опубликую в следующем посте)