Jak porovnat dva listy Excelu pro duplikáty: 5 rychlých způsobů

Při práci s velkými datovými sadami v Excelu je běžné mít více listů obsahujících podobná data. Porovnání těchto listů pro duplicitní záznamy může být docela problém, zvláště když jsou data rozptýlena v různých sloupcích a řádcích.

Nejběžnější způsoby, jak najít duplikáty ve dvou listech aplikace Excel, jsou:

1. Funkce VLOOKUP, COUNTIF nebo EXACT

2. Podmíněné formátování

3. Power Query

4. Externí nástroje a doplňky

5. Vizuální kontrola duplikátů

Tento článek vás krok za krokem provede těmito pěti metodami k určení a zpracování duplikátů ve více listech. Na konci uvidíte, jak snadné je spravovat duplicitní hodnoty.

Začněme!

Obsah

• Ukázky pracovních listů a dat
• 1. Použití funkcí SVYHLEDAT, COUNTIF nebo EXACT k vyhledání duplikátů
  • A. Jak používáte funkci SVYHLEDAT k vyhledání duplikátů ve dvou listech?
    • Co když zpracováváte různé sešity?
  • B. Jak používáte funkci COUNTIF k nalezení duplikátů v pracovních listech?
  • C. Jak používáte funkci EXACT k nalezení duplikátů v pracovních listech?
• 2. Jak používat podmíněné formátování pro duplicitní řádky
  • Jak používat Správce pravidel podmíněného formátování
• 3. Jak používat Power Query k hledání duplikátů v pracovních listech
• 4. Nástroje a doplňky pro identifikaci duplikátů v pracovních listech
• 5. Jak vizuálně zkontrolovat duplikáty ve dvou listech
• 3 tipy pro přípravu excelových listů
• Jak řešit chyby a nesrovnalosti
• Závěrečné myšlenky

Ukázky pracovních listů a dat

Chcete-li postupovat podle příkladů v tomto článku, vytvořte nový sešit aplikace Excel s těmito daty ve sloupci A prvního listu:

• Jablko

• oranžový

• Hruška

• Jahoda

Vložte tato data do sloupce A druhého listu:

• Hruška

• Jahoda

• Jablko

• Ananas

Vaše pracovní listy vypadají takto:

Jakmile budete mít své listy nastaveny, můžeme nyní projít různými způsoby, jak najít duplikáty ve dvou listech, počínaje funkcemi SVYHLEDAT, COUNTIF a EXACT.

Pojďme!

1. Použití funkcí SVYHLEDAT, COUNTIF nebo EXACT k vyhledání duplikátů

Excel má tři vestavěné funkce, díky kterým je hledání duplikátů hračkou: SVYHLEDAT , COUNTIF a EXACT .

Tyto funkce jsou navrženy tak, aby vám pomohly najít, počítat a porovnávat data v tabulkách, což z nich činí ideální nástroje pro hledání duplicitních záznamů.

Každá funkce slouží jedinečnému účelu a pochopení toho, jak je používat, může výrazně zefektivnit proces analýzy dat.

V následujících částech prozkoumáme, jak používat funkce SVYHLEDAT, COUNTIF a EXACT k efektivní identifikaci duplikátů ve vašich excelových listech.

A. Jak používáte funkci SVYHLEDAT k vyhledání duplikátů ve dvou listech?

VLOOKUP je zkratka pro Vertical Lookup. Tuto funkci použijete k nalezení duplicitních hodnot mezi dvěma sloupci. Toto je syntaxe:

=VLOOKUP(vyhledávací_hodnota, pole_tabulky, číslo indexu_sloupce, [vyhledání_rozsahu])

• lookup_value : Hodnota, kterou chcete vyhledat v prvním sloupci pole table_array.

• table_array : Rozsah buněk obsahujících data, ve kterých chcete vyhledávat.

• col_index_num : Číslo sloupce v poli table_array, ze kterého chcete vrátit hodnotu.

• range_lookup : Volitelné. Je to buď PRAVDA (přibližná shoda) nebo NEPRAVDA (přesná shoda). Výchozí hodnota je TRUE.

Chcete-li použít funkci ve dvou listech v souboru aplikace Excel, musíte vědět, jak odkazovat na samostatný list ve vzorci. Chcete-li to provést, zadejte název listu následovaný vykřičníkem (!) a buňkou nebo rozsahem buněk.

Například to odkazuje na buňky A2 až A5 v listu 2 stejného sešitu: List 2!$A$2:$A$5.

Chcete-li ve vzorové tabulce použít funkci SVYHLEDAT, postupujte takto:

1. Vyberte buňku B2 pro zobrazení prvního výsledku porovnání.

2. Zadejte tento vzorec: =VLOOKUP(A2,List2!$A$2:$A$5, 1, NEPRAVDA).

3. Stiskněte Enter pro zobrazení výsledku porovnání.

4. Vyplňte vzorec a porovnejte hodnoty pro zbytek řádků na prvním listu.

Výsledky budou vypadat jako na obrázku níže:

Na displeji můžete uklidit tak, že se místo chyby zobrazí uživatelsky přívětivá zpráva, když není nalezen duplikát.

Tento vzorec například zobrazí „Ano“ nebo „Ne“ pro nalezené a nenalezené hodnoty:

=IF(ISNA(VLOOKUP(A2, List 2!$A$2:$A$5, 1, FALSE)), “Ne”, “Ano”)

Další příklady této funkce jsou v našem článku na .

Co když zpracováváte různé sešity?

Pokud jsou vaše listy v samostatných sešitech, použití funkce je stejné se dvěma soubory aplikace Excel. Odkazování na druhý list je však trochu složitější.

Musíš:

• Uzavřete název sešitu aplikace Excel do hranatých závorek

• Následujte název listu

• Uzavřete sešit a list do uvozovek.

Pokud jsou například buňky v listu s názvem List2 v sešitu s názvem „WB 2.xlsx“, bude formát vypadat takto:

'[WB 2.xlsx]List2'!$A$2:$A$5

Před zadáním vzorce zavřete druhý sešit. V opačném případě se zobrazí chyba.

B. Jak používáte funkci COUNTIF k nalezení duplikátů v pracovních listech?

Funkce COUNTIF v aplikaci Excel se používá k počítání počtu buněk v zadaném rozsahu, které splňují daná kritéria.

Chcete-li porovnat více listů, můžete spočítat počet buněk v druhém listu, který odpovídá buňce v prvním listu.

Toto je syntaxe funkce:

=COUNTIF(rozsah, kritéria)

• Rozsah : rozsah buněk, které chcete spočítat na základě zadaných kritérií.

• Kritéria : podmínka, která musí být splněna, aby byla buňka započítána.

Chcete-li použít funkci s ukázkovými daty, postupujte takto:

1. Vyberte buňku B2 pro zobrazení prvního výsledku porovnání.

2. Zadejte tento vzorec: =COUNTIF(List2!$A$2:$A$5, A2)

3. Stiskněte Enter pro zobrazení výsledku porovnání.

4. Vyplňte vzorec a porovnejte hodnoty pro zbytek řádků na prvním listu.

Funkce najde jednu shodu pro některé buňky a žádnou pro ostatní. Porovnávací buňka zobrazuje počet. Zde je výsledek z ukázkových dat:

Funkci COUNTIF lze použít pro další užitečné úkoly, jako je .

C. Jak používáte funkci EXACT k nalezení duplikátů v pracovních listech?

Funkci EXACT v aplikaci Excel lze také použít k vyhledání duplikátů ve stejných buňkách ve dvou různých listech aplikace Excel. Syntaxe je:

=PŘESNĚ(text1, text2)

• text1 je první textový řetězec, který chcete porovnat.

• text2 je druhý textový řetězec, který chcete porovnat.

Následuj tyto kroky:

1. Vyberte buňku B2.

2. Zadejte vzorec = PŘESNĚ(A2, ​​List2!A2)

3. Stiskněte Enter pro zobrazení výsledku porovnání. Vzorec vrátí hodnotu TRUE, pokud jsou obě hodnoty totožné, nebo FALSE v opačném případě.

4. Vyplňte vzorec a porovnejte hodnoty pro zbytek řádků na prvním listu.

Všimněte si, že tato metoda nevyhledává duplikáty v rozsahu buněk. Místo toho hledáte pouze shody založené na stejné buňce v jiném listu.

To může být užitečné u uspořádaných dat, kde očekáváte jen několik výjimek.

Funkce VLOOKUP, COUNTIF a EXACT jsou užitečné pro hledání duplikátů, ale Excel je všestranný program a existují další možnosti. V další části se podíváme na to, jak můžete použít podmíněné formátování k identifikaci duplikátů na dvou listech.

2. Jak používat podmíněné formátování pro duplicitní řádky

V této části se dozvíte, jak používat podmíněné formátování k vyhledání a zvýraznění duplicitních řádků ve dvou listech aplikace Excel.

Chcete-li vytvořit pravidlo podmíněného formátování, postupujte takto:

1. Vyberte oblast buněk obsahujících data (v tomto případě A2:A5).

2. Klikněte na kartu „Domů“ na pásu karet aplikace Excel.

3. Klikněte na „Podmíněné formátování“ ve skupině „Styly“.

4. Z rozbalovací nabídky vyberte „Nové pravidlo“.

Dalším úkolem je poskytnout vzorec, který má vaše pravidlo použít. Následuj tyto kroky:

1. V dialogovém okně zvolte „Použít vzorec k určení, které buňky se mají formátovat“.

2. Zadejte následující vzorec: =COUNTIF(List2!$A$2:$A$5, A2) > 0

Nakonec použijete formátování, které upřednostňujete pro duplicitní buňky.

1. Klepnutím na tlačítko Formát otevřete dialogové okno Formát buněk.

2. Vyberte formát, např. vyplňte duplikáty žlutou barvou pozadí.

3. Klepněte na tlačítko OK.

Vaše duplicitní data jsou nyní zvýrazněna žlutě.

Jak používat Správce pravidel podmíněného formátování

Jakmile vytvoříte pravidlo podmíněného formátování, můžete jej spravovat pomocí Správce pravidel podmíněného formátování.

Přístup ke správci:

1. Přejděte na kartu Domů.

2. Klikněte na Podmíněné formátování.

3. Vyberte „Spravovat pravidla“.

Zobrazí se seznam všech pravidel podmíněného formátování aplikovaných na vybraný list. Můžete upravit, odstranit nebo změnit pořadí pravidel výběrem pravidla a kliknutím na příslušná tlačítka.

Chcete-li použít stejné pravidlo na druhý list, postupujte takto:

1. Na druhém listu vyberte rozsah, který chcete porovnat.

2. Přejděte do Správce pravidel podmíněného formátování.

3. Vyberte pravidlo, klikněte na Duplikovat pravidlo a poté klikněte na Upravit pravidlo.

4. Nahraďte „Sheet2“ názvem prvního listu, který chcete porovnat.

Nyní, když jste na oba listy aplikovali pravidlo podmíněného formátování, budou duplikáty zvýrazněny podle vámi zvoleného formátování.

Nezapomeňte upravit rozsah a odkazy na buňky ve vzorcích podle potřeby tak, aby pokryly všechna data, která chcete porovnat.

Podmíněné formátování se může zdát trochu primitivní. Pokud chcete jemnější ovládání, pak může být řešením Power Query! V další části probereme, jak můžete pomocí Power Query najít duplikáty.

3. Jak používat Power Query k hledání duplikátů v pracovních listech

Power Query je nástroj pro transformaci a přípravu dat v aplikaci Microsoft Excel. Identifikace stejných hodnot je pouze jednou z mnoha analytických úloh, které můžete pomocí tohoto nástroje provádět.

Chcete-li tak učinit, měli byste nejprve importovat data ze dvou listů do samostatných tabulek. Na každém listu postupujte takto:

1. Klepněte pravým tlačítkem na oblast buněk.

2. Zvolte „Získat data z tabulky/rozsahu“.

3. Upravte název tabulky na něco vhodného.

Po importu obou listů je prvním úkolem sloučit data:

1. Přejděte na kartu Data.

2. Klikněte na „Získat data“.

3. Vyberte „Kombinovat dotazy“.

4. Zvolte „Sloučit“ a vyberte dvě tabulky.

5. Klikněte na dva klíčové sloupce.

6. Vyberte „Inner“ jako „Druh připojení“ a klikněte na OK.

Otevře se Editor Power Query s kombinovanými daty z obou tabulek v listu Excel. Uvidíte dva sloupce, jeden z každé tabulky. Protože vás zajímají pouze duplicitní hodnoty, můžete druhý sloupec odstranit.

Kliknutím na „Zavřít a načíst“ v editoru Power Query načtete duplikáty do nového listu.

Chcete-li prozkoumat další aspekty této výkonné funkce, postupujte podle příkladů v našem článku na .

Excel má také nástroje a doplňky třetích stran, které přidávají možnost hladce najít duplikáty, takže se na některé z těchto nástrojů podíváme v další části.

4. Nástroje a doplňky pro identifikaci duplikátů v pracovních listech

Externí nástroje a doplňky mohou nabízet pokročilé funkce, které nemusí být dostupné v nativních funkcích Excelu. Tyto nástroje mohou dále zefektivnit proces porovnávání listů pro duplikáty.

Spreadsheet Compare je nástroj společnosti Microsoft, který umožňuje porovnávat dva sešity vedle sebe, zvýraznit rozdíly a snadno identifikovat duplikáty. Můžete si jej stáhnout z webu společnosti Microsoft.

Existuje několik doplňků, které můžete nainstalovat a automatizovat proces hledání duplikátů. Jedním z příkladů je „ Duplicate Remover “. Chcete-li nainstalovat doplněk:

1. Přejděte na kartu Vložit.

2. Klikněte na „Získat doplněk“.

3. Vyhledejte „Duplikovat“.

4. Klikněte na „Přidat“ na nástroji dle vašeho výběru.

5. Jak vizuálně zkontrolovat duplikáty ve dvou listech

Pokud vše ostatní selže, použijte oči! Dialogové okno Uspořádat Windows v Excelu umožňuje zobrazit více listů nebo sešitů vedle sebe.

I když přímo nenachází duplikáty, může vám pomoci vizuálně porovnávat data v pracovních listech nebo sešitech, abyste našli duplikáty. Následuj tyto kroky:

1. Klikněte na kartu „Zobrazit“ na pásu karet aplikace Excel.

2. Klikněte na „Uspořádat vše“ ve skupině „Okno“.

3. Vyberte možnost uspořádání, např. „Vertikální“ nebo „Vodorovně“.

Tím se zobrazí oba listy buď vedle sebe, nebo jeden nad druhým. Nyní můžete ručně porovnat data v každém listu a identifikovat duplikáty.

Musíte procházet data a vizuálně zkontrolovat každou hodnotu, abyste našli shodu.

Všimněte si, že tato metoda není efektivní pro velké datové sady, protože vyžaduje ruční srovnání. Použití jiných metod v tomto článku bude efektivnější pro nalezení duplikátů ve větších datových sadách.

A to je poslední z našich běžných metod pro hledání duplicitních hodnot v excelových listech! V další části vám dáme několik tipů pro přípravu pracovních listů.

3 tipy pro přípravu excelových listů

Než začnete porovnávat více listů, ujistěte se, že máte sloupce a řádky datových sad správně seřazené.

Ujistěte se, že oba mají stejnou strukturu a stejné názvy záhlaví. V případě potřeby můžete změnit uspořádání sloupců v obou listech tak, aby si navzájem odpovídaly.

Zde jsou tři návrhy, jak zajistit přesné srovnání:

1. Uspořádejte data v obou listech ve stejném pořadí. To usnadňuje efektivní práci funkcí Excelu.

2. Normalizujte svá data pomocí konzistentního formátování, velkých písmen a datových typů. Předejdete tak neshodám zadaným v důsledku drobných rozdílů.

3. Odstraňte zbytečné prázdné řádky nebo sloupce, protože mohou narušovat proces porovnávání.

Spočítáním jedinečných hodnot můžete posoudit, kolik duplicit máte v sadě dat. Toto video vás provede několika způsoby:

Jak řešit chyby a nesrovnalosti

Nekonzistence ve vašich datech mohou ovlivnit proces porovnávání. Zde jsou čtyři tipy pro řešení nesrovnalostí:

1. Zkontrolujte nesrovnalosti v datových typech, jako je kombinace textu a číselných hodnot ve stejném sloupci.

2. Zajistěte konzistentní formátování pro data, čísla a další datové typy.

3. Zkontrolujte, zda vaše data neobsahují chybějící nebo nesprávné položky, a v případě potřeby je aktualizujte.

4. Standardizujte zkratky nebo nekonzistentní konvence pojmenování ve svých souborech dat.

Závěrečné myšlenky

Nalezení duplikátů ve dvou excelových listech je základním úkolem pro správu a analýzu dat, která zajišťuje integritu a přesnost dat. Excel nabízí několik technik k identifikaci duplikátů, z nichž každá má své výhody a omezení.

Výběr metody závisí na potřebách uživatele, velikosti a složitosti datové sady a požadovaném výsledku. Pro menší datové sady a jednoduchá srovnání může být dostačující použití , COUNTIF nebo podmíněného formátování.

Pro větší datové sady nebo složitější transformace dat je Power Query výkonným a flexibilním nástrojem, který zvládne širokou škálu úloh přípravy dat, včetně hledání duplikátů.

Abych to zakončil, porovnávání duplikátů je super šikovná dovednost, kterou musíte mít ve svém panelu nástrojů. Pomocí triků v tomto článku můžete odhalit ty otravné duplikáty a udržet vaše data čistá.

Věřte nám, jak se v tom budete zlepšovat, projdete svými datovými úkoly jako dech a uděláte dojem na všechny kolem vás!