Přejít k hlavnímu obsahu

Porovnejte dva sloupce a najděte duplikáty v Excelu (úplný průvodce)


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í

  1. klikněte Domů > Podmíněné formátování > Nové pravidlo, viz screenshot:
  2. 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 .
  3. Ve vysunutém Formát buněk dialogovém okně zadejte jednu barvu, kterou chcete zvýraznit duplicitní položky. A klikněte OK.
  4. 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:

Tip:
  • 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.

Poznámka: Pokud chcete použít toto Vyberte stejné a různé buňky funkce, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

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:

  1. Vyberte zdrojová data a porovnávaná data v souboru Najít hodnoty v a Podle krabice zvlášť;
  2. vybrat Každý řádek pod Na základě sekce;
  3. Vybrat Stejné hodnoty z Najít sekce;
  4. Určete barvu pozadí pro zvýraznění duplicitních hodnot pod Zpracování výsledků sekce;
  5. 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:

Tip:
  • 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é.

Tip: Tento vzorec je necitlivé na případy.

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

  1. Stisknout Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
  2. V otevřeném okně klikněte na Vložit > Modul k vytvoření nového prázdného modulu.
  3. 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 sloupci
    Sub 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

  1. 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.
  2. 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.

Tip:
  • 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:

Tip: Tyto vzorce jsou univerzální a lze je použít nejen pro text, ale také pro čísla, data a časy.
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")
Tip: "Shoda", "Žádná shoda" můžete podle potřeby změnit na jiné výrazy.

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")   
Tip: "Shoda", "Žádná shoda" můžete podle potřeby změnit na jiné výrazy.


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.

Poznámka: Pokud chcete použít toto Porovnat buňky funkce, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

klikněte Kutools > Porovnat buňky, V roce Porovnat buňky V dialogovém okně proveďte následující operace:

  1. Vyberte data ze dvou sloupců v Najít hodnoty v a Podle krabice zvlášť;
  2. vybrat Stejné buňky pod Najít sekce;
  3. Určete barvu pozadí pro zvýraznění shod pod Zpracování výsledků sekce;
  4. 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:

Tip:
  • 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í

  1. 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 .
  2. Ve vysunutém Formát buněk dialogovém okně zadejte jednu barvu, kterou chcete zvýraznit duplicitní položky. A klikněte OK.
  3. 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:

Tip:
  • 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:

Tip:
  • 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")
  • 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.