Jak bezpiecznie usunąć duplikaty w programie Excel

Wyobraź sobie księgową, która ma plik z eksportem zamówień klientów zawierający 12 000 wierszy. Dwóch klientów ma to samo nazwisko, kilka zamówień się powtarza, bo eksport uruchomiono dwukrotnie, a wśród nich są wpisy, które tylko na pozór wyglądają na duplikaty. Zaznacza cały arkusz, klika opcję „Usuń duplikaty”, a Excel radośnie informuje: „Znaleziono i usunięto 347 zduplikowanych wartości”. Gotowe w kilka sekund. Tylko że około 90 z nich wcale nie było duplikatami, a teraz ich już nie ma.

W tym właśnie tkwi pułapka. Proces bezpiecznego usuwania duplikatów w programie Excel zaczyna się jeszcze przed kliknięciem przycisku. Funkcja „Usuń duplikaty” działa szybko, powoduje trwałe zmiany i nie informuje w żaden sposób o tym, co faktycznie porównuje.

Większość poradników od razu przechodzi do tego przycisku. Pomijają jednak część, która pozwala zachować dane: najpierw sprawdź, zanim usuniesz. Najpierw zaznacz komórki, sprawdź, co program Excel uznaje za duplikaty, a dopiero potem je usuń. Przejdźmy więc przez ten proces krok po kroku.

Dlaczego typowe porady powodują utratę danych przez użytkowników

Większość poradników składa się z około trzech zdań. Zaznacz dane, przejdź do zakładki „Dane” i kliknij opcję „Usuń duplikaty”. Technicznie rzecz biorąc, jest to poprawne. W praktyce jednak niebezpieczne.

W tym przypadku pojawiają się dwa problemy. Po pierwsze, ta operacja ma charakter destrukcyjny. Program Excel nie przenosi duplikatów do osobnego arkusza ani nie oznacza ich do sprawdzenia — po prostu usuwa wiersze i przesuwa całą zawartość w górę. Po zapisaniu i zamknięciu pliku następnego dnia rano nie będzie już możliwości cofnięcia tej operacji.

Po drugie – i tego nikt nie wyjaśnia – kolumny zaznaczone w oknie dialogowym decydują o tym, co uznaje się za duplikat. Ten jeden wybór zmienia cały wynik. Wystarczy zaznaczyć tylko kolumnę „E-mail”, a program Excel uzna dwa wiersze za identyczne, jeśli adresy e-mail są zgodne, nawet jeśli nazwy, daty zamówień i kwoty są zupełnie inne. Zachowuje pierwszy wiersz, a resztę usuwa, przez co prawdziwe zamówienia znikają.

W własnych wytycznych firma Microsoft przyznaje, że usunięcie duplikatów powoduje trwałe skasowanie danych, dlatego też dział pomocy technicznej Microsoftu zaleca skopiowanie zaznaczonego zakresu do innego arkusza przed uruchomieniem narzędzia. To dobry nawyk. Prawie nikt tego nie robi.

Oto sposób, który naprawdę chroni Twoje dane. Zajmuje to może dwie minuty, a dzięki temu nic nie zostanie usunięte, czego nie zdążyłbyś wcześniej zobaczyć.

Krok 1 – Znajdowanie duplikatów w programie Excel za pomocą formatowania warunkowego

Zanim podejmiesz jakiekolwiek radykalne działania, spraw, by powtórzenia stały się widoczne. Zaznacz zakres, a następnie przejdź do zakładki „Narzędzia główne” (Home) > „Formatowanie warunkowe” (Conditional Formatting) > „Podświetlanie komórek” (Highlight Cells) „Reguły” (Rules) > „Powtarzające się wartości” (Duplicate Values). Wybierz kolor wypełnienia i potwierdź.

Program Excel zaznacza każdą zduplikowaną wartość. To najszybszy sposób na wykrycie duplikatów bez wprowadzania zmian w żadnej komórce, a ponadto można go całkowicie cofnąć. Nie podoba ci się efekt? Usuń regułę. Nic nie uległo zmianie.

Warto pamiętać o jednej rzeczy dotyczącej funkcji zaznaczania: działa ona kolumna po kolumnie na wszystkich zaznaczonych komórkach. Jeśli zaznaczysz jedną kolumnę, program Excel zaznaczy powtarzające się wartości tylko w tej kolumnie. Jeśli zaznaczysz kilka kolumn, program traktuje każdą komórkę osobno, co może sprawiać wrażenie nieuporządkowanego, ale pozwala dokładnie zlokalizować miejsca, w których występują powtórzenia.

Krok 2 – Sprawdź wszystko przed zatwierdzeniem

Przejrzyj zaznaczone komórki. To właśnie ten krok pominął księgowy. Zastanów się, czy zaznaczona wartość to rzeczywiście zduplikowany wiersz, czy też po prostu wartość, która powtarza się zgodnie z oczekiwaniami. Jeśli zaznaczysz kolumnę z nazwiskami, podświetlą się dwie różne osoby o nazwisku „James Miller”. Żadna z nich nie stanowi zduplikowanego zamówienia.

W tej sekcji można dostrzec różnicę między stwierdzeniem „ta wartość pojawia się dwukrotnie” a stwierdzeniem „cały ten rekord jest kopią”. To zupełnie dwie różne rzeczy — a funkcja „Usuń duplikaty” rozpoznaje wyłącznie te kolumny, które jej podasz.

Krok 3: Usuń, mając zaznaczone odpowiednie kolumny

Teraz możesz usunąć dane, mając pełną świadomość, co właśnie usuwasz. Zaznacz dane, przejdź do karty „Dane” i wybierz opcję „Usuń duplikaty”. W oknie dialogowym wyświetlona jest lista wszystkich kolumn wraz z polami wyboru. To właśnie to okno dialogowe jest kluczem do sukcesu.

Jak wybór kolumny zmienia wszystko

To właśnie ta część, którą większość konkurentów całkowicie pomija, więc przyjrzyjmy się dokładnie, co się dzieje.

Usuwanie duplikatów w wielu kolumnach – program Excel usuwa wiersz tylko wtedy, gdy wszystkie zaznaczone kolumny dokładnie pokrywają się z innym wierszem. Należy zaznaczyć wszystkie kolumny, a wiersz musi być ich dokładną kopią, zanim zostanie usunięty. Jest to najbezpieczniejsze ustawienie, ponieważ praktycznie nigdy nie powoduje usunięcia rekordu, który różni się w jakikolwiek istotny sposób.

Odznaczając kolumny, rozluźniasz definicję. Załóżmy, że dane zawierają pola: Imię i nazwisko, Adres e-mail, Numer zamówienia oraz Kwota. Oto, jak ten sam przycisk zachowuje się w zależności od tego, co zaznaczysz:

Zauważ wzór. Im mniej zaznaczonych kolumn, tym bardziej radykalne usuwanie. Każdy, kto chce zachować każde odrębne zamówienie, powinien zaznaczyć wszystkie kolumny, które sprawiają, że wiersz jest unikalny, podczas gdy osoba porządkująca zwykłą listę mailingową może śmiało zaznaczyć tylko kolumnę „E-mail” – powtarzający się adres e-mail rzeczywiście stanowi w tym przypadku zduplikowany kontakt.

Ogólna zasada: zaznacz kolumny, które razem pozwalają zidentyfikować konkretny rekord. W przypadku zamówień są to zazwyczaj numer zamówienia oraz kilka innych pól. W przypadku listy kontaktów może to być po prostu adres e-mail.

Czasami w ogóle nie chcesz niszczyć oryginału. Chcesz po prostu otrzymać osobno przejrzystą listę unikalnych wartości. Są na to dwa bezpieczne sposoby.

Funkcja UNIQUE działa najsprawniej, jeśli obsługuje ją Twoja wersja programu Excel. Wystarczy wpisać ją w pustą komórkę, a wygeneruje ona listę bez duplikatów w sąsiednich komórkach, nie zmieniając przy tym źródła danych.

=UNIQUE(A2:A5000)

Oryginalne dane pozostają dokładnie tam, gdzie są. Wystarczy zmienić źródło, a lista unikalnych wpisów zaktualizuje się automatycznie. To główna zaleta w porównaniu z funkcją „Usuń duplikaty” — działanie tej funkcji jest z założenia nieniszczące.

Inną opcją jest „Filtr zaawansowany”. Data > Advanced > „Kopiuj do innej lokalizacji” z zaznaczoną opcją „Tylko unikalne rekordy”. Spowoduje to zapisanie kopii bez duplikatów w innym miejscu arkusza. Jeśli szukasz alternatywnych rozwiązań, serwis ExcelDemy przedstawia kilka metod nieniszczących, a serwis DataCamp bardziej szczegółowo omawia podejście oparte na funkcji UNIQUE. Obie metody zachowują źródło w nienaruszonym stanie, co jest ich główną zaletą.

Rozwiązywanie problemów związanych z typowymi pułapkami

Jest kilka rzeczy, które sprawiają ludziom trudności, nawet jeśli postępują zgodnie z instrukcją.

„Usunęło to wiersze, których potrzebowałem.” Prawie zawsze chodzi o problem z zaznaczeniem kolumn. Zaznaczyłeś zbyt mało kolumn, więc Excel dopasował dane na podstawie fragmentów. Nie zapisałeś i nie zamknąłeś pliku? Natychmiast naciśnij Ctrl+Z. Już zamknąłeś? Korzystasz z kopii zapasowej. Właśnie dlatego nawyk kopiowania pliku przed edycją jest tak ważny.

Duplikaty, które nie zostały wykryte. Zazwyczaj są to spacje na końcu lub niespójna wielkość liter. Z punktu widzenia programu Excel zapis „[email protected] ” z przestrzenią nie jest równoznaczny z zapisem „[email protected]”. Najpierw zastosuj funkcje TRIM i CLEAN do tej kolumny, w przeciwnym razie etap zaznaczania po cichu je pominie.

Liczby zapisane jako tekst. Wartość sformatowana jako tekst i ta sama wartość zapisana jako liczba nie będą się zgadzać. Przed usunięciem duplikatów należy przekonwertować kolumnę na jeden spójny format, w przeciwnym razie pojawią się fałszywe wyniki negatywne.

Połączone komórki generują błędy. Program „Remove Duplicates” nie działa na zakresach zawierających scalone komórki. Najpierw należy je rozdzielić.

Czy usunięcie duplikatów w programie Excel powoduje usunięcie całego wiersza, czy tylko jednej komórki?
Cały wiersz. Program Excel usuwa cały rekord i przesuwa wiersze znajdujące się poniżej w górę. Nigdy nie usuwa zawartości pojedynczej komórki, dlatego tak ważne jest zaznaczenie kolumny – dopasowanie odbywa się na poziomie kolumny, ale usuwanie dotyczy całego wiersza.

Czy po usunięciu duplikatów będę mógł odzyskać swoje dane?
Można to zrobić tylko za pomocą skrótu Ctrl+Z przed zapisaniem i zamknięciem pliku lub przywracając zapisany plik kopii zapasowej. Po zamknięciu pliku nie ma już możliwości odzyskania danych. Dla bezpieczeństwa skopiuj najpierw zaznaczony zakres do innego arkusza.

Jak zaznaczyć duplikaty zamiast je usuwać?
Strona główna > Formatowanie warunkowe > Reguły zaznaczania komórek > Duplikaty wartości. Funkcja ta zaznacza kolorem każdą powtarzającą się wartość bez zmiany danych, a regułę można w każdej chwili usunąć. Jest to najbezpieczniejszy sposób wyszukiwania duplikatów oferowany przez program Excel.

Jaka jest różnica między opcją „UNIQUE” a opcją „Usuń duplikaty”?
UNIQUE to formuła, która generuje oddzielną listę unikalnych wartości, obliczaną na bieżąco, nie zmieniając przy tym danych źródłowych. Funkcja „Usuń duplikaty” trwale modyfikuje dane w miejscu. Z funkcji UNIQUE należy korzystać, gdy chcesz zachować oryginalne dane.

Jak usunąć zduplikowane wartości w wielu kolumnach?
W oknie dialogowym zaznacz wszystkie kolumny, które łącznie definiują unikalny rekord. Program Excel usuwa wiersz tylko wtedy, gdy wszystkie zaznaczone kolumny dokładnie pokrywają się z innym wierszem. Im więcej kolumn zostanie zaznaczonych, tym bezpieczniejsze będzie działanie; im mniej, tym bardziej radykalne.

W skrócie

Funkcja „Usuń duplikaty” nie jest wrogiem. Wrogiem jest jej bezkrytyczne stosowanie. Zaznacz duplikaty za pomocą formatowania warunkowego, aby były dobrze widoczne, sprawdź, czy są to prawdziwe duplikaty, czy tylko powtarzające się wartości, a następnie usuń je, zaznaczając odpowiednie kolumny. Za każdym razem najpierw skopiuj dane do innego arkusza, ponieważ narzędzie nie daje drugiej szansy po zamknięciu pliku.

Oto jak usunąć duplikaty w programie Excel bez usuwania potrzebnych danych.

Taką samą ostrożność należy zachować w przypadku oprogramowania, na którym to wszystko uruchamiasz. Oryginalna, prawidłowo licencjonowana kopia pakietu Office gwarantuje, że formuły, funkcje takie jak UNIQUE oraz pliki będą działać zgodnie z opisem w dokumentacji.