Een ledenlijst is een typische taak voor een klein bedrijf: tel mensen die aan een voorwaarde voldoen, bereken het gemiddelde van een groep, meet de tijd vanaf een startdatum en markeer wie aandacht nodig heeft. De vaardigheden zijn COUNTIFS, AVERAGEIF, datumrekenen en IF met AND of OR.
Plan voordat je schrijft
Schrijf elke vraag als “hoeveel”, “welk gemiddelde” of “welke”. “Hoeveel” is een telling, “welk gemiddelde” is GEMIDDELDE.ALS (Engels: AVERAGEIF), en “welke” is een markeringskolom die met ALS (Engels: IF) wordt gemaakt. Bepaal of de voorwaarden allemaal waar moeten zijn (AND) of maar één ervan (OR). Die door elkaar halen is de meest voorkomende fout.
Tellen met voorwaarden
AANTALLEN.ALS (Engels: COUNTIFS) werkt met paren: een bereik en een test. Elk paar moet waar zijn, wil een rij meetellen. Een test met een vergelijking staat tussen aanhalingstekens, zoals ">=4". Met een cel koppel je hem met &: =AANTALLEN.ALS(B2:B30;">="&F1).
GEMIDDELDE.ALS (Engels: AVERAGEIF) werkt als SOM.ALS (Engels: SUMIF), maar geeft het gemiddelde van de passende rijen terug. Als geen enkele rij past, geeft het #DIV/0! terug. Dat is een aanwijzing dat je criteriumtekst afwijkt van de gegevens, bijvoorbeeld door een extra spatie.
Datums zijn getallen
Excel slaat een datum op als aantal dagen, dus het aftrekken van twee datums geeft de dagen ertussen. Staat een startdatum in D2, dan is =VANDAAG()-D2 het aantal dagen sinds die datum. Lijkt het resultaat op een datum, verander dan de celnotatie naar Getal of Standaard.
Omdat TODAY elke dag verandert, veranderen je antwoorden ook. Gebruik voor een vast antwoord in een casestudy de datum uit de vraag, in een eigen cel.
Een klein verzonnen voorbeeld
Om te oefenen, stel je je een lijst van tien bibliotheekpassen voor. Kolom A bevat de naam van het lid, kolom B het aantal uitleningen dit jaar, kolom C het type pas (Kind of Volwassene) en kolom D de datum van uitgifte. Je kunt vragen hoeveel volwassenen minstens vijf boeken hebben geleend, wat het gemiddelde aantal uitleningen is voor kinderpassen, en welke passen ouder zijn dan twee jaar en ongebruikt.
Let op hoe elke vraag bij een hulpmiddel past: een telling met twee voorwaarden, een gemiddelde met één voorwaarde, en een markering die een datumvergelijking en een vergelijking van het aantal uitleningen nodig heeft. Schrijf die koppeling op papier voordat je begint, en controleer daarna elk resultaat met een snelle handmatige telling van de eerste paar rijen.
Markeringen en controles
Een markeringsformule moet lezen als de regel: IF (de eerste test AND de tweede test), dan één label, anders een ander label. Vul hem door de hele lijst, tel daarna de labels met AANTAL.ALS (Engels: COUNTIF) en vergelijk met een telling die je op een andere manier hebt gemaakt. De labels moeten samen het aantal leden zijn.
- Kopieer formules pas naar beneden als de bereiken $ gebruiken waar ze vast moeten blijven.
- Controleer de grenzen: telt “minstens 4” een lid met precies 4 mee?
- Zoek naar lege cellen en tekst in getalkolommen voordat je een telling vertrouwt.