VLOOKUP "Excel", 2 dalis: "VLOOKUP" naudojimas be duomenų bazės

Nesename straipsnyje pristatėme "Excel" funkciją, vadinamą VLOOKUP , ir paaiškinome, kaip ji gali būti naudojama iš duomenų bazės gauti informaciją į vietinį lakštą esančią ląstelę.Šiame straipsnyje mes paminėjome, kad VLOOKUP buvo du naudojimas, o tik vienas iš jų buvo skirtas užklausų duomenų bazėms.Šiame straipsnyje, antrame ir paskutiniame serijose VLOOKUP, mes nagrinėjame šį kitą, mažiau žinomą naudojimą VLOOKUP funkcijai.

Jei dar to nepadarėte, perskaitykite pirmąjį "VLOOKUP" straipsnį - šiame straipsnyje bus manoma, kad daugelis šiame straipsnyje aprašytų sąvokų jau žinomos skaitytojui.

Kai dirbate su duomenų bazėmis, "VLOOKUP" perduodamas "unikalus identifikatorius", kuris identifikuoja, kokį duomenų įrašą mes norime rasti duomenų bazėje( pvz., Produkto kodą arba kliento ID).Šis unikalus identifikatorius turi egzistuoti duomenų bazėje, kitaip VLOOKUP grąžina mums klaidą.Šiame straipsnyje mes išnagrinėsime, kaip naudoti "VLOOKUP", kur visam duomenų bazės identifikatoriui nebūtina egzistuoti. Tai beveik tarsi VLOOKUP gali priimti "pakankamai pakanka" pakankamai gerą požiūrį į tai, kaip grąžinti reikalingus duomenis. Tam tikromis aplinkybėmis tai yra

tiksliai , ko mums reikia.

Mes parodysime šį straipsnį realiuoju pavyzdžiu - apskaičiuojant komisinius, gaunamus iš pardavimo skaičiaus. Pradėsime nuo labai paprasto scenarijaus, o tada palaipsniui jį pakeisime, kol vienintelis racionalus šios problemos sprendimas yra naudoti VLOOKUP.Pradinis mūsų fiktyvios įmonės scenarijus veikia taip: jei pardavėjas tam tikrais metais sukuria daugiau nei 30 000 dolerių vertės pardavimų, komisiniai, kuriuos jie uždirba iš šių pardavimų, yra 30%.Kitaip jų komisiniai sudaro tik 20%.Kol kas tai yra gana paprastas darbalapis:

Norėdami naudoti šį darbalapį, pardavėjas įveda savo pardavimo duomenis B1 langelyje, o B2 langelio formulė apskaičiuoja teisingą komisinių normą, kurią jie turi teisę gauti, kuri naudojama skaičiuojant B3 langelįvisa komisija, kurią turi sumokėti pardavėjas( tai yra paprastas B1 ir B2 pakartojimas).

Ląstelė B2 yra vienintelis įdomus šios darbo lapo lapas - formulė, skirta nuspręsti, kokį komisinį kursą naudoti: viena , žemesnė už , riba yra 30 000 arba viena virš ribos.Ši formulė naudoja "Excel" funkciją, vadinamą , jei .Tiems skaitytojams, kurie nėra susipažinę su IF, jis veikia taip:

IF( būklė, reikšmė, jei teisinga, vertė, jei klaidinga )

Kai sąlyga yra išraiška, kuri vertinama kaip tikras arba klaidingas .Aukščiau pateiktame pavyzdyje būklė yra išraiška B1 , kuris gali būti skaitomas kaip "Ar B1 yra mažesnis nei B5?", Arba, kitaip tariant, "Ar bendras pardavimų kiekis yra mažesnis už slenkstį".Jei atsakymas į šį klausimą yra "taip"( tiesa), tada mes naudojame reikšmę, jei tikrasis funkcijos parametras, ty B6 , šiuo atveju - komisiniai, jei pardavimo suma buvo žemiau ribos. Jei atsakymas į klausimą yra "ne"( klaidingas), tada mes naudojame reikšmę, jeigu šiuo atveju yra klaidingas parametro parametras, ty B7 - tokiu atveju komisinis mokestis, jei pardavimo apimtis buvo virš ribos.

Kaip matote, naudodamiesi 20 000 dolerių pardavimais, gauname 20% biuletenį B2 langelyje. Jei įvesime 40 000 dolerių vertę, gauname skirtingą komisinių normą:

Taigi mūsų skaičiuoklė veikia.

Leiskite tai padaryti sudėtingesnį.Leiskite pristatyti antrą ribą: jei pardavėjas uždirba daugiau nei 40 000 JAV dolerių, jų komisinis mokestis padidėja iki 40%:

Paprastai suprantama realiame pasaulyje, bet B2 langelyje mūsų formulė tampa vis sudėtingesnė.Jei atidžiai perskaitysite formulę, pamatysite, kad trečias pirminės IF funkcijos parametras( reikšmė, jei klaidinga ) dabar yra visa IF funkcija. Tai vadinama įdėta funkcija ( funkcija pagal funkciją).Tai puikiai tinka "Excel"( netgi veikia!), Bet sunkiau skaityti ir suprasti.

Mes nesiruošia įeiti į veržles ir varžtus, kaip ir kodėl tai veikia, taip pat neišnagrinėsime įdėtų funkcijų niuansų.Tai pamoka VLOOKUP, o ne "Excel" apskritai.

Bet kokiu atveju, tai blogėja! Ką apie tai, kai nuspręsime, kad jei jie uždirba daugiau nei 50 000 JAV dolerių, tada jie turi teisę į 50% komisinį atlyginimą, o jei jie uždirba daugiau nei 60 000 JAV dolerių, tada jie turi teisę gauti 60% komisinius?

Dabar formulė B2 langelyje, nors ir teisinga, tapo praktiškai neįskaitoma. Niekas neturėtų rašyti formulių, kuriose funkcijos yra įdėtos keturiais lygiais giliai!Žinoma, turi būti paprastesnis būdas?

Tai tikrai yra. VLOOKUP prie gelbėjimo!

Leiskite šiek tiek redaguoti darbalapį.Mes išlaikysime visus tuos pačius skaičius, bet organizuosime jį nauju būdu, dar daugiau lentelių būdas:

Paimkite akimirką ir įsitikinkite, kad nauja greičio lentelė veikia tiksliai taip pat kaip aukščiau išvardytų slenksčių serija.

Konceptualiai, ką mes ketiname padaryti, yra naudoti "VLOOKUP", norėdami ieškoti pardavėjo pardavimo sumos( iš B1) tarifų lentelėje ir grąžinti mums atitinkamą komisinį kursą.Atkreipkite dėmesį, kad pardavėjas iš tikrųjų sukūrė pardavimus, kurie yra , o ne - viena iš penkių verčių lentelėje( $ 0, $ 30,000, $ 40,000, $ 50,000 arba $ 60,000).Jie galėjo sukurti 34,988 dolerių.Svarbu atkreipti dėmesį į tai, kad lentelėje pateikiama 34 988 JAV dolerių, o nėra .Pažiūrėkime, ar VLOOKUP vis tiek gali išspręsti mūsų problemą. ..

Mes pasirenkame langelį B2( vietą, kuria norime įdėti mūsų formulę), tada įterpkite VLOOKUP funkciją iš Formulių skirtuko:

Parodomas funkcijų argumentų langas langelyje VLOOKUP.Mes užpildome argumentus( parametrus) po vieną, pradedant nuo Lookup_value , kuris šiuo atveju yra pardavimo iš B1 ląstelių.Mes įdėjome žymeklį lauke Lookup_value ir tada spustelėkite vieną kartą langelyje B1:

Toliau mes turime nurodyti VLOOKUP, kokia lentelė ieškoti šių duomenų. Šiame pavyzdyje, žinoma, yra normos lentelė.Mes įdedame žymeklį į lauką Table_array ir tada paryškiname visą greičio lentelę - , išskyrus antraštes :

Toliau turime nurodyti, kuris stulpelis lentelėje yra ta informacija, kurią norime, kad mūsų formulė grąžintų mums. Tokiu atveju mes norime komisinių normos, kuri yra lentelės antroje skiltyje, todėl mes 2 į lauką Col_index_num įvedame

. Galiausiai įvedame reikšmę lauke Range_lookup .

Svarbu: tai yra šio lauko naudojimas, kuris išskiria du VLOOKUP naudojimo būdus. Jei norite naudoti VLOOKUP su duomenų baze, šis galutinis parametras Range_lookup visada turi būti nustatytas kaip FALSE , tačiau naudojant šį kitą VLOOKUP naudojimą, mes turime palikti tuščią arba įvesti TRUE vertę.Naudodamiesi VLOOKUP, svarbu, kad teisingai pasirinktumėte šį galutinį parametrą.

Jei bus aiškus, mes įveskite tikrosios vertę Range_lookup lauke. Taip pat būtų gerai, kad paliktumėte tuščią, nes tai yra numatytoji reikšmė:

Mes atlikome visus parametrus. Dabar spustelėkite OK mygtuką, o Excel sukuria mūsų VLOOKUP formulę mums:

Jei mes eksperimentuosime su keliais skirtingais pardavimų kiekiais, galime įsitikinti, kad formulė veikia.

Išvada

VLOOKUP "duomenų bazės" versijoje, kurioje parametras Range_lookup yra FALSE , pirmajame parametre( Lookup_value ) vertė turi būti .Kitaip tariant, mes ieškome tikslios atitikties.

Bet šiuo kito "VLOOKUP" naudojimo atveju mes nebūtinai ieškome tikslios atitikties.Šiuo atveju "artimas pakankamai pakankamai geras".Bet ką mes turime omenyje "beveik pakankamai"?Pavyzdžiui: naudodamiesi "VLOOKUP" formule grąžiname mums 30 procentų sumą, kuri yra teisingas atsakymas. Kodėl ji pasirinko lentelėje esančią eilutę, kurioje yra 30%?Iš tikrųjų, kas šiuo atveju "pakankamai arti" reiškia? Tiksliai tariame:

Kai Range_lookup nustatytas kaip TRUE ( arba praleistas), VLOOKUP atrodys 1 stulpelyje ir atitinka didžiausią vertę, kuri yra ne didesnė nei parametro Lookup_value .

Taip pat svarbu pažymėti, kad norint dirbti šioje sistemoje, lentelė turi būti rūšiuojama didėjimo tvarka 1 stulpelyje !

Jei norite naudoti "VLOOKUP", šiame straipsnyje parodytą pavyzdinę rinkmeną galite atsisiųsti iš čia.