Si të krahasoni dy kolona në Excel dhe të hiqni dublikatat (theksoni, ngjyrosni, lëvizni)

Ky artikull do t'ju marrë rreth 10 minuta për ta lexuar. Në 5 minutat e ardhshme, mund të krahasoni lehtësisht dy kolona në Excel dhe të zbuloni nëse ka dublikatë në to, t'i fshini ose t'i nënvizoni me ngjyra. Pra, ka ardhur koha!

Excel është një aplikacion shumë i fuqishëm dhe vërtet i lezetshëm për krijimin dhe përpunimin e sasive të mëdha të të dhënave. Nëse keni disa libra pune me të dhëna (ose vetëm një tabelë të madhe), atëherë ndoshta dëshironi të krahasoni 2 kolona, ​​të gjeni vlera të kopjuara dhe më pas të bëni diçka me to, për shembull, të fshini, theksoni ose pastroni përmbajtjen. Kolonat mund të jenë në të njëjtën tabelë, të jenë ngjitur ose jo ngjitur, mund të vendosen në 2 fletë të ndryshme ose edhe në libra të ndryshëm.

Imagjinoni që kemi 2 kolona me emrat e njerëzve – 5 emra për kolonë A dhe 3 emra në një kolonë B. Ju duhet të krahasoni emrat në këto dy kolona dhe të gjeni dublikatë. Siç e kuptoni, këto janë të dhëna fiktive, të marra vetëm për shembull. Në tabela reale kemi të bëjmë me mijëra apo edhe dhjetëra mijëra rekorde.

Opsioni A: të dyja kolonat janë në të njëjtën fletë. Për shembull, një kolonë A dhe kolona B.

Opsioni B: Kolonat janë në fletë të ndryshme. Për shembull, një kolonë A në fletë Sheet2 dhe kolona A në fletë Sheet3.

Excel 2013, 2010 dhe 2007 kanë një mjet të integruar Hiq Dublikatat (Hiq Dublikatat) por është i pafuqishëm në këtë situatë pasi nuk mund të krahasojë të dhënat në 2 kolona. Për më tepër, mund të heqë vetëm dublikatat. Nuk ka opsione të tjera si theksimi ose ndryshimi i ngjyrave. Dhe pikë!

Më pas, unë do t'ju tregoj mënyrat e mundshme për të krahasuar dy kolona në Excel, të cilat do t'ju lejojnë të gjeni dhe hiqni të dhënat e kopjuara.

• Krahasoni 2 kolona dhe kërkoni dublikata duke përdorur formulat
  
  • Opsioni A: të dyja kolonat janë në të njëjtën fletë
  • Opsioni B: Kolonat janë në fletë të ndryshme ose në libra të ndryshëm pune
• Përpunimi i dublikatave të gjetura
  
  • Trego vetëm rreshta dublikatë në kolonën A
  • Ndrysho ngjyrën ose thekso kopjet e gjetura
  • Hiqni dublikatat nga kolona e parë

Krahasoni 2 kolona në Excel dhe gjeni hyrje të kopjuara duke përdorur formula

Opsioni A: të dyja kolonat janë në të njëjtën fletë

1. Në qelizën e parë boshe (në shembullin tonë, kjo është qeliza C1), ne shkruajmë formulën e mëposhtme:

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

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

   

   Në formulën tonë A1 kjo është qeliza e parë e kolonës së parë që do të krahasojmë. 1 dollarë BB и 10000 dollarë BB këto janë adresat e qelizës së parë dhe të fundit të kolonës së dytë, me të cilat do të bëjmë krahasimin. Vini re referencat absolute - shkronjat e kolonave dhe numrat e rreshtave paraprihen nga një shenjë dollari ($). Unë përdor referenca absolute në mënyrë që adresat e qelizave të mbeten të njëjta gjatë kopjimit të formulave.

   Nëse dëshironi të gjeni dublikatë në një kolonë B, ndryshoni referencat në mënyrë që formula të duket si kjo:

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

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

   Në vend të kësaj "Vetëm"Dhe"I kopjuar» Ju mund të shkruani etiketat tuaja, për shembull, «Nuk u gjet"Dhe"gjetur", ose largohu vetëm"I kopjuar' dhe futni një karakter hapësinor në vend të vlerës së dytë. Në rastin e fundit, qelizat për të cilat nuk gjenden kopje do të mbeten bosh dhe, besoj, kjo paraqitje e të dhënave është më e përshtatshme për analiza të mëtejshme.

2. Tani le të kopjojmë formulën tonë në të gjitha qelizat në kolonë C, deri në rreshtin e poshtëm, i cili përmban të dhënat në kolonë A. Për ta bërë këtë, lëvizni treguesin e miut në këndin e poshtëm të djathtë të qelizës C1, treguesi do të marrë formën e një kryqëzimi të zi, siç tregohet në foton më poshtë:Klikoni dhe mbani shtypur butonin e majtë të miut dhe tërhiqni kufirin e kornizës poshtë, duke theksuar të gjitha qelizat ku dëshironi të futni formulën. Kur të zgjidhen të gjitha qelizat e kërkuara, lëshoni butonin e miut:

   

Këshillë: Në tabela të mëdha, kopjimi i formulës do të jetë më i shpejtë nëse përdorni shkurtore të tastierës. Theksoni një qelizë C1 dhe shtypni Ctrl + C (për të kopjuar formulën në clipboard), më pas kliko Ctrl + Shift + Fund (për të zgjedhur të gjitha qelizat jo bosh në kolonën C) dhe në fund shtypni Ctrl + V (për të futur formulën në të gjitha qelizat e zgjedhura).

3. E shkëlqyeshme, tani të gjitha vlerat e kopjuara janë shënuar si "I kopjuar':

Opsioni B: dy kolona janë në fletë të ndryshme (në libra të ndryshëm pune)

1. Në qelizën e parë të kolonës së parë boshe në fletën e punës Sheet2 (në rastin tonë është kolona B) shkruani formulën e mëposhtme:

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

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

   Këtu Sheet3 është emri i fletës në të cilën ndodhet kolona e 2-të, dhe 1$A$10000$A$ janë adresat e qelizave nga 1 deri në të fundit në këtë kolonë të dytë.

2. Kopjoni formulën në të gjitha qelizat në një kolonë B (njëlloj si opsioni A).
3. Ne marrim këtë rezultat:

Përpunimi i dublikatave të gjetura

E shkëlqyeshme, ne kemi gjetur hyrje në kolonën e parë që janë të pranishme edhe në kolonën e dytë. Tani duhet të bëjmë diçka me ta. Kalimi me dorë i të gjitha regjistrimeve të kopjuara në një tabelë është mjaft joefikas dhe kërkon shumë kohë. Ka mënyra më të mira.

Trego vetëm rreshta dublikatë në kolonën A

Nëse kolonat tuaja nuk kanë tituj, atëherë duhet t'i shtoni ato. Për ta bërë këtë, vendosni kursorin në numrin që përfaqëson rreshtin e parë dhe ai do të kthehet në një shigjetë të zezë, siç tregohet në figurën më poshtë:

Klikoni me të djathtën dhe zgjidhni nga menyja e kontekstit futje (Fut):

Jepni emra kolonave, për shembull, "Emër"Dhe"Dublikatë?» Pastaj hapni skedën Data (Të dhënat) dhe shtypni filtra (Filtër):

Pas kësaj klikoni në shigjetën e vogël gri pranë "Dublikatë?« për të hapur menynë e filtrit; zgjidh të gjithë artikujt në këtë listë përveç I kopjuar, dhe shtypni OK.

Kjo është e gjitha, tani ju shihni vetëm ato elemente të kolonës А, të cilat janë dyfishuar në kolonë В. Ekzistojnë vetëm dy qeliza të tilla në tabelën tonë të trajnimit, por, siç e kuptoni, në praktikë do të ketë shumë më tepër prej tyre.

Për të shfaqur përsëri të gjitha rreshtat e një kolone А, klikoni simbolin e filtrit në kolonë В, e cila tani duket si një gyp me një shigjetë të vogël dhe zgjidhni Zgjidh gjithë (Selektoj të gjitha). Ose mund të bëni të njëjtën gjë përmes Shiritit duke klikuar Data (Të dhënat) > Zgjidh & Filtro (Rendit & Filtro) > Qartë (Pastro) siç tregohet në pamjen e mëposhtme të ekranit:

Ndrysho ngjyrën ose thekso kopjet e gjetura

Nëse shënimet "I kopjuar” nuk mjafton për qëllimet tuaja dhe ju dëshironi të shënoni qeliza të dyfishta me një ngjyrë të ndryshme fonti, ngjyrë mbushjeje ose ndonjë metodë tjetër…

Në këtë rast, filtroni dublikatat siç tregohet më sipër, zgjidhni të gjitha qelizat e filtruara dhe klikoni Ctrl + 1për të hapur dialogun Qelizat e formatit (format qelizë). Si shembull, le të ndryshojmë ngjyrën e mbushjes së qelizave në rreshta me dublikatë në të verdhë të ndezur. Sigurisht, ju mund të ndryshoni ngjyrën e mbushjes me mjet Mbush Skeda (Ngjyra e mbushjes). Fillimi (Shtëpia) por avantazhi i kutisë së dialogut Qelizat e formatit (Formati i qelizës) në të cilën mund të konfiguroni të gjitha opsionet e formatimit në të njëjtën kohë.

Tani definitivisht nuk do t'ju mungojë asnjë qelizë me dublikatë:

Heqja e vlerave të kopjuara nga kolona e parë

Filtro tabelën në mënyrë që të shfaqen vetëm qelizat me vlera të dyfishta dhe zgjidhni ato qeliza.

Nëse 2 kolonat që po krahasoni janë në fletë të ndryshme, domethënë, në tabela të ndryshme, kliko me të djathtën në diapazonin e zgjedhur dhe zgjidhni Fshi Rreshtin (Hiq rreshtin):

shtyp OKkur Excel ju kërkon të konfirmoni që vërtet dëshironi të fshini të gjithë rreshtin e fletës dhe më pas pastroni filtrin. Siç mund ta shihni, mbeten vetëm rreshtat me vlera unike:

Nëse 2 kolona janë në të njëjtën fletë, afër njëri-tjetrit (ngjitur) ose jo afër njëri-tjetrit (jo ngjitur), atëherë procesi i heqjes së dublikatave do të jetë pak më i ndërlikuar. Nuk mund ta heqim të gjithë rreshtin me vlera të kopjuara, pasi kjo do të heqë qelizat edhe nga kolona e dytë. Pra, për të lënë vetëm shënime unike në një kolonë А, bëni këtë:

1. Filtro tabelën për të treguar vetëm vlera të kopjuara dhe zgjidhni ato qeliza. Klikoni me të djathtën mbi to dhe zgjidhni nga menyja e kontekstit Pastro përmbajtjen (pastaj përmbajtjen).
2. Pastroni filtrin.
3. Zgjidhni të gjitha qelizat në një kolonë А, duke filluar nga qeliza A1 deri në fund që përmban të dhënat.
4. Kliko Data (Të dhënat) dhe shtypni Renditni nga A në Z (Renditni nga A në Z). Në kutinë e dialogut që hapet, zgjidhni Vazhdoni me zgjedhjen aktuale (Renditni brenda përzgjedhjes së specifikuar) dhe klikoni butonin e zezë (Renditja):
5. Fshijeni kolonën me formulën, nuk do t'ju duhet më, tani e tutje keni vetëm vlera unike.
6. Kjo është ajo, tani kolona А përmban vetëm të dhëna unike që nuk janë në kolonë В:

Siç mund ta shihni, heqja e kopjimeve nga dy kolona në Excel duke përdorur formula nuk është aq e vështirë.