Innhold
Vi har nylig dedikert en artikkel til en av de mest nyttige Excel-funksjonene kalt VPR og viste hvordan den kan brukes til å trekke ut nødvendig informasjon fra en database til en regnearkcelle. Vi nevnte også at det er to brukstilfeller for funksjonen VPR og bare en av dem omhandler databasespørringer. I denne artikkelen vil du lære en annen mindre kjent måte å bruke funksjonen på VPR i Excel.
Hvis du ikke har gjort dette ennå, så husk å lese den siste artikkelen om funksjonen VPR, fordi all informasjonen nedenfor forutsetter at du allerede er kjent med prinsippene beskrevet i den første artikkelen.
Når du arbeider med databaser, funksjoner VPR en unik identifikator sendes, som brukes til å identifisere informasjonen vi ønsker å finne (for eksempel en produktkode eller et kundeidentifikasjonsnummer). Denne unike koden må være til stede i databasen, ellers VPR vil rapportere en feil. I denne artikkelen skal vi se på denne måten å bruke funksjonen på VPRnår ID-en ikke finnes i databasen i det hele tatt. Som om funksjonen VPR byttet til omtrentlig modus, og velger hvilke data som skal gi oss når vi vil finne noe. Under visse omstendigheter er dette akkurat det som trengs.
Et eksempel fra livet. Vi satte oppgaven
La oss illustrere denne artikkelen med et eksempel fra virkeligheten – beregning av provisjoner basert på et bredt spekter av salgsverdier. Vi starter med et veldig enkelt alternativ, og så vil vi gradvis komplisere det til den eneste rasjonelle løsningen på problemet er å bruke funksjonen VPR. Det første scenariet for vår fiktive oppgave er som følger: Hvis en selger tjener mer enn $30000 30 i salg i løpet av et år, er provisjonen hans 20%. Ellers er provisjonen bare XNUMX %. La oss sette det i form av en tabell:
Selgeren legger inn sine salgsdata i celle B1, og formelen i celle B2 bestemmer den korrekte provisjonssatsen som selgeren kan forvente. I sin tur brukes den resulterende satsen i celle B3 for å beregne den totale provisjonen selgeren skal motta (bare å multiplisere cellene B1 og B2).
Den mest interessante delen av tabellen er inneholdt i celle B2 - dette er formelen for å bestemme provisjonssatsen. Denne formelen inneholder en Excel-funksjon kalt IF (HVIS). For de lesere som ikke er kjent med denne funksjonen, vil jeg forklare hvordan den fungerer:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Betingelse er et funksjonsargument som tar verdien av enten SANN KODE (SANN), eller FALSK (FALSK). I eksemplet ovenfor, uttrykket B1
Er det sant at B1 er mindre enn B5?
Eller du kan si det annerledes:
Er det sant at det totale salgsbeløpet for året er mindre enn terskelverdien?
Hvis vi svarer på dette spørsmålet JA (TRUE), så returnerer funksjonen verdi hvis sant (verdi hvis TRUE). I vårt tilfelle vil dette være verdien av celle B6, dvs. provisjonssats når totalt salg er under terskelverdien. Hvis vi svarer på spørsmålet NEI (FALSE) returnerer deretter verdi hvis falsk (verdi hvis FALSE). I vårt tilfelle er dette verdien av celle B7, dvs. provisjonssats når totalt salg er over terskelen.
Som du kan se, hvis vi tar det totale salget på $20000, får vi en provisjonssats på 2% i celle B20. Hvis vi angir en verdi på $40000 30, vil provisjonssatsen endres med XNUMX %:
Slik fungerer bordet vårt.
Vi kompliserer oppgaven
La oss gjøre ting litt vanskeligere. La oss sette en annen terskel: hvis selgeren tjener mer enn $40000, øker provisjonssatsen til 40%:
Alt ser ut til å være enkelt og klart, men formelen vår i celle B2 blir merkbart mer komplisert. Hvis du ser nøye på formelen, vil du se det tredje argumentet til funksjonen IF (IF) ble til en annen fullverdig funksjon IF (HVIS). Denne konstruksjonen kalles nesting av funksjoner inn i hverandre. Excel tillater gladelig disse konstruksjonene, og de fungerer til og med, men de er mye vanskeligere å lese og forstå.
Vi vil ikke fordype oss i de tekniske detaljene - hvorfor og hvordan det fungerer, og vi vil ikke gå inn på nyansene ved å skrive nestede funksjoner. Tross alt er dette en artikkel dedikert til funksjonen VPR, ikke en komplett guide til Excel.
Uansett, formelen blir mer komplisert! Hva om vi introduserer et annet alternativ for en provisjonssats på 50 % for de selgerne som tjener mer enn $50000 60000 i salg. Og hvis noen har solgt mer enn $60 XNUMX, vil de betale XNUMX % provisjon?
Nå er formelen i celle B2, selv om den ble skrevet uten feil, blitt helt uleselig. Jeg tror at det er få som ønsker å bruke formler med 4 hekkingsnivåer i sine prosjekter. Det må finnes en enklere måte?!
Og det er en slik måte! Funksjonen vil hjelpe oss VPR.
Vi bruker VLOOKUP-funksjonen for å løse problemet
La oss endre utformingen av bordet vårt litt. Vi vil beholde alle de samme feltene og dataene, men ordne dem på en ny, mer kompakt måte:
Ta et øyeblikk og sørg for det nye bordet Pristabell inkluderer de samme dataene som den forrige terskeltabellen.
Hovedideen er å bruke funksjonen VPR for å bestemme ønsket tollsats i henhold til tabellen Pristabell avhengig av salgsvolum. Vær oppmerksom på at selger kan selge varer for et beløp som ikke er lik en av de fem terskelverdiene i tabellen. For eksempel kunne han selge for $34988, men det er ikke noe slikt beløp. La oss se hvordan funksjonen VPR kan håndtere en slik situasjon.
Sette inn en VLOOKUP-funksjon
Velg celle B2 (hvor vi vil sette inn formelen vår) og finn VLOOKUP (VLOOKUP) i Excel-funksjonsbiblioteket: Formler (formler) > Funksjonsbibliotek (Funksjonsbibliotek) > Oppslag og referanse (Referanser og matriser).
En dialogboks vises Funksjonsargumenter (Funksjonsargumenter). Vi fyller ut verdiene til argumentene én etter én, og starter med Oppslagsverdi (Oppslagsverdi). I dette eksemplet er dette det totale salgsbeløpet fra celle B1. Sett markøren i feltet Oppslagsverdi (Oppslagsverdi) og velg celle B1.
Deretter må du spesifisere funksjonene VPRhvor du skal lete etter data. I vårt eksempel er dette en tabell Pristabell. Sett markøren i feltet Tabell_array (Tabell) og velg hele tabellen Pristabellbortsett fra overskrifter.
Deretter må vi spesifisere hvilken kolonne vi skal trekke ut data fra ved å bruke formelen vår. Vi er interessert i provisjonssatsen, som er i den andre kolonnen i tabellen. Derfor, for argumentasjonen Col_index_num (Column_number) skriv inn verdien 2.
Og til slutt introduserer vi det siste argumentet – Range_lookup (Intervall_oppslag).
Viktig: det er bruken av dette argumentet som utgjør forskjellen mellom de to måtene å bruke funksjonen på VPR. Når du arbeider med databaser, argumentet Range_lookup (range_lookup) må alltid ha en verdi FALSK (FALSE) for å søke etter et eksakt samsvar. I vår bruk av funksjonen VPR, må vi la dette feltet stå tomt, eller angi en verdi SANN KODE (EKTE). Det er ekstremt viktig å velge dette alternativet riktig.
For å gjøre det klarere, vil vi introdusere SANN KODE (TRUE) i feltet Range_lookup (Intervall_oppslag). Selv om du lar feltet stå tomt, vil dette ikke være en feil, siden SANN KODE er standardverdien:
Vi har fylt inn alle parameterne. Nå trykker vi OK, og Excel lager en formel for oss med en funksjon VPR.
Hvis vi eksperimenterer med flere forskjellige verdier for det totale salgsbeløpet, vil vi sørge for at formelen fungerer riktig.
konklusjonen
Når funksjonen VPR fungerer med databaser, argument Range_lookup (range_lookup) må godta FALSK (FALSK). Og verdien angitt som Oppslagsverdi (Oppslagsverdi) må eksistere i databasen. Det er med andre ord ute etter en eksakt match.
I eksemplet vi har sett på i denne artikkelen, er det ikke nødvendig å få et eksakt samsvar. Dette er tilfellet når funksjonen VPR må bytte til omtrentlig modus for å returnere ønsket resultat.
For eksempel: Vi ønsker å bestemme hvilken sats som skal brukes i provisjonsberegningen for en selger med et salgsvolum på $34988. Funksjon VPR gir oss en verdi på 30 %, som er helt riktig. Men hvorfor valgte formelen raden som inneholder nøyaktig 30 % og ikke 20 % eller 40 %? Hva menes med omtrentlig søk? La oss være klare.
Når argumentet Range_lookup (interval_lookup) har en verdi SANN KODE (TRUE) eller utelatt, funksjon VPR går gjennom den første kolonnen og velger den største verdien som ikke overskrider oppslagsverdien.
Viktig poeng: For at denne ordningen skal fungere, må den første kolonnen i tabellen sorteres i stigende rekkefølge.