Применение формулы ПРОМЕЖУТОЧНЫЕ ИТОГИ в Excel

Формула ПРОМЕЖУТОЧНЫЙ ИТОГ — основные функции с примерами.

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

Сегодня вы узнаете, как самостоятельно создавать формулы промежуточных итогов и каковы их преимущества.

• Синтаксис и номера функций.
• 3 важных особенности использования.
• Примеры с разными функциями и условиями.
• Потому что не работает?

Синтаксис и использование.

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

В отличие от других функций Excel, предназначенных для решения одной конкретной задачи, он на удивление универсален. Он может выполнять различные арифметические и логические операции, такие как подсчет количества значений, вычисление среднего, поиск минимума или максимума и многое другое.

ПРОМЕЖУТОЧНЫЙ ИТОГ доступен во всех версиях Excel 2019, 2016 и более ранних.

Его синтаксис следующий:

INTERMEDIATE.TOTALS (номер_функции; ссылка1; [ссылка2];…)

Где:

• Номер_функции — это число, указывающее, какую функцию использовать для вычисления промежуточного итога.
• Link1, link2,… — одна или несколько ячеек или диапазонов с исходными данными. Первый аргумент является обязательным, остальные (до 254) — необязательными.

Аргумент номер_функции может быть:

• 1-11 — Отфильтрованные ячейки игнорируются, но добавляются скрытые вручную строки.
• 101-111: вам нужно игнорировать все скрытые ячейки — отфильтрованные и скрытые вручную.

На самом деле нет необходимости запоминать все эти числа. Как только вы начнете вводить формулу промежуточного итога в ячейку или столбец формулы, Microsoft Excel отобразит список доступных вам функций с числами. Остается только выбрать подходящий.

Например, вот как вы можете написать формулу номер 9 для суммирования значений в ячейках с C2 по C8:

Чтобы добавить номер функции в формулу, дважды щелкните его в списке, затем введите точку с запятой, укажите диапазон, введите закрывающую скобку и нажмите Enter. Готовая формула будет выглядеть так:

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

Особенности формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

1. Учитывается только для столбцов, не подходит для строк.
2. Отфильтрованные значения игнорируются. Скрытые вручную значения учитываются функциями с номерами от 1 до 11.
3. Учитываются только необработанные данные. Никакие другие промежуточные итоги не включены.
4. Это применимо только к регулярным интервалам, это не работает с таблицами Excel.
5. Когда вы используете формулу промежуточного итога с функцией суммы, например SUM или AVERAGE, она вычисляет только ячейки с числами, игнорируя пробелы и нечисловые значения.

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

Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, возникает большой вопрос: зачем ее изучать? Почему бы просто не использовать общие функции, такие как СУММ, СЧЁТ, МАКС и т.д.? Ниже вы найдете ответ.

3 основных причины использовать ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel

По сравнению с традиционными функциями Excel, INTERMEDIATE.TOTAL предлагает следующие важные преимущества.

1. Вычисляет значения с учетом применяемого фильтра.

Поскольку функция Excel INTERMEDIATE.TOTALS игнорирует значения в отфильтрованных строках, вы можете использовать ее для создания динамической сводной таблицы данных, где промежуточные итоги автоматически пересчитываются на основе фильтра. Значения, скрытые фильтром, не учитываются.

Но если мы используем обычную функцию, например SUM, применение фильтра не изменит вычисленную сумму. Также добавляются числа, скрытые фильтром.

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

Стандартная функция СУММ, которая считает только необходимые ячейки, работать не будет. Вам нужно будет скопировать эти данные в другое место, чтобы никто не мешал или использовать функцию СУММЕСЛИ и постоянно корректировать ее в соответствии с необходимыми целями.

Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу с номером функции 9 или 109.

2. Вычисление только видимых ячеек.

Как вы помните, формулы промежуточных итогов с номерами функций от 101 до 111 игнорируют все скрытые, отфильтрованные и скрытые вручную ячейки. Поэтому при использовании функции «Скрыть» в Excel для удаления нерелевантных данных из представления используйте номер функции 101-111, чтобы исключить значения в скрытых строках из общего количества.

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

Следующий пример поможет вам лучше понять, как это работает: Промежуточный итог 9 против 109.

3. Игнорируются значения во вложенных формулах промежуточных итогов.

Если диапазон, указанный в формуле промежуточных итогов Excel, содержит другие формулы промежуточных итогов, эти промежуточные итоги будут проигнорированы, поэтому одни и те же числа не будут вычисляться дважды. Нам не нужен двойной счет. Красиво, не правда ли?

На скриншоте ниже формула 

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

игнорирует результаты формул промежуточных итогов в ячейках G7 и G120, как если бы вы использовали функцию СУММ с двумя отдельными диапазонами: СУММ (G2: C6, G8: G11).

Использование промежуточных итогов в Excel — примеры формул

Когда вы впервые сталкиваетесь с промежуточными итогами, это может показаться пугающим, запутанным и даже бессмысленным. Но как только вы узнаете все детали, вы поймете, что овладеть ими не так уж и сложно и что может быть много преимуществ. Следующие примеры дадут вам несколько полезных советов и новых идей.

Пример 1. Промежуточный итог с функцией 9 или 109?

Как вы уже знаете, INTERMEDIATE.TOTAL использует 2 набора номеров функций: 1-11 и 101-111. Все игнорируют отфильтрованные строки, но номера 1–11 включают строки, скрытые вручную, а номера 101–111 исключают их. Чтобы лучше понять разницу, давайте взглянем на следующий пример.

Чтобы суммировать отфильтрованные строки, вы можете использовать формулу промежуточного итога с функцией 9 или с функцией 109, как показано ниже:

Но если вы вручную скрыли отдельные элементы,

• с помощью команды Скрыть строки на вкладке Главная> Ячейки> Формат> Скрыть и показать»,
• или щелкнув строки правой кнопкой мыши и выбрав «Скрыть»,

и теперь вы хотите суммировать значения только в видимых строках, использование функции 109 — единственный вариант:

Остальные номера функций работают так же. Например, для подсчета непустых отфильтрованных ячеек подходит формула с функцией 3 или 103. Но только функция 103 может правильно подсчитывать видимые значения, если в диапазоне есть скрытые вручную строки.

Примечание. Функция Excel INTERMEDIATE.TOTALS Numbers 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете формулу = INTERMEDIATE.TOTAL (109; D2: H10) для суммирования чисел в горизонтальном диапазоне (строка за строкой), скрытие столбца F не повлияет на результат.

Пример 2. Промежуточные итоги с условием.

Если вы создаете сводный отчет или панель мониторинга, где вам нужно просматривать различные сводные данные, но у вас ограниченное пространство для размещения множества вариантов итогов, вы можете:

• В ячейке создайте раскрывающийся список, содержащий имена функций, такие как SUM, MAX, MIN и другие.
• В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, которые соответствуют именам функций в раскрывающемся списке.

Например, если предположить, что значения промежуточной суммы находятся в ячейках C2: C16, а раскрывающееся меню в A17 содержит элементы «Итого», «Среднее», «Максимум» и «Минимум», «динамическая» формула для промежуточной суммы будет выглядеть так: это:

= IF (A17 = «Сумма»; INTERMEDIATE.TOTAL (9; C2: C16); IF (A17 = «Среднее»; INTERMEDIATE.TOTAL (1; C2: C16); IF (A17 = «Minimum»; INTERMEDIATE.TOTAL)) (5; C2: C16); IF (A17 = «Максимум»; INTERMEDIATE.TOTAL (4; C2: C16);»»))))

И теперь, в зависимости от функции, выбранной пользователем из выпадающего меню, соответствующая функция будет вычислять значения в отфильтрованных строках:

Примечание. Если вдруг выпадающий список и ячейка формулы исчезнут с рабочего листа, не забудьте снова выбрать их в списке фильтров — это означает, что вы случайно отфильтровали их.

Я думаю, что с помощью этого метода вы можете заменить стандартные функции суммирования, найти максимум и минимум, среднее значение, счет и т.д. С помощью этой универсальной функции данных, но просто выполнить счет. Главное, чтобы не было пустых строк.

Также обратите внимание, что если бы мы использовали функцию СУММ для расчета суммы продаж, мы не смогли бы подсчитать продажи для конкретного клиента или группы клиентов в этой таблице. Вы должны использовать функцию СУММЕСЛИ для каждого из них.

Вы можете просто вычислить промежуточные итоги с условием, если отфильтруете необходимые строки по продуктам, клиентам или другим критериям. Результат будет показан только по значениям, оставшимся после фильтрации. В итоге расчет будет производиться только по ним.

Например, вот как можно рассчитать общий объем продаж темного шоколада без молочного шоколада с помощью фильтра:

Как видите, с помощью СУММ этого сделать нельзя. А использование СУММЕСЛИ означает исправление условия в формуле каждый раз или создание нескольких формул «на все случаи жизни». И здесь мы выбираем только то, что хотим, используя фильтр. Согласитесь, это намного проще и удобнее, чем писать и потом исправлять громоздкие формулы.

Почему промежуточные итоги не работают? Распространенные ошибки.

Если формула промежуточного итога возвращает ошибку, это, вероятно, вызвано одной из следующих причин:

#ЦЕНИТЬ! — num_function не является целым числом от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку на несколько листов в книге одновременно.

# DIV / 0! — возникает, когда указанная функция должна выполнить деление на ноль (например, вычисление среднего или стандартного отклонения для диапазона ячеек, не содержащего чисел).

#ИМЯ? — неправильно написано название функции — исправить ошибку проще простого:)

Совет. Если вам все еще не нравится функция ПРОМЕЖУТОЧНЫЙ.ИТОГО, вы можете использовать встроенный инструмент ПРОМЕЖУТОЧНЫЙ ИТОГО и автоматически вставлять формулы.