VLOOKUP i Excel, del 2 Bruke VLOOKUP uten database
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 salgsverdi 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 den korrekte provisjonsraten de har rett til å motta, som brukes i celle B3 for å beregne den totale provisjonen som selgeren er skyldig (hvilket er en enkel multiplikasjon av B1 og B2).
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:
HVIS(tilstand, verdi hvis sant, verdien hvis det er feil)
Hvor i tilstand er et uttrykk som vurderer til heller ekte eller falsk. I eksemplet ovenfor er tilstand er uttrykket B1
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, 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?
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 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 for å 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 gjør det ikke vises i sats tabellen. 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 til 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 provisjonsfrekvensen, 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 den være tom 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:
Hvis vi eksperimenterer med noen forskjellige salgsbeløp, kan vi forsikre oss om at formelen virker.
Konklusjon
I "database" -versjonen av VLOOKUP, hvor range_lookup parameteren er FALSK, verdien 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, 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 EKTE (eller utelatt), vil VLOOKUP se i kolonne 1 og matche den høyeste verdien som ikke er større enn de søkeverdi parameter.
Det er også viktig å merke seg at dette systemet skal 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.
