Jak porównać dwie kolumny w Excelu (dla dopasowań i różnic)
Zobacz wideo – Porównaj dwie kolumny w Excelu dla dopasowań i różnic
Jednym z pytań, które często dostaję jest – „jak porównać dwie kolumny w Excelu?”.
Można to zrobić na wiele różnych sposobów, a metoda, której należy użyć, będzie zależeć od struktury danych i tego, co użytkownik chce od niej uzyskać.
Na przykład, możesz chcieć porównać dwie kolumny i znaleźć lub podświetlić wszystkie pasujące punkty danych (które są w obu kolumnach), lub tylko różnice (gdzie punkt danych jest w jednej kolumnie, a nie w drugiej), itp.
Ponieważ jestem o to pytany tak często, postanowiłem napisać ten masywny samouczek z zamiarem pokrycia większości (jeśli nie wszystkich) możliwych scenariuszy.
Jeśli uznasz to za przydatne, przekaż to innym użytkownikom Excela.
Ten samouczek obejmuje:
Zauważ, że techniki porównywania kolumn pokazane w tym samouczku nie są jedynymi.
W oparciu o twój zestaw danych, możesz potrzebować zmienić lub dostosować metodę. Jednak podstawowe zasady pozostaną takie same.
Jeśli uważasz, że jest coś, co można dodać do tego tutoriala, daj mi znać w sekcji komentarzy
Compare Two Columns For Exact Row Match
Ten jest najprostszą formą porównania. W tym przypadku musisz wykonać porównanie wiersz po wierszu i określić, które wiersze mają te same dane, a które nie.
Przykład: Compare Cells in the Same Row
Poniżej znajduje się zestaw danych, w którym muszę sprawdzić, czy nazwa w kolumnie A jest taka sama w kolumnie B, czy nie.
Jeżeli jest zgodność, potrzebuję wynik jako „TRUE”, a jeżeli nie ma zgodności, to potrzebuję wynik jako „FALSE”.
Poniższa formuła to zrobi:
=A2=B2
Przykład: Porównaj komórki w tym samym wierszu (używając formuły IF)
Jeśli chcesz uzyskać bardziej opisowy wynik, możesz użyć prostej formuły IF, aby zwrócić „Dopasuj”, gdy nazwy są takie same i „Niedopasowanie”, gdy nazwy są różne.
=IF(A2=B2,"Match","Mismatch")
Uwaga: W przypadku, gdy chcesz, aby porównanie uwzględniało wielkość liter, użyj następującej formuły JEŻELI:
=IF(EXACT(A2,B2),"Match","Mismatch")
Przy użyciu powyższej formuły, 'IBM’ i 'ibm’ będą uważane za dwie różne nazwy i powyższa formuła zwróci „niedopasowanie”.
Przykład: Podświetl wiersze z pasującymi danymi
Jeśli chcesz podświetlić wiersze, które mają pasujące dane (zamiast uzyskiwać wynik w osobnej kolumnie), możesz to zrobić, używając Formatowania warunkowego.
Oto kroki, aby to zrobić:
- Wybierz cały zbiór danych.
- Kliknij kartę „Strona główna”.
- W grupie Style kliknij opcję „Formatowanie warunkowe”.
- Z rozwijanej listy kliknij opcję „Nowa reguła”.
- W oknie dialogowym „Nowa reguła formatowania” kliknij opcję „Użyj formuły do określenia komórek do sformatowania”.
- W polu formuły wprowadź formułę: =$A1=$B1
- Kliknij przycisk Format i określ format, który chcesz zastosować do pasujących komórek.
- Kliknij przycisk OK.
W ten sposób zostaną zaznaczone wszystkie komórki, w których nazwy są takie same w każdym wierszu.
Porównaj dwie kolumny i podświetl pasujące dane
Jeśli chcesz porównać dwie kolumny i podświetlić pasujące dane, możesz skorzystać z funkcji duplikatów w formatowaniu warunkowym.
Zauważ, że jest to inne rozwiązanie niż to, które widzieliśmy podczas porównywania każdego wiersza. W tym przypadku, nie będziemy robić porównania wiersz po wierszu.
Przykład: Compare Two Columns and Highlight Matching Data
Często dostajemy zestawy danych, w których są dopasowania, ale mogą one nie być w tym samym wierszu.
Coś jak pokazano poniżej:
Zauważ, że lista w kolumnie A jest większa niż ta w B. Ponadto niektóre nazwy znajdują się na obu listach, ale nie w tym samym wierszu (np. IBM, Adobe, Walmart).
Jeśli chcesz zaznaczyć wszystkie pasujące nazwy firm, możesz to zrobić, używając formatowania warunkowego.
Oto kroki, jak to zrobić:
- Wybierz cały zestaw danych.
- Kliknij kartę Strona główna.
- W grupie Style kliknij opcję 'Formatowanie warunkowe’.
- Najedź kursorem na opcję Reguły podświetlania komórek.
- Kliknij opcję Duplikuj wartości.
- W oknie dialogowym Duplikuj wartości upewnij się, że wybrana jest opcja 'Duplikuj’.
- Zdefiniuj formatowanie.
- Kliknij OK.
Powyższe kroki dałyby wynik jak poniżej.
Uwaga: Reguła duplikowania formatowania warunkowego nie uwzględnia wielkości liter. Tak więc „Apple” i „apple” są uważane za takie same i zostaną podświetlone jako duplikaty.
Przykład: Compare Two Columns and Highlight Mismatched Data
W przypadku gdy chcesz podświetlić nazwiska, które występują na jednej liście, a na drugiej nie, możesz użyć formatowania warunkowego również do tego celu.
- Zaznacz cały zestaw danych.
- Kliknij kartę Home.
- W grupie Style kliknij opcję „Formatowanie warunkowe”.
- Najechać kursorem na opcję Highlight Cell Rules.
- Kliknąć na Duplicate Values.
- W oknie dialogowym Duplicate Values upewnić się, że wybrana jest opcja 'Unique’.
- Sprecyzuj formatowanie.
- Kliknij OK.
W ten sposób uzyskasz wynik jak poniżej. Podświetla wszystkie komórki, które mają nazwę, która nie występuje na drugiej liście.
Compare Two Columns and Find Missing Data Points
Jeśli chcesz określić, czy punkt danych z jednej listy występuje na drugiej liście, musisz użyć formuł lookup.
Załóżmy, że masz zbiór danych jak poniżej i chcesz zidentyfikować firmy, które są obecne w kolumnie A, ale nie w kolumnie B,
Aby to zrobić, mogę użyć następującej formuły VLOOKUP.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Ta formuła używa funkcji VLOOKUP do sprawdzenia, czy nazwa firmy w A jest obecna w kolumnie B, czy nie. Jeśli jest obecna, zwróci tę nazwę z kolumny B, w przeciwnym razie zwróci błąd #N/A.
Te nazwy, które zwracają błąd #N/A, są tymi, których brakuje w kolumnie B.
FunkcjaISERROR zwróciłaby TRUE, jeśli wynik VLOOKUP jest błędem i FALSE, jeśli nie jest błędem.
Jeśli chcesz uzyskać listę wszystkich nazwisk, w których nie ma dopasowania, możesz przefiltrować kolumnę wyników, aby uzyskać wszystkie komórki z TRUE.
Możesz również użyć funkcji MATCH, aby zrobić to samo;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Uwaga: Osobiście wolę używać funkcji Dopasuj (lub kombinacji INDEX/MATCH) zamiast VLOOKUP. Uważam, że jest ona bardziej elastyczna i potężniejsza. Możesz przeczytać różnicę między Vlookup i Index/Match tutaj.
Compare Two Columns and Pull the Matching Data
Jeśli masz dwa zbiory danych i chcesz porównać elementy z jednej listy do drugiej i pobrać pasujący punkt danych, musisz użyć formuł lookup.
Przykład: Pull the Matching Data (Exact)
Na przykład, na poniższej liście, chcę pobrać wartość wyceny rynkowej dla kolumny 2. Aby to zrobić, muszę sprawdzić tę wartość w kolumnie 1, a następnie pobrać odpowiadającą jej wartość wyceny rynkowej.
Poniżej znajduje się formuła, która to umożliwi:
=VLOOKUP(D2,$A:$B,2,0)
lub
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Przykład: Wyciągnij pasujące dane (częściowe)
W przypadku, gdy otrzymujesz zbiór danych, w którym istnieje niewielka różnica w nazwach w dwóch kolumnach, użycie wyżej pokazanych formuł wyszukujących nie zadziała.
Te formuły wyszukujące wymagają dokładnego dopasowania, aby dać właściwy wynik. W funkcji VLOOKUP lub MATCH istnieje opcja dopasowania przybliżonego, ale nie można jej tutaj użyć.
Załóżmy, że masz zestaw danych, jak pokazano poniżej. Zauważ, że w kolumnie 2 znajdują się nazwy, które nie są kompletne (takie jak JPMorgan zamiast JPMorgan Chase i Exxon zamiast ExxonMobil).
W takim przypadku można użyć częściowego wyszukiwania za pomocą znaków wieloznacznych.
Następujący wzór da w tym przypadku właściwy wynik:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
lub
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
W powyższym przykładzie gwiazdka (*) jest znakiem wieloznacznym, który może reprezentować dowolną liczbę znaków. Kiedy wartość wyszukiwania jest otoczona nim po obu stronach, każda wartość w kolumnie 1, która zawiera wartość wyszukiwania w kolumnie 2 będzie uważana za dopasowanie.
Na przykład, *Exxon* będzie dopasowaniem dla ExxonMobil (ponieważ * może reprezentować dowolną liczbę znaków).
You May Also Like the Following Excel Tips & Tutorials:
- How to Compare Two Excel Sheets (for differences)
- How to Highlight Blank Cells in Excel.
- Highlight EVERY Other ROW in Excel.
- Excel Advanced Filter: Kompletny przewodnik z przykładami.
- Wyświetlanie wierszy na podstawie wartości komórki w Excelu.