Indhold
Vi har for nylig dedikeret en artikel til en af de mest nyttige Excel-funktioner kaldet VPR og viste, hvordan det kan bruges til at udtrække de nødvendige oplysninger fra en database til en arbejdsarkcelle. Vi nævnte også, at der er to use cases for funktionen VPR og kun én af dem omhandler databaseforespørgsler. I denne artikel vil du lære en anden mindre kendt måde at bruge funktionen på VPR i Excel.
Hvis du ikke har gjort dette endnu, så sørg for at læse den sidste artikel om funktionen VPR, fordi alle oplysningerne nedenfor forudsætter, at du allerede er bekendt med principperne beskrevet i den første artikel.
Når du arbejder med databaser, funktioner VPR der videregives en unik identifikator, som bruges til at identificere den information, vi ønsker at finde (f.eks. en produktkode eller et kundeidentifikationsnummer). Denne unikke kode skal ellers være til stede i databasen VPR vil rapportere en fejl. I denne artikel vil vi se på denne måde at bruge funktionen på VPRnår id'et slet ikke findes i databasen. Som om funktionen VPR skiftet til den omtrentlige tilstand, og vælger, hvilke data der skal give os, når vi vil finde noget. Under visse omstændigheder er det præcis, hvad der er brug for.
Et eksempel fra livet. Vi stiller opgaven
Lad os illustrere denne artikel med et eksempel fra det virkelige liv – beregning af provision baseret på en bred vifte af salgsmålinger. Vi starter med en meget simpel mulighed, og derefter vil vi gradvist komplicere den, indtil den eneste rationelle løsning på problemet er at bruge funktionen VPR. Det indledende scenarie for vores fiktive opgave er som følger: Hvis en sælger tjener mere end $30000 i salg på et år, så er hans kommission 30%. Ellers er provisionen kun 20%. Lad os sætte det i form af en tabel:
Sælgeren indtaster deres salgsdata i celle B1, og formlen i celle B2 bestemmer den korrekte kommissionssats, som sælgeren kan forvente. Til gengæld bruges den resulterende sats i celle B3 til at beregne den samlede kommission, sælgeren skal modtage (simpelthen multiplicerer cellerne B1 og B2).
Den mest interessante del af tabellen er indeholdt i celle B2 - dette er formlen til at bestemme provisionssatsen. Denne formel indeholder en Excel-funktion kaldet IF (HVIS). For de læsere, der ikke er bekendt med denne funktion, vil jeg forklare, hvordan den fungerer:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Betingelse er et funktionsargument, der tager værdien af enten SAND KODE (SAND), eller FALSK (FALSK). I eksemplet ovenfor er udtrykket B1
Er det rigtigt, at B1 er mindre end B5?
Eller du kan sige det anderledes:
Er det rigtigt, at det samlede salgsbeløb for året er mindre end tærskelværdien?
Hvis vi besvarer dette spørgsmål JA (TRUE), så vender funktionen tilbage værdi, hvis den er sand (værdi hvis SAND). I vores tilfælde vil dette være værdien af celle B6, dvs. provisionssats, når det samlede salg er under tærsklen. Hvis vi svarer på spørgsmålet INGEN (FALSK) vender derefter tilbage værdi hvis falsk (værdi hvis FALSK). I vores tilfælde er dette værdien af celle B7, dvs. provisionssats, når det samlede salg er over tærsklen.
Som du kan se, hvis vi tager det samlede salg på $20000, får vi en provisionssats på 2% i celle B20. Hvis vi indtaster en værdi på $40000, ændres kommissionssatsen med 30%:
Sådan fungerer vores bord.
Vi komplicerer opgaven
Lad os gøre tingene lidt sværere. Lad os sætte en anden tærskel: hvis sælgeren tjener mere end 40000 $, så stiger provisionssatsen til 40%:
Alt ser ud til at være enkelt og klart, men vores formel i celle B2 bliver mærkbart mere kompliceret. Hvis du ser nærmere på formlen, vil du se, at funktionens tredje argument IF (IF) forvandlet til en anden fuldgyldig funktion IF (HVIS). Denne konstruktion kaldes indlejring af funktioner ind i hinanden. Excel tillader heldigvis disse konstruktioner, og de virker endda, men de er meget sværere at læse og forstå.
Vi vil ikke dykke ned i de tekniske detaljer - hvorfor og hvordan det virker, og vi vil ikke gå ind i nuancerne ved at skrive indlejrede funktioner. Dette er trods alt en artikel dedikeret til funktionen VPR, ikke en komplet guide til Excel.
Uanset hvad, bliver formlen mere kompliceret! Hvad hvis vi introducerer en anden mulighed for en kommissionssats på 50 % for de sælgere, der tjener mere end 50000 USD i salg. Og hvis nogen har solgt mere end $60000, vil de så betale 60% kommission?
Nu er formlen i celle B2, selvom den er skrevet uden fejl, blevet fuldstændig ulæselig. Jeg tror, at der er få, der ønsker at bruge formler med 4 niveauer af nesting i deres projekter. Der må være en nemmere måde?!
Og der er sådan en måde! Funktionen vil hjælpe os VPR.
Vi anvender VLOOKUP-funktionen til at løse problemet
Lad os ændre designet på vores bord lidt. Vi vil beholde alle de samme felter og data, men arrangere dem på en ny, mere kompakt måde:
Brug et øjeblik og sørg for det nye bord Rate Tabel indeholder de samme data som den foregående tærskeltabel.
Hovedideen er at bruge funktionen VPR at bestemme den ønskede takst i henhold til tabellen Rate Tabel afhængig af salgsvolumen. Vær opmærksom på, at sælger kan sælge varer for et beløb, der ikke svarer til en af de fem tærskler i tabellen. For eksempel kunne han sælge for $34988, men der er ikke et sådant beløb. Lad os se, hvordan funktionen VPR kan håndtere sådan en situation.
Indsættelse af en VLOOKUP-funktion
Vælg celle B2 (hvor vi vil indsætte vores formel) og find LOPSLAG (VLOOKUP) i Excel-funktionsbiblioteket: formler (formler) > Funktionsbibliotek (Funktionsbibliotek) > Opslag og reference (Referencer og arrays).
En dialogboks vises Funktionsargumenter (Funktionsargumenter). Vi udfylder værdierne af argumenterne én efter én, begyndende med Opslagsværdi (Opslagsværdi). I dette eksempel er dette den samlede mængde salg fra celle B1. Sæt markøren i feltet Opslagsværdi (Opslagsværdi) og vælg celle B1.
Dernæst skal du specificere funktionerne VPRhvor man skal lede efter data. I vores eksempel er dette en tabel Rate Tabel. Sæt markøren i feltet Tabelarray (Tabel) og vælg hele tabellen Rate Tabelundtagen overskrifter.
Dernæst skal vi angive, hvilken kolonne der skal udtrækkes data fra ved hjælp af vores formel. Vi er interesserede i provisionssatsen, som er i den anden kolonne i tabellen. Derfor for argumentet Col_index_num (Column_number) indtast værdien 2.
Og til sidst introducerer vi det sidste argument – Range_lookup (Interval_opslag).
Vigtig: det er brugen af dette argument, der gør forskellen mellem de to måder at anvende funktionen på VPR. Når du arbejder med databaser, er argumentet Range_lookup (range_lookup) skal altid have en værdi FALSK (FALSK) for at søge efter et nøjagtigt match. I vores brug af funktionen VPR, skal vi lade dette felt stå tomt, eller indtaste en værdi SAND KODE (RIGTIGT). Det er ekstremt vigtigt at vælge denne mulighed korrekt.
For at gøre det klarere vil vi introducere SAND KODE (TRUE) i marken Range_lookup (Interval_opslag). Selvom du lader feltet stå tomt, vil dette ikke være en fejl, da SAND KODE er dens standardværdi:
Vi har udfyldt alle parametre. Nu trykker vi OK, og Excel opretter en formel til os med en funktion VPR.
Hvis vi eksperimenterer med flere forskellige værdier for det samlede salgsbeløb, så vil vi sikre os, at formlen fungerer korrekt.
Konklusion
Når funktionen VPR arbejder med databaser, argument Range_lookup (range_lookup) skal acceptere FALSK (FALSK). Og værdien indtastet som Opslagsværdi (Opslagsværdi) skal eksistere i databasen. Med andre ord leder den efter et nøjagtigt match.
I eksemplet, vi har set på i denne artikel, er der ingen grund til at få et nøjagtigt match. Dette er tilfældet, når funktionen VPR skal skifte til omtrentlig tilstand for at returnere det ønskede resultat.
For eksempel: Vi ønsker at bestemme, hvilken sats der skal bruges i provisionsberegningen for en sælger med en salgsmængde på 34988 USD. Fungere VPR giver os en værdi på 30 %, hvilket er helt korrekt. Men hvorfor valgte formlen den række, der indeholder præcis 30 % og ikke 20 % eller 40 %? Hvad menes med omtrentlig søgning? Lad os være klare.
Når argumentet Range_lookup (interval_lookup) har en værdi SAND KODE (TRUE) eller udeladt, funktion VPR gentager den første kolonne og vælger den største værdi, der ikke overstiger opslagsværdien.
Vigtigt punkt: For at dette skema skal fungere, skal den første kolonne i tabellen sorteres i stigende rækkefølge.