Excel jest wszechstronną aplikacją, która znacznie wykracza poza wczesne wersje, jako proste rozwiązanie dla arkuszy kalkulacyjnych. Zatrudniony jako kierownik ewidencji, książka adresowa, narzędzie do prognozowania i wiele innych, wiele osób korzysta nawet z Excela w sposób, w jaki nigdy nie był przeznaczony.
Jeśli korzystasz z programu Excel w domu lub w biurze, wiesz, że czasami pliki Excel mogą szybko stać się nieporęczne ze względu na liczbę rekordów, z którymi pracujesz.
Na szczęście program Excel ma wbudowane funkcje ułatwiające wyszukiwanie i usuwanie duplikatów. Niestety, istnieje kilka zastrzeżeń dotyczących korzystania z tych funkcji, więc bądź ostrożny lub możesz nieświadomie usunąć rekordy, których nie chcesz usunąć. Poza tym obie poniższe metody natychmiastowo usuwają duplikaty, nie pozwalając zobaczyć, co zostało usunięte.
Wspomnę również o sposobie wyróżnienia pierwszych wierszy, aby można było zobaczyć, które z nich zostaną usunięte przez funkcje przed ich uruchomieniem. Musisz użyć niestandardowej reguły formatowania, aby podświetlić wiersz, który jest całkowicie duplikatem.
Usuń duplikaty funkcji
Załóżmy, że używasz programu Excel do śledzenia adresów i podejrzewasz, że masz zduplikowane rekordy. Spójrz na przykładowy arkusz programu Excel poniżej:
Zauważ, że rekord "Jonesa" pojawia się dwukrotnie. Aby usunąć takie zduplikowane rekordy, kliknij kartę Dane na Wstążce i znajdź funkcję Usuń duplikaty w sekcji Narzędzia danych . Kliknij Usuń duplikaty i otworzy się nowe okno.
Tutaj musisz podjąć decyzję na podstawie tego, czy używasz etykiet nagłówków na górze kolumn. Jeśli tak, wybierz opcję oznaczoną jako Moje dane has nagłówków . Jeśli nie używasz etykiet nagłówków, użyjesz standardowych oznaczeń kolumn w programie Excel, takich jak Kolumna A, Kolumna B itp.
W tym przykładzie wybierzemy kolumnę A i klikniemy przycisk OK . Okno opcji zostanie zamknięte, a program Excel usunie drugi rekord "Jones".
Oczywiście był to prosty przykład. Wszelkie rekordy adresów, które nadal używasz Excela, mogą być znacznie bardziej skomplikowane. Załóżmy na przykład, że masz plik adresowy, który wygląda tak.
Zauważ, że chociaż istnieją trzy zapisy "Jonesa", tylko dwa są identyczne. Jeśli użyliśmy powyższych procedur do usunięcia duplikatów, pozostanie tylko jeden wpis "Jones". W tym przypadku musimy rozszerzyć nasze kryteria decyzji, aby uwzględnić zarówno imię, jak i nazwisko, odpowiednio w kolumnach A i B.
Aby to zrobić, ponownie kliknij kartę Dane na Wstążce, a następnie kliknij opcję Usuń duplikaty . Tym razem, gdy pojawi się okno opcji, wybierz kolumny A i B. Kliknij przycisk OK i zauważ, że tym razem Excel usunął tylko jeden z rekordów "Mary Jones".
Wynika to z tego, że powiedzieliśmy programowi Excel, aby usunął duplikaty, dopasowując rekordy oparte na kolumnach A i B, a nie tylko na kolumnie A. Im więcej kolumn wybierzesz, tym więcej kryteriów musi zostać spełnionych, zanim program Excel uzna rekord za duplikat. Wybierz wszystkie kolumny, jeśli chcesz usunąć wiersze, które są całkowicie duplikowane.
Program Excel wyświetli komunikat informujący o liczbie usuniętych duplikatów. Nie pokazuje jednak, które wiersze zostały usunięte! Przewiń w dół do ostatniej sekcji, aby zobaczyć, jak podświetlić zduplikowane wiersze przed uruchomieniem tej funkcji.
Zaawansowana metoda filtrowania
Drugim sposobem na usunięcie duplikatów jest użycie opcji zaawansowanego filtru. Najpierw zaznacz wszystkie dane w arkuszu. Następnie na karcie Dane na wstążce kliknij Zaawansowane w sekcji Sortowanie i filtrowanie .
W wyskakującym oknie dialogowym zaznacz pole wyboru Tylko unikatowe rekordy .
Możesz filtrować listę w miejscu lub możesz kopiować nie powielone elementy do innej części tego samego arkusza kalkulacyjnego. Z jakiegoś dziwnego powodu nie można skopiować danych do innego arkusza. Jeśli chcesz go umieścić na innym arkuszu, najpierw wybierz lokalizację w bieżącym arkuszu, a następnie wytnij i wklej te dane do nowego arkusza.
Dzięki tej metodzie nie dostajesz nawet komunikatu informującego, ile wierszy zostało usuniętych. Wiersze zostały usunięte i to wszystko.
Podświetl Duplikaty wierszy w Excelu
Jeśli chcesz sprawdzić, które rekordy są duplikowane przed ich usunięciem, musisz wykonać trochę ręcznej pracy. Niestety, program Excel nie ma możliwości wyróżnienia wierszy, które są całkowicie duplikowane. Ma funkcję pod warunkowym formatowaniem, która wyróżnia duplikaty komórek, ale ten artykuł dotyczy duplikatów wierszy.
Pierwszą rzeczą, którą musisz zrobić, to dodać formułę w kolumnie po prawej stronie zestawu danych. Formuła jest prosta: po prostu połącz wszystkie kolumny dla tego wiersza.
= A1 i B1 i C1 i D1 i E1
W poniższym przykładzie mam dane w kolumnach od A do F. Jednak pierwsza kolumna to numer identyfikacyjny, więc wykluczyłem to z poniższej formuły. Upewnij się, że uwzględniono wszystkie kolumny, które mają dane, które chcesz sprawdzić w poszukiwaniu duplikatów.
Umieściłem tę formułę w kolumnie H, a następnie przeciągnąłem ją do wszystkich moich wierszy. Ta formuła po prostu łączy wszystkie dane w każdej kolumnie jako jeden duży fragment tekstu. Teraz pomiń jeszcze kilka kolumn i wprowadź następującą formułę:
= COUNTIF (1 H $ 1: $ H 34 USD, H1)> 1
Tutaj używamy funkcji LICZ.JEŻELI, a pierwszym parametrem jest zestaw danych, które chcemy obejrzeć. Dla mnie była to kolumna H (która ma formułę danych kombinacji) z wiersza od 1 do 34. Dobrym pomysłem jest także pozbycie się wiersza nagłówka przed wykonaniem tego.
Będziesz także chciał się upewnić, że używasz znaku dolara ($) przed literą i numerem. Jeśli masz 1000 wierszy danych, a Twoja kombinowana formuła wierszy znajduje się w kolumnie F, na przykład Twoja formuła będzie wyglądać następująco:
= COUNTIF ($ F 1: $ F 1000, F1)> 1
Drugi parametr ma tylko znak dolara przed literą kolumny, więc jest zablokowany, ale nie chcemy blokować numeru wiersza. Ponownie przeciągniesz to na wszystkie wiersze danych. Powinno to wyglądać w ten sposób, a duplikaty wierszy powinny mieć w nich wartość PRAWDA.
Teraz wyróżnij wiersze, które mają w nich wartość PRAWDA, ponieważ są to duplikaty wierszy. Najpierw zaznacz cały arkusz danych, klikając mały trójkąt w lewym górnym rogu przecięcia wierszy i kolumn. Teraz przejdź do karty Strona główna, następnie kliknij Formatowanie warunkowe i kliknij Nowa reguła .
W oknie dialogowym kliknij Użyj formuły, aby określić komórki do sformatowania .
W polu pod Formatuj wartości tam, gdzie ta formuła jest prawdziwa: wprowadź następującą formułę, zastępując literę P kolumną o wartościach PRAWDA lub FAŁSZ. Pamiętaj, aby doliczyć znak dolara przed literą kolumny.
= $ P1 = PRAWDA
Kiedy już to zrobisz, kliknij Formatuj i kliknij kartę Wypełnienie. Wybierz kolor, który zostanie użyty do podświetlenia całego zduplikowanego wiersza. Kliknij OK i powinieneś zobaczyć podświetlone duplikaty wierszy.
Jeśli to nie działa, zacznij od nowa i rób to powoli. Trzeba to zrobić dokładnie tak, aby wszystko to zadziałało. Jeśli nie zauważysz jednego symbolu $ po drodze, nie będzie on działał poprawnie.
Ostrzeżenia dotyczące usuwania duplikatów rekordów
Istnieje oczywiście kilka problemów z zezwoleniem programowi Excel automatycznie usuwać duplikaty rekordów. Po pierwsze, należy uważać, aby wybrać zbyt mało lub zbyt wiele kolumn, aby program Excel mógł posłużyć jako kryterium identyfikujące zduplikowane rekordy.
Zbyt mało i możesz przypadkowo usunąć potrzebne rekordy. Zbyt wiele lub włączając kolumnę identyfikatora przez przypadek i nie będzie żadnych duplikatów.
Po drugie, Excel zawsze zakłada, że pierwszym unikalnym rekordem, który napotkał, jest rekord główny. Przyjmuje się, że wszelkie kolejne rekordy są duplikatami. Jest to problem, jeśli na przykład nie udało się zmienić adresu jednej z osób w pliku, ale utworzył nowy rekord.
Jeśli nowy (poprawny) rekord adresu pojawi się po starym (nieaktualnym) rekordzie, Excel przyjmie, że pierwszy (nieaktualny) rekord będzie wzorcem i usunie wszystkie kolejne znalezione rekordy. Dlatego musisz uważać na to, jak liberalnie lub zachowawczo pozwalasz programowi Excel decydować, co jest lub nie jest duplikatem.
W takich przypadkach powinieneś użyć metody duplikowania podkreślenia, o której pisałem i ręcznie usunąć odpowiedni duplikat rekordu.
Na koniec program Excel nie prosi o sprawdzenie, czy naprawdę chcesz usunąć rekord. Wykorzystując wybrane parametry (kolumny) proces jest całkowicie zautomatyzowany. Może to być niebezpieczne, gdy masz ogromną liczbę rekordów i masz pewność, że podjęte przez ciebie decyzje są poprawne i pozwolisz programowi Excel automatycznie usunąć duplikaty rekordów.
Sprawdź również nasz poprzedni artykuł dotyczący usuwania pustych wierszy w programie Excel. Cieszyć się!
