Функция SUBTOTAL: вычисляет общее значение отфильтрованного списка в Excel.

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

Хотя знание суммы столбца электронной таблицы полезно, вам может потребоваться узнать сумму различных групп. Функция промежуточного итога в Excel — это практичный способ расчета меньших сумм отдельно от окончательной суммы. Его относительно легко использовать в электронных таблицах, и он позволяет вам организовывать данные.

Функция промежуточного итога в Excel — это формула, которая вычисляет диапазон значений на основе указанной операции. Он выглядит как =SUBTOTAL(function_num, ref1, [ref2],...), где function_num относится к операции, которую должна выполнить формула. ref1, [ref2],... — это значения, которые Excel должен включить в расчет. Одним из наиболее распространенных применений функции промежуточного итога является суммирование столбца, но она также может выполнять следующие операции:

• Середина
• Считать
• Считать все
• Максимальное значение
• Минимальное значение
• Умножение
• Стандартное отклонение генеральной совокупности
• Стандартное отклонение выборки
• Дисперсия населения
• Дисперсия выборки

Учебное пособие выполнено в Excel 2019, однако вы по-прежнему можете применять этот расчет в других версиях Excel, таких как Excel 2007, Excel 2010, Excel 2013, Excel 2016, поскольку в учебном пособии используется функция Excel Subtotal.

Рассчитайте общую стоимость отфильтрованного списка с помощью функции «Промежуточный итог» в Excel

Предположим, у нас есть таблица данных, подобная следующей, с уже созданными фильтрами.

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

Требуется рассчитать общую стоимость книг. Если вы используете функцию SUM() в отфильтрованной таблице данных выше, то полученный вами результат будет не общей стоимостью книг, а общей стоимостью всех товаров.

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

=SUBTOTAL(9,H7:H11)

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

В результате общая стоимость книг составила 131 000.

О функции SUBTOTAL()

Функция SUBTOTAL() просматривает весь список значений в столбце D и вычисляет только те значения, которые удовлетворяют фильтру. Вы можете взглянуть на изображение выше и догадаться, что это потому, что мы объявили аргумент 9. Однако этот аргумент сообщает Excel, что мы хотим вычислить СУММУ указанных значений. В следующей таблице перечислены принятые аргументы:

Посмотрев на таблицу выше, вы, возможно, зададитесь вопросом, в чем разница между 9 и 109. Когда мы используем аргумент 9, функция SUBTOTAL() будет суммировать все скрытые значения. При использовании аргумента 109 функция SUBTOTAL() будет игнорировать скрытые значения. Нам необходимо четко различать скрытые значения и значения, которые исключены, поскольку они не удовлетворяют фильтру . Скрыть строку можно, щелкнув правой кнопкой мыши по порядку строк и выбрав «Скрыть» . Это полностью отличается от строк, которые не отображаются, поскольку не удовлетворяют фильтру.

Функция SUBTOTAL() также используется для выполнения многих других полезных задач. Вы можете обратиться к руководству по функции SUBTOTAL на сайте Quantrimang.com.

Смотрите также:

• Как использовать функцию Power в Excel
• Как использовать функцию Round в Excel
• Как использовать функцию ВПР в Excel