АГРЕГАТ функция в Excel
АГРЕГАТ Функция в Excel връща съвкупността от дадена таблица с данни или списъци с данни, тази функция също има първия аргумент като номер на функция и допълнителни аргументи са за диапазон от набори от данни, номерът на функцията трябва да бъде запомнен, за да знае коя функция да използва .
Синтаксис
Има два синтаксиса за формулата AGREGATE:
- Референтен синтаксис
= АГРЕГАТ (номер_на_функция, опции, ref1, ref2, ref [3], ...)
- Синтаксис на масива
= АГРЕГАТ (номер_на_функция, опции, масив, [k])
Function_num е число, което обозначава конкретна функция, която искаме да използваме, това е число от 1-19
Опция: това е също числова стойност, варираща от 0 до 7 и определя кои стойности трябва да се игнорират при изчисленията
Ref1, ref2, ref [3]: е аргументът, докато се използва референтният синтаксис, това е числова стойност или стойности, върху които искаме да извършим изчислението, поне два аргумента са необходими, а останалите аргументи не са задължителни.
Масив: е масив от стойности, върху които искаме да извършим операцията, той се използва в синтаксиса на масива на функцията AGGREGATE в Excel
K: е незадължителен аргумент и е числова стойност, използва се, когато се използва функцията като LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC в Excel.
Примери
Можете да изтеглите този АГРЕГАТ Шаблон за функция на Excel тук - АГРЕГАТ Шаблон за функция на ExcelПример - # 1
Да предположим, че имаме списък с числа и ще изчислим средното, броя, което е броят на клетките, които съдържат стойност, брояча на броя на клетките, които не са празни, максимум, минимум, продукт и сума от дадените числови стойности. Стойностите са дадени по-долу в таблицата:
Нека първо изчислим средната стойност в ред 9 за всички дадени стойности. Средно функцията_ число е
В колона C са дадени всички стойности и няма да се налага да игнорираме никакви стойности, така че ще изберем вариант 4 (не игнорираме нищо)
И избор на диапазон от стойности C1: C8 като масив от числови стойности
Тъй като ' k' е незадължителен аргумент и се използва, когато се използва функция като LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC, но в този случай ние изчисляваме средното, така че ние ще пропусне стойността на k.
И така, средната стойност е
По същия начин, за диапазон D1: D8, отново ще изберем опция 4.
За диапазон E1: E8, клетка E6 съдържа стойност на грешка, ако ще използваме същата формула AGGREGATE, ще получим грешка, но когато се използва подходяща опция, AGGREGATE в Excel дава средната стойност на останалите стойности, пренебрегвайки грешката стойност в E6.
За да игнорираме стойностите на грешките, имаме опция 6.
По същия начин, за диапазон G1: G8 ще използваме опцията 6 (игнорирайте стойностите на грешките)
Сега, за диапазон H3, ако поставим стойност 64 и скрием третия ред и използваме опция 5, за да игнорираме скрития ред, АГРЕГАТЪТ в Excel ще дадем средната стойност само за видими цифрови стойности.
Изход без скриване на ред 3
Изход след скриване на ред 3
Прилагайки формулата AGGREGATE за други операции, имаме
Пример - # 2
Да предположим, че имаме таблица за приходите, генерирани на различни дати от различните канали, както е дадено по-долу
Сега искаме да проверим генерираните приходи за различни канали. Така че, когато прилагаме функцията сума, получаваме общия генериран приход, но в случай, че искаме да проверим генерираните приходи за органичен канал или директен канал или който и да е друг, когато прилагаме филтри в Excel за същото, функцията за суми винаги ще работи дайте общата сума
Искаме, когато филтрираме канала, да получим сумата от стойностите, които са видими, така че вместо да използваме функцията SUM, ще използваме функцията AGGREGATE, за да получим сумата от стойностите, които са видими, когато филтърът е приложени.
И така, замествайки формулата SUM с функция AGGREGATE с код на опция 5 (игнорирайки скритите редове и стойности), имаме,
Сега, когато ще приложим филтъра за различни канали, той ще покаже приходите за този канал само когато останалите редове се скрият.
Общ приход, генериран за директен канал:
Общо генерирани приходи за органичен канал:
Общо генерирани приходи за платен канал:
И така, можем да видим, че функцията AGGREGATE изчислява различните стойности на сумата за генерираните приходи за различни канали, след като те бъдат филтрирани. Така че, функцията AGGREGATE може да се използва динамично за замяна на различни функции за различни условия, без да се използва условната формула.
Да предположим, че за същия канал на таблица и приходи, някои от нашите стойности на приходи съдържат грешка, сега трябва да игнорираме грешките и в същото време, ако искаме да приложим филтър, функцията AGGREGATE трябва да игнорира и скритите стойности на редове.
Когато използваме опция 5, получаваме грешката за сумата на общите приходи, сега, за да игнорираме грешките, трябва да използваме опция 6
Използвайки опция 6, получаваме сумата, пренебрегвайки стойностите на грешките, но когато приложим филтъра, например, филтриране по стойност на канала Direct, получаваме същата сума, игнорирайки грешките, но в същото време трябва да игнорираме и скритите стойности.
Така че, в този случай ще използваме опцията 7, която игнорира стойностите на грешките и в същото време скритите редове
Неща за запомняне
- Функцията AGGREGATE не разпознава функцията _ num стойност по-голяма от 19 или по-малка от 1 и по подобен начин за Option number тя не разпознава стойностите по-големи от 7 и по-малки от 1, ако предоставим други стойности, която дава #VALUE ! Грешка
- Той винаги приема числовата стойност и винаги връща числова стойност като изход
- АГРЕГАТЪТ в Excel има ограничение; той само игнорира скритите редове, но не игнорира скритите колони.