Kako koristiti Excel AGGREGATE funkciju -

Sadržaj

Sažetak

Funkcija Excel AGGREGATE vraća skupni izračun poput PROSJECA, BROJ, MAKS, itd., Po želji zanemarujući skrivene retke i pogreške. Dostupno je ukupno 19 operacija, specificiranih brojem funkcije u prvom argumentu (opcije potražite u tablici).

Svrha

Povrat agregatnog izračuna

Povratna vrijednost

Ovisi o navedenoj funkciji

Sintaksa

= AGREGATE (funkcija_broj, opcije, ref1, ref2)

Argumenti

  • function_num - Operacija za izvođenje (1-19).
  • options - Vrijednosti koje treba zanemariti (0-7).
  • ref1 - Prvi argument.
  • ref2 - Drugi argument (k).

Verzija

Excel 2010

Napomene o upotrebi

Funkcija AGGREGATE vraća rezultat skupnog izračuna poput PROSJEČNOG, BROJ, MAKS, MIN, itd. Dostupno je ukupno 19 operacija, a operacija koju treba izvesti navedena je kao broj, koji se pojavljuje kao prvi argument u funkciji. Drugi argument, opcije, kontrolira kako AGGREGATE obrađuje pogreške i vrijednosti u skrivenim redovima. Pogledajte donje tablice za sve dostupne opcije.

Primjer # 1

Da biste vratili MAX vrijednost u rasponu A1: A10, zanemarujući i pogreške i skrivene retke, navedite 4 za broj funkcije i 7 za opcije:

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

Da biste vratili MIN vrijednost s istim opcijama, promijenite broj funkcije u 5:

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

Primjer # 2

U gore prikazanom primjeru formula u D5 je:

=AGGREGATE(4,6,values)

gdje je "vrijednosti" imenovani raspon B5: B14. Broj funkcije je 4, što određuje MAX. Opcije se pružaju kao 6, za zanemarivanje samo pogrešaka.

Primjer # 3 - n. Najveći

Formule u D8: D10 pokazuju kako vratiti "n-te najveće" vrijednosti:

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

Ovdje je broj funkcije 14, koji pokreće funkciju LARGE. Budući da funkcija LARGE zahtijeva ak argument, ona se pojavljuje kao posljednji argument u gore navedene tri formule.

Primjer # 4 - rad s nizom

Ono što AGREGATE čini posebno korisnim za složenije formule je to što može nativno obrađivati ​​nizove kada je broj funkcije 14-19. Na primjer, da biste pronašli MAX vrijednost ponedjeljkom, s podacima koji uključuju datume i vrijednosti, možete upotrijebiti AGGREGATE ovako:

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

Ovdje navodimo 14 za funkciju (LARGE) i 6 za opciju (zanemari pogreške). Zatim gradimo logički izraz pomoću funkcije TEXT za provjeru svih datuma ponedjeljkom. Rezultat ove operacije je niz TRUE / FALSE vrijednosti, koje postaju nazivnik izvornih vrijednosti. FALSE procjenjuje kao nulu i baca # DIV / 0! pogreška. TRUE izračunava vrijednost 1 i vraća izvornu vrijednost. Konačni niz vrijednosti i pogrešaka djeluje poput filtra. AGGREGATE ignorira sve pogreške i vraća najveću (maksimum) od preživjelih vrijednosti. Potpuniji primjer ovdje.

Brojevi funkcija

Tablica u nastavku navodi brojeve funkcija dostupnih funkciji AGREGATE, zajedno s nazivom pridružene funkcije. Treći stupac, Ref2, označava vrijednost drugog argumenta koja se očekuje kada je potrebno.

Funkcija Funkcija Ref2
1 PROSJEČNO
2 RAČUNATI
3 TAČKA
4 MAKS
5 MIN
6 PROIZVOD
7 STDEV.S
8 STDEV.P
9 IZNOS
10 VAR.S
11 VAR.P
12 MEDIJAN
13 NAČIN.SNGL
14 VELIKO k
15 MALI k
16 PERCENTILE.INC k
17 KVARTILNI.INC kvart
18 PERCENTILE.EXC k
19 KVARTILNI.EXC kvart

Opcije ponašanja

Funkcija AGGREGATE ima mnogo mogućnosti za zanemarivanje pogrešaka, skrivenih redaka i drugih funkcija. Moguće vrijednosti su 0-7, kao što je prikazano u donjoj tablici.

Opcija Ponašanje
0 Zanemarite funkcije SUBTOTAL i AGREGATE
1 Zanemarite skrivene retke, funkcije SUBTOTAL i AGREGATE
2 Zanemarite vrijednosti pogrešaka, funkcije SUBTOTAL i AGREGATE
3 Zanemarite skrivene retke, vrijednosti pogrešaka, funkcije SUBTOTAL i AGREGATE
4 Ništa ne ignorirajte
5 Zanemari skrivene retke
6 Zanemari vrijednosti pogreške
7 Zanemari skrivene retke i vrijednosti pogrešaka

Bilješke

  • AGGREGATE vraća #Vrijednost! pogreška ako je potreban drugi argument funkcije, ali nije naveden.
  • 3D reference ne rade s AGREGATE.
  • Funkcija AGGREGATE dizajnirana je okomito, a ne vodoravno.

Zanimljivi članci...