СУММЕСЛИ и СУММЕСЛИМН в Excel — суммирование по условиям, даты, сотрудники, отчёты. Подробный разбор функций СУММЕСЛИ и СУММЕСЛИМН в Excel: как суммировать продажи по сотруднику, периоду и нескольким условиям, как правильно задавать критерии (звёздочка, даты), как избежать ошибок с целыми столбцами и заголовками. Практические примеры и системный подход.
Аналитические функции Excel — МИН, МАКС, СРЗНАЧ, СЧЁТ: база анализа больших таблиц. ЧАСТЬ 1
СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel — анализ данных по условиям без сводных таблиц. ЧАСТЬ 2
СУММЕСЛИ И СУММЕСЛИМН В EXCEL: КАК СУММИРОВАТЬ ПО УСЛОВИЯМ И ДЕЛАТЬ ОТЧЁТЫ, КОТОРЫМ ВЕРЯТ. ЧАСТЬ 3
Почему «посчитать количество» — это только половина аналитики
В Части 2 мы научились отвечать на вопросы вида: сколько было заказов, сколько у сотрудника, сколько в период. Но руководителю почти всегда нужен другой ответ: на какую сумму.
И вот тут Excel превращается из «таблицы со строками» в инструмент управления:
- выручка по сотруднику
- выручка по периоду
- сумма продаж по условиям
- контроль отклонений
- быстрое сравнение показателей
За это отвечают две функции:
- СУММЕСЛИ — сумма по одному условию
- СУММЕСЛИМН — сумма по нескольким условиям
И важно понять одну вещь: если СЧЁТЕСЛИ помогает ответить «сколько», то СУММЕСЛИ отвечает «сколько денег».
СУММЕСЛИ: простая функция, которую чаще всего ломают критерием
Как работает СУММЕСЛИ
Она суммирует числа в диапазоне, но только в тех строках, где выполняется условие. Типовой сценарий: есть столбец «Сотрудник» и столбец «Сумма заказа». Нужно посчитать сумму продаж Купцова. Правильная логика всегда такая:
- что суммируем (диапазон сумм)
- по чему отбираем (диапазон критериев)
- какой критерий
Пример формулы (идея):
=СУММЕСЛИ(столбец_сотрудник; "Купцов"; столбец_сумма)* *
Почему критерий “Купцов Александр” даёт ошибку
Потому что в данных встречаются варианты:
- Купцов А.
- Купцов Александр
- Купцов
Надёжный критерий:
"Купцов"* *
Звёздочка означает «любой хвост». Это делает формулу устойчивой к «творчеству» ввода.
Подстановочные знаки в СУММЕСЛИ: когда они обязательны
Символы:
- * — любой набор символов
- ? — ровно один символ
Где это реально спасает:
- фамилии с инициалами
- статусы (“Доставка”, “Доставка платная”, “Доставка бесплатно”)
- непредсказуемые пометки (“Купцов (стажёр)”, “Купцов — VIP”)
Пример «начинается с»:
=СУММЕСЛИ(Сотрудник; "Купцов"; Сумма)* *
Пример «содержит»:
=СУММЕСЛИ(Комментарий; "доставка"; Сумма) *
СУММЕСЛИМН: когда у бизнеса появляется второй вопрос
Как только вы научились суммировать по одному условию, жизнь приносит второе:
- сумма продаж Купцова в марте
- сумма продаж Купцова с доставкой
- сумма продаж Купцова в марте и только онлайн
- сумма продаж по региону и категории товара
Это уже СУММЕСЛИМН.
Как думает СУММЕСЛИМН
Она выбирает строки, где выполняются все условия одновременно, и суммирует выбранные значения. Запомни правило:
СУММЕСЛИМН = “суммируй это” + “при таких условиях”
Практика: сумма продаж Купцова за март (правильно, надёжно, универсально)
У нас есть:
- столбец Фамилия/Сотрудник
- столбец Дата
- столбец Сумма
Нужно: сумма продаж Купцова за март 2019. Ключ: период задаём не “март”, а границами дат:
- дата ≥ 01.03.2019
- дата < 01.04.2019
Так корректнее и быстрее. Формула концептуально выглядит так:
=СУММЕСЛИМН(Сумма; Сотрудник; "Купцов"; Дата; ">=01.03.2019"; Дата; "<01.04.2019")* *
Почему даты “внутри формулы” — слабое место
Потому что при смене периода вы будете править формулу. А это означает:
- риск ошибки
- потеря времени
- невозможность быстро переиспользовать расчёт
Универсальная версия: даты берём из ячеек
Делаем на листе:
- H13 — начало периода
- H14 — конец периода
И строим критерии как “текст + значение из ячейки”. Пример:
=СУММЕСЛИМН(Сумма; Сотрудник; "Купцов"; Дата; ">="&H13; Дата; "<"&H14)* *
Это уже формула уровня «в отчёт». Меняешь H13/H14 — пересчитывается всё.
Главные ошибки, из-за которых суммы не сходятся
Ошибка 1: считать весь столбец
Если диапазон = весь столбец, а у вас заголовок и “пустые миллионы строк”, Excel может:
- тормозить
- возвращать странные результаты в связке с другими расчётами
- тратить ресурсы на пустоты
Правило:
- суммируем реальный диапазон
- или используем таблицу (Ctrl+T)
Ошибка 2: разные размеры диапазонов
В СУММЕСЛИМН все диапазоны должны быть одинаковой длины. Иначе получите ошибку или неверную логику.
Ошибка 3: дата как текст
Дата “01.03.2019” может оказаться текстом (особенно после импорта).
Тогда условия “>=” работают непредсказуемо. Проверка:
- попробуйте сменить формат
- сравните =ТИП(ячейка)
Ошибка 4: пробелы в фамилиях
“Купцов ” и “Купцов” — разные значения. Решение:
- чистить данные (СЖПРОБЕЛЫ)
- или использовать структурированный ввод
Когда СУММЕСЛИМН лучше сводной таблицы
Сводная хороша, но:
- её нужно обновлять
- её легко “сломать” настройками
- её неудобно внедрять в автоматические отчёты
СУММЕСЛИМН выигрывает, когда:
- нужен “живой” отчёт на листе
- нужны динамические периоды
- нужно строить панель KPI
- нужно быстро копировать расчёты по сотрудникам/региону
VBA: суммирование по периоду и фамилии (быстрая проверка расчётов)
Иногда надо проверить итог без формул — особенно в файлах после импорта.
Sub SumOrdersByEmployeeAndPeriod()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim emp As String
Dim d1 As Date, d2 As Date
Dim total As Double
Set ws = ActiveSheet
emp = InputBox("Фамилия (например: Купцов):", "Критерий")
If emp = "" Then Exit Sub
d1 = CDate(InputBox("Начало периода (дд.мм.гггг):", "Период"))
d2 = CDate(InputBox("Конец периода (дд.мм.гггг):", "Период"))
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
total = 0
For i = 2 To lastRow
If CStr(ws.Cells(i, 2).Value) Like emp & "*" Then
If ws.Cells(i, 1).Value >= d1 And ws.Cells(i, 1).Value < d2 Then
total = total + CDbl(ws.Cells(i, 4).Value)
End If
End If
Next i
MsgBox "Сумма заказов: " & Format(total, "# ##0.00"), vbInformation, "Итог"
End Sub
Примечание по логике:
- столбец A = дата
- столбец B = сотрудник
- столбец D = сумма
Если структура другая — меняются номера столбцов.
👉 Если ты хочешь, чтобы Excel начал отвечать на вопросы уровня “сколько денег и где именно”, подпишись на канал и напиши комментарий. Это помогает выпускать продолжение быстрее.
Итог Части 3
Теперь у тебя есть рабочая связка:
- СЧЁТЕСЛИ / СЧЁТЕСЛИМН — “сколько”
- СУММЕСЛИ / СУММЕСЛИМН — “на какую сумму”
Ты умеешь:
- суммировать по сотруднику
- суммировать по периоду
- строить универсальные расчёты через ячейки дат
- избегать типовых ошибок (заголовки, столбцы целиком, текстовые даты)
В Части 4 мы добьём систему:
- МИН/МАКС/СРЗНАЧ по условиям (МИНЕСЛИ/МАКСЕСЛИ, СРЗНАЧЕСЛИ/СРЗНАЧЕСЛИМН)
- правильные KPI и панели
- контроль качества данных и проверочные формулы
- ускорение расчётов на больших таблицах
Аналитические функции Excel — МИН, МАКС, СРЗНАЧ, СЧЁТ: база анализа больших таблиц. ЧАСТЬ 1
СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel — анализ данных по условиям без сводных таблиц. ЧАСТЬ 2
СУММЕСЛИ И СУММЕСЛИМН В EXCEL: КАК СУММИРОВАТЬ ПО УСЛОВИЯМ И ДЕЛАТЬ ОТЧЁТЫ, КОТОРЫМ ВЕРЯТ. ЧАСТЬ 3