Функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ ИТОГИ) в Excel предназначена для выполнения определенного вычисления в диапазоне ячеек, игнорируя ячейки, которые не должны быть включены. SUBTOTAL может возвращать SUM, AVERAGE, COUNT, MAX и другие значения, а функция SUBTOTAL может включать или исключать значения в скрытых строках.
Что делает функция SUBTOTAL в Excel?
Функция SUBTOTAL в Excel позволяет получить промежуточный итог в списке или базе данных.
Возвращаемое значение
Число, представляющее определенный вид промежуточного итога.
Аргументы
- function_num — число, указывающее, какую функцию использовать для расчета промежуточных итогов в списке. Полный список см. в таблице ниже.
- ref1 — именованный диапазон или ссылка на промежуточный итог.
- ref2 — [необязательный] именованный диапазон или ссылка на промежуточный итог.
Синтаксис
=SUBTOTAL(function_num, ref1, [ref2], …)
Примечания по использованию
Функция SUBTOTAL в Excel предназначена для запуска заданного вычисления в диапазоне ячеек, игнорируя при этом ячейки, которые не должны быть включены. ПРОМЕЖУТОЧНЫЙ ИТОГ имеет три функции, которые делают его особенно полезным:
- Он автоматически игнорирует ячейки, которые были отфильтрованы вне поля зрения.
- Он автоматически игнорирует существующие формулы промежуточных итогов, чтобы избежать двойного учета.
- Он может выполнять множество вычислений, включая 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, ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключают строки, которые были скрыты вручную.
Функция | Включить скрытые | Игнорировать скрытые |
СРЕДНИЙ | 1 | 101 |
СЧИТАТЬ | 2 | 102 |
СЧЁТ | 3 | 103 |
МАКС | 4 | 104 |
МИН. | 5 | 105 |
ПРОДУКТ | 6 | 106 |
СТАНДОТКЛОН | 7 | 107 |
STDEVP | 8 | 108 |
СУММА | 9 | 109 |
ВАР | 10 | 110 |
ВАРП | 11 | 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 игнорируют другие формулы, существующие в ссылках, чтобы предотвратить двойной подсчет.
- Функция работает с вертикальными данными. В горизонтальные диапазоны всегда включаются значения в скрытых столбцах.