Kako usporediti dva stupca u Excelu i ukloniti duplikate (označiti, obojati, premjestiti)

Za čitanje ovog članka trebat će vam oko 10 minuta. U sljedećih 5 minuta možete jednostavno usporediti dva stupca u Excelu i saznati postoje li duplikati u njima, izbrisati ih ili označiti bojom. Dakle, došlo je vrijeme!

Excel je vrlo moćna i stvarno cool aplikacija za stvaranje i obradu velikih količina podataka. Ako imate nekoliko radnih knjiga s podacima (ili samo jednu ogromnu tablicu), onda vjerojatno želite usporediti 2 stupca, pronaći duplicirane vrijednosti, a zatim učiniti nešto s njima, na primjer, izbrisati, označiti ili očistiti sadržaj. Stupci mogu biti u istoj tablici, biti susjedni ili nesusjedni, mogu se nalaziti na 2 različita lista ili čak u različitim knjigama.

Zamislite da imamo 2 stupca s imenima ljudi – 5 imena po stupcu A i 3 imena u stupcu B. Morate usporediti imena u ova dva stupca i pronaći duplikate. Kao što razumijete, ovo su fiktivni podaci, uzeti samo za primjer. U stvarnim tablicama imamo posla s tisućama ili čak desecima tisuća zapisa.

Opcija A: oba su stupca na istom listu. Na primjer, stupac A i stupac B.

Opcija B: Stupci su na različitim listovima. Na primjer, stupac A na plahti Sheet2 i stupac A na plahti Sheet3.

Excel 2013, 2010 i 2007 imaju ugrađeni alat Ukloni duplikate (Ukloni duplikate), ali je nemoćan u ovoj situaciji jer ne može usporediti podatke u 2 stupca. Štoviše, može ukloniti samo duplikate. Nema drugih opcija poput isticanja ili mijenjanja boja. I točka!

Zatim ću vam pokazati moguće načine za usporedbu dvaju stupaca u Excelu, što će vam omogućiti pronalaženje i uklanjanje dvostrukih zapisa.

• Usporedite 2 stupca i potražite duplikate pomoću formula
  
  • Opcija A: oba su stupca na istom listu
  • Opcija B: Stupci su na različitim listovima ili u različitim radnim knjigama
• Obrada pronađenih duplikata
  
  • Prikaži samo duplicirane retke u stupcu A
  • Promijenite boju ili označite pronađene duplikate
  • Uklonite duplikate iz prvog stupca

Usporedite 2 stupca u Excelu i pronađite dvostruke unose pomoću formula

Opcija A: oba su stupca na istom listu

1. U prvu praznu ćeliju (u našem primjeru to je ćelija C1) upisujemo 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 prvog stupca koju ćemo usporediti. 1 B$ и 10000 B$ to su adrese prve i zadnje ćelije drugog stupca, s kojima ćemo izvršiti usporedbu. Obratite pažnju na apsolutne reference – ispred slova stupaca i brojeva redaka stoji znak dolara ($). Koristim apsolutne reference kako bi adrese ćelija ostale iste prilikom kopiranja formula.

   Ako želite pronaći duplikate u stupcu 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"A"Duplikat» Možete napisati vlastite oznake, na primjer, «Nije pronađen"A"Pronađeno“, ili ostaviti samo “Duplikat' i unesite razmak umjesto druge vrijednosti. U potonjem slučaju, ćelije za koje nisu pronađeni duplikati ostat će prazne, a vjerujem da je ovakav prikaz podataka najprikladniji za daljnju analizu.

2. Sada kopirajmo našu formulu u sve ćelije u stupcu C, sve do donjeg retka, koji sadrži podatke u stupcu A. Da biste to učinili, pomaknite pokazivač miša u donji desni kut ćelije C1, pokazivač će imati oblik crnog križića, kao što je prikazano na slici ispod:Pritisnite i držite lijevu tipku miša i povucite rub okvira prema dolje, označavajući sve ćelije u koje želite umetnuti formulu. Kada su odabrane sve potrebne ćelije, otpustite tipku miša:

   

Savjet: U velikim tablicama kopiranje formule bit će brže ako koristite tipkovničke prečace. Označite ćeliju C1 i pritisnite Ctrl + C (za kopiranje formule u međuspremnik), zatim kliknite Ctrl + Shift + End (za odabir svih nepraznih ćelija u stupcu C) i na kraju pritisnite Ctrl + V (za umetanje formule u sve odabrane ćelije).

3. Odlično, sada su sve duplicirane vrijednosti označene kao "Duplikat„:

Opcija B: dva stupca su na različitim listovima (u različitim radnim knjigama)

1. U prvoj ćeliji prvog praznog stupca na radnom listu Sheet2 (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 Sheet3 je naziv lista na kojem se nalazi 2. stupac, i $A$1: $A$10000 su adrese ćelija od prve do posljednje u ovom drugom stupcu.

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

Obrada pronađenih duplikata

Odlično, pronašli smo unose u prvom stupcu koji su prisutni i u drugom stupcu. Sada moramo nešto učiniti s njima. Ručno pregledavanje svih dupliciranih zapisa u tablici prilično je neučinkovito i oduzima previše vremena. Ima boljih načina.

Prikaži samo duplicirane retke u stupcu A

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

Kliknite desnom tipkom miša i odaberite iz kontekstnog izbornika Umetanje (Umetnuti):

Dodijelite nazive stupcima, na primjer, "Ime i Prezime"A"Duplikat?» Zatim otvorite karticu Datum (Podaci) i pritisnite Filter (Filtar):

Nakon toga kliknite na malu sivu strelicu pored "Duplikat?« za otvaranje izbornika filtera; poništite odabir svih stavki na ovom popisu osim Duplikat, i pritisnite OK.

To je sve, sada vidite samo te elemente stupca А, koji se dupliciraju u stupcu В. U našem stolu za vježbanje postoje samo dvije takve ćelije, ali, kao što razumijete, u praksi će ih biti mnogo više.

Za ponovno prikazivanje svih redaka stupca А, kliknite simbol filtra u stupcu В, koji sada izgleda kao lijevak s malom strelicom, i odaberite Odaberi sve (Odaberi sve). Ili možete učiniti isto putem vrpce klikom Datum (Podaci) > Odaberite i filtrirajte (Sortiraj i filtriraj) > Izbriši (Očisti) 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 označiti duplicirane ćelije drugom bojom fonta, bojom ispune ili nekom drugom metodom...

U tom slučaju filtrirajte duplikate kao što je gore prikazano, odaberite sve filtrirane ćelije i kliknite Ctrl + 1za otvaranje dijaloga Format Cells (format ćelije). Na primjer, promijenimo boju ispune ćelija u recima s duplikatima u svijetložutu. Naravno, možete promijeniti boju ispune pomoću alata Popuniti (Boja ispune) kartica Naslovna (Dom), ali prednost dijaloškog okvira Format Cells (Format ćelije) tako da možete konfigurirati sve opcije oblikovanja u isto vrijeme.

Sada sigurno nećete propustiti ćelije s duplikatima:

Uklanjanje dupliciranih vrijednosti iz prvog stupca

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

Ako su 2 stupca koja uspoređujete na različitim listovima, odnosno u različitim tablicama desnom tipkom miša kliknite odabrani raspon i odaberite Izbriši redak (Ukloni liniju):

tisak OKkada Excel od vas traži da potvrdite da stvarno želite izbrisati cijeli red lista i zatim poništite filtar. Kao što vidite, preostali su samo redovi s jedinstvenim vrijednostima:

Ako su 2 stupca na istom listu, blizu jedan drugome (susjedni) ili ne blizu (nisu susjedni), tada će postupak uklanjanja duplikata biti malo kompliciraniji. Ne možemo ukloniti cijeli redak s dupliciranim vrijednostima jer će se time ukloniti i ćelije iz drugog stupca. Dakle, ostaviti samo jedinstvene unose u stupcu А, napravi to:

1. Filtrirajte tablicu da prikazuje samo duplicirane vrijednosti i odaberite te ćelije. Desnom tipkom miša kliknite na njih i odaberite iz kontekstnog izbornika Čisti sadržaj (jasan sadržaj).
2. Očistite filter.
3. Odaberite sve ćelije u stupcu А, počevši od ćelije A1 sve do dna koji sadrži podatke.
4. kliknite Datum (Podaci) i pritisnite Poredaj od A do Z (Poređaj od A do Ž). U dijaloškom okviru koji se otvori odaberite Nastavite s trenutnim odabirom (Sortiraj unutar navedenog odabira) i klikni gumb Crna (Sortiranje):
5. Izbrišite stupac s formulom, više vam neće trebati, od sada imate samo jedinstvene vrijednosti.
6. To je to, sad kolumna А sadrži samo jedinstvene podatke koji nisu u stupcu В:

Kao što vidite, uklanjanje duplikata iz dva stupca u Excelu pomoću formula nije tako teško.