1Aug
Egy nemrég megjelent cikkben bemutattuk az VLOOKUP nevű Excel-függvényt, és kifejtettük, hogyan lehet felhasználni az adatbázisból származó adatoknak egy helyi munkalapban 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 az abban a cikkben már említett fogalmak többsége már az olvasó számára ismert.
Az adatbázisok feldolgozásakor a VLOOKUP egy "egyedi azonosítót" ad át, 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).Az egyedi azonosítónak -nek kell lennie az adatbázisban, ellenkező esetben 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
pontosan , amire szükségünk van.Ezt a cikket 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 munkalap használatához az értékesítő bevezeti a B1 cellában lévő értékesítési számokat, és a B2 cellában lévő képlet kiszámítja a helyes jutalékot, amelyet jogosultak a fogadásra, amelyet a B3 cellában használnak aaz eladót terhelő teljes jutalék( ami a B1 és B2 egyszerű szorzása).
A B2 cellában ez a munkalap egyetlen érdekes része - az a formula, amely meghatározza, melyik jutalékot kell használni: az alatt a küszöbérték 30.000, vagy az az küszöb felett van. Ez a képlet használja az IF nevű Excel-funkciót. Azok számára, akik nem ismerik az IF-t, így működik:
IF( állapot, érték, ha valódi, érték, ha hamis )
Ha az feltétel egy olyan kifejezés, amely értékű vagy false értéket. A fenti példában az feltétel az B1
Ahogy láthatja, a 20.000 dolláros értékesítés összesen 20% jutalékot biztosít a B2 cellában. Ha 40 000 dollár értéket adunk meg, akkor más jutalékot kapunk:
Tehát a táblázatunk működik.
Bonyolultabbá tegyük. Vessünk be egy második küszöbértéket: Ha az eladó több mint 40 000 dollárt keres, akkor a jutalékuk 40% -ra emelkedik:
Megfelelőnek tűnik ahhoz, hogy megértse a valós világot, de a B2 cellában a képletünk egyre bonyolultabbá válik. Ha alaposan megnézzük a képletet, akkor látni fogjuk, hogy az eredeti IF függvény harmadik paramétere( az érték, ha hamis az ) most teljes IF-függvény. Ezt beágyazott függvénynek hívják( 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 a csavarokba, 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.
Mindenesetre rosszabb lesz! 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?
Most a B2 cellában található képlet, helyes, gyakorlatilag olvashatatlan. Senki sem szabad olyan képleteket írni, ahol a funkciók négy szint mélységbe ágyazódnak! Biztosan egyszerűbb módon kell lennie?
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, egy táblázatos módon:
Pillanatok, és ellenőrizzük magunknak, hogy az új árfolyam-táblázata pontosan ugyanúgy működik, mint a fenti küszöbértékek sorozata.
Koncepcionálisan az a dolgunk, hogy a VLOOKUP segítségével felmérjük az értékesítési értékesítési összeget( a B1-ből) a kamatláb táblázatban, és térjünk vissza a megfelelő jutalékhoz. Vegye figyelembe, hogy az értékesítő valóban létrehozott olyan értékesítéseket, amelyek és 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 a 34,988 dollár nem jelenik meg a sebesség táblázatában. Nézzük meg, hogy a VLOOKUP megoldja-e a problémát. ..
Kiválasztjuk a B2 cellát( a helyet, ahová a képletünket fel szeretnénk helyezni), majd helyezzük be a VLOOKUP függvényt az Formulák lapjáról:
Az függvény argumentumai Az mező a VLOOKUP megjelenik. Az argumentumokat( paramétereket) egyenként töltjük ki, kezdve az Lookup_value -vel, amely ebben az esetben a B1 cellából származó értékesítés. A kurzort az Lookup_value mezőbe helyezzük, majd egyszer a B1 cellára kattintunk:
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 az Table_array mezőbe helyezzük, majd kiemeljük a teljes adattáblázatot - az :
fejlécek kivételével. Meg kell adnunk, hogy a táblázat mely oszlopai tartalmazzák az általunk kívánt formanyomtatványra vonatkozó információkat. Ebben az esetben a táblázat második oszlopában szereplő jutalékot szeretnénk elérni, tehát 2 -t kell beírni az Col_index_num mezőbe:
Végül beírunk egy értéket az Range_lookup mezőbe.
Fontos: A mező használata ezzel megkülönbözteti a VLOOKUP használatának két módját. A VLOOKUP adatbázis használatához ezt a végső paramétert Range_lookup mindig FALSE értékre kell állítani, de ezzel a VLOOKUP más felhasználásával üresen kell hagynunk, vagy be kell adnunk az TRUE értékét. A VLOOKUP használata során elengedhetetlen, hogy a végleges paramétert a megfelelő választásnál végezze el.
Az Range_lookup mezőbe be kell írnunk az valódi értékét. Szintén helytelen lehet üresen hagyni, mivel ez az alapértelmezett érték:
Az összes paramétert elvégeztük. Most kattintsunk az OK gombra, és az Excel elkészíti a VLOOKUP képletünket:
Ha néhány különböző értékesítési összeget kísérelünk meg, akkor meggyőződhetünk arról, hogy a képlet működik.
Következtetés
A VLOOKUP "adatbázis" verziójában, ahol az Range_lookup paraméter FALSE , az értékének át kell adnia 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:
Ha az Range_lookup TRUE ( vagy kihagyott) értékre van állítva, a VLOOKUP az 1. oszlopban fog megjelenni, és megfelel az értéknek, amely nem nagyobb az Lookup_value paraméternél.
Fontos megjegyezni, hogy a rendszer működtetéséhez az táblázatot az 1. oszlop szerint növekvő sorrendben kell rendezni!
Ha szeretne gyakorolni a VLOOKUP programmal, a cikkben bemutatott mintafájl letölthető innen.
