Logo

CopyPastWorld

Обучение программированию

Excel для аналитики: от таблицы до отчёта

SUMIFS, COUNTIFS и поиск: анализ по категориям и маркетплейсам

3/5
Теория + Тест

SUMIFS, COUNTIFS и поиск: анализ по категориям и маркетплейсам

40 мин25 баллов
Бесплатный мини-курсУрок 3 из 540–60 минутУровень: начинающий

🎯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 «Группа ассортимента». В нём предстоит использовать функцию поиска по артикулу.

СтолбецНазваниеДля чего нужен
AДатаСравнение периодов
BНомер заказаИдентификация операции
CМаркетплейсУсловие Ozon / Wildberries
DРегионРегиональные срезы
EКатегорияУсловие «Худи», «Брюки» и др.
FАртикулКлюч для поиска в справочнике
HКоличествоКоличество единиц в операции
KСтатус«Выкуплен», «Возврат», «Отменён»
NВыручка, ₽Ненулевая только у учебно выкупленных операций
OСебестоимость продаж, ₽Аналогично статусной методике
PВаловая прибыль, ₽Разница N − O
QВыкуплено, шт.Количество выкупленных единиц
RГруппа ассортиментаПодставим из справочника

Правило этого курса: выкупленная операция считается продажей; «Возврат» и «Отменён» не образуют самостоятельную признанную продажу. Значения N, O, P и Q в таких строках равны нулю. Мы не вычитаем возвраты повторно, поскольку в учебной выгрузке нет связи с первоначальным признанием продажи. В настоящем учёте возврат может оформляться сторно или отдельной корректировкой; эту методику нельзя автоматически переносить на реальные данные компании.

Проверочные итоги предыдущего урока: 60 записей, 49 выкупленных операций, 55 выкупленных единиц, 111 500 ₽ выручки и 68 590 ₽ валовой прибыли. Весь сегодняшний анализ должен согласовываться с этими цифрами.

Учебные файлы для выполнения урока 3

Для работы в третьем уроке скачайте рабочую книгу sales_analysis_practice.xlsx. В ней уже сохранены все 60 операций и формулы из уроков 1–2, добавлен справочник товаров и лист для расчёта аналитических срезов.

XLSX

sales_analysis_practice.xlsx

Рабочая книга Excel к уроку 3•~15 КБ
Основной файл

Содержит 4 листа: «Начните здесь», «Продажи» (с формулами L–Q и новым столбцом R «Группа ассортимента»), «Справочник» (6 артикулов) и «Анализ» (ячейки выбора B3/B4 и формулы срезов).

Структура листов книги:
• «Продажи»: R2:R3 — образцы ВПР, R4:R61 — для вашей практики
• «Справочник»: A2:D7 — артикулы, наименования и группы
• «Анализ»: B3 (площадка), B4 (категория) и формулы СУММЕСЛИМН
Скачать sales_analysis_practice.xlsx
CSV

sales_training.csv

Исходный набор данных•~6.9 КБ
Из уроков 1–2

Первичный датасет из 60 операций (UTF-8-BOM, разделитель точка с запятой). Пригодится для проверки чистоты данных, повторения импорта или интеграции в сторонние аналитические инструменты.

Скачать sales_training.csv
Методика расчётов: Только строки со статусом «Выкуплен» признаются учебной выручкой. Возвраты и отмены учитываются в общем числе операций (строк), но не включаются в финансовые итоги.

СУММЕСЛИ и 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: «Конструктор формул СУММЕСЛИМН и СЧЁТЕСЛИМН»

Соберите формулу из блоков, проверьте синтаксис и разберите частые ошибки

Задача: рассчитать сумму выручки по выкупленным операциям, где маркетплейс равен значению в ячейке B3, а категория — в ячейке B4.
Значения тестовых ячеек:
B3:
B4:
Результат формулы: 47 000 ₽
Итоговая формула для вставки в Excel (RU):
=СУММЕСЛИМН(Продажи!$N2:$N61; Продажи!$C2:$C61; B3; Продажи!$E2:$E61; B4)
Идеально! Формула использует столбец N (только выкупленные заказы) и динамические ссылки на ячейки B3 и B4. При смене параметров отчёт сразу пересчитается.

Даты в условиях: анализ по месяцам без ручной фильтрации

Особенно полезная задача — сравнение периодов. В исходном столбце 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 — реальные даты первого числа соответствующего месяца. Получим:

ПериодВыручкаВаловая прибыльВыкупленные операции
Сентябрь 202654 150 ₽33 410 ₽25
Октябрь 202657 350 ₽35 180 ₽24
Итого111 500 ₽68 590 ₽49

Процент изменения выручки при ненулевой базе:

=ЕСЛИОШИБКА(B15/B14-1;0)

Для наших данных прирост равен приблизительно 5,91%. Это сравнение двух учебных периодов; в реальном бизнесе также нужно учитывать сопоставимость календарных дней, сезонность и возможные задержки поступления данных. В выгрузке есть демонстрационные даты до конца октября 2026 года — не воспринимайте их как уже случившуюся коммерческую статистику.

Частая ошибка: пытаться задать дату критерием "09.2026" либо искать подстроку в столбце с настоящими датами. Лучше сравнивать даты как числа в корректных границах периода.

Показатели должны сходиться между разными разрезами

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

Для учебного набора:

РазрезЧастиКонтроль
ПлощадкиOzon 69 400 + Wildberries 42 100111 500 ₽
КатегорииХуди 42 000 + Брюки 47 000 + Футболки 14 400 + Аксессуары 8 100111 500 ₽
МесяцыСентябрь 54 150 + октябрь 57 350111 500 ₽

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

В данном датасете валовая прибыль Ozon составляет 42 110 ₽, Wildberries — 26 480 ₽. По категориям наибольшую абсолютную валовую прибыль приносят брюки — 29 160 ₽, но эта цифра сама по себе не позволяет сравнить эффективность рекламы, логистики или комиссии: таких затрат в исходной выгрузке нет.

Тренажёр №1: «Анализ продаж с условиями отчёта»

Имитация формул СУММЕСЛИМН и СЧЁТЕСЛИМН на полном наборе из 60 операций

Быстрые сценарии из технического задания урока:
Выручка (СУММЕСЛИМН)
111 500 ₽
Столбец N (только выкупленные)
Выкупленных заказов
49 заказов
СЧЁТЕСЛИМН по K="Выкуплен"
Продано единиц
55 шт.
СУММЕСЛИМН по столбцу Q
Валовая прибыль
68 590 ₽
Себестоимость: 42 910 ₽
Средний чек (Выручка / Выкупленные заказы)
2 275,51 ₽
Средняя цена единицы (Выручка / Единицы)
2 027,27 ₽
Всего строк выборки: 60
Выкуп: 49Возврат: 7Отмена: 4
Различие понятий: Обратите внимание на разницу между числом выкупленных операций (49 шт.) и числом проданных единиц (55 шт.). СЧЁТЕСЛИМН считает строки, а суммирование штук производится через СУММЕСЛИМН по столбцу Q.
Всего записей в базе: 60

Поиск по артикулу: зачем нужна таблица-справочник

Иногда нужной характеристики нет в основной выгрузке. Например, в ней указана категория «Футболки», но руководитель хочет построить укрупнённый отчёт: «Верх», «Низ», «Аксессуары». Вручную присваивать метку каждой из 60 строк неудобно, а для тысячи строк — особенно рискованно. Для этого используется справочник.

На листе «Справочник» записано шесть уникальных артикулов. Каждый артикул связан с названием, категорией и дополнительной группой ассортимента:

АртикулТоварКатегорияГруппа
00101Худи Urban базовыйХудиВерх
00102Брюки Urban прямыеБрюкиНиз
00103Футболка BasicФутболкиВерх
00104Худи OversizeХудиВерх
00105Шапка RibАксессуарыАксессуары
00106Брюки JoggerБрюкиНиз

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

Артикулы 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 операций продаж со справочником товаров по текстовому артикулу

Лист «Справочник» (диапазон A2:D7):Столбец 1: Артикул | Столбец 4: Группа
Кол. 1: Артикул (A)Кол. 2: Товар (B)Кол. 3: Категория (C)Кол. 4: Группа (D) ★
'00101'Худи Nord базовыйХудиВерх
'00102'Брюки Cargo прямыеБрюкиНиз
'00103'Свитшот OversizeХудиВерх
'00104'Футболка Basic плотнаяФутболкиВерх
'00105'Кепка StreetАксессуарыАксессуары
'00106'Шорты CasualБрюкиНиз
Интерактивный тест поиска по артикулу:
Формула в ячейке R2 (столбец «Группа ассортимента»):
=ЕСЛИОШИБКА(ВПР(F2; Справочник!$A$2:$D$7; 4; ЛОЖЬ); "Не найден")
Результат для артикула 00101:Верх
Контрольная сверка выручки по товарным группам:
Группа «Верх» (00101, 00103, 00104)
56 400 ₽
Группа «Низ» (00102, 00106)
47 000 ₽
Группа «Аксессуары» (00105)
8 100 ₽
Итого по группам
111 500 ₽

Сумма выручки по трём группам (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="Выкуплен" при подсчёте числа заказов? В-четвёртых, являются ли даты датами, а не текстом, и правильно ли указаны границы месяцев? Наконец, проверены ли ключи справочника и текстовый формат артикулов?

Рассмотрим несколько характерных симптомов:

СимптомВероятное объяснениеКак проверить
Выручка больше 111 500 ₽Суммируется L вместо NСверить используемый столбец
На Ozon больше 24 выкупленных операцийНе проверяется статусДобавить критерий K="Выкуплен"
За сентябрь получается 0Даты записаны текстомПроверить тип ячеек A и B5–B7
По артикулу появляется #Н/ДНет совпадения по ключуПроверить ведущие нули и справочник
Выручка «Верх» меньше 56 400 ₽Не заполнен R или неверна группаНайти пустые и «Не найден» в R

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

Как использовать 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 — Брюки. Заполните показатели, опираясь на подсказки выше:

  1. Площадка и число выкупленных операций. Через СЧЁТЕСЛИМН получите количество выкупленных заказов Ozon. Сравните с суммой заказов по двум площадкам.
  2. Площадка и выручка. Через СУММЕСЛИМН рассчитайте выручку и валовую прибыль Ozon, затем переключите площадку на Wildberries и убедитесь, что результат автоматически изменился.
  3. Пересечение двух условий. Получите выручку и число выкупленных заказов в связке Ozon + Брюки. После этого проверьте вариант Ozon + Футболки и объясните нулевой результат.
  4. Сравнение месяцев. По условиям дат вычислите выручку сентября и октября, затем процент изменения между месяцами.
  5. Поиск по справочнику. Заполните R2:R61 функцией ВПР по текстовому артикулу. Рассчитайте выручку группы «Верх», проверьте отсутствие «Не найден» и сверьте сумму выручки трёх групп с общей выручкой.

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

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

Контрольные показатели урока 3 (5 практических задач)

Рассчитайте значения в файле sales_analysis_practice.xlsx и введите для сверки

1
Выручка Ozon, ₽
СУММЕСЛИМН по выкупленной выручке (столбец N) с условием маркетплейс = Ozon (C2:C61 = "Ozon").
2
Выкупленных операций Wildberries, шт.
СЧЁТЕСЛИМН по двум условиям: маркетплейс = Wildberries и статус = "Выкуплен".
3
Выручка Ozon × Брюки, ₽
СУММЕСЛИМН по столбцу N с условиями: C2:C61 = "Ozon" и E2:E61 = "Брюки".
4
Выручка октября 2026, ₽
СУММЕСЛИМН по столбцу N с границами дат: >=2026-10-01 и <2026-11-01 (или >=01.10.2026 и <01.11.2026).
5
Выручка группы «Верх», ₽
СУММЕСЛИМН по столбцу N и новому столбцу R («Группа ассортимента») с условием R2:R61 = "Верх".
Поддерживаются форматы: 69400, 69 400 ₽

Аналитический вывод руководителю (4–6 предложений)

Сформулируйте управленческий вывод на основе полученных показателей

Напишите краткий отчёт: какая площадка приносит больше выручки, в чём различие среднего чека, как изменились продажи в октябре и почему полученную прибыль нельзя называть «чистой».
Чек-лист полноты аналитического вывода:Предложений: 0
Сравнение Ozon и WB
Динамика месяцев
Ограничения данных (комиссии)
Сохраняется в вашем браузере

Итоги и продолжение обучения

Теперь вы умеете получать управленческие показатели не только по всей таблице, но и по конкретной площадке, категории или временному периоду. СУММЕСЛИМН отвечает на вопрос «сколько денег принесли строки с такими условиями?», СЧЁТЕСЛИМН — «сколько таких записей?», а ВПР помогает дополнить исходную выгрузку информацией из справочника. Эти инструменты позволяют создавать обновляемые отчёты и проводить контрольные сверки без ручного копирования десятков строк.

Важнейший результат урока — понимание методики. Правильный Excel-отчёт начинается не с красивой таблицы, а с определения показателя, условий его расчёта и проверки источника данных. Любая формула должна быть воспроизводимой и понятной другому аналитику.

До завершения бесплатного курса осталось 2 урока. Далее, в уроке 4, мы на основе этих же данных создадим сводные таблицы и диаграммы. В уроке 5 объединим все расчёты в итоговый мини-отчёт руководителю.

Вопрос 1

Какая функция в Excel лучше всего подходит для суммы выручки при двух условиях: маркетплейс и категория?

Вопрос 2

Какие утверждения о СЧЁТЕСЛИМН верны? (выберите все верные варианты)

Вопрос 3

Почему для расчёта сентябрьской выручки удобно использовать условия >=01.09.2026 и <01.10.2026?

Вопрос 4

Для чего в ВПР по артикулу указывают ЛОЖЬ / FALSE?

Вопрос 5

Какие действия помогают проверить правильность отчёта? (выберите все верные варианты)

Ваш прогресс в уроке

Выполняйте задания, чтобы заработать все баллы

Теория
25 баллов
Тесты
0/ 25
Текущий результат:
25/ 50 баллов
SUMIFS, COUNTIFS и поиск: анализ по категориям и маркетплейсам — Урок курса «Excel для аналитики: от таблицы до отчёта» | CopyPastWorld | CopyPastWorld