Kako primerjati dva stolpca v Excelu in odstraniti dvojnike (označi, pobarvaj, premakni)

Branje tega članka vam bo vzelo približno 10 minut. V naslednjih 5 minutah lahko enostavno primerjate dva stolpca v Excelu in ugotovite, ali so v njih dvojniki, jih izbrišete ali barvno označite. Torej, prišel je čas!

Excel je zelo zmogljiva in res kul aplikacija za ustvarjanje in obdelavo velikih količin podatkov. Če imate več delovnih zvezkov s podatki (ali samo eno ogromno tabelo), boste verjetno želeli primerjati 2 stolpca, poiskati podvojene vrednosti in nato narediti nekaj z njimi, na primer izbrisati, označiti ali počistiti vsebino. Stolpci so lahko v isti tabeli, so sosednji ali ne sosednji, lahko se nahajajo na dveh različnih listih ali celo v različnih knjigah.

Predstavljajte si, da imamo 2 stolpca z imeni oseb – 5 imen na stolpec A in 3 imena v stolpcu B. Imena v teh dveh stolpcih morate primerjati in poiskati dvojnike. Kot razumete, so to izmišljeni podatki, vzeti samo za primer. V realnih tabelah imamo opravka z več tisoč ali celo deset tisoči zapisov.

Možnost A: oba stolpca sta na istem listu. Na primer, stolpec A in stolpec B.

Možnost B: Stolpci so na različnih listih. Na primer, stolpec A na listu Sheet2 in stolpec A na listu Sheet3.

Excel 2013, 2010 in 2007 imajo vgrajeno orodje Odstrani dvojnike (Remove Duplicates), vendar je v tej situaciji nemočen, saj ne more primerjati podatkov v 2 stolpcih. Poleg tega lahko odstrani samo dvojnike. Ni drugih možnosti, kot je poudarjanje ali spreminjanje barv. In pika!

Nato vam bom pokazal možne načine za primerjavo dveh stolpcev v Excelu, kar vam bo omogočilo iskanje in odstranjevanje podvojenih zapisov.

• Primerjajte 2 stolpca in poiščite dvojnike z uporabo formul
  
  • Možnost A: oba stolpca sta na istem listu
  • Možnost B: Stolpci so na različnih listih ali v različnih delovnih zvezkih
• Obdelava najdenih dvojnikov
  
  • Pokaži samo podvojene vrstice v stolpcu A
  • Spremenite barvo ali označite najdene dvojnike
  • Odstranite dvojnike iz prvega stolpca

Primerjajte 2 stolpca v Excelu in poiščite podvojene vnose z uporabo formul

Možnost A: oba stolpca sta na istem listu

1. V prvo prazno celico (v našem primeru je to celica C1) zapišemo naslednjo formulo:

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

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

   

   V naši formuli A1 to je prva celica prvega stolpca, ki jo bomo primerjali. 1 B$ и 10000 B$ to sta naslova prve in zadnje celice drugega stolpca, s katerima bomo opravili primerjavo. Upoštevajte absolutne reference – pred črkami stolpcev in številkami vrstic je znak za dolar ($). Uporabljam absolutne reference, tako da ostanejo naslovi celic enaki pri kopiranju formul.

   Če želite najti dvojnike v stolpcu B, spremenite sklice tako, da bo formula videti takole:

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

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

   Namesto tega "samo"In"Dvojnik» Napišete lahko svoje oznake, na primer «Ni bilo mogoče najti"In"Najdeno“ ali pustite samo “Dvojnik' in namesto druge vrednosti vnesite presledek. V slednjem primeru bodo celice, za katere ni najdenih dvojnikov, ostale prazne in menim, da je ta predstavitev podatkov najprimernejša za nadaljnjo analizo.

2. Zdaj pa kopirajmo našo formulo v vse celice v stolpcu C, vse do spodnje vrstice, ki vsebuje podatke v stolpcu A. Če želite to narediti, premaknite kazalec miške v spodnji desni kot celice C1, bo kazalec v obliki črnega križca, kot je prikazano na spodnji sliki:Kliknite in pridržite levi gumb miške ter povlecite rob okvirja navzdol in označite vse celice, v katere želite vstaviti formulo. Ko so izbrane vse zahtevane celice, spustite tipko miške:

   

Nasvet: V velikih tabelah bo kopiranje formule hitrejše, če uporabljate bližnjice na tipkovnici. Označite celico C1 in pritisnite Ctrl + C (da kopirate formulo v odložišče), nato kliknite Ctrl + Shift + End (da izberete vse neprazne celice v stolpcu C) in na koncu pritisnite Ctrl + V (za vstavljanje formule v vse izbrane celice).

3. Odlično, zdaj so vse podvojene vrednosti označene kot "Dvojnik":

Možnost B: dva stolpca sta na različnih listih (v različnih delovnih zvezkih)

1. V prvi celici prvega praznega stolpca na delovnem listu Sheet2 (v našem primeru je to stolpec B) vnesite naslednjo formulo:

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

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

   Tukaj Sheet3 je ime lista, na katerem je 2. stolpec, in 1 A $ 10000: XNUMX A $ so naslovi celic od 1. do zadnjega v tem 2. stolpcu.

2. Kopirajte formulo v vse celice v stolpcu B (enako kot možnost A).
3. Dobimo ta rezultat:

Obdelava najdenih dvojnikov

Odlično, našli smo vnose v prvem stolpcu, ki so prisotni tudi v drugem stolpcu. Zdaj moramo nekaj narediti z njimi. Ročno pregledovanje vseh podvojenih zapisov v tabeli je precej neučinkovito in traja preveč časa. Obstajajo boljši načini.

Pokaži samo podvojene vrstice v stolpcu A

Če vaši stolpci nimajo glav, jih morate dodati. Če želite to narediti, postavite kazalec na številko, ki predstavlja prvo vrstico, in spremenila se bo v črno puščico, kot je prikazano na spodnji sliki:

Z desno miškino tipko kliknite in izberite v kontekstnem meniju vstavljanje (Vstavi):

Poimenujte stolpce, na primer "Ime"In"Podvojeno?» Nato odprite zavihek datum (Podatki) in pritisnite filter (Filter):

Po tem kliknite na majhno sivo puščico poleg "Podvojeno?« da odprete meni filtrov; počistite vse elemente na tem seznamu, razen Dvojnikin pritisnite OK.

To je vse, zdaj vidite samo tiste elemente stolpca А, ki se v stolpcu podvojijo В. V naši vadbeni mizi sta samo dve takšni celici, a kot razumete, jih bo v praksi veliko več.

Za ponovni prikaz vseh vrstic stolpca А, kliknite simbol filtra v stolpcu В, ki je zdaj videti kot lijak z majhno puščico, in izberite Izberi vse (Izberi vse). Lahko pa storite enako prek traku s klikom datum (Podatki) > Izberi in filtriraj (Razvrsti in filtriraj) > Počisti (Počisti), kot je prikazano na spodnjem posnetku zaslona:

Spremenite barvo ali označite najdene dvojnike

Če opombe "Dvojnik” ni dovolj za vaše namene in želite označiti podvojene celice z drugo barvo pisave, barvo polnila ali kako drugo metodo ...

V tem primeru filtrirajte dvojnike, kot je prikazano zgoraj, izberite vse filtrirane celice in kliknite Ctrl + 1da odprete pogovorno okno Oblikuj celice (format celice). Na primer, spremenimo barvo polnila celic v vrsticah z dvojniki v svetlo rumeno. Seveda lahko z orodjem spremenite barvo polnila Izpolnite (Barva polnila). Domov (Domov), vendar prednost pogovornega okna Oblikuj celice (Oblika celice), tako da lahko konfigurirate vse možnosti oblikovanja hkrati.

Zdaj zagotovo ne boste zamudili nobene celice z dvojniki:

Odstranjevanje podvojenih vrednosti iz prvega stolpca

Filtrirajte tabelo tako, da so prikazane samo celice s podvojenimi vrednostmi, in izberite te celice.

Če sta stolpca, ki ju primerjate, na različnih listih, torej v različnih tabelah z desno miškino tipko kliknite izbrani obseg in izberite Izbriši vrstico (Odstrani vrstico):

Pritisnite OKko vas Excel vpraša, da potrdite, da res želite izbrisati celotno vrstico lista, in nato počistite filter. Kot lahko vidite, ostanejo samo vrstice z edinstvenimi vrednostmi:

Če sta 2 stolpca na istem listu, blizu drug drugemu (sosednji) ali ne blizu drug drugega (niso sosednji), potem bo postopek odstranjevanja dvojnikov nekoliko bolj zapleten. Ne moremo odstraniti celotne vrstice s podvojenimi vrednostmi, saj bomo s tem odstranili tudi celice iz drugega stolpca. Torej, da v stolpcu pustite samo edinstvene vnose А, To storite:

1. Filtrirajte tabelo, da prikaže samo podvojene vrednosti, in izberite te celice. Z desno tipko miške kliknite nanje in izberite v kontekstnem meniju Jasna vsebina (pregledna vsebina).
2. Očistite filter.
3. Izberite vse celice v stolpcu А, začenši s celico A1 vse do dna, ki vsebuje podatke.
4. Kliknite datum (Podatki) in pritisnite Razvrsti od A do Ž (Razvrsti od A do Ž). V pogovornem oknu, ki se odpre, izberite Nadaljujte s trenutno izbiro (Razvrsti znotraj navedenega izbora) in klikni gumb črna (Razvrščanje):
5. Izbrišite stolpec s formulo, ne boste ga več potrebovali, od zdaj naprej imate samo edinstvene vrednosti.
6. To je to, zdaj pa kolumna А vsebuje samo edinstvene podatke, ki niso v stolpcu В:

Kot lahko vidite, odstranjevanje dvojnikov iz dveh stolpcev v Excelu s pomočjo formul ni tako težko.