Jak Znaleźć Duplikaty w Excelu. Jak Zaznaczyć Duplikaty.

Jak znaleźć duplikaty w Excelu w szybki i prosty sposób? Duplikaty danych w Excelu to komórki, które zawierają takie same wartości (liczby, daty, tekst), jak inne komórki. Zazwyczaj znajdują się one w tej samej kolumnie lub tabeli. Niekiedy ich występowanie niekorzystnie wpływa na czytelność danych i możliwość ich analizy, dlatego w tym artykule omawiam, jak znaleźć duplikaty w Excelu, wykorzystując do tego gotowe formuły i funkcje do zaznaczenia powtórek w Excelu. Z artykułu dowiesz się również, jak zablokować możliwość wpisywania w arkuszu powtórzonych wartości oraz jak wprowadzić informacje o pojawieniu się duplikatów.

  1. Czym są duplikaty w Excelu
  2. Jak znaleźć duplikaty w Excelu dla pojedynczych komórek za pomocą formatowania warunkowego
  3. Jak znaleźć duplikaty w Excelu i podświetlić cały wiersz
  4. Jak znaleźć duplikaty w Excelu? – zaznaczanie kolorem duplikatu następnego
  5. Jak zablokować wprowadzanie duplikatów w Excelu
  6. Jak ustawić w Excelu powiadomienie o wprowadzeniu duplikatu

Czym są duplikaty w Excelu

Duplikat to komórka, która zawiera identyczny tekst, liczbę lub datę, co inna komórka. Jako duplikat uznajemy powtarzające się wartości w obrębie jednej tabeli lub zakresu danych. Wystarczy, że dwie komórki mają identyczną zawartość, ale możliwe jest również istnienie większej ich liczby – wszystkie w takim przypadku są uznawane za duplikaty.

Jak znaleźć duplikaty w Excelu dla pojedynczych komórek za pomocą formatowania warunkowego

Formatowanie warunkowe pozwala podkreślić wzorce i trendy w danych, sprawiając, że stają się one bardziej zauważalne. To narzędzie można zastosować do określonego zakresu komórek, zarówno poprzez zaznaczenie, jak i poprzez odwołanie się do nazwanego zakresu. Formatowanie warunkowe można również wykorzystać do znalezienia duplikatów w Excelu.

Aby znaleźć duplikaty w Excelu za pomocą formatowania warunkowego:

  1. w pierwszej kolejności zaznacz zakres (komórki), które mają zostać sprawdzone pod kątem duplikatów. Zaznaczam zakres w kolumnie C.
Obraz1

2. Następnie z karty narzędzia główne wybierz Formatowanie warunkowe > Reguły wyróżniania komórek > Duplikujące się wartości

Obraz2

3. Zduplikowane wartości w zaznaczonym zakresie zostaną automatycznie podświetlone w innym kolorze. Po prawej stronie pojawi się natomiast okno, które pozwala na wybór koloru (stylu formatowania), za pomocą którego będą oznaczane duplikaty. Po wybraniu stylu formatowania zduplikowanych komórek wystarczy nacisnąć przycisk OK

Obraz3

Wykonane formatowanie działa na bieżąco. Oznacza to, że jeśli zmienimy wartość na taką, jaka występuje już w naszym zakresie, to wartości zduplikowane zostaną automatycznie zaznaczone we wskazanym kolorze.

Dla przykładu zmieniłem wartość komórki C6 na taką samą co C4 i te dwie komórki zostały podświetlone na czerwono.

Obraz4

Jak znaleźć duplikaty w Excelu i podświetlić cały wiersz

Oprócz podświetlenia pojedynczych komórek ze zduplikowanymi wartościami możemy zaznaczyć cały wiersz dla tych duplikatów znajdujących się w zaznaczonym zakresie danych.

Aby to lepiej zobrazować. Przykładowo z poniższych danych (na czerwono oznaczono duplikaty):

Obraz5

Osiągamy taki efekt:

Obraz6

Co zrobić, aby zaznaczyć cały wiersz gdy szukamy duplikujących się komórek?

  1. W komórkach obok zakresu danych — wprowadzamy pomocniczo funkcję LICZ.JEŻELI

Funkcję LICZ.JEŻELI wpisujemy w komórce F3 i jest ona odpowiednikiem komórki C3

  • Pierwszy argument C3:C10 – jest zakresem wszystkich danych, które będziemy przeszukiwać
  • Drugi argument C3 – wskazuje jakiej wartości szukamy w tym przypadku będzie to data 02.02.2022 znajdująca się w tej komórce.
Obraz7

Funkcja zwróci nam wartość 2, ponieważ w podanym zakresie danych występują dwie takie same daty 02.02.2022r.

Funkcję będziemy chcieli zastosować też do innych komórek, wobec tego zablokujmy komórki przed wykonaniem kopiowania za pomocą wartości bezwzględnych – znak $.

Obraz8 1

Po przekopiowaniu funkcji dla pozostałych komórek otrzymujemy taki wynik:

Obraz9

Jak widzisz, wszystkie duplikaty otrzymały wartość 2, a pozycje unikalne wartość 1.

Formatowanie warunkowe powinno zwracać informację w postaci PRAWDA lub FAŁSZ. Aby uzyskać taki efekt zmodyfikuję funkcję dodając porównanie >1

Obraz10

Dzięki temu otrzymaliśmy PRAWDA gdy wartość dla komórki jest zduplikowana w zakresie i FAŁSZ gdy wartość jest unikalna.

Obraz11

Funkcję będę chciał przekopiować w prawą stronę – dlatego muszę zablokować przesunięcie komórki C3 w kolumnie ($C3)

Obraz12

Po przekopiowaniu funkcji w dół i w prawo otrzymałem zakres danych PRAWDA/FAŁSZ, który będzie odpowiadał podświetleniu komórek dla zakresu C3:D10

Obraz13

Stworzona formuła, która będzie podświetlała komórki dla których wartość logiczna jest prawdziwa PRAWDA

Teraz pozostaje wykonać formatowanie warunkowe na podstawie utworzonej formuły:

  1. Skopiuj utworzoną formułę

=LICZ.JEŻELI($C$3:$C$10;$C3)>1

  1. Zaznacz zakres danych do sformatowania
Obraz14

2. Następnie wybierz z karty Narzędzia główne > Formatowanie warunkowe > Nowa reguła.

Obraz15

3. Pojawi się okno, w którym zaznacz typ Użyj formuły do określenia komórek, które należy sformatować. W polu Formatuj wartości, dla których następująca formuła jest prawdziwa, wklej skopiowaną poprzednio formułę.

Obraz16

4. Na tym etapie możesz również zmienić sposób podświetlenia komórki, klikając w przycisk Formatuj… i następnie wybierając zakładkę Wypełnienie ustawiamy pożądany kolor. Po czym zatwierdzamy całość przyciskiem OK.

Po wykonaniu tych czynności podświetlone zostaną całe wiersze gdy dane będą zduplikowane.

Obraz17 1

Funkcja działa na bieżąco, natomiast jeśli chcesz dopisywać następne wartości poniżej, to powinieneś z zakresu utworzyć tabele. W tym celu zaznacz obszar zakresu obejmujący w przykładzie zarówno Datę jak i Miejscowości, a następnie użyj skrótu klawiszowego CTRL+T.  Pojawi się okno Utwórz tabelę, w którym kliknij OK.

Obraz18

Dzięki temu zastosowane formatowanie będzie automatycznie działało także dla kolejnych wpisywanych pozycji.

Obraz19

Jak znaleźć duplikaty w Excelu? – zaznaczanie kolorem duplikatu następnego

Skoro już wiesz, jak znaleźć duplikujące się komórki w Excelu warto, abyś wiedział, jak podświetlić duplikat następny.

Zaznaczanie kolorem duplikatu następnego pozwala na wyróżnienie danych, które występują po raz drugi oraz kolejny. Dzięki tej opcji analizowanie danych pod kątem występujących błędów jest o wiele łatwiejsze, a sama ich prezentacja czytelniejsza.

W naszym przykładzie będziemy chcieli podświetlić drugie i każde kolejne wystąpienie daty, która już wystąpiła wcześniej. Dane przeglądamy od góry do dołu. Finalnie uzyskując taki efekt:

Obraz20

Aby uzyskać zaznaczanie duplikatu koloru następnego, ponownie skorzystamy z funkcji LICZ.JEŻELI, którą wprowadzimy w komórce F3:

=LICZ.JEŻELI($C$3:$C$3;$C3)>1

  1. Funkcja LICZ.JEŻELI zlicza ilość wystąpień wartości komórki C3 tj. 02.02.2022 w zakresie komórek C3:C3, czyli 1 i tą wartość ma być większa od 1.

 Dla tej komórki formuła zwróci wartość FAŁSZ, ponieważ wynik funkcji LICZ.JEŻELI nie jest większy od 1.

Obraz21

2. Następnie skopiuj formułę dla pozostałych pozycji.

Obraz22

Zobacz, że dzięki zablokowaniu pierwszej komórki zakresu $C$3 zakres danych objętych sprawdzaniem zwiększa się wraz z położeniem wiersza. Dzięki temu wartość logiczna PRAWDA występuje tylko dla drugiego i każdego następnego wystąpienia.

3. Teraz pozostaje nam tak jak w poprzednim przykładzie wprowadzić tę formułę do reguły formatowania warunkowego:

Obraz23

Po zatwierdzeniu reguły formatowania uzyskamy efekt jak poniżej:

Obraz24

Jak zablokować wprowadzanie duplikatów w Excelu?

W przypadku powtarzających się danych warto nie tylko wiedzieć, jak znaleźć duplikaty w Excelu, ale także jak zablokować możliwość ich wprowadzania.

Zablokowanie możliwości wprowadzania duplikatów w Excelu pozwala na uniknięcie wprowadzenia błędnych danych lub danych, które nie powinny zostać wprowadzone w określonej postaci.

Przejdźmy do przykładu (jak z poprzedniego zadania) i zablokujmy możliwość wprowadzania daty, która już występuje w naszej tabeli danych.

W tym celu:

  1. Przekopiuj formułę, która sprawdza, czy data występuje kolejny raz w zakresie danych

=LICZ.JEŻELI($C$3:$C$3;$C3)>1

Obraz25

2. Zaznacz zakres danych, na który ma zostać nałożona blokada poprawności. W naszym przykładzie zakres C3:D12

3. Przejdź do karty Dane > Narzędzia danych > Poprawność danych

Obraz26

4. Otworzy się okno Sprawdzanie poprawności danych. Z rozwijanej listy kryteriów poprawności jako Dozwolone wybierz Niestandardowe, a następnie w polu Formuła wprowadź skopiowaną wcześniej formułę i zmodyfikuj ją, aby tym razem dopuszczała wartości tj. zwracała PRAWDA gdy wartości będę mniejsze lub równe 1 (takie wartości będą dopuszczone do wprowadzenia).

=LICZ.JEŻELI($C$3:C3;$C3)<=1.

Obraz27

5. W tym samym oknie przejdź do zakładki Alert o błędzie i w oknie Tytuł wpisz treść, jaka ma pojawiać się po wprowadzeniu zduplikowanych danych, np. Duplikat. W polu Komunikat o błędzie wpisz natomiast instrukcje dla użytkowników arkusza, którym wyświetli się alert o wprowadzeniu duplikatu. Np. “Popraw błąd”. Na koniec kliknij OK.

Obraz28

Zastosowane ustawienia sprawią, że wprowadzenie duplikatów do arkusza w danym zakresie nie będzie już możliwe. Przy próbie ich wprowadzenia pojawi się z kolei komunikat.

W poniższym przykładzie wprowadziłem w kolejnej komórce datę 03.02.2022, Excel nie pozwolił na zatwierdzenie tej daty w komórce.

Obraz29
Obraz30
Obraz31

Jak ustawić w Excelu powiadomienie o wprowadzeniu duplikatu?

W poprzednim dziale pokazałem, jak zablokować wprowadzenie duplikującej się wartości. Natomiast możemy też pozwolić na duplikat, ale poinformować, że taki duplikat się pojawił.

Aby ustawić tę opcję dla poprzedniego przykładu:

  1. przejdź ponownie do okna Sprawdzanie poprawności danych tj. karta Dane > Narzędzia danych > Poprawność danych. Zaznacz Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami.
Obraz32

2. Następnie przejdź do zakładki Alert o błędzie i z rozwijanej listy Styl: wybierz Informacje. W polu Komunikat o błędzie możesz ustawić treść informacji dla użytkownika np. Popraw duplikat. Wprowadzone zmiany zatwierdź przyciskiem OK.

Obraz33

Po zastosowaniu powyższych ustawień możliwość wprowadzania zduplikowanych wartości zostanie odblokowana. Po ich wpisaniu pojawi się natomiast wyłącznie komunikat.

Obraz34

Po wciśnięciu przycisku OK wprowadzanie danych w komórce nadal będzie jednak możliwe. Przy czym, jeśli będą one duplikatem, zostaną podświetlone kolorem.

Tutaj pobierz plik do tego ćwiczenia:

plik do pobrania

Kurs Excel

Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.

kurs excel logo

Podobne wpisy