VLOOKUP példa - Hogyan kell elvégezni a VLOOKUP programot az Excelben

A Microsoft Excel különféle funkciókat tartalmaz, amelyek segítséget nyújtanak a felhasználóknak bármilyen típusú számításokhoz. Az Excel funkcionalitása annyira átfogó, hogy az átlag felhasználók még a legtöbb segédprogram előnyeit sem használják ki.

Azonban, ha gyakran görgeti az oszlopokat és a sorokat, és ugyanazokat az információkat keresi, akkor valószínű, hogy értékelni fogja a VLOOKUP funkciót. A „vertikális keresés” rövidítéssel ellátott VLOOKUP segítségével gyorsan megtalálja a megadott értékhez társított adatokat.

Például lehet egy táblázata, amely egyedi azonosítókkal és árakkal rendelkező termékeket tartalmaz. A VLOOKUP megmutatja egy adott termék árát, ha megadja annak azonosítóját.

A VLOOKUP-ot sokféleképpen használhatja, és ez jelentősen leegyszerűsíti a munkáját, különösen akkor, ha nagy táblákkal foglalkozik.

Nem kell sok időt tölteni egy adott cella keresésével, mert ez a funkció megtalálja az Ön számára. A kezdő felhasználóknak azonban gyakran nehéz a VLOOKUP beállítása. Ezért úgy döntöttem, hogy segítek önnek a részletes útmutató elkészítésével.

Mi az a VLOOKUP?

Először is, a VLOOKUP egy függvény. Ezért, ha még nem ismeri az Excel alkalmazást, érdemes megismerkednie néhány alapvető funkcióval, például az ÁTLAG, a SUM vagy a TODAY funkcióval. Így könnyen megértheti ennek a funkciónak a működését.

A VLOOKUP egy adatbázis-függvény, ezért adatbázis-táblákhoz készült. Az ilyen táblázatok alapvetően a különböző elemek listáját tartalmazzák. Ezt a funkciót például akkor használhatja, amikor terméklistákkal, alkalmazottakkal, ügyfelekkel stb. Dolgozik.

Tegyük fel, hogy van egy négy oszlopból álló terméklistája. Tartalmazhatja az első oszlopban szereplő tételkódot, a második oszlopban a termék nevét vagy leírását, valamint a harmadik és a negyedik oszlopban az árat és a készleten kapható cikkek számát.

Az adatbázis-táblák általában valamilyen egyedi azonosítóval rendelkeznek minden elemhez. Ebben az esetben ez az elem kódja. Ez az oszlop a VLOOKUP függvény működéséhez szükséges, és a táblázat első oszlopának kell lennie.

Ha Ön kezdő, akkor először meg kell értenie, hogy a VLOOKUP pontosan mit csinál. Egyszerűen fogalmazva, egy listából vagy adatbázisból származó információkat jelenít meg a felhasználó által megadott egyedi azonosító alapján.

Ha figyelembe vesszük a fenti példát, ez a funkció megmutathatja a termék árát, leírását vagy elérhetőségét a termék kódja alapján. Hogy pontosan mit fog mutatni, az az általad írt képlettől függ. A VLOOKUP támogatja a pontos és a hozzávetőleges egyezést, valamint a helyettesítő karaktereket a részleges mérkőzésekhez.

Hogyan működik a VLOOKUP

A VLOOKUP függvényt leíró képletek szintaxisa:

=VLOOKUP(value, table, column_index, [range])

  • value ez a függvény fogja keresni az első oszlopban
  • tableaz a táblázat, amelyből a függvény lekéri a szükséges információkat
  • column_index annak az oszlopnak a száma, ahonnan a függvény lekéri az információkat
  • rangeegy logikai paraméter, amely lehet IGAZ vagy HAMIS. A TRUE az alapértelmezett érték, és hozzávetőleges egyezésnek felel meg. A FALSE csak a pontos egyezéseket jeleníti meg.

Még ennek a függvénynek a neve is tartalmazza a „vertikális” szót, a VLOOKUP pedig csak olyan táblákhoz készült, ahol az adatokat függőleges oszlopokba rendezték. Ezért, ha az adatokat vízszintesen rendezi, akkor ez a funkció haszontalan lesz. Ebben az esetben használhat hasonló funkciót a vízszintes kereséshez - HLOOKUP.

Ne feledje azt is, hogy ez a funkció csak balról jobbra működik. Más szavakkal, ha az egyedi azonosító nem szerepel a táblázat első oszlopában, akkor a függvény nem képes információkat lekérni az azonosítótól balra lévő oszlopokból.

Minden oszlopnak megvan a maga száma, és az összes oszlop balról jobbra van számozva. Ha egy adott oszlopból szeretne értéket szerezni, akkor adja meg annak számát a képletben. A fenti képletsablonban ezt a számot hívják meg column_index.

Például, ha be szeretné szerezni a termék nevét a fenti példából, az oszlopindexnek 2-nek kell lennie.

Amint azt már fentebb említettem, a VLOOKUP funkció két egyező módot támogat: közelítő és pontos. Ez a paraméter a képlet negyedik argumentuma. A hozzávetőleges egyezés alapértelmezés szerint meg van adva. Ha pontos egyezést szeretne választani, akkor állítsa a keresési tartományt erre FALSE.

Ezért mindkét alábbi képlet hozzávetőleges egyezéssel fogja lekérni az adatokat:

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index, TRUE)

Mint láthatja, ha a pontos egyezési módot szeretné használni, legyen óvatos. Ha nem ad meg semmilyen keresési tartomány értéket, a funkció továbbra is a hozzávetőleges egyezési módot használja.

A következő képlet kényszeríti a pontos egyezési módot:

=VLOOKUP(value, table, column_index, FALSE)

Ne felejtse el beállítani az értéket, FALSEha a pontos egyezési módot fogja használni. Valószínű, hogy a legtöbb esetben pontos egyezésre lesz szüksége, ezért ha még nem ismeri az Excel alkalmazást, ne feledkezzen meg erről a részletről.

A pontos egyezés a megfelelő választás, ha van oszlop az elemazonosítóval. Lehet bármilyen egyedi érték, amely felhasználható a pontos kereséshez. Ez lehet például egy könyv vagy film egyedi címe, valamint bármely más egyedi kulcsszó. Ne feledje, hogy a VLOOKUP nem különbözteti meg a kis- és nagybetűket.

Előfordulhat azonban, hogy nem a pontos, hanem a lehető legjobb egyezésre van szüksége. Ebben az esetben használhatja a hozzávetőleges egyezési módot.

Például használhatja ezt a módot olyan adattáblák kezeléséhez, ahol a szükséges információk megegyeznek bizonyos numerikus értékekkel, és olyan értékeket szeretne lekérni, amelyek nem szerepelnek a táblázatban.

Ezt a megközelítést akkor használhatja, amikor a meglévő adatok alapján számításokat végez. Ha megad egy értéket, és a függvény megtalálja a pontos egyezést, akkor információt kap a megfelelő sorból. Ha azonban nincs pontos egyezés a táblázatban, akkor a funkció meg fog egyezni az előző sorral.

Miért felelne meg az előző sornak, nem egy másiknak? Nos, nem lesz, ha nem megfelelően rendezi az asztalát. Ahhoz, hogy a VLOOKUP a legjobb hozzávetőleges értéket keresse, ellenőrizze, hogy az oszlop összes értéke növekvő sorrendben van-e rendezve. Ebben az esetben a függvény csak hátralép, és lekéri a legközelebbi értéket.

# N / A hibák

A VLOOKUP függvény, valamint az Excel sok más funkciójának használata során gyakran # N / A hibát kaphat. Ez a hiba azt jelenti, hogy az érték nem található.

Ezt a hibát több okból is megkaphatja. Íme néhány a leggyakoribb esetek:

  • hibásan írta az értéket, vagy adott egy extra helyet
  • a szükséges érték nem szerepel a táblázatban
  • a pontos egyezési módot használja, amikor hozzávetőleges értéket keres
  • nem adta meg a megfelelő táblázattartományt
  • a VLOOKUP fájlt másolta, miközben a táblázat hivatkozása nincs zárolva.

Ha a táblázat abszolút referenciával rendelkezik, ez azt jelenti, hogy a sorok és oszlopok nem változnak, ha másolják őket. Viszonylagos hivatkozással azonban nem ez a helyzet. Ebben az esetben át kell váltania az abszolút referenciára.

A # N / A hiba szövegét testre szabhatja az IFNA függvény használatával. Ebben az esetben hosszabb képletet kell írni az IFNA-val, amely tartalmazza a VLOOKUP-ot.

Íme egy példa egy képletre, amely # N / A helyett a „not found” értéket adja vissza:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

És itt van egy képlet, amely üres eredményt ad vissza:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

Bár testreszabhatja az üzenetet, fontolóra veheti a # N / A hiba használatát, mert az azonnal felkelti a figyelmét és tájékoztatja Önt, ha valami nem stimmel.

A VLOOKUP használata

Írhat képleteket a semmiből, vagy használhatja az Excel menüt is. Válassza ki azt a cellát, ahol meg akarja jeleníteni az eredményt, majd válassza a „Képletek” fület.

Ezután kattintson a „Funkció beszúrása” gombra. Megjelenik egy doboz, ahol kiválaszthatja a funkciók kategóriáit, és kiválaszthatja a VLOOKUP funkciót. Használhatja a „Funkció keresése” mezőt is, és beírhatja a „vlookup” szót.

Válassza ki a funkciót, és megjelenik a „Function Argument” mező. Itt adhatja meg a függvény szükséges paramétereit. Ebben az ablakban meg kell adnia a keresett egyedi azonosítót, az adatbázis helyét és a visszakeresni kívánt azonosítónak megfelelő információkat.

Ezek az argumentumok: „Keresési_érték”, „Táblázat_rajz” és „Col_index_num”. Ezeket a mezőket félkövéren írják, mert ezek az érvek kötelezőek.

A negyedik argumentum a keresési módra vonatkozik, és megadhatja, vagy nem. A hozzávetőleges mód alapértelmezés szerint van beállítva.

Az első argumentum megadásához, amely az egyedi azonosító, kiválaszthatja a szükséges cellát, és megnyomhatja az Enter billentyűt. Ebben az esetben ennek a cellának az értéke automatikusan be lesz írva a VLOOKUP függvény első argumentumaként.

Most meg kell adnia a második argumentumot. Az adatbázisnak nem feltétlenül a bal felső sarokban kell kezdődnie. Például lehet egy oszlopokat leíró sor is, amely fejlécként szolgál.

"Az egyik legjobb dolog a VLOOKUP funkcióban, hogy az adatbázis helye is testreszabható" - jegyzi meg Bridget Allen, a Best Writers Online könyvelője.

Tekintettel arra, hogy a VLOOKUP csak oszlopok számával működik, meg kell adnia, hogy a táblázat melyik területét szeretné használni a kereséshez. Erre szolgál a „Table_array” mező.

Például, ha a táblázata a bal felső sarokban kezdődik, és első sora egy fejléc, akkor az egész adatbázist kiválaszthatja az első sor nélkül. Ha az adatbázisában négy oszlop (AD) és öt elem van, a táblázat tömbje A2: D6 lesz, mert az A1-D1 cellák tartalmazzák a fejlécet.

Kattintson a szükséges munkalap fülre, és kiválaszthatja az adatbázissal ellátott területet. Nyomja meg az Enter billentyűt, és a kiválasztott cellatartomány automatikusan hozzáadódik a VLOOKUP függvény második argumentumához. Íme egy példa egy függvény argumentumra:

‘database_name’!A2:D6

Ebben az esetben az „adatbázis_név” a munkalap fül neve.

Most már csak meg kell határoznia, hogy milyen információkat szeretne letölteni, és meg kell adnia a szükséges oszlop számát.

Például, ha egy cikk árát szeretné lekérni a cikk elején található táblázatból, akkor használja a harmadik sort, és a „Col_index_num” értéke 3 lesz.

Csomagolás

A Microsoft Excel számos funkcióval rendelkezik, amelyek segíthetik a felhasználókat a különböző számítások és egyéb feladatok kezelésében. A VLOOKUP egy nagyon hasznos funkció, amely egy bizonyos értéknek megfelelő információt képes lekérni egy adatbázis-táblából.

Ha még nem ismeri az Excel alkalmazást, nehézségeket tapasztalhat a funkcióval kapcsolatban, mivel négy argumentuma van.

Ha azonban betartja útmutatónkat, kiválaszthatja a megfelelő argumentumokat és a megfelelő képleteket. Ha a gyakorlatban néhányszor használja ezt a funkciót, akkor gyorsan meg fogja érteni a működését, így bármikor képes lesz használni a VLOOKUP alkalmazást.