1Aug
I en ny artikel introducerede vi Excel-funktionen VLOOKUP og forklarede, hvordan den kunne bruges til at hente information 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 artiklen, allerede er kendt for læseren.
Når du 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 eksistere 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
præcis , 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 gradvist 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 således: Hvis en sælger skaber mere end $ 30.000 værd af salget i et givet år, er provisionen de tjener på disse salg 30%.Ellers er deres provisioner kun 20%.Hidtil 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 procentsats, de har ret til at modtage, som bruges i celle B3 til at beregneDen 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 Excel-funktionen kaldet IF .For de læsere, der ikke er bekendt med IF, virker det som dette:
IF( betingelse, værdi hvis true, værdi hvis falsk )
Hvor betingelsen er et udtryk, der vurderer til enten ægte eller falsk .I eksemplet ovenfor er -tilstanden udtrykket B1 & B5 , som kan læses som "Er B1 mindre end B5?" Eller, på anden måde, "Er det samlede salg mindre end tærsklen".Hvis svaret på dette spørgsmål er "ja"( sandt), så bruger vi -værdien, hvis true -parameter for funktionen, nemlig B6 i dette tilfælde - kommissionsraten, hvis salgsbeløbet var under tærsklen. Hvis svaret på spørgsmålet er "nej"( falsk), så bruger vi værdien hvis falsk parameter af funktionen, nemlig B7 i dette tilfælde - kommissionsraten hvis salgsbeløbet var over tærskelen.
Som du kan se, giver vi en salgspris på 20% i celle B2 ved hjælp af et salgssum på $ 20.000.Hvis vi indtaster en værdi på $ 40.000, får vi en anden procentsats:
Så vores regneark virker.
Lad os gøre det mere komplekst. Lad os introducere en anden tærskel: Hvis sælgerne tjener mere end $ 40.000, stiger deres provisionssats til 40%:
Nem nok til at forstå i den virkelige verden, men i celle B2 bliver vores formel mere kompleks. Hvis du ser tæt på formlen, vil du se, at den tredje parameter i den oprindelige IF-funktion( -værdien hvis falsk ) nu er en hel IF-funktion i sig selv. Dette kaldes en indlejret funktion ( en funktion inden for en funktion).Det er helt gyldigt i Excel( det virker også!), Men det er sværere at læse og forstå.
Vi går ikke ind i møtrikker og bolte af hvordan og hvorfor dette virker, og vi vil heller ikke undersøge nuancer af nestede funktioner. Dette er en vejledning om VLOOKUP, ikke på Excel generelt.
Anyway, det bliver 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, så 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! Sikkert må der 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 den på en ny måde, en mere tabelformat måde:
Tag et øjeblik og kontroller for dig selv, at den nye Rate Table virker nøjagtig den samme som rækken af tærskler ovenfor.
Konceptuelt er det, hvad vi skal gøre, at bruge VLOOKUP til at opsøge sælgerens salgstal( fra B1) i taksttabellen og returnere til os tilsvarende provisionsfrekvens. Bemærk, at sælgeren muligvis faktisk har oprettet salg, der er , ikke , en af de fem værdier i taksttabellen( $ 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 viser ikke 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 -formlerne -fanen:
Funktionsargumenter -boksen til VLOOKUP vises. Vi udfylder argumenterne( parametre) en efter en, der starter med Lookup_value , hvilket er i dette tilfælde salgssummen fra celle B1.Vi placerer markøren i Lookup_value feltet og klikker så en gang på celle B1:
Næste skal vi angive for at VLOOKUP, hvilken tabel der skal søges op i disse data. I dette eksempel er det naturligvis taksttabellen. Vi placerer markøren i Table_array feltet og markerer derefter hele taksttabellen - eksklusive overskrifterne :
Næste 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 i Col_index_num feltet:
Endelig indtaster vi en værdi i Range_lookup feltet.
Vigtigt: Det er brugen af dette felt, som adskiller de to måder at bruge VLOOKUP på.For at bruge VLOOKUP med en database skal denne sidste parameter, Range_lookup , altid indstilles til FALSE , men med denne anden brug af VLOOKUP må vi enten lade den være tom eller indtaste en værdi af TRUE .Når du bruger VLOOKUP, er det vigtigt, at du foretager det rigtige valg til denne endelige parameter.
For at være eksplicit, vil vi indtaste en værdi af ægte i Range_lookup feltet. 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å OK -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" -versionen af VLOOKUP, hvor Range_lookup parameteren er FALSE , skal værdien, der passeres i den første parameter( Lookup_value ) , 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 rækken i bordet indeholdende 30%?Hvad betyder faktisk "nær nok" i dette tilfælde? Lad os være præcise:
Når Range_lookup er indstillet til TRUE ( eller udelades), vil VLOOKUP se i kolonne 1 og matche den højeste værdi, der ikke er større end Lookup_value parameteren.
Det er også vigtigt at bemærke, at for dette system at arbejde, skal tabellen sorteres i stigende rækkefølge på kolonne 1 !
Hvis du gerne vil øve med VLOOKUP, kan den prøvefil, der er vist i denne artikel, downloades herfra.
