Връща сбор в списък или база данни. Функцията AGGREGATE може да прилага различни агрегатни функции към списък или база данни с опция за игнориране на скритите редове и и грешните стойности.
Синтаксис
Форма за препратка
AGGREGATE (функция_ном; опции; препр1; [препр2];...)
Синтаксис за масив
AGGREGATE (функция_ном; опции; масив; [k])
Синтаксисът на функцията AGGREGATE има следните аргументи:
- Function_num Задължително. Число от 1 до 19, което указва коя функция да се използва.
| Функция_ном | Функция |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SUM |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
Опции Задължително. Числова стойност, която определя кои стойности да се пренебрегват в диапазона за изчисление за функцията.
Забележка
Функцията няма да игнорира скрити редове, вложени междинни суми или вложени обединения, ако аргументът за масив включва изчисление, например: =AGGREGATE(14;3;A1:A100*(A1:A100>0);1)
| Опция | Поведение |
|---|---|
| 0 или пропуснато | Игнорират се вложените функции SUBTOTAL и AGGREGATE |
| 1 | Игнорират се скритите редове, вложените функции SUBTOTAL и AGGREGATE |
| 2 | Игнорират се грешните стойности, вложените функции SUBTOTAL и AGGREGATE |
| 3 | Игнорират се скритите редове, грешните стойности, вложените функции SUBTOTAL и AGGREGATE |
| 4 | Нищо не се игнорира |
| 5 | Игнорират се скритите редове |
| 6 | Игнорират се грешните стойности |
| 7 | Игнорират се скритите редове и грешните стойности |
- Препратка1 Задължително. Първият числов аргумент за функции, които приемат множество числови аргументи за които искате агрегатната стойност.
-
Препр2,... Незадължително. От 2 до 253 числови аргумента, за които искате агрегатната стойност.
За функциите, които приемат масив, препр1 е масив, формула за масив или препратка към диапазон от клетки, за които търсите агрегатната стойност. Препр2 е втори аргумент, който се изисква за определени функции. Следните функции изискват аргумент препр2:
| Функция |
|---|
| LARGE(масив;к) |
| SMALL(масив;к) |
| PERCENTILE.INC(масив;к) |
| QUARTILE.INC(масив;кварт) |
| PERCENTILE.EXC(масив;к) |
| QUARTILE.EXC(масив;кварт) |
Забележки
Function_num:
- Докато въвеждате аргумента функция_ном при въвеждането на функцията AGGREGATE в клетка на работния лист, ще виждате списък с всички функции, които можете да използвате като аргументи.
Грешки:
- Ако се изисква втори препр аргумент, но не е предоставен, AGGREGATE връща #VALUE! грешка.
- Ако една или няколко от препратките са 3D препратки, функцията AGGREGATE връща #VALUE! стойността на грешка.
Тип на диапазона:
- Функцията AGGREGATE е предназначена за колони с данни или вертикални диапазони. Тя не е предназначена за редове с данни или хоризонтални диапазони. Когато например правите междинна сума за хоризонтален диапазон, като използвате опция 1, като например AGGREGATE(1; 1; препр1), скриването на колона не засяга стойността на агрегатната сума. Обаче скриването на ред във вертикален диапазон засяга агрегата.
Пример
Копирайте примерните данни в следващата таблица и ги поставете в клетка A1 на нов работен лист на Excel. За да покажат резултати формулите, изберете ги, натиснете клавиша F2 и след това натиснете клавиша Enter. Ако е необходимо, коригирайте ширините на колоните, за да видите всичките данни.
| #DIV/0! | 82 | |
|---|---|---|
| 72 | 65 | |
| 30 | 95 | |
| #NUM! | 63 | |
| 31 | 53 | |
| 96 | 71 | |
| 32 | 55 | |
| 81 | 83 | |
| 33 | 100 | |
| 53 | 91 | |
| 34 | 89 | |
| Формула | Описание | Резултат |
| =AGGREGATE(4; 6; A1:A11) | Изчислява максималната стойност, като пренебрегва стойностите на грешки в диапазона | 96 |
| =AGGREGATE(14; 6; A1:A11; 3) | Изчислява третата по големина стойност, като пренебрегва стойностите на грешки в диапазона | 72 |
| =AGGREGATE(15; 6; A1:A11) | Ще върне #VALUE! грешка. Това е така, защото AGGREGATE очаква втори "препр" аргумент, тъй като функцията (SMALL) изисква един. | #VALUE! |
| =AGGREGATE(12; 6; A1:A11; B1:B11) | Изчислява медианата, като пренебрегва стойностите на грешки в диапазона | 68 |
| =MAX(A1:A2) | Ще върне стойност на грешка, понеже в диапазона на изчислението има стойности на грешки. | #DIV/0! |