Jak porównać dwa arkusze Excela dla duplikatów: 5 szybkich sposobów

Podczas pracy z dużymi zestawami danych w programie Excel często istnieje wiele arkuszy roboczych zawierających podobne dane. Porównanie tych arkuszy pod kątem zduplikowanych rekordów może być sporym wyzwaniem, zwłaszcza gdy dane są rozproszone w różnych kolumnach i wierszach.

Najczęstszymi metodami wyszukiwania duplikatów w dwóch arkuszach Excela są:

1. Funkcje WYSZUKAJ.PIONOWO, LICZ.JEŻELI lub DOKŁADNIE

2. Formatowanie warunkowe

3. Power Query

4. Zewnętrzne narzędzia i dodatki

5. Wizualne kontrole pod kątem duplikatów

W tym artykule krok po kroku omówiono te pięć metod, aby wskazać i obsłużyć duplikaty w wielu arkuszach. Pod koniec zobaczysz, jak łatwo zarządzać zduplikowanymi wartościami.

Zacznijmy!

Spis treści

• Przykładowe arkusze robocze i dane
• 1. Korzystanie z funkcji WYSZUKAJ.PIONOWO, LICZ.JEŻELI lub DOKŁADNIE w celu znalezienia duplikatów
  • A. Jak korzystać z funkcji WYSZUKAJ.PIONOWO, aby znaleźć duplikaty w dwóch arkuszach?
    • Co zrobić, jeśli obsługujesz różne skoroszyty?
  • B. Jak używać funkcji LICZ.JEŻELI do znajdowania duplikatów w arkuszach roboczych?
  • C. Jak używać funkcji EXACT do znajdowania duplikatów w arkuszach roboczych?
• 2. Jak używać formatowania warunkowego dla zduplikowanych wierszy
  • Jak korzystać z Menedżera reguł formatowania warunkowego
• 3. Jak używać dodatku Power Query do wyszukiwania duplikatów w arkuszach roboczych
• 4. Narzędzia i dodatki do identyfikowania duplikatów w arkuszach roboczych
• 5. Jak wizualnie sprawdzić duplikaty w dwóch arkuszach
• 3 wskazówki dotyczące przygotowywania arkuszy programu Excel
• Jak radzić sobie z błędami i niespójnościami
• Końcowe przemyślenia

Przykładowe arkusze robocze i dane

Aby wykonać przykłady z tego artykułu, utwórz nowy skoroszyt programu Excel z tymi danymi w kolumnie A pierwszego arkusza:

• Jabłko

• Pomarańczowy

• Gruszka

• Truskawka

Umieść te dane w kolumnie A drugiego arkusza:

• Gruszka

• Truskawka

• Jabłko

• Ananas

Twoje karty pracy wyglądają tak:

Po skonfigurowaniu arkuszy roboczych możemy teraz omówić różne sposoby znajdowania duplikatów w dwóch arkuszach, zaczynając od funkcji WYSZUKAJ.PIONOWO, LICZ.JEŻELI i DOKŁADNIE.

Chodźmy!

1. Korzystanie z funkcji WYSZUKAJ.PIONOWO, LICZ.JEŻELI lub DOKŁADNIE w celu znalezienia duplikatów

Program Excel ma trzy wbudowane funkcje, które ułatwiają znajdowanie duplikatów: WYSZUKAJ.PIONOWO , LICZ.JEŻELI i DOKŁADNIE .

Te funkcje zostały zaprojektowane, aby pomóc Ci znaleźć, policzyć i porównać dane w arkuszach kalkulacyjnych, dzięki czemu są idealnymi narzędziami do znajdowania zduplikowanych wpisów.

Każda funkcja służy unikalnemu celowi, a zrozumienie, jak z nich korzystać, może znacznie usprawnić proces analizy danych.

W poniższych sekcjach zbadamy, jak używać funkcji WYSZUKAJ.PIONOWO, LICZ.JEŻELI i DOKŁADNIE, aby skutecznie identyfikować duplikaty w arkuszach programu Excel.

A. Jak korzystać z funkcji WYSZUKAJ.PIONOWO, aby znaleźć duplikaty w dwóch arkuszach?

VLOOKUP oznacza pionowe wyszukiwanie. Ta funkcja służy do znajdowania zduplikowanych wartości między dwiema kolumnami. Oto składnia:

=WYSZUKAJ.PIONOWO(wyszukiwana_wartość; tablica_tabeli; numer_indeksu_kolumny; [przeszukiwany_zakres])

• szukana_wartość : wartość, którą chcesz wyszukać w pierwszej kolumnie tabeli_tablica.

• table_array : Zakres komórek zawierających dane, które chcesz przeszukać.

• col_index_num : numer kolumny w tablicy_tablicy, z której chcesz zwrócić wartość.

• range_lookup : Opcjonalne. Może to być PRAWDA (dopasowanie przybliżone) lub FAŁSZ (dopasowanie dokładne). Wartość domyślna to PRAWDA.

Aby użyć tej funkcji w dwóch arkuszach w pliku programu Excel, musisz wiedzieć, jak odwoływać się do osobnego arkusza w formule. Aby to zrobić, wpisz nazwę arkusza, po której następuje wykrzyknik (!) i komórkę lub zakres komórek.

Na przykład odwołuje się to do komórek od A2 do A5 w arkuszu 2 tego samego skoroszytu: Arkusz 2!$A$2:$A$5.

Aby użyć funkcji WYSZUKAJ.PIONOWO w przykładowym arkuszu kalkulacyjnym, wykonaj następujące kroki:

1. Wybierz komórkę B2, aby wyświetlić pierwszy wynik porównania.

2. Wpisz następującą formułę: = WYSZUKAJ.PIONOWO(A2;Arkusz2!$A$2:$A$5; 1; FAŁSZ).

3. Naciśnij Enter, aby wyświetlić wynik porównania.

4. Wypełnij formułę, aby porównać wartości dla pozostałych wierszy w pierwszym arkuszu.

Wyniki będą wyglądać jak na poniższym obrazku:

Możesz uporządkować wyświetlacz, wyświetlając przyjazny dla użytkownika komunikat zamiast błędu, gdy nie zostanie znaleziony duplikat.

Na przykład ta formuła wyświetli „Tak” lub „Nie” odpowiednio dla znalezionych i nieznalezionych wartości:

=JEŻELI(CZY.NA(WYSZUKAJ.PIONOWO(A2, arkusz 2!$A$2:$A$5; 1; FAŁSZ)), „Nie”, „Tak”)

Więcej przykładów tej funkcji znajduje się w naszym artykule na temat używania WYSZUKAJ.PIONOWO do porównywania dwóch kolumn .

Co zrobić, jeśli obsługujesz różne skoroszyty?

Jeśli arkusze kalkulacyjne znajdują się w oddzielnych skoroszytach, użycie funkcji jest takie samo w przypadku dwóch plików programu Excel. Jednak odwoływanie się do drugiego arkusza jest nieco bardziej skomplikowane.

Musisz:

• Umieść nazwę skoroszytu programu Excel w nawiasach

• Podążaj za nazwą arkusza

• Ujmij skoroszyt i arkusz w cudzysłów.

Na przykład, jeśli komórki znajdują się w arkuszu o nazwie Arkusz2 w skoroszycie o nazwie „WB 2.xlsx”, format będzie wyglądał następująco:

„[WB 2.xlsx]Arkusz2”! $A$2:$A$5

Przed wprowadzeniem formuły zamknij drugi skoroszyt. W przeciwnym razie pojawi się błąd.

B. Jak używać funkcji LICZ.JEŻELI do znajdowania duplikatów w arkuszach roboczych?

Funkcja LICZ.JEŻELI w programie Excel służy do zliczania komórek w określonym zakresie, które spełniają określone kryteria.

Aby porównać wiele arkuszy, możesz policzyć liczbę komórek w drugim arkuszu, które pasują do komórki w pierwszym arkuszu.

Oto składnia funkcji:

=LICZ.JEŻELI(zakres; kryteria)

• Zakres : zakres komórek, które chcesz zliczyć na podstawie określonych kryteriów.

• Kryteria : warunek, który musi być spełniony, aby komórka została zliczona.

Aby użyć funkcji z przykładowymi danymi, wykonaj następujące kroki:

1. Wybierz komórkę B2, aby wyświetlić pierwszy wynik porównania.

2. Wpisz następującą formułę: =LICZ.JEŻELI(Arkusz2!$A$2:$A$5, A2)

3. Naciśnij Enter, aby wyświetlić wynik porównania.

4. Wypełnij formułę, aby porównać wartości dla pozostałych wierszy w pierwszym arkuszu.

Funkcja znajdzie jedno dopasowanie dla niektórych komórek, a żadne dla innych. Komórka porównania wyświetla liczbę. Oto wynik z przykładowych danych:

Funkcja LICZ.JEŻELI może być używana do innych przydatnych zadań, takich jak zliczanie niepustych komórek w programie Excel .

C. Jak używać funkcji EXACT do znajdowania duplikatów w arkuszach roboczych?

Funkcja EXACT w programie Excel może być również używana do wyszukiwania duplikatów w tych samych komórkach w dwóch różnych arkuszach programu Excel. Składnia to:

=DOKŁADNIE(tekst1; tekst2)

• text1 to pierwszy ciąg tekstowy, który chcesz porównać.

• text2 to drugi ciąg tekstowy, który chcesz porównać.

Wykonaj następujące kroki:

1. Wybierz komórkę B2.

2. Wpisz formułę =DOKŁADNIE(A2; Arkusz2!A2)

3. Naciśnij Enter, aby wyświetlić wynik porównania. Formuła zwróci PRAWDA, jeśli obie wartości są identyczne, lub FAŁSZ w przeciwnym razie.

4. Wypełnij formułę, aby porównać wartości dla pozostałych wierszy w pierwszym arkuszu.

Należy zauważyć, że ta metoda nie wyszukuje duplikatów w zakresie komórek. Zamiast tego szukasz tylko dopasowań opartych na tej samej komórce w innym arkuszu.

Może to być przydatne w przypadku uporządkowanych danych, w przypadku których oczekuje się tylko kilku wyjątków.

Funkcje WYSZUKAJ.PIONOWO, LICZ.JEŻELI i DOKŁADNIE są przydatne do znajdowania duplikatów, ale program Excel jest programem wszechstronnym i dostępne są inne opcje. W następnej sekcji przyjrzymy się, jak można użyć formatowania warunkowego do identyfikacji duplikatów w dwóch arkuszach.

2. Jak używać formatowania warunkowego dla zduplikowanych wierszy

W tej sekcji dowiesz się, jak używać formatowania warunkowego do znajdowania i wyróżniania zduplikowanych wierszy w dwóch arkuszach programu Excel.

Aby utworzyć regułę formatowania warunkowego, wykonaj następujące kroki:

1. Wybierz zakres komórek zawierających dane (w tym przypadku A2:A5).

2. Kliknij kartę „Strona główna” na wstążce programu Excel.

3. Kliknij „Formatowanie warunkowe” w grupie „Style”.

4. Wybierz „Nowa reguła” z menu rozwijanego.

Następnym zadaniem jest podanie formuły, która ma być używana przez regułę. Wykonaj następujące kroki:

1. Wybierz „Użyj formuły, aby określić, które komórki sformatować” w oknie dialogowym

2. Wprowadź następującą formułę: =LICZ.JEŻELI(Arkusz2!$A$2:$A$5, A2) > 0

Na koniec zastosuj preferowane formatowanie dla zduplikowanych komórek.

1. Kliknij przycisk Format, aby otworzyć okno dialogowe Formatowanie komórek.

2. Wybierz format, np. wypełnij duplikaty żółtym kolorem tła.

3. Kliknij OK.

Twoje zduplikowane dane są teraz podświetlone na żółto.

Jak korzystać z Menedżera reguł formatowania warunkowego

Po utworzeniu reguły formatowania warunkowego możesz zarządzać nią za pomocą Menedżera reguł formatowania warunkowego.

Aby uzyskać dostęp do menedżera:

1. Przejdź do karty Strona główna.

2. Kliknij Formatowanie warunkowe.

3. Wybierz „Zarządzaj regułami”.

Zobaczysz listę wszystkich reguł formatowania warunkowego zastosowanych do wybranego arkusza. Możesz edytować, usuwać lub zmieniać kolejność reguł, wybierając regułę i klikając odpowiednie przyciski.

Aby zastosować tę samą regułę do drugiego arkusza, wykonaj następujące czynności:

1. Wybierz zakres, który chcesz porównać w drugim arkuszu.

2. Przejdź do Menedżera reguł formatowania warunkowego.

3. Wybierz regułę, kliknij Duplikuj regułę, a następnie naciśnij Edytuj regułę.

4. Zastąp „Arkusz2” nazwą pierwszego arkusza do porównania.

Po zastosowaniu reguły formatowania warunkowego do obu arkuszy duplikaty zostaną podświetlone zgodnie z wybranym formatowaniem.

Pamiętaj, aby odpowiednio dostosować zakres i odwołania do komórek w formułach, aby uwzględnić wszystkie dane, które chcesz porównać.

Formatowanie warunkowe może wydawać się nieco prymitywne. Jeśli potrzebujesz dokładniejszej kontroli, odpowiedzią może być Power Query! W następnej sekcji omówimy, jak używać dodatku Power Query do znajdowania duplikatów.

3. Jak używać dodatku Power Query do wyszukiwania duplikatów w arkuszach roboczych

Power Query to narzędzie do przekształcania i przygotowywania danych w programie Microsoft Excel. Identyfikacja tych samych wartości to tylko jedno z wielu zadań analitycznych, które można wykonać za pomocą tego narzędzia.

W tym celu należy najpierw zaimportować dane z dwóch arkuszy do oddzielnych tabel. Wykonaj następujące kroki w każdym arkuszu:

1. Kliknij zakres komórek prawym przyciskiem myszy.

2. Wybierz „Pobierz dane z tabeli/zakresu”.

3. Zmień nazwę tabeli na odpowiednią.

Po zaimportowaniu obu arkuszy pierwszym zadaniem jest scalenie danych:

1. Przejdź do zakładki Dane.

2. Kliknij „Pobierz dane”.

3. Wybierz „Połącz zapytania”.

4. Wybierz „Scal” i wybierz dwie tabele.

5. Kliknij dwie kluczowe kolumny.

6. Wybierz „Inner” jako „Dołącz rodzaj” i kliknij OK.

Edytor dodatku Power Query zostanie otwarty z połączonymi danymi z obu tabel w arkuszu programu Excel. Zobaczysz dwie kolumny, po jednej z każdej tabeli. Ponieważ interesują Cię tylko zduplikowane wartości, możesz usunąć drugą kolumnę.

Możesz kliknąć „Zamknij i załaduj” w edytorze Power Query, aby załadować duplikaty do nowego arkusza.

Aby poznać więcej aspektów tej zaawansowanej funkcji, postępuj zgodnie z przykładami w naszym artykule na temat korzystania z dodatku Power Query w programie Excel .

Excel ma również narzędzia i dodatki innych firm, które dodają możliwość bezproblemowego znajdowania duplikatów, więc przyjrzyjmy się niektórym z tych narzędzi w następnej sekcji.

4. Narzędzia i dodatki do identyfikowania duplikatów w arkuszach roboczych

Zewnętrzne narzędzia i dodatki mogą oferować zaawansowane funkcje, które mogą nie być dostępne w natywnych funkcjach programu Excel. Narzędzia te mogą jeszcze bardziej usprawnić proces porównywania arkuszy pod kątem duplikatów.

Spreadsheet Compare to narzędzie firmy Microsoft, które umożliwia porównanie dwóch skoroszytów obok siebie, podkreślając różnice i łatwo identyfikując duplikaty. Można go pobrać ze strony Microsoftu.

Istnieje kilka dodatków, które można zainstalować, aby zautomatyzować proces znajdowania duplikatów. Jednym z przykładów jest „ Usuwanie duplikatów ”. Aby zainstalować dodatek:

1. Przejdź do zakładki Wstaw.

2. Kliknij „Pobierz dodatek”.

3. Wyszukaj „Duplikat”.

4. Kliknij „Dodaj” na wybranym narzędziu.

5. Jak wizualnie sprawdzić duplikaty w dwóch arkuszach

Jeśli wszystko inne zawiedzie, użyj oczu! Okno dialogowe Rozmieść okna w programie Excel umożliwia wyświetlanie obok siebie wielu arkuszy lub skoroszytów.

Chociaż nie znajduje bezpośrednio duplikatów, może pomóc w wizualnym porównaniu danych w arkuszach lub skoroszytach w celu wykrycia duplikatów. Wykonaj następujące kroki:

1. Kliknij kartę „Widok” na wstążce programu Excel.

2. Kliknij „Rozmieść wszystko” w grupie „Okno”.

3. Wybierz opcję ułożenia np. „Pionowo” lub „Poziomo”.

Spowoduje to wyświetlenie obu arkuszy obok siebie lub jeden nad drugim. Teraz możesz ręcznie porównać dane w każdym arkuszu, aby zidentyfikować duplikaty.

Musisz przewinąć dane i wizualnie sprawdzić każdą wartość, aby znaleźć dopasowania.

Należy zauważyć, że ta metoda nie jest wydajna w przypadku dużych zbiorów danych, ponieważ wymaga ręcznego porównania. Korzystanie z innych metod opisanych w tym artykule będzie bardziej efektywne w znajdowaniu duplikatów w większych zestawach danych.

I to jest ostatnia z naszych popularnych metod znajdowania zduplikowanych wartości w arkuszach Excela! W następnej sekcji podamy kilka wskazówek dotyczących przygotowywania arkuszy roboczych.

3 wskazówki dotyczące przygotowywania arkuszy programu Excel

Zanim zaczniesz porównywać wiele arkuszy, upewnij się, że kolumny i wiersze zestawów danych są odpowiednio wyrównane.

Upewnij się, że oba arkusze Excela mają taką samą strukturę i te same nazwy nagłówków. W razie potrzeby możesz zmienić rozmieszczenie kolumn w obu arkuszach, aby do siebie pasowały.

Oto trzy sugestie, aby zapewnić dokładne porównania:

1. Ułóż swoje dane w tej samej kolejności w obu arkuszach. Ułatwia to efektywne działanie funkcji programu Excel.

2. Normalizuj swoje dane, stosując spójne formatowanie, stosowanie wielkich liter i typy danych. Zapobiegnie to niedopasowaniu wpisów z powodu drobnych różnic.

3. Usuń niepotrzebne puste wiersze lub kolumny, ponieważ mogą one zakłócać proces porównywania.

Możesz ocenić, ile duplikatów masz w zbiorze danych, zliczając unikalne wartości. W tym filmie przedstawiono kilka metod:

Jak radzić sobie z błędami i niespójnościami

Niespójności w Twoich danych mogą mieć wpływ na proces porównywania. Oto cztery wskazówki dotyczące rozwiązywania niespójności:

1. Sprawdź rozbieżności w typach danych, takie jak mieszanie wartości tekstowych i liczbowych w tej samej kolumnie.

2. Upewnij się, że używane jest spójne formatowanie dat, liczb i innych typów danych.

3. Sprawdź swoje dane pod kątem brakujących lub nieprawidłowych wpisów i zaktualizuj je, jeśli to konieczne.

4. Standaryzuj skróty lub niespójne konwencje nazewnictwa w swoich zestawach danych.

Końcowe przemyślenia

Znalezienie duplikatów w dwóch arkuszach programu Excel to podstawowe zadanie zarządzania danymi i ich analizy, zapewniające integralność i dokładność danych. Program Excel oferuje wiele technik identyfikowania duplikatów, z których każda ma swoje zalety i ograniczenia.

Wybór metody zależy od potrzeb użytkownika, wielkości i złożoności zbioru danych oraz pożądanego wyniku. W przypadku mniejszych zestawów danych i prostych porównań użycie funkcji WYSZUKAJ.PIONOWO , LICZ.JEŻELI lub formatowania warunkowego może być wystarczające.

W przypadku większych zestawów danych lub bardziej złożonych transformacji danych dodatek Power Query jest potężnym i elastycznym narzędziem, które może obsłużyć szeroki zakres zadań związanych z przygotowywaniem danych, w tym wyszukiwanie duplikatów.

Podsumowując, porównywanie arkuszy Excela pod kątem duplikatów to bardzo przydatna umiejętność, którą warto mieć w zestawie narzędzi. Dzięki sztuczkom opisanym w tym artykule możesz wykryć te nieznośne duplikaty i zachować czystość danych.

Zaufaj nam, gdy staniesz się w tym lepszy, szybko poradzisz sobie z zadaniami związanymi z danymi i zaimponujesz wszystkim wokół siebie!