Функция AGGREGATE
Тази функция връща агрегиран резултат от изчисление върху диапазона от клетки. Допустимите агрегатни функции са изброени по-долу. Функцията AGGREGATE ви позволява да пропускате скритите редове, грешките и резултатите от SUBTOTAL и други срещания на AGGREGATE.
Функцията AGGREGATE се прилага върху вертикални диапазони от клетки с активиран автофилтър. Ако автофилтърът не е включен, автоматичното преизчисляване резултата на функцията не работи за скрити впоследствие редове. Не се предполага да работи върху хоризонтални диапазони, но може да бъде прилагана и върху такива, макар и с ограничения. В частност, функцията AGGREGATE, приложена върху хоризонтален диапазон от клетки, не разпознава скриването на колони, макар че правилно пропуска грешките и резултатите от SUBTOTAL и други срещания на AGGREGATE в реда.
Синтаксис
AGGREGATE(Функция; Настройка; Обр1 [; Обр2 [; …]])
или
AGGREGATE(Функция; Настройка; Масив [; k])
Функция – задължителен аргумент. Индекс на функция или обръщение към клетка със стойност от 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 |
Настройка – задължителен аргумент. Индекс на настройка или обръщение към клетка със стойност от 0 до 7, определя какво да се игнорира в обработваните клетки.
Индекс на настройка |
Прилагана настройка |
0 |
Игнориране само на вложените функции SUBTOTAL и AGGREGATE |
1 |
Игнориране само на скритите редове и вложените функции SUBTOTAL и AGGREGATE |
2 |
Игнориране само на грешките, вложените функции SUBTOTAL и AGGREGATE |
3 |
Игнориране на скритите редове, грешките и вложените функции SUBTOTAL и AGGREGATE |
4 |
Нищо не се игнорира |
5 |
Игнориране само на скритите редове |
6 |
Игнориране само на грешките |
7 |
Игнориране само на скритите редове и грешките |
Обр1 – задължителен аргумент. Първият числов аргумент (ако диапазонът е зададен чрез списък от стойности в самата функция) или обръщение към клетка, която го съдържа.
Обр2, 3, ... – незадължителни. Числов аргумент или обръщение към клетка (до 253 аргумента), за която да се изчисли агрегатната стойност.
Масив – задължителен аргумент. Масивът може да се зададе чрез граници на диапазон, име на наименуван диапазон или заглавие на колона.
За да се използват заглавия на колони, трябва да е включена възможността „Автоматично намиране заглавия на редове и колони“.
k – задължителен аргумент за следните функции: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC. Това е числов аргумент, който трябва да съответства на втория аргумент на изброените функции.
Примери
A |
B |
C |
|
1 |
КолонаЕдно |
КолонаДве |
КолонаТри |
2 |
34 |
11 |
12 |
3 |
10 |
56 |
35 |
4 |
#DIV/0! |
5 |
3 |
5 |
20 |
8 |
1 |
6 |
0 |
8 |
9 |
7 |
#VALUE! |
20 |
21 |
8 |
5 |
7 |
8 |
9 |
14 |
0 |
5 |
=AGGREGATE(4;2;A2:A9)
Връща максималната стойност за диапазона A2:A9 = 34, а =MAX(A2:A9) връща Err:511.
=AGGREGATE(9;5;A5:C5)
Връща сумата за диапазона A5:C5 = 29, дори ако някои от колоните са скрити.
=AGGREGATE(9;5;B2:B9)
Връща сумата на колоната B = 115. Ако някой ред е скрит, функцията ще пропусне стойността му, например ако се скрие ред 7, резултатът на функцията ще бъде 95.
Ако искате да приложите функцията върху триизмерен диапазон, този пример ви показва как.
=AGGREGATE(13;3;Лист1.B2:B9:Лист3.B2:B9)
Функцията връща модата на стойностите от вторите колони на листове 1:3 (които съдържат еднакви данни) = 8.
За всеки от аргументите във формулата може да се използва обръщение към клетка или диапазон. Това е показано в следващия пример. Освен това се вижда как можете да зададете масив чрез заглавията на колони.
=AGGREGATE(E3;E5;'ColumnOne')
Ако E3 = 13 и E5 = 5, функцията изчислява модата на първата колона = 10.