Finch’s weblog

donderdag, 17 januari 2008

Excel: Verticaal zoeken

Filed under: Excel — thefinch @ 9:53

Vandaag gaan we het makkelijk houden, en beperken we ons tot de formule vert.zoeken (Vlookup in een Engelstalige versie). Veel beginnende Excel gebruikers zijn vaak niet bekend met deze formule en proberen de functionaliteit na te bootsen met geneste ALS formules, maar lopen dan al heel snel tegen problemen aan. Op forums moet je geregeld het antwoord geven: “dat is een probleem dat eenvoudig op te lossen is met de vert.zoeken formule”. Vandaar de reden dat ik dit artikel schrijf. In essentie kunnen we de vert.zoeken formule omschrijven als een formule die een gevraagde waarde gaat opzoeken in een zoektabel en dan overeenkomstige gegevens gaat weergeven.

Voorbeeld 1:

Voor het eerste voorbeeld nemen we volgende gegevenstabel als uitgangssituatie:

Deze tabel bevat klantgegevens van een onderneming. Telkens men een factuur gaat aanmaken vult men alle gegevens over die klant handmatig in, in de daarvoor bestemde velden. Echt handig is dat niet, het is tijdrovend de juiste klant op te zoeken, en dan al de gegevens één voor één intypen. Bovendien is de kans op foutieve invoer zeer reëel. Beter zou zijn enkel de klantID in te vullen, aangezien deze uniek dient te zijn, en dat dan al de overeenkomstige gegevens automatisch ingevuld worden.

Onderstaande afbeelding bevat een voorbeeld hoe bovenstaande situatieschets in Excel verwerkt kan worden:

Voor het automatisch ophalen van de gevraagde data, gebruiken we zoals reeds aangehaald de vert.zoeken formule. Deze formule heeft 4 argumenten, waarvan 1 optioneel. Deze 4 argumenten zijn:

  1. Zoekwaarde: Dit is de waarde die we wensen op te zoeken in onze tabelmatrix. Vaak gebruiken we een celverwijzing om de zoekwaarde te bepalen. In ons voorbeeld is dat B1.
  2. Tabelmatrix: Dit is de tabel waar we in de eerste kolom gaan zoeken, en waaruit we de gevraagde gegevens gaan halen (uit de volgende kolommen). In ons voorbeeld is dat F1:L11.
  3. Kolomindex_getal: Een getal dat aangeeft uit welke kolom van de tabelmatrix we de gegevens willen halen. In ons voorbeeld levert een 2 als argument naam gegevens op, en een 4 de postcode.
  4. Benaderen: Dit is het booleaans optionele argument. Wordt de waarde weggelaten, gebruikt Excel de waarde WAAR, wat wil zeggen dat wanneer er geen exacte match is met de zoekwaarde, de gegevens van de waarde die juist kleiner is dan zoekwaarde worden getoond. Je kan ook de waarde ONWAAR meegeven met de formule. Dan wordt er enkel een resultaat teruggegeven wanneer er sprake is van een exacte match. Aangezien in ons voorbeeld exacte gelijkenis wenselijk is zetten we deze waarde op ONWAAR.

OPMERKING: De zoekwaarde wordt altijd gezocht in de eerste kolom, en ook enkel in de eerste kolom, van de tabelmatrix! Dit impliceert dan ook dat gegevens die je kan ophalen zich ook altijd bevinden na de eerste kolom van de tabelmatrix, en dus nooit ervoor.

De tabelmatrix is in de meeste gevallen een vast bereik, en wordt daarom best in de formule met absolute celverwijzingen “aangesproken” (de $-tekens voor de kolomnaam en het rijnummer). Ditzelfde geldt vaak ook voor de zoekwaarde. In ons voorbeeld is de zoekwaarde steeds B1, vandaar ook weer de absolute celverwijzingen.

Toegepast op onze situatie krijgen we volgende formules:

Wanneer we nu in B1 de waarde veranderen, gaan de cellen B2:B7 automatisch de gevraagde gegevens ophalen en tonen, wanneer de zoekwaarde niet gevonden wordt, krijgen we echter een foutmelding.

Voorbeeld 2:

Als afsluiter voor vandaag nog een tweede voorbeeldje met de vert.zoeken formule. Dit keer gaan we de parameter “benaderen” van de formule op WAAR instellen. Daarvoor nemen we het uitgangspunt van (vereenvoudigde) puntenscores.

Bij een score van 0 tot en met 49 willen we de omschrijving “Buis!” weergeven, een score tussen 50 en 70 levert de omschrijving “Geslaagd” op, enz. Deze opzet in Excelformaat kan er bijvoorbeeld zo uitzien:

De formule in B2 ziet er als volgt uit: “=VERT.ZOEKEN($B$1;$E$2:$F$8;2;WAAR)”. Deze laatste optie waar konden we weglaten aangezien dat de standaard parameter is.

Aangezien we benaderen op waar hebben gezet zouden we bij een score van bv. 79 de omschrijving “Onderscheiding” willen zien, en bij een score van 80 “Grote Onderscheiding”. Hieronder 2 screenshots die dat aantonen:

Randopmerkingen:

Om af te sluiten wil ik nog een paar randopmerkingen rond het gebruik van de vert.zoeken formule geven:

  • Alvorens vert.zoeken te gebruiken maak je er best de gewoonte van de tabelmatrix oplopend te sorteren op het eerste veld. In sommige gevallen, zouden anders resultaten wel eens anders kunnen uitvallen dan verhoopt.
  • Wanneer de sleutelwaarde in je tabelmatrix niet uniek is (d.w.z. in kolom 1 van je tabelmatrix staan dubbels) dan “kiest” Excel van welk rij de gegevens worden weergegeven. Meestal is dat de rij van de eerste waarde die gevonden wordt, maar dat is niet altijd het geval.
  • Binnen de formule kan je ook werken met namen van bereiken. Namen geef je aan bereiken door in Excel 2007 in de ribbon “Formules” te klikken op “Namen beheren” vervolgens “nieuw” en daarna een geldige naam in te vullen alsook een bereik. (In Excel versies voor 2007 wordt dit Invoegen > Naam > Definiëren en ook een geldige naam en bereik opgeven). Stel dat we in het laatste voorbeeld volgende naamgeving hadden toegepast:
    • Naam: Zoekwaarde     Bereik: $B$1
    • Naam: Zoektabel         Bereik: $E$2:$F$8

    Dan konden we ook deze formule gebruiken: “=VERT.ZOEKEN(Zoekwaarde;Zoektabel;2;WAAR)”

  • In ons eerste voorbeeld diende we bij elk veld steeds het derde argument handmatig te veranderen om de veranderde gewenste kolom aan te duiden. Door gebruik te maken van de formule RIJ() hadden we dit kunnen vermijden. Deze formule geeft het rijnummer terug van het argument (welke een celverwijzing is), of bij het ontbreken van een argument het rijnummer van de huidige cel. Door de formule voor B2 te veranderen in: “=VERT.ZOEKEN($B$1;$F$2:$L$11;RIJ(A2);ONWAAR)” en deze door te voeren naar de cellen B3:B7 krijgen we ook steeds de gewenste gegevens terug.
About these ads

7 reacties »

  1. Hoi finch,

    Ik heb laatst een tabel gemaakt, die ik wilde gebruiken met VERT.ZOEKEN……
    Echter, in de eerste kolom waar de zoekwaarde stond, had ik 3 en 2 letter-woorden. Dus eerst b.v. 2 regels met 3 letter woorden, 3 regels met 2 letter woorden en daarna weer enkele regels met 3 letter woorden.

    abc
    cba
    bca
    eu
    ue
    gfd
    hyt
    hji
    kl
    ml
    jk (enz…enz)

    Met deze opzet, layout, gaat vert.zoeken volledig de mist in. hoe zou ik dat het beste kunnen oplossen ??

    Gr. Bart

    Reactie door Bart — vrijdag, 18 januari 2008 @ 7:39

  2. Hallo Bart,

    allereerst wil ik je bedanken voor je reactie. Je geeft aan dat vert.zoeken volledig de mist in gaat, maar spijtig genoeg zeg je niet wat er juist mis gaat. Er zijn een tweetal zaken waar ik zo aan denk, waarom het mis kan gaan:
    1) Je data is niet oplopend gesorteerd. Zoals ik in het artikel aangeef, kan je je resultaat dan niet goed inschatten, dus eerste stap data sorteren.
    2) Je gebruikt misschien niet de (optionele) parameter ONWAAR voor het argument benaderen. Het weglaten van die parameter, WAAR is de standaard waarde, wil zeggen dat je gaat benaderen wanneer er geen exacte match wordt gevonden. Misschien is het raadzaam om die in jou geval op ONWAAR te zetten (Excel geeft dan enkel exacte overeenkomsten terug).

    Moest je hiermee niet geholpen zijn, dan hoor ik het wel.

    Groeten,

    Finch

    Reactie door thefinch — vrijdag, 18 januari 2008 @ 10:03

  3. Hoi Finch,

    Als ik het eerste voorbeeld toe pas in mijn excel sheet dan loopt deze vast
    Dit komt waarschijnlijk omdat de tabel waarin ik zoek veel te groot is en ik te vaak de formule moet uitvoeren

    Mijn vraag is dus: Zijn er nog andere mogelijkheden ?

    Gr. Maarten

    Reactie door Maarten — dinsdag, 3 juni 2008 @ 10:29

  4. hallo,
    Ik heb volgens probleem op te lossen:
    1. een sheet “KILO” met een tabel van datum, uur, gemeten gewicht (de datum is oplopend en tot vier keer per dag)
    2. een sheet “SYNTHESE”, waarin ik

    Reactie door peter wouters — vrijdag, 4 februari 2011 @ 15:30

  5. Hallo,
    Ik heb een (engelstalige) sheet(*) met datum (oplopend), uur, gewicht en verschil
    (*uittreksel)
    Datum Uur gewicht (KG) Verschil (KG)
    03/01/2011 08:25 105,70 0,00
    03/01/2011 12:24 105,60 -0,10
    03/01/2011 17:59 104,70 -0,90
    04/01/2011 08:10 105,30 0,60
    05/01/2011 08:02 106,30 1,00
    05/01/2011 12:32 105,40 -0,90
    05/01/2011 18:48 105,40 0,00

    Een tweede sheet met de synthese:
    – “Actueel gewicht”, in feite slechts het eerste gemeten gewicht van de dag
    – maximum gewicht
    – minimum gewicht

    Actueel gewicht:106,30 BMI: 38,09
    Max. Gewicht: 105,70 op datum:
    Min. Gewicht: 104,70 op datum:

    Ik slaag erin om het gewicht (éérste opname, max en min) te bepalen,
    maar niet, via “Vlookup”, om:
    – telkens het lààtste gemeten gewicht te afficheren (in actueel gewicht)
    – de datum van het max & min gewicht te publiceren

    Wat zie ik over het hoofd?

    Met vooraf dank voor uw hulp.

    Reactie door peter wouters — vrijdag, 4 februari 2011 @ 15:48

  6. -het laatst gemeten gewicht kan je bekomen door een combinatie van formules te gebruiken. Bv. De Indirect formule icm de verschuiven formule. Een indirect formule met een aantalarg formule is een andere mogelijkheid. Onder anderde deze pagina op mijn blog: http://thefinch.wordpress.com/2008/02/09/excel-dynamische-afhankelijke-datavalidatie/ laat je toe om dit te bekomen. De pagina zelf is uitgebreider in opzet. Maar wel bepalen door een lijst door het eerste en laatste element van die lijst te bepalen, die techniek kan je ook gebruiken.

    – De datum uit je lijst halen gaat niet lukken met een vert.zoeken formule in je huidige opzet. Daarvoor dienen je resultaat velden (de datum in casu kolom A) achter je zoekveld (het gewicht in casu kolom C) te staan. Om toch de datum te zoeken in je huidige opzet, dien je gebruik te maken van de index functie icm de vergelijken functie.

    Hopelijk zet ik je hiermee op het goede pad.

    Reactie door thefinch — vrijdag, 4 februari 2011 @ 16:03

  7. Hallo Finch
    Hartelijk dank voor je uitleg en voorbeelden over de functie verticaal zoeken in Excel. Ik heb er wat van geleerd. In vergelijking met andere site’s die hierover uitleg geven is uw uitleg vele malen beter.
    Nogmaals hartelijk dank.
    Vriendelijke groet
    Rob

    Reactie door Rob Molenaar — donderdag, 11 april 2013 @ 9:35


RSS feed voor reacties op dit bericht. TrackBack URI

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit / Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit / Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit / Bijwerken )

Google+ photo

Je reageert onder je Google+ account. Log uit / Bijwerken )

Verbinden met %s

Het Shocking Blue Green thema. Blog op WordPress.com.

Volg

Ontvang elk nieuw bericht direct in je inbox.

%d bloggers like this: