I en nylig artikkel presenterte vi Excel-funksjonen som ble kalt VLOOKUP og forklarte hvordan det kunne brukes til å hente informasjon fra en database til en celle i et lokalt regneark. I den artikkelen nevnte vi at det var to bruksområder for VLOOKUP, og bare en av dem jobbet med spørringsdatabaser. I denne artikkelen, den andre og endelige i VLOOKUP-serien, undersøker vi denne andre, mindre kjente bruken for VLOOKUP-funksjonen.
Hvis du ikke allerede har gjort det, vennligst les den første VLOOKUP-artikkelen - denne artikkelen vil anta at mange av konseptene som er forklart i den artikkelen, allerede er kjent for leseren.
Ved arbeid med databaser, er VLOOKUP bestått en "unik identifikator" som tjener til å identifisere hvilken dataregistrering vi ønsker å finne i databasen (for eksempel en produktkode eller kunde-ID). Denne unike identifikatoren må finnes i databasen, ellers returnerer VLOOKUP oss en feil. I denne artikkelen vil vi undersøke en måte å bruke VLOOKUP der identifikatoren ikke trenger å eksistere i databasen i det hele tatt. Det er nesten som om VLOOKUP kan vedta en "nær nok er god nok" tilnærming til å returnere dataene vi leter etter. Under visse omstendigheter er dette nøyaktig det vi trenger.
Vi vil illustrere denne artikkelen med et ekteeksempel - det å beregne provisjonene som genereres på et sett med salgstall. Vi starter med et veldig enkelt scenario, og gjør det gradvis mer komplekst, til den eneste rasjonelle løsningen på problemet er å bruke VLOOKUP. Det opprinnelige scenariet i vårt fiktive selskap virker som dette: Hvis en selger skaper mer enn $ 30.000 i salget i et gitt år, er provisjonen de tjener på dette salget 30%. Ellers er deres provisjon bare 20%. Så langt er dette et ganske enkelt regneark:
Cellen B2 inneholder den eneste interessante delen av dette regnearket - formelen for å bestemme hvilken provisjonsgrad som skal brukes: den ene under terskelen på $ 30.000, eller den ene ovenfor terskelen. Denne formelen gjør bruk av Excel-funksjonen som kalles HVIS. For de leserne som ikke er kjent med IF, virker det slik:
IF(condition,value if true,value if false)
Hvor i tilstand er et uttrykk som evaluerer til heller ekte eller falsk. I eksemplet ovenfor, er tilstand er uttrykket B1, som kan leses som "Er B1 mindre enn B5?", eller sett på en annen måte, "Er det totale salget mindre enn terskelen". Hvis svaret på dette spørsmålet er "ja" (sant), bruker vi verdi hvis sant parameter av funksjonen, nemlig B6 i dette tilfellet - provisjonsraten dersom salgsbeløpet var under terskelen. Hvis svaret på spørsmålet er "nei" (falskt), bruker vi verdien hvis det er feil parameter av funksjonen, nemlig B7 i dette tilfellet - provisjonsraten dersom salgsbeløpet var ovenfor terskelen.
Som du kan se, bruker en salgssum på $ 20 000 oss en provisjonsgrad på 20% i celle B2. Hvis vi oppgir en verdi på $ 40.000, får vi en annen provisjonsrate:
La oss gjøre det mer komplekst. La oss introdusere en annen grense: Hvis selgeren tjener mer enn $ 40.000, øker provisjonsraten til 40%:
Enkel nok til å forstå i den virkelige verden, men i celle B2 blir vår formel mer kompleks. Hvis du ser nøye på formelen, ser du at den tredje parameteren til den opprinnelige IF-funksjonen (den verdien hvis det er feil) er nå en hel IF-funksjon i seg selv. Dette kalles a nestet funksjon (en funksjon i en funksjon). Det er helt gyldig i Excel (det virker også!), Men det er vanskeligere å lese og forstå.
Vi skal ikke gå inn i mutter og bolter av hvordan og hvorfor dette virker, og vi vil heller ikke undersøke nyansene til nestede funksjoner. Dette er en veiledning på VLOOKUP, ikke på Excel generelt.
Uansett blir det verre! Hva med når vi bestemmer at hvis de tjener mer enn $ 50 000, har de rett til 50% provisjon, og hvis de tjener mer enn $ 60 000, har de rett til 60% provisjon?
Det er sikkert. VLOOKUP til redning!
La oss redesigne regnearket litt. Vi beholder alle de samme tallene, men organiserer det på en ny måte, en mer tabell vei:
Ta et øyeblikk og bekreft selv at den nye Rate Table virker nøyaktig det samme som serien av terskler over.
Konseptuelt, hva vi skal gjøre, er å bruke VLOOKUP til å se opp selgerens salgssum totalt (fra B1) i satsen og returnere til oss tilsvarende provisjonsraten. Merk at selgeren kanskje har opprettet salg som er ikke en av de fem verdiene i prisbordet ($ 0, $ 30.000, $ 40.000, $ 50.000 eller $ 60.000). De kan ha opprettet salg på $ 34,988. Det er viktig å merke seg at $ 34,988 gjør ikke vises i hastighetstabellen. La oss se om VLOOKUP kan løse problemet vårt uansett …
Vi velger celle B2 (plasseringen vi ønsker å sette vår formel), og deretter sette VLOOKUP-funksjonen fra formler tab:
De Funksjonsargumenter boksen for VLOOKUP vises. Vi fyller ut argumentene (parametere) en etter en, begynner med søkeverdi, som i dette tilfellet er salgssummen fra celle B1. Vi plasserer markøren i søkeverdi feltet og klikk deretter en gang på celle B1:
Deretter må vi spesifisere for å VLOOKUP hvilken tabell du skal se etter i disse dataene. I dette eksemplet er det selvfølgelig taksttabellen. Vi plasserer markøren i tabell feltet, og merk deretter hele taksttabellen - unntatt overskriftene:
Deretter må vi spesifisere hvilken kolonne i tabellen inneholder den informasjonen vi ønsker at vår formel skal returnere til oss. I dette tilfellet ønsker vi provisjonsraten, som finnes i den andre kolonnen i tabellen, så vi legger derfor inn en 2 inn i det Col_index_num felt:
Til slutt legger vi inn en verdi i range_lookup felt.
Viktig: Det er bruk av dette feltet som skiller de to måtene å bruke VLOOKUP. For å bruke VLOOKUP med en database, denne siste parameteren, range_lookup, må alltid settes til FALSK, men med denne andre bruken av VLOOKUP må vi enten la det være tomt eller skrive inn en verdi på EKTE. Når du bruker VLOOKUP, er det viktig at du gjør det riktige valget for denne siste parameteren.
For å være eksplisitt, vil vi legge inn en verdi på ekte i range_lookup felt. Det ville også være fint å la det være tomt, da dette er standardverdien:
Vi har fullført alle parametrene. Vi klikker nå på OK knappen, og Excel bygger vår VLOOKUP formel for oss:
Konklusjon
I "database" -versjonen av VLOOKUP, hvor range_lookup parameteren er FALSK, verdien som ble passert i den første parameteren (søkeverdi) må være til stede i databasen. Med andre ord, vi leter etter en eksakt kamp.
Men i denne andre bruken av VLOOKUP, søker vi ikke nødvendigvis etter en eksakt kamp. I dette tilfellet er "nær nok god nok". Men hva mener vi med "nær nok"? La oss bruke et eksempel: Når vi søker etter en provisjonsrate på et salg på totalt 34,988 dollar, vil vår VLOOKUP formel returnere oss en verdi på 30%, som er det riktige svaret. Hvorfor valgte det raden i bordet som inneholdt 30%? Hva betyr faktisk "nær nok" i dette tilfellet? La oss være presise:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Det er også viktig å merke seg at for dette systemet å fungere, Tabellen må sorteres i stigende rekkefølge i kolonne 1!
Hvis du vil øve med VLOOKUP, kan prøvefilen som er illustrert i denne artikkelen lastes ned herfra.
