Kako usporediti dvije kolone u Excelu i ukloniti duplikate (istaknuti, obojiti, premjestiti)

Ovaj članak će vam trebati oko 10 minuta za čitanje. U narednih 5 minuta možete jednostavno uporediti dvije kolone u Excel-u i saznati da li u njima ima duplikata, obrišite ih ili označite bojom. Dakle, došlo je vrijeme!

Excel je vrlo moćna i stvarno cool aplikacija za kreiranje i obradu velikih količina podataka. Ako imate nekoliko radnih knjiga sa podacima (ili samo jednu ogromnu tabelu), onda verovatno želite da uporedite 2 kolone, pronađete duple vrednosti, a zatim uradite nešto sa njima, na primer, izbrišete, označite ili obrišete sadržaj. Kolone mogu biti u istoj tabeli, susedne ili nesusedne, mogu se nalaziti na 2 različita lista ili čak u različitim knjigama.

Zamislite da imamo 2 kolone sa imenima ljudi – 5 imena po koloni A i 3 imena u koloni B. Morate uporediti imena u ove dvije kolone i pronaći duplikate. Kao što razumijete, ovo su fiktivni podaci, uzeti samo za primjer. U stvarnim tabelama imamo posla sa hiljadama ili čak desetinama hiljada zapisa.

Opcija A: obe kolone su na istom listu. Na primjer, stupac A i stupac B.

Opcija B: Kolone su na različitim listovima. Na primjer, stupac A na listu List2 i stupac A na listu List3.

Excel 2013, 2010 i 2007 imaju ugrađeni alat Ukloni duplikate (Ukloni duplikate) ali je nemoćan u ovoj situaciji jer ne može upoređivati ​​podatke u 2 kolone. Štaviše, može ukloniti samo duplikate. Nema drugih opcija kao što je isticanje ili promjena boja. I tačka!

Zatim ću vam pokazati moguće načine za poređenje dvije kolone u Excelu, što će vam omogućiti da pronađete i uklonite duple zapise.

• Uporedite 2 kolone i potražite duplikate koristeći formule
  
  • Opcija A: obje kolone su na istom listu
  • Opcija B: Kolone su na različitim listovima ili u različitim radnim sveskama
• Obrada pronađenih duplikata
  
  • Prikaži samo duplirane redove u koloni A
  • Promijenite boju ili označite pronađene duplikate
  • Uklonite duplikate iz prve kolone

Uporedite 2 kolone u Excel-u i pronađite duple unose pomoću formula

Opcija A: obje kolone su na istom listu

1. U prvoj praznoj ćeliji (u našem primjeru, ovo je ćelija C1), pišemo sljedeću formulu:

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

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

   

   U našoj formuli A1 ovo je prva ćelija prve kolone koju ćemo uporediti. 1 B$ и 10000 B$ ovo su adrese prve i poslednje ćelije druge kolone sa kojima ćemo izvršiti poređenje. Obratite pažnju na apsolutne reference – slovima kolona i brojevima redova prethodi znak dolara ($). Koristim apsolutne reference tako da adrese ćelija ostaju iste prilikom kopiranja formula.

   Ako želite pronaći duplikate u koloni B, promijenite reference tako da formula izgleda ovako:

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

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

   Umjesto toga “samo"I"duplikat» Možete napisati vlastite etikete, na primjer, «Nije pronađen"I"nađen“, ili ostavite samo “duplikat' i unesite znak razmak umjesto druge vrijednosti. U potonjem slučaju, ćelije za koje nisu pronađeni duplikati ostaće prazne, a vjerujem da je ovakav prikaz podataka najpogodniji za dalju analizu.

2. Sada kopirajmo našu formulu u sve ćelije u koloni C, sve do donjeg reda, koji sadrži podatke u koloni A. Da biste to učinili, pomaknite pokazivač miša u donji desni ugao ćelije C1, pokazivač će imati oblik crnog križića, kao što je prikazano na slici ispod:Kliknite i držite lijevu tipku miša i povucite ivicu okvira prema dolje, naglašavajući sve ćelije u koje želite da umetnete formulu. Kada su sve potrebne ćelije odabrane, pustite tipku miša:

   

Savjet: U velikim tabelama, kopiranje formule će biti brže ako koristite prečice na tastaturi. Označite ćeliju C1 i pritisnite Ctrl + C (da biste kopirali formulu u međuspremnik), a zatim kliknite Ctrl+Shift+End (da biste odabrali sve ćelije koje nisu prazne u koloni C) i na kraju pritisnite Ctrl + V (da biste umetnuli formulu u sve odabrane ćelije).

3. Odlično, sada su sve duplirane vrijednosti označene kao "duplikat":

Opcija B: dvije kolone su na različitim listovima (u različitim radnim knjigama)

1. U prvoj ćeliji prve prazne kolone na radnom listu List2 (u našem slučaju to je stupac B) unesite sljedeću formulu:

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

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

   ovdje List3 je naziv lista na kojem se nalazi 2. stupac, i $A$1:$A$10000 su adrese ćelija od 1. do posljednje u ovoj 2. koloni.

2. Kopirajte formulu u sve ćelije u koloni B (isto kao opcija A).
3. Dobijamo ovaj rezultat:

Obrada pronađenih duplikata

Odlično, našli smo unose u prvoj koloni koji su prisutni i u drugoj koloni. Sada moramo nešto da uradimo sa njima. Ručno prolaženje kroz sve duple zapise u tabeli je prilično neefikasno i oduzima previše vremena. Postoje bolji načini.

Prikaži samo duplirane redove u koloni A

Ako vaše kolone nemaju zaglavlja, morate ih dodati. Da biste to učinili, postavite kursor na broj koji predstavlja prvi red i on će se pretvoriti u crnu strelicu, kao što je prikazano na slici ispod:

Kliknite desnim tasterom miša i izaberite iz kontekstnog menija umetanje (umetnuti):

Dajte imena kolonama, na primjer, “Ime"I"Duplikat?» Zatim otvorite karticu podaci (Podaci) i pritisnite Filter (Filter):

Nakon toga kliknite na malu sivu strelicu pored “Duplikat?« da otvorite meni filtera; poništite sve stavke na ovoj listi osim duplikat, i pritisnite OK.

To je sve, sada vidite samo te elemente kolone А, koji su duplirani u koloni В. U našoj tabeli za obuku postoje samo dvije takve ćelije, ali, kao što razumijete, u praksi će ih biti mnogo više.

Da ponovo prikažete sve redove kolone А, kliknite na simbol filtera u koloni В, koji sada izgleda kao lijevak sa malom strelicom, i odaberite Izaberite sve (Označi sve). Ili možete učiniti isto kroz traku klikom podaci (Podaci) > Odaberite i filtrirajte (Sortiraj i filtriraj) > jasno (Obriši) kao što je prikazano na slici ispod:

Promijenite boju ili označite pronađene duplikate

Ako bilješke “duplikat” nije dovoljno za vaše potrebe i želite da označite duple ćelije drugom bojom fonta, bojom ispune ili nekom drugom metodom...

U ovom slučaju, filtrirajte duplikate kao što je prikazano iznad, odaberite sve filtrirane ćelije i kliknite Ctrl + 1da otvorite dijalog Formatiranje ćelija (format ćelije). Kao primjer, promijenimo boju ispune ćelija u redovima s duplikatima u svijetlo žutu. Naravno, pomoću alata možete promijeniti boju ispune Ispunite Kartica (Boja popune). Početna (Home) ali prednost dijaloga Formatiranje ćelija (Format ćelije) u kojem možete konfigurirati sve opcije oblikovanja u isto vrijeme.

Sada vam sigurno neće nedostajati nijedna ćelija s duplikatima:

Uklanjanje duplih vrijednosti iz prve kolone

Filtrirajte tablicu tako da se prikazuju samo ćelije s dupliranim vrijednostima i odaberite te ćelije.

Ako su 2 kolone koje upoređujete na različitim listovima, odnosno u različitim tabelama, kliknite desnim tasterom miša na izabrani opseg i izaberite Izbriši red (Ukloni red):

pritisnuti OKkada Excel od vas zatraži da potvrdite da zaista želite da izbrišete cijeli red lista, a zatim obrišite filter. Kao što vidite, ostaju samo redovi sa jedinstvenim vrijednostima:

Ako su 2 kolone na istom listu, blizu jedan drugom (susedni) ili ne blizu jedan drugom (nesusedni), tada će proces uklanjanja duplikata biti malo komplikovaniji. Ne možemo ukloniti cijeli red s dupliranim vrijednostima, jer će to ukloniti ćelije i iz druge kolone. Dakle, ostaviti samo jedinstvene unose u koloni А, uradi ovo:

1. Filtrirajte tablicu da prikažete samo duplirane vrijednosti i odaberite te ćelije. Kliknite desnim tasterom miša na njih i izaberite iz kontekstnog menija Jasan sadržaj (jasan sadržaj).
2. Očistite filter.
3. Odaberite sve ćelije u koloni А, počevši od ćelije A1 sve do dna koji sadrži podatke.
4. kliknite podaci (Podaci) i pritisnite Sortiraj od A do Ž (Poređaj od A do Z). U dijaloškom okviru koji se otvori odaberite Nastavite sa trenutnim odabirom (Sortiraj unutar navedenog odabira) i kliknite na dugme Black (Sortiranje):
5. Izbrišite kolonu s formulom, više vam neće trebati, od sada imate samo jedinstvene vrijednosti.
6. To je to, sada kolona А sadrži samo jedinstvene podatke koji nisu u koloni В:

Kao što možete vidjeti, uklanjanje duplikata iz dvije kolone u Excelu pomoću formula nije tako teško.