Функция AGGREGATE в Excel

Функция AGGREGATE (АГРЕГАТ) в Excel возвращает агрегированное вычисление, такое как AVERAGE, COUNT, MAX и т. д., при необходимости игнорируя скрытые строки и ошибки. Всего доступно 19 операций, определяемых номером функции в первом аргументе.

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

Функция AGGREGATE (АГРЕГАТ) в Excel вычисляет совокупный доход.

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

Зависит от указанной функции.

Аргументы

• function_num — Операция для выполнения (1-19).
• options — значения для игнорирования (0-7).
• ref1 — Первый аргумент.
• ref2 — [необязательный] Второй аргумент (k).

Синтаксис

=AGGREGATE(function_num, options, ref1, [ref2], …)

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

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

1. Имеет ряд параметров для игнорирования ошибок, скрытых строк и других функций, которые могут появляться в данных.
2. Может обрабатывать многие операции с массивами изначально, без Control + Shift + Enter .

AGGREGATE может запускать в общей сложности 19 функций, и функция, которую необходимо выполнить, задается в виде числа, которое появляется в качестве первого аргумента функции, function_num. Второй аргумент, options , управляет тем, как AGGREGATE обрабатывает ошибки и значения в скрытых строках. В  таблице ниже указаны все доступные варианты.

Функция AGGREGATE принимает четыре аргумента: function_num, options, ref1 и ref2. Для первых 13 поддерживаемых функций требуются только первые три аргумента: function_num указывает операцию, options задают различные варианты поведения, а ref1  — это массив значений для обработки.

Последние 6 функций требуют всех четырех аргументов: function_num определяет операцию, параметры  задают различные варианты поведения,  ref1  — это массив значений для обработки, а ref2 — «второй аргумент» для вызываемой функции. Например, такие функции, как SMALL и LARGE, принимают второй аргумент k, а ref2 представляет этот аргумент. Следующие шесть функций требуют аргумента ref2 :

LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

Пример №1

Чтобы вернуть максимальное значение в диапазоне A1:A10, игнорируя как ошибки, так и скрытые строки, укажите 4 для номера функции и 7 для параметров:

=AGGREGATE(4,7,A1:A10) // max value

Чтобы вернуть значение MIN с теми же параметрами, измените номер функции на 5:

=AGGREGATE(5,7,A1:A10) // min value

Пример #2

В показанном выше примере формула в D5 выглядит так:

=AGGREGATE(4,6,values)

где «значения» — это именованный диапазон B5:B14. Номер функции 4, что указывает MAX. Вариантов предусмотрено 6, чтобы игнорировать только ошибки.

Пример #3 — n-й по величине

Формулы в D8:D10 демонстрируют, как вернуть «n-е по величине» значение:

=AGGREGATE(14,6,values,1) // 1st largest
=AGGREGATE(14,6,values,2) // 2nd largest
=AGGREGATE(14,6,values,3) // 3rd largest

Здесь номер функции 14, которая запускает функцию LARGE. Поскольку для функции LARGE требуется аргумент k, он отображается как последний аргумент в трех приведенных выше формулах.

Пример #4 — работа с массивом

Что делает AGGREGATE особенно полезным для более сложных формул, так это то, что он может обрабатывать массивы изначально, когда номер функции равен 14-19. Например, чтобы найти МАКСИМАЛЬНОЕ значение по понедельникам с данными, которые включают даты и значения, вы можете использовать АГРЕГАТ следующим образом:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Здесь мы указываем 14 для функции (LARGE) и 6 для опции (игнорировать ошибки). Затем мы строим логическое выражение, используя TRUE/FALSE, которые становятся знаменателем исходных значений.

FALSE оценивается как ноль и выдает ошибку #DIV/0! ошибка. TRUE оценивается как 1 и возвращает исходное значение. Окончательный массив значений и ошибок действует как фильтр. АГРЕГАТ игнорирует все ошибки и возвращает наибольшее (максимальное) из сохранившихся значений.

Номера функций

В таблице ниже перечислены номера функций, доступных для функции AGGREGATE, а также имя связанной функции. Третий столбец Ref2 указывает «второй аргумент», ожидаемый последними 6 функциями.

Варианты поведения

Функция АГРЕГАТ имеет множество параметров для игнорирования ошибок, скрытых строк и других функций. Опции задаются аргументом options. Возможные значения 0-7, как показано в таблице ниже.

Примечание. Функция АГРЕГАТ похожа на обновленную версию старой функции SUBTOTAL и предоставляет больше возможностей для расчета и больший контроль над игнорированием определенных вещей. Однако одно тонкое различие между AGGREGATE и SUBTOTAL заключается в том, что поведение по умолчанию для скрытых строк обратное.

В то время как SUBTOTAL всегда будут игнорировать значения в строках, скрытых фильтром, и для игнорирования строк, скрытых вручную, требуется другой номер функции, АГРЕГАТ всегда будет игнорировать строки, скрытые вручную, и ему требуется конкретная опция для игнорирования строк, скрытых фильтром. Даже если для аргумента опции установлено значение 4 (ничего не игнорировать), AGGREGATE будет игнорировать значения в строках, скрытых вручную.

Примечания

• АГРЕГАТ в Excel возвращает #VALUE! ошибка, если требуется второй аргумент функции, но он не указан.
• 3D-ссылки не работают с AGGREGATE.
• Функция предназначена для вертикальных, а не горизонтальных диапазонов.