Jak porównać dwie kolumny w Excelu ( za pomocą VLOOKUP & IF)

kiedy pracujesz z danymi w programie Excel, prędzej czy później będziesz musiał porównać dane. Może to być porównanie dwóch kolumn lub nawet danych w różnych arkuszach / skoroszytach.

w tym samouczku programu Excel pokażę Ci różne metody porównywania dwóch kolumn w programie Excel i wyszukiwania dopasowań lub różnic.

istnieje wiele sposobów, aby to zrobić w programie Excel i w tym samouczku pokażę Ci niektóre z nich (takie jak porównywanie za pomocą formuły VLOOKUP lub formuły if lub formatowania warunkowego).

więc zaczynajmy!

Porównaj dwie kolumny (obok siebie)

jest to najbardziej podstawowy typ porównania, w którym musisz porównać komórkę w jednej kolumnie z komórką w tym samym wierszu w innej kolumnie.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i po prostu chcesz sprawdzić, czy wartość w kolumnie a w określonej komórce jest taka sama (lub inna) w porównaniu z wartością w sąsiedniej komórce.

oczywiście możesz to zrobić, gdy masz mały zestaw danych, gdy masz duży, możesz użyć prostej formuły porównawczej, aby to zrobić. I pamiętaj, że zawsze istnieje szansa na ludzki błąd, gdy robisz to ręcznie.

pozwól, że pokażę Ci kilka prostych sposobów, aby to zrobić.

Porównaj obok siebie, używając operatora równego znakowi

Załóżmy, że masz poniższy zestaw danych i chcesz wiedzieć, które wiersze mają pasujące dane, a które wiersze mają różne dane.

Poniżej znajduje się prosty wzór na porównanie dwóch kolumn (obok siebie):

=A2=B2

powyższa formuła da ci TRUE, jeśli obie wartości są takie same i FALSE w przypadku, gdy nie są.

teraz, jeśli chcesz znać wszystkie wartości, które pasują, po prostu zastosuj filtr i Pokaż tylko wszystkie prawdziwe wartości. A jeśli chcesz poznać wszystkie wartości, które są różne, filtruj wszystkie wartości, które są fałszywe (Jak pokazano poniżej):

Porównaj obok siebie za pomocą funkcji IF

inną metodą, której możesz użyć do porównania dwóch kolumn, może być użycie funkcji IF.

jest to podobne do powyższej metody, gdzie użyliśmy operatora equal to ( = ), z jedną dodatkową zaletą. Korzystając z funkcji IF, możesz wybrać wartość, którą chcesz uzyskać, gdy są dopasowania lub różnice.

na przykład, jeśli istnieje dopasowanie, możesz uzyskać tekst „Dopasuj” lub uzyskać wartość taką jak 1. Podobnie, gdy występuje niedopasowanie, możesz zaprogramować formułę, aby dać ci tekst „niedopasowanie” lub dać 0 lub pustą komórkę.

poniżej znajduje się formuła IF, która zwraca „Match”, gdy dwie komórki mają wartość komórki i „Not a Match”, gdy wartość jest inna.

=IF(A2=B2,"Match","Not a Match")

powyższa formuła używa tego samego warunku, aby sprawdzić, czy dwie komórki (w tym samym wierszu) mają pasujące dane, czy nie (A2=B2). Ale ponieważ używamy funkcji IF, możemy poprosić ją o zwrócenie określonego tekstu w przypadku, gdy warunek jest True lub False.

gdy masz wyniki formuły w osobnej kolumnie, możesz szybko filtrować dane i uzyskać wiersze, które mają pasujące dane lub wiersze z niedopasowanymi danymi.

Wyróżnij Wiersze z pasującymi danymi (lub różnymi danymi)

kolejnym świetnym sposobem na szybkie sprawdzenie wierszy, które mają pasujące dane (lub mają inne dane), jest podświetlenie tych wierszy za pomocą formatowania warunkowego.

możesz zrobić oba – podświetl wiersze, które mają tę samą wartość w wierszu, jak również przypadek, gdy wartość jest inna.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz podświetlić wszystkie wiersze, w których nazwa jest taka sama.

poniżej znajdują się kroki, aby użyć formatowania warunkowego, aby wyróżnić wiersze z pasującymi danymi:

1. wybierz cały zestaw danych (z wyjątkiem nagłówków)
2. kliknij kartę Strona główna
3. w grupie Style kliknij Formatowanie warunkowe
4. w opcjach, które się pojawią, kliknij „Nowa reguła’
5. w oknie dialogowym „Nowa reguła formatowania” kliknij opcję – „Użyj formuły, aby określić, które komórki mają zostać sformatowane’
6. w polu „Formatuj wartości, w których ta formuła jest prawdziwa” wprowadź formułę: =$A2=$B2
7. kliknij przycisk Formatuj
8. kliknij kartę „Wypełnij” i wybierz kolor, w którym chcesz podświetlić wiersze o tej samej wartości w obu kolumnach
9. kliknij OK

powyższe kroki natychmiast podświetlą wiersze, w których nazwa jest to samo w obu kolumnach a i B (w tym samym wierszu). A w przypadku, gdy nazwa jest inna, te wiersze nie będą podświetlone.

jeśli chcesz porównać dwie kolumny i wyróżnić wiersze, w których nazwy są różne, użyj poniższej formuły w formatowanie warunkowe okno dialogowe (w kroku 6).

=$A2<>$B2

Jak to działa?

kiedy używamy formatowania warunkowego z formułą, podświetla ona tylko te komórki, w których formuła jest prawdziwa.

gdy użyjemy $A2=$B2, sprawdzi każdą komórkę (w obu kolumnach) i sprawdzi, czy wartość w wierszu w kolumnie A jest równa wartości w kolumnie B, czy nie.

w przypadku, gdy jest to dokładne dopasowanie, podświetli go w określonym kolorze, a w przypadku, gdy nie pasuje, nie będzie.

najlepszą częścią formatowania warunkowego jest to, że nie wymaga użycia formuły w osobnej kolumnie. Ponadto, gdy zastosujesz regułę na zbiorze danych, pozostaje ona dynamiczna. Oznacza to, że jeśli zmienisz dowolną nazwę w zbiorze danych, formatowanie warunkowe zostanie odpowiednio dostosowane.

Porównaj dwie kolumny za pomocą VLOOKUP (Znajdź pasujące/różne dane)

w powyższych przykładach pokazałem, jak porównać dwie kolumny (lub listy), gdy porównujemy tylko komórki obok siebie.

w rzeczywistości rzadko tak się dzieje.

w większości przypadków będziesz miał dwie kolumny z danymi i będziesz musiał dowiedzieć się, czy punkt danych w jednej kolumnie istnieje w drugiej kolumnie, czy nie.

w takich przypadkach nie można użyć prostego znaku równości lub nawet funkcji IF.

potrzebujesz czegoś mocniejszego …

… czegoś co jest w sam raz dla Ciebie!

pozwól, że pokażę Ci dwa przykłady, w których porównujemy dwie kolumny w programie Excel za pomocą funkcji VLOOKUP, aby znaleźć dopasowania i różnice.

Porównaj dwie kolumny używając VLOOKUP i znajdź dopasowania

Załóżmy, że mamy zestaw danych, jak pokazano poniżej, gdzie mamy kilka nazw w kolumnach a I B.

jeśli musisz dowiedzieć się, jakie są nazwy w kolumnie B, które są również w kolumnie A, możesz użyć poniższej formuły VLOOKUP:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

powyższa formuła porównuje dwie kolumny (a i B) i podaje nazwę w przypadku, gdy nazwa znajduje się w kolumnie B, a także A, i zwraca „brak dopasowania” w przypadku, gdy nazwa znajduje się w kolumnie B, a nie w kolumnie A.

domyślnie funkcja VLOOKUP zwróci błąd #N/A W przypadku, gdy nie znajdzie dokładnego dopasowania. Aby uniknąć wystąpienia błędu, owinąłem funkcję VLOOKUP w funkcji IFERROR, dzięki czemu daje „brak dopasowania”, gdy nazwa nie jest dostępna w kolumnie A.

Możesz również wykonać porównanie w drugą stronę-aby sprawdzić, czy nazwa znajduje się w kolumnie A, a także w kolumnie B. Poniższa formuła zrobiłaby to:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

Porównaj dwie kolumny za pomocą VLOOKUP i znajdź różnice (brakujące punkty danych)

podczas gdy w powyższym przykładzie sprawdziliśmy, czy dane w jednej kolumnie były w innej kolumnie, czy nie.

możesz również użyć tej samej koncepcji, aby porównać dwie kolumny za pomocą funkcji VLOOKUP i znaleźć brakujące dane.

Załóżmy, że mamy zestaw danych, jak pokazano poniżej, gdzie mamy kilka nazw w kolumnach a I B.

jeśli musisz dowiedzieć się, jakie są nazwy w kolumnie B, których nie ma w kolumnie A, możesz użyć poniższej formuły VLOOKUP:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

powyższy wzór sprawdza nazwę w kolumnie B ze wszystkimi nazwami w kolumnie A. W przypadku, gdy znajdzie dokładne dopasowanie, zwróci tę nazwę, a w przypadku, gdy nie znajdzie dokładnego dopasowania, zwróci błąd #N/A.

ponieważ jestem zainteresowany znalezieniem brakujących nazw, które tam są, jest kolumna B, a nie w kolumnie A, muszę znać nazwy, które zwracają błąd #N / A.

dlatego owinąłem funkcję VLOOKUP w funkcje IF I ISERROR. Ta cała formuła daje wartość – „niedostępne”, gdy brakuje nazwy w kolumnie A, i” dostępne”, gdy jest obecna.

aby poznać wszystkie brakujące nazwy, możesz filtrować kolumnę wyników na podstawie wartości „niedostępne”.

Możesz również użyć poniższej funkcji dopasowania, aby uzyskać ten sam wynik:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

typowe zapytania podczas porównywania dwóch kolumn

Poniżej znajdują się typowe zapytania, które zwykle otrzymuję, gdy ludzie próbują porównać dane w dwóch kolumnach w programie Excel.

Jak porównać wiele kolumn w programie Excel w tym samym wierszu dla dopasowań? Policz również całkowite duplikaty.

Daliśmy procedurę porównania dwóch kolumn w Excelu dla tego samego wiersza powyżej. Ale jeśli chcesz porównać wiele kolumn w programie excel dla tego samego wiersza, zobacz przykład

=IF(AND(A2=B2, A2=C2),"Full Match", "")

tutaj porównaliśmy dane kolumny A, kolumny B i kolumny C. Następnie zastosowałem powyższą formułę w kolumnie D i uzyskałem wynik.

teraz, aby policzyć duplikaty, musisz użyć funkcji Countif.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

Jakiego operatora używasz do meczów i różnic?

Poniżej znajdują się operatory, z których można korzystać:

• aby znaleźć dopasowania, użyj znaku equal to (=)
• aby znaleźć różnice (niedopasowania), użyj znaku not-equal – to (<>)

Q3. Jak porównać dwie różne tabele i wyciągnąć pasujące dane?

W tym celu możesz użyć funkcji VLOOKUP lub funkcji dopasuj indeks &. Aby lepiej to zrozumieć, weźmiemy przykład.

tutaj weźmiemy dwie tabele i teraz chcemy wyciągnąć pasujące dane. W pierwszej tabeli masz zestaw danych, a w drugiej tabeli weź listę owoców, a następnie użyj pull pasujących danych w innej kolumnie. Aby dopasować pull, użyj wzoru

=INDEX($B:$B,MATCH($D2,$A:$A,0))

Q4. Jak usunąć duplikaty w programie Excel?

Aby usunąć zduplikowane dane, musisz najpierw znaleźć zduplikowane wartości.

aby znaleźć duplikat, możesz użyć różnych metod, takich jak formatowanie warunkowe, Vlookup, Instrukcja If I wiele innych. Excel ma również wbudowane narzędzie, w którym można po prostu wybrać Dane i usunąć duplikaty z kolumny lub nawet wielu kolumn

Q5. Widzę, że w obu kolumnach jest pasująca wartość. Jednak formuły, które udostępniłeś powyżej, nie traktują ich jako dokładnych dopasowań. Dlaczego?

Ans: Excel uważa coś za dokładne dopasowanie, gdy każdy znak jednej komórki jest równy drugiej. Istnieje duża szansa, że w zbiorze danych znajdują się spacje wiodące lub końcowe.

chociaż te spacje mogą nadal sprawiać, że wartości wydają się równe gołym okiem, w przypadku Excela są one inne. Jeśli masz taki zestaw danych, najlepiej pozbyć się tych spacji (możesz do tego użyć funkcji Excela, takich jak TRIM).

Jak porównać dwie kolumny, które dają wynik jako TRUE, gdy wszystkie wartości całkowite pierwszych kolumn są nie mniejsze niż wartości całkowite drugiej kolumny. Aby rozwiązać ten problem, nie wymagam formatowania warunkowego, funkcji Vlookup, instrukcji If I żadnych innych formuł. Potrzebuję formuły, żeby rozwiązać ten problem.

Możesz użyć formuły array do rozwiązania tego problemu.

składnia to {=AND(H6:H12>I6:I12)}. To da ci” True „jako wynik, gdy wartość kolumny H jest większa niż wartość w kolumnie I, w przeciwnym razie wynikiem będzie” False”.

Możesz również polubić następujące samouczki Excela:

• Porównaj dwie kolumny w programie Excel (dla dopasowań i różnic)
• jak ukryć kolumny na podstawie wartości komórki w programie Excel
• jak podzielić jedną kolumnę na wiele kolumn w programie Excel
• jak wybrać alternatywne kolumny w programie Excel (lub każdą N-tą kolumnę)
• jak wkleić w filtrowanej kolumnie pomijając ukryte komórki
• najlepsze książki Excel (które uczynią Cię Excel Pro w 2020)

zgłoś to ogłoszenie