В течение многих лет я использовал функцию SUBTOTAL в Microsoft Excel для создания легко видимых промежуточных итогов в верхней части моих рабочих листов. Однако, когда я столкнулся с AGGREGATE, это стало моей функцией-подсказкой для достижения того же результата, но с большей гибкостью.
Прежде чем продолжить объяснение, давайте вспомним, как работает функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Функция SUBTOTAL
Функция SUBTOTAL в Microsoft Excel довольно проста в использовании. Она позволяет выполнять вычисления промежуточных итогов для диапазонов ячеек, и вы можете решить, хотите ли вы включать вручную скрытые строки в результат.
Однако отфильтрованные строки всегда исключаются, а функция SUBTOTAL не работает, если в данных есть ошибки, если только вы не вложите ее в функцию IFERROR. Более того, SUBTOTAL поддерживает только 11 функций, что ограничивает ее использование, если вы хотите выполнить более сложные статистические вычисления.
Вот синтаксис:
=ПРОМЕЖУТОЧНЫЙ ИТОГ(a,b,c)
в котором
- a (обязательно) — число, представляющее функцию, которую вы хотите использовать в расчетах,
- b (обязательно) — первый диапазон ячеек для подсчета промежуточной суммы, и
- c (необязательно) представляет собой первый из 252 диапазонов для подсчета промежуточной суммы, каждый из которых разделен запятой.
Для аргументации a, 1 или 101 = СРЗНАЧ, 2 или 102 = СЧЁТ, 3 или 103 = СЧЁТЗ, 4 или 104 = МАКС, 5 или 105 = МИН, 6 или 106 = ПРОИЗВЕДЕНИЕ, 7 или 107 = СТАНДОТКЛОН, 8 или 108 = СТАНДОТКЛОН.П, 9 или 109 = СУММА, 10 или 110 = ДИСП и 11 или 111 = ДИСП.
Числа от 1 до 11 для аргумента a принудительно включают в результат скрытые вручную строки, а значения 101–111 исключают скрытые вручную строки.
В этом примере функция SUBTOTAL была использована для вычисления среднего значения в столбце B, при этом строка 6 (команда E) была скрыта. Промежуточный итог в ячейке E1 включает скрытую строку (аргумент a = 1), тогда как промежуточный итог в ячейке E2 — нет (аргумент a = 101).
Однако когда фильтр используется для скрытия счета команды E, оба промежуточных итога одинаковы, поскольку нет возможности включить в промежуточный итог отфильтрованные значения.
В этом последнем примере вы можете видеть, что функция SUBTOTAL возвращает ошибку, если в диапазоне есть ошибка.
Чтобы избежать этих проблем, вы можете использовать функцию АГРЕГАТ.
Функция АГРЕГАТ
Функция AGGREGATE похожа на функцию SUBTOTAL, но имеет больше опций для типа агрегации, которую вы хотите создать. Кроме того, у вас есть более широкий выбор вещей, которые вы можете исключить из результата, и вы можете заставить Excel игнорировать ошибки в ваших данных.
Другими словами, по сравнению с функцией SUBTOTAL, функция AGGREGATE является более мощной и гибкой.
Функция АГРЕГАТ имеет два синтаксиса: один для ссылок и один для массивов, хотя вам не нужно беспокоиться о том, какой из них вы используете, поскольку Excel выбирает соответствующий в зависимости от введенных вами аргументов.
Синтаксис ссылочной формы функции АГРЕГАТ следующий:
=АГРЕГАТ(a,b,c,d)
в котором
- a (обязательно) — число, представляющее функцию, которую вы хотите использовать в расчетах,
- b (обязательно) — число, определяющее, что именно должно игнорироваться при расчете,
- c (обязательно) — диапазон ячеек, к которым будет применена функция, и
- d (необязательно) — первый из 252 дополнительных аргументов, которые определяют дополнительные диапазоны.
Если вы работаете с массивами, функция AGGREGATE имеет немного другой синтаксис:
=АГРЕГАТ(a,b,c,d)
в котором
- a (обязательно) — число, представляющее функцию, которую вы хотите использовать в расчетах,
- b (обязательно) — число, определяющее, что именно должно игнорироваться при расчете,
- c (обязательно) — массив значений, к которым будет применена функция, и
- d — второй аргумент, необходимый для функций массива, таких как LARGE, SMALL, PERCENTILE.INC и других.
Первое отличие между SUBTOTAL и AGGREGATE, которое следует отметить, заключается в том, что последний имеет больше вариантов аргументов. a: 1 = СРЗНАЧ, 2 = СЧЁТ, 3 = СЧЁТЗ, 4 = МАКС, 5 = МИН, 6 = ПРОИЗВЕДЕНИЕ, 7 = СТАНДОТКЛОН.У, 8 = СТАНДОТКЛОН.Г, 9 = СУММА, 10 = ДИСП.У, 11 = ДИСП.Г, 12 = МЕДИАНА, 13 = МОДА.ОДНГ, 14 = НАИБОЛЬШИЙ, 15 = НАИМЕНЬШИЙ, 16 = ПРОЦЕНТИЛЬ.ВКЛ, 17 = КВАРТИЛЬ.ВКЛ, 18 = ПРОЦЕНТИЛЬ.ИСКЛ и 19 = КВАРТИЛЬ.ИСКЛ.
Однако, где AGGREGATE действительно превосходит SUBTOTAL, так это в его способности исключать определенные значения из результата (аргумент b):
|
Номер регистрации |
Что игнорируется |
|---|---|
|
0 |
Вложенные функции SUBTOTAL и AGGREGATE |
|
1 |
Скрытые строки и вложенные функции SUBTOTAL и AGGREGATE |
|
2 |
Ошибки и вложенные функции SUBTOTAL и AGGREGATE |
|
3 |
Скрытые строки, значения ошибок и вложенные функции SUBTOTAL и AGGREGATE |
|
4 |
Ничего пока |
|
5 |
Только скрытые строки |
|
6 |
Только ошибки |
|
7 |
Скрытые строки и ошибки |
Итак, давайте посмотрим на это в действии.
В этом примере введите:
=АГРЕГАТ(1,7,Очки_Команды[Очки])
в ячейку E2 возвращается среднее значение (аргумент a = 1) видимых значений в столбце B, игнорируя скрытое значение в строке 8 и ошибку в строке 7 (аргумент b = 7).
В этом сценарии вы не сможете получить тот же результат, используя только функцию SUBTOTAL, поскольку она не может игнорировать ошибки, если не вложена в формулу ЕСЛИОШИБКА.
Хотя обход ошибок — это хороший способ сделать вашу таблицу более аккуратной, не привыкайте игнорировать их полностью! Они существуют не просто так и могут помочь в устранении неполадок.
С другой стороны, если набрать:
=АГРЕГАТ(1,6,Очки_Команды[Очки])
в ячейку E3 возвращается среднее значение (аргумент a = 1) всех значений в столбце B, включая значение в строке 8, которое отфильтровано, и исключая ошибку (аргумент b = 6).
Опять же, это было бы невозможно с помощью функции SUBTOTAL, поскольку она всегда включает отфильтрованные строки в результат, и нет способа обойти ошибку в данных.
На этот раз в ячейке E3 функция АГРЕГАТ используется для генерации медианы (аргумент a = 12), одно из многих вычислений, которые функция SUBTOTAL не позволяет выполнить:
=АГРЕГАТ(12,6,Очки_Команды[Очки])
В последнем примере аргумент d был использован для генерации второго по величине значения:
=АГРЕГАТ(14,6,Очки_Команды[Очки],2)
Резюме: ПРОМЕЖУТОЧНЫЙ ИТОГ против АГРЕГАТА в Excel
Ниже приведен краткий обзор функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ в Microsoft Excel:
|
Особенность |
SUBTOTAL |
ОБЩИЙ |
|---|---|---|
|
Подходит для простых агрегаций |
Y |
Y |
|
Подходит для сложных статистических агрегаций |
N |
Y |
|
Количество поддерживаемых функций |
11 |
19 |
|
Можно включать или исключать вручную скрытые строки. |
Y |
Y |
|
Можно включать или исключать отфильтрованные строки |
N |
Y |
|
Может игнорировать ошибки |
N |
Y |
|
Можно игнорировать вложенные результаты SUBTOTAL или AGGREGATE |
Y |
Y |
|
Предлагает дополнительные возможности для функций массива |
N |
Y |
Даже если вы думаете, что остановились на функции, которая вам подходит, всегда дважды проверяйте, нет ли другой функции, которая выполняет эту работу еще лучше! Microsoft часто представляет новые функции, которые являются обновлениями старых — например, теперь я использую XLOOKUP вместо VLOOKUP, а XMATCH гораздо удобнее для пользователя, чем MATCH.
