Excel kent meerdere database functies. In dit artikel leer je de belangrijkste, namelijk: DBSOM, DBGEMIDDELDE, DBAANTAL, DBAANTALC, DBMAX, DBMIN en DBPRODUCT.
Deze functies lijken op bekende Excel functies die je wellicht al kent, zoals SOM, GEMIDDELDE en MAX. Sommige van deze functies hebben ook een variant waarbij je gebruik kunt maken van een voorwaarde, zoals SOM.ALS of GEMIDDELDE.ALS.
Bij databasefuncties werk je met een gegevensbereik dat ook wel een database wordt genoemd. Dit lijkt op het ‘bereik’ argument dat je gebruikt bij gewone functies. Het verschil is dat je bij databasefuncties daarnaast gebruik maakt van een aparte criteriatabel.
Deze tabel bevat kolomnamen die overeenkomen met de kopteksten in je gegevens. Hierdoor is het mogelijk om te filteren op één of meerdere voorwaarden tegelijk.
Dit klinkt misschien wat ingewikkeld. Dat had ik in eerste instantie ook, waardoor ik de functies lange tijd naast mij neerlegde. Maar als je eenmaal door hebt hoe het werkt, zijn het juiste hele krachtige functies en is het helemaal niet zo lastig. In dit artikel leer je stap voor stap aan de hand van eenvoudige voorbeelden hoe deze functies werken.
De database
Om een databasefunctie te gebruiken heb je een database nodig. Dit is simpelweg een overzichtelijke tabel met gegevens.
Je gegevens hoeven niet officieel als een ’tabel’ te zijn opgemaakt via het lint (invoegen > tabel). Het enige wat van belang is, is dat de eerste rij kopteksten bevat.
Hieronder zie je een voorbeeld van een eenvoudige database waarmee we in dit artikel de functies gaan uitproberen.
De gegevens staan in de cellen B2:F7. Dit bereik is de database voor de databasefuncties.
- De bovenste rij bevat duidelijke ‘veldnamen’. In dit geval ‘datum’, ‘productsoort’, ‘aantal’, ‘prijs per stuk’ en ’totaalprijs’.
- Wanneer je de databasefuncties gebruikt, moet je altijd het volledige bereik selecteren, dus inclusief kopteksten.
Wat is een criteriatabel?
Je hebt voor deze functies ook een criteriatabel nodig. Hierin kun je opgeven waar je op wilt filteren. Het is hierbij van belang dat de kolomnamen in deze tabel precies overeenkomen met de kolomnamen uit je databron.
In onderstaand voorbeeld heb ik naast de database een criteriatabel gemaakt in het bereik H2:I3.
Dit betekent dat Excel alle rijen moet filteren waar de productsoort gelijk is aan ‘bank’ EN het aantal groter is dan 1.
De database functies, die we hierna gaan behandelen, zullen vervolgens alleen berekeningen uitvoeren op de rijen in de database die aan deze voorwaarden voldoen.
- Je kunt gerust één kolom als criterium gebruiken. Bijvoorbeeld alleen ‘Productsoort’.
- Je kunt ook meerdere regels onder elkaar gebruiken, om een soort ‘OF filter’ te maken. Bijvoorbeeld ‘Bank’ of ‘Tafel’. Dit zal ook worden laat zien in de voorbeelden.
1. Functie DBSOM
We beginnen met de functie DBSOM. Deze functie gaan we gebruiken om de totaalprijs op te tellen van producten uit de database, maar dan alleen rijen die voldoen aan de voorwaarden uit de criteriatabel.
De schrijfwijze van de formule is als volgt:
=DBSOM(database;veld;criteria)
- Database – dit is het bereik met je gegevens, inclusief de kopteksten.
- Veld – de naam van de kolom waarin je wilt optellen, bijvoorbeeld ’totaalprijs’.
- Criteria – het bereik met je voorwaarden, inclusief de kopteksten.
In cel C10 staat bijvoorbeeld de volgende formule:
=DBSOM(B2:F7;"Totaalprijs";H2:I3)
In deze formule kijkt Excel eerst naar het gegevensbereik B2:F7. Daarna kijkt Excel in de kolom ‘Totaalprijs‘ wat er opgeteld moet worden. En vervolgens telt Excel alleen de rijen op die voldoen aan de voorwaarden in de criteriatabel H2:I3.
In dit voorbeeld resulteert dat in €1000, omdat alleen de eerste Bank (rij 3) voldoet aan beide voorwaarden.
Bij het argument ‘veld’ kun je in plaats van de kolomnaam “Totaalprijs” ook gebruik maken van een celverwijzing naar de koptekst zelf. In dit geval staat de kolomkop in cel F2, dus je kunt de formule ook als volgt schrijven:
=DBSOM(B2:F7;F2;H2:I3)
Je kunt ook meerdere rijen met criteria toevoegen, bijvoorbeeld:
Omdat het criteriatabel nu groter is, dien je dit argument aan te passen, waardoor je de volgende formule krijgt:
=DBSOM(B2:F7;F2;H2:I4)
Excel telt met deze formule de totaalprijzen op van de productsoorten Bank en Lamp, maar alleen als het aantal groter is dan 1.
In dit voorbeeld is dat de bank uit rij 3 (€1000) + de lamp uit rij 5 (€320), waardoor de som resulteert in €1320.
Het is hierbij van belang om te weten dat in de criteriatabel elke rij apart wordt beoordeeld. De filters per rij worden met EN gecombineerd, en rijen onder elkaar werken als een OF filter.
Je kunt ook extra kolommen toevoegen aan je filter. Stel, je voegt kolom J toe met de kop “Prijs per stuk”. Dan kun je bijvoorbeeld kiezen voor zoiets:
In dit geval dient het criteria argument uitgebreid te worden naar H2:J4.
Excel filter vervolgens op:
- Productsoort (Bank of Lamp)
- Alleen als het aantal groter dan 1 is.
- De prijs per stuk hoger ligt dan wat er in kolom J staat: >100 voor de banken en >50 voor de lampen.
Door gebruik te maken van een criteriatabel krijg je dus krachtige functies voor het analyseren van je gegevens met meerdere voorwaarden. Zoals je hebt kunnen zien kun je hiermee namelijk relatief eenvoudig filteren op meerdere kolommen en rijen tegelijk, iets wat met standaard functies wat lastiger is.
Hierna gaan we naar de andere database functies kijken, maar hierbij hou ik bewust de criteriatabel eenvoudig, zodat je goed kunt volgen hoe alles werkt. Maar je weet nu dus wel dat je de filter zo uitgebreid kan maken als je zelf wilt.
Bij alle databasefuncties is de opbouw van de formule steeds hetzelfde, namelijk:
=FUNCTIENAAM(database;veld;criteria)
Het enige dat verandert is de functienaam. Daarom wordt hierna niet steeds weer de argumenten uitgelegd, omdat je betekenis daarvan inmiddels weet. In de voorbeelden die volgen zal het criteriatabel soms wel wat verschillen, zodat je verschillende mogelijkheden ziet en begrijpt.
2. Functie DBGEMIDDELDE
Met de functie DBGEMIDDELDE bereken je het gemiddelde van een veld. Maar alleen indien de rijen voldoen aan de criteria.
Stel we willen de gemiddelde prijs per stuk berekenen, maar alleen van producten met een kleiner aantal dan 3 en een prijs per stuk die hoger ligt dan 400.
De formule wordt dan als volgt:
=DBGEMIDDELDE(B2:F7;"Prijs per stuk";H2:I3)
Excel zoekt eerst naar rijen waarbij het aantal kleiner is dan 3. Dit is het geval bij rij 3, 4 6 en 7. Daarvan blijven alleen de rijen over waar de prijs per stuk groter is dan 400.Dat zijn de rijen 3 (€500), 6 (€600) en 7 (€500). Excel berekent vervolgens het gemiddelde van deze drie waarden:
(500 + 600 + 500) / 3 = €533,33
3. Functie DBAANTAL
Met de functie DBAANTAL kun je tellen hoeveel cellen in een bepaalde kolom een getal bevatten en voldoen aan de opgegeven criteria.
Stel we willen tellen hoeveel producten er zijn met een aantal kleiner dan 3.
- Het argument ‘veld’ is “Aantal”. Dit is namelijk de kolom waarin geteld wordt.
- Het argument ‘criteria’ verwijst naar het celbereik H2:H3. Hier staat namelijk het filter <3 ingevuld bij de kolomnaam ‘Aantal’.
De formule wordt dan als volgt:
=DBAANTAL(B2:F7;"Aantal";H2:H3)
Excel kijkt in deze formule naar kolom D (Aantal) en telt alleen de cellen die aan het criterium voldoen. In dit geval zijn dat 4 producten (rijen 3, 4, 6 en 7).
Deze functie telt dus het aantal cellen met getallen en niet de som van de waarden. Wil je de totalen optellen? Dan moet je de functie DBSOM gebruiken die eerder is uitgelegd.
Je ziet in dit voorbeeld dat de ‘criteriatabel’ groter is, maar dat je niet alles hoeft te selecteren. In dit voorbeeld is bij het argument ‘criteria’ alleen H2:H3 geselecteerd.
Als we het bijvoorbeeld wel uitbreiden naar H2:I3, dan telt Excel alleen de producten waarvan:
- Het aantal kleiner is dan 3, en..
- De prijs per stuk hoger is dan 400
Nu voldoen alleen nog de producten uit rij 3, 6 en 7 aan beide voorwaarden. Het resultaat is dus 3.
4. Functie DBAANTALC
De functie DBAANTALC lijkt veel op de vorige functie (DBAANTAL0, maar telt alle niet lege cellen zolang ze aan de criteria voldoen. Dus ook getallen met tekst.
Stel we willen tellen hoeveel producten er zijn vóór 4 augustus 2025, waarbij de totaalprijs lager is dan €1000. De criteriatabel wordt dan als volgt:
- Datum < 4-8-2025
- Totaalprijs < 1000
De formule wordt dan:
=DBAANTALC(B2:F7;"Productsoort";H2:I3)
Excel telt vervolgens alle niet lege cellen in de kolom ‘Productsoort’ die aan deze criteria voldoen.
In dit geval resulteert dat in het getal 3 (rij 4, 5 en 6).
5. Functie DBMAX
Met de functie DBMAX zoek je de hoogste waarde in een kolom van je database, maar alleen voor de rijen die voldoen aan de opgegeven criteria.
Stel we willen de hoogste totaalprijs weten van de producten die zijn verkocht na 1 augustus 2025. Hiervoor hebben we alleen het volgende criteria nodig in de criteriatabel:
- Datum > 1-8-2025
De formule wordt dan als volgt:
=DBMAX(B2:F7;"Totaalprijs";H2:H3)
Excel kijkt nu in de rijen die voldoen aan de voorwaarde (datum groter dan 1 augustus) en zoekt deze hoogste waarde in de kolom ‘Totaalprijs’.
In dit geval is dit €600 (rij 6), want het product met een totaalprijs van €1000 (rij 3) valt op 1 augustus en wordt dus niet meegenomen.
6. Functie DBMIN
De functie DBMIN werkt hetzelfde als DBMAX, maar retourneert juist de laagste waarde uit een kolom. Wederom op basis van de rijen die voldoen aan je gekozen criteria.
In dit voorbeeld willen we de laagste totaalprijs weten van het productsoort ‘bank’. Hiervoor moeten we in de criteriatabel het volgende invoeren:
- Productsoort: Bank
De formule wordt dan als volgt:
=DBMIN(B2:F7;"Totaalprijs";H2:H3)
Excel kijkt nu alleen naar de rijen waar ‘bank’ voorkomt in de kolom ‘productsoort’. En vervolgens zoekt het van die rijen de laagste waarde in de kolom ’totaalprijs’.
In dit geval zijn er twee rijen met banken:
- Rij 3 met een totaalprijs van €1000
- Rij 7 met een totaalprijs van €500
De laagste waarde is €500, en dit wordt dan ook geretourneerd door de functie.
7. Functie DBPRODUCT
Met de functie DBPRODUCT kun je in een database het product berekenen van getallen in één kolom. De betekenis van ‘product’ is niet voor iedereen duidelijk, maar in Excel betekent product simpelweg vermenigvuldigen.
Uiteraard wordt hierbij, net zoals bij de andere databasefuncties, alleen gekeken naar de rijen die voldoen aan de opgegeven criteria.
Stel we willen het aantal stuks vermenigvuldigen van alle producten waarbij de prijs per stuk hoger is dan €100.
In de criteriatabel dient dan het volgende te staan:
- Prijs per stuk > 100
De formule wordt dan als volgt:
=DBPRODUCT(B2:F7;"Aantal";I2:I3)
Excel zoekt nu eerst de rijen waar de prijs per stuk hoger is dan 100. Dat zijn de volgende rijen:
- Rij 3: prijs per stuk = €500, aantal = 2
- Rij 4: prijs per stuk = €150, aantal = 1
- Rij 6: prijs per stuk = €600, aantal = 1
- Rij 7: prijs per stuk = €500, aantal = 1
Het resultaat is daarom: 2 × 1 × 1 × 1 = 2
Als we bijvoorbeeld het criterium aanpassen naar >75, dan telt ook rij 5 mee (prijs per stuk = €80, aantal = 4). De vermenigvuldiging wordt dan:
2 × 1 × 1 × 1 × 4 = 8
Nawoord
Ik hoop dat na het lezen van dit artikel duidelijk is geworden hoe deze databasefuncties werken. Waarschijnlijk gebruik je nu vooral populairdere functies als SOM.ALS of GEMIDDELDE.ALS, maar het is zeker de moeite waard om deze databasefuncties eens als alternatief uit te proberen. Vooral wanneer je met grotere tabellen en/of meerdere criteria werkt, kan dit een goede keuze zijn. Die bovendien niet zo ingewikkeld is als het wellicht op het eerste gezicht lijkt.
Wil je meer leren over het werken met formules en functies in Excel? Neem dan gerust eens een kijkje bij mijn online cursus Formules en functies in Excel.
