SUMIFS, COUNTIFS и поиск: анализ по категориям и маркетплейсам
🎯SUMIFS, COUNTIFS и поиск: анализ по категориям и маркетплейсам
Связь с уроками 1–2: используем тот же датасет из 60 операций интернет-магазина за сентябрь и октябрь 2026 года, обогащённый формулами выручки и новым справочником товаров.
СУММЕСЛИМН и СЧЁТЕСЛИМН, обогатите операции группой ассортимента через ВПР (VLOOKUP) и защитите формулу от ошибок с ЕСЛИОШИБКА.Курс: Excel для аналитики: от таблицы до отчёта
Формат: бесплатный мини-курс CopyPastWorld
Урок 3 из 5. SUMIFS, COUNTIFS и поиск данных: анализ продаж по категориям и маркетплейсам
Уровень: начинающий · Время: 40–60 минут с практикой
Продолжение уроков 1–2: используем те же 60 учебных операций, за сентябрь и октябрь 2026 года. Все организации, заказы и суммы в датасете вымышлены.
До этого урока мы научились загружать данные и рассчитывать показатели для каждой строки: стоимость операции, себестоимость, учебную выручку, валовую прибыль и количество выкупленных товаров. В результате получились столбцы L–Q, а общий итог по выкупленным операциям составил 111 500 ₽ выручки и 68 590 ₽ валовой прибыли. Но руководителю редко достаточно одного общего числа. Он спрашивает: «Сколько принёс Ozon? Какие категории продаются на Wildberries? Сколько заказов было в сентябре? И почему два сотрудника получают разные итоги из одной таблицы?» Ответим на эти вопросы средствами Excel, не копируя строки вручную и не полагаясь на случайно оставленный фильтр.
В этом уроке научимся суммировать значения при нескольких условиях, считать подходящие записи, работать с датами и находить информацию о товаре по артикулу. Главная идея — перейти от отдельных формул к управляемому мини-отчёту, в котором показатели меняются вместе с выбранной площадкой или категорией. В конце выполним практический кейс и проверим результаты по контрольным суммам.
Какие бизнес-вопросы решает условная агрегация
Представьте таблицу из тысяч заказов: в одном столбце указан маркетплейс, в другом категория товара, в третьем статус, а в четвёртом — выручка. Если нужно получить продажи только худи на Ozon, обычная СУММ не подходит: она складывает значения всего диапазона. Можно включить фильтр и посмотреть строки, но это неудобно для регулярно обновляемого отчёта. К тому же сумма, полученная вручную после фильтрации, легко перестанет соответствовать актуальным данным.
Агрегация — это сведение многих строк к нескольким показателям: сумме, количеству, среднему и так далее. Условная агрегация включает в расчёт только записи, удовлетворяющие заданным критериям. Например: площадка = Ozon и категория = Худи и статус = Выкуплен. Такие условия применяются одновременно, то есть между ними действует логическое И.
К концу занятия мы построим ответы на пять вопросов: какова выручка каждой площадки; какая категория даёт больше валовой прибыли; сколько выкупленных операций на каждой площадке; как изменились продажи между сентябрём и октябрём; можно ли добавить к выгрузке новую характеристику товара из справочника, не переписывая её вручную.
Вспоминаем устройство файла и методику расчётов
Откройте sales_analysis_practice.xlsx. На листе «Продажи» 60 операций — строки 2–61, заголовки в строке 1. Исходные столбцы A–K совпадают с предыдущими уроками; L–Q уже заполнены проверяемыми формулами из урока 2. Для сегодняшней практики добавлен столбец R «Группа ассортимента». В нём предстоит использовать функцию поиска по артикулу.
Правило этого курса: выкупленная операция считается продажей; «Возврат» и «Отменён» не образуют самостоятельную признанную продажу. Значения N, O, P и Q в таких строках равны нулю. Мы не вычитаем возвраты повторно, поскольку в учебной выгрузке нет связи с первоначальным признанием продажи. В настоящем учёте возврат может оформляться сторно или отдельной корректировкой; эту методику нельзя автоматически переносить на реальные данные компании.
Проверочные итоги предыдущего урока: 60 записей, 49 выкупленных операций, 55 выкупленных единиц, 111 500 ₽ выручки и 68 590 ₽ валовой прибыли. Весь сегодняшний анализ должен согласовываться с этими цифрами.
Учебные файлы для выполнения урока 3
Для работы в третьем уроке скачайте рабочую книгу sales_analysis_practice.xlsx. В ней уже сохранены все 60 операций и формулы из уроков 1–2, добавлен справочник товаров и лист для расчёта аналитических срезов.
sales_analysis_practice.xlsx
Содержит 4 листа: «Начните здесь», «Продажи» (с формулами L–Q и новым столбцом R «Группа ассортимента»), «Справочник» (6 артикулов) и «Анализ» (ячейки выбора B3/B4 и формулы срезов).
sales_training.csv
Первичный датасет из 60 операций (UTF-8-BOM, разделитель точка с запятой). Пригодится для проверки чистоты данных, повторения импорта или интеграции в сторонние аналитические инструменты.
СУММЕСЛИ и SUMIF: первая сумма по условию
Функция СУММЕСЛИ (SUMIF) суммирует числовые значения только у строк, которые соответствуют одному условию. Русская запись функции:
=СУММЕСЛИ(диапазон_условия;условие;диапазон_суммы)
Допустим, требуется выручка Ozon. Условия находятся в столбце C, а сумма — в столбце N:
=СУММЕСЛИ(Продажи!$C$2:$C$61;"Ozon";Продажи!$N$2:$N$61)
Английский вариант с запятыми:
=SUMIF('Продажи'!$C$2:$C$61,"Ozon",'Продажи'!$N$2:$N$61)
Результат — 69 400 ₽. Для Wildberries по аналогичной формуле получаем 42 100 ₽. Их сумма равна 111 500 ₽, что совпадает с общей выручкой. Это первая контрольная проверка: если один из показателей не сходится, следует проверить исходные диапазоны, правильность статусов и отсутствие лишних пробелов в названиях площадок.
В этой формуле диапазоны зафиксированы знаками $. При копировании отчётной формулы вниз или вправо ссылки на исходные столбцы не должны смещаться. Условие можно вводить текстом в формуле, но удобнее ссылаться на ячейку выбора: в Анализ!B3 будет название площадки, и тогда формула станет динамической.
СУММЕСЛИМН и SUMIFS: одновременно несколько условий
Функция СУММЕСЛИМН (SUMIFS) отличается тем, что принимает несколько пар «диапазон — критерий». Порядок аргументов у неё другой: сначала диапазон суммирования, затем диапазоны и условия.
=СУММЕСЛИМН(диапазон_суммы;диапазон_1;условие_1;диапазон_2;условие_2)
Например, требуется сумма выручки от продаж худи на Ozon. Добавляем два условия:
=СУММЕСЛИМН(Продажи!$N$2:$N$61;Продажи!$C$2:$C$61;"Ozon";Продажи!$E$2:$E$61;"Худи")
Результат — 22 400 ₽. Поскольку столбец N уже содержит ноль у отменённых и возвращённых операций, отдельное условие по статусу при расчёте выручки здесь необязательно. Но для подсчёта числа заказов проверка статуса будет обязательной — число строк с заказами и число выкупленных заказов не одно и то же.
Для многоразового отчёта замените текст условия ссылками на ячейки. На листе «Анализ» в B3 стоит площадка, в B4 — категория. Тогда формула для B11:
=СУММЕСЛИМН(Продажи!$N$2:$N$61;Продажи!$C$2:$C$61;$B$3;Продажи!$E$2:$E$61;$B$4)
В английском Excel:
=SUMIFS('Продажи'!$N$2:$N$61,'Продажи'!$C$2:$C$61,$B$3,'Продажи'!$E$2:$E$61,$B$4)
Если в B3 выбрано Ozon, а в B4 — Брюки, ответ будет 47 000 ₽. Если изменить категорию на Футболки, получите 0 ₽: по учебному набору Ozon не имеет выкупленных операций в этой категории. Ноль не следует заменять на пустую ячейку: здесь он имеет конкретный смысл — по выбранной комбинации не найдено признанных продаж.
Типичная ошибка: у диапазона суммы 60 строк, а у одного из диапазонов условия 59. Все диапазоны должны совпадать по форме и охватывать одни и те же записи. Не смешивайте столбец N с исходным столбцом L, если ваша цель — выручка только по выкупленным операциям.
СЧЁТЕСЛИ и СЧЁТЕСЛИМН: считаем подходящие строки
СЧЁТЕСЛИ (COUNTIF) считает записи, отвечающие одному условию. СЧЁТЕСЛИМН (COUNTIFS) — нескольким. Это не сумма количества товаров, а количество подходящих строк. Если одна запись содержит две футболки, COUNTIFS посчитает её как одну операцию.
Число выкупленных операций на Ozon:
=СЧЁТЕСЛИМН(Продажи!$C$2:$C$61;"Ozon";Продажи!$K$2:$K$61;"Выкуплен")
Ответ — 24. На Wildberries выкуплено 25 операций. Всего 24 + 25 = 49.
Число выкупленных операций с брюками на Ozon:
=СЧЁТЕСЛИМН(Продажи!$C$2:$C$61;"Ozon";Продажи!$E$2:$E$61;"Брюки";Продажи!$K$2:$K$61;"Выкуплен")
Результат — 17. Если бы мы забыли условие статуса, в число попали бы и незавершённые/возвращённые операции соответствующей категории. Для расчёта количества единиц товара нужно не COUNTIFS, а СУММЕСЛИМН по столбцу Q — например, всего выкупленных единиц на Ozon 27.
Обратите внимание на различие: 24 заказа на Ozon и 27 единиц товара. Количество заказов участвует в среднем чеке, а количество проданных единиц — в средней цене единицы и расчёте производственных объёмов.
Делать критерии динамическими: одна ячейка вместо десяти формул
Перейдите на лист «Анализ» учебной книги. Здесь заранее подготовлены поля выбора: B3 — маркетплейс, B4 — категория, B5 — начало сентября, B6 — начало октября, B7 — начало ноября. В B8 и B9 даны образцы формул. Остальные значения вы вычислите самостоятельно.
Попробуйте поменять B3 с Ozon на Wildberries. Правильно составленные формулы на листе «Анализ» должны пересчитаться без редактирования исходной таблицы. Это основа профессионального отчёта: отдельные данные, отдельные параметры, отдельные результаты. Старайтесь не вписывать названия площадок прямо внутрь каждой формулы, если пользователь будет переключаться между ними.
В реальной книге полезно добавить раскрывающийся список через Данные → Проверка данных → Список, чтобы избежать значений вроде Ozon с пробелом или опечатки Ozоn с буквой из другого алфавита. Для учебной книги доступны оба варианта: выбрать значение из списка или ввести точный текст.
Чтобы посчитать средний чек по выбранной площадке, разделите сумму выручки B9 на количество выкупленных операций B8:
=ЕСЛИОШИБКА(B9/B8;0)
При выборе Ozon он равен приблизительно 2 891,67 ₽, Wildberries — 1 684,00 ₽. Значение не равно средней цене товара, потому что в некоторых операциях продано больше одной единицы.
При нуле заказов результат деления не определён. В учебной модели ЕСЛИОШИБКА возвращает ноль ради устойчивости интерфейса, но в профессиональном отчёте лучше явно отображать «нет заказов», чтобы не спутать невозможность расчёта с реальным нулевым средним чеком.
Тренажёр №2: «Конструктор формул СУММЕСЛИМН и СЧЁТЕСЛИМН»
Соберите формулу из блоков, проверьте синтаксис и разберите частые ошибки
Даты в условиях: анализ по месяцам без ручной фильтрации
Особенно полезная задача — сравнение периодов. В исходном столбце A хранятся настоящие даты Excel, а не строки, похожие на даты. Для сентября необходимо включить записи с 1 сентября включительно до 1 октября не включительно. Такой приём надёжнее, чем проверять только номер месяца: он учитывает год и корректен для любых дат с временем.
Формула выручки за сентябрь на листе «Анализ»:
=СУММЕСЛИМН(Продажи!$N$2:$N$61;Продажи!$A$2:$A$61;">="&$B$5;Продажи!$A$2:$A$61;"<"&$B$6)
За октябрь используем диапазон >=B6 и <B7. Значения B5, B6 и B7 — реальные даты первого числа соответствующего месяца. Получим:
Процент изменения выручки при ненулевой базе:
=ЕСЛИОШИБКА(B15/B14-1;0)
Для наших данных прирост равен приблизительно 5,91%. Это сравнение двух учебных периодов; в реальном бизнесе также нужно учитывать сопоставимость календарных дней, сезонность и возможные задержки поступления данных. В выгрузке есть демонстрационные даты до конца октября 2026 года — не воспринимайте их как уже случившуюся коммерческую статистику.
Частая ошибка: пытаться задать дату критерием "09.2026" либо искать подстроку в столбце с настоящими датами. Лучше сравнивать даты как числа в корректных границах периода.
Показатели должны сходиться между разными разрезами
Существует простой приём, который отличает проверяемую аналитику от случайного набора формул: контрольные суммы. Общая выручка должна совпасть с суммой выручки по площадкам, по категориям и по месяцам, если эти группы покрывают все выкупленные операции и не пересекаются.
Для учебного набора:
Если суммы не сходятся, это не всегда «неправильная формула»: возможно, у записей отсутствует категория, появился новый маркетплейс, нарушена структура дат или одни строки учтены дважды. Поэтому аналитик сначала проверяет качество данных и полноту группировок, а не подгоняет числа под итог.
В данном датасете валовая прибыль Ozon составляет 42 110 ₽, Wildberries — 26 480 ₽. По категориям наибольшую абсолютную валовую прибыль приносят брюки — 29 160 ₽, но эта цифра сама по себе не позволяет сравнить эффективность рекламы, логистики или комиссии: таких затрат в исходной выгрузке нет.
Тренажёр №1: «Анализ продаж с условиями отчёта»
Имитация формул СУММЕСЛИМН и СЧЁТЕСЛИМН на полном наборе из 60 операций
Поиск по артикулу: зачем нужна таблица-справочник
Иногда нужной характеристики нет в основной выгрузке. Например, в ней указана категория «Футболки», но руководитель хочет построить укрупнённый отчёт: «Верх», «Низ», «Аксессуары». Вручную присваивать метку каждой из 60 строк неудобно, а для тысячи строк — особенно рискованно. Для этого используется справочник.
На листе «Справочник» записано шесть уникальных артикулов. Каждый артикул связан с названием, категорией и дополнительной группой ассортимента:
Справочник демонстрационный: группы заданы для учебного анализа, а не получены от внешнего каталога. Важно, чтобы значение ключа было уникальным. Если в справочнике один артикул встречается дважды с разными группами, поиск не сможет гарантировать правильное сопоставление.
Артикулы 00101 и 101 — не одно и то же, если используется текстовый идентификатор с ведущими нулями. Поэтому F и столбец A справочника должны хранить идентификаторы как текст, иначе поиск может не найти совпадение.
ВПР / VLOOKUP: добавляем группу к каждой операции
Функция ВПР (VLOOKUP) ищет значение в первом столбце указанного диапазона и возвращает значение из другого столбца той же строки. В нашем справочнике артикул находится в A, нужная группа — в D, то есть в четвёртом столбце диапазона.
В ячейку Продажи!R2:
=ЕСЛИОШИБКА(ВПР(F2;Справочник!$A$2:$D$7;4;ЛОЖЬ);"Не найден")
Английский вариант:
=IFERROR(VLOOKUP(F2,'Справочник'!$A$2:$D$7,4,FALSE),"Не найден")
Значение ЛОЖЬ / FALSE требует точного совпадения. Для идентификаторов товара это обязательное условие: приблизительный поиск способен вернуть неправильную строку. Первые две ячейки R2 и R3 уже содержат образец; протяните R2 до R61 и проверьте несколько строк, включая артикул 00105 — он должен получить группу Аксессуары.
Почему добавлена ЕСЛИОШИБКА? Без неё отсутствующий ключ обычно приводит к #Н/Д / #N/A. Текст «Не найден» удобен для ученика, но не означает, что проблему можно игнорировать. После заполнения R2:R61 необходимо убедиться, что таких значений нет. Справочник может быть неполным, идентификаторы — в разных форматах, а исходная таблица — содержать ошибки.
Если у вас доступна функция ПРОСМОТРX / XLOOKUP, поиск можно записать нагляднее:
=ПРОСМОТРX(F2;Справочник!$A$2:$A$7;Справочник!$D$2:$D$7;"Не найден";0)
В английской версии: =XLOOKUP(F2,'Справочник'!$A$2:$A$7,'Справочник'!$D$2:$D$7,"Не найден",0). Эта функция доступна не во всех старых версиях Excel. Поэтому основной обязательный путь урока — ВПР, а ПРОСМОТРX — полезная современная альтернатива.
Агрегация по найденной характеристике
После заполнения R2:R61 можно анализировать не только исходные категории, но и новую группу. Посчитайте выручку группы «Верх»:
=СУММЕСЛИМН(Продажи!$N$2:$N$61;Продажи!$R$2:$R$61;"Верх")
Выручка группы «Верх» — 56 400 ₽: сюда входят худи (42 000 ₽) и футболки (14 400 ₽). Группа «Низ» даёт 47 000 ₽, аксессуары — 8 100 ₽. И снова сумма всех групп должна равняться общей выручке 111 500 ₽. Если в столбце R остались пустые значения, эта проверка может не сойтись — так вы увидите, почему незавершённый этап обогащения данных влияет на следующий расчёт.
В более сложных системах подобную задачу решают через объединение таблиц (JOIN), Power Query или модели данных. Но принцип один и тот же: сначала определяем ключ и соответствие строк, затем строим показатели. На следующем этапе карьеры аналитика это станет основой для SQL и BI.
Тренажёр №3: «Обогащение данных через ВПР / VLOOKUP»
Связывание 60 операций продаж со справочником товаров по текстовому артикулу
| Кол. 1: Артикул (A) | Кол. 2: Товар (B) | Кол. 3: Категория (C) | Кол. 4: Группа (D) ★ |
|---|---|---|---|
| '00101' | Худи Nord базовый | Худи | Верх |
| '00102' | Брюки Cargo прямые | Брюки | Низ |
| '00103' | Свитшот Oversize | Худи | Верх |
| '00104' | Футболка Basic плотная | Футболки | Верх |
| '00105' | Кепка Street | Аксессуары | Аксессуары |
| '00106' | Шорты Casual | Брюки | Низ |
Сумма выручки по трём группам (56 400 + 47 000 + 8 100) даёт ровно 111 500 ₽. Это полностью совпадает с суммой по маркетплейсам (69 400 + 42 100) и суммой по месяцам (54 150 + 57 350). Неприсвоенных артикулов во всех 60 строках — ровно 0.
Когда Excel показывает неправильный итог: диагностика
Если ожидаемая выручка не совпала с фактической, проверяйте проблему по порядку. Во-первых, верно ли выбран диапазон суммы? Для выручки это N, не L: столбец L включает отменённые и возвращённые операции. Во-вторых, совпадают ли адреса и длины диапазонов условий? Для всех 60 операций используем строки 2–61. В-третьих, не забыто ли условие K="Выкуплен" при подсчёте числа заказов? В-четвёртых, являются ли даты датами, а не текстом, и правильно ли указаны границы месяцев? Наконец, проверены ли ключи справочника и текстовый формат артикулов?
Рассмотрим несколько характерных симптомов:
Не исправляйте результаты произвольным числом «для красоты». Исправлять нужно данные, формулу или методику, обязательно понимая причину расхождения.
Как использовать AI для создания и проверки формул
AI может быстро подсказать синтаксис СУММЕСЛИМН, составить формулу по описанию задачи или объяснить причину ошибки #Н/Д. Но он не видит настоящую структуру вашей книги, если вы не передали её в запросе, и может перепутать столбцы. Поэтому в запросе лучше прямо указать названия листов, адреса колонок и определение выручки.
Пример рабочего промпта:
В Excel на листе «Продажи» строки 2–61 содержат операции. В C указан маркетплейс, в E категория, в K статус, в N учебная выручка по выкупленным операциям. На листе «Анализ» B3 — выбранная площадка, B4 — категория. Напиши формулу для суммы выручки по этим двум условиям. Дай варианты для русской и английской локализации Excel, объясни порядок аргументов и способ проверки результата. Не используй столбец L, поскольку он содержит стоимость всех операций.
Проверь формулу на известных примерах: Ozon + Брюки = 47 000 ₽, Ozon + Худи = 22 400 ₽, Ozon + Футболки = 0 ₽. Если нейросеть предложила формулу без фиксированных диапазонов, попросите объяснить, как она будет вести себя при копировании. Если речь идёт о большом рабочем файле, лучше поручать арифметические вычисления Excel или другому вычислительному инструменту, а AI — формулирование логики и интерпретацию.
Практика: соберите отчёт для руководителя
Откройте лист «Анализ» в sales_analysis_practice.xlsx. В B3 выберите Ozon, в B4 — Брюки. Заполните показатели, опираясь на подсказки выше:
- Площадка и число выкупленных операций. Через СЧЁТЕСЛИМН получите количество выкупленных заказов Ozon. Сравните с суммой заказов по двум площадкам.
- Площадка и выручка. Через СУММЕСЛИМН рассчитайте выручку и валовую прибыль Ozon, затем переключите площадку на Wildberries и убедитесь, что результат автоматически изменился.
- Пересечение двух условий. Получите выручку и число выкупленных заказов в связке
Ozon + Брюки. После этого проверьте вариантOzon + Футболкии объясните нулевой результат. - Сравнение месяцев. По условиям дат вычислите выручку сентября и октября, затем процент изменения между месяцами.
- Поиск по справочнику. Заполните R2:R61 функцией ВПР по текстовому артикулу. Рассчитайте выручку группы «Верх», проверьте отсутствие «Не найден» и сверьте сумму выручки трёх групп с общей выручкой.
После числовых расчётов напишите вывод из 4–6 предложений: какая площадка приносит больше выручки, в чём различается средний чек, насколько изменилась выручка по месяцам и какие данные нужны, чтобы оценивать прибыльность площадок после комиссий и логистики. Не называйте валовую прибыль чистой и не делайте выводов о фактической эффективности рекламных кампаний: в данных отсутствует соответствующая информация.
Критерий выполненной практики — не просто близкое число. В формулах используются корректные колонки и диапазоны, статус учитывается при подсчёте операций, ключи справочника совпадают, итоговая сверка выполняется, а управленческие выводы не выходят за пределы данных.
Контрольные показатели урока 3 (5 практических задач)
Рассчитайте значения в файле sales_analysis_practice.xlsx и введите для сверки
69400, 69 400 ₽Аналитический вывод руководителю (4–6 предложений)
Сформулируйте управленческий вывод на основе полученных показателей
Итоги и продолжение обучения
Теперь вы умеете получать управленческие показатели не только по всей таблице, но и по конкретной площадке, категории или временному периоду. СУММЕСЛИМН отвечает на вопрос «сколько денег принесли строки с такими условиями?», СЧЁТЕСЛИМН — «сколько таких записей?», а ВПР помогает дополнить исходную выгрузку информацией из справочника. Эти инструменты позволяют создавать обновляемые отчёты и проводить контрольные сверки без ручного копирования десятков строк.
Важнейший результат урока — понимание методики. Правильный Excel-отчёт начинается не с красивой таблицы, а с определения показателя, условий его расчёта и проверки источника данных. Любая формула должна быть воспроизводимой и понятной другому аналитику.
До завершения бесплатного курса осталось 2 урока. Далее, в уроке 4, мы на основе этих же данных создадим сводные таблицы и диаграммы. В уроке 5 объединим все расчёты в итоговый мини-отчёт руководителю.
Какая функция в Excel лучше всего подходит для суммы выручки при двух условиях: маркетплейс и категория?
Какие утверждения о СЧЁТЕСЛИМН верны? (выберите все верные варианты)
Почему для расчёта сентябрьской выручки удобно использовать условия >=01.09.2026 и <01.10.2026?
Для чего в ВПР по артикулу указывают ЛОЖЬ / FALSE?
Какие действия помогают проверить правильность отчёта? (выберите все верные варианты)
Ваш прогресс в уроке
Выполняйте задания, чтобы заработать все баллы
