Sådan bruges Excel AGGREGATE-funktionen

Resumé

Excel AGGREGATE-funktionen returnerer en samlet beregning som GENNEMSNIT, TÆLLE, MAKS osv., Der eventuelt ignorerer skjulte rækker og fejl. I alt 19 operationer er tilgængelige, specificeret efter funktionsnummer i det første argument (se tabel for valgmuligheder).

Formål

Returnere samlet beregning

Returneringsværdi

Afhænger af den angivne funktion

Syntaks

= AGGREGATE (funktionsnummer, optioner, ref1, ref2)

Argumenter

  • function_num - Funktion, der skal udføres (1-19).
  • optioner - Værdier, der skal ignoreres (0-7).
  • ref1 - Første argument.
  • ref2 - Andet argument (k).

Version

Excel 2010

Brugsanvisninger

Funktionen AGGREGATE returnerer resultatet af en samlet beregning som GEMIDDEL, TÆLLE, MAKS, MIN osv. I alt 19 operationer er tilgængelige, og den operation, der skal udføres, angives som et tal, der vises som det første argument i funktionen. Det andet argument, valgmuligheder, styrer hvordan AGGREGATE håndterer fejl og værdier i skjulte rækker. Se tabeller nedenfor for alle tilgængelige muligheder.

Eksempel 1

For at returnere MAX-værdien i området A1: A10, ignorer både fejl og skjulte rækker, skal du angive 4 for funktionsnummer og 7 for muligheder:

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

For at returnere MIN-værdien med de samme muligheder skal du ændre funktionsnummeret til 5:

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

Eksempel 2

I eksemplet vist ovenfor er formlen i D5:

=AGGREGATE(4,6,values)

hvor "værdier" er det navngivne område B5: B14. Funktionsnummeret er 4, som specificerer MAX. Valgmuligheder er angivet som 6 for kun at ignorere fejl.

Eksempel 3 - den største

Formlerne i D8: D10 demonstrerer, hvordan man returnerer "nth største" værdier:

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

Funktionsnummeret her er 14, som kører LARGE-funktionen. Da LARGE-funktionen kræver et argument, vises det som det sidste argument i de tre formler ovenfor.

Eksempel # 4 - matrixhandling

Hvad der gør AGGREGATE især nyttigt til mere komplekse formler er, at det kan håndtere arrays indbygget, når funktionsnummeret er 14-19. For eksempel for at finde MAX-værdien på mandage med data, der inkluderer datoer og værdier, kan du bruge AGGREGATE sådan:

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

Her specificerer vi 14 for funktion (LARGE) og 6 for option (ignorer fejl). Derefter bygger vi et logisk udtryk ved hjælp af TEKST-funktionen til at kontrollere alle datoer for mandage. Resultatet af denne operation er en matrix af SAND / FALSK-værdier, der bliver nævneren for de oprindelige værdier. FALSE evalueres som nul og kaster et # DIV / 0! fejl. TRUE vurderes som 1 og returnerer den oprindelige værdi. Den sidste række værdier og fejl fungerer som et filter. AGGREGATE ignorerer alle fejl og returnerer den største (maksimale) af de overlevende værdier. Mere komplet eksempel her.

Funktionsnumre

Tabellen nedenfor viser de funktionsnumre, der er tilgængelige for AGGREGATE-funktionen sammen med navnet på den tilknyttede funktion. Den tredje kolonne, Ref2, angiver den anden argumentværdi, der forventes, når det kræves.

Fungere Fungere Ref2
1 GENNEMSNIT
2 TÆLLE
3 COUNTA
4 MAX
5 MIN
6 PRODUKT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 STOR k
15 LILLE k
16 PERCENTILE.INC k
17 QUARTILE.INC kvart
18 PERCENTILE.EXC k
19 QUARTILE.EXC kvart

Adfærdsmuligheder

AGGREGATE-funktionen har mange muligheder for at ignorere fejl, skjulte rækker og andre funktioner. Mulige værdier er 0-7, som vist i nedenstående tabel.

Mulighed Opførsel
0 Ignorer SUBTOTAL- og AGGREGATE-funktioner
1 Ignorer skjulte rækker, SUBTOTAL- og AGGREGATE-funktioner
2 Ignorer fejlværdier, SUBTOTAL og AGGREGATE-funktioner
3 Ignorer skjulte rækker, fejlværdier, SUBTOTAL og AGGREGATE-funktioner
4 Ignorer intet
5 Ignorer skjulte rækker
6 Ignorer fejlværdier
7 Ignorer skjulte rækker og fejlværdier

Bemærkninger

  • AGGREGATE returnerer en #VALUE! fejl, hvis der kræves et andet funktionsargument, men ikke leveres.
  • 3D-referencer fungerer ikke med AGGREGATE.
  • AGGREGATE-funktionen er designet lodrette områder, ikke vandrette områder.

Interessante artikler...