AGGREGATE-funktion i Excel
AGGREGATE-funktionen i Excel returnerar aggregatet för en given datatabell eller datalistor, den här funktionen har också det första argumentet som funktionsnummer och ytterligare argument är för ett intervall av datauppsättningarna, funktionsnumret bör komma ihåg för att veta vilken funktion som ska användas .
Syntax
Det finns två syntaxer för AGGREGATE Formula:
- Referenssyntax
= AGGREGATE (funktionsnummer, alternativ, ref1, ref2, ref [3], ...)
- Array Syntax
= AGGREGATE (funktionsnummer, alternativ, array, [k])
Function_num är ett tal som anger en specifik funktion som vi vill använda, det är ett tal från 1-19
Alternativ: det är också ett numeriskt värde som sträcker sig från 0 till 7 och avgör vilka värden som ska ignoreras vid beräkningar
Ref1, ref2, ref [3]: är argumentet när man använder referenssyntaxen, det är numeriskt värde eller värden som vi vill utföra beräkningen på, åtminstone två argument krävs rest argument är valfria.
Array: är en matris av värden som vi vill utföra operationen på, den används i array-syntax för AGGREGATE-funktionen i Excel
K: är ett valfritt argument och är ett numeriskt värde, det används när funktionen som LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel används.
Exempel
Du kan ladda ner denna Excel-mall för AGGREGATE här - AGGREGATE-funktion Excel-mallExempel - # 1
Antag att vi har en lista med siffror och att vi beräknar medelvärdet, antalet som är antalet celler som innehåller ett värde, counta-antalet celler som inte är tomma, maximalt, minimalt, produkt och summan av de angivna numeriska värdena. Värdena ges nedan i tabellen:
Låt oss först beräkna genomsnittet i rad 9 för alla angivna värden. För genomsnittet är funktionen_ num
I kolumn C ges alla värden och vi behöver inte ignorera några värden, så vi väljer alternativ 4 (ignorerar ingenting)
Och välja värdena C1: C8 som en uppsättning numeriska värden
Eftersom ' k' är ett valfritt argument och används när en funktion som LARGE, SMALL i Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC används men i det här fallet beräknar vi genomsnittet så att vi kommer att utelämna värdet på k.
Så det genomsnittliga värdet är
På samma sätt, för intervall D1: D8, väljer vi igen alternativ 4.
För intervall E1: E8 innehåller en cell E6 ett felvärde, om vi använder samma AGGREGATE-formel får vi ett fel, men när ett lämpligt alternativ används, ger AGGREGATE i Excel genomsnittet av de återstående värdena som försummar felet värde i E6.
För att ignorera felvärdena har vi alternativ 6.
På samma sätt, för intervall G1: G8 kommer vi att använda alternativet 6 (ignorera felvärdena)
Nu, för intervall H3 om vi lägger till ett värde 64 och döljer den tredje raden och använder alternativ 5, för att ignorera den dolda raden, kommer AGGREGATE i Excel endast att ge medelvärdet för synliga numeriska värden.
Output utan att dölja rad 3
Resultat efter att ha gömt rad 3
Tillämpa AGGREGATE-formeln för andra operationer har vi
Exempel - # 2
Antag att vi har en tabell för intäkterna som genereras på olika datum från de olika kanalerna enligt nedan
Nu vill vi kontrollera intäkterna från olika kanaler. Så när vi tillämpar summeringsfunktionen får vi den totala intäkten som genereras, men om vi vill kontrollera intäkterna som genereras för organisk kanal eller direktkanal eller någon annan, när vi tillämpar filter i excel för samma, kommer summan alltid att fungera ge den totala summan
Vi vill att när vi filtrerar kanalen, får vi summan av de värden som är synliga, så istället för att använda SUM-funktionen kommer vi att använda AGGREGATE-funktionen för att få summan av värdena som är synliga när ett filter är applicerad.
Så att ersätta SUM-formeln med en AGGREGATE-funktion med alternativkod 5 (ignorerar de dolda raderna och värdena) vi har,
När vi nu använder filtret för olika kanaler kommer det att visa intäkterna för den kanalen bara när resten av raderna blir dolda.
Totala intäkter genererade för direkt kanal:
Totala intäkter genererade för organisk kanal:
Totala intäkter genererade för betald kanal:
Så vi kan se att funktionen AGGREGATE beräknar de olika summan för intäkterna som genereras för olika kanaler när de har filtrerats. Så AGGREGATE-funktionen kan användas dynamiskt för att ersätta olika funktioner för olika förhållanden utan att använda den villkorliga formeln.
Antag att för samma tabellkanal och intäkter innehåller några av våra intäktsvärden ett fel, nu måste vi ignorera felen och samtidigt, om vi vill tillämpa ett filter, bör funktionen AGGREGATE också ignorera de dolda radvärdena.
När vi använder alternativ 5 får vi felet för SUMMA av de totala intäkterna, nu för att ignorera felen måste vi använda alternativ 6
Med alternativ 6 får vi summan som ignorerar felvärdena, men när vi tillämpar filtret, till exempel, filtrerar efter kanalvärde direkt får vi samma summa som ignorerar felen men samtidigt måste vi också ignorera de dolda värdena.
Så i det här fallet kommer vi att använda alternativet 7 som ignorerar felvärdena och samtidigt de dolda raderna
Saker att komma ihåg
- AGGREGATE-funktionen känner inte igen funktionen _ num-värde större än 19 eller mindre än 1 och på motsvarande sätt för alternativnummer känner den inte igen värdena större än 7 och mindre än 1, om vi tillhandahåller några andra värden ger det en #VALUE ! Fel
- Det accepterar alltid det numeriska värdet och returnerar alltid ett numeriskt värde som en utgång
- AGGREGATE i Excel har en begränsning; det ignorerar bara de dolda raderna men ignorerar inte de dolda kolumnerna.
