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äkningReturvärde
Beror på specificerad funktionSyntax
= 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 2010Anvä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.