AGGREGATE-funktion i Excel
AGGREGATE-funktion 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 datasatserna, 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 nummer 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 under beräkningarna
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å, minst två argument krävs. Vilaargument är valfria.
Array: är en rad värden som vi vill använda; den används i array-syntax för AGGREGATE-funktionen i excel.
K: är ett valfritt argument och numeriskt värde; den används när funktionen som LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel används.
Exempel
Exempel - # 1
Antag att vi har en lista med siffror och att vi kommer att beräkna medelvärdet, räkningen som är antalet celler som innehåller ett värde, counta -antalet celler som inte är tomma, maximalt, minimum, 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. I genomsnitt är funktionen_ num 1
I kolumn C ges alla värden och vi behöver inte ignorera några värden så att vi väljer alternativ 4 (ignorerar ingenting)
Och välja värdeintervallet 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 till utelämna värdet på k.
Så det genomsnittliga värdet är
På samma sätt väljer vi alternativ 4 för intervall D1: D8.
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. Fortfarande, när ett lämpligt alternativ används, ger AGGREGATE i Excel genomsnittet av de återstående värdena som försummar felvärdet 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 ett värde 64 och döljer den tredje raden och använder alternativ 5, för att ignorera den dolda raden, AGGREGATE i Excel, ger vi endast medelvärdet för synliga numeriska värden.
Output utan att dölja rad 3
Output 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 använder filter i excel för samma, är summan kommer alltid att ge den totala summan
Vi vill att när vi filtrerar kanalen får vi summan av de synliga värdena, så istället för att använda SUM-funktionen kommer vi att använda AGGREGATE-funktionen för att få summan av de värden som är synliga när ett filter appliceras.
Så när vi ersätter SUM-formeln med en AGGREGATE-funktion med alternativkod 5 (ignorerar de dolda raderna och värdena) har vi,
När vi nu använder filtret för olika kanaler visar det 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:
Vi kan se 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 dynamiskt användas 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 tabell innehåller några av våra intäktsvärden ett fel, nu måste vi ignorera felen, och samtidigt, om vi vill använda 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. För att ignorera felen måste vi använda alternativ 6
Med alternativ 6 får vi summan som ignorerar felvärdena. Fortfarande, när vi tillämpar filtret, till exempel filter 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 alternativ 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 som är större än 19 eller mindre än 1. På samma sätt identifierar det inte för alternativnummer värden som är större än 7 och mindre än 1; om vi tillhandahåller andra värden ger det en #VÄRDE! 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.
