1Aug
I en nylig artikkel presenterte vi Excel-funksjonen VLOOKUP , og forklarte hvordan den 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.
Når du arbeider 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
akkurat hva 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:
For å bruke dette regnearket, går selgeren inn i salgstallene i celle B1, og formelen i celle B2 beregner riktig provisjonsrate de har rett til å motta, som brukes i celle B3 for å beregneTotal provisjon som selgeren er skyldig( som er en enkel multiplikasjon av B1 og B2).
Cellen B2 inneholder den eneste interessante delen av dette regnearket - formelen for å bestemme hvilken provisjonsrate som skal brukes: den ene under terskelen på $ 30.000, eller den ene over terskelen. Denne formelen gjør bruk av Excel-funksjonen kalt IF .For de leserne som ikke er kjent med IF, fungerer det slik:
IF( betingelse, verdi hvis sant, verdier hvis falsk )
Hvor tilstand er et uttrykk som evaluerer til enten true eller falsk .I eksemplet ovenfor er -tilstanden uttrykket B1 & lt; B5 , som kan leses som "Er B1 mindre enn B5?", Eller sett på en annen måte, "Er totalsalg mindre enn terskelen".Hvis svaret på dette spørsmålet er "ja"( sant), bruker vi -verdien hvis sann -parameter for funksjonen, nemlig B6 i dette tilfellet - provisjonsraten hvis salgsbeløpet var under terskelen. Hvis svaret på spørsmålet er "nei"( falskt), bruker vi -verdien hvis falsk -parameter for funksjonen, nemlig B7 i dette tilfellet - provisjonsraten hvis salgsbeløpet var over 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:
Så vårt regneark virker.
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, vil du se at den tredje parameteren til den opprinnelige IF-funksjonen( -verdien hvis falsk ) nå er en hel IF-funksjon i sin egen rett. Dette kalles en 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 på 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?
Nå er formelen i celle B2, mens den er riktig, blitt nesten ulestelig. Ingen må skrive formler hvor funksjonene er nestede fire nivåer dypt! Sikkert må det være en enklere måte?
Det er sikkert. VLOOKUP til redning!
La oss redesigne regnearket litt. Vi beholder alle de samme tallene, men organiserer den på en ny måte, en mer tabellformat måte:
Ta et øyeblikk og verifiser for deg selv at den nye Rate Table fungerer nøyaktig det samme som serien av terskler over.
Konseptuelt er det vi skal gjøre, bruk 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 frekvenstabellen( $ 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 viser ikke i hastighetstabellen. La oss se om VLOOKUP kan løse vårt problem uansett. ..
Vi velger celle B2( plasseringen vi ønsker å sette vår formel), og deretter setter du VLOOKUP-funksjonen fra -formler -fanen:
-funksjonargumentene -boksen for VLOOKUP vises. Vi fyller ut argumentene( parametere) en etter en, som starter med Lookup_value , som i dette tilfellet er salgssummen fra celle B1.Vi plasserer markøren i Lookup_value -feltet, og klikker deretter en gang på celle B1:
Deretter må vi spesifisere for å VLOOKUP hvilken tabell for å lete opp disse dataene. I dette eksemplet er det selvfølgelig taksttabellen. Vi plasserer markøren i Table_array -feltet, og merker deretter hele kurstabellen - 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 i Col_index_num -feltet:
Endelig legger vi inn en verdi i Range_lookup -feltet.
Viktig: Det er bruk av dette feltet som skiller de to måtene å bruke VLOOKUP.For å bruke VLOOKUP med en database må denne siste parameteren Range_lookup alltid settes til FALSE , men med denne andre bruken av VLOOKUP må vi enten la den være tom eller taste inn en verdi på TRUE .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 skrive inn en verdi av sant i Range_lookup -feltet. Det ville også være fint å la det stå tomt, da dette er standardverdien:
Vi har fullført alle parametrene. Vi klikker nå OK -knappen, og Excel bygger vår VLOOKUP-formel for oss:
Hvis vi eksperimenterer med noen forskjellige salgsbeløp, kan vi forsikre oss om at formelen fungerer.
Konklusjon
I "database" -versjonen av VLOOKUP, hvor Range_lookup parameteren er FALSE , må verdien som passeres i den første parameteren( Lookup_value ) , være til stede i databasen. Med andre ord, vi leter etter en eksakt kamp.
Men i denne andre bruken av VLOOKUP, ser 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:
Når Range_lookup er satt til TRUE ( eller utelatt), vil VLOOKUP se i kolonne 1 og matche den høyeste verdien som ikke er større enn Lookup_value parameteren.
Det er også viktig å merke seg at for dette systemet å virke, må tabellen sorteres i stigende rekkefølge på kolonne 1 !
Hvis du vil øve med VLOOKUP, kan prøvefilen som er illustrert i denne artikkelen lastes ned herfra.