Сумма после фильтра в Excel: как считать видимые продажи и скрытые строки

1. Определите, что означает выбранная сумма

Менеджер оставляет в выгрузке один SKU и ожидает увидеть итог по нему. Но рядом может стоять сумма всего исходного диапазона или показатель, рассчитанный при другом наборе фильтров. Чтобы избежать такого расхождения, отделите общий контроль по файлу от итога текущего отбора и подпишите оба значения.

Сохраните оригинальную выгрузку, а проверку проводите в копии книги. Выберите один кабинет, период и валюту. Уточните смысл денежного поля: начисление за продажу, стоимость заказа и выплата продавцу не взаимозаменяемы. Описанный приём меняет состав учитываемых строк, но не превращает оборот в прибыль и не проверяет правильность начислений.

Ниже используется обычный вертикальный диапазон в настольном Excel для Windows. Это учебная таблица редакции, а не формат выгрузки конкретной площадки. Все суммы числовые, возврат записан со знаком минус, каждая строка представляет отдельную операцию. В рабочем отчёте правила знаков сначала сверяют с описанием источника.

2. Подготовьте шесть проверяемых операций

В новом листе разместите заголовки в A1:D1: «Операция», «SKU», «Вид», «Сумма». Заполните строки 2–7 данными из таблицы: первая операция попадёт в строку 2, последняя — в строку 7. В денежные ячейки вводите числа без пробелов и символа рубля внутри значения; валюту обозначьте форматированием или заголовком. Возвраты введите как -200 и -100, используя обычный знак минуса на клавиатуре. Не переносите учебные строки в реальный отчёт.

До фильтрации общий контроль составляет 1 200 + 800 − 200 + 500 + 900 − 100 = 3 100 ₽. По SKU A получаем 1 800 ₽, по SKU B — 1 300 ₽. Эти три результата нужны как независимые контрольные числа для следующих действий. Если они не сходятся, сначала найдите отличающуюся исходную запись.

3. Включите фильтр и проверьте две формулы

Выделите A1:D7 и включите «Данные → Фильтр». В списке столбца SKU снимите выделение всех значений и оставьте A. Microsoft описывает этот порядок для фильтрации диапазона. В результате должны остаться три операции: 001, 002 и 003. Проверьте именно их состав, а не только число видимых строк.

Разместите контрольные формулы ниже диапазона, чтобы скрытие строк 2–7 не скрывало сами результаты. В F10 введите =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D2:D7), а в F11 — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:D7). Подпишите E10 «По фильтру, включая скрытые вручную», E11 — «Только видимые строки».

Оба результата должны равняться 1 800 ₽. В английском Excel им соответствует =SUBTOTAL(9,D2:D7) и =SUBTOTAL(109,D2:D7). Имена функций и разделитель аргументов зависят от настройки Excel. Введите формулу в принятом для вашей установки виде, сохранив номера функций и диапазон.

4. Проверьте разницу между 9 и 109

Не снимая фильтр SKU A, вручную скройте строку 3 с продажей на 800 ₽ через контекстное меню её номера. Теперь код 9 должен оставить 1 800 ₽, а код 109 — показать 1 000 ₽: 1 200 − 200. Именно такой разбор скрытых строк описан в справке Microsoft по ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Этот опыт помогает выбрать правило. Если строки прячут только для удобства просмотра, но они остаются частью отчёта, подходит 9. Если требуется сумма только фактически видимых строк, используйте 109. Во втором случае случайное скрытие операции меняет результат, поэтому значение нельзя передавать без контекста.

Покажите строку 3 обратно и убедитесь, что обе формулы снова дают 1 800 ₽. Затем очистите фильтр SKU: обе должны вернуть 3 100 ₽. Не используйте скрытие как способ исправить неподтверждённую операцию. Для спорной строки сохраните отдельный признак и причину проверки, чтобы коллега мог воспроизвести решение.

5. Для таблицы Excel настройте строку итогов

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

Посмотрите фактическую формулу: строка итогов допускает другие функции, и коллега мог настроить её иначе. Для столбца «Сумма» внутри строки итогов пример имеет вид =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Сумма]). Повторите контроль с одним SKU и ручным скрытием, прежде чем переносить результат в управленческий отчёт.

Если нужны итоги по нескольким денежным колонкам, настройте каждую отдельно и проверьте её ссылку. Microsoft отдельно предупреждает: обычное копирование и вставка формулы строки итогов может сохранить прежнюю ссылку на столбец. Одинаковые значения в соседних колонках — повод проверить формулы, а не подтверждение совпадения показателей.

6. Перед передачей результата проверьте ограничения

Посмотрите фильтры всех столбцов. Microsoft поясняет, что дополнительные фильтры сужают уже выбранное множество. Например, отбор SKU A вместе с видом «Продажа» даст в учебной таблице 2 000 ₽, поскольку возврат исключён. Это другой показатель, а не исправление суммы 1 800 ₽.

Функция предназначена для вертикальных данных: скрытие столбца не работает как исключение скрытой строки. Не включайте в диапазон вручную добавленные общие суммы. Вложенные ПРОМЕЖУТОЧНЫЕ.ИТОГИ функция пропускает, но это не разрешение смешивать исходные операции с произвольными итоговыми числами.

При обновлении файла сверьте границы диапазона с последней операцией. Сохраните в заметке имя выгрузки, период, показатель, фильтры, код функции и контрольную сумму без отбора. При нерешённых расхождениях обозначьте результат как требующий проверки. Для следующего анализа магазина можно перейти во «Внутреннюю аналитику WB и Ozon» HelpStat; описанная настройка выполняется в Excel, а автоматическое исправление этой книги здесь не предполагается.