Каждый пользователь электронных таблиц применял функцию SUM тысячи раз. Она подходит для учебных задач и небольших таблиц, но в реальной работе оказывается слишком простым инструментом. Если вам нужны надежные итоги, существует более эффективный подход, который избавляет от лишних ошибок и головной боли при составлении отчетов.
Почему классическая функция SUM подводит при работе с отчетами
Функция SUM суммирует абсолютно всё, независимо от того, видны ячейки или нет. Такова её задача. Однако возникают ситуации, когда данные фильтруются или определенные строки скрываются. В таких случаях SUM продолжает учитывать все скрытые значения в фоновом режиме. В результате итоговая сумма увеличивается, а отчеты искажаются. Если вам когда-либо приходилось объяснять, почему общий итог превышает объем отфильтрованных данных, вы понимаете суть проблемы. Многие пользователи сталкиваются с этим ограничением, постоянно корректируя формулы вручную, пока не открывают для себя функцию, способную справляться со всеми базовыми задачами без подобных трудностей — SUBTOTAL.
Как работает функция SUBTOTAL и почему она точнее
Представьте таблицу с тысячами строк данных о продажах. Если вы отфильтруете столбец, чтобы увидеть показатели только по определенному товару, остальные числа исчезнут с экрана, но SUM продолжит складывать их в фоне. То же самое касается функций COUNT и AVERAGE. В отличие от нее, SUBTOTAL адаптируется автоматически. Когда вы применяете фильтрацию, итоговое значение пересчитывается на лету, отображая исключительно видимые отфильтрованные строки.
Синтаксис формулы выглядит следующим образом: =SUBTOTAL(function_num, range). Возможности не ограничиваются только суммированием. Изменяя первую цифру в формуле, можно переключаться между суммой, средним значением, количеством, минимумом, максимумом и другими расчетами.
Кроме того, можно указать SUBTOTAL включать скрытые строки, если убрать первую цифру из параметра function_num. Например, аргумент 1 вычисляет среднее для скрытых ячеек, а 101 игнорирует их. В отличие от SUM, функция SUBTOTAL не производит двойного счета. При наличии промежуточных итогов по категориям и общего итога внизу SUBTOTAL умеет пропускать другие промежуточные итоги, тогда как SUM просто объединяет всё вместе и завышает показатели. Подобная особенность исключает появление вложенных сумм.
Применение SUBTOTAL в Excel и Google Sheets
Гибкость делает SUBTOTAL очевидным выбором: вы получаете расширенные возможности без дополнительной сложности. Чтобы получить сумму ячеек от C2 до C15 с игнорированием скрытых строк, применяется следующая формула:
=SUBTOTAL(109, C2:C15)
Такой подход корректно работает как для полной таблицы, так и для отфильтрованных данных. Однако другие показатели могут поначалу оставаться некорректными: функция COUNT продолжает показывать общее число строк, а среднее значение делится на него, оказываясь ниже ожидаемого. Эту проблему легко решить, поскольку SUBTOTAL поддерживает функции COUNT и COUNTA. Например, вместо стандартной формулы подсчета можно использовать:
=SUBTOTAL(103, A2:A15)
Число 103 соответствует функции COUNTA. Благодаря этому счетчики начинают автоматически адаптироваться при фильтрации данных, а средние значения всегда отображают корректный результат. SUBTOTAL также поддерживает функции MAX и MIN. Всего функция охватывает 11 различных вариантов расчетов, которых достаточно для большинства сводных таблиц. Все описанные возможности одинаково работают и в Google Sheets.
Существуют лишь две причины продолжать использовать SUM: если вы никогда не фильтруете и не скрываете строки, не передаете файлы другим людям и не проверяете цифры, либо если вам нравится объяснять расхождения в отчетах. Для всех остальных переход на SUBTOTAL делает электронные таблицы динамичными, надежными и защищенными от ошибок в повседневной отчетности.
Как использовать SUBTOTAL в Excel и Google Sheets
Синтаксис формулы устроен максимально просто: =SUBTOTAL(function_num, range). Меняя первое число в аргументе, можно мгновенно переключаться между расчетом суммы, среднего значения, количества, минимума, максимума и другими полезными вычислениями.
Вы можете явно указать функции SUBTOTAL необходимость учитывать скрытые строки, просто отбросив первую цифру в коде параметра function_num. Например, аргумент 1 вычислит среднее значение с учетом скрытых ячеек, а аргумент 101 полностью проигнорирует их.
Если в вашей таблице настроены промежуточные итоги для отдельных категорий, а в самом низу выводится общий итог, обычная функция SUM начинает суммировать всё подряд, создавая вложенные суммы и существенно раздувая итоговые цифры. В отличие от нее, SUBTOTAL обладает достаточной «интеллектуальностью», чтобы автоматически пропускать другие промежуточные итоги.
Применение конкретных формул на практике выглядит следующим образом. Допустим, вам нужно просуммировать диапазон ячеек от C2 до C15 с автоматическим игнорированием скрытых строк. Для этого используется формула:
=SUBTOTAL(109, C2:C15)
Применение данного подхода к строкам позволяет корректно рассчитывать показатели как для всей таблицы целиком, так и после применения фильтров, из-за чего числа наконец начинают сходиться. Тем не менее, если использовать стандартные формулы параллельно, они продолжат выдавать ошибки: классический подсчет через COUNT продолжит выводить общее количество строк (например, 14), из-за чего средние значения будут делить общую сумму на неверное число и окажутся заниженными.
Эту проблему легко исправить. Вместо привычного подсчета через =COUNTA(A2:A15) для ячейки B16 следует применить аналог от SUBTOTAL:
=SUBTOTAL(103, A2:A15)
Здесь число 103 выступает в роли официального номера функции COUNTA. После такой замены счетчики начинают динамически адаптироваться под любую фильтрацию данных, а расчет средних значений начинает показывать абсолютно верные цифры.
Помимо прочего, SUBTOTAL полноценно поддерживает функции поиска максимального (MAX) и минимального (MIN) значений, что неоднократно выручает при анализе больших массивов. Всего этот инструмент охватывает 11 различных расчетных функций. И хотя он не способен полностью заменить абсолютно каждую существующую формулу в Excel, данного набора из 11 вариантов более чем достаточно для подавляющего большинства повседневных сводных таблиц.
Все рассмотренные возможности работают абсолютно одинаково и в популярном редакторе Google Sheets — полученные знания применимы на любой выбранной вами платформе.
