VLOOKUP Excelben, 2. rész A VLOOKUP használata adatbázis nélkül
Egy nemrégiben megjelent cikkben bemutattuk az Excel funkciót VLOOKUP és elmagyarázta, hogyan lehetne felhasználni az adatbázisból származó információk letöltésére egy helyi munkalap cellájába. Ebben a cikkben megemlítettük, hogy a VLOOKUP-nak két felhasználási módja van, és csak az egyik foglalkozott adatbázisok lekérdezésével. Ebben a cikkben, a VLOOKUP sorozat második és utolsó változatában ezt a másik, kevésbé ismert felhasználást vizsgáljuk a VLOOKUP függvényben.
Ha még nem tette meg, olvassa el az első VLOOKUP cikket - ez a cikk azt feltételezi, hogy az ebben a cikkben kifejtett számos fogalom már ismert az olvasó számára.
Adatbázisokkal való munka során a VLOOKUP egy „egyedi azonosítót” kap, amely az adatbázisban található adatrekordok (például termékkód vagy ügyfélazonosító) azonosítására szolgál. Ez az egyedi azonosító kell létezik az adatbázisban, különben a VLOOKUP hibát ad vissza. Ebben a cikkben megvizsgáljuk a VLOOKUP használatának módját, ahol az azonosítónak nem kell egyáltalán léteznie az adatbázisban. Majdnem olyan, mintha a VLOOKUP elfogadná a „elég közel” elég jó megközelítést a keresett adatok visszaküldéséhez. Bizonyos körülmények között ez az pontosan amire szükségünk van.
Ezt a cikket valódi példával illusztráljuk - az értékesítési számadatok alapján generált jutalékok kiszámítását. Egy nagyon egyszerű forgatókönyvvel kezdjük, majd fokozatosan bonyolultabbá válunk, amíg a probléma egyetlen racionális megoldása a VLOOKUP használata. A fiktív cégünk kezdeti forgatókönyve így működik: Ha egy értékesítő egy adott évben több mint 30 000 dollár értékű értékesítést hoz létre, akkor az ezeken az értékesítéseken elért jutalék 30%. Ellenkező esetben a jutalékuk csak 20%. Eddig ez elég egyszerű munkalap:
A munkalap használatához az értékesítő a B1 cellában adja meg értékesítési adatait, és a B2 cellában szereplő képlet kiszámítja a megfelelő jutalékszintet, amelyet a B3 cellában használnak, hogy kiszámítsák a teljes jutalékot, amelyet az eladó fizet. B1 és B2 egyszerű szorzata).
A B2 cellában ez a munkalap egyetlen érdekes része - az a képlet, amely meghatározza, hogy melyik jutalékot kell használni: az egyik lent a 30 000 dolláros küszöb vagy az egy felett küszöb. Ez a képlet az Excel funkciót használja HA. Azoknak az olvasóknak, akik nem ismerik az IF-t, úgy működik:
HA(állapot, érték, ha igaz, érték, ha hamis)
Hol a feltétel egy kifejezést, amely akár az is értékeli igaz vagy hamis. A fenti példában a feltétel a kifejezés B1
Mint látható, a 20 000 dolláros értékesítés összesen 20% -os jutalékot biztosít számunkra a B2 cellában. Ha 40 000 dollár értéket adunk meg, más jutalékot kapunk:
Így a táblázatunk működik.
Legyen ez bonyolultabb. Mutassunk be egy második küszöböt: Ha az eladó több mint 40.000 dollárt keres, akkor jutalékuk 40% -ra emelkedik:
Elég könnyű ahhoz, hogy megértsük a valós világban, de a B2 cellában összetettebbé válik a képlete. Ha közelebbről megnézzük a képletet, látni fogod, hogy az eredeti IF függvény harmadik paramétere (a érték, ha hamis) most egy teljes IF funkció önmagában. Ezt a-nek hívják beágyazott funkció (egy függvényen belüli függvény). Tökéletesen érvényes az Excelben (még működik!), De nehezebb olvasni és megérteni.
Nem megyünk be az anyákra és csavarokra, hogy hogyan és miért működik ez, és nem vizsgáljuk meg a beágyazott funkciók árnyalatait. Ez egy bemutató a VLOOKUP-on, nem általában az Excelen.
Különben is, rosszabbodik! Mi a helyzet akkor, amikor úgy döntünk, hogy ha több mint 50.000 dollárt keresnek, akkor 50% -os jutalékra jogosultak, és ha 60 000 dollárnál többet keresnek, akkor 60% -os jutalékra jogosultak?
Most a B2-es cellában lévő formula, bár helyes, gyakorlatilag olvashatatlanná vált. Senki sem kell írni olyan képleteket, ahol a funkciók négy szinten mélyen beágyazva vannak! Biztos, hogy egyszerűbbnek kell lennie?
Természetesen ott van. VLOOKUP a mentéshez!
Egy kicsit újratervezzük a munkalapot. Megtartjuk ugyanazokat a számokat, de újabb módon rendezzük táblázatos út:
Vegyünk egy pillanatot, és ellenőrizzük, hogy az új Árfolyam táblázat pontosan ugyanaz, mint a fenti küszöbértékek.
Koncepcionálisan a VLOOKUP-ot használjuk fel arra, hogy keressük meg az értékesítő összes értékesítési árát (B1-től) az árfolyam táblázatban, és visszaküldjük nekünk a megfelelő jutalékot. Ne feledje, hogy az értékesítő valóban olyan eladásokat hozott létre, amelyek nem az érték táblázatban szereplő öt érték közül az egyik ($ 0, $ 30,000, $ 40,000, $ 50,000 vagy $ 60,000). Lehet, hogy 34,988 dollár árbevételt hoztak létre. Fontos megjegyezni, hogy 34.988 $ van nem megjelenik az árfolyam táblázatban. Lássuk, vajon a VLOOKUP megoldja-e a problémát…
Kiválasztjuk a B2 cellát (az a hely, ahová a képletet szeretnénk), majd helyezzük be a VLOOKUP funkciót a képletek lapon:
A Funkció érvek megjelenik a VLOOKUP mező. Az argumentumokat (paramétereket) egyenként töltöttük ki, kezdve a keresési_érték, amely ebben az esetben a B1 cellából származó eladások összege. A kurzort a keresési_érték majd kattintson egyszer a B1 cellára:
Ezután meg kell adnunk a VLOOKUP-nak, hogy melyik táblázatot keressük be az adatokba. Ebben a példában természetesen ez az arány táblázat. A kurzort a tábla_tömb mezőt, majd jelölje ki a teljes díjszabást - a címsorok kivételével:
Ezután meg kell határoznunk, hogy melyik oszlop tartalmazza a táblázatot, amellyel a képletünk visszatér. Ebben az esetben azt szeretnénk, ha a jutalék mértéke a táblázat második oszlopában található, így belépünk a 2 ba,-be oszlopszám mező:
Végül megadunk egy értéket a tartományban_keres mező.
Fontos: Ez a mező a VLOOKUP használatának két módját különbözteti meg. A VLOOKUP adatbázis használatával ez az utolsó paraméter, tartományban_keres, mindig be kell állítania HAMIS, de ezzel a VLOOKUP más használatával vagy üresen kell hagynunk, vagy meg kell adnunk egy értéket IGAZ. A VLOOKUP használatakor elengedhetetlen, hogy a végső paraméterhez megfelelő választás legyen.
Ahhoz, hogy kifejezett legyen, megadunk egy értéket igaz ban,-ben tartományban_keres mező. Jó lenne, ha üresen hagyja, mivel ez az alapértelmezett érték:
Elvégeztük az összes paramétert. Most rákattintunk a rendben gomb, és az Excel a VLOOKUP képletünkre épül:
Ha néhány különböző értékesítési összeget kísérletezünk, meggyőződhetünk arról, hogy a képlet működik.
Következtetés
A VLOOKUP „adatbázis” változatában, ahol a tartományban_keres paraméter HAMIS, az első paraméterben elért érték (keresési_érték) kell legyen jelen az adatbázisban. Más szóval, keresünk egy pontos meccset.
A VLOOKUP ezen más felhasználása során azonban nem feltétlenül keresünk egy pontos meccset. Ebben az esetben „elég közel van”. De mit értünk „elég közel”? Használjunk egy példát: Ha 34,988 dolláros eladási összeget keres, a VLOOKUP-formula 30% -os értéket ad vissza, ami a helyes válasz. Miért választotta ki a táblázatban a 30% -ot tartalmazó sort? Ami valójában „elég közel” jelent ebben az esetben? Legyünk pontosak:
Amikor tartományban_keres beállítása IGAZ (vagy elhagyott), a VLOOKUP az 1. oszlopban fog megjelenni és egyezik a legmagasabb érték, amely nem nagyobb, mint a keresési_érték paraméter.
Fontos megjegyezni, hogy ez a rendszer működik, az asztalt az 1. oszlopban növekvő sorrendben kell rendezni!
Ha a VLOOKUP használatával szeretne gyakorolni, az itt bemutatott mintafájl letölthető innen.
