Nesename straipsnyje pristatome "Excel" funkciją VLOOKUP ir paaiškino, kaip ji gali būti naudojama iš duomenų bazės perkelti į lokalio 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šą norime rasti duomenų bazėje (pvz., Produkto kodą arba kliento ID). Šis unikalus identifikatorius privalo egzistuoja 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 pagal pardavimo skaičių rinkinį. 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:
Ląstelė B2 yra vienintelė įdomi šio darbo lapo dalis - formulė, skirta nuspręsti, kokį komisinį kursą naudoti: vienas žemiau 30 000 dolerių riba arba viena aukščiau riba. Ši formulė naudoja "Excel" funkciją, vadinamą Jei. Tiems skaitytojams, kurie nėra susipažinę su IF, tai veikia taip:
IF(condition,value if true,value if false)
Kur sąlyga yra išraiška, kuri įvertina arba tiesa arba klaidinga. Pavyzdyje aukščiau, sąlyga yra išraiška B1, kuris gali būti skaitomas kaip "Ar B1 yra mažesnis nei B5?", arba, kitaip tariant, "Ar bendras pardavimas yra mažesnis už ribą". Jei atsakymas į šį klausimą yra "taip" (tiesa), tada mes naudojame vertė tikra funkcijos parametras, būtent B6 šiuo atveju - komisinis mokestis, jei pardavimų buvo žemiau riba. Jei atsakymas į klausimą yra "ne" (klaidingas), mes naudojame vertė, jei klaidinga funkcijos parametras, būtent B7 šiuo atveju - komisinis mokestis, jei pardavimų buvo aukščiau riba.
Kaip matote, naudojant 20 000 USD pardavimo sumą, B2 langelyje mes suteikiame 20% komisinius. Jei įvesime 40 000 dolerių vertę, gauname skirtingą komisinių normą:
Padarykime jį sudėtingesnį. Leiskite pristatyti antrą ribą: jei pardavėjas uždirba daugiau nei 40 000 JAV dolerių, jų komisinis mokestis padidėja iki 40%:
Lengvai suprantama realiame pasaulyje, bet B2 langelyje mūsų formulė tampa vis sudėtingesnė. Jei atidžiai pažiūrėsite į formulę, pamatysite, kad trečias originalios IF funkcijos parametras ( vertė, jei klaidinga) dabar yra visa IF funkcija savo nuožiūra. Tai vadinama a įdėta funkcija (funkcija per 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, nei mes išnagrinėsime nestandartinių funkcijų. Tai pamoka VLOOKUP, o ne "Excel" apskritai.
Bet kokiu atveju jis 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?
Tai tikrai yra. VLOOKUP prie gelbėjimo!
Leiskite šiek tiek redaguoti darbalapį. Mes išlaikysime visus tuos pačius duomenis, bet organizuosime jį naujokai, dar daugiau lentelė būdas:
Paimkite šiek tiek laiko ir įsitikinkite, kad nauja Reitingų lentelė veikia lygiai taip pat, kaip aukščiau nurodytų ribų serija.
Konceptualiai, tai, ko mes norime padaryti, yra naudoti "VLOOKUP", norėdami ieškoti pardavėjo pardavimo apimties (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 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į, kad 34 988 JAV dolerių ne pasirodys normos lentelėje. Pažiūrėkime, ar VLOOKUP vistiek gali išspręsti mūsų problemą …
Mes pasirenkame langelį B2 (vietą, kuria norime įdėti mūsų formulę), tada įterpti funkciją VLOOKUP iš Formulės skirtukas:
The Funkcija Argumentai pasirodys langelis VLOOKUP. Mes užpildome argumentus (parametrus) po vieną, pradedant nuo Lookup_value, kuris šiuo atveju yra pardavimo suma iš B1 elemento. Mes įdėkite žymeklį į Lookup_value laukelį ir tada spustelėkite vieną kartą langelyje B1:
Kitas turime nurodyti VLOOKUP, kokia lentelė ieškoti šių duomenų. Šiame pavyzdyje, žinoma, yra lentelės norma. Mes įdėkite žymeklį į Table_array laukelį ir tada paryškinkite visą normos lentelę - išskyrus antraštes:
Toliau turime nurodyti, kuris lentelės stulpelis yra ta informacija, kurią norime, kad mūsų formulė grąžintų mums. Tokiu atveju mes norime, kad komisinis mokestis, kuris yra antroje lentelės stulpelyje, taigi mes įvesti 2 į Col_index_num laukas:
Galiausiai įvedame reikšmę " Range_lookup" laukas.
Svarbu: tai yra šio lauko naudojimas, kuris išskiria du VLOOKUP naudojimo būdus. Norėdami naudoti VLOOKUP su duomenų baze, šis galutinis parametras " Range_lookup", visada turi būti nustatytas į Klaidinga, bet su šiuo kitu VLOOKUP naudojimu mes turime palikti tuščią arba įvesti reikšmę TIESA. Naudodamiesi VLOOKUP, svarbu, kad teisingai pasirinktumėte šį galutinį parametrą.
Kad būtų aiškus, mes įvesti reikšmę tiesa viduje konors " Range_lookup" laukas. Taip pat būtų gerai palikti tuščią, nes tai yra numatytoji reikšmė:
Mes atlikome visus parametrus. Dabar spustelėkime Gerai mygtukas, o "Excel" sukuria mūsų "VLOOKUP" formulę mums:
Išvada
"VLOOKUP" "duomenų bazės" versijoje, kur " Range_lookup" parametras yra Klaidinga, vertė, perduodama pirmajame parametre (Lookup_value) privalo būti duomenų bazėje. Kitaip tariant, mes ieškome tikslios atitikties.
Tačiau š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? Būkime tiksli:
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.
Taip pat svarbu pažymėti, kad norint, kad ši sistema veiktų, lentelė turi būti rūšiuojama 1 stulpelyje didėjančia tvarka!
Jei norite praktikuoti su "VLOOKUP", šiame straipsnyje parodytą pavyzdinę rinkmeną galite atsisiųsti iš čia.
