Obsah
Přečtení tohoto článku vám zabere asi 10 minut. V následujících 5 minutách můžete v Excelu snadno porovnat dva sloupce a zjistit, zda v nich nejsou duplicity, odstranit je nebo barevně zvýraznit. Takže nadešel čas!
Excel je velmi výkonná a opravdu skvělá aplikace pro vytváření a zpracování velkého množství dat. Pokud máte několik sešitů s daty (nebo jen jednu obrovskou tabulku), pravděpodobně budete chtít porovnat 2 sloupce, najít duplicitní hodnoty a pak s nimi něco udělat, například odstranit, zvýraznit nebo vymazat obsah. Sloupce mohou být ve stejné tabulce, sousedí nebo nesousedí, mohou být umístěny na 2 různých listech nebo dokonce v různých knihách.
Představte si, že máme 2 sloupce se jmény lidí – 5 jmen na sloupec A a 3 jména ve sloupci B. Musíte porovnat názvy v těchto dvou sloupcích a najít duplikáty. Jak jste pochopili, jedná se o fiktivní údaje, které jsou brány pouze jako příklad. V reálných tabulkách máme co do činění s tisíci nebo dokonce desetitisíci záznamů.
Varianta A: oba sloupce jsou na stejném listu. Například sloupec A a sloupce B.
Varianta B: Sloupce jsou na různých listech. Například sloupec A na listu Sheet2 a sloupce A na listu Sheet3.
Excel 2013, 2010 a 2007 mají vestavěný nástroj Odebrat duplikáty (Remove Duplicates), ale v této situaci je bezmocný, protože nemůže porovnávat data ve 2 sloupcích. Kromě toho může odstranit pouze duplikáty. Nejsou žádné další možnosti, jako je zvýraznění nebo změna barev. A bod!
Dále vám ukážu možné způsoby porovnání dvou sloupců v Excelu, které vám umožní najít a odstranit duplicitní záznamy.
Porovnejte 2 sloupce v Excelu a najděte duplicitní položky pomocí vzorců
Možnost A: oba sloupce jsou na stejném listu
- Do první prázdné buňky (v našem příkladu je to buňka C1) napíšeme následující vzorec:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
V našem vzorci A1 toto je první buňka prvního sloupce, který budeme porovnávat. $ B $ 1 и $ B $ 10000 to jsou adresy první a poslední buňky druhého sloupce, se kterými provedeme porovnání. Všimněte si absolutních odkazů – písmenům sloupců a číslům řádků předchází znak dolaru ($). Používám absolutní odkazy, aby adresy buněk při kopírování vzorců zůstaly stejné.
Pokud chcete najít duplikáty ve sloupci B, změňte odkazy tak, aby vzorec vypadal takto:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Namísto "Pouze"A"Duplikát» Můžete napsat vlastní štítky, například «Nenašli"A"Nalezeno“, nebo nechat jen “Duplikát' a místo druhé hodnoty zadejte mezeru. V druhém případě buňky, pro které nebyly nalezeny žádné duplikáty, zůstanou prázdné a domnívám se, že tato reprezentace dat je pro další analýzu nejvhodnější.
- Nyní zkopírujeme náš vzorec do všech buněk ve sloupci C, úplně dolů na spodní řádek, který obsahuje data ve sloupci A. Chcete-li to provést, přesuňte ukazatel myši do pravého dolního rohu buňky C1, ukazatel bude mít podobu černého zaměřovacího kříže, jak je znázorněno na obrázku níže:Klikněte a podržte levé tlačítko myši a přetáhněte okraj rámečku dolů, zvýrazněte všechny buňky, kam chcete vzorec vložit. Když jsou vybrány všechny požadované buňky, uvolněte tlačítko myši:
Klikněte a podržte levé tlačítko myši a přetáhněte okraj rámečku dolů, zvýrazněte všechny buňky, kam chcete vzorec vložit. Když jsou vybrány všechny požadované buňky, uvolněte tlačítko myši:
Tip: Ve velkých tabulkách bude kopírování vzorce rychlejší, pokud použijete klávesové zkratky. Zvýrazněte buňku C1 a stiskněte Ctrl + C (chcete-li vzorec zkopírovat do schránky), klepněte na Ctrl + Shift + Konec (pro výběr všech neprázdných buněk ve sloupci C) a nakonec stiskněte Ctrl + V (pro vložení vzorce do všech vybraných buněk).
- Skvělé, nyní jsou všechny duplicitní hodnoty označeny jako „Duplikát„:
Možnost B: dva sloupce jsou na různých listech (v různých sešitech)
- V první buňce prvního prázdného sloupce na listu Sheet2 (v našem případě se jedná o sloupec B) zadejte následující vzorec:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Zde Sheet3 je název listu, na kterém se nachází 2. sloupec, a $ A $ 1: $ A $ 10000 jsou adresy buněk od 1. do poslední v tomto 2. sloupci.
- Zkopírujte vzorec do všech buněk ve sloupci B (stejné jako možnost A).
- Dostaneme tento výsledek:
Zpracování nalezených duplikátů
Skvělé, v prvním sloupci jsme našli položky, které jsou přítomny i ve sloupci druhém. Teď s nimi musíme něco udělat. Ruční procházení všech duplicitních záznamů v tabulce je značně neefektivní a zabere příliš mnoho času. Jsou lepší způsoby.
Zobrazit pouze duplicitní řádky ve sloupci A
Pokud vaše sloupce nemají záhlaví, musíte je přidat. Chcete-li to provést, umístěte kurzor na číslo, které představuje první řádek, a změní se na černou šipku, jak je znázorněno na obrázku níže:
Klikněte pravým tlačítkem a vyberte z kontextové nabídky Vložení (Vložit):
Pojmenujte sloupce, například „Příjmení"A"Duplikát?» Poté kartu otevřete Data (Data) a stiskněte Filtr (Filtr):
Poté klikněte na malou šedou šipku vedle „Duplikát?« pro otevření nabídky filtru; zrušte zaškrtnutí všech položek v tomto seznamu kromě Duplikát, a stiskněte tlačítko OK.
To je vše, nyní vidíte pouze ty prvky sloupce А, které jsou ve sloupci duplikovány В. V naší tréninkové tabulce jsou pouze dvě takové buňky, ale jak víte, v praxi jich bude mnohem více.
Chcete-li znovu zobrazit všechny řádky sloupce А, klikněte na symbol filtru ve sloupci В, který nyní vypadá jako trychtýř s malou šipkou, a vyberte Vybrat vše (Vybrat vše). Nebo můžete totéž udělat kliknutím na pás karet Data (Data) > Vybrat a filtrovat (Třídit a filtrovat) > Zrušit (Vymazat), jak je znázorněno na níže uvedeném snímku obrazovky:
Změňte barvu nebo zvýrazněte nalezené duplikáty
Pokud poznámky „Duplikát“ pro vaše účely nestačí a chcete označit duplicitní buňky jinou barvou písma, barvou výplně nebo jinou metodou…
V tomto případě filtrujte duplikáty, jak je uvedeno výše, vyberte všechny filtrované buňky a klikněte Ctrl + 1pro otevření dialogu Formát buněk (formát buňky). Jako příklad změňme barvu výplně buněk v řádcích s duplikáty na jasně žlutou. Pomocí nástroje můžete samozřejmě změnit barvu výplně Vyplnit kartu (Barva výplně). Domů (Home), ale dialogové okno výhodou Formát buněk (Formát buňky) v tom, že můžete konfigurovat všechny možnosti formátování současně.
Nyní vám určitě neuniknou žádné buňky s duplikáty:
Odstranění duplicitních hodnot z prvního sloupce
Filtrujte tabulku tak, aby byly zobrazeny pouze buňky s duplicitními hodnotami, a vyberte tyto buňky.
Pokud jsou 2 sloupce, které porovnáváte, na různých listech, to znamená, že v různých tabulkách klikněte pravým tlačítkem na vybraný rozsah a vyberte Odstranit řádek (Odstranit řádek):
Stisknout OKkdyž vás Excel požádá o potvrzení, že opravdu chcete odstranit celý řádek listu, a poté zrušte filtr. Jak vidíte, zůstávají pouze řádky s jedinečnými hodnotami:
Pokud jsou 2 sloupce na stejném listu, blízko u sebe (sousedí) nebo ne blízko u sebe (ne sousedí), pak bude proces odstraňování duplikátů trochu složitější. Nemůžeme odstranit celý řádek s duplicitními hodnotami, protože tím odstraníme i buňky z druhého sloupce. Chcete-li tedy ve sloupci ponechat pouze jedinečné položky А, Udělej tohle:
- Filtrujte tabulku tak, aby zobrazovala pouze duplicitní hodnoty, a vyberte tyto buňky. Klikněte na ně pravým tlačítkem a vyberte z kontextové nabídky Přehledný obsah (jasný obsah).
- Vyčistěte filtr.
- Vyberte všechny buňky ve sloupci А, počínaje buňkou A1 až na dno obsahující data.
- Klepněte na tlačítko Data (Data) a stiskněte Třídit od A do Z. (Seřadit od A do Z). V dialogovém okně, které se otevře, vyberte Pokračujte v aktuálním výběru (Seřadit v rámci zadaného výběru) a klikněte na tlačítko Černá (Řazení):
- Sloupec se vzorcem smažte, už ho nebudete potřebovat, odteď máte jen unikátní hodnoty.
- To je vše, nyní sloupec А obsahuje pouze jedinečná data, která nejsou ve sloupci В:
Jak vidíte, odstranění duplikátů ze dvou sloupců v Excelu pomocí vzorců není tak obtížné.