„VLOOKUP“ programoje „Excel“, 2 dalis VLOOKUP naudojimas be duomenų bazės
Neseniame straipsnyje pristatėme „Excel“ funkciją VLOOKUP ir paaiškino, kaip jį būtų galima naudoti norint gauti informaciją iš duomenų bazės į vietos darbalapio langelį. Šiame straipsnyje paminėjome, kad VLOOKUP naudojosi dviem būdais, o tik vienas iš jų nagrinėjo duomenų bazių užklausas. Šiame straipsnyje, antroje ir baigiamojoje VLOOKUP serijoje, mes nagrinėjame šį kitą, mažiau žinomą VLOOKUP funkcijos naudojimą.
Jei to dar nepadarėte, perskaitykite pirmąjį VLOOKUP straipsnį - šiame straipsnyje daroma prielaida, kad daugelis šiame straipsnyje paaiškintų sąvokų jau yra žinomi skaitytojui.
Dirbant su duomenų bazėmis, „VLOOKUP“ perduodamas „unikalus identifikatorius“, kuris padeda nustatyti, kurį duomenų įrašą norime rasti duomenų bazėje (pvz., Produkto kodą ar kliento ID). Šis unikalus identifikatorius turi yra duomenų bazėje, kitaip VLOOKUP grąžina mums klaidą. Šiame straipsnyje mes išnagrinėsime būdą, kaip naudoti VLOOKUP, kai identifikatorius nebūtinai turi būti duomenų bazėje. Beveik taip, lyg VLOOKUP galėtų priimti „pakankamai arti“, kad būtų galima grąžinti ieškomus duomenis. Tam tikromis aplinkybėmis tai yra tiksliai ko mums reikia.
Mes iliustruosime šį straipsnį su realaus pasaulio pavyzdžiu - apskaičiuojant komisinius, kurie yra generuojami pagal pardavimo duomenis. Pradėsime nuo labai paprasto scenarijaus, tada palaipsniui tapsime sudėtingesni, kol vienintelis racionalus 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 JAV dolerių vertės pardavimus, komisiniai, kuriuos jie uždirba, yra 30%. Priešingu atveju jų komisiniai yra tik 20%. Iki šiol tai gana paprastas darbalapis:
Norėdami naudoti šį darbalapį, pardavėjas savo pardavimo duomenis įveda B1 ląstelėje, o B2 ląstelės formulė apskaičiuoja teisingą komisinių normą, kurią jie turi teisę gauti ir kurie naudojami B3 ląstelėje, kad būtų apskaičiuotas bendras pardavėjo skolos dydis (kuris yra paprastas B1 ir B2 dauginimas).
Ląstelėje B2 yra vienintelė įdomi šio darbo lapo dalis - formulė, pagal kurią nuspręsta, kokią komisinių normą naudoti: vieną žemiau 30 000 JAV dolerių ribą arba vieną aukščiau ribą. Ši formulė naudojama naudojant „Excel“ funkciją IF. Tiems skaitytojams, kurie nėra susipažinę su IF, jis veikia taip:
IF (būklė, vertė, jei tiesa, vertė, jei klaidinga)
Kur sąlyga yra išraiška, kuri vertinama ir į vieną tiesa arba klaidinga. Pirmiau pateiktame pavyzdyje sąlyga yra išraiška B1
Kaip matote, naudodamiesi pardavimais iš viso 20 000 dolerių, mes turime 20 proc. Jei įvesime 40 000 dolerių vertę, gauname kitokį komisinį tarifą:
Taigi mūsų skaičiuoklė veikia.
Padarykime jį sudėtingesnę. Pristatome antrą slenkstį: jei pardavėjas uždirba daugiau nei 40 000 JAV dolerių, tada jų komisiniai tarifai padidėja iki 40%:
Pakankamai paprasta suprasti realiame pasaulyje, bet B2 ląstelėje mūsų formulė tampa sudėtingesnė. Jei atidžiai žiūrėsite į formulę, pamatysite, kad trečiasis pirminio IF funkcijos parametras ( vertė, jei klaidinga) dabar yra visa IF funkcija. Tai vadinama a įdėta funkcija (funkcijos funkcija). Tai puikiai tinka „Excel“ programoje (netgi veikia!), Bet sunkiau skaityti ir suprasti.
Mes neketiname eiti į veržles ir varžtus, kaip ir kodėl tai veikia, taip pat neanalizuosime įdėtų funkcijų niuansus. Tai yra VLOOKUP pamoka, o ne „Excel“.
Bet kokiu atveju, tai dar blogiau! Ką apie tai, kada mes nusprendžiame, kad jei jie uždirba daugiau nei 50 000 JAV dolerių, jie turi teisę į 50% komisinius, o jei jie uždirba daugiau nei 60 000 JAV dolerių, jie turi teisę į 60% komisinį atlyginimą?
Dabar formulė B2 ląstelėje, 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?
Ten tikrai yra. VLOOKUP gelbėjimui!
Leiskite šiek tiek pertvarkyti darbalapį. Mes laikysime visus tuos pačius duomenis, tačiau juos organizuosime nauju būdu lentelės būdas:
Paimkite akimirką ir patikrinkite, ar tai nauja Tarifų lentelė veikia lygiai taip pat, kaip ir aukščiau nurodytų slenksčių serija.
Konceptualiai tai, ką ketiname padaryti, naudokite VLOOKUP ieškoti pardavėjo pardavimo sumos (nuo B1) į tarifų lentelę ir grąžinti mums atitinkamą komisinių normą. Atkreipkite dėmesį, kad pardavėjas galbūt sukūrė pardavimus ne viena iš penkių verčių lentelėje ($ 0, $ 30,000, $ 40,000, $ 50,000 arba $ 60,000). Jie galėjo sukurti 34,988 dolerius. Svarbu pažymėti, kad 34,988 doleriai ne rodomi tarifų lentelėje. Pažiūrėkime, ar VLOOKUP bet kuriuo atveju gali išspręsti mūsų problemą ...
Pasirenkame langelį B2 (vietą, kurią norime įdėti į mūsų formulę), o tada įterpti VLOOKUP funkciją iš Formulės skirtukas:
The Funkcijos argumentai pasirodo langelis „VLOOKUP“. Užpildome argumentus (parametrus) po vieną, pradedant nuo Lookup_value, kuris šiuo atveju yra bendras ląstelių B1 kiekis. Padedame žymeklį į Lookup_value tada spustelėkite vieną kartą ant langelio B1:
Toliau VLOOKUP reikia nurodyti, kokią lentelę ieškoti šiems duomenims. Šiame pavyzdyje, žinoma, tai yra tarifų lentelė. Padedame žymeklį į Table_array lauke, tada paryškinkite visą tarifų lentelę - išskyrus pozicijas:
Toliau turime nurodyti, kuri lentelės stulpelyje yra informacija, kurią norime, kad mūsų formulė grįžtų į mus. Tokiu atveju mes norime, kad komisinių norma būtų nurodyta antroje lentelės stulpelyje, todėl įvesime a 2 į Col_index_num laukas:
Galiausiai mes įvesime vertę Range_lookup lauke.
Svarbu: šio lauko naudojimas išskiria du VLOOKUP naudojimo būdus. Norėdami naudoti VLOOKUP su duomenų baze, šis galutinis parametras, Range_lookup, visada turi būti nustatyta FALSE, bet naudojant šį kitą VLOOKUP naudojimą, turime palikti jį tuščią arba įvesti vertę TIESA. Naudojant VLOOKUP, labai svarbu, kad teisingai pasirinktumėte šį galutinį parametrą.
Kad būtų aišku, mes įvesime vertę tiesa viduje konors Range_lookup lauke. Taip pat būtų gerai palikti tuščią, nes tai yra numatytoji vertė:
Atlikome visus parametrus. Dabar spustelėjome Gerai mygtukas, o „Excel“ sukuria mūsų „VLOOKUP“ formulę:
Jei eksperimentuojame su keliomis skirtingomis pardavimų sumomis, galime įsitikinti, kad formulė veikia.
Išvada
VLOOKUP „duomenų bazės“ versijoje, kur Range_lookup parametras yra FALSE, pirmame parametre perduota vertė (Lookup_value) turi būti duomenų bazėje. Kitaip tariant, ieškome tikslios atitikties.
Tačiau šiame kitame „VLOOKUP“ naudojime mes nebūtinai ieškome tikslios atitikties. Šiuo atveju „pakankamai arti yra pakankamai“. Bet ką mes turime galvoje „pakankamai arti“? Panaudosime pavyzdį: ieškodami komisinio mokesčio už 34,988 dolerius, mūsų „VLOOKUP“ formulė grąžins mums 30% vertę, kuri yra teisingas atsakymas. Kodėl jis pasirinko lentelės eilutę, kurioje yra 30%? Kas šiuo atveju reiškia „pakankamai arti“? Būkime tikslūs:
Kada Range_lookup yra nustatytas TIESA (arba praleisti), VLOOKUP ieškos 1 stulpelyje ir atitiks didžiausia vertė yra ne didesnė kaip Lookup_value parametras.
Taip pat svarbu pažymėti, kad ši sistema veikia, lentelė turi būti rūšiuojama didėjančia tvarka 1 stulpelyje!
Jei norite praktikuoti su „VLOOKUP“, čia galite parodyti šio straipsnio pavyzdį.
