Menu

Office

Jak zaznaczyć duplikaty w Excelu?

Redakcja ithings.pl Publikacja:

Laptop z otwartym arkuszem kalkulacyjnym

Najszybciej zaznaczysz duplikaty w Excelu przez formatowanie warunkowe. Zaznacz zakres danych, wybierz Narzędzia główne → Formatowanie warunkowe → Reguły wyróżniania komórek → Duplikujące się wartości, a następnie ustaw kolor wyróżnienia.

Jak najszybciej zaznaczyć duplikaty w Excelu?

Ta metoda podświetla każdą wartość, która występuje w zaznaczonym zakresie co najmniej dwa razy. Nie usuwa danych, lecz oznacza je kolorem.

  1. Zaznacz kolumnę lub zakres komórek, w którym chcesz znaleźć powtórzenia.
  2. Otwórz kartę Narzędzia główne i wybierz Formatowanie warunkowe.
  3. Przejdź do Reguły wyróżniania komórek → Duplikujące się wartości.
  4. Wybierz sposób formatowania, na przykład czerwone wypełnienie, i kliknij OK.

Reguła działa na bieżąco. Gdy zmienisz wartość na taką, która już występuje w zakresie, Excel automatycznie wyróżni oba wystąpienia.

Metoda Kiedy stosować Złożoność
Duplikujące się wartości Gdy chcesz szybko zaznaczyć powtórzenia w jednej kolumnie Niska
LICZ.JEŻELI w formule Gdy trzeba wyróżnić całe wiersze lub tylko kolejne wystąpienia Średnia
Poprawność danych Gdy chcesz ostrzegać przed wpisaniem duplikatu Średnia

Jak zaznaczyć cały wiersz z duplikatem?

Gotowa reguła zaznacza zwykle komórki z powtarzającą się wartością. Jeśli chcesz podświetlić cały wiersz, utwórz regułę opartą na formule.

Załóżmy, że sprawdzane dane znajdują się w kolumnie C, od wiersza 3 do 10. Formuła dla reguły formatowania warunkowego będzie wyglądać tak:

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

Funkcja LICZ.JEŻELI sprawdza, ile razy wartość z bieżącego wiersza pojawia się w całym zakresie. Wynik większy od 1 oznacza duplikat.

  1. Zaznacz cały zakres, który ma być kolorowany, na przykład A3:D10. Zacznij od jego lewego górnego rogu.
  2. Wybierz Narzędzia główne → Formatowanie warunkowe → Nowa reguła.
  3. Kliknij opcję Użyj formuły do określenia komórek, które należy sformatować.
  4. Wpisz formułę =LICZ.JEŻELI($C$3:$C$10;$C3)>1, wybierz format i zatwierdź regułę.

Znaki dolara mają tu konkretne zadanie. $C$3:$C$10 blokuje całą kolumnę i zakres, aby nie przesuwały się podczas sprawdzania kolejnych komórek. Zapis $C3 blokuje kolumnę C, ale pozostawia zmienny numer wiersza. Dzięki temu Excel analizuje wartość z każdego kolejnego wiersza, a reguła może objąć kilka kolumn.

Jeśli dane będą dopisywane, zamień zakres na tabelę skrótem Ctrl+T. Tabela zwykle rozszerza istniejące formatowanie warunkowe na nowe wiersze.

Jak zaznaczyć tylko drugie i kolejne wystąpienie?

Jeśli pierwsza wartość ma pozostać bez koloru, zastosuj zakres, który zaczyna się zawsze w tym samym miejscu, ale kończy w bieżącym wierszu:

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

W pierwszym wierszu Excel sprawdza tylko jedną komórkę, więc wynik nie może być większy od 1. W następnym wierszu sprawdzany zakres obejmuje już dwie komórki. W ten sposób kolor otrzymują dopiero drugie i kolejne wystąpienia.

Jak zaznaczyć duplikat będący parą wartości?

Czasem pojedyncza wartość może powtarzać się prawidłowo. Duplikatem jest dopiero zgodność dwóch kolumn, na przykład tej samej daty i tego samego samochodu. Wtedy użyj funkcji LICZ.WARUNKI:

=LICZ.WARUNKI($B$3:$B$13;$B3;$C$3:$C$13;$C3)>1

Formuła sprawdza, ile wierszy ma jednocześnie tę samą wartość w kolumnie B i w kolumnie C. Znak $ blokuje zakresy, natomiast numery wierszy pozostają zmienne. Gdy para ma obejmować także kolumnę D, dopisz kolejne kryterium:

=LICZ.WARUNKI($B$3:$B$13;$B3;$C$3:$C$13;$C3;$D$3:$D$13;$D3)>1

Aby oznaczyć tylko drugie i kolejne wystąpienie pary, zablokuj początek zakresów, ale nie ich koniec:

=LICZ.WARUNKI($B$3:$B3;$B3;$C$3:$C3;$C3)>1

Jak zablokować wpisywanie duplikatów?

Do kontroli danych służy funkcja Poprawność danych. Możesz dzięki niej całkowicie zablokować powtórzoną wartość albo tylko wyświetlać ostrzeżenie.

  1. Zaznacz zakres, dla którego ma działać kontrola.
  2. Przejdź do Dane → Poprawność danych.
  3. W polu Dozwolone wybierz Niestandardowe.
  4. Wpisz formułę dopuszczającą tylko pierwsze wystąpienie, na przykład =LICZ.JEŻELI($C$3:C3;$C3)<=1.
  5. Na karcie Alert o błędzie wpisz tytuł i komunikat, a następnie wybierz styl Zatrzymaj lub Informacje.

Styl Zatrzymaj nie pozwala zatwierdzić duplikatu. Styl Informacje pokazuje komunikat, ale użytkownik może mimo to zaakceptować wpis. To drugie rozwiązanie pasuje do sytuacji, w których powtórzenie czasem jest uzasadnione.

Przy dwóch kolumnach użyj odpowiednio formuły:

=LICZ.WARUNKI($B$3:B3;$B3;$C$3:C3;$C3)<=1

Poprawność danych może nie zareagować na dane wklejone ze schowka. Dlatego przy ważnych zestawieniach warto pozostawić także formatowanie warunkowe, które pokaże duplikaty już obecne w arkuszu.

Dlaczego Excel nie widzi duplikatu?

Excel porównuje zawartość komórek, a nie zawsze sam wygląd tekstu. Najczęstsze przyczyny problemów to:

  • Spacja na początku albo końcu tekstu sprawia, że na przykład „Kraków” i „Kraków ” są różnymi wartościami.
  • Liczba zapisana jako tekst może być traktowana inaczej niż liczba zapisana w standardowym formacie liczbowym.
  • Daty mogą mieć różne wartości wewnętrzne, nawet jeśli wyglądają tak samo po zastosowaniu formatowania.
  • Niewłaściwie zaznaczony zakres może pomijać część danych albo obejmować nieodpowiednią kolumnę.

Sprawdź też adresowanie formuły. Zakres zablokowany jako $A$1:$A$10 pozostaje stały, zapis $A1 blokuje kolumnę, a A$1 blokuje wiersz. Pomylenie tych zapisów często powoduje, że reguła koloruje niewłaściwe komórki.

Do samego oznaczania użyj formatowania warunkowego. Gdy potrzebujesz kontroli nad całymi wierszami, parami wartości lub kolejnością wystąpień, zastosuj LICZ.JEŻELI albo LICZ.WARUNKI. Poprawność danych służy natomiast do zatrzymania duplikatu jeszcze przed zapisaniem go w arkuszu.

Redakcja ithings.pl

Witaj na ithings.pl! Tworzymy zespół praktyków, którzy w przystępny sposób pomagają opanować codzienne wyzwania technologiczne - od konfiguracji poczty i sieci, przez obsługę sprzętu i drukarek, po bezpieczeństwo danych i świat gier. Zostań z nami i korzystaj z technologii bez przeszkód!

Czytaj dalej w tym dziale

Office

Jak wykonać F-test w Excelu?

Aby wykonać F-test w Excelu, użyj narzędzia „Test F: Test dwupróbkowy dla wariancji” dostępnego w dodatku Analysis ToolPak, nazywanym w polskiej wersji „Analizą danych”. Test porów…

Redakcja ithings.pl

Office

Jak używać dodatku Solver w programie Excel?

Solver w Excelu pomaga znaleźć najlepszą wartość celu, zmieniając wskazane komórki i respektując zdefiniowane ograniczenia. Nie zgaduje wyniku. Korzysta z formuł zapisanych w arkus…

Redakcja ithings.pl

Office

Jak włączyć dodatek Solver w programie Excel?

Aby włączyć Solver w Excelu, przejdź do Plik → Opcje → Dodatki. Następnie wybierz „Dodatki programu Excel”, kliknij „Przejdź”, zaznacz „Dodatek Solver” i zatwierdź przyciskiem „OK”…

Redakcja ithings.pl

Do góry strony