Резюме
Функция Excel AGGREGATE возвращает агрегированные вычисления, такие как AVERAGE, COUNT, MAX и т. Д., При необходимости игнорируя скрытые строки и ошибки. Всего доступно 19 операций, определяемых номером функции в первом аргументе (параметры см. В таблице).
Цель
Возврат совокупного расчетаВозвращаемое значение
Зависит от указанной функцииСинтаксис
= АГРЕГАТ (номер_функции; параметры; ссылка1; ссылка2)Аргументы
- function_num - Операция, которую нужно выполнить (1-19).
- options - Значения, которые следует игнорировать (0-7).
- ref1 - Первый аргумент.
- ref2 - Второй аргумент (k).
Версия
Excel 2010Примечания по использованию
Функция AGGREGATE возвращает результат совокупного вычисления, такого как AVERAGE, COUNT, MAX, MIN и т. Д. Всего доступно 19 операций, и операция, которую нужно выполнить, указывается в виде числа, которое появляется в качестве первого аргумента функции. Второй аргумент, параметры, определяет, как AGGREGATE обрабатывает ошибки и значения в скрытых строках. См. Таблицы ниже для всех доступных опций.
Пример # 1
Чтобы вернуть значение MAX в диапазоне 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, что указывает на МАКС. Опции представлены как 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, что запускает функцию НАИБОЛЬШИЙ. Поскольку для функции НАИБОЛЬШИЙ требуется аргумент k, он появляется как последний аргумент в трех приведенных выше формулах.
Пример # 4 - работа с массивом
Что делает AGGREGATE особенно полезным для более сложных формул, так это то, что он может обрабатывать массивы изначально, когда номер функции 14-19. Например, чтобы найти значение MAX по понедельникам с данными, которые включают даты и значения, вы можете использовать AGGREGATE следующим образом:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)
Здесь мы указываем 14 для функции (БОЛЬШОЙ) и 6 для опции (игнорировать ошибки). Затем мы строим логическое выражение с помощью функции ТЕКСТ, чтобы проверить все даты понедельника. Результатом этой операции является массив значений ИСТИНА / ЛОЖЬ, которые становятся знаменателем исходных значений. FALSE оценивается как ноль и выдает # DIV / 0! ошибка. ИСТИНА оценивается как 1 и возвращает исходное значение. Последний массив значений и ошибок действует как фильтр. AGGREGATE игнорирует все ошибки и возвращает наибольшее (максимальное) из оставшихся значений. Более полный пример здесь.
Номера функций
В таблице ниже перечислены номера функций, доступных для функции AGGREGATE, а также имя связанной функции. В третьем столбце Ref2 указано значение второго аргумента, ожидаемое при необходимости.
| Функция | Функция | Ref2 |
|---|---|---|
| 1 | СРЕДНИЙ | |
| 2 | COUNT | |
| 3 | COUNTA | |
| 4 | МАКСИМУМ | |
| 5 | MIN | |
| 6 | ТОВАР | |
| 7 | СТАНДОТКЛОН.S | |
| 8 | СТАНДОТКЛОН.P | |
| 9 | СУММ | |
| 10 | VAR.S | |
| 11 | VAR.P | |
| 12 | МЕДИАНА | |
| 13 | MODE.SNGL | |
| 14 | БОЛЬШОЙ | k |
| 15 | МАЛЕНЬКИЙ | k |
| 16 | PERCENTILE.INC | k |
| 17 | QUARTILE.INC | кварта |
| 18 | PERCENTILE.EXC | k |
| 19 | QUARTILE.EXC | кварта |
Варианты поведения
Функция АГРЕГАТ имеет множество опций для игнорирования ошибок, скрытых строк и других функций. Возможные значения 0-7, как показано в таблице ниже.
| Вариант | Поведение |
|---|---|
| 0 | Игнорировать функции ПРОМЕЖУТОЧНЫЙ ИТОГ и АГРЕГАТ |
| 1 | Игнорировать скрытые строки, функции ПРОМЕЖУТОЧНЫЙ ИТОГ и АГРЕГАТ |
| 2 | Игнорировать значения ошибок, функции SUBTOTAL и AGGREGATE |
| 3 | Игнорировать скрытые строки, значения ошибок, функции SUBTOTAL и AGGREGATE |
| 4 | Ничего не игнорировать |
| 5 | Игнорировать скрытые строки |
| 6 | Игнорировать значения ошибок |
| 7 | Игнорировать скрытые строки и значения ошибок |
Заметки
- AGGREGATE возвращает #VALUE! ошибка, если второй аргумент функции требуется, но не указан.
- 3D-ссылки не работают с AGGREGATE.
- Функция AGGREGATE предназначена для вертикальных, а не горизонтальных диапазонов.
