Jak porównać dwie kolumny w Excelu i usunąć duplikaty (podświetl, pokoloruj, przenieś)

Ten artykuł zajmie Ci około 10 minut. W ciągu następnych 5 minut możesz łatwo porównać dwie kolumny w Excelu i dowiedzieć się, czy są w nich duplikaty, usunąć je lub podświetlić kolorem. Nadszedł więc czas!

Excel to bardzo potężna i naprawdę fajna aplikacja do tworzenia i przetwarzania dużych ilości danych. Jeśli masz kilka skoroszytów z danymi (lub tylko jedną ogromną tabelę), prawdopodobnie chcesz porównać 2 kolumny, znaleźć zduplikowane wartości, a następnie coś z nimi zrobić, na przykład usunąć, podświetlić lub wyczyścić zawartość . Kolumny mogą znajdować się w tej samej tabeli, sąsiadować lub nie sąsiadować, mogą znajdować się na 2 różnych arkuszach lub nawet w różnych książkach.

Wyobraź sobie, że mamy 2 kolumny z imionami osób – 5 imion w kolumnie A i 3 nazwiska w kolumnie B. Musisz porównać nazwy w tych dwóch kolumnach i znaleźć duplikaty. Jak rozumiesz, są to fikcyjne dane, wzięte wyłącznie na przykład. W prawdziwych tabelach mamy do czynienia z tysiącami, a nawet dziesiątkami tysięcy rekordów.

Opcja: obie kolumny znajdują się na tym samym arkuszu. Na przykład kolumna A i kolumna B.

Opcja B: Kolumny znajdują się na różnych arkuszach. Na przykład kolumna A na prześcieradle Sheet2 i kolumna A na prześcieradle Sheet3.

Excel 2013, 2010 i 2007 mają wbudowane narzędzie Usuń duplikaty (Usuń duplikaty), ale jest bezsilny w tej sytuacji, ponieważ nie może porównać danych w 2 kolumnach. Co więcej, może usuwać tylko duplikaty. Nie ma innych opcji, takich jak podświetlanie lub zmiana kolorów. I punkt!

Następnie pokażę Ci możliwe sposoby porównania dwóch kolumn w Excelu, co pozwoli Ci znaleźć i usunąć zduplikowane rekordy.

• Porównaj 2 kolumny i poszukaj duplikatów za pomocą formuł
  
  • Opcja A: obie kolumny znajdują się na tym samym arkuszu
  • Opcja B: kolumny znajdują się na różnych arkuszach lub w różnych skoroszytach
• Przetwarzanie znalezionych duplikatów
  
  • Pokaż tylko zduplikowane wiersze w kolumnie A
  • Zmień kolor lub zaznacz znalezione duplikaty
  • Usuń duplikaty z pierwszej kolumny

Porównaj 2 kolumny w Excelu i znajdź zduplikowane wpisy za pomocą formuł

Opcja A: obie kolumny znajdują się na tym samym arkuszu

1. W pierwszej pustej komórce (w naszym przykładzie jest to komórka C1) piszemy następującą formułę:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   W naszej formule A1 to jest pierwsza komórka pierwszej kolumny, którą będziemy porównywać. $1$ и $10000$ są to adresy pierwszej i ostatniej komórki drugiej kolumny, z którymi dokonamy porównania. Zwróć uwagę na odwołania bezwzględne – litery kolumn i numery wierszy są poprzedzone znakiem dolara ($). Używam odwołań bezwzględnych, aby adresy komórek pozostały takie same podczas kopiowania formuł.

   Jeśli chcesz znaleźć duplikaty w kolumnie B, zmień odwołania tak, aby formuła wyglądała tak:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Zamiast "Only"I"Duplikat» Możesz pisać własne etykiety, na przykład «Nie znaleziono"I"Znaleziony„lub zostaw tylko”Duplikat' i wprowadź znak spacji zamiast drugiej wartości. W tym drugim przypadku komórki, dla których nie zostaną znalezione duplikaty, pozostaną puste i uważam, że taka reprezentacja danych jest najwygodniejsza do dalszej analizy.

2. Skopiujmy teraz naszą formułę do wszystkich komórek w kolumnie C, aż do dolnego wiersza, który zawiera dane w kolumnie A. Aby to zrobić, przesuń wskaźnik myszy w prawy dolny róg komórki C1, wskaźnik przyjmie postać czarnego celownika, jak pokazano na poniższym obrazku:Kliknij i przytrzymaj lewy przycisk myszy i przeciągnij krawędź ramki w dół, podświetlając wszystkie komórki, w których chcesz wstawić formułę. Po zaznaczeniu wszystkich wymaganych komórek zwolnij przycisk myszy:

   

Wskazówka: W dużych tabelach kopiowanie formuły będzie szybsze, jeśli użyjesz skrótów klawiaturowych. Zaznacz komórkę C1 i naciśnij Ctrl + C (aby skopiować formułę do schowka), a następnie kliknij Ctrl + Shift + Koniec (aby zaznaczyć wszystkie niepuste komórki w kolumnie C) i na koniec naciśnij Ctrl + V (aby wstawić formułę do wszystkich wybranych komórek).

3. Świetnie, teraz wszystkie zduplikowane wartości są oznaczone jako „Duplikat":

Opcja B: dwie kolumny znajdują się na różnych arkuszach (w różnych skoroszytach)

1. W pierwszej komórce pierwszej pustej kolumny w arkuszu Sheet2 (w naszym przypadku jest to kolumna B) wpisz następującą formułę:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Tutaj Sheet3 to nazwa arkusza, na którym znajduje się druga kolumna, oraz $ A $ 1: $ A $ 10000 to adresy komórek od pierwszej do ostatniej w tej drugiej kolumnie.

2. Skopiuj formułę do wszystkich komórek w kolumnie B (tak samo jak opcja A).
3. Otrzymujemy ten wynik:

Przetwarzanie znalezionych duplikatów

Świetnie, znaleźliśmy wpisy w pierwszej kolumnie, które są również obecne w drugiej kolumnie. Teraz musimy coś z nimi zrobić. Ręczne przeglądanie wszystkich zduplikowanych rekordów w tabeli jest dość nieefektywne i zajmuje zbyt dużo czasu. Są lepsze sposoby.

Pokaż tylko zduplikowane wiersze w kolumnie A

Jeśli Twoje kolumny nie mają nagłówków, musisz je dodać. Aby to zrobić, umieść kursor na liczbie reprezentującej pierwszą linię, a zmieni się ona w czarną strzałkę, jak pokazano na poniższym rysunku:

Kliknij prawym przyciskiem myszy i wybierz z menu kontekstowego Wprowadzenie (Wstawić):

Nadaj nazwy kolumnom, na przykład „Imię"I"Duplikować?» Następnie otwórz zakładkę Dane (Dane) i naciśnij FILTRY (Filtr):

Następnie kliknij małą szarą strzałkę obok „Duplikować?«, aby otworzyć menu filtrów; usuń zaznaczenie wszystkich pozycji na tej liście z wyjątkiem Duplikati naciśnij OK.

To wszystko, teraz widzisz tylko te elementy kolumny А, które są zduplikowane w kolumnie В. W naszej tabeli treningowej są tylko dwie takie komórki, ale jak rozumiesz, w praktyce będzie ich znacznie więcej.

Aby ponownie wyświetlić wszystkie wiersze kolumny А, kliknij symbol filtra w kolumnie В, który teraz wygląda jak lejek z małą strzałką i wybierz Wybierz wszystko (Zaznacz wszystko). Lub możesz zrobić to samo przez Wstążkę, klikając Dane (Dane) > Wybierz i filtruj (Sortuj i filtruj) > Wyczyść (Wyczyść), jak pokazano na poniższym zrzucie ekranu:

Zmień kolor lub zaznacz znalezione duplikaty

Jeśli notatki „Duplikat” to za mało i chcesz oznaczyć zduplikowane komórki innym kolorem czcionki, kolorem wypełnienia lub inną metodą…

W takim przypadku przefiltruj duplikaty, jak pokazano powyżej, zaznacz wszystkie filtrowane komórki i kliknij Ctrl + 1aby otworzyć okno dialogowe Formatowanie komórek (format komórki). Jako przykład zmieńmy kolor wypełnienia komórek w wierszach z duplikatami na jasnożółty. Oczywiście możesz zmienić kolor wypełnienia za pomocą narzędzia Wypełniać Zakładka (Kolor wypełnienia) Strona główna (Dom), ale przewaga okna dialogowego Formatowanie komórek (Cell Format), dzięki czemu możesz jednocześnie skonfigurować wszystkie opcje formatowania.

Teraz na pewno nie przegapisz żadnych komórek z duplikatami:

Usuwanie zduplikowanych wartości z pierwszej kolumny

Przefiltruj tabelę tak, aby pokazywane były tylko komórki ze zduplikowanymi wartościami, i zaznacz te komórki.

Jeśli 2 kolumny, które porównujesz, znajdują się na różnych arkuszach, czyli w różnych tabelach, kliknij prawym przyciskiem myszy wybrany zakres i wybierz Usuń wiersz (Usuń linię):

Naciśnij przycisk OKgdy program Excel poprosi o potwierdzenie, że naprawdę chcesz usunąć cały wiersz arkusza, a następnie wyczyść filtr. Jak widać, pozostają tylko wiersze z unikalnymi wartościami:

Jeśli 2 kolumny znajdują się na tym samym arkuszu, blisko siebie (sąsiadując) lub nie blisko siebie (nie sąsiadując), proces usuwania duplikatów będzie nieco bardziej skomplikowany. Nie możemy usunąć całego wiersza ze zduplikowanymi wartościami, ponieważ spowoduje to usunięcie również komórek z drugiej kolumny. Aby pozostawić tylko unikalne wpisy w kolumnie А, Zrób to:

1. Przefiltruj tabelę, aby wyświetlić tylko zduplikowane wartości i wybierz te komórki. Kliknij je prawym przyciskiem myszy i wybierz z menu kontekstowego Wyczyść zawartość (jasna treść).
2. Wyczyść filtr.
3. Zaznacz wszystkie komórki w kolumnie А, zaczynając od komórki A1 aż do samego dołu zawierającego dane.
4. Kliknij Dane (Dane) i naciśnij Sortuj od A do Z (Sortuj od A do Z). W otwartym oknie dialogowym wybierz Kontynuuj z bieżącym wyborem (Sortuj w ramach określonego wyboru) i kliknij przycisk Czarny (Sortowanie):
5. Usuń kolumnę z formułą, już jej nie będziesz potrzebować, od teraz masz tylko unikalne wartości.
6. To wszystko, teraz kolumna А zawiera tylko unikalne dane, których nie ma w kolumnie В:

Jak widać, usuwanie duplikatów z dwóch kolumn w Excelu za pomocą formuł nie jest takie trudne.