Inhoudsopgave
Hoe de zoekfuncties werken
Laat ons eens beginnen met een eenvoudig voorbeeld. Stel dat je 2 lijstjes hebt:
De eerste lijst geeft bestelnummers weer in de eerste kolom en in de tweede kolom productcodes (beginnend met PC in dit voorbeeld.)
Bestelnummer – Productcode
001 – PC0123
002 – PC0157
003 – PC0764
Daarnaast heb je een tweede tabel, deze keer met 3 kolommen. De eerste kolom bestaat uit alle productcodes die je aanbiedt, en dan in de kolommen daarnaast de productbenaming en daarnaast de productprijs
Productcode – productbenaming – productprijs
PC1000 – tandenborstel – 5
PC1010 – borstel – 3
PC0123 – haardroger – 25
PC0157 – handdoek – 5
PC0567 – shampoo – 3
PC0764 – douchegel – 2
In Excel ziet dat er zo uit:
Zou het niet mogelijk zijn om de eerste tabel aan te vullen met de productbenaming en de productprijs? Dat is handig, want die productcodes alleen maken niet heel duidelijk over welk product het gaat.
Je zou manueel bij elke bestelling de productbenaming en de prijs kunnen aanvullen, maar dat is natuurlijk een dom werkje. Met een spreadsheet tool moet je dat opzoeken kunnen automatiseren. Zeker wanneer de lijsten veel langer zouden zijn dan de enkele items die er nu weergegeven worden/
Dat combineren van lijsten is precies wat opzoekfuncties voor je doen: je zoekt gegevens in een tabel op op basis van gegevens in een andere tabel.
Met verticaal zoeken (dat is één van de 3 opzoekfuncties die we hier behandelen) bijvoorbeeld ga je een bepaalde waarde uit de eerste tabel (de productcode) gebruiken om in de tweede tabel de overeenkomende rij op te zoeken. Microsoft Excel start bovenaan bij de eerste rij in de tweede tabel, en gaat dan rij voor rij naar beneden (vandaar ook verticaal zoeken) tot de overeenkomende productcode gevonden is.
Stel bijvoorbeeld dat je de prijs voor de derde bestelling (003) wil opzoeken. Je zoekwaarde (wat je opzoekt) is dan PC0764. In de tweede tabel begin je bovenaan te zoeken in kolom F en je zakt naar beneden tot je die PC0764 vindt. In het voorbeeld is dat rij 8. Vervolgens kijk je in de 3de kolom van tabel 2. Het antwoord is dan 2.
Hieronder leg ik dat nog wat meer in detail uit en leren we hoe je de functies in Excel gebruikt.
Opbouw van de functie VERT.ZOEKEN
Wanneer we in een cel =VERT.ZOEKEN( intypen dan zien we de opbouw van deze functie. Ze bestaat uit 4 delen:
=VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;[benaderen])Ik kan me inbeelden dat de uitleg hieronder wat algemeen klinkt als je nog nooit verticaal zoeken gebruikt hebt. Lees het dan maar gewoon door, bekijk daarna het voorbeeld onderaan en kom dan hier opnieuw eens lezen. Veel kans dat je dan wél snapt wat de bedoeling is.
- Zoekwaarde: dat is de waarde die je wil gebruiken om andere waarden op te zoeken. Ze moet dus voorkomen in je 2 tabellen (in het voorbeeld van hierboven was dat de productcode)
- Tabelmatrix: dit is een bereik (het bestaat dus uit meerdere kolommen) waarin je zowel de zoekwaarde moet terugvinden, als de waarde die je wil opzoeken (in het voorbeeld hierboven de prijs). Belangrijk is dat die zoekwaarde in de meest linkse kolom moeten zitten. Selecteer dus nauwkeurig je tabel. Onze tabelmatrix moet in het voorbeeld van kolom F tot H gaan.
- Kolomindex getal: dit is een getal dat aangeeft in de hoeveelste kolom in je bereik van het puntje hierboven je antwoord zit. Als je dus bij de tabelmatrix een bereik aanduidt F2:H8, en als je antwoord in kolom H zit, dan is je kolomindex getal 3. H is immers de 3de kolom in je bereik, dat begint bij F.
- Benaderen: wat moet er gebeuren als de zoekwaarde zelf niet letterlijk teruggevonden wordt in de tabelmatrix? Als je hier ONWAAR typt, gaat het systeem op zoek naar exacte overeenkomst. Dat betekent dat je een foutmelding krijgt indien een letterlijke waarde niet wordt teruggevonden. Dat zou je bijvoorbeeld in het voorbeeld hierboven kunnen gebruiken. Een productcode opzoeken die niet bestaat, dat moet een foutmelding opleveren.
Als je hier WAAR typt, doet Excel iets anders wanneer je zoekwaarde niet wordt teruggevonden. In dit geval zie je geen foutmelding, maar wordt er toch iets geselecteerd. Excel selecteert namelijk een rij waarvan de meest linkse cel lijkt op de zoekwaarde. Dat klinkt moeilijk, maar een voorbeeld straks maakt het duidelijk.
VERT.ZOEKEN voorbeeld
Laat ons dat eenvoudige voorbeeld van daarstraks nu eens uitwerken in Excel zelf.
Je wil eerst in C3 de productnaam opzoeken op basis van de productcode.
Wat je dan doet is starten met deze functie te typen in cel C3
=VERT.ZOEKEN(Wat is de zoekwaarde? De productcode natuurlijk. Voor C3 zit die in B3.
=VERT.ZOEKEN(B3;
In welke tabel gaan we die B3 opzoeken? Dat is natuurlijk de tabel F3:H8 (merk op dat je niet alleen kolom F mag selecteren; je bereik moet ook de kolom met de “oplossing” bevatten). Omdat we straks de formule in C3 gaan doorvoeren naar beneden, moeten we natuurlijk hier de celverwijzingen absoluut maken met dollartekens. Anders zou Excel in C4 zoeken in de tabel F4:H9, in C5 in de tabel F5:H10 etc. Dat mogen we niet laten gebeuren. Daarom dus dollartekens.
=VERT.ZOEKEN(B3;$F$3:$H$8;
Voor alle duidelijkheid, als we alleen doorvoeren naar beneden zou een dollarteken voor de rijnummers volstaan, de kolomletters veranderen bij doorvoeren naar beneden immers sowieso niet. In het voorbeeld simpeler te maken, laat ik het zo staan.
Nu tijd voor de kolomindex. In de hoeveelste kolom in mijn tabelmatrix zit het gezochte antwoord? De naam zit in kolom G (de naam), dus de 2de kolom van mijn bereik (F tot H).
=VERT.ZOEKEN(B3;$F$3:$H$8;2;
Wat moet er gebeuren indien een productcode niet gevonden wordt? In dit geval moet er natuurlijk een foutmelding komen. Daarom typ ik hierna ONWAAR. Vervolgens doe je de haakjes toe en kan je de formule doorvoeren naar beneden.
=VERT.ZOEKEN(B3;$F$3:$H$8;2;ONWAAR)
Dat werkt! Nog even doorvoeren tot rij 5 en we zijn klaar.
Probeer nu zelf eens hetzelfde te doen voor de kolom D. Bedoeling is dat je de daar prijs opzoekt op basis van opnieuw de productcode.
Gelukt? Bekijk het resultaat hieronder. Ik heb hier zo weinig mogelijk absoluut gemaakt (enkel de rijen in de tabelmatrix, niet de kolommen) om te tonen hoe dat moet.
Nu je dat allemaal al weet, nog een voorbeeldje met benaderen WAAR. Stel dat je 10% korting wil geven op de producten met een prijs vanaf 10 euro. Ik voegde een klein tabelletje toe met de voorwaarden voor korting, hieronder noem ik dat de kortingtabel.
De bedoeling is dus dat in kolom D de prijs wordt berekend waarin de korting is verrekend.
We zouden dat kunnen oplossen met aan logische functie (ALS), maar we kiezen nu voor verticaal zoeken. Wat we gaan doen is het kortingpercentage opzoeken dat hoort bij de prijs van ons product.
Tip | Waarom kiezen voor Verticaal Zoeken ipv. ALS-functies?
Veel vraagstukken kan je oplossen met zowel een ALS-functie als Verticaal Zoeken. Wanneer je echter meer dan enkele mogelijke uiitkomsten hebt, dan werk je vaak veel efficiënter met Verticaal Zoeken. In een geval met X uitkomsten/gevallen, heb je immers altijd X-1 ALS-functies nodig. Dus een vraag met 2 oplossingen heeft 2-1=1 ALS-functie nodig, terwijl een vraag met 10 uitkomsten/gevallen 10-1=9 ALS-funcies nodig heeft.
De kans dat je in 9 ALS-functies een fout typt, is erg groot. Een oplossing met Verticaal Zoeken heeft echter maar 1 functie nodig. Zo voorkom je dus fouten en maak je formules die beter begrijpbaar/leesbaar zijn.
Anders dan daarnet zullen we nu echter de prijs niet letterlijk terugvinden in de tabelmatrix. Een tandenborstel kost 5 euro, maar 5 vind je niet letterlijk terug in de kortingtabel. We lossen dat op door in het laatste argument van de verticaal zoeken functie nu niet te kiezen voor ONWAAR, maar wel voor WAAR. Als de letterlijke waarde niet in de tabelmatrix gevonden wordt moet het programma de waarde kiezen die voor de zoekwaarde komt. In ons voorbeeld komt de 0 in de tabelmatrix voor de waarde 5. 10 zou immers te veel zijn.
Om die reden vindt Excel de korting voor een product van 5 euro op de rij van 0 euro.
Hetzelfde geldt voor producten die duurder zijn dan 10 euro. Als je benaderen ONWAAR zou gebruiken, dan krijg je een foutmelding bij het kortingpercentage voor de haardrager. 25 staat immers niet letterlijk in de zoekmatrix. Als we WAAR gebruiken daarentegen, selecteert Excel de waarde in de kortingtabel die net kleiner is dan de zoekwaarde. Hier is dat dus 10. En de korting die daarbij hoort in kolom 2 is 10%.
Tip | Sorteer van klein naar groot in de tabelmatrix
Gebruik maken van benaderen WAAR levert enkel voorspelbare resultaten op als de waarden van klein naar groot gesorteerd zijn in je opzoektabel. Daarop moet je dus wel even letten.
Hieronder ga ik ermee aan de slag.
Merk op dat we in het voorbeeld niet gewoon het kortingpercentage opzoeken, maar dat we dat percentage meteen gebruiken om de korting zelf te berekenen. Vandaar gebruiken we natuurlijk verticaal zoeken als een deeltje van een grotere formule.
prijs met korting = prijs zonder korting - kortingpercentage x prijs zonder korting
Voor het kortingpercentage hebben we verticaal zoeken gebruikt.
Snap je het?
HORIZ.ZOEKEN voorbeeld
Horizontaal zoeken werkt volledig op dezelfde manier als verticaal zoeken. Alleen zoek je hier niet in rijen van boven naar beneden, maar wel in kolommen, van links naar rechts.
Stel dat we onder elkaar verschillende verkopers plaatsen en hun omzet.
Bovenaan voegen we een commissietabel toe die weergeeft hoeveel commissie een verkoper krijgt, op basis van de omzet die zij/hij genereert.
Die commissietabel is nu horizontaal georiënteerd, waardoor we moeten zoeken in de kolommen. Waar het bij verticaal zoeken belangrijk was dat de zoekwaarde in de eerste kolom vermeld stonden in de tabelmatrix, is het bij horizontaal zoeken belangrijk dat de zoekwaarde op de eerste rij vindbaar is. Vermits de omzetten bovenaan staan is dat in ons voorbeeld in orde.
We beginnen met eerst een lege cel te selecteren, in dit geval C5. Daar wil ik de commissie berekenen voor Jan. We typen vervolgens = gevolgd door de functie HORIZ.ZOEKEN, en dan geopende haakjes.
=HORIZ.ZOEKEN(Het eerste argument dat we moeten ingeven is de zoekwaarde. In dit geval is dit de verkoop voor Jan. Dus klik cel B5. Typ daarna puntkomma (;) om het volgende argument in te geven.
=HORIZ.ZOEKEN(B5;Voor het volgende argument geven we het bereik in waar de gegevens moeten worden gezocht, dat zijn de waarden in de commissietabel.
Sleep met de muisaanwijzer over het bereik (B1:E2). Druk op F4 tot je overal dollartekens ziet, of maak alleen de rijen absoluut (dat kan omdat we straks immers enkel naar beneden doorvoeren, niet naar links of rechts).
=HORIZ.ZOEKEN(B5;$B$1:$E$2;Voor het volgende argument moeten we het rijnummer ingeven. Dat geeft aan op de hoeveelste rij in onze tabelmatrix de oplossing staat. Dat moet hier de 2de rij zijn.
=HORIZ.ZOEKEN(B5;$B$1:$E$2;2Benaderen zetten we hier op WAAR. Indien een omzetbedrag niet letterlijk in de commissietabel voorkomt, moet Excel de kleinere overeenstemmende waarde nemen.
=HORIZ.ZOEKEN(B5;$B$1:$E$2;2;WAAR)Hoewel onze functie nu volledig is ingevuld, moeten we onze formule nog verder afmaken.
Als we hier zouden stoppen, staat er gewoon 2% of 5%. En dat is niet de bedoeling. Daarom voegen we nog de commissieberekening zelf toe. Dat is eenvoudig want we moeten gewoon het commissiepercentage vermenigvuldigen met de omzet.
=HORIZ.ZOEKEN(B5;$B$1:$E$2;2;WAAR)*B5Klaar. We kunnen dit nu doorvoeren!
Hieronder zie je de volledige oplossing.
X.ZOEKEN: de nieuwe zoekfunctie
Sinds een tijdje zit er een nieuwe zoekfunctie in Excel. Die is veel flexibeler inzetbaar dan verticaal zoeken en horizontaal zoeken, en kan ook gebruikt worden voor zowel verticaal als horizontaal zoeken.
Wat als bijvoorbeeld je bestand zo is opgebouwd dat in je tabelmatrix de zoekwaarde niet in de linkse kolom vindt? Dan kan je traditioneel verticaal zoeken niet gebruiken. Tot voor kort was je dan aangewezen op iets complexere functies als INDEX gecombineerd met VERGELIJKEN, maar nu kan je daarvoor X.ZOEKEN gebruiken.
Ik leg hier niet alle opties en mogelijkheden van X.ZOEKEN uit, wel de basis.
In zijn simpelste vorm heeft deze functie 3 argumenten:
- zoekwaarde: dit is de waarde waarmee je wil opzoeken
- zoeken matrix: dit is de tabel waarin de zoekwaarde wordt opgezocht. De functie telt in de hoeveelste cel in dat bereik de waarde wordt gevonden. Bijvoorbeeld plaats 7.
- retourneren matrix: dit is de tabel waarin de antwoorden staan. De functie telt het hierboven bekomen aantal (7 hierboven) en selecteert dan die cel.
Geloof mij, het spreekt redelijk voor zich. Laat ons dat voorbeeldje van hierboven nog een keertje doen, maar dan met X.ZOEKEN. Stel dat we de prijs willen opzoeken op basis van de productcode.
De zoekwaarde is nu opnieuw de productcode.
X.ZOEKEN(B3;
De zoeken matrix is de kolom waarin al die productcodes zitten. Excel telt in de hoeveelste cel in dat bereik de zoekwaarde wordt teruggevonden. Denk aan absoluut maken (dollartekens).
=X.ZOEKEN(B3;$F$3:$F$8;Vervolgens geef je de retourneren matrix in. Excel gebruikt dan de uitkomst van de zoektocht in de zoeken matrix om de overeenkomstige cel te selecteren in die retourneren matrix. In mensentaal: als de zoekwaarde op de 5de cel, dan selecteer je hier ook de 5de cel (maar dan in een andere kolom of andere rij).
Tussen haakjes: het is zeer belangrijk dat de oriëntatie van de zoeken en retourneren matrix gelijk is. Als je zegt dat je zoeken matrix loopt van F3 tot F8, dan moet je retourneren matrix ook op rij 3 beginnen. Anders klopt je telling natuurlijk niet.
Terug naar ons voorbeeld. De antwoorden vinden we in kolom I, dus onze retourneren matrix bevindt zich ook daar.
=X.ZOEKEN(B3;$F$3:$F$8;$H$3:$H$8)Zo, de basis is nu gelegd. Je kan nog uitbreiden met extra argumenten (zoals je ook WAAR en ONWAAR had bij verticaal zoeken) , maar die laat ik in deze basiscursus even buiten beschouwing. Experimenteer er eens mee, zou ik zeggen!
