Ik ken heel veel projectmanagers die uren kunnen spenderen aan het ‘Excellen’. Er moeten immers geregeld rapportages worden gemaakt, en het zou te makkelijk zijn als alles rechtstreeks bruikbaar uit systemen zou komen. Er zijn een paar functies die ik zeer vaak gebruik, maar die bij veel collega projectmanagers toch uitdagingen op blijven leveren. Bij dezen dus mijn eerste Excel tip: verticaal zoeken.
Verticaal zoeken kan je enorm veel werk besparen. Stel: ik heb uit een urensysteem een lijst gekregen van resources die op bepaalde data uren hebben geschreven. Maar deze uren moeten worden vergoed aan de afdelingen, en niet aan de mensen. Hoe kom je er dan handig achter welke resource ook alweer bij welke afdeling hoorde?
1. De bron
Dit is de bron: wat we gaan vullen, is kolom D: de afdeling. Om dat te doen – zonder handwerk – moeten we eerst een tabelletje maken waarin de resources en de bijbehorende afdelingen staan.
2. De zoek matrix
In kolom G en H heb ik de gegevens geplaatst waarin we Excel gaan laten zoeken. Dit kan uiteraard ook een zoekbron op een ander blad zijn. We gaan nu Excel in kolom D de waarden laten zoeken uit deze matrix G2:H6.
3. De zoekformule
Verticaal zoeken doen we met de formule VERT.ZOEKEN (VLOOKUP in het Engels). Dit ziet er zo uit:
=VERT.ZOEKEN(Zoekwaarde;ZoekMatrix;KolomNummer;Benaderen?)
| Zoekwaarde | De waarde waarop je in de zoekmatrix wilt zoeken – hier kolom B |
| ZoekMatrix | De tabel waaruit je de gewenste waarde wilt ophalen – hier G2:H6 (zonder kolomhoofden dus – die zijn vooral voor jezelf) |
| KolomNummer | Het kolomnummer waar je gegevens vandaan moeten komen – hier de 2e kolom (want we willen de Afdeling hebben) |
| Benaderen | Als de waarde niet bestaat, pakt Excel de dichtstbijzijnde waarde als je deze WAAR (TRUE) maakt. Bij mij bijna altijd ONWAAR (FALSE). |
Dus: als we in cel D2 de afdeling van Erik willen opzoeken, doen we dit als volgt:
Je ziet dat de formule gevuld is met: de zoekwaarde (B2), er wordt gezocht in de tabel G2:H6, ik wil kolom 2 hebben, en niet benaderen.
Waarom die $ tekens in de formule bij de zoekmatrix? Dat doe ik om ervoor te zorgen dat als de formule wordt gekopieerd en geplakt in andere cellen, deze waarden hetzelfde blijven: de zoekmatrix zal immers nooit verplaatsen!
4. Het resultaat
Druk je op Enter, dan zal je zien dat D2 ineens is gevuld met “Communicatie”. Kopieer je de formule van D2 naar de cellen D3 tot en met D22, dan zie je dit:
Dit lijkt nu veel werk, maar als je dit moet toepassen op lijsten van duizenden rijen, dan scheelt het ineens fors! Zeker als je je brongegevens gaat verrijken met meer bijbehorende gegevens.
Met deze gegevens kun je vervolgens aan de slag om bijvoorbeeld draaitabellen te maken – dat behandel ik de volgende keer.
Meer tips?
Als je nu al vragen hebt over bepaalde Excel handigheidjes die je als projectmanager kunt gebruiken, laat dan een reactie achter!
Eén reactie op “Excel tip: Verticaal zoeken”