Функция АГРЕГАТ
Эта функция возвращает агрегацию вычислений в диапазоне. Можно использовать различные функции агрегации, перечисленные ниже. Функция «Агрегат» при вычислении позволяет пропускать скрытые строки, ошибки, результаты вычисления функции ИТОГ, а также результаты вычисления других функций АГРЕГАТ.
Функция АГРЕГАТ применяется к вертикальным диапазонам данных с активированным автофильтром. Если автофильтр не активирован, то автоматический пересчёт результата функции не будет работать для только что скрытых строк. Функция не предполагает работу с горизонтальными диапазонами, однако может к ним применяться, хоть и с ограничениями. В частности, функция АГРЕГАТ, применяемая к горизонтальным диапазонам данных, не распознаёт скрытые столбцы, хотя правильно пропускает ошибки и результаты работы функции ИТОГ и других функций АГРЕГАТ, находящихся в строке.
АГРЕГАТ(Функция; Параметр; Число 1[; Число 2][; ... ;[Число 253]])
или
АГРЕГАТ(Функция; Параметр; Массив[; k])
Функция — обязательный аргумент. Индекс функции или ссылка на ячейку со значением от 1 до 19, в соответствии со следующей таблицей.
|
Индекс функции |
Применяемая функция |
|---|---|
|
1 |
СРЗНАЧ |
|
2 |
СЧЁТ |
|
3 |
СЧЁТА |
|
4 |
МАКС |
|
5 |
МИН |
|
6 |
ПРОИЗВ |
|
7 |
СТОТКЛ.В |
|
8 |
СТОТКЛ.Г |
|
9 |
СУММ |
|
10 |
ДИСП.В |
|
11 |
ДИСП.Г |
|
12 |
МЕДИАНА |
|
13 |
МОДА.ОДН |
|
14 |
НАИБОЛЬШИЙ |
|
15 |
SMALL |
|
16 |
ПРОЦЕНТИЛЬ.ВКЛ |
|
17 |
КВАРТИЛЬ.ВКЛ |
|
18 |
ПРОЦЕНТИЛЬ.ИСКЛ |
|
19 |
КВАРТИЛЬ.ИСКЛ |
Параметр: обязательный аргумент. Индекс параметра или ссылка на ячейку со значением от 0 до 7 определяет что следует игнорировать в диапазоне для функции.
|
Индекс параметра |
Применяемый параметр |
|---|---|
|
0 |
Игнорировать только вложенные функции ИТОГ и АГРЕГАТ |
|
1 |
Игнорировать только скрытые строки, вложенные функции ИТОГ и АГРЕГАТ |
|
2 |
Игнорировать только ошибки, вложенные функции ИТОГ и АГРЕГАТ |
|
3 |
Игнорировать скрытые строки, ошибки, вложенные функции ИТОГ и АГРЕГАТ |
|
4 |
Ничего не игнорировать |
|
5 |
Игнорировать только скрытые строки |
|
6 |
Игнорировать только ошибки |
|
7 |
Игнорировать только скрытые строки и ошибки |
Число 1 — обязательный аргумент. Первый числовой аргумент (если диапазон задан списком значений внутри функции) или ссылка на ячейку, содержащую его.
Число 2, 3, ... — необязательный аргумент. Числовой аргумент или ссылка на ячейку (до 253 аргументов), для которой требуется агрегированное значение.
Массив — обязательный аргумент. Массив может быть указан с помощью границ диапазона, имени именованного диапазона или заголовка столбца.
При использовании заголовков столбцов необходимо включить функцию «Автоматически искать метки столбцов и строк».
k — обязательный аргумент для следующих функций: НАИБОЛЬШИЙ, НАИМЕНЬШИЙ, ПРОЦЕНТИЛЬ.ВКЛ, КВАРТИЛЬ.ВКЛ, ПРОЦЕНТИЛЬ.ИСКЛ, КВАРТИЛЬ.ИСКЛ. Это числовой аргумент, который должен соответствовать второму аргументу этих функций.
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
Столбец первый |
Столбец второй |
Столбец третий |
|
2 |
34 |
11 |
12 |
|
3 |
10 |
56 |
35 |
|
4 |
#ДЕЛ/0! |
5 |
3 |
|
5 |
20 |
8 |
1 |
|
6 |
0 |
8 |
9 |
|
7 |
#ЗНАЧ! |
20 |
21 |
|
8 |
5 |
7 |
8 |
|
9 |
14 |
0 |
5 |
=АГРЕГАТ(4;2;A2:A9)
Возвращает максимальное значение для диапазона A2:A9 = 34.
=АГРЕГАТ(9;5;A5:C5)
Возвращает сумму для диапазона A5:C5 = 29, даже если некоторые из столбцов скрыты.
=АГРЕГАТ(9;5;B2:B9)
Возвращает сумму столбца B = 115. Если какая-либо из строк скрыта, функция пропускает это значение, например, если ряд 7 скрыт, то функция возвращает 95.
Если необходимо применить функцию с 3D-диапазоном, то в этом примере показано как это сделать.
=АГРЕГАТ(13;3;Лист1.B2:B9:Лист3.B2:B9)
Функция возвращает моду значений второго столбца в листах 1:3 (содержащих те же данные) = 8.
Можно использовать ссылку на ячейку или диапазон для каждого аргумента в формуле. Следующий пример показывает как это работает. Кроме того, здесь показано, что можно использовать заголовки столбцов для указания массива.
=АГРЕГАТ(E3;E5;'Столбец первый')
If E3 = 13 and E5 = 5, функция возвращает моду первого столбца = 10.
COM.MICROSOFT.AGGREGATE