coruТаблицы и данные на практике

Сумма видимых строк в Excel: чем отличаются 9 и 109

Фильтр и команда «Скрыть» убирают строки с экрана, но по-разному влияют на итог. Сначала решите, какие строки нужно учитывать, затем проверьте формулу на небольшом примере.

Автор Coru · Опубликовано · Обновлено

Выберите, что должно входить в сумму

Вы отфильтровали список, а итог по-прежнему учитывает исчезнувшие строки. Проверьте формулу: СУММ складывает числа своего диапазона независимо от видимости строк. Для вертикального списка ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает фильтр, а при нужном первом аргументе — и ручное скрытие.

  • Все строки B2:B7: =СУММ(B2:B7).
  • Строки, прошедшие фильтр, включая скрытые вручную: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B7).
  • Строки, прошедшие фильтр и не скрытые вручную: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:B7).

Один список — три правильных ответа

В нашем вымышленном примере шесть чисел дают сумму 100. Фильтр исключит позиции C и F: это 25 + 8 = 33. Затем мы вручную скроем позицию E — с экрана исчезнет ещё 30. Видимыми останутся числа 12, 18 и 7.

Шесть чисел: 12, 18, 25, 7, 30 и 8. СУММ учитывает все и даёт 100. ПРОМЕЖУТОЧНЫЕ.ИТОГИ с 9 исключает отфильтрованные 25 и 8: итог 67. Режим 109 также исключает скрытое вручную 30: итог 37.
После фильтра, исключающего 25 и 8, и ручного скрытия строки с 30: СУММ = 100, ПРОМЕЖУТОЧНЫЕ.ИТОГИ с 9 = 67, со 109 = 37. Перечёркнутые числа не входят в соответствующий итог. Иллюстрация: Coru. Авторская схема Coru по разобранному примеру.

Вставьте пример в A1

text

Позиция	Сумма	Группа
A	12	Оставить
B	18	Оставить
C	25	Убрать
D	7	Оставить
E	30	Оставить
F	8	Убрать

В примере столбцы разделены табуляцией: он займёт A1:C7. Числа окажутся в B2:B7.

Подготовьте сравнение

На том же листе поместите следующие формулы в B9, B10 и B11, за пределами исходного диапазона. Здесь русские имена функций и точка с запятой. Используйте язык функций и разделитель аргументов, которые принимает ваш Excel.

B9: все шесть чисел

excel

=СУММ(B2:B7)

Ожидаемый результат: 100, включая строки, исключённые фильтром и скрытые вручную.

B10: итог с учётом фильтра

excel

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B7)

После фильтра ожидаем 67. Ручное скрытие строки 6 оставит этот итог равным 67.

B11: исключаем оба вида скрытых строк

excel

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:B7)

После фильтра и ручного скрытия ожидаем 37.

Примените фильтр, затем скройте строку

  • Выделите A1:C7 и выберите «Данные → Фильтр». Откройте стрелку в заголовке «Группа», оставьте выбранным только «Оставить» и подтвердите. Формулы итогов останутся ниже диапазона фильтра.
  • Проверьте три результата: 100, 67, 67. Обе формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ совпадают: фильтр исключил одни и те же два числа.
  • Щёлкните правой кнопкой по номеру строки 6 и выберите «Скрыть». Исчезнет целая строка листа с позицией E и числом 30. Теперь должны получиться 100, 67, 37.
  • Для проверки снова отобразите строку 6, сохранив фильтр. Оба промежуточных итога должны вернуться к 67.

Ожидаемые итоги по шагам

text

Состояние              СУММ   9   109
Все строки видны        100  100  100
Группа = Оставить       100   67   67
Затем скрыть строку 6   100   67   37
Отобразить строку 6     100   67   67

Coru проверил арифметику по правилам из документации Microsoft. Это ожидаемые результаты, а не запись запуска Excel.

Если результат всё ещё отличается

  • Проверьте диапазон в строке формул: в этом примере нужен B2:B7, без самой ячейки итога.
  • Установите, что убрало строку с экрана: фильтр или ручное скрытие. Одного слова «скрытая» недостаточно.
  • Правило относится к строкам вертикального диапазона. Скрытие столбца не исключает его из =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2).
  • Функция также пропускает вложенные ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Это не защита от любых повторных итогов. На отдельном листе введите 12 в B2, 18 в B3 и =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B3) в B4. Тогда =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B4) даст 30, а =СУММ(B2:B4) — 60. Число 30, введённое вручную, или формула СУММ в B4 не являются таким вложенным итогом.

Если данные уже оформлены как таблица Excel

Для настоящей таблицы Excel включите «Строку итогов» на вкладке её конструктора, затем выберите «Сумма» в меню итога нужного столбца. В примере Microsoft создаётся ПРОМЕЖУТОЧНЫЕ.ИТОГИ со 109 и структурированной ссылкой на столбец. Посмотрите получившуюся формулу, чтобы подтвердить правило расчёта.

Проверьте, какие строки считаются

Выберите ответ или откройте пояснения ниже.

Фильтр по-прежнему исключает 25 и 8. Вы вручную скрыли строку с числом 30. Что должна вернуть формула ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B7)?

Ответы и пояснения
  • 37: 37 получается со 109. При аргументе 9 скрытое вручную число 30 остаётся в сумме.
  • 67: Верно: 12 + 18 + 7 + 30 = 67. Фильтр убирает 25 и 8, но ручное скрытие не исключает 30 при аргументе 9.
  • 100: 100 включает все шесть чисел. И 9, и 109 исключают строки, убранные фильтром.

Источники и материалы

Помогите улучшить руководство