Hogyan hasonlítsunk össze két listát Excelben

Az Excel egy hatékony adatfeldolgozó program. Az információelemzés egyik módszere pedig két lista összehasonlítása. Ha helyesen hasonlít össze két listát az Excelben, a folyamat megszervezése nagyon egyszerű lesz. Elég, ha követünk néhány pontot, amelyekről ma beszélünk. Ennek a módszernek a gyakorlati megvalósítása teljes mértékben az adott személy vagy szervezet szükségleteitől függ. Ezért több lehetséges esetet is figyelembe kell venni.

Két lista összehasonlítása Excelben

Természetesen manuálisan is összehasonlíthat két listát. De ez sokáig fog tartani. Az Excel saját intelligens eszközkészlettel rendelkezik, amely nem csak az adatok gyors összehasonlítását teszi lehetővé, hanem olyan információk megszerzését is, amelyekhez nem olyan könnyű hozzájutni. Tegyük fel, hogy van két oszlopunk A és B koordinátákkal. Néhány érték megismétlődik bennük.

A probléma megfogalmazása

Tehát össze kell hasonlítanunk ezeket az oszlopokat. A két dokumentum összehasonlításának eljárása a következő:

1. Ha ezeknek a listáknak az egyedi cellái megegyeznek, és az egyedi cellák teljes száma megegyezik, és a cellák azonosak, akkor ezek a listák azonosnak tekinthetők. A listában szereplő értékek halmozási sorrendje nem számít annyira.
2. A listák részleges egybeeséséről akkor beszélhetünk, ha maguk az egyedi értékek azonosak, de az ismétlések száma eltérő. Ezért az ilyen listák eltérő számú elemet tartalmazhatnak.
3. Azt, hogy a két lista nem egyezik, az egyedi értékek eltérő halmaza jelzi.

Ez a három feltétel egyszerre jelenti a problémánk feltételét.

A probléma megoldása

A listák összehasonlításának megkönnyítése érdekében hozzunk létre két dinamikus tartományt. Mindegyik megfelel az egyes listáknak.

Két lista összehasonlításához tegye a következőket:

1. Egy külön oszlopban létrehozunk egy listát az egyedi értékekről, amelyek mindkét listára jellemzőek. Ehhez a következő képletet használjuk: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Magát a képletet tömbképletként kell felírni.
2. Határozzuk meg, hogy az egyes egyedi értékek hányszor fordulnak elő az adattömbben. Íme a képletek ehhez: =COUNTIF(Lista1,D5) és =COUNTI(Lista2,D5).
3. Ha mind az ismétlések száma, mind az egyedi értékek száma megegyezik az összes listában, amely ezekben a tartományokban szerepel, akkor a függvény 0 értéket ad vissza. Ez azt jelzi, hogy az egyezés XNUMX%. Ebben az esetben a listák fejlécei zöld hátteret kapnak.
4. Ha minden egyedi tartalom mindkét listában szerepel, akkor képletek visszaadják =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) az érték nulla lesz. Ha E1 nem tartalmaz nullát, de ilyen értéket az E2 és F2 cellák tartalmaznak, akkor ebben az esetben a tartományokat a rendszer egyezőként ismeri fel, de csak részben. Ebben az esetben a megfelelő listák fejlécei narancssárgára váltanak.
5. Ha pedig a fent leírt képletek egyike nullától eltérő értéket ad vissza, akkor a listák teljesen nem egyeznek.

Ez a válasz arra a kérdésre, hogy hogyan lehet az egyezések oszlopait képletekkel elemezni. Mint látható, a függvények használatával szinte minden olyan feladatot megvalósíthat, amely első pillantásra nem kapcsolódik a matematikához.

Példa tesztelés

A mi táblázatunkban minden fent leírt típusból háromféle lista található. Részben és teljesen egyező, valamint nem egyező.

Az adatok összehasonlításához az A5:B19 tartományt használjuk, amelybe felváltva szúrjuk be ezeket a listapárokat. Hogy mi lesz az összehasonlítás eredménye, azt az eredeti listák színéből fogjuk megérteni. Ha teljesen mások, akkor piros háttér lesz. Ha az adatok egy része megegyezik, akkor sárga. Teljes azonosság esetén a megfelelő címsorok zöldek lesznek. Hogyan készítsünk színt az eredménytől függően? Ehhez feltételes formázásra van szükség.

Különbségek keresése két listában kétféleképpen

Ismertessünk még két módszert a különbségek megállapítására, attól függően, hogy a listák szinkronok-e vagy sem.

1. lehetőség: Szinkron listák

Ez egy egyszerű lehetőség. Tegyük fel, hogy vannak ilyen listáink.

Annak meghatározásához, hogy az értékek hányszor nem konvergáltak, használhatja a következő képletet: =ÖSSZEG(—(A2:A20<>B2:B20)). Ha 0-t kaptunk, az azt jelenti, hogy a két lista megegyezik.

2. lehetőség: Kevert listák

Ha a listák nem azonosak a bennük lévő objektumok sorrendjében, akkor olyan szolgáltatást kell alkalmaznia, mint például a feltételes formázás, és ki kell színezni az ismétlődő értékeket. Vagy használja a funkciót COUNTIF, melynek segítségével meghatározzuk, hogy az egyik lista eleme hányszor fordul elő a másodikban.

2 oszlop összehasonlítása soronként

Amikor két oszlopot hasonlítunk össze, gyakran össze kell hasonlítanunk a különböző sorokban található információkat. Ehhez az üzemeltető segít nekünk HA. Nézzük meg, hogyan működik ez a gyakorlatban. Ehhez több szemléltető helyzetet mutatunk be.

Példa. Hogyan hasonlítsunk össze 2 oszlopot az egyezések és különbségek tekintetében egy sorban

Annak elemzésére, hogy az ugyanabban a sorban, de különböző oszlopokban lévő értékek megegyeznek-e, írjuk fel a függvényt IF. A képlet minden olyan sorba bekerül a segédoszlopba, ahol az adatfeldolgozás eredményei megjelennek. De egyáltalán nem szükséges minden sorban előírni, csak másolja be az oszlop többi cellájába, vagy használja az automatikus kiegészítés jelölőjét.

Fel kell írnunk egy ilyen képletet, hogy megértsük, hogy mindkét oszlop értéke megegyezik-e vagy sem: =HA(A2=B2; „Egyezés”, „”). Ennek a funkciónak a logikája nagyon egyszerű: összehasonlítja az A2 és B2 cellák értékeit, és ha megegyeznek, akkor az „Egybeesik” értéket jeleníti meg. Ha az adatok különböznek, akkor nem ad vissza értéket. Azt is ellenőrizheti a cellák között, hogy van-e egyezés közöttük. Ebben az esetben a következő képletet használjuk: =IF(A2<>B2, “Nem egyezik”, “”). Az elv ugyanaz, először az ellenőrzést kell elvégezni. Ha kiderül, hogy a cellák megfelelnek a feltételnek, akkor a „Nem egyezik” érték jelenik meg.

A képletmezőben a következő képletet is használhatja az „Egyezés” megjelenítésére, ha az értékek megegyeznek, és a „Nem egyezik”, ha különböznek: =IF(A2=B2; „Egyező”, „Nem egyezik”). Használhatja az egyenlőtlenség operátort is az egyenlőség operátor helyett. Csak az ebben az esetben megjelenő értékek sorrendje lesz némileg eltérő: =IF(A2<>B2, “Nem egyezik”, “Egybeesik”). A képlet első verziójának használata után az eredmény a következő lesz.

A képlet ezen változata nem tesz különbséget a kis- és nagybetűk között. Ezért, ha az egyik oszlopban lévő értékek csak abban különböznek a többitől, hogy nagybetűkkel vannak írva, akkor a program nem veszi észre ezt a különbséget. Az összehasonlítás kis- és nagybetűk megkülönböztetéséhez használja a függvényt a feltételekben PONTOS. A többi érv változatlan marad: =HA(PONTOS(A2,B2), „Egyező”, „Egyedi”).

Több oszlop összehasonlítása az egy sorban lévő egyezésekhez

Lehetőség van a listákban szereplő értékek elemzésére számos kritérium szerint:

1. Keresse meg azokat a sorokat, amelyek mindenhol azonos értékekkel rendelkeznek.
2. Keresse meg azokat a sorokat, ahol csak két listában vannak egyezések.

Nézzünk néhány példát arra, hogyan kell eljárni ezekben az esetekben.

Példa. Hogyan találhatunk egyezéseket egy sorban egy táblázat több oszlopában

Tegyük fel, hogy van egy sor oszlopunk, amely tartalmazza a szükséges információkat. Azzal a feladattal állunk szemben, hogy meghatározzuk azokat a sorokat, amelyekben az értékek megegyeznek. Ehhez a következő képletet kell használnia: =HA(ÉS(A2=B2,A2=C2), "egyezik", " ").

Ha túl sok oszlop van a táblázatban, akkor csak a függvénnyel együtt kell használni IF operátor COUNTIF: =HA(COUNTIF($A2:$C2,$A2)=3;”egyezik”;” “). A képletben használt szám az ellenőrizendő oszlopok számát jelzi. Ha eltér, akkor annyit kell írnia, amennyi igaz az Ön helyzetére.

Példa. Hogyan találhatunk egyezéseket egy sorban a táblázat bármely 2 oszlopában

Tegyük fel, hogy ellenőriznünk kell, hogy egy sorban lévő értékek egyeznek-e két oszlopban a táblázatban szereplőkkel. Ehhez a függvényt feltételként kell használni OR, ahol felváltva írja be az egyes oszlopok egyenlőségét a másikkal. Íme egy példa.

Ezt a képletet használjuk: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Előfordulhat olyan helyzet, amikor sok oszlop van a táblázatban. Ebben az esetben a képlet hatalmas lesz, és sok időbe telhet az összes szükséges kombináció kiválasztása. A probléma megoldásához a funkciót kell használnia COUNTIF: =HA(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Egyedi karakterlánc”; „Nem egyedi karakterlánc”)

Látjuk, hogy összesen két funkciónk van COUNTIF. Az elsőnél felváltva meghatározzuk, hogy hány oszlopban van hasonlóság az A2-vel, a másodikkal pedig a B2 értékkel ellenőrizzük a hasonlóságok számát. Ha a képlettel történő számítás eredményeként nulla értéket kapunk, ez azt jelzi, hogy ebben az oszlopban minden sor egyedi, ha több, akkor hasonlóságok vannak. Ezért ha két képlettel számolva és a végeredményt összeadva nulla értéket kapunk, akkor az „Egyedi karakterlánc” szöveges értéket kapjuk vissza, ha ez a szám nagyobb, akkor azt írják, hogy ez a karakterlánc nem egyedi.

Hogyan hasonlítsunk össze két oszlopot az Excelben az egyezésekhez

Most vegyünk egy példát. Tegyük fel, hogy van egy két oszlopos táblázatunk. Meg kell nézni, hogy egyeznek-e. Ehhez a képletet kell alkalmazni, ahol a függvényt is használni kell IF, és az üzemeltető COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Nincs egyezés a B oszlopban", "Vannak egyezések a B oszlopban"

Nincs szükség további teendőkre. Miután ezzel a képlettel kiszámítottuk az eredményt, megkapjuk, ha a függvény harmadik argumentumának értékét IF mérkőzések. Ha nincsenek, akkor a második argumentum tartalma.

Hogyan hasonlítsunk össze két oszlopot az Excelben az egyezések és a színek kiemelése érdekében

Az egyező oszlopok vizuális azonosításának megkönnyítése érdekében színnel kiemelheti őket. Ehhez a „Feltételes formázás” funkciót kell használni. Lássuk a gyakorlatban.

Egyezések keresése és kiemelése szín szerint több oszlopban

Az egyezések meghatározásához és kiemeléséhez először ki kell választania azt az adattartományt, amelyben az ellenőrzést végrehajtani, majd a „Főoldal” fülön nyissa meg a „Feltételes formázás” elemet. Ott válassza ki a „Duplikált értékeket” cellakiválasztási szabályként.

Ezt követően megjelenik egy új párbeszédablak, amelyben a bal oldali felugró listában megtaláljuk az „Ismétlés” opciót, a jobb oldali listában pedig kiválasztjuk a kiválasztáshoz használandó színt. Miután rákattintunk az „OK” gombra, az összes hasonlósággal rendelkező cella háttere ki lesz jelölve. Ezután hasonlítsa össze szemenként az oszlopokat.

Egyező vonalak keresése és kiemelése

A húrok egyezésének ellenőrzésének technikája kissé eltér. Először is létre kell hoznunk egy további oszlopot, és ott a kombinált értékeket fogjuk használni a & operátor használatával. Ehhez meg kell írnia a következő képletet: =A2&B2&C2&D2.

Kiválasztjuk a létrehozott oszlopot, amely az egyesített értékeket tartalmazza. Ezután ugyanazt a műveletsort hajtjuk végre, amelyet fentebb az oszlopoknál leírtunk. Az ismétlődő vonalak az Ön által megadott színnel lesznek kiemelve.

Látjuk, hogy nincs semmi nehéz az ismétlések keresésében. Az Excel minden szükséges eszközt tartalmaz ehhez. Fontos, hogy csak gyakoroljon, mielőtt mindezt a tudást a gyakorlatba ülteti.