VLOOKUP Két munkafüzet között: Egyszerű, lépésről lépésre útmutató

Ha több munkafüzettel dolgozik az Excelben, előfordulhat, hogy a VLOOKUP függvényt kell használnia az adatok egyik munkafüzetből a másikba való lekéréséhez.

A VLOOKUP függvény egy adott értéket keres egy külön munkafüzet táblázatában, és a megfelelő értéket adja vissza ugyanabból a sorból. A szintaxisnak tartalmaznia kell egy teljesen minősített hivatkozást a második munkafüzetre és a munkalap nevére.

A hivatkozások helyes megadása kihívást jelenthet, amikor először használja a funkciót. Ez a cikk mintatáblázatokat használ a képletek pontos bevitelének bemutatására.

Ezenkívül megismerheti a VLOOKUP néhány fejlesztését és a funkció számos alternatíváját.

Kezdjük el!

Tartalomjegyzék

• Mi az a VLOOKUP?
• A VLOOKUP használata két munkafüzet között
  • Minta adat
  • 1. lépés: Tartsa nyitva mindkét munkafüzetet
  • 2. lépés: Kezdje el beírni a VLOOKUP képletet
  • 3. lépés: Hivatkozás a második munkafüzetre
• 3 gyakori VLOOKUP hiba és javításuk
  • 1. #N/A Hiba
  • 2. #Ref! Oszlopszám hiba
  • 3. Idézetek és szögletes zárójelek hiba
• 4 tipp a különböző munkafüzetek kezeléséhez
• Az adatok automatikus frissítése
• A VLOOKUP funkció speciális felhasználásai
• A VLOOKUP 2 fő alternatívája
  • 1. INDEX MATCH
  • 2. XLOOKUP
• A VLOOKUP használata több lapon
  • 1. lépés: Azonosítsa az összetevőket
  • 2. lépés: Állítsa be adatait
  • 3. lépés: Használja a VLOOKUP funkciót
  • (Opcionális) 4. lépés: Az #N/A hibák hibaelhárítása
• Végső gondolatok

Mi az a VLOOKUP?

Tegyük fel, hogy van egy munkafüzete a termékértékesítésekről és egy külön munkafüzet az egyes termékek árairól. Az árakat a második munkafüzetből szeretné elérni, és az első munkafüzet számításaiban használni szeretné.

A VLOOKUP az egyik megoldás erre a feladatra. A kifejezés a „Függőleges keresés” rövidítése . "

Ez egy beépített funkció, amely lehetővé teszi, hogy egy adott értéket keressen egy oszlopban, és egy megfelelő értéket adjon vissza egy másik oszlopban a hely alapján.

Használható:

• egyetlen táblázat

• több lapot ugyanabban a munkafüzetben

• két Excel munkafüzet

Ha nem rendelkezik a több munkafüzet további bonyolultságával, tekintse meg ezt az oktatóanyagot itt: .

A VLOOKUP használata két munkafüzet között

A VLOOKUP funkció különösen akkor hasznos, ha több munkalapon vagy munkafüzetben kell adatokat keresnie.

Ennek három fő lépése a következő:

1. Nyissa meg mindkét munkafüzetet.

2. Írja be a VLOOKUP képletet az első Excel-munkafüzetbe.

3. Adjon meg egy teljesen minősített hivatkozást a második munkafüzetre.

Minta adat

Ez az egyszerű oktatóanyag példaadatokon alapuló példákon keresztül működik. A folytatáshoz hozzon létre egy új munkafüzetet „Eladások” néven, és írja be ezeket az adatokat az A és B oszlopba:

• Alma, 40

• Narancs, 30

• Körte, 70

• Banán, 20

Adjon hozzá egy harmadik oszlopot az árinformációk tárolására:

Hozzon létre egy második Excel-munkafüzetet „Termék” néven, és nevezze át az 1. munkalapot „Árak” névre. Írja be ezeket az adatokat az A és B oszlopba:

• Alma, 1,50

• Narancs, 2.30

• Körte, 3,60

• Banán, 2.10

Az adatok így néznek ki:

Az első táblázatban szereplő eladások bevételének kiszámításához meg kell keresnünk az árat a második táblázatban. VLOOKUP a megmentésre!

1. lépés: Tartsa nyitva mindkét munkafüzetet

Mindkét munkafüzetnek nyitva kell lennie, ha a VLOOKUP képletet két munkafüzet között használja. Ha a második munkafüzet be van zárva, a függvény hibát ad vissza.

Megnyithatja őket külön-külön, vagy az Excel „Megnyitás” parancsával egyszerre nyithatja meg őket. Mindkét munkafüzet kétféleképpen nyitható meg:

• Lépjen a Fájl fülre, és válassza a „Megnyitás” lehetőséget.

• Használja a „Ctrl + O” billentyűparancsot .

Miután mindkét munkafüzetet megnyitotta, válthat közöttük az Excel ablakának alján található megfelelő fülre kattintva.

2. lépés: Kezdje el beírni a VLOOKUP képletet

Íme a VLOOKUP függvény alapvető szintaxisa:

=VLOOKUP(keresési_érték, táblázat_tömb, oszlop_index_száma, [tartomány_keresése])

• lookup_value : A másik munkafüzetben keresni kívánt érték.

• table_array : A másik munkafüzet celláinak tartománya, amely a lekérni kívánt adatokat tartalmazza.

• col_index_num : Az oszlop száma abban a tartományban, amely a lekérni kívánt adatokat tartalmazza.

• range_lookup : Ez az utolsó paraméter nem kötelező. Ha IGAZ értékre állítja, vagy kihagyja, a VLOOKUP hozzávetőleges egyezést fog keresni. Ha FALSE-ra állítja, a VLOOKUP csak pontos egyezést fog keresni.

Tegyük fel, hogy a mintaadataink második munkafüzetében szeretne utánanézni az alma árának. Így bomlik le a képlet:

• A keresési_érték az első munkafüzet A2 cellájában található.

• A táblázattömb a második munkafüzet A1:B5 cellatartományában található.

• A col_index_num a második oszlop, amely az árat tartalmazza.

A VLOOKUP képlet legbonyolultabb része a második munkafüzet helyes hivatkozása.

3. lépés: Hivatkozás a második munkafüzetre

A VLOOKUP képlet két munkafüzethez való kitöltéséhez egy teljesen minősített hivatkozást kell használnia, amely tartalmazza a második munkafüzet alábbi részleteit:

• munkafüzet neve

• lap neve

• sejttartomány

Ez biztosítja, hogy az Excel pontosan tudja, hol találja a keresett adatokat. A teljes képzésű hivatkozás a VLOOKUP függvény table_array argumentumához használatos.

Ezek a mintaadatok értékei:

• munkafüzet neve: Product.xlsx

• lap neve: Árak

• cellatartomány: $A1:$B5

Az első cikk árának megkereséséhez írja be a következő képletet az első munkafüzet C2 cellájába:

=KERESÉS(A2, [Product.xlsx]Árak!$A$1:$B$5, 2, HAMIS)

Ez a kép az első cella képletsorában működő funkciót mutatja:

Vegye figyelembe, hogy a munkafüzet neve szögletes zárójelben van, a munkalap nevét pedig felkiáltójel követi.

Ha az Excel fájlnév szóközt vagy speciális karaktereket tartalmaz, akkor a teljes munkafüzetet és lapot egyetlen idézőjelbe kell írni.

A képlet abszolút értékeket használ a cellatartományhoz (a $ előtag). Ez lehetővé teszi a képlet lemásolását a többi sorba a kitöltő fogantyúra kattintva.

Íme a VLOOKUP eredményei:

És ez az! Most már tudja, hogyan kell hatékonyan használni a VLOOKUP-ot. A következő részben összegyűjtöttük a három leggyakoribb VLOOKUP-hibát, hogy segítséget nyújtsunk abban az esetben, ha bármilyen hibába ütközne.

3 gyakori VLOOKUP hiba és javításuk

Most, hogy megtanulta a VLOOKUP használatát egy másik munkafüzetből, előfordulhat, hogy gyakori hibákkal találkozhat.

Íme a három leggyakoribb hiba és a javításuk módja.

1. #N/A Hiba

Az #N/A hiba akkor fordul elő, ha a keresési érték nem található a táblatömbben. A hiba kijavításához próbálkozzon a következőkkel:

• Ellenőrizze a keresési érték helyesírását.

• Győződjön meg arról, hogy a keresési érték a táblatömb első oszlopában van.

• Ellenőrizze, hogy a keresési érték nem szövegként van formázva a két lapon.

• Győződjön meg arról, hogy a range_lookup argumentum FALSE értékre van állítva a pontos egyezés megtalálásához.

A fenti példa nem talált megfelelő értéket a cseresznyéhez a második munkafüzetben.

2. #Ref! Oszlopszám hiba

A # Ref! Hiba akkor történik, ha a col_index_num argumentum nagyobb, mint a táblatömb oszlopainak száma.

A hiba kijavításához ellenőrizze, hogy a col_index_num argumentum kisebb vagy egyenlő-e, mint a forrástábla oszlopainak száma.

Az alábbi képen a col_index_num értéke 3, de csak két oszlop van a referenciatartományban.

3. Idézetek és szögletes zárójelek hiba

Az idézőjelek és szögletes zárójelek hiba akkor fordul elő, ha a VLOOKUP műveletet idézőjeleket vagy szögletes zárójeleket tartalmazó értékkel hajtja végre.

A hiba kijavításához próbálkozzon a következőkkel:

• Használjon dupla idézőjeleket a keresési érték körül.

• Használja a CHAR(34) függvényt dupla idézőjel beszúrásához.

• Használja az IFERROR függvényt egy üres cella visszaadásához, ha a keresési érték idézőjeleket vagy szögletes zárójeleket tartalmaz.

Ha már ismeri ezeket a hibákat, a kijavításuk egyszerű! A következő részben négy tippet adunk a különböző munkafüzetek kezeléséhez annak érdekében, hogy VLOOKUP profivá válhasson.

4 tipp a különböző munkafüzetek kezeléséhez

Ha több munkafüzettel dolgozik, fontos nyomon követni, hogy melyik munkafüzet milyen adatokat tartalmaz.

Íme a négy legjobb tippünk a józanság megőrzéséhez:

1. Használjon elnevezési konvenciókat, hogy megjegyezze, mely Excel-táblázatok milyen adatokat tartalmaznak.

2. Használjon abszolút hivatkozásokat a képletekben annak biztosítására, hogy a keresési tartomány a megfelelő cellákra hivatkozzon, még akkor is, ha áthelyezi vagy másolja a képletet egy másik helyre vagy Excel-lapra.

3. Az IFERROR függvény segítségével kezelheti azokat a #N/A hibákat, amelyek akkor fordulhatnak elő, ha a keresési értékek nem találhatók a forrásadatokban.

4. Gondold át, hogy ne lehessen őket felülírni.

Az adatok automatikus frissítése

Ha több Excel-fájllal dolgozik, időigényes lehet a .

Ha azt szeretné, hogy a VLOOKUP képlet automatikusan frissüljön, amikor adatokat vesz fel vagy távolít el a többi munkafüzetből, használhatja az INDIRECT funkciót.

Itt van, hogyan:

• A VLOOKUP képletben cserélje ki a táblatömb argumentumát az INDIRECT függvényre.

• Az INDIRECT függvényben használjon hivatkozást a másik munkafüzet táblatömbjét tartalmazó cellára.

Ennek eléréséhez minta munkafüzeteinkkel kövesse az alábbi lépéseket.

1. Használja a D oszlop egyik celláját a „Termék” munkafüzet „Árak” lapjára való hivatkozás és az A1:B5 cellatartomány megadásához a következőképpen: [Termék.xlsx]Árak!A:B

2. A „Sales” munkafüzet C2 cellájában használja a következő képletet: =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. Másolja le a képletet (az abszolút hivatkozások biztosítják, hogy a táblázatot tartalmazó cella megmaradjon).

Ezek a műveletek biztosítják, hogy az eredeti munkalap módosításai automatikusan frissüljenek a csatlakoztatott cellákban.

Ez a kép az INDIRECT funkciót mutatja a VLOOKUP funkcióval kombinálva:

Vegye figyelembe, hogy ez a példa az „A:B” tartományt használja a két oszlop hivatkozására. Ez azt jelenti, hogy ha további termékeket és árakat ad hozzá, a képlet továbbra is működni fog az aktuális munkafüzetben.

Most, hogy belekóstoltál a VLOOKUP erejébe, vessünk egy pillantást a funkció néhány speciális felhasználására a részben!

A VLOOKUP funkció speciális felhasználásai

A cikkben szereplő példák minden keresési értékhez egyetlen egyezést adnak vissza. Ha több egyezéssel szeretne dolgozni egy másik munkafüzetből, használhat helyette egy tömbképletet.

A VLOOKUP funkciót más funkciókkal is kombinálhatja. Például beágyazhatja egy IF függvénybe a további összetett elemzés érdekében.

A komplex elemzés olyan technikákat foglal magában, mint a khi-négyzet tesztek és a páros minták T-tesztjei. Íme egy példa a khi-négyzet teszt használatára az Excelben:

A haladó felhasználók kihasználják a VLOOKUP előnyeit az adatkészletek összehasonlítására és az eltérések azonosítására. Ebben a cikkben más módszereket is talál.

De mi van akkor, ha bármilyen okból nem szeretné használni a VLOOKUP-ot? szerencséd van! Van néhány alternatíva, amivel a következő részben foglalkozunk.

A VLOOKUP 2 fő alternatívája

Amikor különböző lapokon keresünk értékeket, a VLOOKUP gyakran a fő funkció. Vannak azonban olyan alternatívák, amelyek bizonyos helyzetekben hasznosak lehetnek.

A két leggyakoribb:

• INDEX MATCH

• XLOOKUP

1. INDEX MATCH

Az INDEX és a MATCH két függvény hatékony kombinációja, amelyek segítségével értékeket lehet kikeresni egy táblázatban. A VLOOKUP-tól eltérően, amely megköveteli, hogy a keresési érték a táblázat bal szélső oszlopában legyen, az INDEX MATCH bármely oszlopban megkeresheti az értékeket.

Így működik:

1. Az INDEX függvény egy sor- és oszlopszám alapján egy megadott tartományból ad vissza értéket.

2. A MATCH függvény egy keresési érték pozícióját adja vissza egy megadott tartományban.

Az INDEX MATCH használatához meg kell adnia a keresni kívánt tartományt, azt a sort, ahol a keresési értéket szeretné megtalálni, és azt az oszlopot, amelybe az eredményt vissza kívánja adni.

Íme egy példaképlet:

=INDEX(Táblázattartomány,EGYEZÉS(Kikeresésiérték,Kikeresési tartomány,0),Oszlopindex)

Ennek a mintatáblázatokkal való használatához a következő képlet:

=INDEX([Termék.xlsx]Árak!$B$1:$B$5, MATCH(A2, [Product.xlsx]Árak!$A$1:$A$5, 0))

Ez a következőképpen bomlik:

• A MATCH függvény az A2 cellában (terméknév) lévő értéket keresi az A1:A5 tartományon belül a „Termék” munkafüzet „Árak” lapján.

• Az INDEX függvény egy értéket ad vissza a B1:B5 tartományból a „Termék” munkafüzet „Árak” lapján, a MATCH függvény által megadott sorszám alapján.

A képen a képlet látható működés közben:

2. XLOOKUP

Az XLOOKUP egy újabb funkció, amelyet az Excel 365- ben vezettek be . Hasonló a VLOOKUP-hoz, de néhány további funkcióval.

Az XLOOKUP egyik legnagyobb előnye, hogy a keresési oszloptól balra tud értékeket keresni.

Így működik:

1. Az XLOOKUP függvény egy megadott tartományból ad vissza egy értéket egy keresési érték alapján.

2. Megadhatja, hogy pontos vagy hozzávetőleges egyezést szeretne-e.

3. Azt is megadhatja, hogy mit küldjön vissza, ha nem található egyezés.

Az XLOOKUP használatához meg kell adnia a keresési értéket, a keresni kívánt tartományt és azt az oszlopot, amelybe az eredményt vissza kívánja adni.

Íme egy példaképlet:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"Nem található",0)

Ennek a mintatáblázatokkal való használatához a következő képlet:

=XLOOKUP(A2, [Termék.xlsx]Árak!$A$1:$A$5, [Termék.xlsx]Árak!$B$1:$B$5)

Ez a következőképpen bomlik:

• V2: A keresni kívánt érték (terméknév).

• [Product.xlsx]Prices!$A$1:$A$5: Az a tartomány, ahol a keresési értéket meg szeretné keresni (a terméknevek a „Termék” munkafüzet „Prices” lapján).

• [Product.xlsx]Prices!$B$1:$B$5: A visszatérési értékeket tartalmazó tartomány (árak a „Termék” munkafüzet „Árak” lapján).

Ez a képlet működés közben:

A VLOOKUP használata több lapon

Ha VLOOKUP-t kell végrehajtania két munkafüzet között, akkor előfordulhat, hogy több munkalapon is használnia kell a VLOOKUP-t.

Szerencsére ennek folyamata nagyon hasonlít a két munkafüzet közötti VLOOKUP végrehajtásához.

Négy lépés van.

1. lépés: Azonosítsa az összetevőket

Ahelyett, hogy belefoglalná a táblázattömböt, mint egy laphoz, a munkalap nevét is meg kell adnia.

Ha például egy A2:B6 tartományban lévő értéket szeretne keresni a Sales_reports.xlsx munkafüzet Jan lapján, a képlet a következőképpen néz ki:

=VKERESÉS(A2,[Sales_reports.xlsx]január!$A$2:$B$6,2,HAMIS)

2. lépés: Állítsa be adatait

Győződjön meg arról, hogy az adatok olyan módon vannak elrendezve, hogy könnyen hivatkozhassanak több lapra.

Érdemes lehet különböző lapokat létrehozni bizonyos referenciaadatokhoz.

3. lépés: Használja a VLOOKUP funkciót

Az adatok beállítása után a VLOOKUP funkcióval kereshet és kérhet le adatokat más lapokról.

Csak ügyeljen arra, hogy a képletben szerepeljen a munkalap neve, ahogyan az fent látható.

(Opcionális) 4. lépés: Az #N/A hibák hibaelhárítása

Ha #N/A hibákat észlel a VLOOKUP több munkalapon történő használatakor, ellenőrizze még egyszer a képletet, hogy megbizonyosodjon arról, hogy helyesen hivatkozott az összes szükséges munkalapra és tartományra.

Azt is érdemes ellenőrizni, hogy pontos egyezést végez-e a tartománykeresés helyett.

Végső gondolatok

Megtanulta, hogyan használhatja a VLOOKUP-ot adatok keresésére és lekérésére két táblázatban. Ez a sokoldalú funkció megkönnyíti az értékek megkeresését az egyik munkafüzetben a másikban lévő megfelelő értékek alapján.

A VLOOKUP funkció ereje jóval túlmutat az egyes munkalapokon, több munkafüzetet is elérhet az adatok összekapcsolásához és elemzéséhez.

A funkció elsajátítása jelentős lépés az Excelben való jártasság megszerzése felé, mivel új lehetőségeket nyit meg az adatok kezelésében és értelmezésében.

Az olyan funkciók beépítésével, mint az INDIRECT, INDEX-MATCH vagy XLOOKUP, tovább növelheti az adatlekérdezések rugalmasságát és hatékonyságát.

Gyakoroljon és fedezze fel az Excel hatalmas lehetőségeit; ez csak a jéghegy csúcsa. Boldog adatvadászatot!