Jak porównać dwie listy w programie Excel? (6 najlepszych metod)
Poniżej wymieniono 6 różnych metod, które są używane do porównywania dwóch list kolumn w programie Excel pod kątem dopasowań i różnic.
- Metoda 1: Porównaj dwie listy za pomocą operatora znaku równości
- Metoda 2: Dopasuj dane za pomocą techniki różnic między wierszami
- Metoda 3: Dopasuj różnicę wierszy przy użyciu warunku JEŻELI
- Metoda 4: Dopasuj dane, nawet jeśli występuje różnica w wierszach
- Metoda 5: Podświetl wszystkie pasujące dane przy użyciu formatowania warunkowego
- Metoda 6: Technika częściowego dopasowania
Omówmy teraz szczegółowo każdą z metod na przykładzie -
Możesz pobrać ten szablon porównania dwóch list tutaj - szablon porównania dwóch list programu Excel# 1 Porównaj dwie listy za pomocą operatora znaku równości
- Krok 1: W następnej kolumnie bezpośrednio po dwóch kolumnach wstaw nową kolumnę o nazwie „Status”.
- Krok 2: Teraz umieść formułę w komórce C2 jako = A2 = B2.
- Krok 3: Ta formuła sprawdza, czy wartość komórki A2 jest równa komórce B2. Jeśli obie wartości komórek są dopasowane, otrzymamy jako wynik PRAWDA, w przeciwnym razie otrzymamy wynik jako FAŁSZ.
- Krok 4: Teraz przeciągnij formułę do komórki C9, aby określić inne wartości,
Wszędzie tam, gdzie mamy te same wartości we wspólnych wierszach, otrzymujemy wynik jako TRUE lub FALSE.
# 2 Dopasuj dane za pomocą techniki różnicowania wierszy
Prawdopodobnie nie używałeś techniki „Różnica wierszy” w swoim miejscu pracy, ale dzisiaj pokażę ci, jak używać tej techniki do dopasowywania danych wiersz po wierszu.
- Krok 1: Aby podświetlić niezgodne komórki wiersz po wierszu, najpierw wybierz całe dane.
- Krok 2: Teraz naciśnij klawisz skrótu programu Excel „ F5 ”, aby otworzyć narzędzie „Przejdź do specjalnego”.
- Krok 3: Naciśnij klawisz F5, aby otworzyć to okno. Teraz w oknie „Go-To” naciśnij zakładkę „Special”.
- Krok 4: W następnym oknie „Go To Special” wybierz opcję „Row Difference”. Po wybraniu „Różnica rzędów” kliknij „Ok”.
Otrzymujemy następujący wynik.
Jak widać w powyższym oknie, zaznaczył komórki wszędzie tam, gdzie występuje różnica w wierszach. Teraz wypełnij nim trochę koloru, aby podświetlić wartości różnic między wierszami.
# 3 Dopasuj różnicę wierszy za pomocą warunku IF
Jak możemy pominąć warunek JEŻELI, gdy chcemy dopasować dane wiersz po wierszu. W pierwszym przykładzie otrzymaliśmy jako wynik PRAWDA lub FAŁSZ, ale co zrobić, jeśli potrzebujemy wyniku różnicy zamiast domyślnych wyników „PRAWDA lub FAŁSZ”. Załóżmy, że potrzebujesz wyniku jako „Pasujący”, jeśli nie ma różnicy między wierszami, a wynik powinien być „Niezgodny”, jeśli występuje różnica między wierszami.
- Krok 1: Najpierw otwórz warunek IF w komórce C2.
- Krok 2: Zastosuj test logiczny jako A2 = B2.
- Krok 3: Jeśli ten test logiczny ma wartość PRAWDA, wprowadź kryteria wyniku. W tym scenariuszu kryterium wyniku to „Dopasowanie”, a jeśli wiersz nie jest zgodny, potrzebujemy wyniku jako „Niezgodny”.
- Krok 4: Zastosuj formułę, aby uzyskać wynik.
- Krok 5: Teraz przeciągnij formułę do komórki C9, aby określić inne wartości,
# 4 Dopasuj dane, nawet jeśli występuje różnica w wierszach
Dopasowywanie danych w metodach różnic w wierszach może nie działać przez cały czas, wartość może również znajdować się w innych komórkach, więc w tych scenariuszach musimy użyć różnych technologii.
Spójrz teraz na poniższe dane.
Na powyższym obrazku mamy dwie listy liczb. Musimy porównać listę 2 z listą 1. Użyjmy więc naszej ulubionej funkcji WYSZUKAJ.PIONOWO.
Więc jeśli dane są zgodne, otrzymujemy liczbę, w przeciwnym razie otrzymujemy wartość błędu jako # N / A.
Wyświetlanie wartości błędów wcale nie wygląda dobrze. Zamiast pokazywać błąd, zastąpmy je słowem „Niedostępne”. W tym celu użyj funkcji IFERROR w programie Excel.
# 5 Podświetl wszystkie pasujące dane
Jeśli nie jesteś fanem formuł programu Excel, nie martw się, nadal możemy dopasować dane również bez formuły. Używając prostego formatowania warunkowego w programie Excel, możemy faktycznie wyróżnić wszystkie pasujące dane z dwóch list.
- Krok 1: W tym celu najpierw wybierz dane.
- Krok 2: Teraz przejdź do Formatowanie warunkowe i wybierz „Podświetl reguły komórki >> Zduplikowane wartości”.
- Krok 3: Teraz zobaczysz okno formatowania „Zduplikowane wartości komórek”.
- Krok 4: W programie Excel możesz wybrać różne kolory formatowania z rozwijanej listy. Wybierz pierwszy kolor formatowania i naciśnij przycisk ok.
- Krok 5: Podświetli to wszystkie pasujące dane z dwóch list.
- Krok 6: Tylko na wszelki wypadek, zamiast zaznaczać wszystkie pasujące dane, jeśli chcesz podświetlić niepasujące dane, to w oknie „Zduplikowane wartości” wybierz opcję „Unikalne”.
Podświetli to wszystkie niezgodne wartości.
# 6 Technika częściowego dopasowania
Widzieliśmy problem niepełnych lub tych samych danych na dwóch listach. Na przykład, jeśli dane z listy 1 mają „ABC Pvt Ltd”, a na liście 2 tylko „ABC” . W takich przypadkach wszystkie nasze domyślne formuły i narzędzia w ogóle nie rozpoznają. W takich przypadkach musimy zastosować specjalną gwiazdkę (*), aby dopasować wartości częściowe.
Na liście 1 mamy nazwę firmy i szczegóły dotyczące przychodów. Na liście 2 mamy nazwy firm, ale nie mamy dokładnych wartości, jak na liście 1. Jest to trudna sytuacja, z którą wszyscy mieliśmy do czynienia w naszym miejscu pracy.
W takich przypadkach nadal możemy dopasować dane za pomocą specjalnego znaku gwiazdki (*).
Otrzymujemy następujący wynik.
Teraz przeciągnij formułę do komórki E9, aby określić inne wartości,
Znak wieloznaczny gwiazdka (*) został użyty do oznaczenia dowolnej liczby znaków, więc będzie pasował do pełnego znaku słowa „ABC” jako „ABC Pvt Ltd”.
Rzeczy do zapamiętania
- Korzystanie z powyższych technik porównywania dwóch list w programie Excel zależy od struktury danych.
- Dopasowanie wiersz po wierszu nie jest najlepszym rozwiązaniem, jeśli dane nie są zorganizowane.
- WYSZUKAJ.PIONOWO to często używana formuła dopasowywania wartości.
