Porovnejte dva sloupce a najděte duplikáty v Excelu (úplný průvodce)
Identifikace duplicitních hodnot ve dvou sloupcích v Excelu je běžným úkolem při analýze dat. To pomáhá určit chyby při zadávání dat, duplicitní záznamy nebo pro účely čištění dat. Tento článek vás provede tím, jak efektivně a přesně identifikovat duplikáty nebo shody ve dvou sloupcích.
Porovnejte dva sloupce a najděte duplicitní hodnoty
- Zvýrazněte duplikáty ve dvou sloupcích pomocí podmíněného formátování
- Vyberte a zvýrazněte duplikáty ve dvou sloupcích pomocí výkonného nástroje – Kutools
- Najít a extrahovat duplikáty ve dvou sloupcích se vzorci
- Vyberte duplikáty ve dvou sloupcích s kódem VBA
Porovnejte dva sloupce a najděte duplicitní hodnoty
Chcete-li najít duplicitní hodnoty mezi dvěma sloupci, je k dispozici několik metod v závislosti na vašich požadavcích, jako je zvýraznění duplikátů pro vizuální přehled nebo jejich extrahování pro hloubkovou analýzu. V této části představíme několik rychlých triků pro provedení tohoto úkolu v Excelu.
Zvýrazněte duplikáty ve dvou sloupcích pomocí podmíněného formátování
Zvýraznění duplikátů ve dvou sloupcích v Excelu je účinný způsob, jak identifikovat opakovaná data, zejména v rámci velkých datových sad, kde je ruční kontrola nepraktická. V tomto případě je Podmíněné formátování užitečnou funkcí k vyřešení tohoto úkolu.
Krok 1: Vyberte data ve sloupci, ze kterého chcete zvýraznit duplikáty
V tomto příkladu vyberu A2:A10, viz snímek obrazovky:
Krok 2: Použijte funkci podmíněného formátování
- klikněte Domů > Podmíněné formátování > Nové pravidlo, viz screenshot:
- v Nové pravidlo pro formátování V dialogovém okně proveďte následující operace:
- 2.1 Vyberte Pomocí vzorce určete, které buňky chcete formátovat z Vyberte typ pravidla seznam;
- 2.2 Zadejte následující vzorec do Formátovat hodnoty, kde je tento vzorec pravdivý Textové pole;
=COUNTIF($B$2:$B$10, A2)>0
- Poznámka: Ve výše uvedeném vzorci, B2: B10 představuje seznam dat, se kterými chcete porovnávat, A2 je první buňka sloupce, ze které chcete zvýraznit duplikáty. Tento vzorec zkontroluje, zda se hodnota v buňce A2 nachází kdekoli ve sloupci B. Upravte odkazy na buňky tak, aby vyhovovaly vašim datům.
- 2.3 Poté klikněte Formát .
- Ve vysunutém Formát buněk dialogovém okně zadejte jednu barvu, kterou chcete zvýraznit duplicitní položky. A klikněte OK.
- Když se vrátí Nové pravidlo pro formátování , klepněte na tlačítko OK OK .
Výsledek:
Nyní jsou hodnoty, které jsou duplicitní ve sloupcích A a B, nyní zvýrazněny ve sloupci A, jak je znázorněno na následujícím snímku obrazovky:
- Pravidlo duplicit v Podmíněné formátování is nerozlišuje velká a malá písmena. Apple i apple by tedy byly označeny jako duplikáty.
- Pokud chcete zvýraznit duplikáty ze sloupce B, stačí nejprve vybrat sloupec B a poté použít následující vzorec na Podmíněné formátování:
=COUNTIF($A$2:$A$10, B2)>0
Vyberte a zvýrazněte duplikáty ve dvou sloupcích pomocí výkonného nástroje – Kutools
Někdy možná budete muset duplikáty nejen zvýraznit, ale také vybrat, abyste je zkopírovali a vložili na jiné místo v sešitu. V takových případech Kutools pro ExcelJe Vyberte stejné a různé buňky funkce je ideální volbou. Může identifikovat duplicitní nebo jedinečné hodnoty zvýrazněním a výběrem buněk, které potřebujete.
klikněte Kutools > vybrat > Vyberte stejné a různé buňky, V roce Vyberte stejné a různé buňky V dialogovém okně proveďte následující operace:
- Vyberte zdrojová data a porovnávaná data v souboru Najít hodnoty v a Podle krabice zvlášť;
- vybrat Každý řádek pod Na základě sekce;
- Vybrat Stejné hodnoty z Najít sekce;
- Určete barvu pozadí pro zvýraznění duplicitních hodnot pod Zpracování výsledků sekce;
- Konečně klikněte na tlačítko OK .
Výsledek:
Nyní jsou hodnoty, které jsou duplicitní ve sloupcích A a B, zvýrazněny a vybrány ve sloupci A, připravené ke kopírování a vkládání do libovolných buněk. Viz snímek obrazovky:
- Tato funkce podporuje rozlišování velkých a malých písmen srovnání při tikání Citlivý na velká písmena zaškrtávací políčko Vyberte stejné a různé buňky dialog;
- Pokud chcete vybrat duplikáty ze sloupce B, stačí zaměnit dva vybrané sloupce v Najít hodnoty v a Podle krabice Vyberte stejné a různé buňky dialogové okno;
- Chcete-li použít tuto funkci, prosím stáhnout a nainstalovat Kutools pro Excel jako první.
Najděte a extrahujte duplikáty ve dvou sloupcích pomocí vzorce
Chcete-li najít a extrahovat duplikáty mezi dvěma sloupci, můžete použít vzorec k určení a vytažení duplikátů.
Zkopírujte a vložte následující vzorec do prázdné buňky, kam chcete umístit výsledek, a poté přetáhněte úchyt výplně dolů po sloupci, abyste tento vzorec použili na další buňky.
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, ze které chcete najít duplikáty; B2: B10 představuje seznam dat, se kterými chcete porovnávat.
Výsledek:
Jak vidíte, pokud data ve sloupci A existují ve sloupci B, zobrazí se hodnota; jinak zůstanou buňky prázdné.
Vyberte duplikáty ve dvou sloupcích s kódem VBA
Tato část vás provede kroky k vytvoření kódu VBA, který identifikuje a vybere duplicitní hodnoty mezi dvěma sloupci.
Krok 1: Otevřete editor modulu VBA a zkopírujte kód
- Stisknout Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
- V otevřeném okně klikněte na Vložit > Modul k vytvoření nového prázdného modulu.
- Poté zkopírujte a vložte níže uvedený kód do prázdného modulu.
Kód VBA: Najděte a vyberte duplicitní hodnoty mezi dvěma sloupciSub Compare() 'Update by Extendoffice Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" On Error Resume Next Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub
Krok 2: Spusťte tento kód VBA
- Po vložení tohoto kódu stiskněte F5 klíč ke spuštění tohoto kódu. V prvním poli výzvy vyberte seznam dat, ze kterého chcete vybrat duplikáty. A pak klikněte OK.
- V druhém poli výzvy vyberte seznam dat, se kterým chcete porovnat, a klepněte na tlačítko OK, viz screenshot:
Výsledek:
Nyní jsou duplicitní hodnoty ze sloupců A a B vybrány ve sloupci A, což vám umožňuje vyplnit buňky barvou nebo je zkopírovat a vložit podle potřeby.
- Tento kód VBA je citlivý na velká písmena;
- Pokud chcete vybrat duplikáty ze sloupce B, stačí při výběru rozsahu dat prohodit dva vybrané sloupce.
Porovnejte dva sloupce pro shody řádek po řádku
V Excelu je často nutné porovnávat dva sloupce řádek po řádku pro kontrolu shod, což pomáhá při úkolech, jako je kontrola záznamů nebo analýza trendů dat. Excel má různé způsoby, jak toho dosáhnout, od jednoduchých vzorců po speciální funkce, takže si můžete vybrat ten nejlepší pro potřeby vašich dat. Pojďme se podívat na několik jednoduchých metod, jak tuto práci zvládnout efektivně.
Porovnejte dva sloupce ve stejném řádku se vzorci
Vzorce Excelu nabízejí přímočarý, ale účinný přístup k porovnání dat napříč sloupci. Zde je návod, jak je můžete použít. Předpokládejme, že máte data ve sloupcích A a B. Chcete-li zkontrolovat, zda se data ve dvou sloupcích shodují, mohou vám pomoci následující vzorce:
- Použití operátoru rovná se (=): nejjednodušší způsob porovnání dvou buněk
- Funkce IF: aby bylo vaše srovnání informativnější
- PŘESNÁ Funkce: srovnání rozlišující malá a velká písmena
Použití operátoru Rovná se (=):
Zadejte nebo zkopírujte následující vzorec, stiskněte vstoupit a poté přetáhněte úchyt výplně dolů, abyste získali všechny výsledky. Vrátí TRUE, pokud jsou hodnoty ve stejném řádku sloupců A a B identické, a FALSE, pokud nejsou. Viz snímek obrazovky:
=A2=B2
Funkce IF:
Pokud chcete, aby srovnání bylo informativnější, můžete použít Funkce IF pro zobrazení vlastních zpráv.
Zadejte nebo zkopírujte níže uvedený vzorec, stiskněte vstoupit a poté přetáhněte úchyt výplně dolů, abyste získali všechny výsledky. Vrátí Shoda, když jsou hodnoty stejné, a Žádná shoda, když se liší. Viz snímek obrazovky:
=IF(A2=B2, "Match", "No Match")
PŘESNÁ funkce:
Pokud potřebujete srovnání rozlišující malá a velká písmena, PŘESNÁ funkce je cesta.
Zadejte nebo zkopírujte následující vzorec, stiskněte vstoupit a poté přetáhněte úchyt výplně dolů, abyste získali všechny výsledky. Vrátí Shoda, když se hodnoty přesně shodují, a Bez shody, když se liší. Viz snímek obrazovky:
=IF(EXACT(A2,B2), "Match", "No match")
Vyberte a zvýrazněte zápasy ve stejném řádku pomocí užitečného nástroje - Kutools
Pokud potřebujete vybrat a vystínovat shody mezi dvěma sloupci řádek po řádku namísto získání výsledku v samostatném sloupci, funkce Porovnat buňky Kutools pro Excel bude vynikající volbou. Umožňuje vám rychle vybrat a použít barvu výplně na buňky, které se shodují nebo liší hodnotou v každém řádku.
klikněte Kutools > Porovnat buňky, V roce Porovnat buňky V dialogovém okně proveďte následující operace:
- Vyberte data ze dvou sloupců v Najít hodnoty v a Podle krabice zvlášť;
- vybrat Stejné buňky pod Najít sekce;
- Určete barvu pozadí pro zvýraznění shod pod Zpracování výsledků sekce;
- Konečně klikněte na tlačítko OK .
Výsledek:
Nyní jsou shody ve stejném řádku zvýrazněny a vybrány ve sloupci A, takže je můžete zkopírovat a vložit do libovolných buněk. Viz snímek obrazovky:
- Tato funkce podporuje rozlišování velkých a malých písmen srovnání, pokud zkontrolujete Citlivý na velká písmena možnost v Porovnat buňky dialogové okno;
- Pokud chcete vybrat shody ze sloupce B, stačí prohodit dva vybrané sloupce v Najít hodnoty v a Podle krabice Porovnat buňky dialogové okno;
- Chcete-li použít tuto funkci, prosím stáhnout a nainstalovat Kutools pro Excel jako první.
Porovnejte dva sloupce a zvýrazněte shody ve stejném řádku
Porovnání dvou sloupců a zvýraznění shod ve stejném řádku lze efektivně provádět pomocí podmíněného formátování v Excelu. Zde je návod, jak identifikovat a zvýraznit shody řádků:
Krok 1: Vyberte rozsah dat
Vyberte rozsah dat, ve kterém chcete zvýraznit shodu řádku.
Krok 2: Použijte funkci podmíněného formátování
- klikněte Domů > Podmíněné formátování > Nové pravidlo. V Nové pravidlo pro formátování V dialogovém okně proveďte následující operace:
- 2.1 Vyberte Pomocí vzorce určete, které buňky chcete formátovat z Vyberte typ pravidla seznam;
- 2.2 Zadejte níže uvedený vzorec do Formátovat hodnoty, kde je tento vzorec pravdivý Textové pole;
=$B2=$A2
- 2.3 Poté klikněte Formát .
- Ve vysunutém Formát buněk dialogovém okně zadejte jednu barvu, kterou chcete zvýraznit duplicitní položky. A klikněte OK.
- Když se vrátí Nové pravidlo pro formátování , klepněte na tlačítko OK OK .
Výsledek:
Nyní jsou odpovídající hodnoty ve stejném řádku zvýrazněny najednou, viz snímek obrazovky:
- Vzorec v podmíněném formátování je nerozlišuje velká a malá písmena.
- Pokud chcete zvýraznit buňky s různými hodnotami, použijte následující vzorec:
=$B2<>$A2
Porovnejte dva sloupce a vytáhněte odpovídající data
Když v Excelu pracujete se dvěma sadami dat a potřebujete najít společné položky z jednoho seznamu ve druhém, jsou vyhledávací vzorce vaším řešením pro načtení těchto shod.
Pokud máte v aplikaci Excel seznam ovoce ve sloupci A a údaje o jejich prodeji ve sloupci B, nyní je chcete přiřadit k výběru ovoce ve sloupci D, abyste našli jejich odpovídající prodeje. Jak byste mohli vrátit relativní hodnoty ze sloupce B v Excelu?
Použijte prosím některý z následujících vzorců, které potřebujete, a poté přetažením úchytu výplně dolů použijte tento vzorec na zbývající buňky.
- Všechny verze Excelu:
=VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
- Excel 365 a Excel 2021:
=XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
Výsledek:
Pokud je nalezena shoda, zobrazí se všechny odpovídající hodnoty, jinak se vrátí chyba #N/A, viz snímek obrazovky:
- U výše uvedených vzorců, pokud jsou ovoce ve sloupci D, které nemají shodu ve sloupci A, vrátí chybu. Aby byly tyto chyby srozumitelnější, můžete svůj vzorec zabalit pomocí funkce IFERROR:
- Všechny verze Excelu:
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
- Excel 365 a Excel 2021:
=IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
- Všechny verze Excelu:
- Pro ty, kteří mají rádi pokročilé vyhledávací vzorce, Kutools pro Excel poskytuje působivou sadu pokročilých vyhledávacích vzorců, které posouvají tradiční funkci VLOOKUP do nových výšin a poskytují vám bezkonkurenční přesnost a efektivitu při vašich úlohách správy dat.
Kutools pro Excel se můžete pochlubit sbírkou více než 300 praktických nástrojů navržených pro zvýšení vaší produktivity. Zažijte plný výkon s bezplatnou 30denní zkušební verzí a pozvedněte své tabulky ještě dnes! Získejte to teď!
Související články:
- Najděte a zvýrazněte duplicitní řádky v rozsahu
- Někdy ve vašem datovém rozsahu listu mohou existovat nějaké duplicitní záznamy a nyní chcete vyhledat nebo zvýraznit duplicitní řádky v rozsahu, jak ukazují následující snímky obrazovky. Samozřejmě je můžete najít jeden po druhém kontrolou řádků. To však není dobrá volba, pokud existují stovky řádků. Zde budu hovořit o některých užitečných způsobech, jak se s tímto úkolem vypořádat.
- Zvýrazněte duplicitní hodnoty v různých barvách
- V aplikaci Excel můžeme snadno zvýraznit duplicitní hodnoty ve sloupci s jednou barvou pomocí podmíněného formátování, ale někdy musíme zvýraznit duplicitní hodnoty v různých barvách, abychom rychle a snadno rozpoznali duplikáty, jak ukazuje následující snímek obrazovky. Jak byste mohli vyřešit tento úkol v aplikaci Excel?
- Najít, zvýraznit, filtrovat, počítat, odstranit duplikáty v aplikaci Excel
- V aplikaci Excel dochází k duplicitním datům čas od času, když zaznamenáváme data ručně, kopírujeme data z jiných zdrojů nebo z jiných důvodů. Někdy jsou duplikáty nezbytné a užitečné. Někdy však duplicitní hodnoty vedou k chybám nebo nedorozumění. Zde tento článek představí metody pro rychlou identifikaci, zvýraznění, filtrování, počítání, mazání duplikátů podle vzorců, pravidel podmíněného formátování, doplňků třetích stran atd. V aplikaci Excel.
- Odeberte duplikáty a nahraďte je prázdnými buňkami
- Normálně, když použijete příkaz Odstranit duplikáty v Excelu, odstraní celé duplicitní řádky. Ale někdy chcete, aby prázdné buňky nahradily duplicitní hodnoty, v této situaci nebude příkaz Remove Duplicate fungovat. Tento článek vás provede odstraněním duplikátů a jejich nahrazením prázdnými buňkami v aplikaci Excel.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Obsah
- Porovnejte dva sloupce a najděte duplicitní hodnoty
- S podmíněným formátováním
- S mocným nástrojem – Kutools
- Se vzorci
- S kódem VBA
- Porovnejte dva sloupce pro shody řádek po řádku
- Se vzorci
- S užitečným nástrojem - Kutools
- S podmíněným formátováním
- Porovnejte dva sloupce a vytáhněte odpovídající data
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře