Hiljutises artiklis tutvustasime Exceli funktsiooni nimetusega VLOOKUP ja selgitas, kuidas seda võib 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, loe palun esimene VLOOKUP-i artikkel - see artikkel eeldab, et paljud selles artiklis selgitatud mõisted on juba lugejaile teada.
Andmebaasidega töötamisel edastatakse VLOOKUP'ile "unikaalne identifikaator", mille abil saab tuvastada, millist andmekirje me soovime andmebaasis leida (nt toote kood või kliendi ID). See unikaalne tunnus peab 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 asjaoludel on see nii täpselt mida me vajame.
Me illustreerime seda artiklit reaalmaailma näitel - müügiartiklites kogutud komisjonitasude arvutamiseks. 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 dollari väärtuses müüki, on komisjonil nende müügitulu 30%. Vastasel juhul on nende komisjonitasu ainult 20%. Siiani on see päris lihtne tööleht:
Lahtris B2 on selle töölehe ainus huvitav osa - valem selle kohta, milline komisjonitasu määr kasutada: üks allpool 30 000-dollariline või selle künnis eespool künnis. See valem kasutab Exceli funktsiooni nimetust KUI. Neile lugejatele, kes ei tunne IF-d, töötab see järgmiselt:
IF(condition,value if true,value if false)
Kui seisund on väljend, mis hindab kas tõsi või vale. Eespool toodud näites: seisund on 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 künnis". Kui vastus sellele küsimusele on "jah" (tõsi), siis kasutame seda väärtus kui tõsi funktsiooni parameeter, nimelt B6 sel juhul - komisjonitasu määr, kui müügi kogumaht oli allpool künnis. Kui vastus küsimusele on "ei" (vale), siis kasutame seda väärtus kui vale funktsiooni parameeter, nimelt B7 sel juhul - komisjonitasu määr, kui müügi kogumaht oli eespool künnis.
Nagu näete, on müügi kogusummas 20 000 dollarit kasutades lahtris B2 antav komisjonitasu 20%. Kui sisestame väärtuse 40 000 dollarit, siis saame erineva maksumäära:
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, aga B2-kava puhul muutub meie valem keerukamaks. Kui vaatate valemit täpselt, näete, et esialgse IF funktsiooni kolmas parameeter ( väärtus kui vale) on nüüd täiesti IF funktsioon. Seda nimetatakse pesastatud funktsioon (funktsioon funktsioonis). 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.
Igatahes 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?
Kindlasti on. VLOOKUP päästmiseks!
Laske töölehe veidi ümber kujundada. Hoiame kõik samad arvud, kuid korraldame seda uuel viisil, rohkem tabelina tee:
Võta hetk ja veenduge, et uus Hinnake tabelit töötab täpselt samamoodi kui ülaltoodud künniste seerias.
Kontseptuaalselt peame tegema VLOOKUP-i, et otsida laenu tabelisse müüja müügitulu (B1-st) ja tagastada vastav komisjonitasu. Pange tähele, et müüja võib tõepoolest luua müüki, mis on mitte üks viiest väärtustabelis ($ 0, $ 30,000, $ 40,000, $ 50,000 või $ 60,000). Nad võisid luua 34 988 dollarit. Tähtis on märkida, et 34 988 dollarit teeb mitte näidatakse tabelis tabelis. Vaatame, kas VLOOKUP suudab igal juhul lahendada …
Valime lahtri B2 (asukoha, kus tahame oma valemit asetada) ja seejärel sisesta VLOOKUP funktsioon Valemid vahekaart:
The Funktsioon Argumendid ilmub VLOOKUP-i kasti. Täidame argumendid (parameetrid) ükshaaval, alustades sellest Lookup_value, mis on antud juhul müügi kogus lahtrist B1. Asetame kursori asukohta Lookup_value välja ja seejärel klõpsake üks kord lahtris B1:
Järgmisena peame VLOOKUPile täpsustama, milline tabel neid andmeid otsida. Selles näites on loomulikult kursistabel. Asetame kursori asukohta Table_array välja ja seejärel esile kogu kiiruse tabel - välja arvatud rubriigid:
Järgmine peame täpsustama, milline veerg tabelis sisaldab teavet, mida me tahame, et meie valem meid naastaks. Sel juhul soovime komisjonitasu määra, mis on tabeli teises veerus, nii et me sisestame seejärel a 2 sisse Col_index_num väli:
Lõpuks sisestame väärtuse Range_lookup valdkonnas.
Tähtis: selle valdkonna kasutamine, mis eristab VLOOKUPi kasutamise kahte võimalust. VLOOKUPi kasutamine andmebaasiga on see lõplik parameeter Range_lookup, peab alati olema seatud VÄÄRNE, kuid selle VLOOKUPi muu kasutamise korral peame kas jätma selle tühjaks või sisestama väärtuse TÕSI. VLOOKUPi kasutamisel on oluline, et teete selle lõpliku parameetri jaoks õige valiku.
Oleme selgesõnaliselt sisestanud väärtuse tõsi aastal Range_lookup valdkonnas. Samuti oleks hea jätta see tühjaks, kuna see on vaikimisi väärtus:
Oleme täitnud kõik parameetrid. Nüüd klõpsame nupul Okei nupp ja Excel loob meie VLOOKUPi valemi meile:
Järeldus
VLOOKUPi "andmekogu" versioonis, kus Range_lookup parameeter on VÄÄRNE, esimeses parameetris (Lookup_value) peab olla andmebaasis olemas. Teisisõnu otsime täpset vastet.
Kuid selle VLOOKUPi muu kasutamise korral 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:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Samuti on oluline märkida, et selleks süsteemiks töötab, tabel tuleb 1. veerus sorteerida kasvavas järjekorras!
Kui soovite kasutada VLOOKUP-i, saate käesolevas artiklis illustreeritud näidisfaili alla laadida siit.
