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 vil 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 referansesyntaks; det er numerisk verdi eller verdier som vi ønsker å utføre beregningen på, minst to argumenter kreves. Hvileargumenter er valgfrie.
Array: er en rekke verdier vi vil bruke; den brukes i array-syntaksen til AGGREGATE-funksjonen i excel.
K: er et valgfritt argument og numerisk verdi; den brukes når funksjonen LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC eller QUARTILE.EXC i Excel brukes.
Eksempler
Eksempel - nr. 1
Anta at vi har en liste over tall, og vi vil beregne gjennomsnittet, antallet som er antallet celler som inneholder en verdi, antall-antall celler som ikke er tomme, maksimum, minimum, produkt og sum av de gitte numeriske verdiene . Verdiene er gitt nedenfor i tabellen:
La oss først beregne gjennomsnittet i rad 9, for alle gitte verdier. For gjennomsnitt er funksjonen # 1
I kolonne C er alle verdier gitt, og vi trenger ikke å ignorere noen verdier slik at 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 til utelat 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. Når et passende alternativ brukes, gir AGGREGATE i Excel likevel gjennomsnittet av de gjenværende verdiene som forsømmer feilverdien 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, AGGREGATE i Excel, vil vi 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, er sumfunksjonen vil alltid gi den totale summen
Vi vil at når vi filtrerer kanalen, får vi summen av de synlige verdiene, så i stedet for å bruke SUM-funksjonen, vil vi bruke AGGREGATE-funksjonen for å få summen av verdiene som er synlige når et filter blir brukt.
Så ved å erstatte SUM-formelen med en AGGREGATE-funksjon med alternativkode 5 (ignorerer de skjulte radene og verdiene), har vi,
Nå, når vi bruker 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:
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 tabell inneholder noen av inntektsverdiene våre en feil, nå må vi ignorere feilene, og på samme tid, 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. For å ignorere feilene, må vi bruke alternativ 6
Ved å bruke alternativ 6 får vi summen som ignorerer feilverdiene. Likevel, når vi bruker filteret, for eksempel, filtrerer 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 alternativ 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. På samme måte, for alternativnummer, identifiserer den ikke verdiene større enn 7 og mindre enn 1; hvis vi gir andre verdier, gir det en # VERDI! 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.
