Deze zelfstudie laat zien hoe u de Excel AVERAGEIF- en AVERAGEIFS-functies in Excel en Google Spreadsheets kunt gebruiken om het gemiddelde te nemen van gegevens die aan bepaalde criteria voldoen.
Overzicht van AVERAGEIF-functie
U kunt de GEMIDDELDE.ALS-functie in Excel gebruiken om cellen te tellen die een specifieke waarde bevatten, cellen te tellen die groter zijn dan of gelijk zijn aan een waarde, enz.
Om de AVERAGEIF Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:
(Let op hoe de formule-invoer verschijnt)
AVERAGEIF Functiesyntaxis en argumenten:
=AVERAGEIF (bereik, criteria, [gemiddeld_bereik])
bereik - Het bereik van te tellen cellen.
criteria - De criteria die bepalen welke cellen moeten worden geteld.
gemiddelde_bereik - [optioneel] De cellen die moeten worden gemiddeld. Indien weggelaten, wordt bereik gebruikt.
Wat is de AVERAGEIF-functie?
De AVERAGEIF-functie is een van de oudere functies die in spreadsheets worden gebruikt. Het wordt gebruikt om door een reeks cellen te scannen om te controleren op een specifiek criterium en vervolgens het gemiddelde (ook bekend als het wiskundige gemiddelde) te geven als de waarden in een bereik overeenkomen met die waarden. De oorspronkelijke AVERAGEIF-functie was beperkt tot slechts één criterium. Na 2007 werd de AVERAGEIFS-functie gecreëerd die een groot aantal criteria mogelijk maakt. Het meeste algemene gebruik blijft hetzelfde tussen de twee, maar er zijn enkele kritische verschillen in de syntaxis die we in dit artikel zullen bespreken.
Als je dat nog niet hebt gedaan, kun je veel van de vergelijkbare structuur en voorbeelden bekijken in het AANTAL.ALS-artikel.
Basis voorbeeld
Laten we eens kijken naar deze lijst met geregistreerde verkopen en we willen het gemiddelde inkomen weten.
Omdat we een uitgave hadden, de negatieve waarde, kunnen we niet zomaar een basisgemiddelde maken. In plaats daarvan willen we alleen de waarden gemiddeld nemen die groter zijn dan 0. De "groter dan 0" is wat onze criteria zullen zijn in een GEMIDDELDE.ALS-functie. Onze formule om dit aan te geven is
=GEMIDDELDEALS(A2:A7, ">0")
Voorbeeld met twee kolommen
Hoewel de oorspronkelijke GEMIDDELDE.ALS-functie is ontworpen om u een criterium te laten toepassen op het bereik van getallen dat u wilt optellen, moet u vaak een of meer criteria toepassen op andere kolommen. Laten we eens kijken naar deze tabel:
Als we nu de originele AVERAGEIF-functie gebruiken om erachter te komen hoeveel bananen we gemiddeld hebben. We zullen onze criteria in cel D1 plaatsen en we moeten het bereik geven dat we willen gemiddeld als het laatste argument, en dus zou onze formule zijn
=GEMIDDELDEALS(A2:A7, D1, B2:B7)
Toen programmeurs zich uiteindelijk realiseerden dat gebruikers meer dan één criterium wilden geven, werd de functie AVERAGEIFS gemaakt. Om één structuur te creëren die voor een willekeurig aantal criteria zou werken, vereist de AVERAGEIFS dat het sombereik eerst wordt vermeld. In ons voorbeeld betekent dit dat de formule moet zijn
=GEMIDDELDE.ALS(B2:B7, A2:A7, D1)
OPMERKING: Deze twee formules krijgen hetzelfde resultaat en kunnen er hetzelfde uitzien, dus let goed op welke functie wordt gebruikt om ervoor te zorgen dat u alle argumenten in de juiste volgorde opsomt.
Werken met datums, meerdere criteria
Wanneer u met datums in een spreadsheet werkt, is het weliswaar mogelijk om de datum rechtstreeks in de formule in te voeren, maar het is het beste om de datum in een cel te plaatsen, zodat u alleen naar de cel in een formule kunt verwijzen. Dit helpt de computer bijvoorbeeld om te weten dat u de datum 27-5-2020 wilt gebruiken, en niet het getal 5 gedeeld door 27 gedeeld door 2022.
Laten we eens kijken naar onze volgende tabel waarin het aantal bezoekers van een site elke twee weken wordt geregistreerd.
We kunnen het begin- en eindpunt specificeren van het bereik dat we willen bekijken in D2 en E2. Onze formule om het gemiddelde aantal bezoekers in dit bereik te vinden, zou dan kunnen zijn:
=GEMIDDELDE.ALS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2)
Merk op hoe we de vergelijkingen van "=" met de celverwijzingen konden samenvoegen om de criteria te creëren. Ook al werden beide criteria toegepast op hetzelfde celbereik (A2:A7), je moet het bereik twee keer uitschrijven, één keer per criterium.
Meerdere kolommen
Wanneer u meerdere criteria gebruikt, kunt u ze toepassen op hetzelfde bereik als in het vorige voorbeeld, of u kunt ze toepassen op verschillende bereiken. Laten we onze voorbeeldgegevens combineren in deze tabel:
We hebben enkele cellen ingesteld zodat de gebruiker kan invoeren waarnaar hij wil zoeken in de cellen E2 tot en met G2. We hebben dus een formule nodig die het totaal aantal geplukte appels in februari optelt. Onze formule ziet er als volgt uit:
=GEMIDDELDE.ALS(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2)
AVERAGEIFS met logica van het OR-type
Tot nu toe waren de voorbeelden die we hebben gebruikt allemaal op EN gebaseerde vergelijking, waarbij we op zoek zijn naar rijen die aan al onze criteria voldoen. Nu zullen we het geval bekijken wanneer u wilt zoeken naar de mogelijkheid dat een rij aan een of ander criterium voldoet.
Laten we eens kijken naar deze lijst met verkopen:
We willen de gemiddelde verkoop voor zowel Adam als Bob bij elkaar optellen. Eerst een korte discussie over het nemen van gemiddelden. Als je een oneven aantal dingen hebt, zoals 3 vermeldingen voor Adam en 2 voor Bob, kun je niet zomaar het gemiddelde nemen van de verkopen van elke persoon. Dit staat bekend als het gemiddelde nemen van gemiddelden, en u geeft uiteindelijk een oneerlijke weging aan het item met weinig items. Als dit het geval is met uw gegevens, moet u een gemiddelde berekenen op de "handmatige" manier: neem de som van al uw artikelen gedeeld door het aantal artikelen. Om te zien hoe u dit kunt doen, kunt u de artikelen hier bekijken:
Nu, als het aantal items hetzelfde is, zoals in onze tabel, dan heb je een aantal opties die je kunt doen. Het eenvoudigst is om twee AVERAGEIFS bij elkaar op te tellen, zoals zo, en dan te delen door 2 (het aantal items in onze lijst)
=(GEMIDDELDE B.F.(B2:B7, A2:A7, "Adam")+GEMIDDELDE B.F.(B2:B7, A2:A7, "Bob"))/2
Hier hebben we de computer onze individuele scores laten berekenen en deze vervolgens bij elkaar optellen.
Onze volgende optie is goed voor wanneer u meer criteriabereiken heeft, zodat u niet de hele formule herhaaldelijk wilt herschrijven. In de vorige formule vertelden we de computer handmatig om twee verschillende AVERAGEIFS bij elkaar op te tellen. U kunt dit echter ook doen door uw criteria in een array te schrijven, zoals deze:
=GEMIDDELDE(GEMIDDELDEIFS(B2:B7, A2:A7, {"Adam", "Bob"}))
Kijk hoe de array is opgebouwd tussen de accolades. Wanneer de computer deze formule evalueert, weet hij dat we een AVERAGEIFS-functie willen berekenen voor elk item in onze array, waardoor een array van getallen ontstaat. De buitenste GEMIDDELDE functie zal dan die reeks getallen nemen en er een enkel getal van maken. Als u de formule-evaluatie doorloopt, ziet het er als volgt uit:
=GEMIDDELDE(GEMIDDELDE.ALS(B2:B7, A2:A7, {"Adam", "Bob"})) =GEMIDDELDE(13701, 21735) =17718
We krijgen hetzelfde resultaat, maar we hebben de formule wat beknopter kunnen uitschrijven.
Omgaan met lege plekken
Soms bevat uw gegevensset lege cellen die u moet zoeken of vermijden. Het instellen van de criteria hiervoor kan een beetje lastig zijn, dus laten we naar een ander voorbeeld kijken.
Merk op dat cel A3 echt leeg is, terwijl cel A5 een formule heeft die een tekenreeks met lengte nul van "" retourneert. Als we het totale gemiddelde willen vinden van werkelijk lege cellen, we zouden een criterium van "=" gebruiken en onze formule zou er als volgt uitzien:
=GEMIDDELDE.IF(B2:B7,A2:A7,"=")
Aan de andere kant, als we het gemiddelde willen krijgen voor alle cellen die er visueel blanco uitzien, veranderen we de criteria in "", en de formule ziet eruit als
=GEMIDDELDE.ALS(B2:B7,A2:A7,"")
Laten we het omdraaien: wat als u het gemiddelde van niet-lege cellen wilt vinden? Helaas kunt u met het huidige ontwerp de string met de lengte nul niet vermijden. U kunt een criterium van "" gebruiken, maar zoals u in het voorbeeld kunt zien, bevat het nog steeds de waarde uit rij 5.
=GEMIDDELDE.ALS(B2:B7,A2:A7,"")
Als u cellen met tekenreeksen met een lengte van nul niet wilt tellen, kunt u overwegen de LEN-functie in een SOMPRODUCT te gebruiken
AVERAGEIF in Google Spreadsheets
De AVERAGEIF-functie werkt precies hetzelfde in Google Spreadsheets als in Excel:
