Ольга закрывается вуалью, Обломов оглядывается, и оба спешат из сада. Им хочется ускользнуть от чужих взглядов. Человек, которого перестали видеть, при этом никуда не делся. У строк в Excel есть похожее свойство: фильтр убирает их с глаз, а числа остаются на листе.
Допустим, вы готовите луковицы тюльпанов для обмена с соседями. В трёх пакетах лежит 10, 20 и 30 штук. Средний пакет пока решили оставить себе, его строку исключили фильтром. Он лежит под столом, зато на экране уже будто исчез. Видны 10 и 30, а обычная сумма всё ещё показывает 60. Пакет освоил светскую жизнь Обломова: сам не показывается, а в списке гостей числится. Поправим состав суммы, сохранив сам список.
Разберём способ в настольном Excel для Windows с русскими именами функций, например Microsoft 365 или Excel 2024. Понадобится уже доступная программа и пустой лист. Для самой формулы ИИ не обязателен; помощник пригодится, если вы захотите перенести способ в свою книгу или разобраться с отличающимся результатом.
Формула промежуточных итогов в Excel: начнём с трёх строк
Сначала покажем действие без длинного перечня функций. Слева все три числа, справа — результат выбора двух строк:
Схема ожидаемого расчёта. Фильтр меняет видимый состав, исходные значения остаются на листе.
Создайте новый пустой лист и заполните A1:B4. Буква обозначает столбец, число — строку; ячейка B3 находится во втором столбце на третьей строке.
| Строка листа | A — Код | B — Количество |
|---|---|---|
| 1 | Код | Количество |
| 2 | Пакет-1 | 10 |
| 3 | Пакет-2 | 20 |
| 4 | Пакет-3 | 30 |
Вводите количества отдельными числами: 10, 20, 30. Слова «штук» в эти ячейки не добавляйте; единицу можно оставить в заголовке. Коды помогают узнать нужный пакет после скрытия строки. Строки сомкнутся, а «Пакет-3» останется третьим пакетом; переименовывать его во второй из-за нового соседа не придётся.
В E1 напишите «Видимый итог». Выберите E2, вставьте туда формулу и нажмите Enter:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:B4)В E2 должно появиться 60. Пока все строки видны, складываем 10 + 20 + 30. Это первая опора: формула читает нужные три числа ещё до работы с фильтром.
Здесь 109 означает суммирование с пропуском вручную скрытых строк; отфильтрованные строки функция тоже исключает. B2:B4 — вертикальный участок от B2 до B4 включительно. Эти свойства и синтаксис описаны в справке Microsoft о функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Мы поставили итог в E2, сбоку от исходного списка. Фильтровать будем только A1:B4. В этом опыте E2 стоит напротив строки с 10: мы её оставляем видимой, поэтому итог сохранится на экране при исключении среднего пакета.
Схема размещения формулы в E2. Коды в своём листе можно назвать «Пакет-1», «Пакет-2», «Пакет-3»; положение данных и итога остаётся тем же.
Исключим средний пакет фильтром
Выделите мышью A1:B4, включая заголовки. Откройте Данные → Фильтр. В заголовках появятся стрелки. Нажмите стрелку у «Количество», снимите флажок у значения 20, оставьте 10 и 30 и подтвердите выбор кнопкой ОК.
На листе должны остаться строки с 10 и 30. Число в E2 должно стать 40. Посмотрите на номера строк слева: после 2 будет идти 4. Между ними по-прежнему живёт строка 3 с двадцатью луковицами, просто сейчас она скрыта вместе с кодом среднего пакета.
Не вводите в итоговую ячейку 40 вручную. Нам нужен результат, который меняется по выбранному составу. Когда затем вернём средний пакет, формула сама должна учесть его количество.
Порядок включения фильтра, выбора значений и очистки описан в инструкции Microsoft по фильтрации диапазона. Фильтр временно скрывает неподходящие строки целиком. Перемещать пакеты в другой лист или стирать их данные для этого не требуется.
Теперь вернём все три строки. Нажмите значок фильтра в заголовке «Количество», снова отметьте 20 и подтвердите выбор. Можно выбрать все значения списка. На листе снова видны 10, 20 и 30, а в E2 — 60. Формула в ячейке остаётся прежней.
Ожидаемая цепочка 60 → 40 → 60. Проверяем оба изменения: исключение строки и её возвращение.
Теперь, когда проверка вернула исходные 60, сохраните книгу через Файл → Сохранить как, выбрав свою папку и формат книги Excel .xlsx. Дайте ей узнаваемое имя, например «Луковицы — видимый итог». Для первого сохранения можно оставить все три строки видимыми, чтобы исходный состав был понятен при следующем открытии.
Почему обычная сумма продолжала считать 60
Запись =СУММ(B2:B4) складывает значения указанного участка. После фильтра этот участок по-прежнему включает B3 с числом 20. Исчезновение строки с экрана само по себе не меняет адреса формулы.
Промежуточные итоги в Excel позволяют связать расчёт с видимостью строк. При коде 109 средний пакет перестаёт участвовать в сумме, когда его строка скрыта. Вернули строку — вернулось и количество. Поэтому взгляд на отобранный список и результат формулы теперь согласованы.
В собственной книге полезно подписью различать два показателя: «Всего в списке» и «Видимый итог». Первый отвечает на вопрос обо всех записанных пакетах, второй — о тех, которые сейчас оставлены на экране. Иногда нужны оба. Различие помогает понять, почему рядом могут стоять два верных, но разных числа.
Сама надпись «Итого» этого выбора не делает. Представьте, что вы отправили соседу лист, на котором два пакета видны, а итог включает третий. Он готовит место для шестидесяти луковиц, вы приносите сорок. У него уже готова клумба для призраков: двадцать посадочных мест ждут тех, кто остался у вас под столом. Число верно для всего запаса, но на обмен вы выбрали другой состав. Подпись «Видимый итог» сразу говорит, сколько вы приготовили к передаче. Весь запас при этом можно хранить в той же книге: третий пакет ждёт своего решения под столом.
Если строку скрыли руками
Фильтр выбирает строки по значениям, а команда «Скрыть» убирает выбранную строку вручную. Для нашего кода 109 оба способа скрытия исключают её количество. Если в примере вручную скрыть строку 3 с числом 20, ожидаемый итог тоже равен 40. Вернули строку — снова 60.
У той же функции есть код 9. Он тоже пропускает отфильтрованные строки, но учитывает строки, скрытые вручную. Поэтому для результата «сумма только видимых строк» мы выбрали именно 109. Менять его на 9 ради сокращения записи не нужно: изменится поведение при ручном скрытии.
Это правило относится к строкам вертикального списка. Если спрятать столбец, число из него таким способом из расчёта не исключается. Не переносите ожидание «исчезло с экрана — исчезло из суммы» на горизонтальный ряд. Для нашего листа все количества стоят друг под другом в B2:B4, и это положение существенно.
Помощник для своей книги
После маленькой пробы можно перенести расчёт в собственный список: расходники для хобби, книги для передачи, вещи для поездки. Сначала найдите столбец с числами. Для своего списка разместите результат ниже всех строк, которые собираетесь фильтровать: скрытие строки действует по всей ширине листа. Если непонятно, какие адреса назвать, откройте DeepSeek и вставьте в обычный чат:
Помоги посчитать сумму только видимых строк в моём Excel. Сначала уточни программу, версию, устройство и язык функций. Затем спроси заголовок столбца с количеством, адреса первой и последней ячейки и где ниже фильтруемого списка можно разместить итог. Давай по одному действию: что выделить, куда вставить формулу и какое число ожидать. Жди моего результата. Если он отличается, разберём причину до следующего шага. Для ориентира у меня есть пример B2:B4 со значениями 10, 20, 30 и формулой =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:B4): ожидаю 60, после исключения 20 — 40, после возвращения — 60.
Достаточно обезличенных заголовков, адресов и нескольких чисел. Целую рабочую книгу для объяснения этого приёма передавать не обязательно. Обычный чат опирается на ваше описание; что сейчас видно в Excel, сообщаете вы.
Когда помощник предложит формулу для своей колонки, прочитайте вместе её адреса. Пусть в B2:B4 были луковицы, а ваши количества находятся в D5:D18. В новой записи должен оказаться именно ваш диапазон. Попросите: «Объясни, какая первая и последняя ячейка участвуют в сумме и почему заголовок не входит». После этого проверьте исключение одной известной строки и её возвращение. Если общий итог был 90, а исключённая строка содержит 15, ожидаем 75; вернули её — снова 90. Проверку подбирают по своим исходным числам, а не по совпадению с нашими 60.
Если в вашем файле количество и подпись ещё находятся в одной текстовой ячейке, сначала пригодится разделение текста по столбцам. К сумме возвращайтесь, когда числам выделен отдельный столбец.
Что сообщить, если результат отличается
Вместо «Excel опять всё считает неправильно» запишите наблюдение: «В E2 стоит такая формула; после фильтра видны 10 и 30, но итог 60». Так можно сопоставить адреса, скрытие и расчёт. Проверьте, что в E2 осталась формула с 109, а не число 60, однажды вставленное вручную.
Если формула охватывает другой диапазон, исправьте адреса по своему листу. Если количество выглядит как текст со словом «штук», покажите помощнику одну такую ячейку и попросите помочь отделить число. Если результат не обновляется, сообщите и об этом: помощник может провести по проверке режима пересчёта для вашей версии программы. Пробовать случайные новые формулы здесь менее полезно, чем выяснить одно конкретное расхождение.
Для разбора сообщения об ошибке и исходных ячеек есть порядок проверки формул Excel. Он пригодится, если вместо результата вы видите код ошибки. Для нашего примера важен контроль состава: сначала три пакета, потом два, затем снова три. Один раз увидеть правильные 60 мало — нужно ещё убедиться, что сумма следует за вашим выбором.
Перед увеличением списка проверьте последнюю строку диапазона. В записи B2:B4 новая ячейка B5 не названа. Если вы добавили в B5 ещё один пакет с 5 луковицами, все четыре пакета вместе дают 65. Прежний адрес B2:B4 его не охватывает. Расширьте ссылку до B2:B5 и убедитесь, что фильтр тоже включает новую строку. Затем исключите одну знакомую строку и верните её: новый полный состав должен восстановиться. Сам факт появления новой строки под списком не сообщает этой конкретной формуле новый адрес.
Несколько вопросов о видимом итоге
Почему результат стоит сбоку, а не внизу?
В нашем опыте E2 стоит напротив строки с 10, которую мы оставляем видимой. Если исключить и её, скрылась бы вся строка 2 вместе с E2. Для произвольного отбора поставьте итог ниже фильтруемого списка, например в E6 для данных A1:B4: тогда скрытие любой строки данных не спрячет результат.
Нужно нажимать «Данные → Промежуточные итоги»?
Для нашего способа достаточно ввести функцию в ячейку. Одноимённая команда меню строит другой способ оформления списка. Ограничения той команды не следует переносить на формулу. Функция используется и в таблицах Excel; Microsoft показывает её в справке строки итогов таблицы.
Это то же самое, что сумма по условию?
Здесь состав задаётся тем, какие строки видны. У СУММЕСЛИ и СУММЕСЛИМН условия записывают в самой формуле — например, код нужной группы. Выбирайте способ по тому, что должно менять результат: текущий отбор на экране или заданное условие по значениям.
Можно удалить строку с 20 и получить 40?
Удаление меняет сам набор данных. Фильтр позволяет временно убрать строку из отбора и затем вернуть её одним выбором. Для обмена луковицами нам пригодится полный список, поэтому здесь используем фильтр.
Что считать законченным результатом?
В сохранённой книге есть исходные 10, 20, 30 и отдельная формула. При всех строках видим 60, после исключения 20 — 40, после его возвращения — 60. Вы можете повторить этот переход и объяснить, какие строки участвуют в сумме.
Герои Гончарова торопились скрыться из сада; наши пакеты спокойно остаются на листе. Теперь можно выбирать, какие из них пойдут на обмен, и сразу видеть их количество. Средний пакет вернётся в расчёт вместе со своей строкой. Пока же сосед получит сорок настоящих луковиц — и ни одного посадочного места для невидимки.