Hogyan lehet kiemelni a duplikált cellákat és sorokat az Excelben

Ebben a bemutatóban megtanulhatja, hogyan lehet megjeleníteni a duplikátumokat az Excelben. Alaposan megnézzük a különböző módszereket a duplikált cellák, teljes sorok vagy egymást követő duplikációk árnyékolására a feltételes formázás segítségével. Azt is látni fogja, hogyan lehet a duplikátumokat különböző színekkel kiemelni egy speciális eszköz segítségével.

A múlt héten a duplikátumok Excelben történő azonosításának különböző módjait vizsgáltuk meg. Kétségtelen, hogy a duplikátumképletek nagyon hasznosak, de a duplikált bejegyzések meghatározott színnel történő kiemelése még egyszerűbbé teheti az adatelemzést.

A duplikátumok keresésének és kiemelésének leggyorsabb módja az Excelben a feltételes formázás használata. Ennek a módszernek a legnagyobb előnye, hogy nem csak a meglévő duplikátumokat mutatja meg, hanem az adatok bevitele, szerkesztése vagy felülírása során az új duplikátumokat is észleli és színezi.

Az útmutató további részében a duplikált bejegyzések kiemelésének számos módját találja meg az adott feladattól függően. Ezek a technikák az Excel 2016, Excel 2013, Excel 2010 és alacsonyabb verziókban is működnek.

• Duplikátumok kiemelése az Excelben 1. előfordulással (beépített-beépített szabály)
• Hogyan lehet kiemelni a duplikátumokat az Excelben az 1. előfordulások kivételével
• Hogyan lehet kiemelni a duplikátumokat egy tartományban (több oszlopban)
• Hogyan lehet kiemelni az N-edik és az azt követő duplikátumokat. rekordok
• Egy oszlopban lévő duplikált értékek alapján egész sorok árnyékolása
• Hogyan lehet a duplikált sorokat kiemelni az Excelben
• Hogyan lehet az egymást követő duplikált cellákat megtekinteni
• Duplicate Remover – Duplicate Remover – a duplikációk kiemelésének leggyorsabb módja az Excelben

Hogyan lehet kiemelni a duplikátumokat az Excelben a beépített szabály segítségével (1. előfordulással)

Kezdésnek, minden Excel-verzióban van egy előre definiált szabály a duplikált cellák kiemelésére. Ha ezt a szabályt szeretné használni a munkalapjain, végezze el a következő lépéseket:

1. Kijelölje a duplikátumokat ellenőrizni kívánt adatokat. Ez lehet egy oszlop, egy sor vagy egy cellatartomány.
2. A Kezdőlap lapon a Stílusok csoportban kattintson a Feltételes formázás > Cellák kiemelése szabályai > Duplikált értékek…
   
3. A Duplikált értékek párbeszédpanel alapértelmezés szerint a világosvörös kitöltés és a sötétvörös szöveg formátum kiválasztásával nyílik meg. Az alapértelmezett formátum alkalmazásához egyszerűen kattintson az OK gombra.

A legördülő listában a piros kitöltés és a szövegformázás mellett egy maroknyi más előre definiált formátum is elérhető. Ha más színnel szeretné kiemelni a duplikátumokat, kattintson az Egyéni formátum… (a legördülő lista utolsó eleme) gombra, és válassza ki a kívánt kitöltési és/vagy betűszínt.

A beépített szabály használatával a duplikációkat egy vagy több oszlopban is kiemelheti, ahogy az a következő képernyőképen látható:

Az Excel beépített szabályának használatakor a duplikált értékek kiemelésére a következő két dolgot tartsa szem előtt:

• Ez csak egyedi cellákra működik. A duplikált sorok kiemeléséhez saját szabályokat kell létrehoznia vagy egy adott oszlop értékei alapján, vagy több oszlop értékeinek összehasonlításával.
• A duplikált cellákat árnyalja, beleértve azok első előfordulását is. Az összes duplikátum kiemeléséhez, kivéve az első előfordulásokat, hozzon létre egy feltételes formázási szabályt ezen a képleten alapulva.

Hogyan lehet kiemelni a duplikátumokat az 1. előfordulások nélkül

A 2. és minden további duplikátum előfordulásának kiemeléséhez jelölje ki a színezni kívánt cellákat, és hozzon létre egy képleten alapuló szabályt a következő módon:

1. A Kezdőlap lapon a Stílusok csoportban kattintson a Feltételes formázás > Új szabály > Egy képlet segítségével határozza meg, hogy mely cellákat kell formázni.
2. Az Értékek formázása, ahol ez a képlet igaz mezőbe írjon be egy ehhez hasonló képletet:
   =COUNTIF($A:$A2,$A2)>1

   Hol A2 a kijelölt tartomány legfelső cellája.

3. Kattintson a Formázás… gombra, és válassza ki a kívánt kitöltési és/vagy betűszínt.
4. Végül kattintson az OK gombra a szabály mentéséhez és alkalmazásához.

Ha nincs sok tapasztalata az Excel feltételes formázás terén, a képletalapú szabály létrehozásának részletes lépéseit a következő bemutatóban találja: Excel feltételes formázás egy másik cella értéke alapján.

Az első példányok kivételével a duplikált cellák az Ön által választott színnel lesznek kiemelve:

Hogyan jelenítse meg a 3., 4. és minden további duplikált rekordot

Az N-edik előfordulással kezdődő duplikációk megjelenítéséhez hozzon létre egy feltételes formázási szabályt a képlet alapján, mint az előző példában, azzal a különbséggel, hogy a képlet végén lévő >1-et a kívánt számmal helyettesíti. Például:

A 3. és az összes további duplikátum kiemeléséhez hozzon létre egy feltételes formázási szabályt ezen a képleten alapulva:

=COUNTIF($A:$A2,$A2)>=3

A 4. és az összes további duplikátum kiemeléséhez használja ezt a képletet:

=COUNTIF($A:$A2,$A2)>=4

A csak bizonyos előfordulások kiemeléséhez használja az egyenlő az operátort (=). Ha például csak a 2. előfordulást szeretné kiemelni, akkor a következő képletet használja:

=COUNTIF($A:$A2,$A2)=2

Hogyan lehet kiemelni a duplikátumokat egy tartományban (több oszlopban)

Ha több oszlopon keresztül szeretné ellenőrizni a duplikátumokat, nem az oszlopok egymással való összehasonlításával, hanem az összes oszlopban ugyanazon elem összes előfordulását szeretné megtalálni, akkor használja a következő megoldások egyikét.

Kiemeli a duplikátumokat több oszlopban, beleértve az 1. előfordulásokat is

Ha az adathalmazban többször előforduló elem első példánya duplikátumnak minősül, a legegyszerűbb az Excel beépített duplikátum-szabályának használata.

Vagy hozzon létre egy feltételes formázási szabályt ezzel a képlettel:

Például az A2 tartományban lévő duplikációk kiemeléséhez:C8, a képlet a következőképpen néz ki:

=COUNTIF($A:$C, A2)>1

Az abszolút cellahivatkozások használata a tartományra ($A$2:$C$8) és a relatív hivatkozások használata a felső cellára (A2).

Kiemelni a duplikációkat több oszlopban, 1. előfordulások nélkül

A megoldás erre a forgatókönyvre sokkal trükkösebb, nem csoda, hogy az Excelnek nincs beépített szabálya erre 🙂

A több oszlopban lévő duplikált bejegyzések kiemeléséhez az 1. előfordulások figyelmen kívül hagyásával 2 szabályt kell létrehoznia a következő képletekkel:

1. szabály. Az első oszlopra vonatkozik

Itt pontosan ugyanazt a képletet használja, mint amit az egy oszlopban lévő duplikált bejegyzések 1. előfordulás nélküli kiemeléséhez használtunk (a részletes lépéseket itt találja).

Ebben a példában az A2:A8-ra hozunk létre egy szabályt ezzel a formulával:

=COUNTIF($A:$A2,$A2)>1

Az eredmény az lesz, hogy a tartomány bal szélső oszlopában (a mi esetünkben csak egy ilyen elem van) az 1. előfordulás nélküli duplikált elemek kerülnek kiemelésre:

2. szabály. Minden további oszlopra vonatkozik

A többi oszlopban (B2:C8) lévő duplikációk kiemeléséhez használjuk ezt a képletet:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

A fenti képletben az első COUNTIF függvény az adott elem előfordulásait számolja az első oszlopban, a második COUNTIF pedig ugyanezt teszi az összes további oszlopban. Ezután összeadja ezeket a számokat, és ellenőrzi, hogy az összeg nagyobb-e 1-nél.

Eredményként az összes duplikált elemet megtalálja és kiemeli, kivéve azok 1. előfordulását:

Kiemelés a duplikált elemek minden oszlopban egyetlen szabállyal

Egy másik lehetséges megoldás, ha az adatállomány bal oldalán egy üres oszlopot adunk hozzá, és a fenti képleteket egyetlen képletté kombináljuk, így:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Hol B2 a céltartomány 2. oszlopában lévő adatokat tartalmazó felső cella.

A képlet jobb megértése érdekében bontsuk 2 fő részre:

• Az első oszlop (B) esetében az IF feltétel soha nem teljesül, így csak a második COUNTIF függvényt alkalmazzuk, amely a szokásos formula, amelyet már használtunk az 1. előfordulás nélküli duplikátumok keresésére egy oszlopban.
• A további oszlopok (C2:D8) esetében a lényeg az abszolút és relatív hivatkozások okos használata a két COUNTIF függvényben. A könnyebb érthetőség kedvéért bemásoltam a G oszlopba, így láthatjuk, hogyan változik a képlet, ha más cellákra alkalmazzuk:
  

Mivel az IF feltétel mindig TRUE az elsőtől eltérő összes oszlopra (az oszlopok száma nagyobb, mint 1), a képlet így halad:

• Megszámolja egy adott elem (a fenti képernyőképen D5) előfordulásának számát az adott oszloptól balra lévő összes oszlopban: COUNTIF(B:$C,D5)
• Megszámolja az adott elem előfordulásának számát az elem oszlopában, az elem cellájáig: A képlet végül összeadja a két COUNTIF függvény eredményét: COUNTIF(D:D5,D5)
• . Ha az összesített szám nagyobb, mint 1, vagyis ha az elemnek több előfordulása van, akkor a szabály alkalmazásra kerül, és az elem kiemelésre kerül.

Egy oszlopban lévő duplikált értékek alapján egész sorok kiemelése

Ha a táblázat több oszlopot tartalmaz, előfordulhat, hogy egy adott oszlopban lévő duplikált rekordok alapján egész sorokat szeretne kiemelni.

Amint már tudjuk, az Excel beépített duplikációs szabálya csak cellaszinten működik. Egy egyéni képletalapú szabállyal azonban nem okoz gondot a sorok árnyékolása. A lényeg, hogy kijelölje az egész sorokat, majd hozzon létre egy szabályt az alábbi képletek valamelyikével:

• A duplikált sorok kiemeléséhez az 1. előfordulás kivételével:
  =COUNTIF($A:$A2, $A2)>1
• A duplikált sorok kiemeléséhez az 1. előfordulással együtt:
  =COUNTIF($A:$A, $A2)>1

Hol A2 az első cella és A15 az utolsó használt cella abban az oszlopban, amelyet duplikátumokra szeretne ellenőrizni. Mint látja, az abszolút és relatív cellahivatkozások okos használata az, ami a különbséget jelenti.
A következő képernyőkép mindkét szabályt működés közben mutatja be:

Hogyan lehet kiemelni a duplikált sorokat az Excelben

Az előző példa bemutatta, hogyan lehet egész sorokat színezni egy adott oszlopban lévő duplikált értékek alapján. De mi van akkor, ha olyan sorokat szeretne megjeleníteni, amelyek több oszlopban is azonos értékeket tartalmaznak? Vagy hogyan lehet kiemelni az abszolút duplikált sorokat, vagyis azokat, amelyek minden oszlopban teljesen azonos értékekkel rendelkeznek?

Ezért alkalmazza a COUNTIFS függvényt, amely lehetővé teszi a cellák több szempont szerinti összehasonlítását. Például az A és B oszlopban azonos értékekkel rendelkező duplikált sorok kiemeléséhez használja a következő képletek egyikét:

• A duplikált sorok kiemeléséhez az 1. előfordulások kivételével:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• A duplikált sorok kiemeléséhez az 1. előfordulásokkal:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

A következő képernyőkép mutatja az eredményt:

Azt érti, hogy a fenti példa csak demonstrációs célokat szolgál. A duplikált sorok kiemelésekor a valós lapjain természetesen nem korlátozódik arra, hogy csak 2 oszlop értékeit hasonlítsa össze, a COUNTIFS függvény akár 127 tartomány/kritérium párost is képes feldolgozni.

Egymás utáni duplikált cellák kiemelése az Excelben

Néha előfordulhat, hogy nem az összes duplikátumot kell kiemelnie egy oszlopban, hanem csak az egymás utáni duplikált cellákat, azaz az egymás mellett lévőket kell megjelenítenie. Ehhez jelölje ki az adatokat tartalmazó cellákat (az oszlopfejlécet nem beleértve), és hozzon létre egy feltételes formázási szabályt az alábbi képletek valamelyikével:

• Az 1. előfordulás nélküli egymást követő duplikációk kiemeléséhez:
  =$A1=$A2
• Az 1. előfordulással rendelkező egymást követő duplikációk kiemeléséhez:
  =OR($A1=$A2, $A2=$A3)

A következő képernyőkép az egymást követő duplikált szövegek kiemelését mutatja be, de ezek a szabályok az egymást követő duplikált számok és dátumok esetében is működnek:

Ha az Excel táblázatában üres sorok lehetnek, és nem szeretné, hogy az egymás utáni üres cellák kiemelésre kerüljenek, végezze el a képletek következő javítását:

• Az 1. előfordulás nélküli egymást követő duplikált cellák kiemelése és az üres cellák figyelmen kívül hagyása:
  =AND($A2<>"", $A1=$A2)
• Az 1. előfordulású egymást követő duplikált cellák kiemelése és az üres cellák figyelmen kívül hagyása:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Mint látja, nem nagy dolog a duplikált cellák kiemelése az Excelben a feltételes formázás segítségével. Van azonban még egy gyorsabb és egyszerűbb módszer is. Ha ezt szeretné megtudni, olvassa el az útmutató következő részét.

Hogyan lehet a duplikátumokat kiemelni az Excelben a Duplicate Removerrel

A Duplicate Remover bővítmény az Excelben található duplikált rekordok kezelésének mindenre kiterjedő megoldása. Képes megtalálni, kiemelni, kijelölni, másolni vagy áthelyezni a duplikált cellákat vagy teljes duplikált sorokat.

Neve ellenére a bővítmény gyorsan, különböző színekkel képes kiemelni a duplikátumokat anélkül, hogy törölné őket.

A Duplicate Remover 3 új funkcióval bővíti az Excel szalagot:

• Dedupe Table – a duplikátumok azonnali keresése és kiemelése egy táblázatban.
• Duplicate Remover – lépésről lépésre haladó varázsló fejlett beállításokkal a duplikátumok vagy egyedi értékek azonosításához és kiemeléséhez 1 táblázatban.
• Compare 2 Tables – duplikátumok keresése és kiemelése két oszlop vagy két különálló táblázat összehasonlításával.

Az Ultimate Suite for Excel telepítése után ezeket az eszközöket az Ablebits Data fülön a Dedupe csoportban találja:

Duplikátumok kiemelése Excelben néhány kattintással

Ezért a példáért a következő táblázatot hoztam létre néhány száz sorral. A célunk pedig az, hogy kiemeljük azokat a duplikált sorokat, amelyek mindhárom oszlopban azonos értékekkel rendelkeznek:

Hiszi vagy sem, mindössze két egérkattintással elérheti a kívánt eredményt 🙂

1. A táblázat bármelyik celláját kijelölve kattintson a Dedupe Table gombra, és az okos kiegészítő az egész táblázatot kiemeli.
2. A Dedupe Table párbeszédablak megnyílik, ahol az összes oszlop automatikusan ki van jelölve, és alapértelmezés szerint a Color duplicates opciót választja. Így csak annyit kell tennie, hogy az OK gombra kattint 🙂 Kész!

Tipp. Ha a duplikált sorokat egy vagy több oszlop alapján szeretné felismerni, törölje az összes nem releváns oszlop jelölését, és csak a kulcsfontosságú oszlop(ok)at hagyja kijelölve.

Az eredmény pedig ehhez hasonlóan nézne ki:

Amint a fenti képen látható, a Dupe Table eszköz kiemelte az első előfordulás nélküli duplikált sorokat.

Ha az első előfordulásokat is tartalmazó duplikátumokat szeretné kiemelni, vagy ha nem a duplikátumokat, hanem az egyedi rekordokat szeretné színezni, vagy ha nem tetszik az alapértelmezett piros szín, akkor használja a Duplikátum eltávolító varázslót, amely mindezekkel a funkciókkal és még sok mással is rendelkezik.

Kiemeli a duplikátumokat az Excelben egy fejlett, lépésről lépésre haladó varázsló segítségével

A gyors Dedupe Table eszközzel összehasonlítva a Duplicate Remover varázsló néhány kattintással többet igényel, de ezt számos további lehetőséggel kárpótolja. Hadd mutassam meg működés közben:

1. Kiválasztja a táblázat bármelyik celláját, ahol a duplikátumokat szeretné kiemelni, és kattintson a szalag Duplikátum eltávolító gombjára. A varázsló lefut, és az egész táblázat kijelölésre kerül. A kiegészítő a biztonság kedvéért egy biztonsági másolat készítését is javasolja a táblázatról. Ha erre nincs szüksége, törölje a jelölőnégyzetet.

   Ellenőrizze, hogy a táblázat helyesen lett-e kijelölve, és kattintson a Tovább gombra.

   

2. Válassza ki a következő adattípusok egyikét, amelyet meg akar találni:
   • Duplikátumok az 1. előfordulások kivételével
   • Duplikátumok az 1. előfordulásokkal
   • Egyedi értékek
   • Egyedi értékek és 1. duplikátum előfordulások

   Ebben a példában keressük meg a duplikátumokat + 1. előfordulásokat:
   

3. Most válasszuk ki a duplikátumok keresésére szolgáló oszlopokat. Mivel a teljes duplikátumsorokat szeretnénk kiemelni, mind a 3 oszlopot kiválasztottam.
   

   A kiegészítővel megadhatja, hogy a táblázatnak van-e fejléce, és hogy ki akarja-e hagyni az üres cellákat. Alapértelmezés szerint mindkét opció be van jelölve.

4. Végül válassza ki a duplikátumokon végrehajtandó műveletet. Számos lehetőségünk van, mint például a duplikátumok kijelölése, törlése, másolása, áthelyezése vagy státusz oszlop hozzáadása a duplikátumok azonosítására.

Mivel ma a duplikátumok Excelben történő kiemelésének különböző módjait vizsgáljuk, a választásunk egyértelmű 🙂 Válassza tehát a Kitöltés színnel lehetőséget, és válassza ki a téma egyik szabványos színét, vagy kattintson a További színek… gombra, és válasszon bármilyen egyéni RGB vagy HSL színt.

Kattintson a Befejezés gombra, és élvezze az eredményt 🙂

Így jelölheti ki a duplikátumokat az Excelben a Duplicate Remover add-in segítségével. Ha kíváncsi arra, hogy kipróbálja ezt az eszközt a saját munkalapjain, akkor szívesen látjuk, ha letölti az Ultimate Suite teljes funkcionalitású próbaverzióját, amely tartalmazza az összes időmegtakarító eszközünket az Excelhez. A visszajelzéseit pedig kommentben nagyra értékeljük!

Ha elégedett a Duplicate Remover teljesítményével, örömmel biztosítjuk Önnek ezt a különleges lehetőséget:

You may also be interested in

• How to identify duplicates in Excel (in 1 table)
• How to compare 2 columns or tables for matches and differences
• How to remove duplicate rows in Excel

How to remove duplicate rows in Excel .