Denne opplæringen viser hvordan du bruker funksjonene Excel AVERAGEIF og AVERAGEIFS i Excel og Google Sheets til gjennomsnittlig data som oppfyller visse kriterier.
AVERAGEIF Funksjonsoversikt
Du kan bruke AVERAGEIF -funksjonen i Excel til å telle celler som inneholder en bestemt verdi, telle celler som er større enn eller lik en verdi, etc.
For å bruke AVERAGEIF Excel -regnearkfunksjonen, velg en celle og skriv:
(Legg merke til hvordan formelinngangene vises)
GJENNOMSNITT Funksjon Syntaks og argumenter:
= GJENNOMSNITT (område, kriterier, [gjennomsnittsområde])
område - Cellens rekkevidde å telle.
kriterier - Kriteriene som styrer hvilke celler som skal telles.
gjennomsnittlig_område - [valgfritt] Cellene til gjennomsnitt. Når den utelates, brukes rekkevidde.
Hva er AVERAGEIF -funksjonen?
AVERAGEIF -funksjonen er en av de eldre funksjonene som brukes i regneark. Den brukes til å skanne gjennom en rekke celler som ser etter et bestemt kriterium, og deretter gi gjennomsnittet (aka det matematiske gjennomsnittet) hvis verdiene i et område som tilsvarer disse verdiene. Den opprinnelige AVERAGEIF -funksjonen var begrenset til bare ett kriterium. Etter 2007 ble AVERAGEIFS -funksjonen opprettet som tillater en rekke kriterier. Det meste av den generelle bruken er den samme mellom de to, men det er noen kritiske forskjeller i syntaksen som vi vil diskutere gjennom denne artikkelen.
Hvis du ikke allerede har gjort det, kan du gå gjennom mye av den lignende strukturen og eksemplene i COUNTIFS -artikkelen.
Grunnleggende eksempel
La oss vurdere denne listen over registrerte salg, og vi vil vite gjennomsnittlig inntekt.
Fordi vi hadde en utgift, den negative verdien, kan vi ikke bare gjøre et grunnleggende gjennomsnitt. I stedet ønsker vi å gjennomsnittlig bare verdiene som er større enn 0. “Større enn 0” er det som vil være kriteriene våre i en AVERAGEIF -funksjon. Vår formel for å si dette er
= GJENNOMSNITT (A2: A7, "> 0")
Eksempel på to kolonner
Mens den opprinnelige AVERAGEIF -funksjonen var designet for å la deg anvende et kriterium på tallområdet du vil oppsummere, må du mye av tiden bruke et eller flere kriterier på andre kolonner. La oss vurdere denne tabellen:
Hvis vi bruker den opprinnelige AVERAGEIF -funksjonen til å finne ut hvor mange bananer vi har i gjennomsnitt. Vi legger kriteriene våre i celle D1, og vi må gi det området vi ønsker gjennomsnitt som det siste argumentet, og slik ville formelen vår være
= GJENNOMSNITT (A2: A7, D1, B2: B7)
Men da programmerere til slutt innså at brukerne ønsket å gi mer enn ett kriterium, ble AVERAGEIFS -funksjonen opprettet. For å lage en struktur som fungerer for et hvilket som helst antall kriterier, krever AVERAGEIFS at sumområdet er oppført først. I vårt eksempel betyr dette at formelen må være
= GJENNOMSNITT (B2: B7, A2: A7, D1)
MERK: Disse to formlene får samme resultat og kan se like ut, så vær nøye med hvilken funksjon som brukes for å sørge for at du lister opp alle argumentene i riktig rekkefølge.
Arbeider med datoer, flere kriterier
Når du arbeider med datoer i et regneark, mens det er mulig å legge inn datoen direkte i formelen, er det best å ha datoen i en celle, slik at du bare kan referere til cellen i en formel. Dette hjelper for eksempel datamaskinen med å vite at du vil bruke datoen 27.5.2020, og ikke tallet 5 dividert med 27 dividert med 2022.
La oss se på vår neste tabell som registrerer antall besøkende til et nettsted annenhver uke.
Vi kan spesifisere start- og sluttpunktene for området vi vil se på i D2 og E2. Vår formel for å finne gjennomsnittet antall besøkende i dette området kan være:
= GJENNOMSNITT (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Legg merke til hvordan vi var i stand til å sammenkoble sammenligningene av “=” med cellereferansene for å lage kriteriene. Selv om begge kriteriene ble brukt på det samme celleområdet (A2: A7), må du skrive ut området to ganger, en gang per hvert kriterium.
Flere kolonner
Når du bruker flere kriterier, kan du bruke dem på samme område som vi gjorde med forrige eksempel, eller du kan bruke dem på forskjellige områder. La oss kombinere eksempeldataene våre i denne tabellen:
Vi har konfigurert noen celler for brukeren å skrive inn det de vil søke etter i cellene E2 til G2. Vi trenger dermed en formel som summerer det totale antallet epler plukket i februar. Formelen vår ser slik ut:
= GJENNOMSNITT (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
GJENNOMSNITT med ELLER -logikk
Frem til dette punktet har alle eksemplene vi har brukt vært AND OG basert sammenligning, der vi leter etter rader som oppfyller alle våre kriterier. Nå skal vi vurdere saken når du vil søke etter muligheten for at en rad oppfyller et eller annet kriterium.
La oss se på denne salgslisten:
Vi vil legge til gjennomsnittlig salg for både Adam og Bob. Først en rask diskusjon om å ta gjennomsnitt. Hvis du har et ujevnt antall ting som f.eks. Har 3 oppføringer for Adam og 2 for Bob, kan du ikke bare ta gjennomsnittet av hver persons salg. Dette er kjent som å ta gjennomsnittet av gjennomsnitt, og du ender opp med å gi en urettferdig vekting av varen som har få oppføringer. Hvis dette er tilfelle med dataene dine, må du beregne et gjennomsnitt på den "manuelle" måten: ta summen av alle elementene dine dividert med antall varer. For å se hvordan du gjør dette, kan du sjekke artiklene her:
Hvis antallet oppføringer er det samme, for eksempel i tabellen vår, har du et par alternativer du kan gjøre. Det enkleste er å legge to AVERAGEIFS sammen, slik, og deretter dele med 2 (antall elementer i listen vår)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam")+AVERAGEIFS (B2: B7, A2: A7, "Bob"))/2
Her har vi fått datamaskinen til å beregne våre individuelle poengsummer, og deretter legger vi dem sammen.
Vårt neste alternativ er bra når du har flere kriterier, slik at du ikke trenger å skrive om hele formelen gjentatte ganger. I den forrige formelen fortalte vi datamaskinen manuelt å legge til to forskjellige AVERAGEIFS sammen. Du kan imidlertid også gjøre dette ved å skrive kriteriene dine i en matrise, slik:
= GJENNOMSNITT (GJENNOMSNITT (B2: B7, A2: A7, {"Adam", "Bob"}))
Se på hvordan matrisen er konstruert inne i de krøllete parentesene. Når datamaskinen evaluerer denne formelen, vil den vite at vi ønsker å beregne en AVERAGEIFS -funksjon for hvert element i matrisen vår, og dermed opprette en rekke tall. Den ytre GJENNOMSNITT -funksjonen vil deretter ta det antallet tall og gjøre det til et enkelt tall. Når vi går gjennom formelevalueringen, vil det se slik ut:
= GJENNOMSNITT (GJENNOMSNITT (B2: B7, A2: A7, {"Adam", "Bob"})) = GJENNOMSNITT (13701, 21735) = 17718
Vi får det samme resultatet, men vi klarte å skrive ut formelen litt mer kortfattet.
Å håndtere emner
Noen ganger vil datasettet ditt ha tomme celler som du enten må finne eller unngå. Å sette opp kriteriene for disse kan være litt vanskelig, så la oss se på et annet eksempel.
Vær oppmerksom på at celle A3 virkelig er tom, mens celle A5 har en formel som returnerer en streng med "" null lengde. Hvis vi vil finne det totale gjennomsnittet av virkelig tomme celler, ville vi bruke et kriterium "=", og formelen vår ville se slik ut:
= GJENNOMSNITT (B2: B7, A2: A7, "=")
På den annen side, hvis vi ønsker å få gjennomsnittet for alle celler som visuelt ser blanke ut, endrer vi kriteriene til å være "", og formelen ser ut som
= GJENNOMSNITT (B2: B7, A2: A7, "")
La oss snu det: hva om du vil finne gjennomsnittet av ikke-tomme celler? Dessverre vil den nåværende designen ikke la deg unngå null-lengden. Du kan bruke et kriterium på “”, men som du kan se i eksemplet, inneholder det fortsatt verdien fra rad 5.
= GJENNOMSNITT (B2: B7, A2: A7, "")
Hvis du ikke trenger å telle celler som inneholder strenger med null lengde, vil du vurdere å bruke LEN -funksjonen inne i et SUMPRODUCT
AVERAGEIF i Google Regneark
AVERAGEIF -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
