30 формул Excel для маркетолога: примеры с результатами

Маркетологу в Excel нужны не сотни функций, а около тридцати формул, которые превращают выгрузку из рекламного кабинета и CRM в отчёт: сколько потрачено, сколько пришло заявок и во что обошлась каждая. Основа — СУММЕСЛИМН и СЧЁТЕСЛИМН для сумм по каналам, ЕСЛИОШИБКА для деления на ноль, ПРОСМОТРX или ИНДЕКС с ПОИСКПОЗ, чтобы подтянуть данные из другой таблицы, и функции текста для разбора UTM-меток. Ниже каждая формула показана на одном учебном примере с результатом, так что её можно сразу повторить и проверить.

Содержание

Чек-лист: таблица, которая считает без ошибок

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

10 из 10 — можно переходить к формулам. Если не отмечены пункты про числа, даты и одинаковые названия, начните с них: на «грязных» данных правильная формула даёт неверный ответ. Как убрать повторы в списках, разобрано в отдельной статье про удаление дубликатов в Excel.

Как записывать формулы: «;» или «,»

Формула в Excel начинается со знака «=», дальше идут имя функции и аргументы в скобках. В этой статье формулы записаны так, как их вводят в русской версии Excel: русские имена функций и точка с запятой между аргументами. Какой разделитель нужен у вас, зависит не от языка программы, а от настроек: по справке Microsoft, разделитель списка «может зависеть от языкового стандарта ОС и параметров Excel», чаще всего это запятая или точка с запятой. Там же сказано, что при десятичной запятой Excel использует точку с запятой как разделитель списка, — так обычно и настроены компьютеры в России и Беларуси.

Если вы копируете формулу из англоязычного источника в русский Excel, переведите имена функций и замените запятые на точки с запятой. Неизвестное имя Excel покажет как ошибку #ИМЯ?. Английские имена приведены в скобках у каждой формулы ниже.

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

Учебная таблица для всех примеров

Все примеры ниже посчитаны на одной таблице. Это условные данные рекламных кампаний за две недели сентября, расход в условных единицах. Перенесите её на лист «Данные» в ячейки A1:G9, и вы получите те же результаты, что в статье.

Ещё два вспомогательных элемента. В столбце H считается стоимость заявки: в H2 введите =ЕСЛИОШИБКА(F2/G2;"") и протяните до H9. Получится 20, 40, пусто, 30, 22, 25, 70, 50. На листе «План» в A1:B5 лежит план заявок по кампаниям: brand_minsk — 10, search_gomel — 5, rsya_retarget — 3, lal_buyers — 5. В ячейку J2 листа «Данные» вставьте ссылку https://site.by/?utm_source=yandex&utm_medium=cpc&utm_campaign=brand_minsk — на ней разберём UTM-метки.

Суммы и подсчёты: 6 формул для отчёта

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

Порядок аргументов. В СУММЕСЛИМН диапазон суммирования идёт первым, а потом пары «диапазон условия — условие». У СУММЕСЛИ с одним условием наоборот: диапазон суммирования третий. Справка Microsoft отдельно предупреждает об этой разнице. Пример: =СУММЕСЛИ(B2:B9;"Директ";F2:F9) вернёт 340 — расход Директа за весь период.

Условие с датой. Оператор сравнения пишут в кавычках и склеивают с датой знаком «&»: ">="&ДАТА(2026;9;8). Так формула не зависит от того, в каком формате у вас записаны даты. Можно сослаться и на ячейку: ">="&K1.

Почему медиана. Среднее по столбцу H равно 36,7, медиана — 30. Разница из-за одной кампании со стоимостью заявки 70. Если среднее заметно выше медианы, ищите выбросы, прежде чем делать выводы. Пустую строку H4 обе функции пропускают: по справке Microsoft, текст в ссылках МЕДИАНА не учитывает, СРЗНАЧ ведёт себя так же. Если у канала нет ни одной подходящей строки, СРЗНАЧЕСЛИМН вернёт #ДЕЛ/0!.

СУММ — первая формула, которую осваивают, и в Excel, и в Google Таблицах она записывается одинаково.

Логика: ЕСЛИ, ЕСЛИМН, ЕСЛИОШИБКА, И/ИЛИ

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

ИСТИНА в конце ЕСЛИМН. Функция проверяет условия по порядку и возвращает значение для первого выполненного. Если не выполнено ни одно, по справке Microsoft получится ошибка #Н/Д. Поэтому последним условием ставят ИСТИНА — это значение «во всех остальных случаях». Пороги 30 и 50 здесь условные: подставьте допустимую стоимость заявки для своего проекта, как её посчитать — в разделе про метрики.

Почему не ноль в ЕСЛИОШИБКА. Частый вариант =ЕСЛИОШИБКА(F4/G4;0) показывает стоимость заявки 0 у кампании, которая потратила 60 и не принесла ни одной заявки. В отчёте это выглядит как самая выгодная кампания. Текстовая пометка или пустая строка честнее. Учтите, что ЕСЛИОШИБКА скрывает любую ошибку, в том числе опечатку в ссылке, поэтому сначала проверьте формулу без неё.

Логику ЕСЛИ удобно проговорить вслух: «если значение больше порога — один ответ, иначе — другой».

Поиск: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРX

Поиск нужен, когда данные лежат в разных таблицах: расходы — в выгрузке кабинета, статусы сделок — в CRM, планы — на отдельном листе. Ниже ищем план заявок по названию кампании на листе «План».

Четвёртый аргумент ВПР. ЛОЖЬ означает точное совпадение. Если его пропустить, функция ищет приблизительное совпадение и при неотсортированном списке вернёт чужое значение без всякой ошибки. Ноль в ПОИСКПОЗ делает то же: точное совпадение.

Режим −1 в ПОИСКПОЗX. По справке Microsoft это «точное совпадение или следующий меньший элемент». Стоимость заявки 40 в H3 меньше 50, но больше 30, поэтому функция вернёт позицию порога 30 — вторую. Дальше ИНДЕКС по этой позиции достанет подпись из соседнего столбца, например «норма» или «дорого».

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

Проверьте себя: 5 вопросов

Пять вопросов по учебной таблице. Посчитайте в уме или в Excel, потом раскройте ответ.

Текст и UTM-метки: 8 формул

Текстовые функции нужны для разбора выгрузок: названий кампаний, ссылок с UTM-метками, заголовков страниц. Примеры — на кампании brand_minsk из C2 и ссылке из J2.

ПОИСК или НАЙТИ. Обе функции возвращают позицию текста: в «brand_minsk» подчёркивание стоит шестым, поэтому ЛЕВСИМВ берёт пять символов. Разница по справке Microsoft: НАЙТИ учитывает регистр, ПОИСК — нет и вдобавок понимает подстановочные знаки «?» и «*».

UTM без Excel 2024. Если ТЕКСТДО и ТЕКСТПОСЛЕ нет, ту же метку достают связкой ПСТР и ПОИСК:

=ПСТР(J2;ПОИСК("utm_source=";J2)+11;ПОИСК("&";J2;ПОИСК("utm_source=";J2))-ПОИСК("utm_source=";J2)-11)

Результат тот же — yandex. Число 11 — длина текста «utm_source=». Формула громоздкая, поэтому для разовой задачи проще «Данные» → «Текст по столбцам» с разделителем «&».

Когда СЖПРОБЕЛЫ не помогает. Функция убирает обычные пробелы. Неразрывный пробел, который часто приходит из выгрузок и с сайтов, она не трогает: справка Microsoft советует для него ПОДСТАВИТЬ с СИМВОЛ(160). Пример: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")).

Даты: недели, конец месяца, сегодня

Даты в Excel — это числа, поэтому их можно складывать, вычитать и группировать. Для отчётов по неделям и месяцам хватает четырёх функций.

СЕГОДНЯ и ТДАТА пересчитываются. СЕГОДНЯ возвращает текущую дату, ТДАТА — дату и время. Обе обновляются при пересчёте листа, поэтому в отчёте, который вы отправили неделю назад, «сегодня» уже другое. Если дату нужно зафиксировать, вставьте её как значение, а не формулу.

Неделя с понедельника. Второй аргумент НОМНЕДЕЛИ задаёт начало недели. При 1 или без аргумента неделя начинается с воскресенья, при 2 — с понедельника. Для нумерации по ISO 8601, как в календарях, используйте 21. Дата начала недели для сводки: =A6-ДЕНЬНЕД(A6;2)+1 вернёт понедельник 07.09.2026.

Коды формата в ТЕКСТ зависят от языка. В русской справке Microsoft примеры записаны кириллицей: «ДД.ММ.ГГГГ», в английской — латиницей. Формула, скопированная из англоязычного источника, может вернуть не то, что ожидалось. И помните: ТЕКСТ превращает дату в текст, считать с ним дальше неудобно. Для группировки надёжнее хранить первое число месяца датой: =КОНМЕСЯЦА(A2;-1)+1 вернёт 01.09.2026. Календарь и сетки дат формулами разобраны в статье про календарь в Excel.

Динамические массивы: УНИК, ФИЛЬТР, СОРТ, LET

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

Несколько условий в ФИЛЬТР. Условия перемножают: (B2:B9="Директ")*(G2:G9>0) означает «и то, и другое». Для «или» условия складывают. Третий аргумент — что показать, если подходящих строк нет; без него будет ошибка, в справке Microsoft она называется #CALC!.

Ошибка #ПЕРЕНОС! Она появляется, когда результату некуда «разлиться»: в соседних ячейках уже что-то есть. Освободите место под формулой.

Что ещё есть в новых версиях. ЛЯМБДА (LAMBDA, Excel 2024 и Microsoft 365) позволяет сохранить свой расчёт, например стоимость заявки, как функцию с именем и использовать во всех отчётах команды. GROUPBY и PIVOTBY (в русской справке — ГРУПППО и СВОДПО) собирают сводку формулой вместо сводной таблицы; в списке функций Microsoft обе помечены как Microsoft 365, хотя страница СВОДПО перечисляет и другие версии — проверьте у себя. Если в вашей версии их нет, сводная таблица решает ту же задачу.

Метрики маркетолога формулами

Метрики рекламы — это простая арифметика, но в отчёте важно считать их по суммам, а не усреднять значения по строкам. Свод по каналам строится формулами из предыдущих разделов: каналы — через УНИК, показатели — через СУММЕСЛИМН по каждому каналу.

Свод по учебной таблице:

Итог считайте по суммам. Общая стоимость заявки — 680 / 22 = 30,91. Простое среднее по трём каналам (28,33; 30; 40) дало бы 32,78: каналы с малым числом заявок получили бы такой же вес, как крупные. То же касается CTR и конверсии.

Допустимая стоимость заявки — это наша рекомендация для планирования, а не стандарт: если средний чек 500, с продажи остаётся 30 % маржи, а в продажу превращается каждая пятая заявка, то заявка дороже 30 в среднем не окупит себя. В свод можно добавить столбец =ЕСЛИ(CPL>допустимая;"дороже нормы";""). Как правильно считать окупаемость и что включать в расходы, разобрано в статье «Формула ROI», а что такое кликабельность и от чего она зависит — в словаре, статья CTR.

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

ABC-анализ в три столбца

ABC-анализ делит товары, услуги или клиентов на группы по вкладу в выручку. Это распространённое деление, а не правило: границы 80 % и 95 % часто используют как отправную точку, но вы можете выбрать свои.

1. Отсортируйте позиции по выручке по убыванию: выручка в столбце B, со строки 2.
2. В C2 посчитайте накопленную долю: =СУММ($B$2:B2)/СУММ($B$2:$B$6) и протяните вниз. Первая ссылка закреплена, вторая растягивается.
3. В D2 присвойте группу: =ЕСЛИМН(C2<=0,8;"A";C2<=0,95;"B";ИСТИНА;"C").

Обратите внимание на «0,8» в формуле: при русских настройках десятичный разделитель — запятая, поэтому аргументы и разделяют точкой с запятой. В английской записи было бы 0.8 и запятые между аргументами. Группу A обычно продвигают в первую очередь, группу C проверяют: нужна ли она в рекламе и в ассортименте.

Excel и Google Таблицы: отличия

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

Средневзвешенное значение в Excel считают через СУММПРОИЗВ: =СУММПРОИЗВ(H2:H3;G2:G3)/СУММ(G2:G3) — средняя стоимость заявки двух кампаний с весом по числу заявок. Для строк 2 и 3: (20 × 6 + 40 × 2) / 8 = 25. В Google Таблицах то же даёт AVERAGE.WEIGHTED.

Если отчёт собирается из нескольких источников и им пользуется клиент, Google Таблицы удобнее. Если строк много и нужны Power Query, макросы или сводные таблицы с моделью данных, выбирают Excel. Часто используют оба: собирают данные в Таблицах, считают и оформляют в Excel.

Нейросети: как просить формулу и проверять

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

Что подставить: версию Excel, названия столбцов и задачу своими словами. Последнее требование — самое полезное: по нему вы сравните ответ нейросети с результатом в своей таблице.

Как проверять формулу от нейросети

• Проверьте имена функций: есть ли они в вашей версии (сверьтесь с таблицей версий).
• Посчитайте результат вручную на двух-трёх строках и сравните.
• Сверьте итог с кабинетом или CRM хотя бы по одной общей цифре.
• Не загружайте в чат-боты персональные данные клиентов: имена, телефоны, e-mail. Для задачи достаточно описать структуру таблицы или дать обезличенные строки.

Ошибки в формулах и как их исправить

Ещё три ошибки без сообщения об ошибке. Сортировка исходной выгрузки ломает ссылки в отчёте — сортируйте копию или собирайте отчёт через СОРТ на отдельном листе. Среднее по строкам вместо деления сумм искажает CPL и CTR. Ноль вместо пометки в ЕСЛИОШИБКА делает убыточную кампанию на вид бесплатной.

Сделайте за 15 минут

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

Что дальше

Частые вопросы

Андрей Гусаров

Основатель школы EDUGUSAROV и агентства GUSAROV. Материал опирается на официальные источники, спорные места помечены.

Обновлено: 22 октября 2026. Страница пересматривается каждые 60 дней.