Nagyon sokan megpróbálják az Excel-t adatbázisként használni. Noha adatbázisként működhet, az adatbázis programban nagyon egyszerű feladatok némelyike meglehetősen összetett az Excel programban. E feladatok egyike két lista egyeztetése egy közös mező alapján; ezt könnyen meg lehet valósítani az Excel VLOOKUP segítségével. A VLOOKUP funkciót rendkívül hasznosnak találja, ezért nézze meg az alábbi példát, amikor és hogyan kell használni ezt a funkciót.
Tegyük fel, hogy az utazási irodája havi zárójelentést küld Önnek az alkalmazottak által bejárt összes helyről. A jelentés a repülőtér nevét használja városnevek helyett. Hasznos lenne, ha egyszerűen beírná a valódi városnevet a kód helyett.
Az interneten megtalálja és importálja a város nevét tartalmazó listát az egyes repülőtér-kódokhoz.
De hogyan szerzi meg ezeket az információkat a jelentés minden rekordjánál?
- Használja a VLOOKUP funkciót. A VLOOKUP a „Függőleges keresés” rövidítése. Bármikor fel lehet használni, ha van egy adatlista a kulcsmezővel a bal oldali oszlopban.
- Kezdd el beírni a funkció
=VLOOKUP(. Írja be a Ctrl + A billentyűkombinációt a funkcióval kapcsolatos segítségért. - A VLOOKUP-nak négy paraméterre van szüksége. Először az eredeti jelentés városkódja. Ebben a példában ez a D4 cella lenne
- A következő paraméter a tartomány a keresési táblázattal. Jelölje ki a tartományt. A tartomány abszolút értékének megadásához feltétlenül használja az F4 billentyűt. (Az abszolút hivatkozásnak mind az oszlop, mind a sor száma előtt dollárjel van. A képlet másolása után a hivatkozás továbbra is az I3: J351 felé mutat.
- A 3. paraméter megmondja az Excelnek, hogy melyik oszlopban található a város neve. Az I3: J351 tartományban a város neve a 2. oszlopban található. Adjon meg egy 2 értéket ehhez a paraméterhez.
- A 4. paraméter megmondja az Excel-nek, hogy a „szoros” egyezés rendben van-e. Ebben az esetben nem, ezért írja be a False értéket.
- Kattintson az OK gombra a képlet befejezéséhez. A képlet lefelé másolásához húzza a kitöltő fogantyút.
- Mivel gondosan adta meg az abszolút képleteket, átmásolhatja az E oszlopot a D oszlopba, hogy megkapja a célvárost. Ebben az esetben az összes indulás a torontói Pearson nemzetközi repülőtérről indul.
Bár ez a példa tökéletesen sikerült, amikor a nézők a VLOOKUP-ot használják, ez általában azt jelenti, hogy különböző forrásokból származó listákat illesztenek össze. Amikor a listák különböző forrásokból származnak, mindig lehetnek finom különbségek, amelyek megnehezítik a listák összehangolását. Íme három példa arra, hogy mi romolhat el, és hogyan lehet ezeket kijavítani.
-
Az egyik listában kötőjelek vannak, a másikban nem. A
=SUBSTITUTE()funkció segítségével távolítsa el a kötőjeleket. A VLOOKUP első próbálkozásakor N / A hibákat fog kapni.A kötőjelek képlettel való eltávolításához használja a SUBSTITUTE képletet. Használjon 3 argumentumot. Az első argumentum az értéket tartalmazó cella. A következő érv az a szöveg, amelyet módosítani szeretne. Az utolsó érv a helyettesítő szöveg. Ebben az esetben a kötőjeleket semmivé akarja változtatni, tehát a képlet az
=SUBSTITUTE(A4,"-","").A leírás megszerzéséhez beburkolhatja ezt a függvényt a VLOOKUP fájlba.
-
Ez finom, de nagyon gyakori. Az egyik listában egy üres hely van a bejegyzés után. A = TRIM () segítségével távolítsa el a felesleges szóközöket. Amikor először beírja a képletet, akkor az összes válasz N / A hibát jelent. Biztosan tudja, hogy az értékek szerepelnek a listában, és a képlettel minden rendben van.
Egy szokásos dolog, amelyet ellenőrizni kell, ha a keresési értékkel rendelkező cellába lép. Nyomja meg az F2 billentyűt a cella szerkesztési módba állításához. Szerkesztés módba lépve láthatja, hogy a kurzor egy szóközzel van az utolsó betűtől. Ez azt jelzi, hogy van egy szóköz a bejegyzésben.
A probléma megoldásához használja a TRIM funkciót.
=TRIM(D4)eltávolítja a vezető tereket, a hátsó tereket, és minden belső kettős helyet egyetlen szóközzel helyettesít. Ebben az esetben a TRIM tökéletesen működik a lemaradó tér eltávolításában.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)a képlet. -
Említettem egy bónusz tippet a műsorban: hogyan lehet a hiányzó értékek # N / A eredményét egy üresre cserélni. Ha a keresési érték nem szerepel a keresési táblázatban, a VLOOKUP N / A hibát ad vissza.
Ez a képlet a
=ISNA()függvény segítségével érzékeli, hogy a képlet eredménye N / A hiba. Ha megkapja a hibát, az IF függvény 2. argumentuma azt mondja az Excel-nek, hogy tegyen be tetszőleges szöveget.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
A VLOOKUP lehetővé teszi, hogy időt takarítson meg az adatok listájának összehangolásakor. Szánjon időt az alapvető használat elsajátítására, és sokkal hatékonyabb feladatokat hajthat végre az Excelben.
