1Aug
A VLOOKUP egyike az Excel leghasznosabb funkcióinak, és ez 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 cég számára. Az
VLOOKUP Excel funkció.Ez a cikk azt feltételezi, hogy az olvasónak már van múltbeli ismerete az Excel-funkcióknak, és olyan alapvető funkciókat is használhat, mint a SUM, az AVERAGE és a TODAY.Leggyakrabban a VLOOKUP egy adatbázis függvény, ami azt jelenti, hogy az adatbázis-táblákkal dolgozik - vagy egyszerűbben, az felsorolja az dolgokat egy Excel munkalapon. Milyen dolgokat? Nos, bármilyen típusú dolog. 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.
Itt van 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:
Az ilyen listáknak általában 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: Ahhoz, hogy a VLOOKUP függvény egy adatbázis / listával dolgozzon, az
listának -nek kell tartalmaznia az egyedi azonosítót( vagy "kulcs" vagy "ID") tartalmazó oszlopot és az -t, hogy az oszlop legyen a táblázat első oszlopa.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 tudjuk-e ezt egyértelműen feltenni: Az
VLOOKUP egy adatbázisból / listából adatokat gyűjt le az egyedi azonosító mellékelt példányán alapulva.
A fenti példában a VLOOKUP függvényt egy tételkóddal egy másik táblázathoz helyezheted el, és visszaadja Önnek a megfelelő tétel leírását, árát vagy elérhetőségét( "készlet" mennyisége) aeredeti 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 információra van szükséged az adatbázisban, akkor nagyon nehéz lenne megkeresni egy olyan VLOOKUP függvény létrehozását, amelyben van.Á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: számlázási sablon , amelyet újra és újra felhasználhatunk fiktív vállalatunkban.
Először elindítjuk az Excel-t, és létrehozunk egy üres számlát:
Így fog működni: A számlázási sablon felhasználója az "A" oszlopban kitölt egy sor kódot, és a rendszer minden elemet lekérdezleírás és ár a termék adatbázisából. Ezeket az információkat használjuk az egyes elemek sorának teljes számításához( feltételezve, hogy érvényes mennyiséget adunk meg).
Ennek a példának egyszerű megtartása érdekében a termékadatbázisot egy külön lapon találjuk ugyanabban a munkafüzetben:
A valóságban valószínűbb, hogy a termékadatbázis külön munkafüzetben található.Kevés a különbség a VLOOKUP függvényhez, ami nem érdekli, ha az adatbázis ugyanazon a lapon, egy másik lapon vagy egy teljesen más munkalapon található.
Így hoztuk létre a termékadatbázisunkat, amely így néz ki:
Ahhoz, hogy teszteljük a VLOOKUP képletet, amelyet meg akarunk írni, először egy érvényes kódot adunk az üres számlánk A11 cellájába:
vigye az aktív cellát a cellába, ahová a VLOOKUP adatbázisból lekérdezett információkat tárolni akarjuk.Érdekes, hogy ez a lépés, hogy a legtöbb ember téved. További magyarázatok: Olyan VLOOKUP formulát akarunk létrehozni, amely lekéri az A11 cellában lévő elem kódját. Hol akarjuk ezt a leírást, amikor megkapjuk? Természetesen a B11 cellában.Így írjuk a VLOOKUP képletet: a B11 cellában. Most válassza a B11 cellát.
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, hogy az Formulas lapra kattint, majd az beszúrási függvény :
Megjelenik egy fiók, amely lehetővé teszi számunkra, hogy kiválasszuk az Excel-ben elérhető funkciókat.
Ahhoz, hogy megtaláljuk a keresett kifejezést, beírhatnánk egy keresési kifejezést, mint a "lookup"( mert az a funkció, amelyet érdekel az funkció).A rendszer visszaadja nekünk az Excel minden keresési funkciójának listáját. A VLOOKUP a második a listában. Válasszon egy kattintást OK .
Az függvény argumentumai mező jelenik meg, amelyben minden argumentum ( vagy paraméter ) szükséges a VLOOKUP funkció befejezéséhez. Ezt a négyzetet a következő kérdésekre gondolhatja:
- Milyen egyedi azonosítót keres az adatbázisban?
- Hol van az adatbázis?
- Melyik adatbázist az egyedi azonosítóval társították, akarja megkapni az Ön számára?
Az első három argumentum merevlemez jelzéssel jelenik meg, jelezve, hogy kötelező argumentumok( a VLOOKUP függvény hiányos, és nem ad vissza érvényes értéket).A negyedik argumentum nem félkövér, azaz opcionális:
Az argumentumokat teljes egészében, felülről lefelé töltjük le.
Az első argumentum, amelyet meg kell adnunk, az Lookup_value argumentum. A funkciónak meg kell adnia, hogy hol találja meg az egyedi azonosítót( elemkód ebben az esetben), hogy visszaadja a leírását. Meg kell választanunk a korábban megadott kódot( A11-ben).
Kattintson az első argumentum jobb oldalán levő választó ikonra:
Akkor kattintson egyszer a céllal, amely tartalmazza az elem kódját( A11), és nyomja meg az billentyűt :
Az "A11" érték be van helyezve az első argumentumba.
Most meg kell adnunk egy értéket az Table_array argumentumhoz. 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:
Most keresse meg az adatbázis / listát, és válassza ki a teljes listát - , az fejlécvonal nélkül. Példánkban az adatbázis egy külön munkalapon található, ezért először a munkalap fülére kattintunk:
Ezután kiválaszthatjuk az egész adatbázist, a fejlécvonal nélkül:
. .. és nyomjuk meg az Enter parancsot. 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 argumentumot, Col_index_num .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 leírása visszatért hozzánk. Ha megnézed az adatbázis munkalapot, észre fogod venni, hogy a "Leírás" oszlop az második oszlop az adatbázisban. Ez azt jelenti, hogy az Col_index_num mezőbe be kell írnunk a "2" értéket:
Fontos megjegyezni, hogy itt nem adunk meg egy "2" -t, mert a "Leírás" oszlop az B oszlopban vanez a munkalap. Ha az adatbázis a munkalap K oszlopában megkezdődött, 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, Range_lookup .Ez az argumentum valódi vagy false értéket igényel, vagy üresnek kell lennie. Amikor a VLOOKUP-t adatbázisokkal használjuk( az idő 90% -ára igaz), az alábbi módon gondolhatjuk meg, hogy mit kell tenni ebben az argumentumban:
Ha az adatbázis első oszlopa( az egyedi azonosítókat tartalmazó oszlop)növekvő sorrendben sorrendben betűrendben / numerikusan sorba rendezve, akkor az valódi értékét beírhatja az argumentumba, vagy üresen hagyhatja.
Ha az adatbázis első oszlopa nem rendezve van, vagy rendezett sorrendben, akkor az -nek -nek kell megadnia az false értéket ebben az argumentumban
Mivel adatbázisunk első oszlopa nem rendezve van, hamis -t adunk be ez az argumentum:
Ez az! A VLOOKUP-hoz szükséges összes információt megadtuk a szükséges érték visszaadásához. Kattintson az OK gombra, és vegye észre, hogy az "R99245" elemcímnek megfelelő leírás helyesen lett beillesztve a B11 cellába:
A hozzánk létrehozott képlet így néz ki:
Ha különböző elemkódot adunk a cellábaA11, elkezdjük látni a VLOOKUP funkció erejét: A leírás cellája megváltozik, hogy megfeleljen az új elemkódnak:
Hasonló lépéseket tudunk végrehajtani ahhoz, hogy az elem ára 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:
. .. és a képlet így fog kinézni:
Megjegyezzük, hogy az egyetlen különbség a két képlet között a harmadik argumentum( Col_index_num ) változott a "2" -ról a "3" -re( mertaz adatgyűjtés 3. oszlopából származó adatokat szeretnénk elérni).
Ha úgy döntöttünk, hogy 2 ilyen tárgyat veszünk, akkor egy "2" -t adunk a D11 cellába. Ekkor egy egyszerű képletet adtunk 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. Ahhoz, hogy befejezzük, a következőket tennénk:
- Elvettük a mintaelem kódját az A11 cellából és a "2" jelet a D11 cellából. Ez az újonnan létrehozott VLOOKUP képletek megjelenítik a hibaüzeneteket:
Meg tudjuk oldani ezt az Excel IF() és ISBLANK() funkcióinak ésszerű használatával. A képletet megváltoztatjuk. .. = VLOOKUP( A11, "Termék adatbázis" A2: D7,2, FALSE) . .. ehhez. .. = IF( ISBLANK( A11), "VLOOKUP( A11," Product Database'! A2: D7,2, FALSE))
- A képleteket a B11, E11 és F11 cellákban másoljuk a számla sorai sorának 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ázishoz tartozó cellaszámokat az abszolút sejt referenciáihoz. Alternatívaként - és még jobb - létrehozhatnánk egy tartománynevet -t a teljes termékinformációhoz( például "Termékek"), és használhatnánk ezt a tartománynevet a cellahivatkozások helyett. A képlet változik ettől. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, "Termék adatbázis" A2: D7,2, FALSE)) . .. ehhez. .. = IF( ISBLANK( A11), "VLOOKUP"( A11, Products, 2, FALSE)) . .. és , akkor az átmásolja a képleteket a számlaelemsor többi sorához.
- Valószínűleg "zároljuk" a képleteket tartalmazó cellákat( vagy inkább feloldja az más cellákat), majd megvédi a munkalapot annak biztosítása érdekében, hogy gondosan megalkotott képletünket ne véletlenül felülírjuk, amikor valaki kitölta számlán.
- Az sablont formátumban mentettük, így az
cég mindenkit újra felhasználhatta. Ha valóban okosnak éreztük volna, minden ügyfelünk adatbázisát létrehoznánk egy másik munkalapon, majd aaz F5 cellába beírt ügyfél-azonosító, hogy automatikusan kitöltse az ügyfél nevét és címét a B6, B7 és B8 cellákban.
Ha szeretne gyakorolni a VLOOKUP programmal, vagy egyszerűen csak látni fogja a számlázási sablonunkat, innen tölthető le.