Hogyan hasonlítsunk össze két oszlopot az Excelben a VLOOKUP segítségével

Ha nagy adatkészletekkel dolgozik az Excelben, előfordulhat, hogy két oszlopot kell összehasonlítania, hogy hasonlóságokat vagy különbségeket találjon.

A VLOOKUP egy hatékony funkció, amellyel két oszlop között kereshet egyező adatokat. A funkció lehetővé teszi, hogy az egyik oszlopban olyan értékeket keressen, amelyek a másikban jelennek meg.

Ez a cikk bemutatja, hogyan hasonlíthat össze két oszlopot a VLOOKUP segítségével, így hatékonyan elemezheti adatait.

Tartalomjegyzék

• A Vlookup funkció alapjai
  • Vlookup funkció
• Adatainak előkészítése a VLOOKUP-hoz
  • Az adatok formázása
• A VLOOKUP használata két oszlop összehasonlításához
  • Egyéb hibák kezelése
• A VLOOKUP alternatívái az oszlopok összehasonlításához
  • 1. Index és Match függvények használata
  • 2. Az XLOOKUP alkalmazása Excelben
• Öt tipp a VLOOKUP teljesítményének javításához
  • 1. Korlátozza a keresési tartományt
  • 2. Használjon abszolút hivatkozásokat
  • 3. Rendezze az adatokat
  • 4. Dupla VLOOKUP használata
  • 5. Használjon feltételes formázást
• A VLOOKUP speciális felhasználásai
• Végső szavunk

A Vlookup funkció alapjai

Tegyük fel, hogy van egy táblázata, amely két elemlistát tartalmaz az A és B oszlopban. Az 1. listában azokat az elemeket szeretné megkeresni, amelyek a 2. listában is megjelennek.

Elképzelhető, hogy a több ezer elemből álló listák manuális kezelése rendkívül időigényes lenne. Szerencsére a VLOOKUP-ot segíti!

A VLOOKUP kifejezés a Vertical Lookup rövidítése. A függvény összehasonlít két oszlopot, egyezést talál közöttük, és visszaadja a kapcsolódó értékeket.

Vlookup funkció

Í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])

Ez az elemek lebontása:

• lookup_value : az az érték, amelyet a table_array első oszlopában keresni szeretne.

• table_array : a keresni kívánt adatokat tartalmazó cellák tartománya.

• col_index_num : a table_array oszlopának száma, amelyből az értéket vissza kívánja adni.

• range_lookup : Nem kötelező. Ez vagy IGAZ (hozzávetőleges egyezés) vagy HAMIS (pontos egyezés). Az alapértelmezett érték IGAZ.

Adatainak előkészítése a VLOOKUP-hoz

Mielőtt a VLOOKUP-ot két összehasonlítására használná, elő kell készítenie az adatokat.

Hozzon létre két külön oszlopot a munkalapon, ahol össze szeretné hasonlítani az értékeket. Ez a cikk az A oszlopot és a B oszlopot használja példáinkhoz.

Íme a cikkben használt mintaadatok:

Ez a minta táblázat fejléccel, amelyet az „Adatok előkészítése a VLOOKUP-hoz” című szakaszhoz kell hozzáadni:

1. lista 2. lista
1. tétel 3.
tétel 2. tétel 4.
tétel 3. tétel 1.
tétel 4. tétel 5. tétel

Az adatok formázása

Fontos annak biztosítása, hogy az adatok mindkét oszlopban hasonló formátumúak legyenek. A VLOOKUP megkülönbözteti a kis- és nagybetűket, ami azt jelenti, hogy a nagybetűk és a kisbetűk számítanak.

Ha a formázás nem konzisztens, a végeredményben egyezési hibák fordulhatnak elő.

A hibák kockázatának minimalizálása érdekében célszerű eltávolítani az ismétlődő értékeket vagy az üres cellákat.

Az ismétlődő értékek eltávolítása:

1. Válassza ki az oszlopot.

2. Lépjen az Adatok fülre a felső szalagon.

3. Kattintson az Ismétlődések eltávolítása elemre az Adateszközök részben.

A gomb kissé nehezen látható. Ez a kép a segítségedre lesz:

Az üres cellák eltávolítása:

1. Válassza ki az oszlopot.

2. Lépjen a Kezdőlap fülre a felső szalagon.

3. Bontsa ki a „Keresés és kiválasztás” menüt.

4. Válassza a „Go to Special” lehetőséget.

5. Válassza az „Üres elemek” lehetőséget a lehetőségek közül.

6. Kattintson a „Törlés” gombra a Cellák részben.

A VLOOKUP használata két oszlop összehasonlításához

Az adatok előkészítése után megírhatja a VLOOKUP képletet, amellyel összehasonlíthatja két oszlopát az Excelben, és azonosíthatja az egyezéseket. Kovesd ezeket a lepeseket:

1. Válasszon ki egy cellát egy új oszlopban, ahol meg szeretné jeleníteni az összehasonlítási eredményeket (pl. C2 cella).

2. Írja be a következő képletet: =VLOOKUP(A2, B:B, 1, FALSE))

3. Nyomja meg az Entert a képlet alkalmazásához.

4. Egyező érték esetén az érték az eredményoszlop ugyanabban a sorában jelenik meg (pl. C oszlop).

5. Húzza le a képletet a C2-ből, hogy annyi cellába másolja, amennyire szüksége van.

Ha mintaadatainkat használja, az összehasonlítási eredményeket a harmadik oszlopba írja. A képlet három egyezést talál az 1. lista négy eleméből.

Figyelje meg, hogy a 2. elem #N/A jelzéssel jelenik meg. Az Excel azt mondja, hogy az egyezés nem alkalmazható, azaz nem található.

Ez helyes, és hasznos információ a hiányzó értékekről. Néhány Excel-felhasználó azonban azt gondolhatja, hogy adat- vagy funkcióhiba van.

Célszerű más jelzést mutatni, ha nem található egyezés. Ez egyszerűen üresség lehet.

Ehhez kapcsolja össze a VLOOKUP funkciót az IFNA funkcióval, így:

=IFNA(VLOOKUP(A1, B:B, 1, HAMIS), ""

Az IFNA funkció észleli a #N/A hibát, és a kimenetet egy üres szóközzel („”) helyettesíti. Használhatja az ISNA függvényt is, vagy további logikát is használhat az IF függvénnyel.

Egyéb hibák kezelése

A VLOOKUP időnként más hibákat is generálhat az összehasonlítási eredményekben. A #REF! Az Excel hibája valódi probléma az adatokkal.

Általában akkor fordul elő, ha a megadott tartomány nem megfelelő. Példánk a teljes oszlopra hivatkozott, de használhat függőleges cellatartományokat is.

Győződjön meg arról, hogy az Ön által hivatkozott keresési tartomány lefedi az összes összehasonlítani kívánt értéket.

A VLOOKUP alternatívái az oszlopok összehasonlításához

Két fő alternatív keresési függvény létezik, amikor két oszlopot hasonlít össze az Excelben az egyezések kereséséhez.

1. Index és Match függvények használata

A VLOOKUP használata helyett összehasonlíthat két oszlopot az Excelben az INDEX és a MATCH függvények kombinálásával.

Ez a módszer rugalmasabb módot biztosít az adatok keresésére, és különösen akkor hasznos, ha nem szomszédos oszlopokkal dolgozik, vagy ha az oszlopindex megváltozhat.

Ez az eredmény oszlopba írandó szintaxis:

=INDEX(visszatérési_tartomány, MATCH(keresési_érték, keresési_tartomány, 0))

• Return_range : a visszaadni kívánt adatokat tartalmazó cellák tartománya.

• Lookup_value : a keresési tartományon belül keresni kívánt érték.

• Keresési_tartomány : az a cellatartomány, amelyen belül meg szeretné találni a keresési értéket.

Az előző példákban szereplő adatok felhasználásával a következőképpen cseréljük le a VLOOKUP képletet:

=(INDEX(B:B, MATCH(A2, B:B, 0)))

A MATCH függvény a keresési érték relatív pozícióját adja vissza a keresési tartományon belül, az INDEX függvény pedig a megfelelő értéket a visszatérési tartományból.

Az ugyanazon adatokon kapott eredmények ugyanazok lesznek, mint a korábban használt VLOOKUP. Ez a kép a használt funkciókat mutatja:

A #N/A hibát egyéni üzenettel is helyettesítheti. Íme egy példa az IFERROR függvény használatára.

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), "Nem található"

2. Az XLOOKUP alkalmazása Excelben

Az Excel 365 vagy Excel 2019 felhasználók számára az XLOOKUP a VLOOKUP alternatív logikai tesztje a közös értékek megtalálásához két oszlopban.

Az XLOOKUP számos előnnyel rendelkezik. Segítségével vízszintesen és függőlegesen is kereshet adatokat, dolgozhat nem szomszédos oszlopokkal, és egyéni értékeket adhat meg a hibákhoz.

Az XLOOKUP szintaxisa a következő:

=XKERESÉS(keresési_érték, keresési_tartomány, visszatérési_tartomány, [ha_nem található], [egyezési_mód], [keresési_mód])

• lookup_value : a keresési tartományon belül keresni kívánt érték.

• lookup_range : az a cellatartomány, amelyen belül meg szeretné találni a keresési értéket.

• return_range : a visszaadni kívánt adatokat tartalmazó cellák tartománya.

• Szükség szerint adjon hozzá egyéni hibaértékeket, egyezési módot és keresési mód paramétereit.

Az XLOOKUP megkeresi a keresési értéket a keresési tartományon belül, és visszaadja a megfelelő értéket a visszatérési tartományból.

A képlet különösen akkor hasznos, ha két listája van több oszlopból álló adatkészletekbe ágyazva. Korábbi példáinkban a listák az első és a második oszlopban voltak, de előfordulhat, hogy a munkalap ennél több adatot tartalmaz.

Íme egy példa két listával a táblázat első és harmadik oszlopában:

Az XLOOKUP képlet így néz ki:

=XKERESÉS(A2,C:C,C:C””)

Ezen a képen az eredmény látható a D oszlopban található összehasonlítással. Az első érték megvan, de a második hiányzik.

Vegye figyelembe, hogy nincs extra hibaképletem, de a hiányzó érték üresen jelenik meg. Ez azért van, mert a negyedik paramétert használom, amely egy egyéni hibaérték. Ebben az esetben ez egy üres karakterlánc.

Bónuszként megmutatom, hogyan használhatja az XLOOKUP-ot a sorok összehasonlítására. Ha két listája van az 1. és a 2. sorban, a képlet legegyszerűbb használata így néz ki:

=XKERESÉS(B1, 2:2, 2:2, "")

Ez a kép a két sor eredményeit mutatja.

Továbbfejlesztett funkcionalitásával és rugalmasságával az XLOOKUP kiváló alternatívája a VLOOKUP-nak a listák Excelben való összehasonlítására annak megállapítására, hogy vannak-e egyezések.

Öt tipp a VLOOKUP teljesítményének javításához

A VLOOKUP függvény optimalizálásával elkerülheti a hosszú várakozási időt, és javíthatja az Excel-munkalap általános válaszkészségét.

Íme hat tipp és trükk, amellyel javíthatja a VLOOKUP Excelben való teljesítményét.

1. Korlátozza a keresési tartományt

A példákban egész oszlopokat használtam az egyszerűsítés érdekében. Ha nagy mennyiségű adattal dolgozik, kerülje ezt. A teljes oszlopok használata lelassíthatja az Excel számítási folyamatát.

Ehelyett próbálja meg az adatokhoz szükséges pontos tartományt használni (pl. A1:A100). Ez csökkenti azoknak a celláknak a számát, amelyeket a VLOOKUP függvénynek ki kell értékelnie.

2. Használjon abszolút hivatkozásokat

Adattartomány megadásakor (pl. a B2-től B5-ig terjedő cellák) abszolút hivatkozásokat használjon. Ez biztosítja, hogy a képlet konzisztens legyen, és az adatok létezzenek, amikor több cellába másolja.

Íme egy példa:

=KERESÉS(A2, $B$2:$B$5, 1, HAMIS)

3. Rendezze az adatokat

Ha tudja, hogy a keresési oszlopban lévő adatok növekvő sorrendben vannak rendezve, akkor a VLOOKUP értéket TRUE vagy 1 értékkel használhatja a „range_lookup” argumentumhoz.

Ennek hatására az Excel hozzávetőleges egyezést hajt végre, ami gyorsabb, mint az összes cella pontos egyezése. Ennek az opciónak a használatakor azonban legyen óvatos, mert a helytelen rendezés helytelen eredményhez vezethet.

4. Dupla VLOOKUP használata

Két VLOOKUP függvényt használhat az Excel keresési folyamatának felgyorsításához.

Az első VLOOKUP úgy határozza meg, hogy létezik-e a keresési érték, ha a 'col_index_num' értéket 1-re, a 'range_lookup' értéket pedig TRUE értékre állítja.

Ha IGAZ értéket ad vissza, egy második VLOOKUP lekéri a kívánt értéket, és a 'range_lookup' értéke IGAZ.

5. Használjon feltételes formázást

Az Excel-munkalap feltételes formázásával kiemelheti az egyező vagy hiányzó értékeket a megadott oszlopban. Egyedi értékekre is alkalmazhat színeket. Ez megkönnyíti az adatok olvashatóságát.

A feltételes formázás menüt a Kezdőlap lap stíluscsoportjában találja.

A VLOOKUP speciális felhasználásai

Páros minták A T-próbákat két rokon minta átlagának összehasonlítására használjuk. Ez a videó bemutatja ezek használatát az Excelben.

Ha vannak további adatok vagy változók, amelyekre hivatkoznia kell az elemzés során, a VLOOKUP segítségével lekérheti a szükséges értékeket egy másik tábláról vagy munkalapról.

A VLOOKUP segítségével például lekérheti a demográfiai információkat vagy a kezelési feltételeket az adatkészletben lévő minden egyes páros megfigyeléshez.

Végső szavunk

Mostanra már alaposan megértette a VLOOKUP használatát. Ez a hatékony funkció lehetővé teszi a két lista közötti különbségek és egyező értékek gyors azonosítását, így az adatelemzés hatékonyabb és pontosabb.

Látott példákat más keresési és hivatkozási funkciókra is, mint például az XLOOKUP, INDEX és MATCH. Ha ezeket az elemeket bővíti készségeivel, az tovább erősíti adatelemzési képességeit.

Gyakorolja és finomítsa VLOOKUP-készségeit, és hamarosan szakértővé válik az Excel oszlopainak összehasonlításában, így időt takaríthat meg és növelheti termelékenységét.