Porównaj listy z VLOOKUP
powszechnym wymogiem w Excelu jest konieczność porównania dwóch list. W tym samouczku porównamy listy z VLOOKUPEM.
aby podświetlić brakujące rekordy, podświetlić pasujące rekordy lub zwrócić wartość, konieczne może być porównanie dwóch list.
funkcja VLOOKUP może być używana do porównywania list w programie Excel, szukając wartości z jednej listy w drugiej. Następnie możesz podjąć wymagane działanie.
ten samouczek pokazuje, jak porównać listy z VLOOKUPEM. Funkcja VLOOKUP może być używana z innymi formułami lub funkcjami programu Excel, aby osiągnąć różne wyniki związane z porównywaniem dwóch list.
Połącz z funkcją IF, aby zwrócić wartość
powiedzmy, że mamy dwie listy klientów, takie jak na poniższym obrazku. Obie listy są identyczne z wyjątkiem tego, że lista 1 ma mniej klientów niż Lista 2.
chcemy porównać obie listy i zidentyfikować różnice. Pole ID zostanie użyte do sprawdzenia obu list, ponieważ jest to unikalne pole.
w tym pierwszym przykładzie poszukamy klientów z listy 1 na liście 2 i zwrócimy tekst „nie znaleziono”, jeśli ich brakuje. Jeśli zostaną znalezione na liście 2, komórka pozostanie pusta.
poniższy wzór wykorzystuje funkcję VLOOKUP wraz z funkcjami IF I ISNA, aby osiągnąć ten cel.
spójrzmy na rolę, jaką odgrywa każda funkcja w tej formule.
: Funkcja VLOOKUP została użyta do wyszukania klienta na drugiej liście i zwrócenia identyfikatora klienta (określonego jako 1 w formule).
jeśli funkcja VLOOKUP nie może znaleźć klienta, zwraca komunikat o błędzie #N/A. To jest to, co nas interesuje, ponieważ oznacza to, że klient znajduje się na liście 1, ale nie na liście 2.
ISNA: funkcja ISNA jest używana do wykrywania wiadomości # N/A wywołanej przez VLOOKUP i tłumaczenia jej na True. Jeśli ID jest zwracane, zwraca False.
jeśli: Funkcja IF wyświetli tekst „nie znaleziono”, jeśli ISNA zwraca True, a komórka pozostanie pusta, jeśli Isna zwróci False. Ta funkcja służy do podejmowania dowolnych działań na podstawie ustaleń z VLOOKUP.
Porównaj listy z VLOOKUP, aby podświetlić brakujące rekordy
ta formuła może być również używana z formatowaniem warunkowym, aby podświetlić różnice między dwiema listami, zamiast zwracać wartość.
ten przykład pokazuje, jak zmienić kolor komórki całego wiersza tabeli, gdy na drugiej liście brakuje klienta. Formatowanie całego wiersza, a nie tylko pojedynczej komórki, ułatwi identyfikację brakujących rekordów, zwłaszcza w tabeli z wieloma kolumnami.
- Zaznacz wszystkie komórki tabeli z wyjątkiem wiersza nagłówka.
- kliknij Formatowanie warunkowe na karcie Strona główna wstążki i wybierz nową regułę.
- Wybierz Użyj formuły, aby określić, które komórki mają zostać sformatowane z górnej połowy okna. W dolnej połowie wprowadź poniższą formułę do podanego pola.
- kliknij przycisk Format i wybierz Formatowanie, które chcesz zastosować do wiersza brakujących rekordów
Porównaj dwie listy i wyróżnij pasujące rekordy
aby porównać dwie listy i wyróżnić pasujące rekordy, funkcja NOT będzie musiała zostać dodana do poprzedniej formuły.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A:$F,1,FALSE)))
funkcja NOT jest używana do odwrócenia wyniku wyszukiwania. Więc zamiast podświetlić brakujące rekordy, podświetla mecze.