Условные расчёты на больших таблицах

Связка СУММЕСЛИМН и абсолютных ссылок превращает плоский журнал в отчёт, который не ломается при копировании.

СУММЕСЛИМН — функция, складывающая значения только тех строк, которые одновременно удовлетворяют нескольким условиям (по категории, городу, периоду и т.д.).

Зачем это нужно

Сводная таблица — это быстро, но иногда нужен живой отчёт прямо в ячейках: он пересчитывается сам при изменении данных, его формулы видны и понятны, его легко вставить в готовый бланк. Здесь и нужна СУММЕСЛИМН (англ. SUMIFS) — она суммирует с условиями. А чтобы один раз написанную формулу размножить по всей сетке отчёта, обязательно понадобятся абсолютные ссылки.

Синтаксис СУММЕСЛИМН

Функция читается так: «сложи значения из диапазона суммирования по тем строкам, где первый диапазон условий равен первому критерию, и второй — второму, и так далее».

=СУММЕСЛИМН(диапазон_суммы;
           диапазон_условия1; критерий1;
           диапазон_условия2; критерий2; ...)

Сначала идёт то, что складываем, затем пары «где искать — что искать». Условий может быть несколько, и все они соединяются логическим И: строка попадёт в сумму, только если выполнены все критерии сразу.

Формально, если строка $i$ имеет сумму $s_i$, категорию $k_i$ и месяц $m_i$, то результат для отчётной ячейки «категория $K$, месяц $M$» равен:

$$ \sum_{i\,:\;k_i = K \;\land\; m_i = M} s_i $$

То есть складываются только те $s_i$, у которых категория совпала с $K$ и месяц совпал с $M$.

Абсолютные и относительные ссылки в отчёте

Вспомним короля копирования — знак $ в ссылке. Он «замораживает» столбец или строку, чтобы при копировании формулы ссылка не «уезжала»:

Идея отчёта: диапазоны исходных данных (они на месте не двигаются) делаем абсолютными, а ссылки на подписи строк и столбцов отчёта — смешанными, чтобы при растягивании каждая ячейка подхватывала свою категорию и свой месяц.

Пример отчёта «категория × месяц»

Пусть журнал лежит так: столбец A — категория, B — месяц, C — сумма (строки 2:200). Слева в отчёте по строкам идут категории (столбец F), сверху по столбцам — месяцы (строка 1, начиная с G). Тогда в первой ячейке отчёта пишем одну формулу:

=СУММЕСЛИМН($C$2:$C$200;
           $A$2:$A$200; $F2;
           $B$2:$B$200; G$1)

Разберём ссылки. Диапазоны данных $C$2:$C$200, $A$2:$A$200, $B$2:$B$200 полностью заморожены — они одинаковы для всех ячеек отчёта. Критерий категории $F2: столбец F заморожен (категории всегда слева), а строка свободна — поедет вниз. Критерий месяца G$1: строка 1 заморожена (месяцы всегда сверху), а столбец свободен — поедет вправо. Теперь эту единственную формулу можно растянуть на всю прямоугольную область отчёта, и каждая ячейка посчитает свой срез.

Проверим логику на наших данных из урока про сводные. Возьмём отчёт по категориям за всё время. Связь СУММЕСЛИМН и абсолютных ссылок воспроизведём на Python — он повторяет, что делает формула при копировании по строкам отчёта:

jurnal = [
    ("Книги", 1200), ("Игры", 3000), ("Книги", 800),
    ("Книги", 500),  ("Игры", 2000), ("Книги", 700),
    ("Игры", 1500),  ("Игры", 1000),
]

# подписи строк отчёта (как столбец F)
kategorii = ["Игры", "Книги"]

def summeslimn(crit):
    # сумма по строкам, где категория == crit
    return sum(s for k, s in jurnal if k == crit)

for k in kategorii:
    print(k, summeslimn(k))

Вывод:

Игры 7500
Книги 3200

Игры: 3000 + 2000 + 1500 + 1000 = 7500; Книги: 1200 + 800 + 500 + 700 = 3200. Совпадает со строкой «Итог» сводной из первого урока — оба инструмента дают один ответ, просто СУММЕСЛИМН делает это живой формулой.

Как это работает

СУММЕСЛИМН внутри проходит по всем строкам диапазона условий, для каждой проверяет И-связку критериев и накапливает сумму подходящих. Критерии — это не только точное равенство: можно писать сравнения как текст, например ">=1000" (сумма не меньше 1000) или "<>Игры" (всё, кроме игр), а также шаблоны со звёздочкой. Важно: первым аргументом всегда идёт диапазон суммирования, а дальше — пары; перепутать порядок — типичная причина ошибки. Все диапазоны (суммы и условий) обязаны быть одной высоты, иначе функция вернёт ошибку.

Частые ошибки

• Забыли $ у диапазонов данных. При копировании относительный C2:C200 съедет на C3:C201, C4:C202 — и часть строк журнала выпадет из суммы. Диапазоны данных всегда абсолютные.
• Перепутали смешанные ссылки. Если у критерия категории не заморозить столбец (F2 вместо $F2), при сдвиге вправо формула начнёт читать категорию из соседнего столбца и посчитает чушь.
• Разная высота диапазонов. $C$2:$C$200 для суммы и $A$2:$A$150 для условия — разная длина, функция выдаст ошибку. Держите все диапазоны строго одинаковыми.
• Лишние пробелы в данных. «Игры » с пробелом на конце не совпадёт с критерием «Игры» — строка не попадёт в сумму, а итог тихо занизится.
• Перепутан порядок аргументов. В СУММЕСЛИМН диапазон суммы идёт ПЕРВЫМ (в отличие от СУММЕСЛИ, где он последний). Это сбивает многих.

Итоги

• СУММЕСЛИМН складывает значения по строкам, удовлетворяющим сразу нескольким условиям (логическое И).
• Порядок аргументов: сначала диапазон суммы, затем пары «диапазон условия — критерий».
• Диапазоны исходных данных делайте абсолютными ($C$2:$C$200) — иначе при копировании они уедут.
• Подписи строк/столбцов отчёта — смешанные ссылки ($F2, G$1): одну координату морозим, вторую отпускаем.
• Одна правильно написанная формула растягивается на весь прямоугольник отчёта и считает каждый срез сама.