Funkce AGGREGATE v aplikaci Excel
AGGREGATE Funkce v aplikaci Excel vrací agregát dané datové tabulky nebo seznamů dat, tato funkce má také první argument jako číslo funkce a další argumenty jsou pro rozsah datových sad, číslo funkce by mělo být zapamatováno, aby bylo známo, kterou funkci použít .
Syntax
Pro AGGREGATE vzorec existují dvě syntaxe:
- Referenční syntaxe
= AGGREGATE (function_num, options, ref1, ref2, ref (3),…)
- Syntaxe pole
= AGGREGATE (číslo_funkce, možnosti, pole, (k))
Function_num je číslo, které označuje konkrétní funkci, kterou chceme použít; je to číslo od 1 do 19
Možnost: je to také číselná hodnota v rozmezí od 0 do 7 a určuje, které hodnoty mají být při výpočtech ignorovány
Ref1, ref2, ref (3): je argument při použití referenční syntaxe; je to číselná hodnota nebo hodnoty, na kterých chceme provést výpočet, jsou vyžadovány alespoň dva argumenty. Argumenty odpočinku jsou volitelné.
Pole: je pole hodnot, na kterých chceme pracovat; používá se v syntaxi pole funkce AGGREGATE v aplikaci Excel.
K: je volitelný argument a číselná hodnota; používá se, když se v Excelu používá funkce jako LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC nebo QUARTILE.EXC.
Příklady
Příklad - # 1
Předpokládejme, že máme seznam čísel a vypočítáme průměr, počet, což je počet buněk, které obsahují hodnotu, počet-počet buněk, které nejsou prázdné, maximum, minimum, součin a součet daných číselných hodnot . Hodnoty jsou uvedeny níže v tabulce:
Nejprve vypočítáme průměr v řádku 9 pro všechny dané hodnoty. Pro průměr je číslo_funkce 1
Ve sloupci C jsou uvedeny všechny hodnoty a nebudeme muset ignorovat žádné hodnoty, abychom vybrali možnost 4 (nic ignorovat)
A výběr rozsahu hodnot C1: C8 jako pole číselných hodnot
Protože ' k' je volitelný argument a používá se, když je použita funkce jako LARGE, SMALL v Excelu, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC nebo QUARTILE.EXC, ale v tomto případě počítáme průměr do vynechat hodnotu k.
Průměrná hodnota tedy je
Podobně pro rozsah D1: D8 znovu vybereme možnost 4.
Pro rozsah E1: E8 obsahuje buňka E6 chybovou hodnotu. Pokud použijeme stejný vzorec AGGREGATE, zobrazí se chyba. I když je použita vhodná možnost, AGGREGATE v aplikaci Excel udává průměr zbývajících hodnot zanedbávající chybovou hodnotu v E6.
Chcete-li ignorovat chybové hodnoty, máme možnost 6.
Podobně pro rozsah G1: G8 použijeme možnost 6 (ignorovat chybové hodnoty)
Nyní pro rozsah H3, pokud zadáme hodnotu 64 a skryjeme třetí řádek a použijeme možnost 5, budeme ignorovat skrytý řádek, AGGREGATE v aplikaci Excel, uvedeme průměrnou hodnotu pouze pro viditelné číselné hodnoty.
Výstup bez skrytí řádku 3
Výstup po skrytí řádku 3
Aplikujeme vzorec AGGREGATE na další operace, máme
Příklad - # 2
Předpokládejme, že máme tabulku příjmů generovaných k různým datům z různých kanálů, jak je uvedeno níže
Nyní chceme zkontrolovat výnosy generované pro různé kanály. Když tedy použijeme funkci součtu, získáme celkové generované výnosy, ale v případě, že chceme zkontrolovat výnosy generované pro organický kanál nebo přímý kanál nebo jakýkoli jiný, když použijeme filtry v aplikaci Excel pro stejné, funkce součtu vždy uvede celkovou částku
Chceme, abychom při filtrování kanálu dostali součet viditelných hodnot, takže místo použití funkce SUM použijeme funkci AGGREGATE k získání součtu hodnot, které jsou viditelné při použití filtru.
Takže nahrazením vzorce SUM funkcí AGGREGATE s kódem možnosti 5 (ignorování skrytých řádků a hodnot) máme,
Když nyní použijeme filtr pro různé kanály, zobrazí se výnosy pro tento kanál, až se skryjí ostatní řádky.
Celkové výnosy generované pro přímý kanál:
Celkové výnosy generované pro organický kanál:
Celkové výnosy generované pro placený kanál:
Vidíme, že funkce AGGREGATE vypočítává různé hodnoty součtu výnosů generovaných pro různé kanály, jakmile jsou filtrovány. Funkci AGGREGATE lze tedy dynamicky použít k nahrazení různých funkcí pro různé podmínky bez použití podmíněného vzorce.
Předpokládejme, že pro stejnou tabulku obsahují některé z našich hodnot výnosů chybu, nyní musíme chyby ignorovat a zároveň, pokud chceme použít filtr, měla by funkce AGGREGATE ignorovat také hodnoty skrytých řádků.
Když použijeme možnost 5, dostaneme chybu pro SUM celkových výnosů. Abychom chyby ignorovali, musíme použít možnost 6
Pomocí možnosti 6 dostaneme součet ignorující chybové hodnoty. Když ale použijeme filtr, například filtrujeme podle hodnoty kanálu Direct, dostaneme stejný součet ignorující chyby, ale zároveň musíme ignorovat také skryté hodnoty.
V tomto případě tedy použijeme volbu 7, která ignoruje chybové hodnoty a zároveň skryté řádky.
Věci k zapamatování
- Funkce AGGREGATE nerozpozná hodnotu _num funkce větší než 19 nebo menší než 1. Podobně pro číslo možnosti neidentifikuje hodnoty větší než 7 a menší než 1; pokud poskytneme jakékoli další hodnoty, dává #HODNOTA! Chyba
- Vždy přijímá číselnou hodnotu a vždy vrací číselnou hodnotu jako výstup
- AGGREGATE v aplikaci Excel má omezení; ignoruje pouze skryté řádky, ale ignoruje skryté sloupce.
