Функция SUBTOTAL в Excel

Функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ ИТОГИ) в Excel предназначена для выполнения определенного вычисления в диапазоне ячеек, игнорируя ячейки, которые не должны быть включены. SUBTOTAL может возвращать SUM, AVERAGE, COUNT, MAX и другие значения, а функция SUBTOTAL может включать или исключать значения в скрытых строках.

Что делает функция SUBTOTAL в Excel?

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

Возвращаемое значение

Число, представляющее определенный вид промежуточного итога.

Аргументы

• function_num — число, указывающее, какую функцию использовать для расчета промежуточных итогов в списке. Полный список см. в таблице ниже.
• ref1 — именованный диапазон или ссылка на промежуточный итог.
• ref2 — [необязательный] именованный диапазон или ссылка на промежуточный итог.

Синтаксис

=SUBTOTAL(function_num, ref1, [ref2], …)

Примечания по использованию

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

1. Он автоматически игнорирует ячейки, которые были отфильтрованы вне поля зрения.
2. Он автоматически игнорирует существующие формулы промежуточных итогов, чтобы избежать двойного учета.
3. Он может выполнять множество вычислений, включая SUM, AVERAGE, COUNT, MAX, MIN и другие .

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

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

Примеры

Ниже приведены примеры SUBTOTAL, настроенных для СУММИРОВАНИЯ, СЧЕТА и СРЕДНЕГО значений в диапазоне. Обратите внимание, что единственная разница — это значение, используемое для аргумента function_num :

=SUBTOTAL(109,range) // SUM
=SUBTOTAL(103,range) // COUNT
=SUBTOTAL(101,range) // AVERAGE

На листе, показанном выше, формулы в C4 и F4:

=SUBTOTAL(3,B7:B19) // count visible
=SUBTOTAL(9,F7:F19) // sum visible

Доступные расчеты

Вычисление, выполняемое функцией, определяется аргументом function_num, который задается в виде числа. Всего имеется 11 вычислений, каждое с двумя вариантами, как показано ниже. Обратите внимание, что эти значения «парные» (например, 1-101, 2-102, 3-103 и т. д.).

Это связано с тем, как SUBTOTAL обрабатывает строки, скрытые вручную. Когда номер_функции находится в диапазоне от 1  включают строки, которые были скрыты вручную. Когда номер_функции находится в диапазоне от 101 до 111, ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключают строки, которые были скрыты вручную.

Примечание. ПРОМЕЖУТОЧНЫЕ ИТОГИ всегда игнорирует значения в ячейках, скрытых фильтром. Значения в строках, которые были «отфильтрованы», никогда не включаются, независимо от  function_num .

ПРОМЕЖУТОЧНЫЕ ИТОГО в таблицах Excel

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

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

ПРОМЕЖУТОЧНЫЙ ИТОГ с контурами

В Excel есть функция «Промежуточный итог», которая автоматически вставляет формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ в отсортированные данные. Вы можете найти эту функцию в Data > Outline > Subtotal. Формулы, вставленные таким образом, используют стандартные номера функций 1-11. Это позволяет промежуточным результатам оставаться видимыми, даже если строки скрыты и отображаются, когда схема свернута и развернута.

Примечание. Хотя функция «Структура» — это «простой» способ вставки промежуточных итогов в набор данных, сводная таблица — это лучший и более гибкий способ анализа данных. Кроме того, сводная таблица отделяет данные от представления данных, что является передовой практикой.

Примечания

• Когда function_num находится в диапазоне от 1 до 11, включают вручную скрытые строки.
• Когда находится в диапазоне от 101 до 111, SUBTOTAL исключают строки, скрытые вручную.
• В отфильтрованных списках функция всегда игнорирует значения в скрытых строках, независимо от function_num .
• ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel игнорируют другие формулы, существующие в ссылках, чтобы предотвратить двойной подсчет.
• Функция работает с вертикальными данными. В горизонтальные диапазоны всегда включаются значения в скрытых столбцах.