Hogyan hasonlítsunk össze két oszlopot Excelben (egyezések és különbségek esetén)
Videó megtekintése – Két oszlop összehasonlítása Excelben egyezések és különbségek esetén
Az egyik kérdés, amelyet gyakran kapok, a következő: “Hogyan hasonlítsunk össze két oszlopot Excelben?”.
Ez többféleképpen is elvégezhető, és az alkalmazandó módszer az adatszerkezettől és attól függ, hogy a felhasználó mit akar tőle.
Elképzelhető például, hogy két oszlopot szeretne összehasonlítani, és megkeresni vagy kiemelni az összes egyező adatpontot (amelyek mindkét oszlopban szerepelnek), vagy csak az eltéréseket (ahol egy adatpont az egyik oszlopban szerepel, a másikban nem) stb.
Miatt, hogy olyan sokat kérdeznek erről, úgy döntöttem, hogy megírom ezt a hatalmas bemutatót azzal a szándékkal, hogy a legtöbb (ha nem is az összes) lehetséges forgatókönyvre kiterjedjen.
Ha hasznosnak találja, adja tovább más Excel-felhasználóknak.
Ez az oktatóanyag a következőket tartalmazza:
Megjegyezzük, hogy az oszlopok összehasonlításának ebben az oktatóanyagban bemutatott technikái nem az egyetlenek.
Az adatállománya alapján előfordulhat, hogy módosítania vagy módosítania kell a módszert. Az alapelvek azonban változatlanok maradnának.
Ha úgy gondolja, hogy van valami, amit hozzá lehetne adni ehhez a bemutatóhoz, ossza meg velem a megjegyzések között
Két oszlop összehasonlítása pontos sorillesztésre
Ez az összehasonlítás legegyszerűbb formája. Ebben az esetben soronkénti összehasonlítást kell végeznie, és azonosítania kell, hogy mely sorok tartalmaznak azonos adatokat, és melyek nem.
Példa: Azonos sorban lévő cellák összehasonlítása
Az alábbiakban egy olyan adathalmazt mutatunk be, ahol azt kell ellenőriznem, hogy az A oszlopban lévő név megegyezik-e a B oszlopban lévővel vagy sem.
Ha van egyezés, akkor az eredményre “TRUE”-ként van szükségem, ha pedig nincs egyezés, akkor az eredményre “FALSE”-ként van szükségem.
Az alábbi képlet ezt tenné:
=A2=B2
Példa:
Ha leíróbb eredményt szeretne kapni, használhat egy egyszerű IF-formulát, amely “Egyezés” értéket ad vissza, ha a nevek megegyeznek, és “Nem egyezés” értéket, ha a nevek különböznek.
=IF(A2=B2,"Match","Mismatch")
Megjegyzés: Ha az összehasonlítást a nagy- és kisbetűkre érzékennyé szeretné tenni, használja a következő IF képletet:
=IF(EXACT(A2,B2),"Match","Mismatch")
A fenti képlettel az “IBM” és az “ibm” két különböző névnek minősül, és a fenti képlet “Mismatch”-et ad vissza.
Példa: Az egyező adatokkal rendelkező sorok kiemelése
Ha az egyező adatokkal rendelkező sorokat szeretné kiemelni (ahelyett, hogy az eredményt külön oszlopban kapná meg), akkor ezt a feltételes formázás használatával teheti meg.
A következők a lépések ehhez:
- Kiválasztja a teljes adathalmazt.
- Kattintson a ‘Kezdőlap’ fülre.
- A Stílusok csoportban kattintson a ‘Feltételes formázás’ lehetőségre.
- A legördülő listából kattintson az ‘Új szabály’ lehetőségre.
- Az ‘Új formázási szabály’ párbeszédpanelen kattintson az ‘Egy képlet használata a formázandó cellák meghatározásához’ lehetőségre.
- A képlet mezőbe írja be a következő képletet: =$A1=$B1
- Kattintson a Formázás gombra, és adja meg a megfelelő cellákra alkalmazni kívánt formátumot.
- Kattintson az OK gombra.
Az összes olyan cellát kiemeli, ahol a nevek minden sorban megegyeznek.
Két oszlop összehasonlítása és az egyezések kiemelése
Ha két oszlopot szeretne összehasonlítani és kiemelni az egyező adatokat, akkor a feltételes formázás duplikációs funkcióját használhatja.
Megjegyezzük, hogy ez más, mint amit az egyes sorok összehasonlításánál láttunk. Ebben az esetben nem soronkénti összehasonlítást végzünk.
Példa: Két oszlop összehasonlítása és az egyező adatok kiemelése
Gyakran kapunk olyan adathalmazokat, ahol vannak egyezések, de ezek nem biztos, hogy ugyanabban a sorban vannak.
Az alábbiak szerint:
Megjegyezzük, hogy az A oszlopban lévő lista nagyobb, mint a B oszlopban lévő. Emellett néhány név mindkét listában szerepel, de nem ugyanabban a sorban (például IBM, Adobe, Walmart).
Ha ki akarja emelni az összes egyező cégnevet, akkor ezt feltételes formázás segítségével teheti meg.
Itt vannak a lépések ehhez:
- Kijelölheti a teljes adathalmazt.
- Kattintson a Kezdőlap fülre.
- A Stílusok csoportban kattintson a ‘Feltételes formázás’ lehetőségre.
- Vissza a kurzort a Cellaszabályok kiemelése lehetőségre.
- Kattintson az Értékek duplikálása lehetőségre.
- A duplikált értékek párbeszédpanelen ellenőrizze, hogy a ‘Duplikálás’ legyen kiválasztva.
- Adja meg a formázást.
- Kattintson az OK gombra.
A fenti lépések az alábbiakban látható eredményt adnák.
Figyelem: A feltételes formázás duplikációs szabálya nem érzékeny a nagy- és kisbetűkre. Tehát az ‘alma’ és az ‘alma’ azonosnak minősül, és duplikátumként kerülne kiemelésre.
Példa: Két oszlop összehasonlítása és az össze nem illő adatok kiemelése
Ha az egyik listában szereplő neveket szeretné kiemelni, a másikban viszont nem, akkor erre is használhatja a feltételes formázást.
- Kijelölheti a teljes adathalmazt.
- Kattintson a Kezdőlap fülre.
- A Stílusok csoportban kattintson a ‘Feltételes formázás’ lehetőségre.
- Vissza a kurzort a Cellaszabályok kiemelése opcióra.
- Kattintson a Duplicate Values (Duplikált értékek) lehetőségre.
- A Duplicate Values (Duplikált értékek) párbeszédpanelen győződjön meg róla, hogy az ‘Unique’ (Egyedi) van kiválasztva.
- Adja meg a formázást.
- Kattintson az OK gombra.
Az alábbiakban látható eredményt kapja. Kiemeli az összes olyan cellát, amelynek neve nem szerepel a másik listában.
Két oszlop összehasonlítása és hiányzó adatpontok keresése
Ha azt szeretné azonosítani, hogy az egyik listában szereplő adatpont szerepel-e a másik listában, akkor a keresési képleteket kell használnia.
Tegyük fel, hogy az alábbiakban bemutatott adatkészlettel rendelkezünk, és szeretnénk azonosítani azokat a vállalatokat, amelyek az A oszlopban szerepelnek, de a B oszlopban nem,
Ezhez a következő VLOOKUP képletet használhatom.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Ez a képlet a VLOOKUP függvényt használja annak ellenőrzésére, hogy az A oszlopban szereplő vállalatnév szerepel-e a B oszlopban vagy sem. Ha jelen van, akkor visszaadja ezt a nevet a B oszlopból, ellenkező esetben #N/A hibát ad vissza.
A #N/A hibát adó nevek azok, amelyek hiányoznak a B oszlopból.
Az ISERROR függvény TRUE-t adna vissza, ha a VLOOKUP eredménye hibás, és FALSE-t, ha nem hibás.
Ha az összes olyan név listáját szeretné megkapni, ahol nincs egyezés, akkor szűrheti az eredmény oszlopot, hogy az összes TRUE értékű cellát megkapja.
A MATCH függvényt is használhatja ugyanerre;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Megjegyzés: Én személy szerint a VLOOKUP helyett inkább a Match függvényt (vagy az INDEX/MATCH kombinációt) használom. Rugalmasabbnak és erősebbnek találom. A Vlookup és az Index/Match közötti különbségről itt olvashat.
Két oszlop összehasonlítása és az egyező adatok kinyerése
Ha két adatkészlettel rendelkezik, és az egyik lista elemeit szeretné összehasonlítani a másikkal, és ki szeretné hozni az egyező adatpontot, akkor a lookup képleteket kell használnia.
Példa:
Az alábbi listában például a 2. oszlop piaci értékelési értékét szeretném lekérni. Ehhez meg kell keresnem ezt az értéket az 1. oszlopban, majd le kell hívnom a megfelelő piaci értékelési értéket.
Az alábbi képlet ezt teszi:
=VLOOKUP(D2,$A:$B,2,0)
vagy
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Példa:
Abban az esetben, ha olyan adathalmazt kap, ahol a két oszlopban lévő nevek között kisebb különbség van, a fent bemutatott keresési formulák használata nem fog működni.
Ezeknek a keresési formuláknak pontos egyezésre van szükségük ahhoz, hogy a megfelelő eredményt adják. A VLOOKUP vagy a MATCH függvényben van egy közelítő egyezés lehetőség, de ez itt nem használható.
Tegyük fel, hogy az alábbiakban bemutatott adatkészlettel rendelkezünk. Vegye figyelembe, hogy a 2. oszlopban vannak olyan nevek, amelyek nem teljesek (például JPMorgan helyett JPMorgan Chase és Exxon helyett ExxonMobil).
Egy ilyen esetben használhat részleges keresést a helyettesítő karakterek használatával.
A következő képlet ebben az esetben a megfelelő eredményt adja:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
vagy
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
A fenti példában a csillag (*) egy joker karakter, amely tetszőleges számú karaktert jelölhet. Ha a keresési érték mindkét oldalán ez szerepel, akkor az 1. oszlopban lévő bármely érték, amely tartalmazza a 2. oszlopban lévő keresési értéket, egyezésnek minősül.
Az *Exxon* például az ExxonMobilra lenne egyezés (mivel a * tetszőleges számú karaktert jelölhet).
Az alábbi Excel tippek & oktatóanyagok is tetszhetnek:
- Hogyan hasonlítson össze két Excel táblázatot (a különbségekért)
- Hogyan emelje ki az üres cellákat az Excelben.
- Kiemeljen minden más sort az Excelben.
- Excel Advanced Filter: Teljes útmutató példákkal.
- Highlight Rows Based on a Cell Value in Excel.