Ako používať funkciu Excel AGGREGATE

Zhrnutie

Funkcia Excel AGGREGATE vráti súhrnný výpočet ako AVERAGE, COUNT, MAX atď., Voliteľne ignoruje skryté riadky a chyby. K dispozícii je celkom 19 operácií, ktoré sú špecifikované číslom funkcie v prvom argumente (možnosti nájdete v tabuľke).

Účel

Súhrnný výpočet návratnosti

Návratová hodnota

Závisí od zadanej funkcie

Syntax

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

Argumenty

  • function_num - operácia, ktorá sa má vykonať (1-19).
  • možnosti - Hodnoty, ktoré sa majú ignorovať (0-7).
  • ref1 - prvý argument.
  • ref2 - Druhý argument (k).

Verzia

Excel 2010

Poznámky k použitiu

Funkcia AGGREGATE vráti výsledok agregovaného výpočtu ako AVERAGE, COUNT, MAX, MIN atď. K dispozícii je celkovo 19 operácií a operácia, ktorá sa má vykonať, je zadaná ako číslo, ktoré sa zobrazí ako prvý argument vo funkcii. Druhý argument, voľby, určuje, ako AGGREGATE spracováva chyby a hodnoty v skrytých riadkoch. V tabuľke nižšie sú uvedené všetky dostupné možnosti.

Príklad č

Ak chcete vrátiť hodnotu MAX v rozsahu A1: A10, ignorujúc chyby aj skryté riadky, zadajte 4 pre číslo funkcie a 7 pre možnosti:

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

Ak chcete vrátiť hodnotu MIN s rovnakými možnosťami, zmeňte číslo funkcie na 5:

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

Príklad č

V príklade uvedenom vyššie je vzorec v D5:

=AGGREGATE(4,6,values)

kde „hodnoty“ sú pomenovaný rozsah B5: B14. Číslo funkcie je 4, čo určuje MAX. Možnosti sú poskytované ako 6, aby sa ignorovali iba chyby.

Príklad č. 3 - n-tý najväčší

Vzorce v D8: D10 ukazujú, ako vrátiť hodnoty „n-tej najväčšej“:

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

Číslo funkcie je tu 14, čím sa spustí funkcia LARGE. Pretože funkcia VEĽKÉ vyžaduje argument ak, javí sa ako posledný argument v troch vyššie uvedených vzorcoch.

Príklad č. 4 - operácia poľa

Vďaka čomu je AGGREGATE obzvlášť užitočný pre zložitejšie vzorce, je to, že dokáže natívne spracovať polia, keď je číslo funkcie 14-19. Napríklad na vyhľadanie hodnoty MAX v pondelok s údajmi, ktoré zahŕňajú dátumy a hodnoty, môžete použiť AGGREGATE takto:

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

Tu zadáme 14 pre funkciu (LARGE) a 6 pre voľbu (chyby ignorovať). Potom pomocou funkcie TEXT zostavíme logický výraz, aby sme skontrolovali všetky dátumy pre pondelok. Výsledkom tejto operácie je pole hodnôt TRUE / FALSE, ktoré sa stanú menovateľom pôvodných hodnôt. FALSE sa vyhodnotí ako nula a hodí # DIV / 0! chyba. Hodnota TRUE sa vyhodnotí ako 1 a vráti pôvodnú hodnotu. Výsledné pole hodnôt a chýb funguje ako filter. AGGREGATE ignoruje všetky chyby a vráti najväčšiu (maximálnu) z prežívajúcich hodnôt. Kompletnejší príklad tu.

Čísla funkcií

V nasledujúcej tabuľke sú uvedené čísla funkcií dostupné pre funkciu AGGREGATE spolu s názvom priradenej funkcie. Tretí stĺpec, Ref2, označuje hodnotu druhého argumentu očakávanú, keď je to potrebné.

Funkcia Funkcia Ref2
1 PRIEMER
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 VÝROBOK
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 REŽIM.SNGL
14 VEĽKÝ k
15 MALÉ k
16 PERCENTIL.INC k
17 QUARTILE.INC kvart
18 PERCENTILE.EXC k
19 QUARTILE.EXC kvart

Možnosti správania

Funkcia AGGREGATE má veľa možností na ignorovanie chýb, skrytých riadkov a ďalších funkcií. Možné hodnoty sú 0-7, ako je uvedené v nasledujúcej tabuľke.

Možnosť Správanie
0 Ignorujte funkcie SUBTOTAL a AGGREGATE
1 Ignorujte skryté riadky, funkcie SUBTOTAL a AGGREGATE
2 Ignorujte chybové hodnoty, SUBTOTAL a AGGREGATE funkcie
3 Ignorujte skryté riadky, chybové hodnoty, funkcie SUBTOTAL a AGGREGATE
4 Ignorovať nič
5 Skryté riadky ignorujte
6 Chybné hodnoty ignorujte
7 Ignorujte skryté riadky a chybové hodnoty

Poznámky

  • AGGREGATE vráti #HODNOTU! chyba, ak je potrebný druhý argument funkcie, ale nie je uvedený.
  • 3D referencie nefungujú s AGGREGATE.
  • Funkcia AGGREGATE je určená pre vertikálne rozsahy, nie pre horizontálne rozsahy.

Zaujímavé články...