Denne vejledning viser, hvordan du bruger Excel AVERAGEIF og AVERAGEIFS -funktionerne i Excel og Google Sheets til gennemsnitlige data, der opfylder visse kriterier.
AVERAGEIF Funktionsoversigt
Du kan bruge AVERAGEIF -funktionen i Excel til at tælle celler, der indeholder en bestemt værdi, tælle celler, der er større end eller lig med en værdi osv.
Hvis du vil bruge AVERAGEIF Excel -regnearksfunktionen, skal du vælge en celle og skrive:
(Bemærk hvordan formelindgangene vises)
AVERAGEIF Funktionssyntaks og argumenter:
= GENNEMSNIT (område, kriterier, [gennemsnitsområde])
rækkevidde - Cellernes rækkevidde at tælle.
kriterier - Kriterierne, der styrer, hvilke celler der skal tælles.
gennemsnitsområde - [valgfrit] Cellerne til gennemsnit. Når den udelades, bruges område.
Hvad er AVERAGEIF -funktionen?
AVERAGEIF -funktionen er en af de ældre funktioner, der bruges i regneark. Det bruges til at scanne gennem en række celler, der kontrollerer et specifikt kriterium, og derefter give gennemsnittet (aka det matematiske middel), hvis værdierne i et område, der svarer til disse værdier. Den originale AVERAGEIF -funktion var begrænset til kun et kriterium. Efter 2007 blev AVERAGEIFS -funktionen oprettet, som tillader en lang række kriterier. Det meste af den generelle brug forbliver den samme mellem de to, men der er nogle kritiske forskelle i syntaksen, som vi vil diskutere i hele denne artikel.
Hvis du ikke allerede har gjort det, kan du gennemgå meget af den lignende struktur og eksempler i COUNTIFS -artiklen.
Grundlæggende eksempel
Lad os overveje denne liste over registrerede salg, og vi vil gerne kende den gennemsnitlige indkomst.
Fordi vi havde en udgift, den negative værdi, kan vi ikke bare lave et grundgennemsnit. I stedet ønsker vi kun at gennemsnitsværdier, der er større end 0. "Større end 0" er, hvad der vil være vores kriterier i en GENNEMGANGIF -funktion. Vores formel til at angive dette er
= GENNEMSNIT (A2: A7, "> 0")
Eksempel på to kolonner
Mens den originale AVERAGEIF -funktion var designet til at lade dig anvende et kriterium på det talinterval, du vil summere, skal du meget af tiden anvende et eller flere kriterier på andre kolonner. Lad os overveje denne tabel:
Hvis vi nu bruger den originale AVERAGEIF -funktion til at finde ud af, hvor mange bananer vi har i gennemsnit. Vi sætter vores kriterier i celle D1, og vi skal give det område, vi vil gennemsnit som det sidste argument, og sådan ville vores formel være
= GENNEMSNIT (A2: A7, D1, B2: B7)
Da programmerere til sidst indså, at brugerne ønskede at give mere end et kriterium, blev funktionen AVERAGEIFS oprettet. For at oprette en struktur, der fungerer for et vilkårligt antal kriterier, kræver AVERAGEIFS, at sumintervallet er angivet først. I vores eksempel betyder dette, at formlen skal være
= GENNEMSNIT (B2: B7, A2: A7, D1)
BEMÆRK: Disse to formler får det samme resultat og kan ligne hinanden, så vær meget opmærksom på, hvilken funktion der bruges til at sikre, at du angiver alle argumenterne i den korrekte rækkefølge.
Arbejde med datoer, flere kriterier
Når du arbejder med datoer i et regneark, mens det er muligt at indtaste datoen direkte i formlen, er det bedste fremgangsmåde at have datoen i en celle, så du bare kan referere til cellen i en formel. For eksempel hjælper dette computeren med at vide, at du vil bruge datoen 27.5.2020 og ikke tallet 5 divideret med 27 divideret med 2022.
Lad os se på vores næste tabel, der registrerer antallet af besøgende på et websted hver anden uge.
Vi kan angive start- og slutpunkter for det område, vi vil se på i D2 og E2. Vores formel for derefter at finde det gennemsnitlige antal besøgende i dette område kan være:
= GENNEMSNITIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Bemærk, hvordan vi var i stand til at sammenkoble sammenligningerne af “=” med cellereferencerne for at oprette kriterierne. Selvom begge kriterier blev anvendt på det samme område af celler (A2: A7), skal du skrive området to gange ud, én gang pr. Kriterium.
Flere kolonner
Når du bruger flere kriterier, kan du anvende dem på det samme område, som vi gjorde med tidligere eksempel, eller du kan anvende dem på forskellige intervaller. Lad os kombinere vores eksempeldata i denne tabel:
Vi har opsat nogle celler, så brugeren kan indtaste det, han vil søge efter i cellerne E2 til G2. Vi har derfor brug for en formel, der summerer det samlede antal æbler, der blev plukket i februar. Vores formel ser sådan ud:
= GENNEMSNITIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
GENNEMGANGSMÆNGDE med OR -logik
Indtil dette tidspunkt har de eksempler, vi har brugt, alle været OG -baseret sammenligning, hvor vi leder efter rækker, der opfylder alle vores kriterier. Nu overvejer vi sagen, når du vil søge efter muligheden for, at en række opfylder et eller andet kriterium.
Lad os se på denne salgsliste:
Vi vil gerne lægge det gennemsnitlige salg til både Adam og Bob. Først en hurtig diskussion om at tage gennemsnit. Hvis du har et ujævnt antal ting som f.eks. Har 3 poster for Adam og 2 for Bob, kan du ikke bare tage gennemsnittet af hver persons salg. Dette er kendt som at tage gennemsnittet af gennemsnit, og du ender med at give en uretfærdig vægtning til den vare, der har få poster. Hvis dette er tilfældet med dine data, skal du beregne et gennemsnit på den "manuelle" måde: tag summen af alle dine varer divideret med antallet af dine varer. For at gennemgå, hvordan du gør dette, kan du tjekke artiklerne her:
Hvis antallet af poster nu er det samme, som i vores tabel, har du et par muligheder, du kan gøre. Det enkleste er at tilføje to AVERAGEIFS sammen, sådan, og derefter dividere med 2 (antallet af elementer på vores liste)
= (Gennemsnit (B2: B7, A2: A7, "Adam")+Gennemsnit (B2: B7, A2: A7, "Bob"))/2
Her har vi fået computeren til at beregne vores individuelle scores, og derefter tilføjer vi dem sammen.
Vores næste mulighed er god til, når du har flere kriterier, således at du ikke ønsker at skulle omskrive hele formlen gentagne gange. I den foregående formel fortalte vi manuelt computeren at tilføje to forskellige AVERAGEIFS sammen. Du kan dog også gøre dette ved at skrive dine kriterier inde i en matrix, sådan her:
= Gennemsnit (GENNEMSNIT (B2: B7, A2: A7, {"Adam", "Bob"}))
Se hvordan matrixen er opbygget inde i de krøllede beslag. Når computeren evaluerer denne formel, ved den, at vi ønsker at beregne en AVERAGEIFS -funktion for hvert element i vores array og dermed oprette et array med tal. Den ydre GENNEMSNIT -funktion vil derefter tage den række tal og gøre det til et enkelt tal. Når man går gennem formelevalueringen, ser det sådan ud:
= Gennemsnit (GENNEMSNIT (B2: B7, A2: A7, {"Adam", "Bob"})) = Gennemsnit (13701, 21735) = 17718
Vi får det samme resultat, men vi var i stand til at skrive formlen lidt mere kortfattet ud.
Beskæftiger sig med emner
Nogle gange vil dit datasæt have tomme celler, som du enten skal finde eller undgå. Opsætning af kriterierne for disse kan være lidt vanskelig, så lad os se på et andet eksempel.
Bemærk, at celle A3 virkelig er tom, mens celle A5 har en formel, der returnerer en nul-længde streng med "". Hvis vi vil finde det samlede gennemsnit af virkelig tomme celler, ville vi bruge et kriterium "=", og vores formel ville se sådan ud:
= GENNEMSNIT (B2: B7, A2: A7, "=")
På den anden side, hvis vi ønsker at få gennemsnittet for alle celler, der visuelt ser tomme ud, ændrer vi kriterierne til at være "", og formlen ligner
= GENNEMSNIT (B2: B7, A2: A7, "")
Lad os vende det rundt: hvad nu hvis du vil finde gennemsnittet af ikke-tomme celler? Desværre vil det nuværende design ikke lade dig undgå nullængden. Du kan bruge et kriterium "", men som du kan se i eksemplet, indeholder det stadig værdien fra række 5.
= GENNEMSNIT (B2: B7, A2: A7, "")
Hvis du ikke skal tælle celler, der indeholder strenge med nul længde, vil du overveje at bruge LEN -funktionen inde i et SUMPRODUCT
AVERAGEIF i Google Sheets
AVERAGEIF -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
