AGGREGATE-funktion i Excel
AGGREGATE-funktion i excel returnerer aggregatet for en given datatabel eller datalister, denne funktion har også det første argument som funktionsnummer, og yderligere argumenter er for en række datasæt, funktionsnummeret skal huskes for at vide, hvilken funktion der skal bruges .
Syntaks
Der er to syntakser for AGGREGATE Formula:
- Reference syntaks
= AGGREGATE (funktionsnummer, optioner, ref1, ref2, ref (3), …)
- Array-syntaks
= AGGREGATE (funktionsnummer, optioner, array, (k))
Function_num er et tal, der angiver en bestemt funktion, som vi vil bruge; det er et tal fra 1-19
Valgmulighed: det er også en numerisk værdi fra 0 til 7 og bestemmer, hvilke værdier der skal ignoreres under beregningerne
Ref1, ref2, ref (3): er argumentet under brug af referencesyntaxen; det er en numerisk værdi eller værdier, som vi ønsker at udføre beregningen på, mindst to argumenter kræves. Hvileargumenter er valgfri.
Array: er en række værdier, som vi ønsker at bruge; det bruges i array-syntaks for AGGREGATE-funktionen i excel.
K: er et valgfrit argument og en numerisk værdi; det bruges, når funktionen som LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel bruges.
Eksempler
Eksempel - nr. 1
Antag, at vi har en liste med tal, og vi beregner gennemsnittet, antallet, der er antallet af celler, der indeholder en værdi, antallet af celler, der ikke er tomme, maksimum, minimum, produkt og sum af de givne numeriske værdier . Værdier er angivet nedenfor i tabellen:
Lad os først beregne gennemsnittet i række 9 for alle givne værdier. For gennemsnit er funktionen_ num 1
I kolonne C er alle værdier angivet, og vi behøver ikke at ignorere nogen værdier, så vi vælger mulighed 4 (ignorerer intet)
Og at vælge værdiområdet C1: C8 som en række numeriske værdier
Da ' k' er et valgfrit argument og bruges, når en funktion som LARGE, SMALL i Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC bruges, men i dette tilfælde beregner vi gennemsnittet til udelad værdien af k.
Så den gennemsnitlige værdi er
På samme måde vælger vi igen rækkevidde D1: D8 mulighed 4.
For område E1: E8 indeholder en celle E6 en fejlværdi. Hvis vi bruger den samme AGGREGATE-formel, får vi en fejl. Når en passende indstilling bruges, giver AGGREGATE i Excel stadig gennemsnittet af de resterende værdier, der forsømmer fejlværdien i E6.
For at ignorere fejlværdierne har vi mulighed 6.
Tilsvarende for rækkevidde G1: G8 vil vi bruge mulighed 6 (ignorere fejlværdierne)
Nu, for rækkevidde H3, hvis vi lægger en værdi 64 og skjuler den tredje række og bruger mulighed 5, for at ignorere den skjulte række, AGGREGATE i Excel, giver vi kun den gennemsnitlige værdi for synlige numeriske værdier.
Output uden at skjule række 3
Output efter at have skjult række 3
Vi anvender AGGREGATE-formlen til andre operationer
Eksempel - nr. 2
Antag, at vi har en tabel over indtjeningen på forskellige datoer fra de forskellige kanaler som angivet nedenfor
Nu vil vi kontrollere indtægterne fra forskellige kanaler. Så når vi anvender sumfunktionen, får vi den samlede indtjening, men hvis vi vil kontrollere indtægterne genereret til organisk kanal eller direkte kanal eller andre, når vi anvender filtre i excel for det samme, er sumfunktionen vil altid give det samlede beløb
Vi ønsker, at når vi filtrerer kanalen, får vi summen af de synlige værdier, så i stedet for at bruge SUM-funktionen bruger vi AGGREGATE-funktionen til at få summen af de værdier, der er synlige, når et filter anvendes.
Så ved at erstatte SUM-formlen med en AGGREGATE-funktion med optionskode 5 (ignorerer de skjulte rækker og værdier) har vi,
Når vi nu anvender filteret til forskellige kanaler, viser det kun indtægterne for den kanal, når resten af rækkerne bliver skjult.
Samlet omsætning genereret til direkte kanal:
Samlet omsætning genereret for organisk kanal:
Samlet omsætning genereret for betalt kanal:
Vi kan se, at AGGREGATE-funktionen beregner de forskellige sumværdier for indtjeningen, der genereres for forskellige kanaler, når de først er filtreret. Så AGGREGATE-funktionen kan bruges dynamisk til at erstatte forskellige funktioner under forskellige forhold uden at bruge den betingede formel.
Antag for den samme tabel, at nogle af vores indtægtsværdier indeholder en fejl, nu skal vi ignorere fejlene, og på samme tid, hvis vi ønsker at anvende et filter, skal AGGREGATE-funktionen også ignorere de skjulte rækkeværdier.
Når vi bruger mulighed 5, får vi fejlen for SUM af den samlede omsætning. For at ignorere fejlene skal vi bruge mulighed 6
Ved hjælp af mulighed 6 får vi summen, der ignorerer fejlværdierne. Stadig når vi anvender filteret, for eksempel, filtrer efter kanalværdi Direkte, får vi den samme sum, idet vi ignorerer fejlene, men på samme tid er vi også nødt til at ignorere de skjulte værdier.
Så i dette tilfælde bruger vi mulighed 7, der ignorerer fejlværdierne og på samme tid de skjulte rækker.
Ting at huske
- Funktionen AGGREGATE genkender ikke funktionen _ num-værdi, der er større end 19 eller mindre end 1. Tilsvarende identificerer den ikke for Option-nummer de værdier, der er større end 7 og mindre end 1; hvis vi giver andre værdier, giver det en #VÆRDI! Fejl
- Det accepterer altid den numeriske værdi og returnerer altid en numerisk værdi som output
- AGGREGATE i Excel har en begrænsning; det ignorerer kun de skjulte rækker, men ignorerer ikke de skjulte kolonner.
