Ako porovnať dva stĺpce v Exceli a odstrániť duplikáty (zvýrazniť, zafarbiť, presunúť)

Prečítanie tohto článku vám zaberie asi 10 minút. V nasledujúcich 5 minútach môžete jednoducho porovnať dva stĺpce v Exceli a zistiť, či sa v nich nenachádzajú duplikáty, odstrániť ich alebo farebne zvýrazniť. Takže, prišiel čas!

Excel je veľmi výkonná a skutočne skvelá aplikácia na vytváranie a spracovanie veľkého množstva údajov. Ak máte niekoľko zošitov s údajmi (alebo len jednu veľkú tabuľku), pravdepodobne budete chcieť porovnať 2 stĺpce, nájsť duplicitné hodnoty a potom s nimi niečo urobiť, napríklad odstrániť, zvýrazniť alebo vymazať obsah. Stĺpce môžu byť v tej istej tabuľke, môžu susediť alebo nie, môžu byť umiestnené na 2 rôznych listoch alebo dokonca v rôznych knihách.

Predstavte si, že máme 2 stĺpce s menami ľudí – 5 mien v stĺpci A a 3 mená v stĺpci B. Musíte porovnať mená v týchto dvoch stĺpcoch a nájsť duplikáty. Ako ste pochopili, ide o fiktívne údaje, brané len ako príklad. V reálnych tabuľkách máme do činenia s tisíckami či dokonca desiatkami tisíc záznamov.

Možnosť A: oba stĺpce sú na rovnakom hárku. Napríklad stĺpec A a stĺpec B.

Možnosť B: Stĺpce sú na rôznych hárkoch. Napríklad stĺpec A na liste Sheet2 a stĺpec A na liste Sheet3.

Excel 2013, 2010 a 2007 majú vstavaný nástroj Odstrániť duplikáty (Odstrániť duplikáty), ale v tejto situácii je bezmocný, pretože nemôže porovnávať údaje v 2 stĺpcoch. Okrem toho môže odstrániť iba duplikáty. Neexistujú žiadne ďalšie možnosti, ako je zvýraznenie alebo zmena farieb. A bod!

Ďalej vám ukážem možné spôsoby porovnania dvoch stĺpcov v Exceli, čo vám umožní nájsť a odstrániť duplicitné záznamy.

• Porovnajte 2 stĺpce a vyhľadajte duplikáty pomocou vzorcov
  
  • Možnosť A: oba stĺpce sú na rovnakom hárku
  • Možnosť B: Stĺpce sú na rôznych hárkoch alebo v rôznych zošitoch
• Spracovanie nájdených duplikátov
  
  • Zobraziť iba duplicitné riadky v stĺpci A
  • Zmeňte farbu alebo zvýraznite nájdené duplikáty
  • Odstráňte duplikáty z prvého stĺpca

Porovnajte 2 stĺpce v Exceli a nájdite duplicitné položky pomocou vzorcov

Možnosť A: oba stĺpce sú na rovnakom hárku

1. Do prvej prázdnej bunky (v našom príklade je to bunka C1) napíšeme nasledujúci vzorec:

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

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

   

   V našom vzorci A1 toto je prvá bunka prvého stĺpca, ktorý budeme porovnávať. 1 $ B$ и 10000 $ B$ sú to adresy prvej a poslednej bunky druhého stĺpca, s ktorými vykonáme porovnanie. Všimnite si absolútne odkazy – písmenám stĺpcov a číslam riadkov predchádza znak dolára ($). Používam absolútne odkazy, aby pri kopírovaní vzorcov zostali adresy buniek rovnaké.

   Ak chcete nájsť duplikáty v stĺpci B, zmeňte odkazy tak, aby vzorec vyzeral takto:

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

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

   Namiesto toho "Iba"A"duplikát» Môžete napísať svoje vlastné štítky, napríklad «Nenašli sa"A"nájdených“, alebo ponechajte iba “duplikát' a namiesto druhej hodnoty zadajte medzeru. V druhom prípade bunky, pre ktoré sa nenájdu žiadne duplikáty, zostanú prázdne a domnievam sa, že táto reprezentácia údajov je najvhodnejšia na ďalšiu analýzu.

2. Teraz skopírujeme náš vzorec do všetkých buniek v stĺpci C, až po spodný riadok, ktorý obsahuje údaje v stĺpci A. Ak to chcete urobiť, presuňte ukazovateľ myši do pravého dolného rohu bunky C1, ukazovateľ bude mať podobu čierneho nitkového kríža, ako je znázornené na obrázku nižšie:Kliknite a podržte ľavé tlačidlo myši a potiahnite okraj rámčeka nadol, pričom zvýraznite všetky bunky, do ktorých chcete vložiť vzorec. Keď sú vybraté všetky požadované bunky, uvoľnite tlačidlo myši:

   

Tip: Vo veľkých tabuľkách bude kopírovanie vzorca rýchlejšie, ak použijete klávesové skratky. Zvýraznite bunku C1 a stlačte Ctrl + C (ak chcete vzorec skopírovať do schránky), potom kliknite Ctrl + Shift + End (na výber všetkých neprázdnych buniek v stĺpci C) a nakoniec stlačte Ctrl + V (pre vloženie vzorca do všetkých vybratých buniek).

3. Skvelé, teraz sú všetky duplicitné hodnoty označené ako „duplikát,:

Možnosť B: dva stĺpce sú na rôznych hárkoch (v rôznych zošitoch)

1. V prvej bunke prvého prázdneho stĺpca na pracovnom hárku Sheet2 (v našom prípade je to stĺpec B) zadajte nasledujúci vzorec:

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

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

   Tu Sheet3 je názov listu, na ktorom sa nachádza 2. stĺpec, a $A$1: $A$10000 sú adresy buniek od 1. do poslednej v tomto 2. stĺpci.

2. Skopírujte vzorec do všetkých buniek v stĺpci B (rovnaké ako možnosť A).
3. Dostaneme tento výsledok:

Spracovanie nájdených duplikátov

Skvelé, v prvom stĺpci sme našli položky, ktoré sa nachádzajú aj v druhom stĺpci. Teraz s nimi musíme niečo urobiť. Manuálne prechádzanie všetkých duplicitných záznamov v tabuľke je dosť neefektívne a zaberá príliš veľa času. Sú lepšie spôsoby.

Zobraziť iba duplicitné riadky v stĺpci A

Ak vaše stĺpce nemajú hlavičky, musíte ich pridať. Ak to chcete urobiť, umiestnite kurzor na číslo, ktoré predstavuje prvý riadok, a zmení sa na čiernu šípku, ako je znázornené na obrázku nižšie:

Kliknite pravým tlačidlom myši a vyberte z kontextovej ponuky vloženie (Vložiť):

Pomenujte stĺpce, napríklad „Meno"A"duplikovať?» Potom otvorte kartu dátum (Údaje) a stlačte filter (Filter):

Potom kliknite na malú sivú šípku vedľa „duplikovať?« na otvorenie ponuky filtra; zrušte začiarknutie všetkých položiek v tomto zozname okrem duplikáta stlačte tlačidlo OK.

To je všetko, teraz vidíte len tie prvky stĺpca А, ktoré sú v stĺpci duplikované В. V našej tréningovej tabuľke sú len dve takéto bunky, ale ako viete, v praxi ich bude oveľa viac.

Na opätovné zobrazenie všetkých riadkov stĺpca А, kliknite na symbol filtra v stĺpci В, ktorý teraz vyzerá ako lievik s malou šípkou, a vyberte Vybrať všetko (Vybrať všetko). Alebo môžete urobiť to isté cez pás s nástrojmi kliknutím dátum (Údaje) > Vybrať a filtrovať (Zoradiť a filtrovať) > Vymazať (Vymazať), ako je znázornené na obrázku nižšie:

Zmeňte farbu alebo zvýraznite nájdené duplikáty

Ak poznámky „duplikát“ nestačí na vaše účely a chcete označiť duplicitné bunky inou farbou písma, farbou výplne alebo inou metódou…

V takom prípade filtrujte duplikáty, ako je uvedené vyššie, vyberte všetky filtrované bunky a kliknite Ctrl + 1na otvorenie dialógového okna formátovanie buniek (formát bunky). Ako príklad zmeňme farbu výplne buniek v riadkoch s duplikátmi na jasne žltú. Samozrejme, môžete zmeniť farbu výplne pomocou nástroja Vyplniť (Farba výplne). Domov (Domov), ale dialógové okno výhodou formátovanie buniek (Formát bunky) v tom, že môžete súčasne nakonfigurovať všetky možnosti formátovania.

Teraz vám určite neuniknú žiadne bunky s duplikátmi:

Odstránenie duplicitných hodnôt z prvého stĺpca

Filtrujte tabuľku tak, aby sa zobrazili iba bunky s duplicitnými hodnotami, a vyberte tieto bunky.

Ak sú 2 porovnávané stĺpce na rôznych hárkoch, to znamená, že v rôznych tabuľkách kliknite pravým tlačidlom myši na vybraný rozsah a vyberte Odstrániť riadok (Odstrániť riadok):

lis OKkeď vás Excel požiada, aby ste potvrdili, že naozaj chcete odstrániť celý riadok hárka, a potom vymažte filter. Ako vidíte, zostávajú iba riadky s jedinečnými hodnotami:

Ak sú 2 stĺpce na rovnakom hárku, blízko seba (susedia) alebo nie blízko seba (nesusedia), potom bude proces odstraňovania duplikátov trochu komplikovanejší. Nemôžeme odstrániť celý riadok s duplicitnými hodnotami, pretože tým odstránime bunky aj z druhého stĺpca. Takže ponechať v stĺpci iba jedinečné položky А, to urobiť:

1. Filtrujte tabuľku, aby sa zobrazili iba duplicitné hodnoty, a vyberte tieto bunky. Kliknite na ne pravým tlačidlom myši a vyberte z kontextového menu Prehľadný obsah (jasný obsah).
2. Vyčistite filter.
3. Vyberte všetky bunky v stĺpci А, počnúc bunkou A1 až nadol s údajmi.
4. Kliknite na tlačidlo dátum (Údaje) a stlačte Zoradiť od A po Z (Zoradiť od A po Z). V dialógovom okne, ktoré sa otvorí, vyberte Pokračujte v aktuálnom výbere (Zoradiť v rámci zadaného výberu) a kliknite na tlačidlo čierna (Triedenie):
5. Vymažte stĺpec so vzorcom, už ho nebudete potrebovať, odteraz máte iba jedinečné hodnoty.
6. To je všetko, teraz stĺpec А obsahuje iba jedinečné údaje, ktoré nie sú v stĺpci В:

Ako vidíte, odstránenie duplikátov z dvoch stĺpcov v programe Excel pomocou vzorcov nie je také ťažké.