Excel to wszechstronna aplikacja, która znacznie wykracza poza swoje 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 szybko stają się nieporęczne ze względu na ogromną 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ę też o sposobie wyróżnienia wierszy, które są najpierw duplikowane, aby można było zobaczyć, które z nich zostaną usunięte. przez funkcje, zanim je uruchomisz. Musisz użyć niestandardowej reguły formatowania w celu podświetlenia wiersza, 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ę dwa razy. Aby usunąć takie zduplikowane rekordy, kliknij kartę Danena Wstążce i znajdź funkcję Usuń duplikatyw sekcji Narzędzia danych. Kliknij Usuń duplikatyi otworzy się nowe okno.
Tutaj musisz podjąć decyzję na podstawie tego, czy używaj etykiet nagłówków na górze kolumn. Jeśli tak, wybierz opcję oznaczoną Moje dane mają nagłówki. Jeśli nie używasz etykiet nagłówków, użyjesz standardowych oznaczeń kolumn w Excelu, takich jak Kolumna A, Kolumna B itd.
Dla w tym przykładzie wybierzemy kolumnę A i klikniemy przycisk OK. Okno opcji zamyka się, a program Excel usuwa drugi rekord "Jonesa".
Oczywiście, był to tylko prosty przykład. Wszelkie rekordy adresu, 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:
Zwróć uwagę, że chociaż istnieją trzy zapisy "Jonesa", tylko dwa są identyczne. Jeśli użyjemy powyższych procedur do usunięcia duplikatów, pozostanie tylko jeden wpis "Jones". W takim 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 Danena Wstążce, a następnie kliknij Usuń duplikaty. Tym razem, gdy pojawi się okno opcji, wybierz kolumny A i B. Kliknij przycisk OKi zauważ, że tym razem Excel usunął tylko jeden z rekordów "Mary Jones".
Dzieje się tak, ponieważ 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 filtru zaawansowanego. Najpierw zaznacz wszystkie dane w arkuszu. Następnie na karcie Dane na wstążce kliknij Zaawansowanew Sortuj & amp; Filtruj sekcję.
W wyskakującym oknie dialogowym sprawdź Tylko unikalne rekordypole wyboru.
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 wycinaj i wklejaj te dane do nowego arkusza.
Dzięki tej metodzie nawet nie otrzymasz wiadomości z informacją, ile wiersze zostały usunięte. Wiersze są usuwane i to wszystko.
Podświetl Duplikaty wierszy w Excelu
Jeśli chcesz zobaczyć, które rekordy są duplikowane przed ich usunięciem, musisz wykonać trochę pracy ręcznej . 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.
Pierwszą rzeczą, którą musisz zrobić, to dodać formułę w kolumnie po prawej stronie swojego zestawu dane. Wzór jest prosty: po prostu połącz wszystkie kolumny dla tego wiersza razem.
= A1 & B1 & C1 & D1 & E1
W moim przykładzie poniżej mam dane w kolumnach od A do F. Jednak pierwsza kolumna to numer identyfikacyjny, więc wykluczam to z poniższej formuły. Upewnij się, że uwzględniłeś wszystkie kolumny, które mają dane, które chcesz sprawdzić dla duplikatów.
Umieściłem tę formułę w kolumnie H, a następnie przeciągnąłem to dla 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($H$1:$H$34, $H1) > 1
Tutaj używamy funkcji LICZ.JEŻELI, a pierwszym parametrem jest zestaw danych, które chcemy wyglądać w. Dla mnie była to kolumna H (która ma formułę danych łączonych) 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ł upewnij się, że korzystasz ze znaku dolara ($) przed literą i numerem. Jeśli masz 1000 wierszy danych i twoja kombinowana formuła wierszy znajduje się na przykład w kolumnie F, Twoja formuła wyglądałaby tak:
=COUNTIF($F$1:$F$1000, $F1) > 1
Drugi parametr ma tylko dolara Zaloguj się przed literą kolumny, aby była zablokowana, ale nie chcemy blokować numeru wiersza. Ponownie przeciągniesz to na wszystkie wiersze danych. Powinien wyglądać tak, a duplikaty wierszy powinny mieć w nich wartość PRAWDA.
Teraz, wyróżnijmy wiersze, które mają w nich PRAWDA, są duplikatami wierszy. Najpierw zaznacz cały arkusz danych, klikając mały trójkąt w górnym lewym przecięciu wierszy i kolumn. Teraz przejdź do karty Strona główna, następnie kliknij Formatowanie warunkowei kliknij Nowa reguła.
W oknie dialogowym kliknij Użyj formuły, aby określić komórki do sformatowania.
W polu poniżej Formatuj wartości, w których ta formuła jest prawdziwa:, wprowadź następującą formułę, zastępując Pkolumną ma wartości PRAWDA lub FAŁSZ. Upewnij się, że znak dolara znajduje się przed literą kolumny.
=$P1=TRUE
Po wykonaniu tej czynności kliknij Format 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 zrób to ponownie 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 przed usunięciem zduplikowanych rekordów
Jest kilka problemów z automatycznym zezwoleniem na program Excel usuń dla Ciebie 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 kryteria identyfikujące duplikaty rekordów.
Zbyt mało i można 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 przyjmuje, ż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 zamiast tego utworzyłeś nowy rekord.
Jeśli nowy (poprawny) rekord adresu pojawi się po starym ( nieaktualny), program Excel zakłada, że pierwszy (nieaktualny) rekord jest wzorcem i usuwa wszystkie kolejne znalezione rekordy. Dlatego musisz uważać na to, jak liberalnie lub konserwatywnie 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 ufasz, że podjęte przez ciebie decyzje są poprawne i pozwolisz programowi Excel automatycznie usunąć duplikaty rekordów.
Pamiętaj też, aby sprawdzić nasz poprzedni artykuł na temat usuwanie pustych linii w Excelu. Ciesz się!