Hur man använder Excel AGGREGATE-funktionen

Innehållsförteckning

Sammanfattning

Excel AGGREGATE-funktionen returnerar en aggregerad beräkning som GENOMSNITT, RÄKNING, MAX, etc., eventuellt ignorerar dolda rader och fel. Totalt finns 19 operationer tillgängliga, specificerade av funktionsnummer i det första argumentet (se tabell för alternativ).

Ändamål

Returnera aggregerad beräkning

Returvärde

Beror på specificerad funktion

Syntax

= AGGREGATE (funktionsnummer, alternativ, ref1, ref2)

Argument

  • function_num - Funktion att utföra (1-19).
  • alternativ - Värden att ignorera (0-7).
  • ref1 - Första argumentet.
  • ref2 - Andra argumentet (k).

Version

Excel 2010

Användningsanmärkningar

Funktionen AGGREGATE returnerar resultatet av en aggregerad beräkning som GENOMSNITT, RÄKNING, MAX, MIN, etc. Totalt finns 19 operationer tillgängliga, och operationen som ska utföras anges som ett tal, som visas som det första argumentet i funktionen. Det andra argumentet, alternativ, styr hur AGGREGATE hanterar fel och värden i dolda rader. Se tabeller nedan för alla tillgängliga alternativ.

Exempel 1

För att returnera MAX-värdet i intervallet A1: A10, ignorera både fel och dolda rader, ange 4 för funktionsnummer och 7 för alternativ:

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

För att returnera MIN-värdet med samma alternativ, ändra funktionsnumret till 5:

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

Exempel 2

I exemplet som visas ovan är formeln i D5:

=AGGREGATE(4,6,values)

där "värden" är det namngivna området B5: B14. Funktionsnumret är 4, vilket anger MAX. Alternativ tillhandahålls som 6, endast för att ignorera fel.

Exempel # 3 - nionde största

Formlerna i D8: D10 visar hur man returnerar "nth största" värden:

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

Funktionsnumret här är 14, som kör LARGE-funktionen. Eftersom LARGE-funktionen kräver ett argument, visas det som det sista argumentet i de tre formlerna ovan.

Exempel # 4 - arrayoperation

Vad som gör AGGREGATE särskilt användbart för mer komplexa formler är att den kan hantera matriser naturligt när funktionsnumret är 14-19. För att till exempel hitta MAX-värdet på måndagar, med data som inkluderar datum och värden, kan du använda AGGREGATE så här:

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

Här anger vi 14 för funktion (STOR) och 6 för alternativ (ignorera fel). Sedan bygger vi ett logiskt uttryck med hjälp av TEXT-funktionen för att kontrollera alla datum för måndagar. Resultatet av denna operation är en matris med SANT / FALSKA värden, som blir nämnaren för de ursprungliga värdena. FALSE utvärderas som noll och kastar ett # DIV / 0! fel. TRUE utvärderas som 1 och returnerar det ursprungliga värdet. Den slutliga matrisen av värden och fel fungerar som ett filter. AGGREGATE ignorerar alla fel och returnerar det största (maximala) av de överlevande värdena. Mer komplett exempel här.

Funktionsnummer

I tabellen nedan listas de funktionsnummer som är tillgängliga för AGGREGATE-funktionen, tillsammans med namnet på den associerade funktionen. Den tredje kolumnen, Ref2, anger det andra argumentvärdet som förväntas vid behov.

Fungera Fungera Ref2
1 GENOMSNITT
2 RÄKNA
3 COUNTA
4 MAX
5 MIN
6 PRODUKT
7 STDEV.S
8 STDEV.P
9 BELOPP
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 STOR k
15 SMÅ k
16 PERCENTILE.INC k
17 QUARTILE.INC quart
18 PERCENTILE.EXC k
19 QUARTILE.EXC quart

Beteendealternativ

AGGREGATE-funktionen har många alternativ för att ignorera fel, dolda rader och andra funktioner. Möjliga värden är 0-7, som visas i tabellen nedan.

Alternativ Beteende
0 Ignorera SUBTOTAL- och AGGREGATE-funktioner
1 Ignorera dolda rader, SUBTOTAL och AGGREGATE-funktioner
2 Ignorera felvärden, SUBTOTAL och AGGREGATE-funktioner
3 Ignorera dolda rader, felvärden, SUBTOTAL och AGGREGATE-funktioner
4 Ignorera ingenting
5 Ignorera dolda rader
6 Ignorera felvärden
7 Ignorera dolda rader och felvärden

Anteckningar

  • AGGREGATE returnerar en #VALUE! fel om ett andra funktionsargument krävs men inte tillhandahålls.
  • 3D-referenser fungerar inte med AGGREGATE.
  • AGGREGATE-funktionen är utformad vertikala områden, inte horisontella områden.

Intressanta artiklar...