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

Функции Excel для маркетологов

Полезные функции Excel

Терминология Excel

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

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

А теперь можем переходить к описанию функций Excel.

ВПР (вертикальный просмотр)

Если нужно найти информацию в строке с текстом или в определенном диапазоне ячеек и перенести в другую таблицу, пригодится этот инструмент «Экселя».

Синтаксис ВПР

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

Формула функции ВПР

Образец формулы ВПР

Разберемся с аргументами, входящими в эту функцию.

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

- «ЛОЖЬ» – ищет в первом столбце заданное значение в точном отображении;

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

Пример использования ВПР без интервального просмотра

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

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

Пример использования функции ВПР

Пример применения ВПР

Функция демонстрирует, сколько раз просматривались запросы (допустим, 45 113). В этом примере не использовался интегральный просмотр. Дальше мы рассмотрим, как работает ВПР с этим аргументом, позволяющий получить более точные данные.

Примеры с интегральным просмотром

Если задействовать функцию ИСТИНА, можно получить примерный показатель. Предположим, нужно найти, сколько раз запускался поиск по запросу «купить телевизор». При искомом значении 1 000 000 мы можем получить приближенную к нему цифру, например 995 995.

Когда используется функция ЛОЖЬ, выдается показатель, полностью соответствующий заданному. В нашем случае это 1 000 000.

Плюсы и минусы вертикального просмотра

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

ЕСЛИ

Эта функция проверяет условия, которые заданы, оценивает сравнение и выдает ответ, истинно оно или ложно.

Синтаксис ЕСЛИ

У функции три аргумента, и ее формула выглядит так.

Формула функции ЕСЛИ

Образец формулы ЕСЛИ

Здесь элементы расшифровываются следующим образом:

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

Пример использования ЕСЛИ

Для начала продемонстрируем, как работает простой вариант этой функции. Допустим, нужно определить, кто из менеджеров выполнил план продаж. Они должны были получить выручку на сумму не меньше 40 тыс. руб., и при помощи функции ЕСЛИ мы будем определять, все ли справились с задачей. Данные о выручке содержатся в нашем случае в столбце С2. Чтобы получить нужный результат, вводим формулу такого вида:

=ЕСЛИ(С2>40000;«план выполнен»;«выручки недостаточно»),

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

значение при ложности формулы – «выручки недостаточно».

Усложненная ЕСЛИ

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

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

Усложненный вариант ЕСЛИ

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

Определение занятости ячейки

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

Пример использования функции ЕСЛИ с целью определить, пустая ячейка или нет

Пример применения ЕСЛИ для определения пустоты ячейки

Тот же результат можно получить и без объединения двух функций. Для этого достаточно ЕПУСТО заменить на формулу «номер ячейки» = «», то есть пустота.

Плюсы и минусы ЕСЛИ

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

ЕСЛИ – это основа для ряда формул «Экселя», три из которых будут рассмотрены дальше.

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

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

Синтаксис СУММЕСЛИ

У этой функции три аргумента, ее формула имеет такой вид.

Формула функции СУММЕСЛИ

Образец формулы СУММЕСЛИ

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

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

Пример использования СУММЕСЛИ

Допустим, нужно объединить информацию по запросам, по которым переходили больше 10 тысяч раз. Диапазон – В2:В12. При таких исходных данных формула будет выглядеть следующим образом.

Пример использования функции СУММЕСЛИ

Пример применения СУММЕСЛИ

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

Синтаксис СУММЕСЛИМН

У этой функции такая формула.

Формула функции СУММЕСЛИМН

Образец формулы СУММЕСЛИМН

Здесь обязательными являются только два аргумента: «условие 1» и его диапазон. Все остальные можно и не использовать.

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

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

Синтаксис СЧЁТЕСЛИ

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

Формула функции СЧЕТЕСЛИ

Образец формулы СЧЕТЕСЛИ

Пример использования СЧЁТЕСЛИ

Будем считать, сколько в диапазоне В2:В12 ключевых фраз, по которым переходили более 10 тысяч раз. Для этого потребуется такая формула.

Пример использования функции СЧЕТЕСЛИ

Пример применения СЧЕТЕСЛИ

Элементы подсчитываются без ссылок на соответствующие ячейки.

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

Синтаксис

В формуле каждому из условий соответствует один диапазон.

Формула функции СЧЕТЕСЛИМН

Образец формулы СЧЕТЕСЛИМН

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

ЕСЛИОШИБКА

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

Синтаксис ЕСЛИОШИБКА

У этой функции два аргумента.

Формула функции ЕСЛИОШИБКА

Образец формулы ЕСЛИОШИБКА

В качестве «значения» указывается формула, по которой осуществляется проверка на предмет присутствия ошибок. А в случае их выявления заданное значение проставляется в ячейке.

Пример использования ЕСЛИОШИБКА

Допустим, аналитический счетчик сломан, поэтому в ячейке с количеством посетителей вместо соответствующего показателя стоит ноль. При этом количество покупок не нулевое. Как это возможно? Использование ЕСЛИОШИБКА позволяет выявить проблему и продемонстрировать ее. Так как нельзя делить на ноль, прописывается то значение, которое предусмотрено на случай ошибки.

ЛЕВСИМВ

Назначение этой функции – выделение определенного количества символов в строке слева.

Синтаксис ЛЕВСИМВ

Формула функции ЛЕВСИМВ

Образец формулы ЛЕВСИМВ

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

Пример использования ЛЕВСИМВ

Эта функция дает возможность оценить, какой вид будут иметь заголовки страниц и публикаций. Допустим, вы хотите сделать их лаконичными (длиной не больше 60 символов) и запускаете формулу на 100 первых знаках. При этом ячейка, которая будет рассматриваться, имеет адрес В3. Значит, потребуется такая формула: =ЛЕВСИМВ(В3;100).

ПСТР

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

Синтаксис ПСТР

Формула состоит из трех обязательных аргументов и имеет такой вид.

Формула функции ПСТР

Образец формулы ПСТР

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

Пример использования ПСТР

Эта функция позволяет убрать слова в начале тайтлов и таким образом упростить их. Допустим, нужно извлечь 80 знаков в ячейке В2, начиная с десятой позиции, на которой находится символ А. Для этого потребуется формула такого вида:

=ПСТР(В2;10;80).

ПРОПИСН

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

Синтаксис ПРОПИСН

В формуле всего один аргумент, а выглядит она так.

Формула функции ПРОПИСН

Образец формулы ПРОПИСН

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

Пример использования ПРОПИСН

Пример использования функции ПРОПИСН

Пример применения ПРОПИСН

Строчные буквы текста, размещенного в ячейке А2, стали прописными.

СТРОЧН

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

Синтаксис СТРОЧН

Формула максимально простая и состоит из одного аргумента «текст».

Формула функции СТРОЧН

Образец формулы СТРОЧН

В нем указывается текстовый элемент или адрес ячейки.

Пример использования СТРОЧН

Функция работает по аналогии с ПРОПИСН. Достаточно указать в формуле текст или ячейку, в которой он размещен. Например, чтобы сделать прописные буквы в ячейке С3 строчными, нужна формула такого вида:

=СТРОЧН(С3).

ПОИСКПОЗ

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

Синтаксис ПОИСКПОЗ

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

Формула функции ПОИСКПОЗ

Образец формулы ПОИСКПОЗ

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

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

Пример использования ПОИСКПОЗ

Предположим, веб-маркетологу нужно определить, по каким запросам из таблицы было 800 и больше переходов. Будем искать не точное значение, а большее или равное искомому в диапазоне В2:В12. Для этого потребуется такая формула:

=ПОИСКПОЗ(800;B2:B12;-1).

Результат использования этой функции – демонстрация позиции строки с подходящими данными в выбранном диапазоне (не в таблице в целом).

ДЛСТР

Назначение этой функции – определение длины содержащегося в определенной ячейке текста.

Синтаксис ДЛСТР

Формула максимально простая и состоит из единственного аргумента.

Формула функции ДЛСТР

Образец формулы ДЛСТР

Можно как вводить сам текст, который нужно оценить, так и указывать ячейку, в которой он находится.

Пример использования ДЛСТР

Эту функцию можно применять, например, для проверки description на предмет выполнения требований к его длине. Допустим, он содержится в ячейке В3. Чтобы запустить ДЛСТР, достаточно указать местонахождение текста в формуле. В результате она будет иметь такой вид:

=ДЛСТР(В3).

Функция выдаст количество символов.

СЦЕПИТЬ

Если нужно объединить несколько текстовых фрагментов в один текст, можно без особого труда это сделать при помощи такой функции. При этом допускается упоминание ячейки с текстом или его самого. Максимальное количество символов – 8192, а элементов – 255.

Синтаксис СЦЕПИТЬ

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

Формула функции СЦЕПИТЬ

Образец формулы СЦЕПИТЬ

Пример использования СЦЕПИТЬ

Допустим, нам необходимо объединить в одну строку тексты из ячеек В3, C4 и D5. Для этого потребуется формула такого вида:

=СЦЕПИТЬ(В3;С4;D5).

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

Добавление пробелов

С этой целью нужно усовершенствовать стандартную формулу.

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

Формула функции СЦЕПИТЬ с пробелами, отделенными кавычками

Образец формулы СЦЕПИТЬ со знаками пробелов в кавычках

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

Формула функции СЦЕПИТЬ с пробелами в каждом аргументе

Образец формулы СЦЕПИТЬ с пробелами в аргументах

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

ПРОПНАЧ

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

Синтаксис ПРОПНАЧ

Формула максимально простая.

Формула функции ПРОПНАЧ

Образец формулы ПРОПНАЧ

Пример использования ПРОПНАЧ

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

=ПРОПНАЧ(В2).

Это ценный инструмент для работы со списками названий стран, населенных пунктов, ФИО и т. п.

ПЕЧСИМВ

С помощью этой функции из текста удаляются непечатные знаки.

Синтаксис ПЕЧСИМВ

Формула функции ПЕЧСИМВ

Образец формулы ПЕЧСИМВ

Пример использования

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

=ПЕЧСИМВ(С3).

СЖПРОБЕЛЫ

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

Синтаксис СЖПРОБЕЛЫ

Формула функции СЖПРОБЕЛЫ

Образец формулы СЖПРОБЕЛЫ

Пример использования

В исходном тексте (ячейка В2) между словами больше одного пробела. Удаляем лишние при помощи следующей формулы:

=СЖПРОБЕЛЫ(В2).

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

НАЙТИ

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

Синтаксис НАЙТИ

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

Формула функции НАЙТИ

Образец формулы НАЙТИ

Пример использования

Если, например, нужно найти в ячейке К12 слово «телевизор», вносим эти исходные данные в формулу. Получается такой результат:

=НАЙТИ(«телевизор»;К12).

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

ИНДЕКС

Такая функция нужна для возврата искомого значения.

Синтаксис

Формула функции ИНДЕКС

Образец формулы ИНДЕКС

Аргумент «номер столбца» используется по желанию, а остальные обязательны.

Пример использования функции ИНДЕКС

Допустим, нужно отобразить содержимое ячейки, которая входит в диапазон А3:С4 и находится в третьей строке третьего столбца этого массива. В таком случае формула ИНДЕКС будет выглядеть так:

=ИНДЕКС(А3:С4;3;3).

Если совместить эту функцию с ПОИСКПОЗ, можно заменить ВПР.

СОВПАД

При помощи такого инструмента можно сравнивать тексты. Если они идентичны, функция выдаст результат «ИСТИНА», а если обнаружатся отличия – «ЛОЖЬ».

Синтаксис СОВПАД

Формула функции СОВПАД

Образец формулы СОВПАД

Пример использования

Один текст находится в ячейке С5, а второй – в D5, формула, необходимая для их сравнения, должна выглядеть так:

=СОВПАД(С5;D5).

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

ИЛИ

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

Синтаксис ИЛИ

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

Образец формулы ИЛИ

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

Пример использования ИЛИ

Предположим, нужно установить, как соотносятся разные показатели. Например, в ячейке А3 указано число 14, в В3 – 28, а в С3 – 7. Сравним их при помощи такой формулы.

Пример использования функции ИЛИ

Образец применения ИЛИ

Результатом будет «ИСТИНА», поскольку два из трех аргументов истинны.

И

При использовании этой функции выдается результат «ИСТИНА», если истинным является каждый из входящих в формулу аргументов. В случае ложности хотя бы одного из них общим результатом будет также «ЛОЖЬ».

Синтаксис И

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

Образец формулы И

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

Пример использования

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

=И(А3=В3/2;В3=С3/2;С3=А3/2).

Результат ее использования – «ЛОЖЬ», так как второй аргумент ложный.

Если такие простые функции, как И и ИЛИ, использовать вместе либо в сочетании с НЕ или ЕСЛИ, можно получить сложные и еще более полезные формулы.

СМЕЩ

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

Синтаксис СМЕЩ

Формула довольно сложная, в ее состав входит пять аргументов.

Формула функции СМЕЩ

Образец формулы СМЕЩ

• «Ссылка» – указание ячейки или диапазона, от которых должен вестись отсчет.
• «Смещение по строкам» – их количество, которое нужно отсчитать. Этот показатель может быть как положительным, так и отрицательным.
• «Смещение по столбцам» – их число, на которое необходимо переместиться для попадания в нужный диапазон.
• «Высота» – количество строк ссылки, которая возвращается.
• «Ширина» – число столбцов.

Показатели высоты и ширины использовать необязательно.

Пример использования

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

=СМЕЩ(В5;-3;1).

В ответ функция продемонстрирует содержимое ячейки С2.

Подведем итоги

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