VLOOKUP v Excelu, 2. del Uporaba VLOOKUP brez baze podatkov

V nedavnem članku smo predstavili funkcijo Excel, imenovano VL in pojasnil, kako se lahko uporablja za pridobivanje informacij iz baze podatkov v celico v lokalnem delovnem listu. V tem članku smo omenili, da sta za uporabo VLOOKUP obstajali dve uporabi, in le ena od njih je obravnavala poizvedbe v podatkovnih bazah. V tem članku, drugem in končnem v seriji VLOOKUP, preučujemo to drugo, manj znano uporabo za funkcijo VLOOKUP..

Če tega še niste storili, preberite prvi članek VLOOKUP - ta članek bo predvideval, da so številni pojmi, pojasnjeni v tem članku, bralcu že znani.

Pri delu s podatkovnimi bazami se VLOOKUP posreduje »edinstveni identifikator«, ki služi za identifikacijo podatkovnih zapisov, ki jih želimo najti v podatkovni bazi (npr. Koda izdelka ali ID stranke). Ta edinstveni identifikator mora obstaja v bazi podatkov, sicer VLOOKUP vrne napako. V tem članku bomo preučili način uporabe VLOOKUP, kjer identifikatorja sploh ni potrebno v bazi podatkov. To je skoraj tako, kot da VLOOKUP lahko sprejme "dovolj blizu je dovolj dober" pristop za vračanje podatkov, ki jih iščemo. V določenih okoliščinah je to natančno kar potrebujemo.

Ta članek bomo ponazorili s primerom iz resničnega sveta - izračunom provizij, ki se ustvarijo na nizu prodajnih številk. Začeli bomo z zelo preprostim scenarijem in ga nato postopoma bolj zapletli, dokler ni edina razumna rešitev problema uporaba VLOOKUP-a. Začetni scenarij v našem fiktivnem podjetju deluje takole: če prodajalec v določenem letu ustvari več kot 30.000 dolarjev prodaje, je provizija, ki jo zaslužijo, 30%. V nasprotnem primeru je njihova provizija le 20%. Doslej je to precej preprost delovni list:

Za uporabo tega delovnega lista prodajalec vnese svoje prodajne številke v celico B1, formula v celici B2 pa izračuna pravilno stopnjo provizije, do katere so upravičeni, ki se uporablja v celici B3 za izračun celotne provizije, ki jo dolguje prodajalec ( je preprosto množenje B1 in B2).

Celica B2 vsebuje edini zanimiv del tega delovnega lista - formulo za odločanje, katero stopnjo provizije uporabiti: eno spodaj praga 30.000 dolarjev ali enega nad praga. Ta formula omogoča uporabo funkcije Excel, ki se imenuje IF. Za tiste bralce, ki niso seznanjeni z IF, deluje tako:

IF (pogoj, vrednost, če je res, vrednost, če je false)

Kje za stanje je izraz, ki se vrednoti prav ali false. V zgornjem primeru je stanje je izraz B1, ki se lahko razume kot »Ali je B1 manjši od B5?« ali, drugače rečeno, »Ali je skupna prodaja nižja od praga«. Če je odgovor na to vprašanje »da« (res), potem uporabimo vrednost, če je res parameter funkcije, namreč B6 v tem primeru - stopnja provizije, če je bila skupna prodaja spodaj praga. Če je odgovor na vprašanje "ne" (false), potem uporabimo vrednost, če je false parameter funkcije, namreč B7 v tem primeru - stopnja provizije, če je bila skupna prodaja nad praga.

Kot lahko vidite, uporaba skupne prodaje v višini 20.000 $ nam daje 20% provizijo v celici B2. Če vnesemo vrednost 40.000 $, dobimo drugačno stopnjo provizije:

Torej naša preglednica deluje.

Naj bo bolj zapleteno. Vnesimo drugi prag: če prodajalec zasluži več kot 40.000 $, se njihova provizija poveča na 40%:

Dovolj preprosto razumeti v resničnem svetu, toda v celici B2 naša formula postaja vse bolj kompleksna. Če pogledate natančno formulo, boste videli, da je tretji parameter izvirne funkcije IF ( vrednost, če je false) je zdaj celotna funkcija IF sama po sebi. To se imenuje a ugnezdena funkcija (funkcija znotraj funkcije). To je popolnoma veljavno v Excelu (celo deluje!), Vendar je težje brati in razumeti.

Ne bomo šli v matice in vijake, kako in zakaj to deluje, niti ne bomo preučili nianse ugnezdenih funkcij. To je vadnica za VLOOKUP, ne pa v Excelu na splošno.

Kakorkoli že, postane še slabše! Kaj pa, če se odločimo, da če zaslužijo več kot 50.000 $, potem imajo pravico do 50% provizije, če pa zaslužijo več kot 60.000 $, potem imajo pravico do 60% provizije.?

Sedaj je formula v celici B2, čeprav je pravilna, postala praktično neberljiva. Nihče ne bi smel napisati formul, kjer so funkcije ugnezdene štiri ravni globoko! Zagotovo mora obstajati enostavnejši način?

Gotovo je. VLOOKUP za reševanje!

Preoblikujmo delovni list malo. Ohranili bomo vse iste številke, vendar jih bomo organizirali na nov način, bolj tabelarno način:

Vzemite si trenutek in se prepričajte, da je nov Tabela cen deluje popolnoma enako kot zgornji niz pragov.

Konceptualno bomo uporabili VLOOKUP za iskanje prodajne prodaje (iz B1) v tabeli cen in vrnitev ustrezne provizije. Upoštevajte, da je prodajalec dejansko ustvaril prodajo, ki je ne ena od petih vrednosti v tabeli cen ($ 0, $ 30,000, $ 40,000, $ 50,000 ali $ 60,000). Morda so ustvarili prodajo v višini 34.988 USD. Pomembno je omeniti, da jih ima 34.988 $ ne v tabeli cen. Poglejmo, ali lahko VLOOKUP vseeno reši naš problem ...

Izberemo celico B2 (mesto, ki jo želimo postaviti v formulo) in nato vstavimo funkcijo VLOOKUP iz Formule zavihek:

The Argumenti funkcije se prikaže polje za VLOOKUP. Argumente (parametre) izpolnjujemo enega za drugim, začenši z Iskanje_vrednost, ki je v tem primeru skupna prodaja iz celice B1. Postavimo kazalec v Iskanje_vrednost in nato enkrat na celico B1:

Nato moramo v VLOOKUP določiti, v katero tabelo iščejo te podatke. V tem primeru je seveda tabela s stopnjami. Postavimo kazalec v Table_array in nato označite celotno tabelo - brez naslovov:

Nato moramo določiti, kateri stolpec v tabeli vsebuje informacije, ki jih želimo, da se nam formula vrne. V tem primeru želimo stopnjo provizije, ki jo najdemo v drugem stolpcu tabele, zato vnesemo 2 v Col_index_num polje:

Na koncu vnesemo vrednost v Razpon_pregleda polje.

Pomembno: Uporaba tega polja razlikuje med obema načinoma uporabe VLOOKUP-a. Če želite uporabiti VLOOKUP z bazo podatkov, ta končni parameter, Razpon_pregleda, mora biti vedno nastavljeno na FALSE, vendar s to drugo uporabo VLOOKUP moramo pustiti prazno ali vnesti vrednost PRAV. Ko uporabljate VLOOKUP, je bistveno, da izberete pravilno končno vrednost.

Če želite biti eksplicitni, bomo vnesli vrednost prav v Razpon_pregleda polje. Prav tako bi bilo dobro, da pustite prazno, ker je to privzeta vrednost:

Izpolnili smo vse parametre. Sedaj kliknemo v redu in Excel za nas pripravi formulo VLOOKUP:

Če eksperimentiramo z nekaj različnimi skupnimi prodajnimi količinami, se lahko prepričamo, da formula deluje.

Zaključek

V "podatkovni" različici VLOOKUP, kjer je Razpon_pregleda parameter je FALSE, vrednost, prenesena v prvem parametru (Iskanje_vrednost) mora v bazi podatkov. Z drugimi besedami, iščemo natančno ujemanje.

Toda v tej drugi uporabi VLOOKUP ne iščemo nujno natančnega ujemanja. V tem primeru je »dovolj blizu dovolj«. Toda kaj mislimo s »dovolj blizu«? Uporabimo primer: ko iščemo stopnjo provizije za skupno prodajo v višini 34.988 $, nam bo formula VLOOKUP vrnila vrednost 30%, kar je pravilen odgovor. Zakaj je izbrala vrstico v tabeli, ki vsebuje 30%? Kaj v tem primeru pomeni »dovolj blizu«? Bodimo natančni:

Kdaj Razpon_pregleda je nastavljeno na PRAV (ali izpuščen), bo VLOOKUP prikazal v stolpcu 1 in se ujemal najvišja vrednost, ki ni večja od. \ t Iskanje_vrednost parameter.

Pomembno je tudi opozoriti, da ta sistem deluje, tabela mora biti razvrščena v naraščajočem vrstnem redu v stolpcu 1!

Če želite vaditi z VLOOKUP, lahko vzorčno datoteko, ki je prikazana v tem članku, prenesete tukaj.