Ako používať funkciu AGGREGATE v programe Excel? | (s príkladmi)

Funkcia AGGREGATE v programe Excel

AGGREGATE Funkcia v programe Excel vracia agregát danej údajovej tabuľky alebo zoznamov údajov, táto funkcia má tiež prvý argument ako číslo funkcie a ďalšie argumenty sú určené pre rozsah množín údajov, je potrebné pamätať na číslo funkcie, aby ste vedeli, ktorú funkciu použiť .

Syntax

Pre vzorec AGGREGATE existujú dve syntaxe:

  1. Referenčná syntax

= AGGREGATE (function_num, options, ref1, ref2, ref [3],…)

  1. Syntax poľa

= AGGREGATE (číslo_funkcie, možnosti, pole, [k])

Function_num je číslo, ktoré označuje konkrétnu funkciu, ktorú chceme použiť, je to číslo od 1 do 19

Možnosť: je to tiež číselná hodnota v rozmedzí od 0 do 7, ktorá určuje, ktoré hodnoty sa majú pri výpočtoch ignorovať

Ref1, ref2, ref [3]:  je argument pri použití referenčnej syntaxe, je to číselná hodnota alebo hodnoty, na ktorých chceme vykonať výpočet, minimálne dva argumenty sú povinné, ostatné argumenty sú voliteľné.

Array: je pole hodnôt, na ktorých chceme vykonať operáciu, používa sa v syntaxi poľa funkcie AGGREGATE v programe Excel

K: je voliteľný argument a je číselnou hodnotou, používa sa, keď sa v Exceli používa funkcia ako LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC alebo QUARTILE.EXC.

Príklady

Túto šablónu programu AGGREGATE Function Excel si môžete stiahnuť tu - šablónu programu AGGREGATE Function Excel

Príklad - # 1

Predpokladajme, že máme zoznam čísel a vypočítame Priemer, Počet, čo je počet buniek, ktoré obsahujú hodnotu, Počet-počet buniek, ktoré nie sú prázdne, Maximum, Minimum, súčin a súčet daných číselných hodnôt. Hodnoty sú uvedené nižšie v tabuľke:

Najprv vypočítajme priemer na riadku 9 pre všetky dané hodnoty. Priemerná hodnota je number_funkce

V stĺpci C sú uvedené všetky hodnoty a nebudeme musieť ignorovať žiadne hodnoty, preto vyberieme možnosť 4 (nič ignorovať)

A výber rozsahu hodnôt C1: C8 ako radu číselných hodnôt

Pretože ' k' je voliteľný argument a používa sa, keď sa použije funkcia ako LARGE, SMALL v Exceli, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC alebo QUARTILE.EXC, ale v tomto prípade vypočítavame priemer, takže vynechá hodnotu k.

Priemerná hodnota teda je

Podobne pre rozsah D1: D8 opäť vyberieme možnosť 4.

Pre rozsah E1: E8 obsahuje bunka E6 chybovú hodnotu, ak použijeme rovnaký vzorec AGGREGATE, zobrazí sa chyba, ale keď sa použije vhodná možnosť, AGGREGATE v programe Excel poskytne priemer zvyšných hodnôt zanedbávajúcich chybu hodnota v E6.

Aby sme mohli ignorovať chybové hodnoty, máme možnosť 6.

Podobne pre rozsah G1: G8 použijeme možnosť 6 (ignorujte chybové hodnoty)

Teraz pre rozsah H3, ak dáme hodnotu 64, a skryjeme tretí riadok a použijeme možnosť 5, budeme ignorovať skrytý riadok, AGGREGATE v Exceli, dáme priemernú hodnotu iba pre viditeľné číselné hodnoty.

Výstup bez skrytia riadku 3

Výstup po skrytí riadku 3

Aplikujeme vzorec AGGREGATE na ďalšie operácie, máme

Príklad - # 2

Predpokladajme, že máme tabuľku výnosov generovaných v rôznych dátumoch z rôznych kanálov, ako je uvedené nižšie

Teraz chceme skontrolovať výnosy generované pre rôzne kanály. Takže keď použijeme funkciu súčtu, dostaneme celkové vygenerované výnosy, ale v prípade, že chceme skontrolovať výnosy vygenerované pre organický kanál alebo priamy kanál alebo akýkoľvek iný, keď použijeme filtre v exceli na to isté, funkcia súčtu bude vždy uveďte celkovú sumu

Chceme, aby sme pri filtrovaní kanála dostali súčet hodnôt, ktoré sú viditeľné, takže namiesto použitia funkcie SUM použijeme funkciu AGGREGATE, aby sme získali súčet hodnôt, ktoré sú viditeľné, keď je filter aplikovaný.

Takže nahradením vzorca SUM funkciou AGGREGATE kódom možnosti 5 (ignorujeme skryté riadky a hodnoty), ktoré máme,

Keď teraz použijeme filter pre rôzne kanály, zobrazí sa výnos pre tento kanál až po skrytí zvyšných riadkov.

Celkové príjmy generované pre priamy kanál:

Celkové príjmy generované pre organický kanál:

Celkové výnosy generované pre platený kanál:

Vidíme teda, že funkcia AGGREGATE počíta rôzne hodnoty súčtu výnosov generovaných pre rôzne kanály, akonáhle sú filtrované. Funkciu AGGREGATE teda možno dynamicky použiť na nahradenie rôznych funkcií rôznymi podmienkami bez použitia podmieneného vzorca.

Predpokladajme, že pri rovnakom kanáli a výnose tabuľky obsahujú niektoré z našich hodnôt výnosov chybu. Teraz musíme chyby ignorovať a zároveň, ak chceme použiť filter, mala by funkcia AGGREGATE ignorovať aj hodnoty skrytých riadkov.

Keď použijeme možnosť 5, dostaneme chybu pre SUM celkových výnosov, teraz aby sme ignorovali chyby, ktoré musíme použiť, možnosť 6

Použitím možnosti 6 dostaneme súčet ignorujúci chybové hodnoty, ale keď použijeme filter, napríklad filtrovať podľa hodnoty kanálu Direct, dostaneme rovnaký súčet ignorujúci chyby, ale zároveň musíme ignorovať aj skryté hodnoty.

V tomto prípade teda použijeme možnosť 7, ktorá ignoruje chybové hodnoty a zároveň skryté riadky

Na čo treba pamätať

  • Funkcia AGGREGATE nerozpozná hodnotu funkcie _ num väčšiu ako 19 alebo menšiu ako 1 a podobne pre číslo možnosti nerozpozná hodnoty väčšie ako 7 a menšie ako 1, ak uvedieme akékoľvek iné hodnoty, dá #VALUE ! Chyba
  • Vždy akceptuje číselnú hodnotu a vždy vráti číselnú hodnotu ako výstup
  • AGGREGATE v programe Excel má obmedzenie; ignoruje iba skryté riadky, ale ignoruje skryté stĺpce.