Cursus
Google Sheets voor gevorderden
4 uur
57.9K
Data science gaat soms niet over deep learning, big data of aanbevelingssystemen. Zeker als je in een sector als finance werkt of bij een bedrijf dat in het verleden sterk op Excel leunde, is de kans groot dat je vaak met Excel-spreadsheets te maken hebt. Ik hoor zelf bij die laatste groep. Mijn werkgever vertrouwde zwaar op Excel en dus moest ik voor mijn werk veel leren over de verschillende mogelijkheden ervan.
Hoewel R en Python op veel vlakken duidelijke voordelen hebben ten opzichte van Excel, blijft Excel een krachtig hulpmiddel voor bepaalde taken, zoals het maken van bedrijfsrapporten. In deze tutorial focus ik op een van Excel’s ingebouwde functies die enorm helpt wanneer je met meerdere Excel-tabellen tegelijk werkt.
De functie waar ik het over heb is VERT.ZOEKEN (VLOOKUP). Met VERT.ZOEKEN kun je verschillende kolommen in hetzelfde werkblad of tussen verschillende werkbladen in dezelfde werkmap aan elkaar relateren. In essentie laat het je een waarde in de ene kolom opzoeken en de bijbehorende waarde uit een andere kolom teruggeven. Dit is vooral handig bij grote, ongesorteerde datatabellen die je op basis van een bepaalde kolom (idealiter met een unieke sleutel) aan elkaar wilt koppelen.
Ik laat zien hoe VERT.ZOEKEN werkt tussen kolommen in hetzelfde werkblad met een eenvoudig voorbeeld:
In de screenshot hierboven zie je een veelvoorkomende VERT.ZOEKEN-formule zoals je die in een bedrijfsrapport zou schrijven. De onderdelen zijn als volgt:
- IFERROR-functie: Gebruikt om een fout te vervangen door een koppelteken (-) als de sleutel leeg was of niet in de tabel voorkomt. Dit is goede praktijk voor foutafhandeling die het werkblad netter houdt wanneer er een fout optreedt, maar het is zeker niet nodig om VERT.ZOEKEN te laten werken.
- VERT.ZOEKEN-argumenten:
- E2 is de opzoekwaarde (in dit geval gebruiken we KOG als voorbeeld, maar dit kan elke sleutel zijn). Deze waarde moet zich in de meest linkse kolom bevinden van het geselecteerde tabelbereik waarin je wilt zoeken.
- B1:C18 is het tabelbereik/-array waarin je waarden zoekt. De meest linkse kolom heeft index 1, zoals getoond. Deze kolom moet ook de opzoekwaarden bevatten om de functie te laten werken.
- 2 is het kolomindexnummer binnen het geselecteerde tabelbereik waarvan je de waarde wilt ophalen. In dit voorbeeld willen we de beschrijving ophalen, dus kiezen we kolom 2 van de tabelarray.
- FALSE is een vlag die aangeeft dat je een exacte overeenkomst van de opzoekwaarde wilt. In dit geval kozen we FALSE omdat we een exacte match van de KOG-sleutel willen. De andere optie is TRUE, wat een benaderende overeenkomst zoekt en de standaard is als je dit argument niet opgeeft. Over het algemeen raad je TRUE niet aan bij het matchen van sleutels of strings, omdat dat tot vreemde resultaten kan leiden.
Na het invoeren van die VERT.ZOEKEN-formule en op ENTER drukken in Excel, krijg je het volgende resultaat:
Zoals je ziet, gaf VERT.ZOEKEN precies wat je verwacht: "King of Gondor", de beschrijving die bij de KOG-sleutel hoort. Dit geldt voor elke sleutel die je nu in cel E2 invoert. Laten we bijvoorbeeld kijken wat er gebeurt als we de sleutel KIN invullen.
The King in the North! Precies: als we de KIN-sleutel invoeren, krijgen we de beschrijving van onze geliefde Jon (Snow).
Maar VERT.ZOEKEN kan meer dan alleen één waarde ophalen op basis van een sleutel. Stel dat we die tabel splitsen in twee delen, elk op een ander werkblad in dezelfde werkmap.
Tabellen samenvoegen
 |
 |
Zoals je ziet is de data nu verdeeld over twee verschillende werkbladen (S1 en S2) en staan de sleutels in de tabel op het tweede blad (S2) in een andere volgorde. Gelukkig is de sleutelkolom in beide bladen hetzelfde, ongeacht de volgorde, dus we kunnen de data in S1 weer samenvoegen met VERT.ZOEKEN (denk aan een left join op één kolom). De syntaxis daarvoor is als volgt:
Dit keer wordt het eerste argument, de opzoekwaarde, B2, omdat we de sleutel B2 in de tabel op S2 willen matchen. Het tabelbereik is nu de volledige datatabel in S2 met in de eerste kolom de gemeenschappelijke sleutels. Je ziet misschien dat in het tabelbereik dollartekens staan. Die voeg je toe door na het selecteren van het bereik op F4 te drukken. Zo vergrendel je het bereik, zodat VERT.ZOEKEN bij het omlaag slepen van de formule in S1 naar hetzelfde oorspronkelijke bereik blijft kijken zonder mee te verschuiven. Het kolomindexnummer blijft 2, omdat we de beschrijving willen, de tweede kolom in S2. Tot slot kiezen we als laatste argument FALSE, omdat we een exacte overeenkomst voor de sleutels willen.
De laatste stap is simpelweg de formule omlaag slepen tot de laatste rij van de tabel. De resulterende tabel in S1 ziet er zo uit:
Voilà! Zo hebben we de data weer aan elkaar gezet met VERT.ZOEKEN. Tabellen op deze manier samenvoegen is heel gebruikelijk in een zakelijke context, zeker wanneer SQL, R of Python niet de tools zijn waar senior executives aan gewend zijn. Bijvoorbeeld: je kunt twee bestanden hebben met verschillende details over gekwalificeerde saleskansen die je aan elkaar kunt relateren met een unieke opportunity-ID voor een maandelijks Excel-rapport over sales (het beste voorbeeld van Excel-gebruik in mijn geval).
Benaderende overeenkomsten gebruiken
Tot nu toe heb je gezien dat we TRUE als laatste argument (oftewel een benaderende overeenkomst) in geen van de VERT.ZOEKEN-functies hebben gebruikt. Dat komt doordat we ons hebben gericht op exacte matches van unieke sleutels. Je kunt TRUE als laatste argument in VERT.ZOEKEN echter prima gebruiken bij numerieke data.
Stel bijvoorbeeld dat we vis verkopen en de volgende kortingen aanbieden wanneer klanten bepaalde aantallen afnemen (Waarom vis? Simpel: vis is geweldig!).
De VERT.ZOEKEN-functie in cel F2 is dit keer:
Zodra je die formule hebt staan, wordt de korting de dichtstbijzijnde bij de drempels in de tabel. Koop je bijvoorbeeld 25 vissen, dan is onze korting 5%, bij 70 vissen 10% en bij 120 vissen 15%. Al deze voorbeelden zie je hieronder:
 |
 |
 |
Introductie tot INDEX-MATCH
VERT.ZOEKEN is een krachtige functie om verschillende kolommen te relateren. Er is echter een alternatief: de combinatie INDEX-MATCH. In tegenstelling tot VERT.ZOEKEN, dat één functie is, is INDEX-MATCH een combinatie van twee functies met verschillende voordelen ten opzichte van standaard VERT.ZOEKEN, ook al is het iets complexer om te schrijven.
Voor deze tutorial bekijken we een eenvoudig voorbeeld waarin we met INDEX-MATCH twee tabellen koppelen op basis van een gemeenschappelijke sleutel, net zoals we hierboven met VERT.ZOEKEN hebben gedaan, en leggen we de syntaxis uit (zie hieronder).
De onderdelen van de INDEX-MATCH-combinatie zijn als volgt:
- INDEX-argumenten:
- M2:M18: Dit is het kolombereik waarvan je de waarden wilt ophalen. In dit geval willen we de beschrijvingskolom, dus selecteren we dat bereik. Let op: we hebben na het selecteren van het bereik F4 gebruikt om het te vergrendelen, zodat het niet verschuift wanneer we de formule omlaag slepen.
- MATCH-argumenten:
- B2: Dit is de sleutel die we willen opzoeken. Dit komt overeen met de opzoekwaarde in VERT.ZOEKEN.
- L2:L18: Dit is het kolombereik met de sleutels waarin de opzoekwaarde moet voorkomen. Ook hier hebben we F4 gebruikt.
- 0: Dit is hetzelfde als FALSE in VERT.ZOEKEN. Het is de vlag voor een exacte overeenkomst.
Aan de hand van de syntaxis zie je meteen een van de belangrijkste voordelen van INDEX-MATCH boven VERT.ZOEKEN: kolomimmuun. Als we nu besluiten een extra kolom toe te voegen tussen Key en Description in de rechtertabel hierboven, maakt dat niet uit, omdat de formule nog steeds naar de kolom Description verwijst. Zie hieronder:
Met VERT.ZOEKEN kan dit niet zonder de formule aan te passen, omdat die een statische kolomverwijzing gebruikt. Met andere woorden: in het voorbeeld hierboven had je in eerste instantie 2 als kolomindexnummer ingevuld om de Description op te halen. Maar na het toevoegen van de kolom Fish zou VERT.ZOEKEN nu de waarde in de kolom Fish teruggeven, omdat dat nu kolom 2 in het tabelbereik is. Je moet dan handmatig terug naar VERT.ZOEKEN en het kolomindexnummer wijzigen naar 3 om de functie weer correct te laten werken.
Zoals je ziet, is de procedure om met INDEX-MATCH de beschrijving aan de naam in de linkertabel te koppelen hetzelfde als met VERT.ZOEKEN. We slepen de formule gewoon omlaag, zoals hieronder getoond:
Let op: tabellen tussen verschillende werkbladen in dezelfde werkmap samenvoegen werkt precies hetzelfde. Het enige verschil is dat je het kolombereik met de gemeenschappelijke sleutels én het kolombereik met de op te halen waarden op het tweede blad moet selecteren.
Conclusie
Hopelijk vond je deze tutorial nuttig. De functies VERT.ZOEKEN en INDEX-MATCH zijn eigenlijk onmisbaar voor iedereen die in een zakelijke context met Excel werkt. Ik raad je aan om verder in INDEX-MATCH te duiken dan wat hier is behandeld, want het biedt handige mogelijkheden zoals hoofdlettergevoelig matchen (ja, standaard zijn INDEX-MATCH en VERT.ZOEKEN niet hoofdlettergevoelig) of matchen op basis van meerdere kolommen. Blijf leren; de lucht is de limiet!
Wil je meer leren over VERT.ZOEKEN? Volg dan de DataCamp-cursus Data Analysis with Spreadsheets.
Onderwerpen
