VLOOKUP i Excel, del 2 Brug VLOOKUP uden database
I en nylig artikel introducerede vi den kaldte Excel-funktion LOPSLAG og forklarede, hvordan det kunne bruges til at hente oplysninger fra en database til en celle i et lokalt regneark. I den artikel nævnte vi, at der var to anvendelser til VLOOKUP, og kun en af dem behandlede forespørgselsdatabaser. I denne artikel, den anden og endelige i VLOOKUP serien, undersøger vi denne anden, mindre kendte brug for VLOOKUP-funktionen.
Hvis du ikke allerede har gjort det, skal du læse den første VLOOKUP artikel - denne artikel vil antage, at mange af de begreber, der er forklaret i den artikel, allerede er kendt for læseren.
Når man arbejder med databaser, er VLOOKUP bestået en "unik identifikator", der tjener til at identificere, hvilken datapost vi ønsker at finde i databasen (fx en produktkode eller kunde-id). Denne unikke identifikator skal findes i databasen, ellers giver VLOOKUP os en fejl. I denne artikel vil vi undersøge en måde at bruge VLOOKUP, hvor identifikatoren ikke behøver at eksistere i databasen overhovedet. Det er næsten som om VLOOKUP kan vedtage en "nær nok er god nok" tilgang til at returnere de data, vi leder efter. Under visse omstændigheder er dette Nemlig hvad vi har brug for.
Vi vil illustrere denne artikel med et eksempelt eksempel - nemlig beregningen af de provisioner, der genereres på et sæt af salgstal. Vi vil starte med et meget simpelt scenario, og derefter gradvis gøre det mere komplekst, indtil den eneste rationelle løsning på problemet er at bruge VLOOKUP. Det oprindelige scenarie i vores fiktive firma virker som dette: Hvis en sælger skaber mere end $ 30.000 værd af salget i et givet år, er provisionen de tjener på det salg 30%. Ellers er deres provisioner kun 20%. Indtil videre er dette et ret simpelt regneark:
For at bruge dette regneark indtaster sælger deres salgstal i celle B1, og formlen i celle B2 beregner den korrekte provisionsrate, de har ret til at modtage, som bruges i celle B3 til at beregne den samlede provision, som sælgeren skylder (hvilket er en simpel multiplikation af B1 og B2).
Cellen B2 indeholder den eneste interessante del af dette regneark - formlen for at bestemme hvilken kommissionsrate, der skal anvendes: den ene under tærsklen på $ 30.000, eller den ene over tærsklen. Denne formel gør brug af den kaldte Excel-funktion HVIS. For de læsere, der ikke er bekendt med IF, fungerer det sådan:
HVIS(tilstand, værdi hvis sand, værdi hvis falsk)
Hvor er tilstand er et udtryk, der vurderer til enten rigtigt eller falsk. I eksemplet ovenfor, den tilstand er udtrykket B1
Som du kan se, ved at bruge et salgstal på $ 20.000 giver vi en kommissionsrate på 20% i celle B2. Hvis vi indtaster en værdi på $ 40.000, får vi en anden provisionsrate:
Så vores regneark virker.
Lad os gøre det mere komplekst. Lad os introducere en anden tærskel: Hvis sælgeren tjener mere end $ 40.000, stiger kommissionsrenten til 40%:
Nem nok til at forstå i den virkelige verden, men i celle B2 bliver vores formel mere kompleks. Hvis du ser nøje på formlen, vil du se, at den tredje parameter i den oprindelige IF-funktion (den værdi hvis falsk) er nu en hel IF-funktion i sig selv. Dette kaldes a nestet funktion (en funktion inden for en funktion). Det er helt gyldigt i Excel (det virker endda!), Men det er sværere at læse og forstå.
Vi vil ikke gå ind i møtrikker og bolte af hvordan og hvorfor det virker, og vi vil heller ikke undersøge nuancer af nestede funktioner. Dette er en vejledning om VLOOKUP, ikke på Excel generelt.
Alligevel bliver det værre! Hvad med, når vi beslutter, at hvis de tjener mere end $ 50.000, så har de ret til 50% provision, og hvis de tjener mere end $ 60.000, har de ret til 60% provision?
Nu er formlen i celle B2, mens den er korrekt, blevet næsten ulæselig. Ingen skal skrive formler, hvor funktionerne er indlejret fire niveauer dybt! Der skal sikkert være en enklere måde?
Der er bestemt. VLOOKUP til redning!
Lad os redesigne regnearket lidt. Vi beholder alle de samme tal, men organiserer det på en ny måde, en mere tabelform vej:
Tag et øjeblik og kontroller for dig selv, at den nye Rate Table fungerer præcis det samme som rækken af tærskler ovenfor.
Konceptuelt er det, hvad vi skal gøre, at bruge VLOOKUP til at slå op sælgerens salgstal (fra B1) i taksttabellen og returnere til os den tilsvarende kommissionsrate. Bemærk, at sælgeren måske har skabt salg, der er ikke en af de fem værdier i sats tabellen ($ 0, $ 30.000, $ 40.000, $ 50.000 eller $ 60.000). De kan have skabt salg på $ 34.988. Det er vigtigt at bemærke, at $ 34.988 gør ikke vises i taksttabellen. Lad os se, om VLOOKUP kan løse vores problem alligevel ...
Vi vælger celle B2 (den placering, vi ønsker at sætte vores formel), og indsæt derefter VLOOKUP-funktionen fra formler fane:
Det Funktionsargumenter boks til VLOOKUP vises. Vi udfylder argumenterne (parametre) en efter en, begyndende med opslagsværdi, hvilket er i dette tilfælde salgssummen fra celle B1. Vi placerer markøren i opslagsværdi felt og klik derefter en gang på celle B1:
Dernæst skal vi angive at VLOOKUP, hvilken tabel der skal søges op i disse data. I dette eksempel er det naturligvis taksttabellen. Vi placerer markøren i tabelmatrix feltet, og marker derefter hele taksttabellen - undtagen overskrifterne:
Dernæst skal vi angive, hvilken kolonne i tabellen indeholder de oplysninger, vi ønsker, at vores formel skal vende tilbage til os. I dette tilfælde ønsker vi kommissionsraten, som findes i den anden kolonne i tabellen, så vi indtaster derfor en 2 ind i Col_index_num Mark:
Endelig indtaster vi en værdi i range_lookup Mark.
Vigtigt: Det er brugen af dette felt, der adskiller de to måder at bruge VLOOKUP på. For at bruge VLOOKUP med en database, denne sidste parameter, range_lookup, skal altid indstilles til FALSK, men med denne anden brug af VLOOKUP må vi enten lade den være tom eller indtaste en værdi af RIGTIGT. Når du bruger VLOOKUP, er det vigtigt, at du foretager det rigtige valg til denne sidste parameter.
For at være eksplicit, vil vi indtaste en værdi på rigtigt i range_lookup Mark. Det ville også være fint at lade det være tomt, da dette er standardværdien:
Vi har gennemført alle parametrene. Vi klikker nu på Okay knappen, og Excel bygger vores VLOOKUP formel for os:
Hvis vi eksperimenterer med et par forskellige salgsbeløb, kan vi forsikre os om, at formlen virker.
Konklusion
I "database" version af VLOOKUP, hvor range_lookup parameter er FALSK, værdien passeret i den første parameter (opslagsværdi) skal være til stede i databasen. Med andre ord søger vi efter et præcist match.
Men i denne anden brug af VLOOKUP søger vi ikke nødvendigvis et nøjagtigt match. I dette tilfælde er "nær nok god nok". Men hvad mener vi med "nær nok"? Lad os bruge et eksempel: Når vi søger efter en provisionssats på et salgstal på $ 34.988, vil vores VLOOKUP formel returnere os en værdi på 30%, hvilket er det rigtige svar. Hvorfor valgte den rækken i bordet indeholdende 30%? Hvad betyder faktisk "nær nok" i dette tilfælde? Lad os være præcise:
Hvornår range_lookup er indstillet til RIGTIGT (eller udeladt), vil VLOOKUP se i kolonne 1 og matche den højeste værdi, der ikke er større end det opslagsværdi parameter.
Det er også vigtigt at bemærke, at for dette system at arbejde, bordet skal sorteres i stigende rækkefølge i kolonne 1!
Hvis du gerne vil øve med VLOOKUP, kan den prøvefil, der er vist i denne artikel, downloades herfra.
