Beregning av gjennomsnittet for tall som møter spesifiserte kriterier

I denne artikkelen vil vi lære å beregne gjennomsnittet for tall som møter spesifiserte kriterier i Excel.

Scenario:

Når vi jobber med data, hvor vi må få gjennomsnittet av tall som har kriterieverdier. Hvorfor kriterieverdier skaper en forskjell i resultat, bør Excel -funksjon vurdere det som null og ta resultatet. En nullverdi kan gjøre en forskjell i gjennomsnitt av tall. Gjennomsnittet av tall er summen av nødvendige tall delt på antall tall. Hvor null ikke bidrar til summen, men det øker antallet. Forstå hvordan du løser problemet.

Hvordan løse problemet?

Her må vi finne gjennomsnittet av tall med kriterier. Kriterier ignorerer kriterieverdier. Hvis du bare har positive eller negative tall, kan du bruke AVERAGEIF -funksjonen

Formelsyntaks (positive tall):

= GJENNOMSNITT (verdier, "> = 0")

Formelen ovenfor inneholder nuller, men ignorerer kriterieverdier. Hvis du vil ignorere nuller og kriterieverdier, bruker du bare kriteriene som "> 0".

På samme måte kan du få gjennomsnittet av negative verdier inkludert nuller ved å bruke formelen:

Formelsyntaks (negative tall):

= GJENNOMSNITT (verdier, "<= 0")

Formelen ovenfor inneholder nuller, men ignorerer kriterieverdier. Hvis du vil ignorere nuller og kriterieverdier, bruker du bare kriteriene som "> 0"

Men problemet med å vurdere tall som ignorerer kriterier er ikke løst. Du tror kanskje at det å ta gjennomsnittet av de to resultatene ovenfor (gjennomsnitt av positive tall og gjennomsnitt av negative tall) vil gi den nødvendige verdien. Det fungerer ikke bra før antallet positive tall er lik antallet negative tall. For dette problemet vil vi bruke AGGREGATE -funksjonen. AGGREGATE -funksjonen er pakken med matematiske funksjoner. Det hjelper generelt med å løse slike problemer. Å pakke ut denne pakken for gjennomsnittet av verdier som ignorerer kriterier. Bruk formelen nedenfor.

Formelsyntaks (for alle tall):

= AGGREGATE (1, 6, verdier)

1: Argument til gjennomsnittstall

6: ignorer kriterier

Eksempel:

Alt dette kan være forvirrende å forstå. La oss ta et eksempel og forstå hvordan du bruker disse formlene i et eksempel. Her har vi en liste over verdier som gjennomsnittet kreves for.

For det første beregner vi gjennomsnittet for de positive tallene. For dette bruker vi dataene nedenfor.

Bruk formelen:

= GJENNOMSNITT (B3: B15, "> = 0")

Forklaring

1. Kriterier er tall som er større enn eller lik null.
2. AVERAGEIF -funksjonen vurderer bare tallene som tilfredsstiller kriteriene og returnerer gjennomsnittet av tallene

Her returnerer formelen 58,6 med tanke på bare de positive verdiene der antallet positive verdier er 7. På samme måte kan du få gjennomsnittet av de negative tallene.

Bruk formelen:

= GJENNOMSNITT (B3: B15, "<= 0")

Forklaring

1. Kriterier er tall som er mindre enn eller lik null.
2. AVERAGEIF -funksjonen vurderer bare tallene som tilfredsstiller kriteriene og returnerer gjennomsnittet av tallene.

For negative tall er gjennomsnittet -21,0 inkludert nuller som ignorerer kriterieverdier. Telle til vurderte tall er 4.

Her i dette eksemplet er antall negative tall ikke lik tellingen av positive tall. Så gjennomsnittet av de to resultatene ovenfor vil ikke gi gjennomsnittet av alle tallene. Bruk formelen nedenfor for disse tilfellene.

Bruk formelen:

= AGGREGATE (1, 6, B3: B15)

Forklaring:

1. Argument 1 er gitt for å bruke gjennomsnittlig funksjon.
2. Argument 6 gis for å ignorere kriterieverdier.
3. AGGREGATE -funksjonen returnerer gjennomsnittet av alle tallene i B3: B15 -arrayet og ignorerer kriterieverdier.

Gjennomsnittet av alle tall som ignorerer alle kriterieverdier kommer ut til å være 36,2. Her er tallene 9. AGGREGATE -funksjon er den beste måten å løse disse problemene på, ettersom disse funksjonene er utmerkede innebygde funksjoner.

Hvis du prøver å få gjennomsnittet av tall ved hjelp av den grunnleggende gjennomsnittsfunksjonen, må du bruke formelen nedenfor for å få gjennomsnittet av tall som ignorerer kriterier.

Bruk formelen:

{ = GJENNOMSNITT (HVIS (IScriteria (B3: B15), "", B3: B15)) }

Merk : ikke bruk krøllbøylene manuelt. Bare bruk Ctrl + Shift + Enter i stedet for Tast inn for å få resultatet.

Forklaring:

1. IF -funksjonen sjekker tilstanden med IScriteria -funksjonen på B3: B15 -arrayet.
2. IScriteria -funksjonen returnerer True for verdien der verdien er et kriterium og False hvor ikke.
3. HVIS funksjonen returnerer blank verdi hvis verdien er kriterier og returnerer den tilsvarende verdien hvis verdien ikke er kriterier.
4. AVERAGE -funksjonen returnerer gjennomsnittet av de returnerte verdiene.
5. Krøllete seler får disse logiske funksjonene til å fungere for en matrise.

Som du kan se fungerer formelen bra og gir samme resultat. Men bruk AGGREGATE -funksjonen, den er enkel å bruke for forskjellige matematiske funksjoner med forskjellige innebygde kriterier.

Hvis du ikke har noen nullverdi i matrisen. Du kan bruke returnullet ut av alle kriterieverdiene og bruke formelen for å ta gjennomsnittet av tall som ignorerer nuller her. Men denne formelen fungerer ikke med kriterieverdier.

Her er alle observasjonsnotatene om bruk av formelen.

Merknader:

1. AGGREGATE -funksjonen er den beste funksjonen å bruke for disse problemene.
2. Funksjonen returnerer #VERDI! kriterier hvis ingen verdi samsvarer med de gitte kriteriene.
3. Ulike kriterier inne i formelen godtas, for eksempel lik (()), mindre enn (<), mindre enn eller lik (=), større enn (>) og ikke lik ().

På denne måten lærte vi å beregne gjennomsnittet for tall som møter spesifiserte kriterier i Excel. Du kan bruke disse funksjonene i Excel 2016, 2013 og 2010. Finn flere artikler om matematisk formulering med forskjellige kriterier. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post

Hvordan markere celler over og under gjennomsnittsverdi: markere verdier som er over eller under gjennomsnittsverdien ved hjelp av den betingede formateringen i Excel.

Ignorer null i gjennomsnittet av tall: beregne gjennomsnittet av tall i matrisen og ignorere nuller ved hjelp av AVERAGEIF -funksjonen i Excel.

Beregn veid gjennomsnitt: finn gjennomsnittet av verdier som har forskjellig vekt ved å bruke SUMPRODUCT -funksjonen i Excel.

Gjennomsnittlig forskjell mellom lister: beregne differansen i gjennomsnitt på to forskjellige lister. Lær mer om hvordan du beregner gjennomsnitt ved hjelp av grunnleggende matematisk gjennomsnittsformel.

Gjennomsnittlige tall hvis de ikke er tomme i Excel: ekstraher gjennomsnitt av verdier hvis cellen ikke er tom i excel.

GJENNOMSNITT av de 3 beste resultatene på en liste i excel: Finn gjennomsnittet av tall med kriterier som høyeste 3 tall fra listen i Excel

Populære artikler:

Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.

Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.

Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.

Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.