VLOOKUP Excelis, 2. osa: VLOOKUPi kasutamine ilma andmebaasi

Hiljutise artikliga tutvustasime Exceli funktsiooni VLOOKUP ja selgitasime, kuidas seda saab kasutada teabe otsimiseks andmebaasist kohaliku töölehe lahtrisse. Selles artiklis me mainisime, et VLOOKUPi jaoks oli kaks kasu ja ainult üks neist tegeleb päringute andmebaasidega. Käesolevas artiklis, teine ​​ja viimane VLOOKUP-seerias, uurime seda teist, vähemtuntud VLOOKUP-i funktsiooni kasutamist.

Kui te pole seda veel teinud, lugege palun esimest VLOOKUP-i artiklit - see artikkel eeldab, et paljud selles artiklis selgitatud mõisted on juba lugeja jaoks teada.

Andmebaasidega töötamisel edastatakse VLOOKUPile "unikaalne identifikaator", mille abil saab tuvastada, millist andmekirje me soovime andmebaasis leida( nt toote kood või kliendi ID).See ainulaadne tunnus peab olema olemas andmebaasis, muidu VLOOKUP tagastab meile vea. Käesolevas artiklis uurime VLOOKUPi kasutamise viisi, kus identifikaatorit üldse andmebaasi üldse ei pea. See on peaaegu nagu VLOOKUP suudab vastu võtta otsinguandmete taastamiseks piisavalt lähedal piisavalt head lähenemisviisi. Teatud tingimustel on

täpselt mida vajame.

Me illustreerime seda artiklit reaalmaailma näitel - arvutamaks komisjonitasusid, mis on loodud müügiartiklite kogumil. Alustame väga lihtsa stsenaariumiga ja seejärel järk-järgult keerukamaks, kuni probleemi ainus mõistlik lahendus on VLOOKUPi kasutamine. Meie fiktiivse ettevõtte esialgne stsenaarium töötab järgmiselt: kui müüja loob konkreetsel aastal rohkem kui 30 000 dollarit müüki, on komisjonil nende müügitulu 30%.Vastasel juhul on nende komisjonitasu ainult 20%.Siiani on see üsna lihtne tööleht:

Selle töölehe kasutamiseks müüb müüja oma müügiarvu lahtris B1 ja valem lahtris B2 arvutab õige komisjonitasu määra, mida neil on õigus saada, mida kasutatakse lahtris B3 arvutamiseksmüüja poolt võlgnetav kogusumma( mis on B1 ja B2 lihtsustatud korrutamine).

Lahtris B2 on selle töölehe ainus huvitav osa - valem otsustamaks, millist kasutustasu määra kasutada: üks allpool on 30 000 naelsterlingit või üks üle künnise. See valem kasutab Exceli funktsiooni kui .Neile lugejatele, kes ei tunne IF-d, töötab see järgmiselt:

IF( tingimus, väärtus, kui õige, väärtus kui vale )

Kui seisund on väljend, mis hindab kas tõelist või vale .Eespool toodud näites on seisund väljend B1 , mida saab lugeda kui "Kas B1 on väiksem kui B5?" Või viidates teisel viisil, "Kas kogumüük on väiksem kui lävi".Kui vastus sellele küsimusele on "jah"( tõsi), siis kasutame väärtust, kui funktsiooni tegelik parameeter, nimelt B6 , antud juhul - komisjonitasu määr, kui müügi kogus oli allpool künnist. Kui vastus küsimusele on "ei"( vale), siis kasutame väärtust, kui funktsiooni vale parameeter, nimelt B7 , antud juhul - komisjonitasu määr, kui müügi kogus oli üle künnise.

Nagu näete, kasutatakse 20 000-dollarise müügi kogusummas lahtris B2 antavat 20% -list komisjonitasu. Kui sisestame väärtuse 40 000 dollarini, siis saame erineva maksumäära:

. Nii et meie arvutustabel töötab.

Teeme seda keerulisemaks. Andke sisse teine ​​künnis: kui müüja teenib rohkem kui 40 000 dollarit, siis nende vahendustasu tõuseb 40% -ni:

Reaalses maailmas on lihtne arusaadav, kuid lahtris B2 muutub meie valem keerukamaks. Kui vaatate valemit tähelepanelikult, näete, et esialgse IF funktsiooni kolmas parameeter( väärtus, kui vale ) on täiesti täis IF funktsioon. Seda nimetatakse pesa funktsiooniks ( funktsiooni funktsioon).See on täiesti kehtiv Excelis( see töötab isegi!), Kuid seda on raskem lugeda ja mõista.

Me ei kavatse minna pähklitele ja poltidele selle kohta, kuidas ja miks see töötab, samuti ei uurita pandud funktsioonide nüansse. See on VLOOKUPi juhendaja, mitte üldiselt Exceli.

Igal juhul muutub see halvemaks! Aga kui me otsustame, et kui nad teenivad rohkem kui 50 000 dollarit, siis on neil õigus saada 50% komisjonitasu ja kui nad teenivad rohkem kui 60 000 dollarit, siis on neil õigus saada 60% komisjonitasu?

Nüüd on valem lahtris B2, ehkki õige, muutunud praktiliselt loetamatuks. Keegi ei peaks kirjutama valemeid, kus funktsioonid on neli taset sügavad! Kindlasti peab olema lihtsam viis?

Kindlasti on olemas. VLOOKUP päästmiseks!

Laske töölehe veidi ümber kujundada. Me hoiame kõik samad numbrid, kuid korraldame seda uuel viisil, rohkem tabulaarse viisi:

Võta hetke ja veenduge ise, et uus hinnaklassi tabel töötab täpselt samamoodi kui ülaltoodud künniste seeria.

Kontseptuaalselt peame tegema VLOOKUPi, et otsida laenu tabelisse müüja müügitulu( B1-st) ja tagastada meile vastav komisjonitasu. Pidage meeles, et müüja võib tõepoolest luua müüki, mis on , mitte , mis on viiest väärtustabelis( $ 0, $ 30,000, $ 40,000, $ 50000 või $ 60,000).Nad võisid luua 34 988 dollarit. Oluline on märkida, et tabelis tabelisse ilmub 34,988 dollarit mitte .Vaatame, kas VLOOKUP suudab ikkagi oma probleemi lahendada. ..

Valime lahtri B2( asukoha, kus tahame oma valemit asetada) ja seejärel sisesta VLOOKUP funktsioon valemitest sakk:

Ilmub funktsiooni argumendid kasti VLOOKUP jaoks. Täidame argumendid( parameetrid) ükshaaval, alustades Lookup_value -st, mis on sellisel juhul müügi kogus lahtrist B1.Asetame kursori Lookup_value väljale ja seejärel klõpsame üks kord lahtris B1:

Järgmisena peame VLOOKUPile täpsustama, milline tabel neid andmeid otsida. Selles näites on see muidugi muidugi määra tabel. Asetame kursori Table_array väljale ja siis tõstavad esile kõik kiirustabelid - , välja arvatud pealkirjad :

Järgmine peame täpsustama, milline veerg tabelis sisaldab teavet, mida me tahame, et meie valem meid naastaks. Sellisel juhul soovime komisjonitasu määra, mis on tabeli teises veerus, nii et me siseneksime 2 väljale Col_index_num :

. Lõpuks sisestage väärtus Range_lookup väljale.

Tähtis: see väli on kasutusel, mis eristab VLOOKUPi kahte võimalust. VLOOKUPi kasutamiseks andmebaasiga peab see lõplik parameeter, Range_lookup olema alati FALSE , kuid selle VLOOKUPi muu kasutamisega peame jätma kas tühjaks või sisestama TRUE väärtuse. VLOOKUPi kasutamisel on oluline, et teete selle lõpliku parameetri jaoks õige valiku.

Tuleb selgesõnaliselt sisestada tõese väärtuse Range_lookup väljale. Samuti oleks hea jätta see tühjaks, kuna see on vaikimisi väärtus:

Oleme täitnud kõik parameetrid. Nüüd klõpsame nuppu OK ja Excel loob meie jaoks VLOOKUPi valemi:

Kui katsume mõne erineva müügi kogusummaga, võime veenduda, et see valem töötab.

Kokkuvõte

VLOOKUPi "andmekogu" versioonis, kus Range_lookup parameeter on FALSE , peab esimeses parameetris( Lookup_value ) antud väärtus olema olemas andmebaasis. Teisisõnu otsime täpset vastet.

Kuid selles VLOOKUP-i teises kasutusvaldkonnas ei otsi me tingimata täpset vastet. Sel juhul on "piisavalt lähedal piisavalt hea".Aga mida me mõtleme "piisavalt lähedal"?Kasutame eeskuju: kui otsite komisjonitasu määra müügi kogumahus 34 988 dollarit, annab meie VLOOKUPi valem meile 30% väärtuse, mis on õige vastus. Miks valis tabelist 30% rida? Mis tegelikult tähendab "piisavalt lähedal" sel juhul? Olgem täpne:

Kui Range_lookup on seatud TRUE ( või välja jätta), otsib VLOOKUP veergu 1 ja vastab suurimale väärtusele, mis ei ole suurem kui parameeter lookup_value .

Samuti on oluline märkida, et selle süsteemi jaoks peab tabel olema sorteeritud kasvavas järjekorras veerus 1 !

Kui soovite kasutada VLOOKUP-i, saate käesolevas artiklis illustreeritud näidisfaili alla laadida siit.