tartalom
Nemrég egy cikket szenteltünk az egyik leghasznosabb Excel-függvénynek, az ún VPR és megmutatta, hogyan lehet ennek segítségével kinyerni a szükséges információkat egy adatbázisból egy munkalap cellába. Azt is megemlítettük, hogy a funkciónak két használati esete van VPR és csak az egyik foglalkozik adatbázis-lekérdezésekkel. Ebben a cikkben a funkció használatának egy másik, kevésbé ismert módját ismerheti meg VPR az Excelben.
Ha még nem tette meg, akkor feltétlenül olvassa el az utolsó cikket a funkcióról VPR, mert az alábbi információk azt feltételezik, hogy Ön már ismeri az első cikkben leírt elveket.
Amikor adatbázisokkal, függvényekkel dolgozik VPR egyedi azonosító kerül átadásra, amely a keresni kívánt információ azonosítására szolgál (például termékkód vagy ügyfélazonosító szám). Ennek az egyedi kódnak jelen kell lennie az adatbázisban, ellenkező esetben VPR hibát fog jelenteni. Ebben a cikkben megvizsgáljuk a funkció használatának ezt a módját VPRamikor az azonosító egyáltalán nem létezik az adatbázisban. Mintha a funkció VPR hozzávetőleges módra vált, és kiválasztja, hogy milyen adatokat adjon meg nekünk, amikor meg akarunk találni valamit. Bizonyos körülmények között pontosan erre van szükség.
Példa az életből. Kitűztük a feladatot
Illusztráljuk ezt a cikket egy valós példával – a jutalékok kiszámításával az értékesítési mutatók széles skálája alapján. Kezdjük egy nagyon egyszerű lehetőséggel, majd fokozatosan bonyolítjuk, amíg a probléma egyetlen racionális megoldása a függvény használata lesz. VPR. A fiktív feladatunk kezdeti forgatókönyve a következő: ha egy értékesítő egy év alatt több mint 30000 30 dollárt értékesít, akkor a jutaléka 20%. Ellenkező esetben a jutalék csak XNUMX%. Tegyük táblázatba:
Az eladó a B1 cellába írja be értékesítési adatait, a B2 cellában lévő képlet pedig meghatározza az eladó által elvárható megfelelő jutalék mértékét. Az eredményül kapott kamatlábat pedig a B3 cellában használja az eladónak fizetendő teljes jutalék kiszámításához (egyszerűen megszorozva a B1 és B2 cellákat).
A táblázat legérdekesebb része a B2 cellában található – ez a képlet a jutalék mértékének meghatározásához. Ez a képlet egy Excel függvényt tartalmaz IF (HA). Azoknak az olvasóknak, akik nem ismerik ezt a funkciót, elmagyarázom, hogyan működik:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Állapot egy függvény argumentum, amely felveszi bármelyik értékét IGAZ KÓD (IGAZ), ill HAMIS (HAMIS). A fenti példában a B1 kifejezés
Igaz, hogy B1 kisebb, mint B5?
Vagy mondhatod másképp is:
Igaz, hogy az éves eladások összértéke kisebb, mint a küszöbérték?
Ha erre a kérdésre válaszolunk IGEN (TRUE), akkor a függvény visszatér érték, ha igaz (érték, ha IGAZ). Esetünkben ez a B6 cella értéke lesz, azaz a jutalék mértéke, amikor az összértékesítés a küszöb alatt van. Ha a kérdésre válaszolunk NEM (HAMIS), majd visszatér érték, ha hamis (érték, ha FALSE). Esetünkben ez a B7 cella értéke, azaz a jutalék mértéke, amikor az összértékesítés meghaladja a küszöböt.
Mint látható, ha a 20000 2 dollár összértékesítést vesszük, 20% jutalékot kapunk a B40000 cellában. Ha 30 USD értéket adunk meg, akkor a jutalék mértéke XNUMX%-kal változik:
A mi táblázatunk így működik.
Bonyolítjuk a feladatot
Nehezítsük meg egy kicsit a dolgokat. Állítsunk be egy másik küszöböt: ha az eladó több mint 40000 40 dollárt keres, akkor a jutalék mértéke XNUMX%-ra emelkedik:
Minden egyszerűnek és világosnak tűnik, de a képletünk a B2 cellában észrevehetően bonyolultabbá válik. Ha alaposan megnézi a képletet, látni fogja, hogy a függvény harmadik argumentuma IF (IF) egy másik teljes értékű funkcióvá változott IF (HA). Ezt a konstrukciót függvények egymásba ágyazásának nevezzük. Az Excel örömmel megengedi ezeket a konstrukciókat, és még működnek is, de sokkal nehezebb elolvasni és megérteni őket.
Nem fogunk belemenni a technikai részletekbe – miért és hogyan működik, és nem megyünk bele a beágyazott függvények írásának árnyalataiba. Végül is ez a funkciónak szentelt cikk VPR, nem egy teljes útmutató az Excelhez.
Bárhogy is legyen, a képlet bonyolultabbá válik! Mi lenne, ha bevezetnénk egy másik lehetőséget 50%-os jutalékra azoknak az eladóknak, akik több mint 50000 60000 dollárt keresnek. És ha valaki 60 XNUMX dollárnál többet adott el, akkor fizet XNUMX% jutalékot?
Most a képlet a B2 cellában, még ha hiba nélkül íródott is, teljesen olvashatatlanná vált. Szerintem kevesen akarnak 4 szintű egymásba ágyazású képleteket használni projektjeikben. Biztos van egyszerűbb út?!
És van ilyen mód! A funkció segítségünkre lesz VPR.
A probléma megoldására a VLOOKUP függvényt alkalmazzuk
Változtassunk egy kicsit az asztalunk kialakításán. Ugyanazokat a mezőket és adatokat megtartjuk, de új, kompaktabb módon rendezzük el őket:
Szánjon rá egy pillanatot, és győződjön meg arról, hogy az új asztal Értékelési táblázat ugyanazokat az adatokat tartalmazza, mint az előző küszöbtábla.
A fő ötlet a függvény használata VPR táblázat szerint a kívánt tarifa mértékének meghatározásához Értékelési táblázat értékesítési mennyiségtől függően. Felhívjuk figyelmét, hogy az eladó olyan összegért is eladhat árut, amely nem egyenlő a táblázatban szereplő öt küszöbérték egyikével. Például 34988 dollárért eladhatná, de nincs ilyen összeg. Lássuk, hogyan működik VPR képes kezelni egy ilyen helyzetet.
VLOOKUP függvény beszúrása
Jelölje ki a B2 cellát (ahová be akarjuk szúrni a képletünket), és keresse meg VLOOKUP (VLOOKUP) az Excel függvénytárban: képletek (képletek) > Funkciókönyvtár (Funkciókönyvtár) > Keresés és referencia (Hivatkozások és tömbök).
Megjelenik egy párbeszédpanel Funkció érvek (Funkció argumentumok). Az argumentumok értékeit egyenként adjuk meg, kezdve ezzel Keresési_érték (Keresési_érték). Ebben a példában ez a B1 cellából származó eladások teljes összege. Helyezze a kurzort a mezőbe Keresési_érték (Lookup_value), és válassza ki a B1 cellát.
Ezután meg kell adnia a funkciókat VPRhol keressünk adatokat. Példánkban ez egy táblázat Értékelési táblázat. Helyezze a kurzort a mezőbe Táblázat_tömb (Táblázat), és válassza ki a teljes táblázatot Értékelési táblázatkivéve a fejléceket.
Ezután meg kell adnunk, hogy a képletünk segítségével melyik oszlopból szeretnénk adatokat kinyerni. Érdekel bennünket a jutalék mértéke, amely a táblázat második oszlopában található. Ezért az érveléshez Oszlop_index_szám (Oszlop_szám) írja be a 2-es értéket.
És végül bemutatjuk az utolsó érvet – Tartomány_keresés (Interval_lookup).
Fontos: ennek az érvnek a használata teszi a különbséget a függvény alkalmazásának két módja között VPR. Amikor adatbázisokkal dolgozik, az érv Tartomány_keresés (range_lookup) értéknek mindig rendelkeznie kell egy értékkel HAMIS (HAMIS) a pontos egyezés kereséséhez. A funkció használatában VPR, ezt a mezőt üresen kell hagynunk, vagy meg kell adnunk egy értéket IGAZ KÓD (IGAZ). Rendkívül fontos ennek a lehetőségnek a helyes kiválasztása.
Hogy érthetőbb legyen, bemutatjuk IGAZ KÓD (IGAZ) a terepen Tartomány_keresés (Interval_lookup). Bár ha üresen hagyja a mezőt, ez nem lesz hiba, hiszen IGAZ KÓD az alapértelmezett értéke:
Minden paramétert kitöltöttünk. Most nyomjuk OK, és az Excel egy függvény segítségével képletet készít nekünk VPR.
Ha több különböző értékkel kísérletezünk a teljes eladási összegre, akkor megbizonyosodunk arról, hogy a képlet megfelelően működik.
Következtetés
Amikor a funkció VPR adatbázisokkal dolgozik, argumentum Tartomány_keresés (tartomány_keresése) el kell fogadnia HAMIS (HAMIS). És a beírt érték mint Keresési_érték (Lookup_value) szerepelnie kell az adatbázisban. Más szóval, pontos egyezést keres.
A cikkben bemutatott példában nincs szükség pontos egyezésre. Ez az eset áll fenn, ha a függvény VPR hozzávetőleges módba kell váltania a kívánt eredmény eléréséhez.
Például: Meg akarjuk határozni, hogy a 34988 USD értékesítési volumenű értékesítőnél milyen arányt kell használni a jutalék kiszámításához. Funkció VPR 30%-os értéket ad vissza, ami teljesen helyes. De miért választotta ki a képlet azt a sort, amely pontosan 30%-ot tartalmaz, és nem 20%-ot vagy 40%-ot? Mit jelent a hozzávetőleges keresés? Tisztázzuk.
Amikor az érvelés Tartomány_keresés (interval_lookup) értéke van IGAZ KÓD (TRUE) vagy kihagyott, függvény VPR végighalad az első oszlopon, és kiválasztja a legnagyobb értéket, amely nem haladja meg a keresési értéket.
Fontos pont: Ahhoz, hogy ez a séma működjön, a táblázat első oszlopát növekvő sorrendbe kell rendezni.