Почему Excel насчитал лишнее после фильтра
Вы оставили в таблице только домашние расходы. На экране три суммы: 120, 150 и 130. Вместе это 400, а итог под таблицей показывает 560. Проверять каждую цифру полезно, но причина может быть проще: формула считает весь диапазон, включая строки, которые фильтр убрал с экрана.
Разберём на пяти вымышленных записях, когда нужна СУММ, а когда — ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 9 или 109. У этих двух кодов есть важное различие: они по-разному относятся к строкам, скрытым вручную.
Что именно проверено
14 ноября 2026 года мы открыли отдельно подготовленную учебную книгу в настольном Microsoft Excel для Windows, версия файла 16.0.20527.20150, в режиме чтения. Сохранённые результаты формул были удалены перед открытием; Excel пересчитал их заново. Ниже — результаты трёх подготовленных состояний книги и настоящие снимки Excel.
Это проверка расчёта формул, а не запись ручного ввода и нажатий в меню. Пошаговые действия ниже предназначены для самостоятельного повторения в доступной для редактирования книге. Подписи SUM и SUBTOTAL на снимках — английские названия функций СУММ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Подготовьте пять записей
Создайте новую книгу. В A5: C5 запишите заголовки «№», «Категория», «Сумма». Затем внесите данные:
| Строка Excel | A: № | B: Категория | C: Сумма |
|---|---|---|---|
| 6 | 1 | Дом | 120 |
| 7 | 2 | Работа | 90 |
| 8 | 3 | Дом | 150 |
| 9 | 4 | Работа | 70 |
| 10 | 5 | Дом | 130 |
Суммы в C6:C10 должны быть числами. Для контрольной проверки в свободной ячейке можно использовать =ЕЧИСЛО(C6): для числа результат — ИСТИНА. Протяните такую проверку по пяти строкам, если данные копировались из другого источника.
Полный итог легко проверить отдельно: 120 + 90 + 150 + 70 + 130 = 560. Категория «Дом» даёт 400, категория «Работа» — 160.
В B13:B15 подпишите три способа расчёта, а в C13:C15 введите:
| Ячейка | Формула |
|---|---|
| C13 | =СУММ(C6:C10) |
| C14 | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C6:C10) |
| C15 | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;C6:C10) |
Итоги находятся за пределами C6:C10. Важно не включать их обратно в исходный диапазон: иначе вы измените задачу или получите циклическую ссылку. В наших русских формулах аргументы разделены точкой с запятой; если Excel ожидает другой разделитель, используйте принятый в ваших региональных настройках.
Сценарий 1. Все строки видимы
Пока нет фильтра и скрытых строк, все три формулы дали 560. Код 9 не означает «девять ячеек», а 109 — «сто девять ячеек». Первый аргумент выбирает способ расчёта и правило обработки скрытых строк.
На этом состоянии легко пропустить различие функций: одинаковые ответы ещё не означают, что формулы будут одинаково вести себя после изменения видимости.
Сценарий 2. Фильтр оставляет только «Дом»
Для самостоятельного повторения выделите A5:C10, включите «Данные → Фильтр», откройте список в заголовке «Категория» и оставьте только значение «Дом». Это обычный фильтр диапазона; преобразовывать диапазон в таблицу Excel для примера не требуется.
В подготовленном состоянии с таким фильтром Excel показал:
• СУММ — 560;
• ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 9 — 400;
• ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 109 — 400.
Откуда разница 160? Это 90 + 70 из категории «Работа». Фильтр временно скрывает неподходящие строки, но не удаляет их данные. СУММ продолжает считать заданный диапазон. Microsoft описывает фильтрацию как временное скрытие данных.
Если вам нужен полный расход независимо от выбранной категории, 560 — правильный результат. Если нужен итог по текущему фильтру, подходят оба варианта ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Здесь между 9 и 109 различия ещё нет.
Сценарий 3. Фильтра нет, но одна строка скрыта
Начните снова с полного списка. Очистите условие фильтра в столбце «Категория» и убедитесь, что все пять записей появились. Затем для повторения выделите строку 8 щелчком по её номеру и выберите команду «Скрыть» в контекстном меню. Не удаляйте строку.
В отдельно подготовленном состоянии без фильтра, но со скрытой строкой 8, Excel вернул 560, 560 и 410.
Теперь разница равна 150 — значению скрытой строки. Код 9 включает её в расчёт, код 109 исключает. СУММ также сохраняет полный итог.
Для самостоятельной проверки снова покажите строку 8: выделите номера соседних строк 7-9 и используйте команду отображения скрытых строк. Все три результата должны вернуться к 560. Это контрольное действие для читателя, а не дополнительный зафиксированный опыт этой проверки.
Как выбрать формулу под свою задачу
| Что нужно посчитать | Подходящий вариант |
|---|---|
| Все числовые значения заданного диапазона независимо от фильтра и скрытия строк | СУММ |
| Строки, прошедшие фильтр, включая скрытые вручную | ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 9 |
| Строки, прошедшие фильтр, с исключением скрытых вручную | ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 109 |
Оба кода исключают строки, отсеянные фильтром. Их различие относится к ручному скрытию строк. Поэтому фраза «9 считает скрытые строки» без уточнения вводит в заблуждение. Это правило описано в справке ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Не заменяйте все суммы на 109 автоматически. Например, сотрудник мог скрыть часть строк для удобства просмотра, хотя их значения по-прежнему нужны в итоговом отчёте. В таком случае исключение этих данных изменит смысл расчёта.
Три границы этого приёма
Скрытые столбцы — другой случай. Microsoft указывает, что функция рассчитана на вертикальные диапазоны. Скрытие столбца внутри горизонтального диапазона B2: G2 не заставляет код 109 исключить его значения. В нашем примере данные расположены по строкам одного столбца.
Вложенные итоги не означают любые дубли. По документации внешняя ПРОМЕЖУТОЧНЫЕ. ИТОГИ не прибавляет повторно результаты вложенных формул той же функции внутри своих ссылок. Это не обещание игнорировать любую строку с надписью «Итого», обычной СУММ или скопированным числом. Эти два ограничения приведены по справке; отдельные опыты с ними здесь не выполнялись.
Текстовое число останется отдельной проблемой. Если «150» хранится текстом, смена способа суммирования сама по себе не исправит тип данных. Проверяйте исходный диапазон, числовой тип и ошибки, прежде чем объяснять любую разницу фильтрацией. Справка СУММ отдельно описывает пропуск текстовых значений в ссылках.
Если работаете с оформленной таблицей Excel, можно включить штатную строку итогов. В официальном примере Microsoft при выборе суммы используется ПРОМЕЖУТОЧНЫЕ. ИТОГИ с кодом 109 и структурированной ссылкой на столбец. Синтаксис будет отличаться от нашего C6:C10, но назначение кода сохраняется. Пример строки итогов таблицы.
Самопроверка перед отправкой отчёта
Ответьте на три вопроса:
1. Должна ли сумма меняться при выборе категории в фильтре?
2. Нужно ли включать строки, которые кто-то скрыл вручную?
3. Формула ссылается только на нужные исходные данные, а значения действительно числовые?
Для нашего примера ответы можно проверить по итоговой таблице:
| Проверенное состояние | СУММ | Код 9 | Код 109 |
|---|---|---|---|
| Все пять записей видны | 560 | 560 | 560 |
| Фильтр «Дом» | 560 | 400 | 400 |
| Без фильтра скрыта строка 8 | 560 | 560 | 410 |
Если после фильтра видны расходы на 400, а формула показывает 560, сначала проверьте, какой итог она должна считать. Правильная формула определяется задачей отчёта, а не только тем, какие строки сейчас помещаются на экране.
Учебная книга и текст подготовлены с помощью ChatGPT. Все данные вымышлены. Три приведённых набора результатов проверены пересчётом в настоящем Excel; иллюстрации — снимки его интерфейса.
Источники для проверки
Microsoft Support: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ (функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ)», «Функция СУММ», «Фильтрация данных в диапазоне или таблице», «Подведение итогов данных в таблице Excel». Документация сверена 14 ноября 2026 года.