Při hledání duplicitních buněk pouze v jednom sloupci nebo seznamu můžeme použít COUNTIF funkce pro rychlé vyhledání a snadné spočítání duplicitních dat.
1. Vyberte prázdnou buňku vedle sloupce, ve kterém najdete duplikáty.

2. Zadejte vzorec = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, a stiskněte tlačítko vstoupit klíč.

3. Přetáhněte úchyt automatického vyplňování buňky vzorce a zkopírujte tento vzorec do dalších buněk.

Poznámky: Ve vzorci = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1,
(1) $ C $ 3: $ C $ 12 je sloupec nebo seznam, ve kterém najdete duplicitní hodnoty. Protože sloupec je statický, když kopírujeme vzorec do jiných buněk, je to obecně absolutní odkaz s „$“.
(2) C3 je první buňka zadaného sloupce. Je to relativní odkaz, protože potřebujeme, aby se automaticky změnil, když zkopírujeme vzorec do jiných buněk.
(3) Tento vzorec se vrátí TRUE or FALÁZE. TRUE znamená, že odpovídající hodnota je duplikát, zatímco NEPRAVDIVÉ označuje, že odpovídající hodnota je ve sloupci jedinečná.
(4) Někdy nemusí být PRAVDA nebo NEPRAVDA pochopeny intuitivně. Můžeme kombinovat původní vzorec a funkci IF pro návrat Duplikáty přímo.
= IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Duplicates", "")

V některých případech musíte porovnat dva sloupce a zjistit duplicitní hodnoty. Například máte dva seznamy jmen a chcete zjistit duplicitní jména ve druhém seznamu ve srovnání s prvním. můžete použít funkci VLOOKUP a IFERROR, abyste to snadno zvládli.

1. Vyberte prázdnou buňku vedle seznamu druhých jmen.

2. Zadejte vzorec = IFERROR (VLOOKUP (D3, $ B $ 3: $ B $ 18,1,0), ""), a stiskněte tlačítko vstoupit klíč.

3. Přetáhněte úchyt automatického vyplňování buňky vzorce a zkopírujte vzorec do dalších buněk podle potřeby.

Poznámky: Ve výše uvedeném vzorci
(1) D3 je první buňka v seznamu druhých jmen. Když kopírujeme vzorec do jiných buněk, musí se odkaz automaticky změnit, ve výsledku je relativní.
(2) $ B $ 3: $ B $ 18 je křestní jméno. Je to absolutní odkaz, protože rozsah musíme zachovat statický, když kopírujeme vzorec do jiných buněk.
(3) Pokud je název duplicitní se jmény v prvním seznamu, vzorec vrátí název; jinak se vrátí prázdné.
(4) Můžete také použít vzorec = IF (COUNTIF ($ B $ 3: $ B $ 18, D3)> 0, "Duplicates", "") zjistit duplicitní jména ve druhém seznamu ve srovnání s prvním seznamem. Tento vzorec se vrátí „Duplikáty“ pokud je odpovídající název duplikát.

(5) Pokud potřebujete najít duplikáty ve dvou sloupcích napříč dvěma listy, stačí před odkaz srovnávaného sloupce přidat název listu. V našem příkladu stačí ve vzorci změnit $ B $ 3: $ B $ 18 na List1! $ B $ 3: $ B $ 18.

Výše uvedené vzorce neodpovídají velikosti písmen při hledání duplikátů, tvrdí, že „jablko“ je duplikát s „APPLE“. Zde můžete použít maticový vzorec a najít duplicitní hodnoty v jednom sloupci se shodnými případy.

1. Vyberte prázdnou buňku vedle sloupce, ve kterém najdete duplikáty.

2. Zadejte vzorec pole = IF (SUMA ((- PŘESNÁ ($ C $ 3: $ C $ 12, C3))) <= 1, "", "duplikát"), a stiskněte tlačítko Ctrl + směna + vstoupit klíče.

3. Přetáhněte úchyt automatického vyplňování buňky vzorce a zkopírujte vzorec pole do dalších buněk.

Poznámky: Ve výše uvedeném vzorci pole
(1) $ C $ 3: $ C $ 12 je sloupec, kde potřebujete najít duplicitní hodnoty. Odkaz je absolutní, protože odkaz je statický při kopírování vzorce pole do jiných buněk.
(2) C4 je první buňka ve sloupci. Odkaz je relativní, protože odkaz se musí automaticky změnit při kopírování vzorce pole do jiných buněk.
(3) Pokud je odpovídající buňka duplikát, vzorec pole vrátí „Duplikovat“, jinak se vrátí prázdný.

Můžete použít Podmíněné formátování funkce pro rychlé zvýraznění duplicitních buněk ve sloupci nebo oblasti.

1. Vyberte sloupec, kde zvýrazníte duplicitní buňky.

2, klikněte Domů > Podmíněné formátování > Zvýrazněte pravidla buněk > Duplicitní hodnoty. Viz snímek obrazovky níže:

3. Ve vyskakovacím dialogu Duplikovat hodnoty vyberte Duplikát z prvního rozevíracího seznamu vyberte scénář zvýraznění z druhého rozevíracího seznamu a klikněte na OK .

Poznámky: Pokud přednastavené scénáře zvýraznění nemohou splnit vaše potřeby, můžete vybrat Vlastní formát z druhého rozevíracího seznamu a poté ve vyskakovacím dialogovém okně Formát buněk vyberte podle potřeby barvu zvýraznění, písmo a ohraničení buněk.

Pak uvidíte, že všechny duplicitní buňky jsou zvýrazněny ve vybraném sloupci, jak je uvedeno níže.

Poznámky:  
(1) Po zvýraznění duplicitních buněk můžeme tyto duplikáty snadno odfiltrovat. (Kliknutím zobrazíte postup)
(2) Po zvýraznění duplicitních buněk můžeme duplikáty také hromadně odstranit. (Kliknutím zobrazíte postup)

Někteří uživatelé dávají přednost zvýraznění řádků na základě duplicitních buněk v určitém sloupci. V této situaci můžeme vytvořit vlastní pravidlo podmíněného formátování, které to udělá.

1. Vyberte rozsah (kromě řádku záhlaví), který zvýrazníte na základě duplicitních buněk.

2, klikněte Domů > Podmíněné formátování > Nové pravidlo.

3. V dialogovém okně Nové pravidlo formátování
(1) Klepnutím vyberte Pomocí vzorce určete, které buňky chcete formátovat volba;
(2) Zadejte vzorec = COUNTIF ($ C $ 3: $ C $ 12, $ C3)> 1 v Formátovat hodnoty, kde je tento vzorec pravdivý krabice;
Tipy: Ve vzorci $ C $ 3: $ C $ 12 je sloupec obsahující duplicitní buňky a $ C3 je první buňka ve sloupci.
(3) Klikněte na Formát .

4. V dialogovém okně Formátovat buňky podle potřeby určete barvu výplně, písmo, ohraničení buněk a klikněte na OK postupně uložte nastavení.

Zatím jsou ve vybraném rozsahu řádky zvýrazněny na základě duplicitních buněk v zadaném sloupci. Viz screenshot:

Chcete-li zvýraznit duplicitní řádky v určitém rozsahu, můžete použít Podmíněné formátování funkce, jak toho dosáhnout.

1. Vyberte rozsah kromě řádku záhlaví.

2, klikněte Domů > Podmíněné formátování > Nové pravidlo.

3. V dialogovém okně Nové pravidlo formátování:
(1) Klepnutím vyberte Pomocí vzorce určete, které buňky chcete formátovat volba;
(2) V Formátovat hodnoty, kde je tento vzorec pravdivý do pole zadejte vzorec =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Klikněte na Formát .

Poznámky: Ve vzorci =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $ B $ 3: $ B $ 12 je první sloupec v rozsahu a $ B3 je první buňka v tomto sloupci;
(2) $ C $ 3: $ C $ 12 je druhý sloupec v rozsahu a $ C3 je první buňka ve sloupci;
(3) $ D $ 3: $ D $ 12 je třetí sloupec v rozsahu a $ D3 je první buňka ve sloupci;
(4) Pokud je ve vašem rozsahu více sloupců, můžete do vzorce přidat odkazy na sloupce a jejich první buňky postupně.

4. V dialogovém okně Formátovat buňky podle potřeby určete barvu zvýraznění, písmo, ohraničení buněk atd. A poté klikněte OK postupně uložte nastavení.

Zatím jsou duplikované řádky identifikovány a zvýrazněny ve vybraném rozsahu. Viz screenshot:

Možná jste si všimli, že všechny duplikáty jsou identifikovány nebo zvýrazněny výše uvedenými metodami. Někdy chcete pouze zjistit, které položky jsou duplikovány, s výjimkou prvních instancí. Můžete to také udělat pomocí Podmíněné formátování funkce a jiný vzorec.

1. Vyberte sloupec s hlavičkou.
Tipy: Pokud potřebujete zvýraznit řádky založené na duplikátech v jednom sloupci kromě prvních instancí, vyberte rozsah bez řádku záhlaví.

2, klikněte Domů > Podmíněné formátování > Nové pravidlo.

3. V dialogovém okně Nové pravidlo formátování:
(1) Kliknutím zvýrazněte ikonu Pomocí vzorce určete, které buňky chcete formátovat volba;
(2) V Formátovat hodnoty, kde je tento vzorec pravdivý do pole zadejte vzorec = COUNTIF ($ C $ 3: $ C3, C3)> 1;
Tipy: Chcete-li zvýraznit řádky založené na duplikátech v jednom sloupci, zadejte vzorec = COUNTIF ($ C $ 3: $ C3, $ C3)> 1.
(3) Klikněte na Formát .

4. Ve vyskakovacím dialogovém okně Formátovat buňky podle potřeby určete barvu zvýraznění, písmo, ohraničení buněk atd. A poté klikněte OK uložte nastavení.

Pak uvidíte duplicitní buňky, kromě zvýraznění prvních instancí ve vybraném sloupci (nebo řádků založených na duplikátech v zadaném sloupci). Viz screenshot:

Když zvýrazníme duplicitní buňky nebo řádky pomocí Podmíněné formátování funkce, všechny duplikáty jsou zvýrazněny stejnou barvou. Pokud jsou však různé řady duplikátů hodnot zvýrazněny různými barvami, bude snazší číst a získat rozdíly. Zde vám VBA může pomoci udělat to v Excelu.

1. lis Další + F11 společně otevřete okno Microsoft Visual Basic pro aplikace.

2. V okně klikněte na Vložit > Modula poté vložte pod kód do nového okna modulu.

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. lis F5 klíč nebo klikněte na Běh ikona pro spuštění tohoto VBA.

4. Ve vyskakovacím dialogu Kutools pro Excel vyberte sloupec, kde zvýrazníte duplicitní buňky různými barvami, a klikněte na OK .

Pak uvidíte, že každá řada duplicitních hodnot je zvýrazněna barvou.

První nástroj, který představím, je Vyberte Duplikovat a jedinečné buňky funkce, kterou poskytuje Kutools pro Excel. Tato funkce umožňuje rychle zjistit jedinečné nebo duplikované buňky.

1. Vyberte sloupec nebo oblast, kde chcete najít, a zvýrazněte duplicitní buňky.

2, klikněte Kutools > vybrat > Vyberte Duplikovat a jedinečné buňky.

3. V dialogu Vyberte duplicitní a jedinečné buňky zaškrtněte možnosti, které potřebujete, a klikněte na Ok k dokončení operace.

Poznámky: V dialogovém okně Vybrat duplicitní a jedinečné buňky
(1) Pokud potřebujete vybrat nebo zvýraznit všechny duplikáty kromě prvních instancí, zaškrtněte políčko Duplikáty (kromě prvního) volba. Jinak zkontrolujte Všechny duplikáty (včetně prvního) volba.
(2) Pokud potřebujete zvýraznit duplikáty, zaškrtněte políčko Vyplňte backcolor vyberte požadovanou barvu zvýraznění.
(3) Pokud chcete vybrat nebo zvýraznit řádky na základě duplikátů ve vybraném sloupci, zaškrtněte políčko Vyberte celé řádky volba.
(4) Pokud chcete vybrat nebo zvýraznit duplicitní hodnoty se shodnými případy, zaškrtněte políčko Citlivý na velká písmena volba.

Kutools pro Excel také poskytuje úžasný nástroj - Porovnat buňky, abychom mohli snadno najít a zvýraznit duplicitní buňky ve dvou sloupcích.

1, klikněte Kutools > Porovnat buňky otevřete dialogové okno Vybrat stejné a různé buňky.

2. V dialogovém okně Vybrat stejné a různé buňky zadejte dva sloupce v Najít hodnoty v a Podle políčka, zaškrtněte Stejné hodnoty možnost a podle potřeby zaškrtněte další možnosti.

Poznámky:
(1) Pokud potřebujete najít duplicitní řádky, zkontrolujte Každý řádek volba; a najít duplicitní buňky, zkontrolujte Jediná buňka možnost v Na základě sekce;
(2) Zaškrtněte políčko Vyplňte backcolor možnost a určit barvu zvýraznění, pokud potřebujete zvýraznit duplicitní řádky nebo buňky;
(3) Zaškrtněte políčko Vybrány celé řádky možnost, pokud potřebujete vybrat nebo zvýraznit celý řádek na základě duplikátů;
(4) Zaškrtněte políčko Citlivý na velká písmena Pokud chcete vyhledat nebo zvýraznit duplikáty rozlišující velká a malá písmena.

3, klikněte Ok nastavení postupně dokončete.

Pak uvidíte duplikáty ve sloupci, který jste zadali v Najít hodnoty v pole jsou označena a zvýrazněna.

Tato metoda vás provede identifikací a zvýrazněním duplicitních buněk podle pravidla podmíněného formátování a následným snadným filtrováním podle barvy zvýraznění v aplikaci Excel.

1. Použijte podmíněné formátování k vyhledání a zvýraznění duplikátů v zadaném sloupci. (Kliknutím zobrazíte postup)

2. Klepnutím vyberte záhlaví sloupce zadaného sloupce a klepněte na tlačítko Data > Filtr.

3. Pokračujte kliknutím na ikonu filtru  v záhlaví sloupce a vyberte Filtr podle barvya poté v rozevíracím seznamu vyberte zadanou barvu podmíněného formátování. Viz screenshot:

Pak uvidíte, že jsou odfiltrovány pouze řádky s duplicitními buňkami. Viz snímek obrazovky:

Alternativně můžeme také identifikovat duplikáty pomocí vzorce ve sloupci pomocníka a potom snadno filtrovat duplikáty se sloupcem pomocníka v aplikaci Excel.

1. Kromě původních dat přidejte pomocný sloupec a zadejte Duplikát jako záhlaví sloupce.

2. Vyberte první prázdnou buňku pod záhlavím sloupce a zadejte vzorec = IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Duplicate", "")a přetažením úchytu automatického vyplňování této buňky zkopírujte tento vzorec do dalších buněk.

Poznámky: Ve výše uvedeném vzorci, $ C $ 3: $ C $ 12 je sloupec obsahující duplicitní data a C3 je první buňka (kromě buňky záhlaví) ve sloupci.

3. Klepnutím vyberte záhlaví sloupce - Duplikáty a klepněte na tlačítko Data > Filtr.

4. Poté klikněte na ikonu filtru  v záhlaví sloupce pouze zaškrtněte Duplikáta klepněte na tlačítko OK knoflík. Viz screenshot:

Řádky s duplicitními hodnotami jsou zatím filtrovány pouze. Viz snímek obrazovky:

Obecně můžeme použít = COUNTIF (rozsah, kritéria) spočítat celkový počet určité hodnoty, která se objeví ve specifikovaném rozsahu. Říká, kolikrát se „Apple“ objeví v seznamu A2: A10, Můžeme použít vzorec = COUNTIF (A2: A10, "Apple") spočítat počet této duplicitní hodnoty.

Vzorec = COUNTIF (rozsah, kritéria) však počítá pouze zadanou duplicitní hodnotu. Co takhle počítat duplicitní hodnotu se dvěma nebo více kritérii? A co když počítání duplikátů rozlišujících velká a malá písmena s kritérii? Níže uvedené metody vám mohou pomoci tyto problémy vyřešit.

Můžeme použít maticový vzorec pro počítání duplicitních hodnot citlivých na malá a velká písmena s kritérii v aplikaci Excel. Chcete-li například spočítat, kolikrát se hodnota „Apple“ objeví v seznamu B2: B21 s odpovídajícími případy, můžete to udělat takto:

1. Vyberte prázdnou buňku.

2. Zadejte vzorec = SUMA (- PŘESNÁ (B2: B20, E2)).

3. lis Ctrl + směna + vstoupit vrátit výsledek počítání.

Poznámky: Ve vzorci pole,
(1) B2: B20 je sloupec, ve kterém budete počítat duplikáty.
(2) E2 je buňka obsahující zadanou hodnotu, u které chcete spočítat počet výskytů. Odkaz na buňku můžete změnit na hodnotu s uvozovkami, říká "Jablko".

Někdy můžete chtít počítat duplikáty se dvěma nebo více kritérii. Můžete použít COUNTIFS funkce.
Například je uvedena tabulka prodeje ovoce, jak je uvedeno níže. Zde musíme spočítat časy opakování jablka, které bylo prodáno 7. 5. 2020 a částka prodeje je větší než 300. Duplikáty s těmito kritérii můžete spočítat takto:

1. Vyberte prázdnou buňku.

2. Zadejte vzorec =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. zmáčkni vstoupit klíč k získání výsledku počítání.

Poznámky: Ve výše uvedeném vzorci,
(1) B3: B20 je sloupec (první) datum a G4 je kritérium data;
(2) C3: C20 je (druhý) sloupec ovoce a G3 je kritérium pro ovoce;
(3) D3: D20 je (třetí) sloupec částky a „> 300“ je částka kritéria.
(4) Pokud je v tabulce více sloupců a kritérií, můžete přidat odkaz na sloupec a kritéria.

Chcete-li spočítat všechny duplikáty ve sloupci kromě prvních výskytů, postupujte takto:

1. Vyberte prázdnou buňku vedle sloupce.

2. Zadejte vzorec = IF (COUNTIF ($ B $ 3: B3, B3)> 1, "YES", "")a potom přetáhněte úchyt automatického vyplňování dolů a zkopírujte tento vzorec do dalších buněk.

Poznámky: Ve výše uvedeném vzorci,
(1) $ B $ 3: B3 je rozsah, ve kterém počítáte duplikáty. V $ B $ 3: B3 se B3 automaticky změní, když zkopírujete vzorec do jiných buněk.
(2) B3 je první buňka v zadaném sloupci.
(3) Tento vzorec vrátí ANO nebo prázdný. ANO znamená, že odpovídající hodnota je duplikát, zatímco prázdné znamená jedinečnou.

Poté jsou identifikovány všechny duplikáty v zadaném sloupci. Můžeme spočítat výsledky vzorce a získat celkový počet duplikátů.

3. Vyberte prázdnou buňku.

4. Zadejte vzorec = COUNTIF (C3: C16, "ANO"), a stiskněte tlačítko vstoupit klíč.

Poznámky: Ve výše uvedeném vzorci,
(1) C3: C16 je rozsah, který jsme použili vzorec k identifikaci duplikátů v posledním kroku.
(2) ANO je hodnota vrácená posledním vzorcem.

Pak dostaneme celkový počet duplicitních hodnot ve specifikovaném sloupci. Celkový počet duplikátů nezahrnuje první výskyty.

Chcete-li spočítat počet všech duplikátů včetně prvních výskytů v aplikaci Excel, můžete k tomu použít vzorec pole.

1. Vyberte prázdnou buňku.

2. Zadejte vzorec =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. lis Ctrl + směna + vstoupit vrátit výsledek počítání.

Poznámky: Ve výše uvedeném vzorci, B3: B16 je zadaný sloupec, který chceme počítat duplikáty včetně prvních výskytů v rámci.

Chcete-li si zjednodušit práci a ulevit si od zapamatování dlouhých nudných vzorců, můžete vyzkoušet Vyberte Duplikovat a jedinečné buňky funkce, poskytuje Kutools pro Excel, pro rychlé spočítání počtu duplicitních hodnot ve specifikovaném seznamu nebo sloupci s vyloučením nebo zahrnutím prvních výskytů.

1. Vyberte sloupec, kde budete počítat počet duplicitních hodnot, a klikněte Kutools > vybrat > Vyberte Duplikovat a jedinečné buňky.

2. V dialogu Vyberte duplicitní a jedinečné buňky zaškrtněte políčko Duplikáty (kromě prvního) or Všechny duplikáty (včetně prvního) vyberte požadovanou možnost a klikněte na ikonu Ok .

3. Poté jsou vybrány všechny duplicitní hodnoty včetně nebo bez prvních výskytů a současně se zobrazí dialogové okno a ukazuje, kolik buněk bylo vybráno. Viz screenshot výše.

Říká, že chcete porovnat dva seznamy jmen a spočítat počet duplikátů mezi nimi, jak byste mohli tento problém rychle vyřešit? Můžeme to také udělat pomocí vzorce v aplikaci Excel.

1. Vyberte prázdnou buňku.

2. Zadejte vzorec = SUMPRODUCT (- (ISNUMBER (MATCH (B3: B12, D3: D18,0)))).

3. zmáčkni vstoupit klíč.

Poznámky: Ve výše uvedeném vzorci,
(1) B3: B12 je první sloupec jmen, ve kterém budete počítat duplikáty.
(2) D3: D18 je druhý sloupec jmen, podle kterého budete počítat duplikáty.

Alternativně můžeme použít doplněk třetí strany, Kutools pro Excel, aby bylo možné snadno rychle spočítat celkový počet duplicitních buněk mezi dvěma sloupci.

1, klikněte Kutools > vybrat > Vyberte stejné a různé buňky.

2. V dialogovém okně Vybrat stejné a rozdílové buňky
(1) Určete dva sloupce v souboru Najít hodnoty v a Podle krabice zvlášť.
(2) Zkontrolujte Jediná buňka volba.
(3) Zkontrolujte Stejné hodnoty volba.

4, Klepněte na tlačítko Ok .

Poté jsou vybrány všechny duplicitní buňky v prvním sloupci a současně se zobrazí dialogové okno a ukazuje, kolik duplicitních buněk bylo vybráno. Viz screenshot:

Poznámky: Tato funkce spočítá celkový počet duplicitních hodnot ve sloupci, který jste zadali v souboru Najít hodnoty v v dialogovém okně Vybrat stejné a různé buňky. Pokud potřebujete spočítat celkový počet duplicitních hodnot ve druhém sloupci, použijte Vyberte stejné a různé buňky funkce znovu se zadáním druhého sloupce v souboru Najít hodnoty v box.

Každou duplicitní hodnotu můžete rychle spočítat jednou pomocí vzorce takto:

1. Vyberte prázdnou buňku.

2. Zadejte vzorec =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3. zmáčkni vstoupit klíč k získání výsledku počítání.

Poznámky: Ve výše uvedeném vzorci, C3: C19 je zadaný sloupec, který chcete spočítat každou duplicitní hodnotu jednou.

Při počítání seznamu lze každou duplicitní hodnotu počítat jednou se shodnými případy, můžete použít vzorec pole, abyste to mohli snadno provést v aplikaci Excel.

1. Vyberte prázdnou buňku.

2. Zadejte vzorec pole = SUM (IFERROR (1 / IF (C3: C19 <> "", FREQUENCY (IF (EXACT (C3: C19, TRANSPOSE (C3: C19)), MATCH (ROW (C3: C19), ROW (C3: C19) ), ""), MATCH (ROW (C3: C19), ROW (C3: C19))), 0), 0)).

3. lis Ctrl + směna + vstoupit společně vrátíte výsledek počítání.

Poznámky: Ve výše uvedeném vzorci pole je C3: C19 zadaný sloupec, kde budete počítat každou sérii duplikátů jednou se shodnými případy.

Pokud máte Kutools pro Excel nainstalován, můžete také použít jeho Počítání buněk s jedinečnými hodnotami funkce pro rychlé spočítání každé řady duplicitních hodnot jednou v aplikaci Excel.

1. Vyberte prázdnou buňku.

2, klikněte Kutools > Pomocník vzorců > Statistický > Počítání buněk s jedinečnými hodnotami (zahrňte první duplicitní hodnotu).

3. V dialogovém okně Pomocník pro vzorce zadejte sloupec, ve kterém budete počítat duplikáty jednou Rozsah a klepněte na tlačítko Ok .

Poté je výsledek počítání okamžitě vyplněn do vybrané buňky.

Můžeme použít Mezisoučet funkce spočítat každou sérii duplicitních hodnot ve sloupci v aplikaci Excel.

1. Vyberte sloupec, ve kterém budete počítat každou sérii duplicitních hodnot, a klikněte Data > Třídit od A do Z. or Roztřídit Z do A..

2. Ve vyskakovacím dialogovém okně Varování řazení zaškrtněte políčko Rozbalte výběr a klepněte na tlačítko Typ .

Pak uvidíte, že výběr byl seřazen podle duplicitních hodnot zadaného sloupce.

3. Ponechte vybraný výběr a klikněte Data > Mezisoučet.

4. V dialogu Mezisoučet
(1) Vyberte zadaný sloupec z Při každé změně v rozbalovací seznam;
(2) Vyberte položku Počítat z použijte funkci rozbalovací seznam;
(3) Zaškrtněte pouze určený sloupec v Přidat mezisoučet do seznam;
(4) Klikněte na OK .

Pak uvidíte, že se počítá každá řada duplicitních hodnot a výsledek počítání se přidá pod každou sérii duplicitních hodnot, viz výše uvedený snímek obrazovky.

Můžeme také vytvořit kontingenční tabulku, abychom mohli rychle spočítat každou sérii duplicitních hodnot v aplikaci Excel.

1. Vyberte rozsah obsahující zadaný sloupec a klikněte Vložit > Kontingenční tabulka.

2. V dialogu Vytvořit kontingenční tabulku určete místo, kde najdete novou kontingenční tabulku, a klikněte na OK .

3. V podokně Pole kontingenční tabulky přetáhněte zadaný sloupec do obou Řádky a Hodnoty sekce. Pak uvidíte, že každá řada duplicitních hodnot v zadaném sloupci se počítá hromadně. Viz snímek obrazovky:

Pokud jste nainstalovali Kutools pro Excel již můžete použít jeho snadno použitelné Pokročilé kombinování řádků funkce pro rychlé spočítání každé řady duplicitních hodnot ve specifikovaném sloupci.

Poznámky: Pokročilé kombinované řádky funkce upraví vybraný rozsah a odstraní řádky na základě duplicitních hodnot ve specifikovaném sloupci primárního klíče. Chcete-li uložit svá data, doporučujeme před níže uvedenými operacemi zálohovat nebo zkopírovat data na jiné místo.

1. Přidejte prázdný sloupec přímo do původního rozsahu dat a pojmenujte nový sloupec jako Počítat.

2. Vyberte původní rozsah dat a nový sloupec společně a klikněte Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků.

3. V dialogovém okně Pokročilé kombinace řádků
(1) Klepnutím vyberte určený sloupec, který bude spočítat každou sérii duplicitních hodnot, a klepněte na Primární klíč.
(2) Klepnutím vyberte nový sloupec (Počet) a poté klepněte na Vypočítaná > Počítat.
(3) V případě potřeby uveďte kombinace nebo typy výpočtu pro další sloupce.
(4) Klikněte na Ok .

Pak uvidíte, že každá řada duplicitních hodnot v zadaném sloupci se počítá hromadně. Viz screenshot:

Říká, že ve sloupci je seznam ovoce. V seznamu se některé plody objevují mnohokrát. Nyní musíte označit každý duplikát ovoce v pořadí, v jakém se objeví, jak byste jej mohli vyřešit? Tady v této části představíme vzorec, který to usnadní v aplikaci Excel.

1. Přidejte k původním datům prázdný sloupec.

2. Zadejte vzorec =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") v první buňce přidaného prázdného sloupce.

3. Přetáhněte úchyt automatického vyplňování této buňky vzorce a zkopírujte vzorec do dalších buněk.

Poznámky: Ve výše uvedeném vzorci,
(1) $ C $ 3: $ C $ 14 je zadaný sloupec, ve kterém chcete spočítat duplicitní hodnoty v pořadí.
(2) C3 je první buňka v zadaném sloupci.
(3) Pokud je odpovídající hodnota duplikát, vrátí tento vzorec pořadové číslo 1, 2, 3… na základě pořadí vzhledu; pokud je odpovídající hodnota jedinečná, vrátí se tento vzorec prázdný.

Můžete použít Odebrat duplikáty funkce k odstranění všech duplicitních hodnot kromě prvních výskytů přímo.

1. Vyberte sloupec, ze kterého chcete odstranit všechny duplicitní hodnoty kromě prvních výskytů.

2, klikněte Data > Odeberte duplikáty.

3. V dialogovém okně Odebrat upozornění na duplikáty zaškrtněte políčko Pokračujte v aktuálním výběru a klepněte na tlačítko Odebrat duplikáty .

Tipy: Chcete-li odstranit řádky na základě duplicitních hodnot ve výběru, zaškrtněte políčko Rozbalte výběr volba.

4. V dialogu Odebrat duplikáty zkontrolujte pouze zadaný sloupec a klikněte na OK .

Tipy: Pokud jste zkontrolovali Rozbalte výběr možnost v posledním kroku, budou zde uvedeny všechny sloupce. I tak musíte zkontrolovat pouze zadaný sloupec.

5. Poté se zobrazí dialogové okno a ukazuje, kolik duplicitních hodnot bylo odstraněno. Klikněte na ikonu OK pro jeho zavření.

Můžete také použít Pokročilý filtr funkce pro snadné odstranění všech duplicitních hodnot ze zadaného sloupce.

1, klikněte Data > pokročilý.

2. V dialogovém okně Rozšířený filtr
(1) Zkontrolujte Kopírovat na jiné místo volba;
(2) V Rozsah seznamu pole, vyberte zadaný sloupec, ze kterého odstraníte duplicitní hodnoty;
(3) V Kopírovat do do pole zadejte rozsah, do kterého vložíte sloupec;
(4) Zaškrtněte políčko Pouze jedinečné záznamy volba.
(5) Klikněte na OK .

Pak uvidíte, že zadaný sloupec je vložen do zadaného rozsahu s odstraněním všech duplicitních hodnot s výjimkou prvních výskytů. Viz screenshot:

Můžete také použít VBA k rychlému odstranění duplicitních hodnot kromě prvních výskytů ze sloupce v aplikaci Excel.

1. lis Další + F11 klávesy pro otevření okna Microsoft Visual Basic for Application.

2, klikněte Vložit > Modula poté vložte pod kód VBA do nového okna modulu.

V ExtendOffice_RemoveAllDeplicate () Dim xRg As Range Dim xURg, xFRg, xFFRg As Range Dim xI, xFNum, xFFNum As Integer Dim xDc As Object Dim xDc_keys Dim xBol As Boolean Dim xStr As String Dim xWs As Works Dim Dim xURgAddress As String On Error Resume xRg = Application.InputBox ("Vyberte rozsah:", "Kutools pro Excel", "",,,, 8) Pokud xRg nic není, ukončete dílčí sadu xURg = Průnik (xRg.Worksheet.UsedRange, xRg) Nastavit xWs = xURg.Worksheet Set xDc = CreateObject ("scripting.dictionary") xURgAddress = xURg.Address xBol = Application.ScreenUpdating Application.ScreenUpdating = False For xFNum = 1 To xURg.Count Set xFRg = xURg.Item (xFNum) If (Not IsErr (xFRg)) Then If xFRg.Value <> "" And (Not IsError (xFRg)) Then For xFFNum = xFNum + 1 To xURg.Count Set xFFRg = xURg.Item (xFFNum) If Not IsError (xFFRg) Then If xFFRg .Value = xFRg.Value Pak xDc (xFFRg.Address) = "" Konec Pokud Konec Pokud Další Konec Pokud Konec Pokud Další xStr = "" xDc_keys = xDc.Keys Pro xI = 1 UBound (xDc_keys) Pokud xStr = "" Pak xStr = xDc_keys (xI) SetxURg = xWs.Range (xStr) Else xStr = xStr & "," & xDc_keys (xI) Set xURg = Application.Union (xWs.Range (xDc_keys (xI)), xURg) Konec, pokud bude další Debug.Print xStr xWs.Activate xURg.Select Selection.Delete Shift: = xlUp xWs.Range (xURgAddress) .Select Application.ScreenUpdating = xBol End Sub

3. lis F5 klíč nebo klikněte na Běh tlačítko pro spuštění tohoto VBA.

4. V rozbalovacím dialogovém okně určete rozsah, ze kterého odstraníte duplicitní hodnoty, a klikněte na OK .

Poté jsou ze zadaného rozsahu okamžitě odstraněny všechny duplicitní hodnoty kromě prvních výskytů.

Poznámka: Tento kód VBA rozlišuje velká a malá písmena.

Můžeme zvýraznit všechny duplicitní hodnoty včetně prvních výskytů ve sloupci nebo seznamu pomocí pravidla podmíněného formátování a poté odfiltrovat všechny duplicitní hodnoty podle barvy zvýraznění. Poté můžeme vybrat všechny odfiltrované duplicitní buňky a poté je hromadně odebrat.

1. Použijte podmíněné formátování ke zvýraznění duplicitních hodnot. (Kliknutím zobrazíte postup)

2. Vyberte sloupec, ze kterého odeberete duplicitní hodnoty (včetně prvních výskytů), a klikněte Data > Filtr.

3. Klikněte na ikonu Filtr  v záhlaví sloupce zadaného sloupce. V rozevírací nabídce vyberte možnost Filtr podle barvya poté zadejte barvu zvýraznění z podnabídky.

Poté jsou odfiltrovány všechny duplicitní hodnoty.

4. Vyberte všechny filtrované buňky, klikněte pravým tlačítkem a vyberte Odstranit řádek z kontextové nabídky. A ve vyskakovacím potvrzovacím dialogovém okně klikněte OK tlačítko pokračovat.

5. Potom jsou všechny duplicitní hodnoty odstraněny hromadně. Ponechte vybraný filtrovaný seznam a klikněte na Filtr > Data filtr zrušíte znovu.

Až dosud uvidíte, že všechny duplicitní buňky včetně prvních výskytů jsou odstraněny hromadně a zůstanou pouze jedinečné hodnoty.

Poznámky: Tato metoda odstraní řádky na základě duplicitních hodnot v zadaném sloupci.

Můžeme také použít vzorec k identifikaci duplicitních hodnot včetně prvních výskytů ve sloupci pomocníka, poté odfiltrovat duplicitní hodnoty podle výsledků vzorce a nakonec tyto filtrované duplicitní hodnoty hromadně odebrat.

1. Přidejte sloupec pomocníka vedle zadaného sloupce, zadejte vzorec = COUNTIF ($ B $ 3: $ B $ 11, B3) do první buňky pomocného sloupce a potom přetáhněte popisovač automatického vyplňování dolů a zkopírujte tento vzorec do dalších buněk. Viz snímek obrazovky:

Poznámka: Ve výše uvedeném vzorci, $ B $ 3: $ B $ 11 je zadaný sloupec, ze kterého odstraníte duplicitní hodnoty, a B3 je první buňka v zadaném sloupci.

2. Vyberte sloupec pomocníka a klikněte Data > Filtr.

3. Klikněte na ikonu filtru  v záhlaví sloupce pomocníka, pak v rozevírací nabídce zkontrolujte všechny hodnoty kromě 1a klepněte na tlačítko OK knoflík. Viz screenshot:

4. Nyní jsou všechny duplicitní hodnoty odfiltrovány. Vyberte odfiltrované buňky ve sloupci pomocníka, klikněte pravým tlačítkem a vyberte Odstranit řádek v místní nabídce.

5. Ve vyskakovacím potvrzovacím dialogovém okně klikněte na OK tlačítko pokračovat.

6. Nyní jsou všechny duplicitní hodnoty a jejich řádky odstraněny hromadně. Pokračujte kliknutím Data > Filtr filtr zrušíte znovu.

Pak uvidíte, že všechny duplicitní hodnoty včetně prvních výskytů jsou hromadně odstraněny.

Pokud máte Kutools pro Excel nainstalován, můžete také použít jeho Vyberte Duplikovat a jedinečné buňky funkce pro rychlý výběr a odstranění duplicitních hodnot včetně nebo vyloučení prvních výskytů snadno v aplikaci Excel.

1. Vyberte sloupec, ze kterého odeberete duplikáty.

2, klikněte Kutools > vybrat > Vyberte Duplikovat a jedinečné buňky.

3. V dialogu Vyberte duplicitní a jedinečné buňky zaškrtněte políčko Všechny duplikáty (včetně prvního) a klepněte na tlačítko Ok .

Poznámky:
(1) Chcete-li vybrat a odstranit duplicitní hodnoty s výjimkou prvních výskytů, zaškrtněte políčko Duplikáty (kromě prvního) volba.
(2) Chcete-li vybrat a odstranit řádky na základě duplicitních hodnot v zadaném sloupci, zaškrtněte políčko Vyberte celé řádky volba.
(3) Chcete-li vybrat a odstranit duplicitní hodnoty se shodnými případy, zaškrtněte políčko Citlivý na velká písmena volba.
(4) Chcete-li vybrat, zvýraznit a odstranit duplicitní buňky nebo řádky, zaškrtněte políčko Vyplňte backcolor or Barva výplně písma možnosti a podle potřeby určete barvy výplně nebo písma.

4. Poté se zobrazí dialogové okno a zobrazí se, kolik buněk bylo vybráno, klepněte na ikonu OK pro jeho zavření.

5. Klikněte pravým tlačítkem na vybrané buňky a vyberte Vymazat z kontextové nabídky.

6. V nadcházejícím dialogu Odstranit zaškrtněte políčko Posuňte buňky nahoru a klepněte na tlačítko OK .

Zatím byly všechny duplicitní hodnoty včetně prvních výskytů odstraněny hromadně.

Ve většině případů identifikujeme duplicitní hodnoty ve sloupci a poté odstraníme celé řádky podle duplicitních hodnot. Abych byl upřímný, tato operace je docela podobná odstranění duplicitních hodnot z jednoho sloupce. Ve výsledku můžeme podobná řešení použít k odstranění řádků na základě duplikátů v zadaném sloupci.

První metodou je použití integrovaného Odebrat duplikáty funkce pro odstranění řádků duplikáty v zadaném sloupci. Stačí vybrat rozsah, který chcete odebrat, kliknout Data > Odebrat duplikáty Chcete-li funkci povolit, zaškrtněte pouze zadaný sloupec v dialogovém okně Odebrat duplikáty a poté klikněte OK pro dokončení operace vyjmutí.

Můžeme také použít funkci Podmíněné formátování a filtr k odstranění řádků na základě duplicitních hodnot v zadaném sloupci. Nejprve zvýrazněte řádky založené na duplicitních hodnotách v určitém sloupci pravidlem podmíněné formace (kliknutím zobrazíte jak). Za druhé filtrujte rozsah podle barvy. Za třetí, snadno odstraňte všechny odfiltrované řádky. Nakonec filtr vymažte nebo zrušte a uvidíte, že v zadaném sloupci zůstanou pouze řádky s jedinečnými hodnotami.

Alternativně můžete přidat pomocný sloupec a použít vzorec = COUNTIF ($ C $ 3: $ C $ 21, C3) k identifikaci duplikátů v zadaném sloupci. Poté odfiltrujte čísla větší než 1 ve sloupci pomocníka a snadno odeberte všechny odfiltrované řádky. Po vymazání filtru uvidíte, že řádky s jedinečnými hodnotami v zadaném sloupci zůstanou pouze vlevo.

Doplněk třetí strany Kutools pro Excel přináší také mimořádnou užitečnou funkci, Vyberte Duplikovat a jedinečné buňkyChcete-li rychle vybrat řádky na základě duplicitních hodnot v zadaném sloupci, můžete tyto vybrané řádky rychle odebrat kliknutím na nabídku pravým tlačítkem myši.

Kutools pro Excel Pokročilé kombinování řádků funkce také může rychle odstranit řádky na základě duplicitních hodnot ve specifikovaném sloupci primárního klíče.

Můžeme přidat pomocný sloupec a použít vzorec k identifikaci duplicitních hodnot mezi dvěma sloupci a potom snadno filtrovat a odstranit duplicitní hodnoty.

1. Přidejte prázdný sloupec vedle zadaného sloupce, ze kterého odstraníte duplicitní hodnoty.

2. V první buňce pomocného sloupce (kromě buňky záhlaví) zadejte vzorec = IF (ISERROR (MATCH (C2, $ A $ 2: $ A $ 13,0)), "Unique", "Duplicate")a potom přetáhněte úchyt automatického vyplňování dolů a zkopírujte vzorec do dalších buněk.

Poznámky: Ve výše uvedeném vzorci,
(1) C2 je první buňka v zadaném sloupci, ze které odstraníte duplicitní hodnoty;
(2) $ A $ 2: $ A $ 13 je další sloupec, se kterým musíme porovnávat.
(3) Tento vzorec se vrátí Duplikát pokud je odpovídající hodnota duplikována s hodnotami v druhém sloupci, a vrátit Unikátní pokud se liší od hodnot v druhém sloupci.

3. Vyberte sloupec pomocníka a klikněte Data > Filtr.

4. Klikněte na ikonu filtru  v záhlaví sloupce pomocníka, pak v rozevírací nabídce pouze zaškrtněte Duplikáta klepněte na tlačítko OK .

5. Nyní jsou všechny duplicitní hodnoty odfiltrovány. Vyberte filtrované buňky, klikněte pravým tlačítkem a vyberte Odstranit řádek z kontextové nabídky. Poté klikněte OK ve vyskakovacím potvrzovacím dialogu.

6. Poté jsou ze zadaného sloupce odstraněny všechny duplicitní hodnoty. Pokračujte kliknutím Data > Filtr filtr zrušíte znovu.

Pak uvidíte, že v zadaném sloupci zůstanou pouze jedinečné hodnoty. Sloupec pomocníka můžete podle potřeby odebrat.

Poznámky: Tato metoda odstraní celé řádky na základě duplicitních hodnot v zadaném sloupci.

Pokud máte Kutools pro Excel nainstalován, můžete použít jeho úžasné Vyberte stejné a různé buňky funkce pro rychlý výběr duplicitních hodnot mezi dvěma sloupci a jejich snadné odstranění.

1, klikněte Kutools > vybrat > Vyberte stejné a různé buňky pro povolení této funkce.

2. V dialogovém okně Vybrat stejné a různé buňky zadejte oba sloupce v Najít hodnoty v a Podle pole zvlášť, zkontrolujte Jediná buňka a Stejné hodnoty možnosti a klikněte na Ok knoflík. Viz screenshot:

3. Poté jsou všechny duplicitní hodnoty ve dvou sloupcích vybrány v prvním sloupci (sloupec, který jste zadali v Najít hodnotu v krabice). A klikněte na OK v rozbalovacím dialogovém okně.

4. Můžete stisknout tlačítko Vymazat klíč k odstranění těchto duplicitních hodnot přímo, nebo na ně klikněte pravým tlačítkem a vyberte Vymazat z kontextové nabídky.