Videó megtekintése - Hasonlítsa össze az Excel két oszlopát a találatok és a különbségek tekintetében
Az egyik lekérdezés, amit sokat kapok: „hogyan lehet összehasonlítani két oszlopot az Excelben?”.
Ezt sokféleképpen lehet elvégezni, és a használni kívánt módszer az adatstruktúrától és attól függ, hogy mit akar a felhasználó.
Például érdemes összehasonlítani két oszlopot, és megkeresni vagy kiemelni az összes egyező adatpontot (amelyek mindkét oszlopban vannak), vagy csak a különbségeket (ahol egy adatpont az egyik oszlopban van, a másikban nem) stb.
Mivel ennyit kérdeznek erről, úgy döntöttem, hogy megírom ezt a hatalmas oktatóanyagot azzal a szándékkal, hogy lefedjem a legtöbb (ha nem az összes) lehetséges forgatókönyvet.
Ha ezt hasznosnak találja, adja tovább más Excel -felhasználóknak.
Ne feledje, hogy az oktatóanyagban bemutatott oszlopok összehasonlítási technikái nem az egyetlenek.
Az adatkészlete 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 valami hozzáadható ehhez az oktatóanyaghoz, tudassa velem a megjegyzések részben
Hasonlítson össze két oszlopot a pontos sormegyezéshez
Ez az összehasonlítás legegyszerűbb formája. Ebben az esetben soronként kell összehasonlítania, és meg kell határoznia, hogy mely sorok azonos adatokkal rendelkeznek, és melyek nem.
Példa: Hasonlítsa össze az azonos sor celláit
Az alábbiakban egy adathalmaz található, ahol ellenőriznem kell, hogy az A oszlopban szereplő név megegyezik -e a B oszlopban.
Ha van egyezés, akkor az eredményre „IGAZ” kell, és ha nem egyezik, akkor az eredményre „HAMIS” -ra van szükségem.
Az alábbi képlet ezt tenné:
= A2 = B2
Példa: Azonos sor celláinak összehasonlítása (IF képlet használatával)
Ha leíróbb eredményt szeretne elérni, akkor egy egyszerű IF képlet segítségével adja vissza a „Match” -t, ha a nevek azonosak, és a „Mismatch”, ha a nevek eltérőek.
= HA (A2 = B2, "Egyezés", "Nem egyezik")
Megjegyzés: Ha meg szeretné különböztetni a kis- és nagybetűket, használja a következő IF képletet:
= HA (PONTOS (A2, B2), "Match", "Mismatch")
A fenti képlet szerint az „IBM” és az „ibm” két különböző névnek minősül, a fenti képlet pedig „Mismatch” -t ad vissza.
Példa: Sorok kiemelése egyező adatokkal
Ha ki szeretné emelni azokat a sorokat, amelyek megfelelő adatokat tartalmaznak (ahelyett, hogy az eredményt külön oszlopba kapná), akkor ezt a Feltételes formázás használatával teheti meg.
Ehhez tegye a következő lépéseket:
- Válassza ki a teljes adatkészletet.
- Kattintson a „Kezdőlap” fülre.
- A Stílusok csoportban kattintson a „Feltételes formázás” lehetőségre.
- A legördülő menüben kattintson az „Új szabály” lehetőségre.
- Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
- A képlet mezőbe írja be a következő képletet: = $ A1 = $ B1
- Kattintson a Formátum gombra, és adja meg a megfelelő cellákra alkalmazni kívánt formátumot.
- Kattintson az OK gombra.
Ez kiemeli az összes cellát, ahol minden sorban azonos a név.
Hasonlítsa össze a két oszlopot és a kiemelési egyezéseket
Ha két oszlopot szeretne összehasonlítani, és kiemeli az egyező adatokat, akkor a feltételes formázásban használhatja az ismétlődő funkciót.
Vegye figyelembe, hogy ez eltér attól, amit az egyes sorok összehasonlításakor láttunk. Ebben az esetben nem soronként végezünk összehasonlítást.
Példa: Hasonlítsa össze két oszlopot és jelölje ki az egyező adatokat
Gyakran előfordul, hogy olyan adatkészleteket kap, ahol vannak egyezések, de előfordulhat, hogy ezek nem ugyanabban a sorban vannak.
Valami az alábbiak szerint:
Ne feledje, hogy az A oszlopban szereplő lista nagyobb, mint a B.
Ha minden egyező cégnevet kiemelni szeretne, akkor ezt feltételes formázással teheti meg.
Ehhez tegye a következő lépéseket:
- Válassza ki a teljes adatkészletet.
- Kattintson a Kezdőlap fülre.
- A Stílusok csoportban kattintson a „Feltételes formázás” lehetőségre.
- Vigye a kurzort a Cellaszabályok kiemelése lehetőségre.
- Kattintson az Ismétlődő értékek lehetőségre.
- Az Ismétlődő értékek párbeszédpanelen győződjön meg arról, hogy az „Ismétlődés” van kiválasztva.
- Adja meg a formázást.
- Kattintson az OK gombra.
A fenti lépések az alább látható eredményt adják.
Megjegyzés: A feltételes formázás ismétlődő szabálya nem különbözteti meg a kis- és nagybetűket. Tehát az „alma” és az „alma” azonosnak tekintendő, és duplikátumként kerül kiemelésre.
Példa: Hasonlítson össze két oszlopot, és jelölje ki a nem egyező adatokat
Abban az esetben, ha az egyik listában szereplő neveket szeretné kiemelni, a másikban nem, akkor ehhez használhatja a feltételes formázást is.
- Válassza ki a teljes adatkészletet.
- Kattintson a Kezdőlap fülre.
- A Stílusok csoportban kattintson a „Feltételes formázás” lehetőségre.
- Vigye a kurzort a Cellaszabályok kiemelése lehetőségre.
- Kattintson az Ismétlődő értékek lehetőségre.
- Az Ismétlődő értékek párbeszédpanelen ellenőrizze, hogy az „Egyedi” van -e kiválasztva.
- Adja meg a formázást.
- Kattintson az OK gombra.
Ez megadja az alább látható eredményt. Kiemeli az összes cellát, amelynek neve nem szerepel a másik listában.
Hasonlítson össze két oszlopot, és keresse meg a hiányzó adatpontokat
Ha szeretné azonosítani, hogy az egyik listából származó adatpont jelen van -e a másik listában, akkor a keresési képleteket kell használnia.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és azonosítani szeretné azokat a vállalatokat, amelyek jelen vannak az A oszlopban, de nem a B oszlopban,
Ehhez a következő VLOOKUP képletet használhatom.
= ISERROR (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Ez a képlet a VLOOKUP függvényt használja annak ellenőrzésére, hogy az A -ban szereplő cégnév szerepel -e a B oszlopban. Ha jelen van, akkor visszaadja ezt a nevet a B oszlopból, különben #N/A hibát ad vissza.
Ezek a nevek, amelyek a #N/A hibát adják vissza, hiányoznak a B oszlopból.
Az ISERROR függvény IGAZ értéket ad vissza, ha a VLOOKUP eredménye hiba, és hamis, ha nem hiba.
Ha listát szeretne kapni az összes névről, ahol nincs egyezés, akkor szűrheti az eredmény oszlopot, hogy az összes IGAZ cellát megkapja.
Ugyanezhez a MATCH funkciót is használhatja;
= NEM (SZÁM (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Megjegyzés: Személy szerint én inkább a Match funkciót (vagy az INDEX/MATCH kombinációját) használom a VLOOKUP helyett. Rugalmasabbnak és erőteljesebbnek tartom. Itt elolvashatja a különbséget a Vlookup és az Index/Match között.
Hasonlítsa össze két oszlopot, és húzza ki az egyező adatokat
Ha két adatkészlete van, és összehasonlítani szeretné az egyik lista elemeit a másikkal, és le szeretné kérni a megfelelő adatpontot, akkor a keresési képleteket kell használnia.
Példa: Hozza le az egyező adatokat (pontos)
Például az alábbi listában szeretném lekérni a 2. oszlop piaci értékét. Ehhez meg kell keresnem ezt az értéket az 1. oszlopban, majd le kell kérnem a megfelelő piaci értéket.
Az alábbiakban ezt a képletet találja meg:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
vagy
= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Példa: Hozza le az egyező adatokat (részleges)
Abban az esetben, ha olyan adatkészletet kap, ahol a két oszlopban lévő névben kisebb eltérések vannak, akkor a fent bemutatott keresési képletek használata nem fog működni.
Ezeknek a keresési képleteknek pontos egyezésre van szükségük a megfelelő eredmény eléréséhez. A VLOOKUP vagy a MATCH funkcióban van egy hozzávetőleges egyezési lehetőség, de ez itt nem használható.
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel. Vegye figyelembe, hogy vannak olyan nevek, amelyek nem teljesek a 2. oszlopban (például JPMorgan a JPMorgan Chase helyett és Exxon az ExxonMobil helyett).
Ebben az esetben részleges keresést használhat helyettesítő karakterek használatával.
A következő képlet adja meg a helyes eredményt ebben az esetben:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
vagy
= INDEX ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
A fenti példában a csillag (*) helyettesítő karakter, amely tetszőleges számú karaktert képviselhet. Ha a keresési érték mindkét oldalán vele van ellátva, akkor az 1. oszlop bármely értéke, amely tartalmazza a 2. oszlopban található keresési értéket, egyezésnek minősül.
Például az * Exxon * megfelelne az ExxonMobilnak (mivel a * tetszőleges számú karaktert jelenthet).
A következő Excel tippek és oktatóanyagok is tetszhetnek:
- Hogyan lehet összehasonlítani két Excel -lapot (a különbségekért)
- Az üres cellák kiemelése az Excelben.
- Az Excel minden további sorát kiemelje.
- Excel speciális szűrő: Teljes útmutató példákkal.
- Sorok kiemelése az Excel cellaértéke alapján.
