АНАЛИТИЧЕСКИЕ ФУНКЦИИ EXCEL: СРЗНАЧЕСЛИ, МИНЕСЛИМН, МАКСЕСЛИМН, KPI И КОНТРОЛЬ КАЧЕСТВА ДАННЫХ НА БОЛЬШИХ ТАБЛИЦАХ. Профессиональный разбор аналитических функций Excel для больших таблиц: СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, МИНЕСЛИМН, МАКСЕСЛИМН, подсчёт и суммирование по условиям. Как строить KPI-панели, находить аномалии, контролировать качество данных и ускорять расчёты на десятках тысяч строк.
Аналитические функции Excel — МИН, МАКС, СРЗНАЧ, СЧЁТ: база анализа больших таблиц. ЧАСТЬ 1
СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel — анализ данных по условиям без сводных таблиц. ЧАСТЬ 2
СУММЕСЛИ И СУММЕСЛИМН В EXCEL: КАК СУММИРОВАТЬ ПО УСЛОВИЯМ И ДЕЛАТЬ ОТЧЁТЫ, КОТОРЫМ ВЕРЯТ. ЧАСТЬ 3
Почему “сумма и количество” — это ещё не KPI
В Части 2 и части 3 мы научились считать:
- сколько (СЧЁТЕСЛИ/СЧЁТЕСЛИМН)
- на какую сумму (СУММЕСЛИ/СУММЕСЛИМН)
Но управленческая аналитика чаще упирается в другие вопросы:
- какой средний чек у сотрудника в период
- какой минимальный заказ был у сотрудника (и почему он такой)
- какой максимальный заказ случился (это рост или ошибка)
- где аномалии
- кто реально эффективен, а кто “на объёме”
- как сделать KPI так, чтобы цифрам можно было верить
Здесь на сцену выходят функции:
- СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН
- МИНЕСЛИМН и МАКСЕСЛИМН
- плюс обязательные привычки: проверки данных и ускорение расчётов
СРЗНАЧЕСЛИ: среднее по условию без плясок
Что делает СРЗНАЧЕСЛИ
Берёт диапазон, отбирает строки по условию и считает среднее значение. Типовая задача:
- средняя сумма заказа по сотруднику
- средний чек по региону
- средняя длительность сделки по статусу
Ключевой момент: среднее по условию всегда честнее, чем среднее по всей таблице, потому что оно отвечает на конкретный вопрос.
Пример логики
Средний чек Купцова:
СРЗНАЧЕСЛИ(Сотрудник; "Купцов"; Сумма)*
Если фамилии записаны по-разному — снова спасает *.
СРЗНАЧЕСЛИМН: среднее по нескольким условиям — настоящий KPI
Самая рабочая связка для отчётов:
- сотрудник
- период
- (иногда) тип заказа / доставка / канал
Пример задачи: средний чек Купцова в марте
Логика такая же, как в СУММЕСЛИМН:
- отбор по сотруднику
- отбор по дате ≥ начало
- отбор по дате < конец
И это уже KPI, который можно показывать руководителю.
Почему важно делать период “двумя границами”
Потому что “март” — это слово, а Excel работает диапазоном дат. Границы делают формулу:
- точной
- быстрой
- переносимой
- универсальной
МИНЕСЛИМН: минимальное значение по условиям — вижу дно, а не среднее
МИН по всей таблице часто бесполезен.
Тебе нужно:
- минимальный чек у сотрудника
- минимальная сумма заказа в период
- минимальная выручка по филиалу
- минимальный показатель KPI в выборке
МИНЕСЛИМН — это функция для “краёв” данных.
Зачем искать минимум по условию
Потому что именно минимум часто показывает:
- ошибки ввода (лишний ноль, пропущенная цифра)
- тестовые записи
- возвраты/корректировки
- мошенничество
- сбои интеграции
- “мёртвые” продажи
Минимум — это не цифра. Это сигнал.
МАКСЕСЛИМН: максимум по условиям — рост или ошибка
Максимум бывает двух типов:
- реальный пик (успешная сделка, крупный клиент)
- ошибка (лишние нули, неверный формат, дубль)
МАКСЕСЛИМН нужен, чтобы:
- поймать выброс
- проверить корректность
- найти “звёздную” продажу
- быстро построить объяснение “почему так”
KPI-панель без сводных: как собрать систему из функций
Нормальная KPI-панель строится по схеме:
1. входные параметры (ячейки):
- сотрудник
- начало периода
- конец периода
- (опционально) статус/канал/доставка
2. блок KPI:
- сумма продаж (СУММЕСЛИМН)
- количество заказов (СЧЁТЕСЛИМН)
- средний чек (СРЗНАЧЕСЛИМН)
- минимум (МИНЕСЛИМН)
- максимум (МАКСЕСЛИМН)
3. блок проверки качества:
- пустые даты
- пустые суммы
- текстовые даты
- суммы = 0
- дубли
С таким подходом у тебя получается отчёт:
- который не надо обновлять как сводную
- который можно копировать по отделам
- который работает на больших таблицах
- который легко автоматизировать
👉 Если хочешь, чтобы серия оставалась на уровне “профессионально и применимо”, подпишись на канал и напиши комментарий: какие KPI тебе нужны чаще всего (сумма/кол-во/средний/мин/макс). Я подстрою примеры под реальную работу, а не под учебник.
Контроль качества данных: без этого любая аналитика — красивые цифры на песке
На больших таблицах (десятки тысяч строк) ошибки неизбежны:
- дата текстом
- пробелы в фамилии
- суммы строкой
- пустые значения
- лишние заголовки в диапазонах
- дубли строк после выгрузки
Если ты не проверяешь качество, то KPI превращаются в “примерно”.
Минимальный набор проверок (жёсткий, но честный)
- есть ли пустые даты
- есть ли пустые суммы
- есть ли суммы ≤ 0
- есть ли текст в столбце сумм
- есть ли нераспознанные даты
- есть ли дубли (по ключу: дата+сотрудник+сумма или по ID)
Ускорение расчётов на больших таблицах: почему формулы начинают “тормозить”
Причины почти всегда одинаковые:
- считаешь целые столбцы там, где достаточно диапазона
- условия построены так, что Excel пересчитывает лишнее
- нет таблицы (Ctrl+T), диапазоны “плавают”
- много повторяющихся формул без вынесения параметров
- грязные данные → Excel тратит ресурсы на попытку понять типы
Практические правила скорости
- используй таблицы Excel (Ctrl+T)
- не ссылайся на миллион строк без нужды
- вынеси параметры в отдельный блок
- делай период через две ячейки (начало/конец)
- проверяй типы данных (дата должна быть датой)
VBA-проверка качества данных перед аналитикой
Ниже макрос, который делает “приземлённый аудит” листа:
- проверяет пустые даты
- пустые суммы
- текст в сумме
- нулевые/отрицательные суммы
- выводит итог
Sub AuditSalesData()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim colDate As Long, colEmp As Long, colSum As Long
Dim emptyDates As Long, emptySums As Long, textSums As Long, nonPositive As Long
Set ws = ActiveSheet
colDate = 1
colEmp = 2
colSum = 4
lastRow = ws.Cells(ws.Rows.Count, colDate).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, colDate).Value = "" Then emptyDates = emptyDates + 1
If ws.Cells(i, colSum).Value = "" Then emptySums = emptySums + 1
If ws.Cells(i, colSum).Value <> "" Then
If Not IsNumeric(ws.Cells(i, colSum).Value) Then
textSums = textSums + 1
Else
If CDbl(ws.Cells(i, colSum).Value) <= 0 Then nonPositive = nonPositive + 1
End If
End If
Next i
MsgBox "Проверка завершена" & vbCrLf & _
"Пустые даты: " & emptyDates & vbCrLf & _
"Пустые суммы: " & emptySums & vbCrLf & _
"Сумма не число: " & textSums & vbCrLf & _
"Суммы <= 0: " & nonPositive, vbInformation, "Аудит данных"
End Sub
Если у тебя в таблице другие столбцы — меняются только colDate, colEmp, colSum. Логика остаётся.
Итог Части 4: у тебя теперь полный набор для управленческой аналитики в Excel
Эта серия закрывает связку “аналитика без сводных”:
- СЧЁТЕСЛИ/СЧЁТЕСЛИМН — сколько
- СУММЕСЛИ/СУММЕСЛИМН — на какую сумму
- СРЗНАЧЕСЛИ/СРЗНАЧЕСЛИМН — средний чек и KPI
- МИНЕСЛИМН/МАКСЕСЛИМН — крайние значения и аномалии
- контроль качества и ускорение расчётов
Ты можешь строить KPI-панели так, чтобы:
- цифры сходились
- отчёт не ломался при смене периода
- формулы были универсальными
- данные проверялись до принятия решений
👉 Если серия полезна — подпишись на канал и напиши комментарий: какой показатель тебе важнее всего в реальной работе (сумма, кол-во, средний чек, мин/макс, период). Я на основе этого соберу шаблон KPI-панели под твой формат.
Аналитические функции Excel — МИН, МАКС, СРЗНАЧ, СЧЁТ: база анализа больших таблиц. ЧАСТЬ 1
СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel — анализ данных по условиям без сводных таблиц. ЧАСТЬ 2
СУММЕСЛИ И СУММЕСЛИМН В EXCEL: КАК СУММИРОВАТЬ ПО УСЛОВИЯМ И ДЕЛАТЬ ОТЧЁТЫ, КОТОРЫМ ВЕРЯТ. ЧАСТЬ 3