Egy nemrég megjelent cikkben bemutattuk az Excel nevű függvényt VLOOKUP és elmagyarázta, hogyan lehet felhasználni az adatbázisból származó adatoknak egy helyi munkalapon lévő cellába történő lekérdezésére. Ebben a cikkben megemlítettük, hogy a VLOOKUP-nak két felhasználási módja volt, és csak az egyikük foglalkozott adatbázisok lekérdezésével. Ebben a cikkben, a második és a végleges a VLOOKUP sorozat, megvizsgáljuk ezt a más, kevésbé ismert használatát a VLOOKUP funkciót.
Ha még nem tette meg, kérjük, olvassa el az első VLOOKUP cikket - ez a cikk azt feltételezi, hogy a cikkben ismertetett fogalmak többsége már ismert az olvasónak.
Amikor adatbázisokkal dolgozik, a VLOOKUP egy "egyedi azonosítóval" rendelkezik, amely azonosítja, hogy mely adatrekordot szeretnénk megtalálni az adatbázisban (például egy termékkódot vagy ügyfél-azonosítót). Ez az egyedi azonosító kell léteznek az adatbázisban, különben a VLOOKUP hibát okoz. Ebben a cikkben megvizsgáljuk a VLOOKUP használatának módját, ahol az azonosítónak egyáltalán nem kell létenie az adatbázisban. Ez majdnem olyan, mintha a VLOOKUP képes lenne "elég közel" elég jó "megközelítést a keresett adatok visszaküldésére. Bizonyos körülmények között ez így van pontosan amire szükségünk van.
Ezt a cikket egy valós példával illusztráljuk - az értékesítési számok alapján generált jutalékok kiszámításánál. Egy nagyon egyszerű forgatókönyvet kezdünk, majd fokozatosan bonyolultabbá tesszük, amíg a probléma egyetlen racionális megoldása a VLOOKUP használata. Fiktív vállalatunk kezdeti forgatókönyve így működik: Ha egy értékesítő egy adott évben több mint 30.000 dolláros értékesítési forgalmat bonyolít le, az ilyen értékesítésekre jutó jutalék 30%. Egyébként jutalékuk csak 20%. Eddig ez egy nagyon egyszerű munkalap:
A B2 cellában ez a munkalap egyetlen érdekes része - a képlet, amely meghatározza, melyik jutalékot kell használni: az egyiket lent a küszöb 30.000 dollár, vagy az egyik felett küszöb. Ez a képlet kihasználja az Excel nevű függvényt HA. Azok számára, akik nem ismerik az IF-t, így működik:
IF(condition,value if true,value if false)
Hol a feltétel egy olyan kifejezés, amelyik értékel igaz vagy hamis. A fenti példában a feltétel a kifejezés B1, amelyet "B1 kevesebb mint B5?" lehet olvasni, vagy másképpen "A teljes értékesítés kevesebb, mint a küszöbérték". Ha a kérdésre adott válasz "igen" (igaz), akkor a érték, ha igaz a funkció paramétere, nevezetesen B6 ebben az esetben - a jutalék mértékét, ha az értékesítési összérték lent küszöb. Ha a kérdésre adott válasz "nem" (hamis), akkor a érték, ha hamis a funkció paramétere, nevezetesen B7 ebben az esetben - a jutalék mértékét, ha az értékesítési összérték felett küszöb.
Amint láthatjuk, a 20 000 dolláros eladási összeg felhasználásával a B2 cellában 20% jutalékot adunk. Ha beírjuk a 40 000 dolláros értéket, külön jutalékot kapunk:
Bonyolultabbá tegyük. Vessünk be egy második küszöbértéket: Ha az értékesítő több mint 40 000 dollárt keres, akkor a jutalékuk 40% -ra emelkedik:
Könnyű ahhoz, hogy megértsük a valós világban, de a B2 cellában képletünk egyre bonyolultabbá válik. Ha alaposan megnézzük a képletet, látni fogjuk, hogy az eredeti IF függvény harmadik paramétere (a érték, ha hamis) most egy teljes IF-függvény. Ezt nevezik a beágyazott függvény (függvény egy függvényen belül). Teljesen érvényes Excel-ben (még működik is!), De nehezebb olvasni és megérteni.
Nem fogunk belemenni az anyák és csavarok, hogy hogyan és miért működik, és nem fogjuk megvizsgálni a beágyazott funkciók árnyalatait. Ez egy bemutató a VLOOKUP-on, nem az Excel-ről általában.
Különben is rosszabb! Mi a helyzet akkor, ha eldöntöttük, hogy ha több mint 50 000 dollárt keresnek, akkor 50% jutalékra jogosultak, és ha több mint 60 000 dollárt keresnek, akkor 60% jutalékra jogosultak?
Biztosan van. VLOOKUP a mentéshez!
Nézzük át újra a munkalapot. Ugyanazokat a számokat fogjuk megtartani, de új módon szervezzük meg táblázatos út:
Vessen egy pillanatot és ellenőrizze magának, hogy az új Táblázat pontosan megegyezik a fenti küszöbértékek sorozataival.
Koncepcionálisan a VLOOKUP használatával kereshetjük meg az értékesítési értékesítési összértéket (a B1-től) az árfolyam táblázatában, és térjünk vissza a megfelelő jutalékhoz. Vegye figyelembe, hogy az eladó valóban létrehozott értékesítéseket hozott létre nem az értéktáblázat öt értéke közül az egyik ($ 0, $ 30,000, $ 40,000, $ 50,000 vagy $ 60,000). Lehet, hogy 34,988 dollár értékesítést hoztak létre. Fontos megjegyezni, hogy 34.988 dollár nem jelennek meg a sebesség táblázatában. Nézzük, vajon a VLOOKUP megoldja-e a problémát …
Kiválasztjuk a B2 cellát (a helyet, ahová a képletünket meg akarjuk adni), majd helyezzük be a VLOOKUP függvényt a képletek lapon:
A Funkcióelméletek megjelenik a VLOOKUP mező. Az argumentumokat (paramétereket) egyenként töltjük ki, a keresési_érték, amely ebben az esetben a B1 cellából származó értékesítési összérték. A kurzort a keresési_érték mezőt, majd egyszer a B1 cellán:
Ezután meg kell adnunk a VLOOKUP-nak azt a táblát, amelyre keresni szeretnénk ezeket az adatokat. Ebben a példában természetesen a sebesség táblázat. A kurzort a tábla_tömb mezőt, majd jelölje ki a teljes árfolyam táblázatot - a fejlécek kivételével:
Ezután meg kell adnunk, hogy melyik oszlop tartalmazza a táblázatban szereplő információkat, melyeket a képletünkben szeretnénk visszatérni. Ebben az esetben a táblázatban a második oszlopban szereplő jutalékot szeretnénk, tehát a 2 ba,-be oszlopszám mező:
Végül beírunk egy értéket a tartományban_keres mező.
Fontos: Ez a mező használata különbözteti meg a VLOOKUP használatának két módját. Ahhoz, hogy a VLOOKUP-t egy adatbázisban használhassuk, ez a végső paraméter, tartományban_keresmindig be kell állítani HAMIS, de ezzel a VLOOKUP másik használatával el kell hagynunk üresen, vagy meg kell adnunk egy értéket IGAZ. A VLOOKUP használata során elengedhetetlen, hogy a végleges paramétert a megfelelő választásnál végezze el.
Ahhoz, hogy egyértelmű legyen, megadjuk a igaz ban,-ben tartományban_keres mező. Szintén helytelen lehet üresen hagyni, mivel ez az alapértelmezett érték:
Végeztünk minden paramétert. Most kattintsunk a rendben gombra, és az Excel elkészíti a VLOOKUP képletünket:
Következtetés
A VLOOKUP "adatbázis" verziójában, ahol a tartományban_keres paraméter HAMIS, az első paraméterben átadott érték (keresési_érték) kell legyen jelen az adatbázisban. Más szóval, pontos egyezést keresünk.
De ebben a VLOOKUP másik használatában nem feltétlenül keresünk pontos egyezést. Ebben az esetben "elég közel van elég jó". De mit értünk "elég közel"? Használjunk egy példát: Amikor egy 34,988 USD értékű értékesítési jutalékot keresünk, a VLOOKUP képletünk 30% értéket ad vissza nekünk, ami a helyes válasz. Miért választotta ki a 30% -ot tartalmazó táblázat sorát? Mi ebben az esetben valójában "elég közel"? Pontosabban:
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.
Fontos megjegyezni azt is, hogy ahhoz, hogy ez a rendszer működjön, a táblázatot az 1. oszlopban növekvő sorrendbe kell sorolni!
Ha a VLOOKUP programmal szeretnél dolgozni, akkor a cikkben bemutatott mintafájl letölthető innen.
