Функция ПРОМЕЖУТОЧНЫЕ ИТОГО в Excel с примерами формул
В учебнике объясняются особенности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках.
В предыдущей статье мы обсудили автоматический способ вставки промежуточных итогов в Excel с помощью функции «Промежуточные итоги». Сегодня вы узнаете, как самостоятельно писать формулы промежуточных итогов и какие преимущества это вам дает.
Функция промежуточного итога в Excel — синтаксис и использование
Microsoft определяет промежуточный итог Excel как функцию, которая возвращает промежуточный итог в списке или базе данных. В этом контексте «промежуточный итог» — это не просто суммирование чисел в определенном диапазоне ячеек. В отличие от других функций Excel, предназначенных для выполнения только одной конкретной задачи, ПРОМЕЖУТОЧНЫЕ.ИТОГИ удивительно универсальны — они могут выполнять различные арифметические и логические операции, такие как подсчет ячеек, вычисление среднего значения, нахождение минимального или максимального значения и многое другое.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.
Синтаксис функции ПРОМЕЖУТОЧНЫЕИТОГИ в Excel следующий:
ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции, ссылка1, [ref2],…)
Где:
- Номер_функции — число, указывающее, какую функцию использовать для промежуточного итога.
- Ref1, Ref2, … – одна или несколько ячеек или диапазонов для промежуточного итога. Первый аргумент ref является обязательным, остальные (до 254) необязательны.
Аргумент номер_функции может принадлежать к одному из следующих наборов:
- 1–11 игнорируют отфильтрованные ячейки, но включают скрытые вручную строки.
- 101 – 111 игнорировать все скрытые ячейки – отфильтровываются и скрываются вручную.
Function_num Функция Описание 1 101 AVERAGE Возвращает среднее число. 2 102 COUNT Подсчитывает ячейки, содержащие числовые значения. 3 103 COUNTA Подсчитывает непустые ячейки. 4 104 MAX Возвращает наибольшее значение. 5 105 МИН Возвращает наименьшее значение. 6 106 ПРОИЗВЕД Вычисляет произведение ячеек. 7 107 СТАНДОТКЛОН Возвращает стандартное отклонение генеральной совокупности на основе выборки чисел. 8 108 СТАНДОТКЛОН Возвращает стандартное отклонение на основе всей совокупности чисел. 9 109 СУММ Складывает числа. 10 110 VAR Оценивает дисперсию генеральной совокупности на основе выборки чисел. 11 111 VARP Оценивает дисперсию совокупности на основе всей совокупности чисел.
На самом деле нет необходимости запоминать все номера функций. Как только вы начнете вводить формулу промежуточного итога в ячейку или в строку формул, Microsoft Excel отобразит для вас список доступных номеров функций.
Например, вот как вы можете создать формулу «Промежуточный итог 9», чтобы суммировать значения в ячейках с C2 по C8:
Чтобы добавить номер функции в формулу, дважды щелкните ее, затем введите запятую, укажите диапазон, введите закрывающую скобку и нажмите Enter. Готовая формула будет выглядеть так:
=ПРОМЕЖУТОЧНЫЙ ИТОГ(9;C2:C8)
Аналогичным образом вы можете написать формулу «Промежуточный итог 1» для получения среднего значения, «Промежуточный итог 2» для подсчета ячеек с числами, «Промежуточный итог 3» для подсчета непустых ячеек и так далее. На следующем снимке экрана показано несколько других формул в действии:
Примечание. Когда вы используете формулу промежуточного итога с функцией суммирования, такой как СУММ или СРЗНАЧ, она вычисляет только ячейки с числами, игнорируя пробелы, и ячейки, содержащие нечисловые значения.
Теперь, когда вы знаете, как создать формулу промежуточного итога в Excel, главный вопрос: зачем кому-то утруждать себя ее изучением? Почему бы просто не использовать обычные функции, такие как SUM, COUNT, MAX и т. д.? Ответ вы найдете прямо ниже.
3 основные причины использовать ПРОМЕЖУТОЧНЫЕ ИТОГОВ в Excel
По сравнению с традиционными функциями Excel функция ПРОМЕЖУТОЧНЫЕ ИТОГО дает следующие важные преимущества.
1. Вычислить значения в отфильтрованных строках
Поскольку функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ в Excel игнорирует значения в отфильтрованных строках, ее можно использовать для создания динамической сводки данных, в которой значения промежуточных итогов автоматически пересчитываются в соответствии с фильтром.
Например, если мы отфильтруем таблицу, чтобы показать продажи только для восточного региона, формула промежуточного итога будет автоматически скорректирована так, что все остальные регионы будут удалены из итога:
Примечание. Поскольку оба набора номеров функций (1-11 и 101-111) игнорируют отфильтрованные ячейки, в этом случае вы можете использовать формулу ether Subtotal 9 или Subtotal 109.
2. Рассчитывать только видимые ячейки
Как вы помните, формулы промежуточных итогов с номером функции от 101 до 111 игнорируют все скрытые ячейки — отфильтровываются и скрываются вручную. Итак, когда вы используете функцию «Скрыть» Excel для удаления ненужных данных из представления, используйте номер функции 101-111, чтобы исключить значения в скрытых строках из промежуточных итогов.
Следующий пример поможет вам лучше понять, как это работает: Итого 9 против всего 109.
3. Игнорировать значения во вложенных формулах промежуточных итогов
Если диапазон, предоставленный вашей формуле промежуточных итогов Excel, содержит какие-либо другие формулы промежуточных итогов, эти вложенные промежуточные итоги будут игнорироваться, поэтому одни и те же числа не будут рассчитываться дважды. Потрясающе, не так ли?
На приведенном ниже снимке экрана формула общего среднего ПРОМЕЖУТОЧНЫЙ ИТОГ (1, C2: C10) игнорирует результаты формул промежуточного итога в ячейках C3 и C10, как если бы вы использовали формулу среднего с двумя отдельными диапазонами СРЗНАЧ (C2: C5, C7: C9). ).
Использование промежуточного итога в Excel — примеры формул
Когда вы впервые сталкиваетесь с ПРОМЕЖУТОЧНЫМИ ИТОГАМИ, это может показаться сложным, запутанным и даже бессмысленным. Но как только вы приступите к делу, вы поймете, что освоить его не так уж и сложно. Следующие примеры покажут вам несколько полезных советов и вдохновляющих идей.
Пример 1. Промежуточный итог 9 против промежуточного итога 109
Как вы уже знаете, Excel SUBTOTAL принимает 2 набора номеров функций: 1-11 и 101-111. Оба набора игнорируют отфильтрованные строки, но номера 1-11 включают скрытые вручную строки, тогда как 101-111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.
Для суммирования отфильтрованных строк вы можете использовать формулу «Промежуточный итог 9» или «Промежуточный итог 109», как показано на снимке экрана ниже:
Но если вы скрыли ненужные элементы вручную с помощью Скрыть строки команда на Дом вкладка > Клетки группа > Формат > Скрыть и показатьили щелкнув строки правой кнопкой мыши, а затем щелкнув Скрыватьи теперь вы хотите суммировать значения только в видимых строках, промежуточный итог 109 — единственный вариант:
Аналогично работают и другие номера функций. Например, для подсчета непустых отфильтрованных ячеек подойдет формула «Итог 3» или «Итог 103». Но только промежуточный итог 103 может правильно подсчитывать видимые непустые строки, если в диапазоне есть какие-либо скрытые строки:
Примечание. Функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ Excel с номером_функции 101–111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете такую формулу, как ПРОМЕЖУТОЧНЫЕ ИТОГ(109, A1:E1) для суммирования чисел в горизонтальном диапазоне, скрытие столбца не повлияет на промежуточный итог.
Пример 2. ЕСЛИ + ПРОМЕЖУТОЧНЫЙ ИТОГ для динамического суммирования данных
Если вы создаете сводный отчет или информационную панель, где вам нужно отобразить сводку различных данных, но у вас нет места для всего, решением может быть следующий подход:
- В одной ячейке создайте раскрывающийся список, содержащий имена функций, такие как Total, Max, Min и т. д.
- В ячейку рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточного итога, соответствующими именам функций в раскрывающемся списке.
Например, предположим, что промежуточные значения находятся в ячейках C2:C16, а раскрывающийся список в A17 содержит Общий, Средний, Максимума также Мин. элементов, формула «динамического» промежуточного итога выглядит следующим образом:
= ЕСЛИ (A17 = “итого”, ПРОМЕЖУТОЧНЫЙ ИТОГ (9, C2: C16), ЕСЛИ (A17 = “среднее”, ПРОМЕЖУТОЧНЫЙ ИТОГ (1, C2: C16), ЕСЛИ (A17 = “минимум”, ПРОМЕЖУТОЧНЫЙ ИТОГ (5, C2: C16) ), ЕСЛИ(A17=”max”, ПРОМЕЖУТОЧНЫЙ ИТОГ(4,C2:C16),””))))
И теперь, в зависимости от того, какую функцию из выпадающего списка выберет ваш пользователь, соответствующая функция Subtotal будет вычислять значения в отфильтрованных строках:
Кончик. Если вдруг выпадающий список и ячейка с формулой исчезнут с вашего листа, обязательно выберите их в списке фильтров.
Промежуточный итог Excel не работает — распространенные ошибки
Если ваша формула промежуточного итога возвращает ошибку, это может быть вызвано одной из следующих причин:
#ЦЕННОСТЬ! – аргумент номер_функции отличен от целого числа от 1 до 11 или от 101 до 111; или любой из аргументов ref содержит трехмерную ссылку.
#ДЕЛ/0! – происходит, если заданная функция суммирования должна выполнять деление на ноль (например, вычисление среднего значения или стандартного отклонения для диапазона ячеек, не содержащих ни одного числового значения).
#ИМЯ? – в названии функции Subtotal написано с ошибкой – проще ошибку исправить 🙂
Кончик. Если вы еще не чувствуете себя комфортно с функцией ПРОМЕЖУТОЧНЫЕИТОГИ, вы можете использовать встроенную функцию ПРОМЕЖУТОЧНЫЕИТОГИ и автоматически вставить формулы для вас.
Вот как можно использовать формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ в Excel для вычисления данных в видимых ячейках. Чтобы вам было легче следовать примерам, вы можете скачать нашу рабочую тетрадь с примерами ниже. Спасибо за чтение!
Практическая рабочая тетрадь
Примеры формулы ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel (файл .xlsx)
