Znajdując zduplikowane komórki tylko w jednej kolumnie lub na liście, możemy zastosować rozszerzenie COUNTIF funkcję szybkiego znajdowania i liczenia zduplikowanych danych.
1. Wybierz pustą komórkę obok kolumny, w której znajdziesz duplikaty.

2. Wpisz wzór = LICZ.JEŻELI (3 C $: 12 C $, C3)> 1i naciśnij Wchodzę klawisz.

3. Przeciągnij uchwyt autouzupełniania komórki z formułą, aby skopiować tę formułę do innych komórek.

Uwagi: We wzorze = LICZ.JEŻELI (3 C $ C: 12 C $ 3, C1)> XNUMX,
(1) 3 C $: 12 C $ to kolumna lub lista, w której znajdziesz zduplikowane wartości. Ponieważ kolumna jest statyczna, gdy kopiujemy formułę do innych komórek, generalnie jest to odniesienie bezwzględne z „$”.
(2) C3 jest pierwszą komórką określonej kolumny. Jest to odniesienie względne, ponieważ potrzebujemy, aby zmieniało się automatycznie, gdy kopiujemy formułę do innych komórek.
(3) Ta formuła powróci TRUE or FAŁSZ. TRUE oznacza, że ​​odpowiednia wartość jest zduplikowana, podczas gdy FAŁSZYWY wskazuje, że odpowiednia wartość jest unikalna w kolumnie.
(4) Czasami PRAWDA lub FAŁSZ mogą nie być zrozumiane intuicyjnie. Możemy połączyć oryginalną formułę i funkcję JEŻELI, aby zwrócić Duplikaty bezpośrednio.
= JEŻELI (LICZ.JEŻELI (3 C $: 12 C $, C3)> 1, "Duplikaty", "")

W niektórych przypadkach musisz porównać dwie kolumny i znaleźć zduplikowane wartości. Na przykład, masz dwie listy nazwisk i chcesz znaleźć zduplikowane nazwy na drugiej liście w porównaniu z pierwszą. możesz zastosować funkcję WYSZUKAJ.PIONOWO i JEŻELI BŁĄD, aby zrobić to łatwo.

1. Wybierz pustą komórkę obok drugiej listy nazwisk.

2. Wprowadź wzór = JEŻELI BŁĄD (WYSZUKAJ.PIONOWO (D3; 3 B $ 18,1,0: B $ XNUMX); „”)i naciśnij Wchodzę klawisz.

3. Przeciągnij uchwyt autouzupełniania komórki z formułą, aby skopiować formułę do innych komórek, jeśli potrzebujesz.

Uwagi: W powyższym wzorze
(1) D3 to pierwsza komórka na drugiej liście nazwisk. Odniesienie musi zmieniać się automatycznie, gdy kopiujemy formułę do innych komórek, w wyniku czego jest względne.
(2) 3 B $: 18 B $ to pierwsza lista nazwisk. Jest to odniesienie bezwzględne, ponieważ zakres musi pozostać statyczny, gdy kopiujemy formułę do innych komórek.
(3) Gdy nazwa jest zduplikowana z imionami na pierwszej liście, formuła zwróci nazwę; w przeciwnym razie zwraca puste.
(4) Możesz również zastosować formułę = JEŻELI (LICZ.JEŻELI (3 mld USD: 18 mld USD, D3)> 0, „Duplikaty”, „”) aby znaleźć zduplikowane nazwy na drugiej liście w porównaniu z pierwszą listą. Ta formuła powróci „Duplikaty” jeśli odpowiednia nazwa jest zduplikowana.

(5) Jeśli chcesz znaleźć duplikaty w dwóch kolumnach na dwóch arkuszach, wystarczy dodać nazwę arkusza przed odwołaniem do porównywanej kolumny. W naszym przykładzie po prostu zmień w formule $ B $ 3: $ B $ 18 na Sheet1! $ B $ 3: $ B $ 18.

Wzory przedstawione powyżej nie odpowiadają wielkości liter podczas wyszukiwania duplikatów, mówi, że uważają, że „jabłko” jest duplikatem z „APPLE”. Tutaj możesz zastosować formułę tablicową, aby znaleźć zduplikowane wartości w jednej kolumnie z pasującymi obserwacjami.

1. Wybierz pustą komórkę obok kolumny, w której znajdziesz duplikaty.

2. Wpisz formułę tablicową = JEŻELI (SUMA ((- DOKŁADNA (3 C $ 12: 3 C $, C1))) <= XNUMX, "", "Duplikat")i naciśnij Ctrl + Shift + Wchodzę klawiatura.

3. Przeciągnij uchwyt autouzupełniania komórki z formułą, aby skopiować formułę tablicową do innych komórek.

Uwagi: W powyższej formule tablicowej
(1) 3 C $: 12 C $ to kolumna, w której musisz znaleźć zduplikowane wartości. Odwołanie jest bezwzględne, ponieważ jest statyczne podczas kopiowania formuły tablicowej do innych komórek.
(2) C4 to pierwsza komórka w kolumnie. Odniesienie jest względne, ponieważ musi zmienić się automatycznie podczas kopiowania formuły tablicowej do innych komórek.
(3) Jeśli odpowiadająca jej komórka jest zduplikowana, formuła tablicowa zwróci „Duplikat”, w przeciwnym razie zwraca wartość pustą.

Możesz zastosować Formatowanie warunkowe funkcja, aby szybko podświetlić zduplikowane komórki w kolumnie lub zakresie.

1. Wybierz kolumnę, w której podświetlisz zduplikowane komórki.

2. Kliknij Strona główna > Formatowanie warunkowe > Podświetl Zasady komórek > Zduplikowane wartości. Zobacz zrzut ekranu poniżej:

3. W pojawiającym się oknie dialogowym Powielone wartości wybierz Duplikat z pierwszej listy rozwijanej wybierz scenariusz z najważniejszymi elementami z drugiej listy rozwijanej i kliknij OK przycisk.

Uwagi: Jeśli gotowe scenariusze najważniejszych wydarzeń nie spełniają Twoich potrzeb, możesz wybrać Format niestandardowy z drugiej listy rozwijanej, a następnie wybierz kolor podświetlenia, czcionkę i obramowanie komórek zgodnie z potrzebami w wyświetlanym oknie dialogowym Formatowanie komórek.

Następnie zobaczysz, że wszystkie zduplikowane komórki są podświetlone w wybranej kolumnie, jak pokazano poniżej.

Uwagi:  
(1) Po zaznaczeniu zduplikowanych komórek możemy łatwo odfiltrować te duplikaty. (Kliknij, aby zobaczyć, jak to zrobić)
(2) Po zaznaczeniu zduplikowanych komórek możemy również łatwo usunąć duplikaty zbiorczo. (Kliknij, aby zobaczyć, jak to zrobić)

Niektórzy użytkownicy wolą wyróżniać wiersze na podstawie zduplikowanych komórek w określonej kolumnie. W tej sytuacji możemy utworzyć niestandardową regułę formatowania warunkowego, aby to zrobić.

1. Wybierz zakres (z wyłączeniem wiersza nagłówka), w którym wyróżnisz wiersze na podstawie zduplikowanych komórek.

2. Kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

3. W oknie dialogowym Nowa reguła formatowania
(1) Kliknij, aby wybrać Użyj formuły, aby określić, które komórki należy sformatować opcja;
(2) Wpisz wzór = LICZ.JEŻELI (3 C $: 12 C $, C3 $)> 1 Formatuj wartości tam, gdzie ta formuła jest prawdziwa pudełko;
Tips: We wzorze 3 C $: 12 C $ to kolumna zawierająca zduplikowane komórki, a $ C3 to pierwsza komórka w kolumnie.
(3) Kliknij utworzony przycisk.

4. W oknie dialogowym Formatowanie komórek określ wymagany kolor wypełnienia, czcionkę i obramowanie komórek i kliknij przycisk OK kolejno przycisków, aby zapisać ustawienia.

Do tej pory w wybranym zakresie wiersze są podświetlane na podstawie zduplikowanych komórek w określonej kolumnie. Zobacz zrzut ekranu:

Aby wyróżnić zduplikowane wiersze w pewnym zakresie, możesz zastosować rozszerzenie Formatowanie warunkowe funkcja, aby to osiągnąć.

1. Wybierz zakres poza wierszem nagłówka.

2. Kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

3. W oknie dialogowym Nowa reguła formatowania:
(1) Kliknij, aby zaznaczyć plik Użyj formuły, aby określić, które komórki należy sformatować opcja;
(2) W Formatuj wartości tam, gdzie ta formuła jest prawdziwa wpisz formułę =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Kliknij utworzony przycisk.

Uwagi: We wzorze =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) 3 B $: 12 B $ to pierwsza kolumna w zakresie, a $ B3 jest pierwszą komórką w tej kolumnie;
(2) 3 C $: 12 C $ to druga kolumna w zakresie, a $ C3 jest pierwszą komórką w kolumnie;
(3) 3 $ D $: 12 $ D $ to trzecia kolumna w zakresie, a $ D3 jest pierwszą komórką w kolumnie;
(4) Jeśli w Twoim zakresie jest więcej kolumn, możesz kolejno dodać odwołania do kolumn i ich pierwsze komórki w formule.

4. W oknie dialogowym Formatowanie komórek określ kolor podświetlenia, czcionkę, obramowanie komórek itp. Według potrzeb, a następnie kliknij OK kolejno przycisków, aby zapisać ustawienie.

Jak dotąd zduplikowane wiersze są identyfikowane i podświetlane w wybranym zakresie. Zobacz zrzut ekranu:

Być może zauważyłeś, że wszystkie duplikaty są identyfikowane lub wyróżniane powyższymi metodami. Czasami chcesz po prostu zobaczyć, które elementy są zduplikowane, z wyjątkiem pierwszych wystąpień. Możesz również zrobić to za pomocą Formatowanie warunkowe funkcja i inna formuła.

1. Wybierz kolumnę z nagłówkiem.
Tips: Jeśli chcesz wyróżnić wiersze na podstawie duplikatów w jednej kolumnie z wyjątkiem pierwszych wystąpień, wybierz zakres bez wiersza nagłówka.

2. Kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

3. W oknie dialogowym Nowa reguła formatowania:
(1) Kliknij, aby podświetlić Użyj formuły, aby określić, które komórki należy sformatować opcja;
(2) W Formatuj wartości tam, gdzie ta formuła jest prawdziwa wpisz formułę = LICZ.JEŻELI (3 C $ C: C3; C3)> 1;
Tips: Aby wyróżnić wiersze oparte na duplikatach w jednej kolumnie, wpisz formułę = LICZ.JEŻELI (3 $ C $: C3 $, C3 $)> 1.
(3) Kliknij utworzony przycisk.

4. W wyskakującym oknie dialogowym Formatuj komórki określ kolor podświetlenia, czcionkę, obramowanie komórek itp. Według potrzeb, a następnie kliknij OK aby zapisać ustawienia.

Następnie zobaczysz zduplikowane komórki, z wyjątkiem pierwszych wystąpień w wybranej kolumnie (lub wierszy opartych na duplikatach w określonej kolumnie). Zobacz zrzut ekranu:

Kiedy podświetlamy zduplikowane komórki lub wiersze za pomocą Formatowanie warunkowe funkcja, wszystkie duplikaty są wyróżnione tym samym kolorem. Jeśli jednak różne serie wartości duplikatów są podświetlone różnymi kolorami, łatwiej będzie odczytać i uzyskać różnice. Tutaj VBA może pomóc Ci to zrobić w programie Excel.

1. naciśnij inny + F11 klucze razem, aby otworzyć okno Microsoft Visual Basic for Applications.

2. W oknie kliknij wstawka > Moduł, a następnie wklej poniższy kod do nowego okna modułu.

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. naciśnij F5 lub kliknij run ikona, aby uruchomić ten VBA.

4. W wyskakującym oknie dialogowym Kutools for Excel wybierz kolumnę, w której podświetlisz zduplikowane komórki o różnych kolorach, i kliknij OK przycisk.

Następnie zobaczysz, że każda seria zduplikowanych wartości jest podświetlona kolorem.

Pierwszym narzędziem, które przedstawię, jest Wybierz Zduplikowane i unikalne komórki funkcja, dostarczona przez Kutools dla programu Excel. Ta funkcja może szybko znaleźć unikalne lub zduplikowane komórki.

1. Wybierz kolumnę lub zakres, w którym chcesz znaleźć i zaznacz zduplikowane komórki.

2. Kliknij Kutools > Wybierz > Wybierz Zduplikowane i unikalne komórki.

3. W oknie dialogowym Wybierz zduplikowane i unikalne komórki zaznacz potrzebne opcje i kliknij Ok przyciski, aby zakończyć operację.

Uwagi: W oknie dialogowym Wybierz zduplikowane i unikalne komórki
(1) Jeśli chcesz zaznaczyć lub zaznaczyć wszystkie duplikaty z wyjątkiem pierwszych wystąpień, zaznacz Duplikaty (z wyjątkiem pierwszego) opcja. W przeciwnym razie sprawdź Wszystkie duplikaty (w tym pierwszy) opcja.
(2) Jeśli chcesz podświetlić duplikaty, zaznacz Wypełnij kolor tła opcję i określ kolor podświetlenia według potrzeb.
(3) Jeśli chcesz wybrać lub podświetlić wiersze na podstawie duplikatów w wybranej kolumnie, zaznacz Wybierz całe wiersze opcja.
(4) Jeśli chcesz wybrać lub podświetlić zduplikowane wartości z pasującymi obserwacjami, zaznacz Rozróżnianie wielkości liter opcja.

Kutools dla programu Excel zapewnia również niesamowite narzędzie - Porównaj komórki, aby pomóc nam łatwo znaleźć i zaznaczyć zduplikowane komórki w dwóch kolumnach.

1. Kliknij Kutools > Porównaj komórki aby otworzyć okno dialogowe Wybierz te same i różne komórki.

2. W oknie dialogowym Wybierz takie same i różne komórki określ dwie kolumny w Znajdź wartości w i Zgodnie z zaznacz pola Te same wartości opcję i zaznacz inne opcje, jeśli potrzebujesz.

Uwagi:
(1) Jeśli chcesz znaleźć zduplikowane wiersze, sprawdź Każdy rząd opcja; i aby znaleźć zduplikowane komórki, sprawdź Pojedyncza komórka opcja w Na podstawie Sekcja;
(2) Zaznacz Wypełnij kolor tła opcja i określ kolor podświetlenia, jeśli chcesz podświetlić zduplikowane wiersze lub komórki;
(3) Zaznacz Wybrano całe wiersze opcja, jeśli chcesz zaznaczyć lub podświetlić cały wiersz na podstawie duplikatów;
(4) Zaznacz Rozróżnianie wielkości liter jeśli chcesz znaleźć lub wyróżnić duplikaty z rozróżnianiem wielkości liter.

3. Kliknij Ok kolejno przycisków, aby zakończyć ustawienia.

Następnie zobaczysz duplikaty w kolumnie określonej w Znajdź wartości w pola są zidentyfikowane i wyróżnione.

Ta metoda poprowadzi Cię do zidentyfikowania i wyróżnienia zduplikowanych komórek za pomocą reguły formatowania warunkowego, a następnie do łatwego filtrowania według koloru podświetlenia w programie Excel.

1. Zastosuj formatowanie warunkowe, aby znaleźć i wyróżnić duplikaty w określonej kolumnie. (Kliknij, aby zobaczyć, jak to zrobić)

2. Kliknij, aby zaznaczyć nagłówek określonej kolumny, i kliknij Dane > FILTRY.

3. Śmiało, aby kliknąć ikonę filtra  w nagłówku kolumny i wybierz Filtr według koloru, a następnie wybierz określony kolor formatowania warunkowego z listy rozwijanej. Zobacz zrzut ekranu:

Następnie zobaczysz, że tylko wiersze z zduplikowanymi komórkami są odfiltrowywane. Zobacz zrzut ekranu:

Alternatywnie możemy również zidentyfikować duplikaty za pomocą formuły w kolumnie pomocniczej, a następnie łatwo odfiltrować duplikaty za pomocą kolumny pomocniczej w programie Excel.

1. Oprócz oryginalnych danych dodaj kolumnę pomocniczą i wpisz Duplikat jako nagłówek kolumny.

2. Wybierz pierwszą pustą komórkę pod nagłówkiem kolumny, wprowadź formułę = JEŻELI (LICZ.JEŻELI (3 C $: 12 C $; C3)> 1, "Duplikat", "")i przeciągnij uchwyt autouzupełniania tej komórki, aby skopiować tę formułę do innych komórek.

Uwagi: W powyższym wzorze, 3 C $: 12 C $ to kolumna zawierająca zduplikowane dane, a C3 jest pierwszą komórką (z wyjątkiem komórki nagłówka) w kolumnie.

3. Kliknij, aby zaznaczyć nagłówek kolumny - Duplikaty, i kliknij Dane > FILTRY.

4. Następnie kliknij ikonę filtra  w nagłówku kolumny zaznacz tylko Duplikati kliknij przycisk OK przycisk. Zobacz zrzut ekranu:

Jak dotąd wiersze ze zduplikowanymi wartościami są odfiltrowywane tylko. Zobacz zrzut ekranu:

Ogólnie możemy zastosować = LICZ.JEŻELI (zakres, kryteria) aby policzyć całkowitą liczbę określonej wartości pojawiającej się w określonym zakresie. Mówi, licząc, ile razy „Jabłko” pojawia się na liście A2: A10. Możemy zastosować wzór = LICZ.JEŻELI (A2: A10; „Apple”) aby policzyć liczbę tej zduplikowanej wartości.

Jednak formuła = LICZ.JEŻELI (zakres, kryteria) zlicza tylko określoną zduplikowaną wartość. A co powiesz na liczenie zduplikowanych wartości z dwoma lub wieloma kryteriami? A co, jeśli licząc duplikaty z rozróżnianiem wielkości liter na podstawie kryteriów? Poniższe metody mogą pomóc w rozwiązaniu tych problemów.

Możemy zastosować formułę tablicową, aby policzyć zduplikowane wartości z uwzględnieniem wielkości liter za pomocą kryteriów w programie Excel. Na przykład, aby policzyć, ile razy wartość „Apple” pojawia się na liście B2: B21 z pasującymi przypadkami, można to zrobić w następujący sposób:

1. Wybierz pustą komórkę.

2. Wprowadź wzór = SUMA (- DOKŁADNIE (B2: B20; E2)).

3. naciśnij Ctrl + Shift + Wchodzę aby zwrócić wynik zliczania.

Uwagi: W formule tablicowej
(1) B2: B20 to kolumna, w której będziesz liczyć duplikaty.
(2) E2 to komórka zawierająca określoną wartość, dla której chcesz policzyć liczbę wystąpień. Możesz zmienić odwołanie do komórki na wartość za pomocą cudzysłowów, mówi "Jabłko".

Czasami możesz chcieć policzyć duplikaty według dwóch lub więcej kryteriów. Możesz zastosować LICZBY funkcji, aby to zrobić.
Na przykład istnieje tabela sprzedaży owoców, jak pokazano poniżej. Tutaj musimy policzyć czasy powtórzeń jabłka, które zostało sprzedane w dniu 7, a kwota sprzedaży jest większa niż 5. Możesz policzyć duplikaty według tych kryteriów w następujący sposób:

1. Wybierz pustą komórkę.

2. Wprowadź wzór =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3, wciśnij Wchodzę aby uzyskać wynik liczenia.

Uwagi: W powyższym wzorze,
(1) B3: B20 to (pierwsza) kolumna z datą, a G4 to kryterium daty;
(2) C3: C20 to (druga) kolumna owoców, a G3 to kryteria dotyczące owoców;
(3) D3: D20 to (trzecia) kolumna kwoty, a "> 300" to kryteria ilościowe.
(4) Jeśli w tabeli jest więcej kolumn i kryteriów, możesz dodać odwołanie do kolumny i kryteria.

Aby policzyć wszystkie duplikaty w kolumnie z wyjątkiem pierwszych wystąpień, wykonaj następujące czynności:

1. Wybierz pustą komórkę obok kolumny.

2. Wprowadź wzór = JEŻELI (LICZ.JEŻELI ($ B $ 3: B3, B3)> 1, "TAK", ""), a następnie przeciągnij uchwyt autouzupełniania w dół, aby skopiować tę formułę do innych komórek.

Uwagi: W powyższym wzorze,
(1) 3 B $: B3 to zakres, w którym liczysz duplikaty. W $ B $ 3: B3, B3 zmieni się automatycznie po skopiowaniu formuły do ​​innych komórek.
(2) B3 jest pierwszą komórką w określonej kolumnie.
(3) Ta formuła zwróci TAK lub będzie puste. TAK oznacza, że ​​odpowiednia wartość jest zduplikowana, a pusta oznacza niepowtarzalność.

Następnie identyfikowane są wszystkie duplikaty w określonej kolumnie. Możemy policzyć wyniki formuły, aby uzyskać całkowitą liczbę duplikatów.

3. Wybierz pustą komórkę.

4. Wprowadź wzór = LICZ.JEŻELI (C3: C16; „TAK”)i naciśnij Wchodzę klawisz.

Uwagi: W powyższym wzorze,
(1) C3: C16 to zakres zastosowany przez nas do identyfikacji duplikatów w ostatnim kroku.
(2) TAK to wartość zwrócona przez ostatnią formułę.

Następnie otrzymujemy całkowitą liczbę zduplikowanych wartości w określonej kolumnie. Całkowita liczba duplikatów nie obejmuje pierwszych wystąpień.

Aby policzyć liczbę wszystkich duplikatów, w tym pierwszych wystąpień w programie Excel, możesz zastosować formułę tablicową, aby to osiągnąć.

1. Wybierz pustą komórkę.

2. Wprowadź wzór =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. naciśnij Ctrl + Shift + Wchodzę aby zwrócić wynik zliczania.

Uwagi: W powyższym wzorze, B3: B16 to określona kolumna, w której chcemy policzyć duplikaty, w tym pierwsze wystąpienia w obrębie.

Aby uprościć swoją pracę i uwolnić się od zapamiętywania długich, nudnych formuł, możesz wypróbować Wybierz Zduplikowane i unikalne komórki funkcja, zapewnia Kutools dla programu Excel, aby szybko policzyć liczbę zduplikowanych wartości na określonej liście lub w określonej kolumnie, z wyłączeniem lub z uwzględnieniem pierwszych wystąpień.

1. Wybierz kolumnę, w której chcesz policzyć liczbę zduplikowanych wartości, i kliknij Kutools > Wybierz > Wybierz Zduplikowane i unikalne komórki.

2. W oknie dialogowym Wybierz zduplikowane i unikalne komórki zaznacz opcję Duplikaty (z wyjątkiem pierwszego) or Wszystkie duplikaty (w tym pierwszy) w razie potrzeby i kliknij plik Ok przycisk.

3. Następnie wybierane są wszystkie zduplikowane wartości, w tym pierwsze wystąpienia lub wykluczające je, i jednocześnie pojawia się okno dialogowe pokazujące, ile komórek zostało zaznaczonych. Zobacz zrzut ekranu powyżej.

Mówi, że chcesz porównać dwie listy nazwisk i policzyć liczbę duplikatów między nimi, jak możesz szybko rozwiązać ten problem? Możemy to również zrobić za pomocą formuły w Excelu.

1. Wybierz pustą komórkę.

2. Wprowadź wzór = SUMPRODUCT (- (ISNUMBER (MATCH (B3: B12; D3: D18,0)))).

3, wciśnij Wchodzę klawisz.

Uwagi: W powyższym wzorze,
(1) B3: B12 to pierwsza kolumna nazw, w ramach której będziesz liczyć duplikaty.
(2) D3: D18 to druga kolumna nazw, na podstawie których będziesz liczyć duplikaty.

Alternatywnie możemy skorzystać z dodatku innej firmy, Kutools dla programu Excel, aby szybko policzyć całkowitą liczbę zduplikowanych komórek między dwiema kolumnami.

1. Kliknij Kutools > Wybierz > Wybierz Te same i różne komórki.

2. W oknie dialogowym Wybierz te same i różnice w komórkach
(1) Określ dwie kolumny w Znajdź wartości w i Zgodnie z pudełka oddzielnie.
(2) Sprawdź Pojedyncza komórka opcja.
(3) Sprawdź Te same wartości opcja.

4, Kliknij Ok przycisk.

Następnie zaznaczane są wszystkie zduplikowane komórki w pierwszej kolumnie, a jednocześnie okno dialogowe wyświetla monit i pokazuje, ile duplikatów komórek zostało zaznaczonych. Zobacz zrzut ekranu:

Uwagi: Ta funkcja zliczy całkowitą liczbę zduplikowanych wartości w kolumnie określonej w Znajdź wartości w w oknie dialogowym Wybierz te same i różne komórki. Jeśli chcesz zliczyć całkowitą liczbę zduplikowanych wartości w drugiej kolumnie, zastosuj Wybierz Te same i różne komórki funkcja ponownie, określając drugą kolumnę w Znajdź wartości w pudełko.

Możesz szybko policzyć każdą zduplikowaną wartość za pomocą formuły w następujący sposób:

1. Wybierz pustą komórkę.

2. Wprowadź wzór =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3, wciśnij Wchodzę aby uzyskać wynik liczenia.

Uwagi: W powyższym wzorze, C3: C19 to określona kolumna, dla której chcesz policzyć raz zduplikowaną wartość.

Podczas liczenia listy każdą zduplikowaną wartość można policzyć raz z pasującymi obserwacjami, możesz zastosować formułę tablicową, aby łatwo to zrobić w programie Excel.

1. Wybierz pustą komórkę.

2. Wprowadź formułę tablicową = SUMA (JEŻELI BŁĄD (1 / JEŻELI (C3: C19 <> "", CZĘSTOTLIWOŚĆ (JEŻELI (DOKŁADNE (C3: C19; TRANSPOZA (C3: C19))), PODAJ.RZĘD (ROW (C3: C19), RZĘD (C3: C19) ), „”), DOPASUJ (WIERSZ (C3: C19), WIERSZ (C3: C19))), 0), 0)).

3. naciśnij Ctrl + Shift + Wchodzę klucze razem, aby zwrócić wynik zliczania.

Uwagi: W powyższej formule tablicowej C3: C19 jest określoną kolumną, w której każdą serię duplikatów policzysz raz z pasującymi obserwacjami.

Jeśli Kutools dla programu Excel zainstalowany, możesz również zastosować jego Policz komórki o unikalnych wartościach funkcja szybkiego liczenia każdej serii zduplikowanych wartości w programie Excel.

1. Wybierz pustą komórkę.

2. Kliknij Kutools > Pomocnik formuły > Statystyczny > Policz komórki z unikalnymi wartościami (uwzględnij pierwszą zduplikowaną wartość).

3. W oknie dialogowym Pomocnik formuł określ kolumnę, w której będą liczyć duplikaty raz w pliku łodzie i kliknij przycisk Ok przycisk.

Następnie wynik zliczania jest natychmiast wypełniany w wybranej komórce.

Możemy zastosować Razem funkcja zliczania każdej serii zduplikowanych wartości w kolumnie w programie Excel.

1. Wybierz kolumnę, w której będziesz liczyć każdą serię zduplikowanych wartości, i kliknij Dane > Sortuj od A do Z or Sortuj od Z do A.

2. W wyświetlanym oknie dialogowym Ostrzeżenie o sortowaniu zaznacz Rozwiń wybór opcję i kliknij przycisk Sortuj przycisk.

Następnie zobaczysz, że wybór został posortowany według zduplikowanych wartości określonej kolumny.

3. Pozostaw zaznaczenie zaznaczone i kliknij Dane > Razem.

4. W oknie dialogowym Suma częściowa
(1) Wybierz określoną kolumnę z Przy każdej zmianie Lista rozwijana;
(2) Wybierz Liczyć z Zastosowanie funkcji Lista rozwijana;
(3) Zaznacz tylko określoną kolumnę w Dodaj sumę częściową do skrzynka na listy;
(4) Kliknij OK przycisk.

Następnie zobaczysz, że zliczana jest każda seria zduplikowanych wartości, a wynik zliczania jest dodawany pod każdą serią zduplikowanych wartości, patrz powyższy zrzut ekranu.

Możemy również utworzyć tabelę przestawną, aby szybko policzyć każdą serię zduplikowanych wartości w programie Excel.

1. Wybierz zakres zawierający określoną kolumnę i kliknij wstawka > Stół obrotowy.

2. W oknie dialogowym Utwórz tabelę przestawną określ miejsce, w którym zostanie umieszczona nowa tabela przestawna, i kliknij przycisk OK przycisk.

3. W okienku Pola tabeli przestawnej przeciągnij określoną kolumnę do obu Wydziwianie i Wartości Sekcje. Następnie zobaczysz, że każda seria zduplikowanych wartości w określonej kolumnie jest liczona zbiorczo. Zobacz zrzut ekranu:

Jeśli masz zainstalowany Kutools dla programu Excel już możesz zastosować jego łatwy w użyciu Zaawansowane wiersze łączenia funkcja szybkiego zliczania każdej serii zduplikowanych wartości w określonej kolumnie.

Uwagi: the Zaawansowane połączone wiersze Funkcja zmodyfikuje wybrany zakres i usunie wiersze na podstawie zduplikowanych wartości w określonej kolumnie klucza podstawowego. Aby zapisać swoje dane, zaleca się wykonanie kopii zapasowej lub skopiowanie danych w inne miejsce przed poniższymi operacjami.

1. Dodaj pustą kolumnę bezpośrednio do oryginalnego zakresu danych i nazwij nową kolumnę jako Liczyć.

2. Wybierz razem pierwotny zakres danych i nową kolumnę i kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączenia.

3. W oknie dialogowym Zaawansowane łączenie wierszy
(1) Kliknij, aby wybrać określoną kolumnę, w której będziesz liczyć każdą serię zduplikowanych wartości, i kliknij Główny klucz.
(2) Kliknij, aby zaznaczyć nową kolumnę (Liczba), a następnie kliknij Obliczać > Liczyć.
(3) W razie potrzeby określ kombinację lub typy obliczeń dla innych kolumn.
(4) Kliknij Ok przycisk.

Następnie zobaczysz, że każda zduplikowana wartość serii w określonej kolumnie jest liczona zbiorczo. Zobacz zrzut ekranu:

Mówi, że w kolumnie jest lista owoców. Na liście niektóre owoce pojawiają się wielokrotnie. Teraz musisz oznaczyć każdy duplikat owocu w kolejności, w jakiej się pojawia, jak możesz go rozwiązać? W tej sekcji przedstawimy formułę ułatwiającą wykonanie tego w programie Excel.

1. Dodaj pustą kolumnę bezpośrednio do oryginalnych danych.

2. Wprowadź wzór =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") w pierwszej komórce dodanej pustej kolumny.

3. Przeciągnij uchwyt autouzupełniania tej komórki z formułą, aby skopiować formułę do innych komórek.

Uwagi: W powyższym wzorze,
(1) 3 C $: 14 C $ to określona kolumna, w której chcesz policzyć zduplikowane wartości w kolejności.
(2) C3 jest pierwszą komórką w określonej kolumnie.
(3) Jeśli odpowiadająca wartość jest zduplikowana, ta formuła zwróci numer kolejny 1, 2, 3… w oparciu o kolejność pojawiania się; jeśli odpowiednia wartość jest unikalna, ta formuła zwróci wartość pustą.

Możesz zastosować Usuń duplikaty funkcja, aby bezpośrednio usunąć wszystkie zduplikowane wartości z wyjątkiem pierwszych wystąpień.

1. Wybierz kolumnę, w której chcesz usunąć wszystkie zduplikowane wartości z wyjątkiem pierwszych wystąpień.

2. Kliknij Dane > Usuń duplikaty.

3. W oknie dialogowym ostrzeżenia o usuwaniu duplikatów zaznacz opcję Kontynuuj z bieżącym wyborem opcję i kliknij przycisk Usuń duplikaty przycisk.

Tips: Aby usunąć wiersze na podstawie zduplikowanych wartości w zaznaczeniu, zaznacz opcję Rozwiń wybór opcja.

4. W oknie dialogowym Usuń duplikaty zaznacz tylko określoną kolumnę i kliknij przycisk OK przycisk.

Tips: Jeśli zaznaczyłeś Rozwiń wybór opcja w ostatnim kroku, wszystkie kolumny zostaną tutaj wymienione. Mimo to musisz sprawdzić tylko określoną kolumnę.

5. Następnie pojawia się okno dialogowe i pokazuje, ile zduplikowanych wartości zostało usuniętych. Kliknij OK przycisk, aby go zamknąć.

Możesz również zastosować Zaawansowany filtr funkcja, aby łatwo usunąć wszystkie zduplikowane wartości z określonej kolumny.

1. Kliknij Dane > Zaawansowane.

2. W oknie dialogowym Filtr zaawansowany
(1) Sprawdź Skopiuj do innej lokalizacji opcja;
(2) W Zakres listy wybierz określoną kolumnę, z której chcesz usunąć zduplikowane wartości;
(3) W Kopiuj do box, określ zakres, do którego wkleisz kolumnę;
(4) Zaznacz Tylko unikalne rekordy opcja.
(5) Kliknij OK przycisk.

Następnie zobaczysz, że określona kolumna została wklejona do określonego zakresu z usuniętymi wszystkimi zduplikowanymi wartościami z wyjątkiem pierwszych wystąpień. Zobacz zrzut ekranu:

Możesz również zastosować VBA, aby szybko usunąć zduplikowane wartości z wyjątkiem pierwszych wystąpień z kolumny w programie Excel.

1. naciśnij inny + F11 klucze, aby otworzyć okno Microsoft Visual Basic for Application.

2. Kliknij wstawka > Moduł, a następnie wklej poniższy kod VBA do nowego okna modułu.

Podłoże 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 Worksheet Dim xURgAddress Next As String On Error Resume xRg = Application.InputBox ("Wybierz zakres:", "Kutools for Excel", "",,,,, 8) Jeśli xRg to nic, wyjdź z podzestawu xURg = Intersect (xRg.Worksheet.UsedRange, xRg) Set 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 IsERror (xFRg)) Then If xFRg.Value <> "" And (Not IsError (xFRg)) Then For xFFNum = xFNum + 1 To xURg.Count Ustaw xFFRg = xURg.Item (xFFNum) If Not IsError (xFFRg) Then If xFFRg .Value = xFRg.Value Then xDc (xFFRg.Address) = "" End If End If Next End If End If Next xStr = "" xDc_keys = xDc.Keys For xI = 1 To UBound (xDc_keys) If xStr = "" Then xStr = xDc_keys (xI) UstawxURg = xWs.Range (xStr) Else xStr = xStr & "," & xDc_keys (xI) Ustaw xURg = Application.Union (xWs.Range (xDc_keys (xI)), xURg) End If Next Debug.Print xStr xWs.Activate xURg.Select Selection.Delete Shift: = xlUp xWs.Range (xURgAddress) .Select Application.ScreenUpdating = xBol End Sub

3. naciśnij F5 lub kliknij run przycisk, aby uruchomić ten VBA.

4. W wyskakującym oknie dialogowym określ zakres, z którego chcesz usunąć zduplikowane wartości, i kliknij OK przycisk.

Następnie wszystkie zduplikowane wartości z wyjątkiem pierwszych wystąpień są natychmiast usuwane z określonego zakresu.

Uwaga: w tym kodzie VBA rozróżniana jest wielkość liter.

Możemy podświetlić wszystkie zduplikowane wartości, w tym pierwsze wystąpienia w kolumnie lub na liście za pomocą warunkowej reguły formatowania, a następnie odfiltrować wszystkie zduplikowane wartości kolorem podświetlenia. Następnie możemy wybrać wszystkie odfiltrowane zduplikowane komórki, a następnie usunąć je zbiorczo.

1. Zastosuj formatowanie warunkowe, aby wyróżnić zduplikowane wartości. (Kliknij, aby zobaczyć, jak to zrobić)

2. Wybierz kolumnę, z której chcesz usunąć zduplikowane wartości (w tym pierwsze wystąpienia), i kliknij Dane > FILTRY.

3. Kliknij ikonę Filtruj  w nagłówku określonej kolumny. W menu rozwijanym wybierz Filtr według koloru, a następnie określ kolor podświetlenia z podmenu.

Następnie wszystkie zduplikowane wartości są odfiltrowywane.

4. Wybierz wszystkie przefiltrowane komórki, kliknij prawym przyciskiem myszy i wybierz Usuń wiersz z menu kontekstowego. W pojawiającym się oknie dialogowym potwierdzającym kliknij OK przycisk, aby przejść dalej.

5. Następnie wszystkie zduplikowane wartości są usuwane zbiorczo. Pozostaw wybraną listę filtrowaną i kliknij FILTRY > Dane ponownie, aby anulować filtr.

Do tej pory zobaczysz, że wszystkie zduplikowane komórki, w tym pierwsze wystąpienia, są usuwane zbiorczo i pozostają tylko unikalne wartości.

Uwagi: Ta metoda usunie wiersze na podstawie zduplikowanych wartości w określonej kolumnie.

Możemy również zastosować formułę, aby zidentyfikować zduplikowane wartości, w tym pierwsze wystąpienia w kolumnie pomocniczej, a następnie odfiltrować zduplikowane wartości według wyników formuły, a na koniec zbiorczo usunąć te odfiltrowane zduplikowane wartości.

1. Dodaj kolumnę pomocniczą obok określonej kolumny, wprowadź formułę = LICZ.JEŻELI (3 mld USD: 11 mld USD, B3) do pierwszej komórki kolumny pomocnika, a następnie przeciągnij uchwyt Autouzupełniania w dół, aby skopiować tę formułę do innych komórek. Zobacz zrzut ekranu:

Note: W powyższym wzorze, 3 B $: 11 B $ to określona kolumna, z której usuniesz zduplikowane wartości, a B3 jest pierwszą komórką w określonej kolumnie.

2. Wybierz kolumnę pomocniczą i kliknij Dane > FILTRY.

3. Kliknij ikonę filtra  w nagłówku kolumny pomocnika, a następnie w menu rozwijanym zaznacz wszystkie wartości oprócz 1i kliknij przycisk OK przycisk. Zobacz zrzut ekranu:

4. Teraz wszystkie zduplikowane wartości są odfiltrowywane. Wybierz odfiltrowane komórki w kolumnie pomocnika, kliknij prawym przyciskiem myszy i wybierz Usuń wiersz w menu kontekstowym.

5. W wyskakującym oknie dialogowym ponownego potwierdzenia kliknij OK przycisk, aby przejść dalej.

6. Teraz wszystkie zduplikowane wartości i ich wiersze są usuwane zbiorczo. Śmiało, aby kliknąć Dane > FILTRY ponownie, aby anulować filtr.

Następnie zobaczysz, że wszystkie zduplikowane wartości, w tym pierwsze wystąpienia, są usuwane zbiorczo.

Jeśli Kutools dla programu Excel zainstalowany, możesz również zastosować jego Wybierz Zduplikowane i unikalne komórki funkcja szybkiego wybierania i usuwania zduplikowanych wartości, w tym lub z łatwością z wyłączeniem pierwszych wystąpień w programie Excel.

1. Wybierz kolumnę, z której chcesz usunąć duplikaty.

2. Kliknij Kutools > Wybierz > Wybierz Zduplikowane i unikalne komórki.

3. W oknie dialogowym Wybierz zduplikowane i unikalne komórki zaznacz opcję Wszystkie duplikaty (w tym pierwszy) opcję i kliknij przycisk Ok przycisk.

Uwagi:
(1) Aby wybrać i usunąć zduplikowane wartości z wyłączeniem pierwszych wystąpień, zaznacz Duplikaty (z wyjątkiem pierwszego) opcja.
(2) Aby wybrać i usunąć wiersze na podstawie zduplikowanych wartości w określonej kolumnie, zaznacz Wybierz całe wiersze opcja.
(3) Aby wybrać i usunąć zduplikowane wartości z pasującymi obserwacjami, zaznacz Rozróżnianie wielkości liter opcja.
(4) Aby zaznaczyć, zaznaczyć i usunąć zduplikowane komórki lub wiersze, zaznacz Wypełnij kolor tła or Wypełnij kolor czcionki opcje i określ kolory wypełnienia lub czcionki według potrzeb.

4. Następnie pojawi się okno dialogowe i pokaże, ile komórek zostało zaznaczonych, kliknij OK przycisk, aby go zamknąć.

5. Kliknij prawym przyciskiem myszy wybrane komórki i wybierz Usuń z menu kontekstowego.

6. W następnym oknie dialogowym Usuń zaznacz plik Przesuń komórki w górę opcję i kliknij przycisk OK przycisk.

Do tej pory wszystkie zduplikowane wartości, w tym pierwsze wystąpienia, zostały zbiorczo usunięte.

W większości przypadków identyfikujemy zduplikowane wartości w kolumnie, a następnie usuwamy całe wiersze według zduplikowanych wartości. Szczerze mówiąc, ta operacja jest dość podobna do usuwania zduplikowanych wartości z jednej kolumny. W rezultacie możemy zastosować podobne rozwiązania do usuwania wierszy na podstawie duplikatów w określonej kolumnie.

Pierwsza metoda polega na zastosowaniu wbudowanego Usuń duplikaty funkcja usuwania wierszy przez duplikaty w określonej kolumnie. Po prostu wybierz zakres, z którego chcesz usunąć wiersze, kliknij Dane > Usuń duplikaty aby włączyć tę funkcję, zaznacz tylko określoną kolumnę w oknie dialogowym Usuń duplikaty, a następnie kliknij OK aby zakończyć operację usuwania.

Możemy również zastosować funkcję Formatowanie warunkowe i filtrowanie, aby usunąć wiersze na podstawie zduplikowanych wartości w określonej kolumnie. Przede wszystkim zaznacz wiersze oparte na zduplikowanych wartościach w określonej kolumnie za pomocą reguły warunkowej (kliknij, aby zobaczyć, jak to zrobić). Po drugie, przefiltruj zakres według koloru. Po trzecie, łatwo usuń wszystkie odfiltrowane wiersze. W końcu wyczyść lub anuluj filtr, a zobaczysz tylko wiersze z unikalnymi wartościami w określonej kolumnie.

Możesz też dodać kolumnę pomocniczą i zastosować formułę = LICZ.JEŻELI (3 C $: 21 C $, C3) do identyfikacji duplikatów w określonej kolumnie. Następnie odfiltruj liczby większe niż 1 w kolumnie pomocnika i łatwo usuń wszystkie odfiltrowane wiersze. Po wyczyszczeniu filtru zobaczysz wiersze z unikalnymi wartościami w określonej kolumnie, które pozostaną tylko.

Dodatek innej firmy Kutools dla programu Excel przynosi również niezwykle przydatną funkcję, Wybierz Zduplikowane i unikalne komórki, aby szybko wybrać wiersze na podstawie zduplikowanych wartości w określonej kolumnie, a następnie można szybko usunąć te wybrane wiersze, klikając menu prawym przyciskiem myszy.

Kutools dla Excela Zaawansowane wiersze łączenia Funkcja może również szybko usuwać wiersze na podstawie zduplikowanych wartości w określonej kolumnie klucza podstawowego.

Możemy dodać kolumnę pomocniczą i zastosować formułę, aby zidentyfikować zduplikowane wartości między dwiema kolumnami, a następnie łatwo filtrować i usuwać zduplikowane wartości.

1. Dodaj pustą kolumnę obok określonej kolumny, z której chcesz usunąć zduplikowane wartości.

2. W pierwszej komórce kolumny pomocnika (z wyjątkiem komórki nagłówka) wpisz formułę = JEŻELI (CZY.BŁĄD (MATCH (C2; A $ 2: 13,0 A $)); „Unikalne”; „Duplikat”), a następnie przeciągnij uchwyt autouzupełniania w dół, aby skopiować formułę do innych komórek.

Uwagi: W powyższym wzorze,
(1) C2 to pierwsza komórka w określonej kolumnie, z której usuniesz zduplikowane wartości;
(2) $ A $ 2: $ A $ 13 to druga kolumna, z którą musimy porównać.
(3) Ta formuła zwróci Duplikat jeśli odpowiednia wartość jest zduplikowana z wartościami w drugiej kolumnie i zwraca Wyjątkowy jeśli różni się od wartości w drugiej kolumnie.

3. Wybierz kolumnę pomocniczą i kliknij Dane > FILTRY.

4. Kliknij ikonę filtra  w nagłówku kolumny pomocnika, a następnie w menu rozwijanym zaznacz tylko Duplikati kliknij przycisk OK przycisk.

5. Teraz wszystkie zduplikowane wartości są odfiltrowywane. Wybierz przefiltrowane komórki, kliknij prawym przyciskiem myszy i wybierz Usuń wiersz z menu kontekstowego. Następnie kliknij OK w wyskakującym oknie dialogowym potwierdzającym.

6. Następnie wszystkie zduplikowane wartości są usuwane z określonej kolumny. Śmiało, aby kliknąć Dane > FILTRY ponownie, aby anulować filtr.

Wtedy zobaczysz, że w określonej kolumnie pozostały tylko unikalne wartości. W razie potrzeby możesz usunąć kolumnę pomocniczą.

Uwagi: Ta metoda usunie całe wiersze na podstawie zduplikowanych wartości w określonej kolumnie.

Jeśli Kutools dla programu Excel zainstalowany, możesz korzystać z jego niesamowitego Wybierz Te same i różne komórki funkcja, aby szybko wybrać zduplikowane wartości między dwiema kolumnami, a następnie łatwo je usunąć.

1. Kliknij Kutools > Wybierz > Wybierz Te same i różne komórki aby włączyć tę funkcję.

2. W oknie dialogowym Wybierz takie same i różne komórki określ obie kolumny w Znajdź wartości w i Zgodnie z osobno, zaznacz Pojedyncza komórka i Te same wartości opcje i kliknij Ok przycisk. Zobacz zrzut ekranu:

3. Następnie wszystkie zduplikowane wartości wynoszą dwie kolumny w pierwszej kolumnie (kolumna określona w Znajdź wartość w pudełko). I kliknij OK w wyskakującym oknie dialogowym.

4. Możesz nacisnąć Usuń , aby bezpośrednio usunąć te zduplikowane wartości lub kliknij je prawym przyciskiem myszy i wybierz Usuń z menu kontekstowego.