AGGREGATE Funksjon i Excel
AGGREGATE Funksjon i excel returnerer aggregatet til en gitt datatabell eller datalister, denne funksjonen har også det første argumentet som funksjonsnummer og ytterligere argumenter er for en rekke datasett, funksjonsnummeret skal huskes for å vite hvilken funksjon du skal bruke .
Syntaks
Det er to syntakser for AGGREGATE Formula:
- Referansesyntaks
= AGGREGATE (funksjonsnummer, opsjoner, ref1, ref2, ref [3],…)
- Array Syntax
= AGGREGATE (funksjonsnummer, opsjoner, matrise, [k])
Function_num er et tall som betegner en bestemt funksjon som vi ønsker å bruke, det er et tall fra 1-19
Alternativ: det er også en numerisk verdi fra 0 til 7 og bestemmer hvilke verdier som skal ignoreres under beregningene
Ref1, ref2, ref [3]: er argumentet mens du bruker referanse-syntaksen, det er numerisk verdi eller verdier som vi ønsker å utføre beregningen på, minst to argumenter kreves hvile-argumenter er valgfrie.
Array: er en rekke verdier som vi ønsker å utføre operasjonen på, den brukes i array-syntaksen til AGGREGATE-funksjonen i Excel
K: er et valgfritt argument og er en numerisk verdi, den brukes når funksjonen LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel brukes.
Eksempler
Du kan laste ned denne AGGREGATE-funksjonen Excel-mal her - AGGREGATE-funksjonen Excel-malEksempel - nr. 1
Anta at vi har en liste over tall, og vi vil beregne gjennomsnittet, antall som er antall celler som inneholder en verdi, antall - antall celler som ikke er tomme, maksimum, minimum, produkt og sum av de angitte numeriske verdiene. Verdiene er gitt nedenfor i tabellen:
La oss først beregne gjennomsnittet i rad 9 for alle gitte verdier. For gjennomsnittet er funksjonen_nummer
I kolonne C er alle verdier gitt, og vi trenger ikke å ignorere noen verdier, så vi velger alternativ 4 (ignorerer ingenting)
Og velge verdiområdet C1: C8 som en rekke numeriske verdier
Siden ' k' er et valgfritt argument og brukes når en funksjon som STOR, LITEN i Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC brukes, men i dette tilfellet beregner vi gjennomsnittet slik at vi vil utelate verdien av k.
Så den gjennomsnittlige verdien er
På samme måte, for område D1: D8, vil vi igjen velge alternativ 4.
For område E1: E8 inneholder en celle E6 en feilverdi. Hvis vi bruker den samme AGGREGATE-formelen, får vi en feil, men når et passende alternativ brukes, gir AGGREGATE i Excel gjennomsnittet av de gjenværende verdiene som neglisjerer feilen verdi i E6.
For å ignorere feilverdiene har vi alternativ 6.
På samme måte, for område G1: G8, vil vi bruke alternativet 6 (ignorere feilverdiene)
Nå, for område H3 hvis vi setter en verdi 64, og skjuler den tredje raden og bruker alternativ 5, for å ignorere den skjulte raden, vil AGGREGATE i Excel bare gi gjennomsnittsverdien for synlige numeriske verdier.
Utgang uten å skjule rad 3
Utgang etter skjuling av rad 3
Vi bruker AGGREGATE-formelen for andre operasjoner
Eksempel - nr. 2
Anta at vi har en tabell for inntektene som genereres på forskjellige datoer fra de forskjellige kanalene, som gitt nedenfor
Nå vil vi sjekke inntektene som genereres for forskjellige kanaler. Så når vi bruker sumfunksjonen, får vi den totale inntekten som genereres, men hvis vi vil sjekke inntektene som genereres for organisk kanal eller direkte kanal eller andre, når vi bruker filtre i excel for det samme, vil sumfunksjonen alltid gi totalsummen
Vi vil at når vi filtrerer kanalen, får vi summen av verdiene som er synlige, så i stedet for å bruke SUM-funksjonen, vil vi bruke AGGREGATE-funksjonen for å få summen av verdiene som er synlige når et filter er anvendt.
Så å erstatte SUM-formelen med en AGGREGATE-funksjon med alternativkode 5 (ignorerer de skjulte radene og verdiene) vi har,
Nå, når vi skal bruke filteret på forskjellige kanaler, vil det bare vise inntektene for den kanalen når resten av radene blir skjult.
Total inntekt generert for direkte kanal:
Total inntekt generert for organisk kanal:
Total inntekt generert for betalt kanal:
Så vi kan se at AGGREGATE-funksjonen beregner de forskjellige sumverdiene for inntektene som genereres for forskjellige kanaler når de er filtrert. Så, AGGREGATE-funksjonen kan brukes dynamisk til å erstatte forskjellige funksjoner for forskjellige forhold uten å bruke den betingede formelen.
Anta at for samme tabellkanal og inntekt, inneholder noen av inntektsverdiene våre en feil. Nå må vi ignorere feilene, og samtidig, hvis vi vil bruke et filter, bør AGGREGATE-funksjonen også ignorere de skjulte radverdiene.
Når vi bruker alternativ 5, får vi feilen for SUM av den totale inntekten, nå for å ignorere feilene vi må bruke alternativ 6
Ved å bruke alternativ 6 får vi summen som ignorerer feilverdiene, men når vi bruker filteret, for eksempel, filtrerer vi etter kanalverdi Direkte får vi den samme summen som ignorerer feilene, men samtidig må vi også ignorere de skjulte verdiene.
Så i dette tilfellet vil vi bruke alternativet 7 som ignorerer feilverdiene og samtidig de skjulte radene
Ting å huske
- AGGREGATE-funksjonen gjenkjenner ikke funksjonen _ num-verdi større enn 19 eller mindre enn 1, og på samme måte for alternativnummer gjenkjenner den ikke verdiene større enn 7 og mindre enn 1, hvis vi gir andre verdier gir den en #VALUE ! Feil
- Den aksepterer alltid den numeriske verdien og returnerer alltid en numerisk verdi som en utgang
- AGGREGATE i Excel har en begrensning; den ignorerer bare de skjulte radene, men ignorerer ikke de skjulte kolonnene.