Как да използвам функцията Excel AGGREGATE -

Обобщение

Функцията Excel AGGREGATE връща обобщено изчисление като СРЕДНО, БРОЙ, МАКС и т.н., като по избор игнорира скритите редове и грешки. Налични са общо 19 операции, посочени от номера на функцията в първия аргумент (вижте таблицата за опции).

Предназначение

Връщане на обобщеното изчисление

Върната стойност

Зависи от посочената функция

Синтаксис

= АГРЕГАТ (номер_на_функция, опции, ref1, ref2)

Аргументи

  • function_num - Операция за изпълнение (1-19).
  • options - Стойности за игнориране (0-7).
  • ref1 - Първи аргумент.
  • ref2 - Втори аргумент (k).

Версия

Excel 2010

Бележки за употреба

Функцията AGGREGATE връща резултата от съвкупно изчисление като AVERAGE, COUNT, MAX, MIN и др. Налични са общо 19 операции и операцията, която трябва да се извърши, е посочена като число, което се появява като първия аргумент във функцията. Вторият аргумент, опции, контролира как AGGREGATE обработва грешки и стойности в скрити редове. Вижте таблиците по-долу за всички налични опции.

Пример # 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 изисква аргумент ak, тя се появява като последния аргумент в трите формули по-горе.

Пример # 4 - операция с масив

Това, което прави AGGREGATE особено полезно за по-сложни формули, е, че той може да обработва масиви в оригинал, когато номерът на функцията е 14-19. Например, за да намерите МАКСИМАЛНАТА стойност в понеделник, с данни, които включват дати и стойности, можете да използвате AGGREGATE по следния начин:

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

Тук посочваме 14 за функция (LARGE) и 6 за опция (игнориране на грешки). След това изграждаме логически израз, използвайки функцията TEXT, за да проверим всички дати за понеделник. Резултатът от тази операция е масив от TRUE / FALSE стойности, които стават знаменател на първоначалните стойности. FALSE се оценява като нула и хвърля # DIV / 0! грешка. TRUE се оценява като 1 и връща първоначалната стойност. Крайният масив от стойности и грешки действа като филтър. AGGREGATE игнорира всички грешки и връща най-голямата (максимална) от оцелелите стойности. По-пълен пример тук.

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

Таблицата по-долу изброява номерата на функциите, достъпни за функцията AGGREGATE, заедно с името на свързаната функция. Третата колона, Ref2, показва втората стойност на аргумента, която се очаква, когато се изисква.

Функция Функция Ref2
1 СРЕДНО АРИТМЕТИЧНО
2 БРОЯ
3 КОНТА
4 МАКС
5 МИН
6 ПРОДУКТ
7 STDEV.S
8 STDEV.P
9 СУММА
10 VAR.S
11. VAR.P
12 МЕДИАНА
13 MODE.SNGL
14. ГОЛЯМ к
15 МАЛКИ к
16. PERCENTILE.INC к
17 КВАРТИЛ.INC четвърт
18. PERCENTILE.EXC к
19. КВАРТИЛЕН.EXC четвърт

Опции за поведение

Функцията AGGREGATE има много опции за игнориране на грешки, скрити редове и други функции. Възможните стойности са 0-7, както е показано в таблицата по-долу.

Вариант Поведение
0 Игнорирайте функциите SUBTOTAL и AGGREGATE
1 Игнорирайте скритите редове, функциите SUBTOTAL и AGGREGATE
2 Игнорирайте стойностите на грешките, функциите SUBTOTAL и AGGREGATE
3 Игнорирайте скритите редове, стойностите на грешките, функциите SUBTOTAL и AGGREGATE
4 Не пренебрегвайте нищо
5 Игнорирайте скритите редове
6 Игнорирайте стойностите на грешките
7 Игнорирайте скритите редове и стойностите на грешките

Бележки

  • AGGREGATE връща #VALUE! грешка, ако се изисква втори аргумент на функция, но не е предоставен.
  • 3D препратките не работят с AGGREGATE.
  • Функцията AGGREGATE е проектирана вертикални, а не хоризонтални диапазони.

Интересни статии...