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, mens man bruger referencesyntaxen, det er en numerisk værdi eller værdier, som vi ønsker at udføre beregningen på, mindst to argumenter kræves, hvile argumenter er valgfri.
Array: er en matrix af værdier, som vi ønsker at udføre operationen på, den bruges i array-syntaks for AGGREGATE-funktionen i excel
K: er et valgfrit argument og er en numerisk værdi, det bruges, når funktionen som STOR, Lille, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel bruges.
Eksempler
Du kan downloade denne AGGREGATE-funktion Excel-skabelon her - AGGREGATE-funktion Excel-skabelonEksempel - nr. 1
Antag, at vi har en liste over tal, og at vi beregner gennemsnittet, antallet, der er antallet af celler, der indeholder en værdi, antallet - 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
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, så vi vil udelade værdien af k.
Så den gennemsnitlige værdi er
Tilsvarende for rækkevidde D1: D8 vil vi igen vælge mulighed 4.
For rækkevidde E1: E8 indeholder en celle E6 en fejlværdi, hvis vi bruger den samme AGGREGATE-formel, får vi en fejl, men når en passende mulighed bruges, giver AGGREGATE i Excel gennemsnittet af de resterende værdier, der forsømmer fejlen værdi i E6.
For at ignorere fejlværdierne har vi mulighed 6.
Til rækkevidde G1: G8 vil vi ligeledes 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, vil AGGREGATE i Excel kun give 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 genererede indtægt, 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, vil sumfunktionen altid give den samlede sum
Vi ønsker, at når vi filtrerer kanalen, får vi summen af de værdier, der er synlige, så i stedet for at bruge SUM-funktionen bruger vi AGGREGATE-funktionen for at få summen af de værdier, der er synlige, når et filter er anvendt.
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 på 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:
Så vi kan se, at funktionen AGGREGATE beregner de forskellige sumværdier for indtjeningen, der genereres for forskellige kanaler, når de er filtreret. Så AGGREGATE-funktionen kan bruges dynamisk til udskiftning af forskellige funktioner til forskellige forhold uden at bruge den betingede formel.
Antag, at for den samme tabelkanal og indtjening indeholder nogle af vores indtægtsværdier 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 nu for at ignorere de fejl, vi skal bruge mulighed 6
Ved hjælp af mulighed 6 får vi summen, der ignorerer fejlværdierne, men når vi anvender filteret, for eksempel filtrerer vi efter kanalværdi Direkte, får vi den samme sum, idet vi ignorerer fejlene, men samtidig skal vi også ignorere de skjulte værdier.
Så i dette tilfælde bruger vi mulighed 7, der ignorerer fejlværdierne og samtidig de skjulte rækker
Ting at huske
- AGGREGATE-funktionen genkender ikke funktionen _ num-værdi større end 19 eller mindre end 1 og på samme måde for Option-nummer genkender den ikke værdier større end 7 og mindre end 1, hvis vi giver andre værdier, giver den en #VALUE ! 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.