A VLOOKUP egyike az Excel leghasznosabb funkcióinak, és az egyik legkevésbé ismert. Ebben a cikkben a VLOOKUP demystify valós életmóddal. Hozzunk létre egy használható Számla sablon egy fiktív társaság számára.
A VLOOKUP egy Excel funkció. Ez a cikk azt feltételezi, hogy az olvasónak már van egy elmúlt ismerete az Excel funkcióknak, és olyan alapvető funkciókat is használhat, mint a SUM, az AVERAGE és a TODAY. Leggyakrabban a VLOOKUP a adatbázis funkció, azaz adatbázis-táblákkal dolgozik - vagy egyszerűbben, listák a dolgok egy Excel munkalapon. Milyen dolgokat? Jól, bármilyen valamiféle. Lehet, hogy van egy munkalapja, amely tartalmazza az alkalmazottak, a termékek, a vásárlók vagy a CD-k listáját a CD-gyűjteményben, vagy csillagokat az éjszakai égbolton. Nem igazán számít.
Íme egy példa egy listára vagy adatbázisra. Ebben az esetben ez a termékek listája, amelyet fiktív cégünk elad:
Általában ilyen listáknak van egyfajta egyedi azonosítója a listában szereplő minden elemhez. Ebben az esetben az egyedi azonosító a "Tétel kód" oszlopban található. Megjegyzés: Ha a VLOOKUP függvény egy adatbázis / listával dolgozik, akkor a listának tartalmaznia kell egy egyedi azonosítót (vagy "kulcsot" vagy "azonosítót") tartalmazó oszlopot, és az oszlopnak az első oszlopnak kell lennie. A fenti minta adatbázisunk ezt a kritériumot kielégíti.
A VLOOKUP legnehezebb része pontosan megérteni, hogy miről van szó. Lássuk tehát, hogy először világossá váljunk:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
A fenti példában a VLOOKUP függvényt egy tételkóddal egy másik táblázathoz kell beilleszteni, és visszaadja Önnek a megfelelő tétel leírását, árát vagy elérhetőségét (az "In stock" mennyiségét), amint azt az eredeti lista. Az alábbi információk közül melyik fog átengedni? Nos, el kell döntenie, amikor létrehozza a képletet.
Ha mindössze annyi adatra van szüksége, amely az adatbázisból származik, akkor nagyon nehéz lenne egy olyan VLOOKUP függvény létrehozására. Általában ezt a funkciót használja egy újrafelhasználható táblázatban, például sablonban. Minden alkalommal, amikor valaki valódi tételkódot ad meg, a rendszer megkapja az összes szükséges információt a megfelelő tételről.
Hozzunk létre egy példát erre: An Számla sablon hogy újra és újra használhatjuk fiktív társaságunkban.
Először elindítjuk az Excel-t, és létrehozunk egy üres számlát:
Ennek a példának egyszerű megtartása érdekében a termékadatbázisot egy külön lapon fogjuk megtalálni ugyanabban a munkafüzetben:
Tehát létrehoztuk a termékadatbázisunkat, amely így néz ki:
Meg kell találnunk az Excel által kínált valamennyi rendelkezésre álló funkció listáját, hogy választhassuk a VLOOKUP-ot, és segítsünk a képlet kitöltésében. Ezt úgy találja meg, hogy először rákattint a képletek lapot, majd kattintson rá Beszúrás funkció:
Ha keresni szeretnénk, kereshetnénk egy keresési kifejezést, mint a "lookup" (mert a funkció, amire kíváncsi vagyunk, Nézz fel funkció). A rendszer visszaadja nekünk az Excel minden keresési funkciójának listáját. VLOOKUP a második a listában. Kattintás kiválasztása rendben.
A Funkcióelméletek doboz jelenik meg, és felszólít minket érvek (vagy paraméterek) szükséges a VLOOKUP funkció befejezéséhez. Ezt a négyzetet úgy értelmezheti, mint a következő kérdéseket feltenné:
- Milyen egyedi azonosítót keres az adatbázisban?
- Hol van az adatbázis?
- Mely adatbázist tartalmaz az egyedi azonosítóval társított adatbázisban, szeretné megkapni az Ön számára?
Az első három argumentum látható kiemelten, jelezve, hogy ők kötelező argumentumok (a VLOOKUP függvény hiányos, és nem érvényes értéket ad vissza). A negyedik argumentum nem merész, azaz opcionális:
Az első érv, amit meg kell fejezni, a keresési_érték érv. A függvénynek meg kell adnia, hogy hol találja meg az egyedi azonosítót (a termékkód ebben az esetben), hogy visszaadja a. Meg kell választanunk a korábban megadott kódot (A11-ben).
Kattintson az első argumentum jobb oldalán lévő választó ikonra:
Ezután kattintson egyszer az elem kódjára (A11) tartalmazó cellára, és nyomja meg a gombot Belép:
Most meg kell adnunk egy értéket a tábla_tömb érv. Más szóval meg kell mondanunk a VLOOKUP-nak, hogy hol találja meg az adatbázisot / listát. Kattintson a választó ikonra a második argumentum mellett:
… és nyomja meg Belép. Az adatbázist reprezentáló cellák tartománya (ebben az esetben "A termék adatbázis" A2: D7 ") automatikusan megadódik számunkra a második argumentumhoz.
Most meg kell adnunk a harmadik érvet, oszlopszám. Ezt az érvet használjuk annak érdekében, hogy megadjuk a VLOOKUP-nak, mely adatbázist az adatbázisban, az A11-ben szereplő elemkóddal társítjuk. Ebben a konkrét példában azt szeretnénk, hogy az elem legyen leírás visszatért hozzánk. Ha megnézed az adatbázis munkalapot, észre fogod venni, hogy a "Leírás" oszlop a második oszlop az adatbázisban. Ez azt jelenti, hogy be kell adnunk a "2" értéket a oszlopszám doboz:
Fontos megjegyezni, hogy itt nem adunk meg egy "2" -t, mert a "Leírás" oszlop a B oszlopot a munkalapon. Ha az adatbázis az oszlopban kezdődik K a munkalapon még mindig "2" -et írunk be ebben a mezőben, mert a "Leírás" oszlop a "Table_array" meghatározásakor kiválasztott cellák második oszlopa.
Végül el kell dönteni, hogy beírjuk-e az értéket a végső VLOOKUP argumentumba, tartományban_keres. Ez az érvelés vagy a igaz vagy hamis értéket, vagy üresnek kell lennie. Ha a VLOOKUP-t adatbázisokkal használjuk (ahogyan az az idő 90% -a), úgy dönthetünk el, hogy mi az, ami ezt az érvet illeti, az alábbiakra gondolhatunk:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Adatbázisunk első oszlopaként nem rendezve, belépünk hamis ezt az érvelést:
Ez az! A VLOOKUP-hoz szükséges összes információt megadtuk a szükséges érték visszaadásához. Kattints a rendben gombot, és észreveszi, hogy az "R99245" cikkszámnak megfelelő leírás helyesen lett beillesztve a B11 cellába:
Ha belépünk a különböző elem kódot az A11 cellába, akkor meglátjuk a VLOOKUP függvény teljesítményét: A leírás cellája megváltozik, hogy megfeleljen az új elemkódnak:
Hasonló lépéseket tudunk végrehajtani az elem megszerzéséhez ár visszatért az E11 cellába. Vegye figyelembe, hogy az új képletet az E11 cellában kell létrehozni. Az eredmény így fog kinézni:
Vegyük észre, hogy a két képlet közötti egyetlen különbség a harmadik érv (oszlopszám) "2" -ról "3" -ra változott (mert az adatokat a 3. oszlopból az adatbázisban szeretnénk megkapni).
Ha úgy döntöttünk, hogy 2 ilyen terméket vásárolunk, akkor egy "2" -t kell beírni a D11 cellába. Egyszerű képletet adtunk be az F11 cellába, hogy megkapjuk a teljes sort:
=D11*E11
… ami így néz ki …
A számla sablon kitöltése
Eddig sokat tanultunk a VLOOKUP-ról. Tény, hogy megtanultam mindent, amit tanulni fogunk ebben a cikkben. Fontos megjegyezni, hogy a VLOOKUP adatbázisok mellett más körülmények között is használható. Ez kevésbé gyakori, és a későbbi How-To Geek cikkekben is szerepelhet.
Számla sablonunk még nem teljes. Ehhez a következőket tennénk:
-
A mintaelem kódját eltávolítjuk az A11 cellából és a "D11" cellából. Ez az újonnan létrehozott VLOOKUP képletek megjelenítik a hibaüzeneteket:
Image Ezt az Excel józan használatával orvosolhatjuk HA() és ISBLANK () funkciókat. Ezt a képletet megváltoztatjuk … = VLOOKUP (A11, "Adatbázis"! A2: D7,2, FALSE) …ehhez… = IF (ISBLANK (A11), "", VLOOKUP (A11, "Termék adatbázis", A2: D7,2, FALSE)
- A képleteket a B11, E11 és F11 cellákban másoljuk a számla tétel sorainak hátralevő részére. Ne feledje, hogy ha ezt tesszük, az így kapott képletek nem fognak megfelelően hivatkozni az adatbázis táblázatra. Meg tudjuk oldani ezt azáltal, hogy megváltoztatjuk az adatbázis cellahivatkozásait abszolút sejt referenciák. Alternatívaként - és még jobb - létrehozhatnánk egy tartománynév (például "Termékek"), és használja ezt a tartománynevet a cellahivatkozások helyett. A képlet ettől fog változni … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Termék adatbázis", A2: D7,2, FALSE) …ehhez… = HA (ISBLANK (A11),””, VLOOKUP (A11, Termékek, 2, HAMIS)) …és azután másolja a képleteket a számlaelem sorainak többi részére.
- Valószínűleg "lezárnánk" a képleteket tartalmazó cellákat (vagy inkább kinyit a más cellákat), majd védje a munkalapot annak biztosítása érdekében, hogy gondosan megalkotott képletünket ne véletlenül felülírjuk, amikor valaki kitölti a számlát.
- A fájlt a sablon, hogy mindenki használhassa újra a cégünket
Ha éreztük magunkat igazán okos, minden ügyfélünk adatbázisát létrehoznánk egy másik munkalapon, majd felhasználhatjuk az F5 cellában megadott ügyfél-azonosítót, hogy automatikusan kitöltse az ügyfél nevét és címét a B6, B7 és B8 cellákban.
