Jak používat funkci Excel AGGREGATE -

souhrn

Funkce Excel AGGREGATE vrací agregovaný výpočet jako PRŮMĚR, POČET, MAX atd., Volitelně ignoruje skryté řádky a chyby. K dispozici je celkem 19 operací specifikovaných číslem funkce v prvním argumentu (možnosti viz tabulka).

Účel

Souhrnný výpočet návratnosti

Návratová hodnota

Závisí na zadané funkci

Syntax

= AGGREGATE (číslo_funkce, možnosti, ref1, ref2)

Argumenty

  • function_num - operace k provedení (1-19).
  • options - Hodnoty k ignorování (0-7).
  • ref1 - první argument.
  • ref2 - Druhý argument (k).

Verze

Excel 2010

Poznámky k použití

Funkce AGGREGATE vrací výsledek agregovaného výpočtu, jako je PRŮMĚR, POČET, MAX, MIN atd. K dispozici je celkem 19 operací a operace, která má být provedena, je zadána jako číslo, které se objeví jako první argument ve funkci. Druhý argument, možnosti, řídí, jak AGGREGATE zpracovává chyby a hodnoty ve skrytých řádcích. V tabulce níže jsou uvedeny všechny dostupné možnosti.

Příklad č. 1

Chcete-li vrátit hodnotu MAX v rozsahu A1: A10, ignorovat chyby i skryté řádky, zadejte 4 pro číslo funkce a 7 pro možnosti:

=AGGREGATE(4,7,A1:A10) // max value

Chcete-li vrátit hodnotu MIN se stejnými možnostmi, změňte číslo funkce na 5:

=AGGREGATE(5,7,A1:A10) // min value

Příklad č. 2

V příkladu zobrazeném výše je vzorec v D5:

=AGGREGATE(4,6,values)

kde „values“ je pojmenovaný rozsah B5: B14. Číslo funkce je 4, což určuje MAX. Možnosti jsou k dispozici jako 6, pouze pro ignorování chyb.

Příklad č. 3 - n. Největší

Vzorce v D8: D10 ukazují, jak vrátit hodnoty „n-té největší“:

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Zde je číslo funkce 14, které spouští funkci VELKÉ. Protože funkce LARGE vyžaduje ak argument, zobrazí se jako poslední argument ve třech výše uvedených vzorcích.

Příklad č. 4 - operace s polem

Díky čemuž je AGGREGATE zvláště užitečný pro složitější vzorce, je to, že dokáže nativně zpracovat pole, když je číslo funkce 14-19. Chcete-li například najít hodnotu MAX v pondělí, s daty, která zahrnují data a hodnoty, můžete použít AGGREGATE takto:

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

Zde zadáme 14 pro funkci (LARGE) a 6 pro volbu (ignorovat chyby). Potom vytvoříme logický výraz pomocí funkce TEXT, abychom zkontrolovali všechna data pro pondělí. Výsledkem této operace je pole TRUE / FALSE hodnot, které se stanou jmenovatelem původních hodnot. FALSE se vyhodnotí jako nula a hodí # DIV / 0! chyba. TRUE vyhodnotí jako 1 a vrátí původní hodnotu. Konečné pole hodnot a chyb funguje jako filtr. AGGREGATE ignoruje všechny chyby a vrátí největší (maximální) z přežívajících hodnot. Úplnější příklad zde.

Čísla funkcí

V tabulce níže jsou uvedena čísla funkcí dostupná pro funkci AGGREGATE spolu s názvem přidružené funkce. Třetí sloupec Ref2 označuje druhou hodnotu argumentu očekávanou v případě potřeby.

Funkce Funkce Ref2
1 PRŮMĚRNÝ
2 POČET
3 COUNTA
4 MAX
5 MIN
6 PRODUKT
7 STDEV.S
8 STDEV.P
9 SOUČET
10 VAR.S
11 VAR.P
12 MEDIÁN
13 MODE.SNGL
14 VELKÝ k
15 MALÝ k
16 PERCENTIL.INC k
17 QUARTILE.INC kvart
18 PERCENTILE.EXC k
19 QUARTILE.EXC kvart

Možnosti chování

Funkce AGGREGATE má mnoho možností pro ignorování chyb, skrytých řádků a dalších funkcí. Možné hodnoty jsou 0-7, jak ukazuje následující tabulka.

Volba Chování
0 Ignorujte funkce SUBTOTAL a AGGREGATE
1 Ignorujte skryté řádky, funkce SUBTOTAL a AGGREGATE
2 Ignorujte chybové hodnoty, funkce SUBTOTAL a AGGREGATE
3 Ignorujte skryté řádky, chybové hodnoty, funkce SUBTOTAL a AGGREGATE
4 Ignorovat nic
5 Ignorovat skryté řádky
6 Chybové hodnoty ignorovat
7 Ignorujte skryté řádky a chybové hodnoty

Poznámky

  • AGGREGATE vrátí #HODNOTA! chyba, pokud je vyžadován druhý argument funkce, ale není poskytnut.
  • 3D reference s AGGREGATE nefungují.
  • Funkce AGGREGATE je navržena jako vertikální rozsahy, nikoli horizontální rozsahy.

Zajímavé články...