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.
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:
w pierwszej kolejności zaznacz zakres (komórki), które mają zostać sprawdzone pod kątem duplikatów. Zaznaczam zakres w kolumnie C.
2. Następnie z karty narzędzia główne wybierz Formatowanie warunkowe > Reguły wyróżniania komórek > Duplikujące się wartości
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
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.
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):
Osiągamy taki efekt:
Co zrobić, aby zaznaczyć cały wiersz gdy szukamy duplikujących się komórek?
W komórkach obok zakresu danych — wprowadzamy pomocniczo funkcję LICZ.JEŻELI
Funkcja LICZ.JEŻELI zlicza ilość komórek o określonej wartości. Pierwszym argumentem jest zakres komórek a drugim, jakiej wartości szukamy.
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.
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 $.
Teraz pozostaje wykonać formatowanie warunkowe na podstawie utworzonej formuły:
Skopiuj utworzoną formułę
=LICZ.JEŻELI($C$3:$C$10;$C3)>1
Zaznacz zakres danych do sformatowania
2. Następnie wybierz z karty Narzędzia główne > Formatowanie warunkowe > Nowa reguła.
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łę.
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.
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.
Dzięki temu zastosowane formatowanie będzie automatycznie działało także dla kolejnych wpisywanych pozycji.
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:
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
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.
2. Następnie skopiuj formułę dla pozostałych pozycji.
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:
Po zatwierdzeniu reguły formatowania uzyskamy efekt jak poniżej:
TIP: Wykonując formatowanie warunkowe na podstawie formuł – twórz tabelę zastępczą PRAWDA/FAŁSZ dzięki temu będziesz miał pewność, że uzyskasz pożądany efekt podświetlenia.
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:
Przekopiuj formułę, która sprawdza, czy data występuje kolejny raz w zakresie danych
=LICZ.JEŻELI($C$3:$C$3;$C3)>1
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
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.
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.
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.
TIP: Aby zakreślić duplikujące się wartości, wybierz opcję Excela Zakreśl nieprawidłowe dane
Dane które są niezgodne z regułą poprawności danych zostaną zakreślone czerwoną elipsą.
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:
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.
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.
Po zastosowaniu powyższych ustawień możliwość wprowadzania zduplikowanych wartości zostanie odblokowana. Po ich wpisaniu pojawi się natomiast wyłącznie komunikat.
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:
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.
Listy rozwijalne w Excelu zabezpieczają przed błędami przy wpisywaniu danych w komórkach oraz zaoszczędzają czas potrzebny na wprowadzenie danych. W tym nagraniu przedstawię, jak wykonać listę rozwijalną w Excelu a do tego dodam kilka przydatnych sztuczek związanych z listami rozwijalnymi.
Jeśli szukasz sposobu, by doskonalić swoje umiejętności w programie Excel, to ten film jest właśnie dla Ciebie! „Excel – Zadania z Rozwiązaniami” to krok po kroku wprowadzenie do rozwiązywania różnorodnych problemów, z jakimi można spotkać się na co dzień, pracując z danymi w Excelu. Podczas trwania filmu poznasz przydatne funkcje oraz techniki, które z pewnością ułatwią Ci pracę z arkuszami kalkulacyjnymi.
Formatowanie komórek Excela w trybie niestandardowym jest sposobem wyświetlana danych w komórce, natomiast niekoniecznie jest wartością tej komórki.
Wartość komórki zawsze wyświetlona będzie w pasku formuły natomiast sposób jej wyświetlenia może być bardzo różny na przykład liczbę 44800,5 możemy wyświetlić jako liczbę całkowitą, liczbę z dokładnością do 2 miejsc po przecinku, wprowadzić separator tysiąca, wyświetlić tą liczbę jako datę, zmienić jej domyślny kolor dla wartości dodatniej inny dla wartości ujemnej,
czy nawet zmienić wyświetlaną liczbę na inną liczbę przy zachowaniu tej samej wartości. W tym nagraniu nauczysz się formatowania niestandardowego dla liczb.
Najlepsze triki w Excelu. Odkryj 5 niezwykłych trików w programie Excel, które mogą znacząco usprawnić Twoją pracę. Dowiedz się, jak wykorzystać niestandardowe skróty klawiaturowe, głęboko ukryć arkusze czy szybko znajdować różnice w danych. Poznaj także sposób na zbiorcze wklejanie danych oraz tworzenie dwóch nagłówków w jednej komórce. Zwiększ swoją efektywność w Excelu już dziś!
W tym nagraniu pokazuję jak znaleźć duplikaty w dwóch kolumnach, oraz jak zablokować duplikaty dla wielu kolumn. Zastosowana w filmie formuła Licz.warunki(), liczy duplikaty dla wskazanego zakresu i zaznacza powtarzające się dane w Excelu.
Dzięki wprowadzeniu formatowania warunkowego dla minimum dwóch warunków możemy sprawdzić ile jest duplikatów w wielu kolumnach, takie duplikaty możemy oznaczyć, lub usunąć po po sprawdzeniu.
Wyszukiwanie duplikatów w Excelu jest proste, bardziej skomplikowane jest podświetlenie tylko drugiego wystąpienia duplikatu dla wskazanych kryteriów.
Film przedstawia też sposób na to jak zablokować duplikaty dla wielu kolumn i wyświetlić informację o pojawieniu się duplikatu np. na dwóch kolumnach.
Jeśli standardowe zaznaczenie duplikatów nie działa tobie poprawie, obejżyj ten film powinien pomóc rozwiązać problem ze znalezieniem i oznaczeniem duplikatów dla wielu kolumn.
Co robisz w przypadku gdy tabela z której chcesz pobrać dane za pomocą funkcji Wyszukaj Pionowo ma kilka kolumn, wprowadzasz numery kolumn ręcznie ? Nie rób tego w tym filmie dowiesz się jak zautomatyzować uzupełnianie numerów kolumn i wyników funkcji wyszukaj pionowo.
