VLOOKUP in Excel, deel 2: VLOOKUP gebruiken zonder een database

In een recent artikel hebben we de Excel-functie VLOOKUP geïntroduceerd en uitgelegd hoe deze kan worden gebruikt om informatie uit een database op te halen in een cel in een lokaal werkblad. In dat artikel hebben we vermeld dat er twee functies voor VLOOKUP waren, en slechts een van hen had te maken met het opvragen van databases. In dit artikel, de tweede en laatste in de VLOOKUP-reeks, onderzoeken we dit andere, minder bekende gebruik voor de functie VERT.ZOEKEN.

Lees het eerste VLOOKUP-artikel als je dit nog niet hebt gedaan - dit artikel gaat ervan uit dat veel van de concepten die in dat artikel worden uitgelegd al bekend zijn bij de lezer.

Bij het werken met databases krijgt VLOOKUP een 'unieke ID' doorgegeven die dient om te identificeren welk gegevensrecord we in de database willen vinden( bijvoorbeeld een productcode of klant-ID).Deze unieke ID moet in de database bestaan, anders geeft VLOOKUP een foutmelding. In dit artikel zullen we een manier onderzoeken om VLOOKUP te gebruiken als de identifier helemaal niet in de database hoeft te bestaan. Het is bijna alsof VERT.ZOEKEN een benadering kan aannemen die "dichtbij genoeg is, goed genoeg" om de gegevens te retourneren waarnaar we op zoek zijn. In bepaalde omstandigheden is dit

precies wat we nodig hebben.

We zullen dit artikel illustreren met een realistisch voorbeeld: het berekenen van de provisies die worden gegenereerd op basis van een reeks verkoopcijfers. We beginnen met een heel eenvoudig scenario en maken het vervolgens complexer, totdat de enige rationele oplossing voor het probleem is om VERT.ZOEKEN te gebruiken. Het beginscenario in ons fictieve bedrijf werkt als volgt: als een verkoper in een bepaald jaar meer dan $ 30.000 aan omzet genereert, is de commissie die zij op die verkopen verdienen 30%.Anders is hun commissie slechts 20%.Tot nu toe is dit een vrij eenvoudig werkblad:

Om dit werkblad te gebruiken, voert de verkoper hun verkoopcijfers in cel B1 in, en de formule in cel B2 berekent de juiste commissie die ze mogen ontvangen, die in cel B3 wordt gebruikt om te berekenende totale commissie die de verkoper verschuldigd is( wat een eenvoudige vermenigvuldiging van B1 en B2 is).

De cel B2 bevat het enige interessante deel van dit werkblad - de formule om te bepalen welk commissietarief moet worden gebruikt: de ene onder , de drempelwaarde van $ 30.000, of de enige boven de drempelwaarde. Deze formule maakt gebruik van de Excel-functie IF .Voor lezers die niet bekend zijn met IF, werkt het als volgt:

IF( -voorwaarde, waarde als waar, waarde als false )

Waar de -voorwaarde een expressie is die evalueert naar true of false .In het bovenstaande voorbeeld is de -voorwaarde de uitdrukking B1 & lt; B5 , die kan worden gelezen als "Is B1 kleiner dan B5?", Of anders gezegd: "Zijn de totale verkopen kleiner dan de drempelwaarde".Als het antwoord op deze vraag 'ja'( waar) is, gebruiken we de -waarde als echte -parameter van de functie, namelijk B6 in dit geval - de provisietarief als het verkooptotaal onder de drempelwaarde was. Als het antwoord op de vraag "nee"( false) is, gebruiken we de -waarde als valse -parameter van de functie, namelijk B7 in dit geval - de provisietarief als het verkooptotaal boven de drempelwaarde was.

Zoals u kunt zien, geeft een verkooptotaal van $ 20.000 ons een commissiepercentage van 20% in cel B2.Als we een waarde van $ 40.000 invoeren, krijgen we een ander commissietarief:

Dus onze spreadsheet werkt.

Laten we het complexer maken. Laten we een tweede drempel introduceren: als de verkoper meer dan $ 40.000 verdient, stijgt hun commissie tot 40%:

Eenvoudig genoeg om te begrijpen in de echte wereld, maar in cel B2 wordt onze formule steeds complexer. Als u de formule goed bekijkt, ziet u dat de derde parameter van de oorspronkelijke ALS-functie( de -waarde als false ) nu een volledige ALS-functie op zich is. Dit wordt een -geneste functie ( een functie binnen een functie) genoemd. Het is perfect geldig in Excel( het werkt zelfs!), Maar het is moeilijker om te lezen en te begrijpen.

We gaan niet in op hoe en waarom dit werkt, noch zullen we de nuances van geneste functies onderzoeken. Dit is een zelfstudie over VERT.ZOEKEN, niet over Excel in het algemeen.

Hoe dan ook, het wordt erger! Wat als we besluiten dat als ze meer dan $ 50.000 verdienen, ze recht hebben op 50% commissie en als ze meer dan $ 60.000 verdienen, hebben ze recht op 60% commissie?

Nu is de formule in cel B2, hoewel correct, vrijwel onleesbaar geworden. Niemand zou formules moeten schrijven waarbij de functies vier niveaus diep zijn genest! Er moet toch een eenvoudiger manier zijn?

Er is zeker. VLOOKUP om te redden!

Laten we het werkblad een beetje herontwerpen. We houden allemaal dezelfde cijfers, maar organiseren het op een nieuwe manier, een meer tabel manier:

Neem even de tijd en controleer voor uzelf dat de nieuwe frequentietabel precies hetzelfde werkt als de bovenstaande reeks drempels.

Conceptueel, wat we gaan doen, is het gebruik van VERT.ZOEKEN om het verkooptotaal van de verkoper op te zoeken( uit B1) in de tarieftabel en ons de overeenkomstige provisietarief terug te geven. Merk op dat de verkoper inderdaad een verkoop heeft aangemaakt die is en niet een van de vijf waarden in de rentetabel( $ 0, $ 30.000, $ 40.000, $ 50.000 of $ 60.000).Ze hebben misschien een omzet van $ 34.988 gecreëerd. Het is belangrijk op te merken dat $ 34.988 niet en in de tarieftabel voorkomt. Laten we kijken of VLOOKUP ons probleem sowieso kan oplossen. ..

We selecteren cel B2( de locatie waar we onze formule willen plaatsen) en voegen dan de functie VERT.ZOEKEN in van de -formules -tab:

De functieargumenten -vak voor VLOOKUP wordt weergegeven. We vullen de argumenten( parameters) één voor één in, te beginnen met de Lookup_value , wat in dit geval het verkooptotaal is van cel B1.We plaatsen de cursor in het Lookup_value -veld en klikken vervolgens eenmaal op cel B1:

. Vervolgens moeten we aan VERT.ZOEKEN opgeven in welke tabel deze gegevens moeten worden opgezocht. In dit voorbeeld is het natuurlijk de tabel met tarieven. We plaatsen de cursor in het veld Tabelmatrix en markeren vervolgens de volledige tarieftabel - met uitzondering van de koppen :

Vervolgens moeten we specificeren welke kolom in de tabel de informatie bevat die we onze formule naar ons terug willen laten komen. In dit geval willen we het commissietarief, dat is te vinden in de tweede kolom in de tabel, dus daarom voeren we een 2 in het veld Col_index_num in:

Tot slot voeren we een waarde in het Range_lookup -veld in.

Belangrijk: Het gebruik van dit veld onderscheidt de twee manieren om VLOOKUP te gebruiken. Om VLOOKUP met een database te gebruiken, moet deze laatste parameter, Range_lookup , altijd worden ingesteld op FALSE , maar met dit andere gebruik van VERT.ZOEKEN, moeten we het leeg laten of een waarde van TRUE invoeren. Wanneer u VLOOKUP gebruikt, is het essentieel dat u de juiste keuze maakt voor deze laatste parameter.

Om expliciet te zijn, zullen we een waarde invoeren van true in het Range_lookup -veld. Het zou ook goed zijn om het leeg te laten, omdat dit de standaardwaarde is:

We hebben alle parameters voltooid. We klikken nu op de OK -knop en Excel bouwt onze VLOOKUP-formule voor ons:

Als we met een paar verschillende verkooptotaalbedragen experimenteren, kunnen we ons ervan overtuigen dat de formule werkt.

Conclusie

In de "database" -versie van VLOOKUP, waarbij de Range_lookup -parameter FALSE is, moet de waarde die wordt doorgegeven in de eerste parameter( Lookup_value ) in de database aanwezig zijn. Met andere woorden, we zijn op zoek naar een exacte match.

Maar in dit andere gebruik van VERT.ZOEKEN zoeken we niet per se naar een exacte overeenkomst. In dit geval is "dichtbij genoeg goed genoeg".Maar wat bedoelen we met "dichtbij genoeg"?Laten we een voorbeeld gebruiken: bij het zoeken naar een commissietarief voor een totaal van $ 34.988, retourneren onze VLOOKUP-formule ons een waarde van 30%, wat het juiste antwoord is. Waarom koos het de rij in de tabel met 30%?Wat betekent "dichtbij genoeg" in dit geval eigenlijk? Laten we precies zijn:

Wanneer Range_lookup is ingesteld op TRUE ( of weggelaten), zal VERT.ZOEKEN in kolom 1 kijken en gelijkstellen aan de hoogste waarde die niet groter is dan , de Lookup_value -parameter.

Het is ook belangrijk op te merken dat voor dit systeem om te werken, de tabel in oplopende volgorde gesorteerd moet worden op kolom 1 !

Als je wilt oefenen met VERT.ZOEKEN, kan het voorbeeldbestand dat in dit artikel wordt geïllustreerd, hier worden gedownload.