Hogyan hasonlítsunk össze két oszlopot az Excelben, és távolítsuk el az ismétlődéseket (kiemelés, színezés, áthelyezés)

Ennek a cikknek az elolvasása körülbelül 10 percet vesz igénybe. A következő 5 percben könnyedén összehasonlíthat két oszlopot az Excelben, és megtudhatja, hogy vannak-e ismétlődések, törölheti vagy színesen kiemelheti őket. Szóval, eljött az idő!

Az Excel egy nagyon hatékony és nagyszerű alkalmazás nagy mennyiségű adat létrehozására és feldolgozására. Ha több adatot tartalmazó munkafüzete van (vagy csak egy hatalmas táblázat), akkor valószínűleg 2 oszlopot szeretne összehasonlítani, ismétlődő értékeket keresni, majd valamit tenni velük, például törölni, kiemelni vagy törölni a tartalmat. Az oszlopok lehetnek ugyanabban a táblázatban, lehetnek szomszédosak vagy nem szomszédosak, elhelyezkedhetnek 2 különböző lapon vagy akár különböző könyvekben is.

Képzeld el, hogy 2 oszlopunk van emberek nevével – oszloponként 5 név A és 3 név egy oszlopban B. Össze kell hasonlítania a két oszlopban található neveket, és meg kell találnia az ismétlődéseket. Mint érti, ezek fiktív adatok, kizárólag például. Valódi táblázatokban több ezer vagy akár több tízezer rekorddal van dolgunk.

A lehetőség: mindkét oszlop ugyanazon a lapon van. Például egy oszlop A és oszlop B.

B lehetőség: Az oszlopok különböző lapokon vannak. Például egy oszlop A a lapon Sheet2 és oszlop A a lapon Sheet3.

Az Excel 2013, 2010 és 2007 beépített eszközzel rendelkezik Távolítsa el a másolatokat (Remove Duplicates), de ebben a helyzetben tehetetlen, mivel nem tudja összehasonlítani az adatokat 2 oszlopban. Sőt, csak a duplikációkat tudja eltávolítani. Nincs más lehetőség, mint például a kiemelés vagy a színek megváltoztatása. És pont!

Ezután bemutatom, hogyan lehet összehasonlítani két oszlopot az Excelben, ami lehetővé teszi az ismétlődő rekordok megkeresését és eltávolítását.

• Hasonlítsa össze a 2 oszlopot, és keressen ismétlődéseket képletekkel
  
  • A lehetőség: mindkét oszlop ugyanazon a lapon található
  • B lehetőség: Az oszlopok különböző lapokon vagy különböző munkafüzetekben találhatók
• A talált ismétlődések feldolgozása
  
  • Csak ismétlődő sorok jelenjenek meg az A oszlopban
  • Módosítsa a színt, vagy jelölje ki a talált ismétlődéseket
  • Távolítsa el az ismétlődéseket az első oszlopból

Hasonlítsa össze az Excel két oszlopát, és keresse meg az ismétlődő bejegyzéseket képletek segítségével

A lehetőség: mindkét oszlop ugyanazon a lapon található

1. Az első üres cellába (példánkban ez a C1 cella) a következő képletet írjuk:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Képletünkben A1 ez az első cella az első oszlopban, amelyet összehasonlítunk. 1 USD B$ и 10000 USD B$ ezek a második oszlop első és utolsó cellájának címei, amelyekkel végezzük el az összehasonlítást. Vegye figyelembe az abszolút hivatkozásokat – az oszlopbetűket és a sorszámokat egy dollárjel ($) előzi meg. Abszolút hivatkozásokat használok, hogy a cellacímek változatlanok maradjanak a képletek másolásakor.

   Ha ismétlődéseket szeretne találni egy oszlopban B, módosítsa a hivatkozásokat, hogy a képlet így nézzen ki:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Ehelyett "Csak"És"Duplicate» Írhat saját címkéket, például «Nem található"És"Talál"vagy hagyd csak"Duplicate', és a második érték helyett adjon meg egy szóközt. Az utóbbi esetben azok a cellák, amelyekhez nem található duplikátum, üresek maradnak, és úgy gondolom, hogy az adatoknak ez a megjelenítése a legkényelmesebb a további elemzéshez.

2. Most másoljuk a képletünket az oszlop összes cellájába C, egészen az alsó sorig, amely az oszlop adatait tartalmazza A. Ehhez vigye az egérmutatót a cella jobb alsó sarkába C1, a mutató fekete szálkereszt formájában jelenik meg, amint az az alábbi képen látható:Kattintson és tartsa lenyomva a bal egérgombot, és húzza le a keret szegélyét, kiemelve az összes cellát, ahová a képletet be kívánja szúrni. Ha az összes szükséges cellát kijelölte, engedje fel az egérgombot:

   

Tipp: Nagy táblázatokban a képlet másolása gyorsabb lesz, ha billentyűparancsokat használ. Jelöljön ki egy cellát C1 és nyomja meg az Ctrl + C (a képlet vágólapra másolásához), majd kattintson Ctrl + Shift + End (az összes nem üres cella kijelöléséhez a C oszlopban), és végül nyomja meg a gombot Ctrl + V (a képlet beszúrásához az összes kijelölt cellába).

3. Remek, most minden ismétlődő érték ""Duplicate„:

B lehetőség: két oszlop különböző lapokon van (különböző munkafüzetekben)

1. A munkalap első üres oszlopának első cellájában Sheet2 (esetünkben ez a B oszlop) írja be a következő képletet:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Itt Sheet3 annak a lapnak a neve, amelyen a 2. oszlop található, és $ A $ 1: $ A $ 10000 cellacímek ebben a 1. oszlopban az 2-től az utolsóig.

2. Másolja a képletet egy oszlop összes cellájába B (ugyanaz, mint az A lehetőség).
3. Ezt az eredményt kapjuk:

A talált ismétlődések feldolgozása

Remek, az első oszlopban olyan bejegyzéseket találtunk, amelyek a második oszlopban is jelen vannak. Most tennünk kell velük valamit. A tábla összes ismétlődő rekordjának manuális végigjátszása meglehetősen nem hatékony, és túl sok időt vesz igénybe. Vannak jobb módszerek is.

Csak ismétlődő sorok jelenjenek meg az A oszlopban

Ha az oszlopoknak nincs fejléce, akkor fel kell vennie őket. Ehhez helyezze a kurzort az első sort jelölő számra, és az fekete nyíllá változik, ahogy az alábbi ábrán látható:

Kattintson a jobb gombbal, és válassza ki a helyi menüből beszúrás (Beszúrás):

Adjon nevet az oszlopoknak, például: "Név"És"Másolat?» Ezután nyissa meg a lapot dátum (Adat) és nyomja meg Szűrő (Szűrő):

Ezután kattintson a kis szürke nyílra a „Másolat?« a szűrő menü megnyitásához; törölje az összes elem jelölését a listában, kivéve Duplicate, és nyomja meg a gombot OK.

Ez minden, most csak az oszlop azon elemei láthatók А, amelyek ismétlődnek az oszlopban В. Csak két ilyen cella van az edzési táblázatunkban, de amint érti, a gyakorlatban sokkal több lesz belőlük.

Egy oszlop összes sorának ismételt megjelenítéséhez А, kattintson a szűrő szimbólumra az oszlopban В, amely most úgy néz ki, mint egy kis nyíllal ellátott tölcsér, és válassza ki Jelölje ki az összes (Mindet kiválaszt). Vagy megteheti ugyanezt a szalagon keresztül is, ha rákattint dátum (Adatok) > Kijelölés és szűrés (Rendezés és szűrés) > Szűrő kikapcsolása (Törlés) az alábbi képernyőképen látható módon:

Módosítsa a színt, vagy jelölje ki a talált ismétlődéseket

Ha a jegyzetek „Duplicate"" nem elég az Ön céljaihoz, és más betűszínnel, kitöltési színnel vagy más módszerrel szeretné megjelölni az ismétlődő cellákat…

Ebben az esetben szűrje ki a duplikátumokat a fentiek szerint, jelölje ki az összes szűrt cellát, és kattintson a gombra Ctrl + 1a párbeszédpanel megnyitásához Cellák formázása (cellaformátum). Példaként változtassuk meg az ismétlődő sorok celláinak kitöltési színét élénksárgára. Természetesen az eszközzel megváltoztathatja a kitöltési színt Tölt (Kitöltés színe) fülre Kezdőlap (Otthon), de a párbeszédpanel előnye Cellák formázása (Cellaformátum), amelyben az összes formázási beállítást egyszerre konfigurálhatja.

Most biztosan nem fog hiányozni egyetlen másodlagos cella sem:

Az ismétlődő értékek eltávolítása az első oszlopból

Szűrje le a táblázatot úgy, hogy csak az ismétlődő értékkel rendelkező cellák jelenjenek meg, és jelölje ki azokat.

Ha a két összehasonlított oszlop különböző lapokon van, vagyis a különböző táblázatokban kattintson a jobb gombbal a kiválasztott tartományra, és válassza ki Sor törlése (Sor eltávolítása):

nyomja meg OKamikor az Excel arra kéri, hogy erősítse meg, hogy valóban törölni szeretné a teljes lapsort, majd törölje a szűrőt. Mint látható, csak egyedi értékkel rendelkező sorok maradnak meg:

Ha 2 oszlop van ugyanazon a lapon, közel egymáshoz (szomszédos) vagy nem közel egymáshoz (nem szomszédos), akkor a duplikátumok eltávolításának folyamata kissé bonyolultabb lesz. Az ismétlődő értékeket tartalmazó teljes sort nem tudjuk eltávolítani, mivel ezzel a második oszlop celláit is eltávolítjuk. Tehát csak egyedi bejegyzések maradjanak egy oszlopban А, csináld ezt:

1. Szűrje le a táblázatot, hogy csak az ismétlődő értékek jelenjenek meg, és jelölje ki azokat a cellákat. Kattintson rájuk a jobb gombbal, és válassza ki a helyi menüből Tiszta tartalom (átlátható tartalom).
2. Tisztítsa meg a szűrőt.
3. Jelölje ki az összes cellát egy oszlopban А, a cellából kiindulva A1 egészen az adatokat tartalmazó aljáig.
4. Kattintson az dátum (Adat) és nyomja meg Rendezés A-tól Z-ig (Rendezés A-tól Z-ig). A megnyíló párbeszédpanelen válassza ki a lehetőséget Folytassa az aktuális választással (Rendezés a megadott kijelölésen belül), majd kattintson a gombra Fekete (Válogató):
5. Törölje az oszlopot a képlettel, többé nem lesz rá szüksége, mostantól csak egyedi értékei vannak.
6. Ennyi, most az oszlop А csak egyedi adatokat tartalmaz, amelyek nem szerepelnek az oszlopban В:

Amint láthatja, a duplikátumok eltávolítása két oszlopból az Excelben képletekkel nem olyan nehéz.