АГРЕГАТ функция в Excel
АГРЕГАТ Функция в Excel връща съвкупността от дадена таблица с данни или списъци с данни, тази функция също има първия аргумент като номер на функция и допълнителни аргументи са за диапазон от набори от данни, номерът на функцията трябва да се запомни, за да се знае коя функция да се използва .
Синтаксис
Има два синтаксиса за формулата AGREGATE:
- Референтен синтаксис
= AGREGATE (номер_на_функция, опции, ref1, ref2, ref (3), …)
- Синтаксис на масива
= АГРЕГАТ (номер_на_функция, опции, масив, (k))
Function_num е число, което обозначава конкретна функция, която искаме да използваме; това е число от 1-19
Опция: това също е числова стойност, варираща от 0 до 7 и определя кои стойности трябва да бъдат игнорирани по време на изчисленията
Ref1, ref2, ref (3): е аргументът при използване на референтния синтаксис; това е числова стойност или стойности, върху които искаме да извършим изчислението, са необходими поне два аргумента. Аргументите за почивка не са задължителни.
Array: е масив от стойности, върху които искаме да работим; той се използва в синтаксиса на масива на функцията AGGREGATE в Excel.
K: е незадължителен аргумент и числова стойност; той се използва, когато в Excel се използват функции като LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC.
Примери
Пример - # 1
Да предположим, че имаме списък с числа и ще изчислим средното, броя, което е броят на клетките, които съдържат стойност, брояч на броя на клетките, които не са празни, максимум, минимум, продукт и сума от дадените числови стойности . Стойностите са дадени по-долу в таблицата:
Нека първо изчислим средната стойност в ред 9 за всички дадени стойности. Средно функцията_ число е 1
В колона C са дадени всички стойности и няма да се налага да пренебрегваме никакви стойности, така че да изберем вариант 4 (да не се игнорира нищо)
И избор на диапазон от стойности C1: C8 като масив от числови стойности
Тъй като ' k' е незадължителен аргумент и се използва, когато се използва функция като LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC, но в този случай ние изчисляваме средното до пропускат стойността на k.
И така, средната стойност е
По същия начин, за диапазон D1: D8, отново ще изберем опция 4.
За диапазон E1: E8, клетка E6 съдържа стойност на грешка. Ако използваме същата формула AGREGATE, ще получим грешка. И все пак, когато се използва подходяща опция, АГРЕГАТЪТ в 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. По същия начин, за номер на опция, тя не идентифицира стойностите, по-големи от 7 и по-малки от 1; ако предоставим някакви други стойности, това дава #VALUE! Грешка
- Той винаги приема числовата стойност и винаги връща числова стойност като изход
- АГРЕГАТЪТ в Excel има ограничение; той само игнорира скритите редове, но не игнорира скритите колони.
