VLOOKUP in Excel, deel 2 VLOOKUP gebruiken zonder een database
In een recent artikel hebben we de Excel-functie genoemd VLOOKUP en legde uit hoe het 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 gebruiksmogelijkheden zijn voor VLOOKUP, en dat slechts één ervan query's gebruikte voor databases. In dit artikel, de tweede en laatste in de VLOOKUP-reeks, onderzoeken we dit andere, minder bekende gebruik voor de functie VERT.ZOEKEN.
Als je dit nog niet hebt gedaan, lees dan het eerste VLOOKUP-artikel - 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" die dient om te identificeren welk gegevensrecord we in de database willen vinden (bijvoorbeeld een productcode of klant-ID). Deze unieke ID moet bestaan in de database, 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 van "dichtbij genoeg is goed genoeg" kan aannemen voor het retourneren van de gegevens waarnaar we op zoek zijn. In bepaalde omstandigheden is dit zo 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 wordt gebruikt in cel B3 om de totale commissie te berekenen die de verkoper verschuldigd is (die is een eenvoudige vermenigvuldiging van B1 en B2).
De cel B2 bevat het enige interessante deel van dit werkblad - de formule om te bepalen welk commissietarief moet worden gebruikt: het ene beneden de drempel van $ 30.000, of die bovenstaande de drempel. Deze formule maakt gebruik van de Excel-functie genaamd ALS. Voor lezers die niet bekend zijn met IF, werkt het als volgt:
ALS(voorwaarde, waarde indien waar, waarde indien onwaar)
Waar de staat is een expressie die voor beide evalueert waar of vals. In het bovenstaande voorbeeld, de staat is de uitdrukking B1
Zoals je ziet, geeft een verkooptotaal van $ 20.000 ons een commissie 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 goed naar de formule kijkt, ziet u dat de derde parameter van de oorspronkelijke ALS-functie (de waarde als false) is nu een volledige ALS-functie op zichzelf. Dit wordt een a genoemd geneste functie (een functie binnen een functie). 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 juist, vrijwel onleesbaar geworden. Niemand zou formules moeten schrijven waarin de functies vier niveaus diep zijn genest! Er moet toch een eenvoudiger manier zijn?
Dat is het zeker. VLOOKUP om te redden!
Laten we het werkblad een beetje herontwerpen. We houden allemaal dezelfde cijfers, maar organiseren het op een nieuwe manier, meer tabellarisch manier:
Neem een moment en controleer voor jezelf het nieuwe Tarieftabel werkt precies hetzelfde als de bovenstaande reeks drempels.
Wat we nu gaan doen, is VLOOKUP gebruiken om het verkooptotaal van de verkoopmedewerker op te vragen (uit B1) in de tarieftabel en ons de overeenkomstige provisietarief terug te geven. Merk op dat de verkoper inderdaad verkopen heeft gecreëerd die dat wel zijn niet een van de vijf waarden in de tarieftabel ($ 0, $ 30.000, $ 40.000, $ 50.000 of $ 60.000). Ze hebben misschien een omzet van $ 34.988 gecreëerd. Het is belangrijk om op te merken dat $ 34.988 dat doet niet verschijnen in de tarieftabel. Laten we kijken of VLOOKUP ons probleem toch kan oplossen ...
We selecteren cel B2 (de locatie waarop we onze formule willen plaatsen) en voegen de functie VERT.ZOEKEN in vanuit de formules tab:
De Functieargumenten kader voor VERT.ZOEKEN verschijnt. We vullen de argumenten (parameters) één voor één in, te beginnen met de Opzoekwaarde, dat is in dit geval het verkooptotaal van cel B1. We plaatsen de cursor in de Opzoekwaarde veld en klik dan eenmaal op cel B1:
Vervolgens moeten we aan de hand van VERT.ZOEKEN opgeven naar welke tabel deze gegevens moeten worden gezocht. In dit voorbeeld is dit natuurlijk de tabel met tarieven. We plaatsen de cursor in de table_array veld en markeer vervolgens de volledige tarieftabel - exclusief de rubrieken:
Vervolgens moeten we specificeren welke kolom in de tabel de informatie bevat die we onze formule willen teruggeven. In dit geval willen we de provisietarief, die is te vinden in de tweede kolom in de tabel, dus daarom voeren we een 2 in de kolomindex_getal veld:
Eindelijk voeren we een waarde in in range_lookup veld-.
Belangrijk: het gebruik van dit veld onderscheidt de twee manieren om VLOOKUP te gebruiken. Om VERT.ZOEKEN te gebruiken met een database, deze laatste parameter, range_lookup, moet altijd worden ingesteld op VALSE, maar met dit andere gebruik van VERT.ZOEKEN, moeten we het leeg laten of een waarde invoeren van TRUE. 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 van invoeren waar in de range_lookup veld. Het zou ook prima zijn om het leeg te laten, omdat dit de standaardwaarde is:
We hebben alle parameters voltooid. We klikken nu op 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, waar de range_lookup parameter is VALSE, de waarde die is doorgegeven in de eerste parameter (Opzoekwaarde) moet aanwezig zijn in de database. Met andere woorden, we zijn op zoek naar een exacte overeenkomst.
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 commissie op een totaalbedrag van $ 34.988, retourneren onze VLOOKUP-formule ons een waarde van 30%, wat het juiste antwoord is. Waarom heeft het de rij in de tabel met 30% gekozen? Wat betekent in feite 'dichtbij genoeg' in dit geval? Laten we precies zijn:
Wanneer range_lookup ingesteld op TRUE (of weggelaten), VLOOKUP kijkt in kolom 1 en overeenkomst de hoogste waarde die niet groter is dan de Opzoekwaarde parameter.
Het is ook belangrijk om op te merken dat dit systeem werkt, de tabel moet in oplopende volgorde in kolom 1 worden gesorteerd!
Als je wilt oefenen met VERT.ZOEKEN, kan het voorbeeldbestand dat in dit artikel wordt geïllustreerd, hier worden gedownload.
