VLOOKUP Excelis, 2. osa VLOOKUP kasutamine ilma andmebaasi
Hiljutises artiklis tutvustasime me Excel-funktsiooni VLOOKUP ja selgitas, kuidas seda saaks kasutada andmebaasi andmete hankimiseks kohalikus töölehel olevasse lahtrisse. Selles artiklis mainisime, et VLOOKUPil oli kaks kasutust, ja ainult üks neist käsitles andmebaasi päringut. Selles artiklis, teine ja viimane VLOOKUPi seerias, uurime seda teist, vähem tuntud kasutamist VLOOKUP funktsiooni jaoks.
Kui te pole seda veel teinud, lugege palun esimest artiklit VLOOKUP - see artikkel eeldab, et paljud selles artiklis selgitatud mõisted on lugejale juba teada.
Andmebaasidega töötades antakse VLOOKUPile „unikaalne identifikaator”, mis võimaldab tuvastada, millist andmesalvestust andmebaasis leida (nt tootekood või kliendi ID). See kordumatu tunnus peab andmebaasis olemas, vastasel juhul tagastab VLOOKUP meile vea. Käesolevas artiklis uurime võimalust kasutada VLOOKUP-i, kus identifikaator ei pea andmebaasis üldse olemas olema. See on peaaegu sama, kui VLOOKUP saab vastu võtta "piisavalt lähedal on piisavalt hea" lähenemine otsitavate andmete tagastamiseks. Teatud asjaoludel on see nii täpselt mida me vajame.
Me illustreerime seda artiklit reaalses näites - komisjonitasude arvutamisel, mis saadakse müüginäitajate kogumilt. Alustame väga lihtsast stsenaariumist ja seejärel järk-järgult keerukamaks, kuni probleemi ainus ratsionaalne lahendus on kasutada VLOOKUP-i. Meie fiktiivse ettevõtte esialgne stsenaarium töötab sellisel viisil: Kui müüja tekitab antud aastal rohkem kui 30 000 dollari väärtuses müüki, on nende müügist teenitud komisjonitasu 30%. Vastasel juhul on komisjonitasu vaid 20%. Seni on see üsna lihtne tööleht:
Selle töölehe kasutamiseks sisestab müüja oma müügiandmed lahtrisse B1 ja lahtris B2 olev valem arvutab õige komisjonitasu, mida neil on õigus saada, mida kasutatakse lahtris B3, et arvutada kogu komisjonitasu, mida müüja võlgneb (mis on B1 ja B2 lihtne korrutamine).
Lahter B2 sisaldab selle töölehe ainsat huvitavat osa - valemit, mille alusel otsustada, millist komisjonitasu kasutada allpool lävi $ 30 000 või üks eespool lävi. See valem kasutab Exceli funktsiooni, mida nimetatakse KUI. Neile lugejatele, kes ei tunne IF-i, töötab see nii:
IF (tingimus, väärtus, kui see on õige, väärtus, kui vale)
Kus seisund on väljend, mis hindab kas tõsi või vale. Ülaltoodud näites on seisund on väljend B1
Nagu näete, annab 20 000 dollari müügimahu kasutamine lahtris B2 vahendustasu 20%. Kui me sisestame väärtuse $ 40,000, saame teistsuguse vahendustasu:
Nii et meie arvutustabel töötab.
Teeme selle keerulisemaks. Tutvustame teist künnist: kui müüja teenib rohkem kui 40 000 dollarit, suureneb nende komisjonitasu 40% -ni:
Lihtne, et reaalses maailmas mõista, kuid lahtris B2 muutub meie valem keerulisemaks. Kui vaatate täpselt valemit, näete, et esialgse IF funktsiooni kolmas parameeter ( väärtus, kui vale) on nüüd kogu IF-funktsioon iseseisvalt. Seda nimetatakse a sisestatud funktsioon (funktsioon funktsioonis). See kehtib täiesti Excelis (see töötab isegi!), Kuid seda on raskem lugeda ja mõista.
Me ei kavatse minna mutritesse ja poltidesse, kuidas ja miks see toimib, samuti ei uurita sisestatud funktsioonide nüansse. See on VLOOKUP-i juhendaja, mitte Excelis üldiselt.
Igatahes, see hullem! Aga millal me otsustame, et kui nad teenivad rohkem kui 50 000 dollarit, on neil õigus saada 50% komisjonitasu ja kui nad teenivad rohkem kui 60 000 dollarit, on neil õigus 60% komisjonitasule?
Nüüd on lahtris B2 olev valem, kuigi õige, muutunud praktiliselt loetamatuks. Keegi ei peaks kirjutama valemeid, kus funktsioonid on sisestatud nelja taseme sügavale! Kindlasti peab olema lihtsam viis?
Kindlasti on. VLOOKUP päästmiseks!
Muutke tööleht veidi ümber. Hoidame kõik samad arvud, kuid korraldame seda uutel viisidel tabel tee:
Võta hetk ja veenduge, et uus Hindade tabel toimib täpselt samamoodi kui ülaltoodud künniste seeria.
Kontseptuaalselt, mida me kavatseme teha, on kasutada VLOOKUP-i, et otsida müüja müügi kogusummat (alates B1-st) hindade tabelis ja tagastada meile vastav komisjonitasu. Pange tähele, et müüja võib tõepoolest luua müügi mitte üks viiest väärtusest tabelis ($ 0, $ 30,000, $ 40,000, $ 50,000 või $ 60,000). Nad võivad olla loonud 34 988 dollarit. On oluline märkida, et $ 34,988 teeb seda mitte kuvatakse määrade tabelis. Vaatame, kas VLOOKUP suudab meie probleemi ikkagi lahendada ...
Me valime lahtri B2 (asukoht, kuhu tahame oma valemit panna) ja seejärel sisestada VLOOKUP funktsiooni Valemid vahekaart:
The Funktsioon argumendid Kuvatakse VLOOKUP-i kast. Täitame argumendid (parameetrid) ükshaaval, alustades Lookup_value, mis on antud juhul lahtri B1 müügimaht. Me paigutame kursori Lookup_value klõpsa lahtris B1 üks kord:
Järgmiseks peame VLOOKUPile täpsustama, millist tabelit nende andmete otsimiseks. Selles näites on see muidugi tabel. Me paigutame kursori Table_array välja ja seejärel tõstke esile kogu hindade tabel - välja arvatud rubriigid:
Järgmisena peame täpsustama, milline tabeli veerg sisaldab teavet, mida me tahame, et meie valem meile tagasi pöörduks. Sellisel juhul tahame komisjonitasu, mis on tabeli teises veerus, seega sisestame a 2 sisse Col_index_num väli:
Lõpuks me sisestame väärtuse Range_lookup valdkonnas.
Oluline: selle välja kasutamine kasutab kahte VLOOKUPi kasutamise viisi. VLOOKUPi kasutamiseks andmebaasis on see viimane parameeter, Range_lookup, peab alati olema FALSE, kuid selle teise VLOOKUPi kasutamisega peame jätma selle tühjaks või sisestama väärtuse TÕSI. VLOOKUPi kasutamisel on äärmiselt oluline, et teete selle lõpliku parameetri valiku.
Et olla selgesõnaline, sisestame me väärtuse tõsi Euroopa Range_lookup valdkonnas. Samuti oleks hea jätta see tühjaks, kuna see on vaikeväärtus:
Oleme täitnud kõik parameetrid. Klõpsame nüüd Okei ja Excel ehitab meie VLOOKUP-valemi:
Kui me katsetame mõne erineva müügimahuga, võime veenduda, et valem töötab.
Järeldus
VLOOKUPi andmebaasi versioonis, kus Range_lookup parameeter on FALSE, esimese parameetri väärtus (Lookup_value) peab olema andmebaasis. Teisisõnu, me otsime täpset vastet.
Kuid selles teises VLOOKUPi kasutuses ei ole me tingimata vaja täpset vastet. Sel juhul on „piisavalt lähedal“ piisavalt hea. Aga mida me mõtleme “piisavalt lähedal”? Kasutame näiteks näiteid: kui otsite komisjonitasu 34,988 dollari müügimahu kohta, tagastab meie VLOOKUP-valem meile 30% väärtuse, mis on õige vastus. Miks ta valis tabelisse rea, mis sisaldas 30%? Mis tegelikult tähendab "piisavalt lähedal" antud juhul? Olgem täpsed:
Millal Range_lookup on seatud TÕSI (või jäetakse välja), VLOOKUP otsib 1. veerus ja sobib kõrgeim väärtus, mis ei ole suurem kui the Lookup_value parameeter.
Samuti on oluline märkida, et see süsteem töötab, tabel tuleb sortida 1. veerus kasvavas järjekorras!
Kui soovite harjutada VLOOKUPiga, saab siinkirjeldatud näidisfaili siit alla laadida.
