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

V nedavnem članku smo uvedli funkcijo Excel, imenovano VLOOKUP , in pojasnila, kako bi jo lahko uporabili za pridobivanje podatkov iz baze podatkov v celico v lokalnem delovnem listu. V tem članku smo omenili, da sta za VLOOKUP obstajala dve različici, samo ena od njih pa se je ukvarjala s poizvedovanjem baz podatkov. V tem članku, drugi in zadnji v seriji VLOOKUP, preučujemo to drugo, manj znano uporabo za funkcijo VLOOKUP.

Če tega še niste storili, prosimo, preberite prvi članek VLOOKUP - ta članek bo domneval, da je veliko pojmov, pojasnjenih v tem članku, že znano bralcu.

Pri delu s podatkovnimi bazami se VLOOKUP prenese "enolični identifikator", ki služi za prepoznavanje podatkovnega zapisa, ki ga želimo najti v bazi podatkov( npr. Koda izdelka ali ID stranke).Ta enolični identifikator mora obstajati v bazi podatkov, sicer VLOOKUP nam vrne napako. V tem članku bomo preučili način uporabe VLOOKUP-a, kjer identifikator sploh ni potreben v bazi podatkov. Zdi se, kot da bi lahko VLOOKUP sprejel pristop "dovolj blizu je dovolj dober" za vrnitev podatkov, ki jih iščemo. V določenih okoliščinah je to

natančno , kar potrebujemo.

Ta članek bomo ponazorili z dejanskim primerom - računanjem provizij, ustvarjenih na nizu prodajnih podatkov. Začeli bomo z zelo preprostim scenarijem in nato postopoma postali bolj zapleteni, dokler ni edina racionalna rešitev problema uporaba VLOOKUP-a. Začetni scenarij v naši izmišljeni družbi deluje takole: če prodajalec ustvari več kot 30.000 dolarjev prodaje v danem letu, je provizija, ki jo zaslužijo pri tej prodaji, 30%.V nasprotnem primeru je njihova provizija le 20%.Do sedaj je precej preprost delovni list:

Za uporabo tega delovnega lista prodajalec v svoje celice B1 vnese svoje prodajne številke, formula v celici B2 pa izračuna pravilno točko provizije, ki jo lahko prejmejo, kar se uporablja v celici B3 za izračunskupna provizija, ki jo dolguje prodajalec( to je preprosto množenje B1 in B2).

Celica B2 vsebuje edini zanimiv del tega delovnega lista - formula za odločanje, katere stopnje provizije uporabite: eno pod prag 30.000 USD ali en nad prag. Ta formula uporablja funkcijo Excel, imenovano IF .Za tiste bralce, ki niso seznanjeni z IF, deluje tako:

IF( pogoj , vrednost če je resnična, vrednost če je napačna )

Kadar je pogoj izraz, ki oceni res ali false .V zgornjem primeru je pogoj izraz B1 , ki ga lahko beremo kot »Ali je B1 manjši od B5?« Ali, drugače, »Ali je skupna prodaja nižja od praga«.Če je odgovor na to vprašanje "da"( true), potem uporabimo vrednost , če je v tem primeru parameter za to funkcijo resnično B6 - stopnja provizije, če je bila prodaja skupaj pod pragom.Če je odgovor na vprašanje "ne"( napačen), potem uporabimo vrednost , če je v tem primeru napačen parameter funkcije, in sicer B7 - provizija, če je prodaja skupaj nad prag.

Kot vidite, z uporabo prodaje v višini 20.000 dolarjev nam daje 20-odstotno provizijo v celici B2.Če vnesemo vrednost 40.000 USD, dobimo drugačno provizijo:

Torej naša preglednica deluje.

Naj bo zapleteno. Predstavimo še drugi prag: če prodajalec zasluži več kot 40.000 dolarjev, se njihova provizijska stopnja poveča na 40%:

Preprosto razumljivo v resničnem svetu, v celici B2 pa postaja naša formula bolj zapletena.Če natančno pogledate formulo, boste videli, da je tretji parameter izvorne funkcije IF( vrednost , če je napačna ) zdaj samostojna funkcija IF.To se imenuje ugnezdena funkcija ( funkcija znotraj funkcije).Popolnoma velja 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 na VLOOKUP, ne na Excelu na splošno.

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

Zdaj formula v celici B2, medtem ko je pravilna, je postala praktično neberljiva. Nihče ne bi smel napisati formul, kjer so funkcije globoko ugnezdene štiri stopnje! Gotovo mora biti enostavnejši način?

Gotovo je. VLOOKUP na reševanje!

Ponovno preoblikujemo delovni list. Ohranili bomo vse iste številke, vendar jih organiziramo na nov način, več tabele v obliki tabele :

Vzemite si trenutek in se prepričajte, da nova merilna tabela deluje popolnoma enako kot niz zgornjih mejnih vrednosti.

Konceptualno, kar bomo naredili, je, da VLOOKUP poiščemo prodajno vsoto prodajalca( iz B1) v tabeli s stopnjami in nam vrnete ustrezno stopnjo provizije. Upoštevajte, da je prodajalec morda dejansko ustvaril prodajo, ki je in ne ena od petih vrednosti v tabeli s stopnjami( 0, 30.000, 40.000, 50.000 ali 60.000 USD).Morda so ustvarili prodajo v višini 34.988 $.Pomembno je omeniti, da se v tabeli s stopnjami pojavijo 34.988 $ in ne .Poglejmo, ali lahko VLOOKUP vseeno reši naš problem. ..

Izberemo celico B2( lokacijo, za katero želimo dati našo formulo), nato pa vstavimo funkcijo VLOOKUP na jeziček Formulas :

Prikaže se oklepaj Function za VLOOKUP.Vstavimo argumente( parametre) eno za drugo, začenši z Lookup_value , kar je v tem primeru prodaja skupaj iz celice B1.Postavimo kazalko v polje Lookup_value in nato enkrat v celici B1:

V nadaljevanju moramo določiti VLOOKUP, za katero tabelo naj te podatke pregledajo. V tem primeru je seveda tabela stopenj. Postavimo kazalko v polje Table_array in nato označimo celotno tabelo stopenj - brez naslovov :

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

Končno vnesemo vrednost v polje Range_lookup .

Pomembno: Uporablja se to polje, ki razlikuje dva načina uporabe VLOOKUP-a.Če želite uporabiti VLOOKUP z bazo podatkov, je ta zadnji parameter, Range_lookup , vedno nastavljen na FALSE , vendar je s to drugo uporabo VLOOKUP treba pustiti prazno ali vnesti vrednost TRUE .Pri uporabi VLOOKUP-a je nujno, da naredite pravo izbiro za ta končni parameter.

Če želite biti izrecni, bomo v polje Range_lookup vnesli vrednost res .Prav tako bi bilo dobro pustiti prazno, ker je to privzeta vrednost:

Izpolnili smo vse parametre. Zdaj kliknemo gumb OK , Excel pa za nas oblikuje našo formulo VLOOKUP:

Če poskušamo z nekaj različnimi skupnimi količinami prodaje, se lahko prepričamo, da formula deluje.

Zaključek

V različici "baze podatkov" VLOOKUP, kjer je parameter Range_lookup FALSE , mora biti vrednost v prvem parametru( Lookup_value ) v zbirki podatkov prisotna .Z drugimi besedami, iščemo natančno ujemanje.

Toda v tej drugi uporabi VLOOKUPa nismo nujno iskali natančnega ujemanja. V tem primeru je "dovolj blizu, da je dovolj dobro".Kaj mislimo z "dovolj blizu"?Uporabimo primer: pri iskanju provizije za prodajo v skupni vrednosti 34.988 $, naša formula VLOOKUP nam bo vrnila vrednost 30%, kar je pravi odgovor. Zakaj je izbrala vrstico v tabeli s 30%?Kaj pravzaprav v tem primeru pomeni "dovolj blizu"?Naj bo natančnejša:

Ko je Range_lookup nastavljen na TRUE ( ali izpuščen), bo VLOOKUP pogledal v stolpec 1 in se bo ujemal najvišjo vrednost, ki ni večja od parametra Lookup_value .

Prav tako je pomembno opozoriti, da je za delovanje sistema tabelo treba razvrščati po naraščajočem vrstnem redu v stolpcu 1 !

Če bi radi vadili z VLOOKUP, lahko vzorčno datoteko, prikazano v tem članku, prenese od tod.