Obejrzyj wideo - porównaj dwie kolumny w programie Excel pod kątem dopasowań i różnic
Jedyne zapytanie, które często otrzymuję, to „jak porównać dwie kolumny w programie Excel?”.
Można to zrobić na wiele różnych sposobów, a sposób użycia będzie zależeć od struktury danych i tego, czego od nich oczekuje użytkownik.
Na przykład możesz chcieć porównać dwie kolumny i znaleźć lub podświetlić wszystkie pasujące punkty danych (znajdujące się w obu kolumnach) lub tylko różnice (gdzie punkt danych znajduje się w jednej kolumnie, a nie w drugiej) itd.
Ponieważ tak często mnie o to pytano, postanowiłem napisać ten obszerny samouczek z zamiarem omówienia większości (jeśli nie wszystkich) możliwych scenariuszy.
Jeśli uznasz to za przydatne, przekaż je innym użytkownikom programu Excel.
Zauważ, że techniki porównywania kolumn pokazane w tym samouczku nie są jedynymi.
W oparciu o Twój zbiór danych może być konieczna zmiana lub dostosowanie metody. Jednak podstawowe zasady pozostałyby takie same.
Jeśli uważasz, że jest coś, co można dodać do tego samouczka, daj mi znać w sekcji komentarzy
Porównaj dwie kolumny w celu dokładnego dopasowania wiersza
To najprostsza forma porównania. W takim przypadku musisz wykonać porównanie wiersz po wierszu i określić, które wiersze zawierają te same dane, a które nie.
Przykład: Porównaj komórki w tym samym wierszu
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śli jest dopasowanie, potrzebuję wyniku jako „PRAWDA”, a jeśli nie pasuje, potrzebuję wyniku jako „FAŁSZ”.
Poniższa formuła zrobiłaby to:
=A2=B2
Przykład: Porównaj komórki w tym samym wierszu (przy użyciu formuły JEŻELI)
Jeśli chcesz uzyskać bardziej opisowy wynik, możesz użyć prostej formuły JEŻELI, aby zwrócić „Dopasowanie”, gdy nazwy są takie same, i „Niezgodność”, gdy nazwy są różne.
=JEŻELI(A2=B2,"Dopasowanie";"Niezgodność")
Uwaga: Jeśli chcesz rozróżniać wielkość liter w porównaniu, użyj następującej formuły JEŻELI:
=JEŻELI(DOKŁADNIE(A2,B2);"Dopasowanie";"Niezgodność")
W powyższym wzorze „IBM” i „ibm” byłyby uważane za dwie różne nazwy, a powyższa formuła zwróciłaby „Niezgodność”.
Przykład: Wyróżnij wiersze pasującymi danymi
Jeśli chcesz wyróżnić wiersze, które mają pasujące dane (zamiast uzyskać wynik w osobnej kolumnie), możesz to zrobić za pomocą 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 rozwijanego menu kliknij „Nowa reguła”.
- W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
- 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 OK.
Spowoduje to podświetlenie wszystkich komórek, których nazwy są takie same w każdym wierszu.
Porównaj dwie kolumny i zaznacz dopasowania
Jeśli chcesz porównać dwie kolumny i wyróżnić pasujące dane, możesz użyć funkcji duplikowania w formatowaniu warunkowym.
Zauważ, że różni się to od tego, co widzieliśmy porównując każdy wiersz. W takim przypadku nie będziemy porównywać wiersz po wierszu.
Przykład: Porównaj dwie kolumny i zaznacz pasujące dane
Często otrzymujesz zestawy danych, w których występują dopasowania, ale mogą one nie znajdować się w tym samym wierszu.
Coś, jak pokazano poniżej:
Zauważ, że lista w kolumnie A jest większa niż ta w B. Również niektóre nazwiska znajdują się na obu listach, ale nie w tym samym wierszu (np. IBM, Adobe, Walmart).
Jeśli chcesz wyróżnić wszystkie pasujące nazwy firm, możesz to zrobić za pomocą formatowania warunkowego.
Oto kroki, aby to zrobić:
- Wybierz cały zestaw danych.
- Kliknij kartę Strona główna.
- W grupie Style kliknij opcję „Formatowanie warunkowe”.
- Najedź kursorem na opcję Wyróżnij reguły komórek.
- Kliknij Powiel wartości.
- W oknie dialogowym Zduplikowane wartości upewnij się, że wybrana jest opcja „Duplikuj”.
- Określ formatowanie.
- Kliknij OK.
Powyższe kroki dadzą wynik, jak pokazano poniżej.
Uwaga: zduplikowana reguła formatowania warunkowego nie uwzględnia wielkości liter. Tak więc „jabłko” i „jabłko” są uważane za to samo i będą wyróżnione jako duplikaty.
Przykład: Porównaj dwie kolumny i zaznacz niedopasowane dane
Jeśli chcesz podświetlić nazwy, które są obecne na jednej liście, a nie na drugiej, możesz również użyć do tego formatowania warunkowego.
- Wybierz cały zestaw danych.
- Kliknij kartę Strona główna.
- W grupie Style kliknij opcję „Formatowanie warunkowe”.
- Najedź kursorem na opcję Wyróżnij reguły komórek.
- Kliknij Powiel wartości.
- W oknie dialogowym Zduplikowane wartości upewnij się, że wybrana jest opcja „Unikalne”.
- Określ formatowanie.
- Kliknij OK.
To da wynik, jak pokazano poniżej. Podświetla wszystkie komórki, które mają nazwę, której nie ma na drugiej liście.
Porównaj dwie kolumny i znajdź brakujące punkty danych
Jeśli chcesz określić, czy punkt danych z jednej listy znajduje się na drugiej liście, musisz użyć formuł wyszukiwania.
Załóżmy, że masz zestaw danych, jak pokazano 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 WYSZUKAJ.PIONOWO.
=BŁĄD(WYSZUKAJ.PIONOWO(A2,$B$2:$B$10,1,0))
Ta formuła używa funkcji WYSZUKAJ.PIONOWO, aby sprawdzić, czy nazwa firmy w A jest obecna w kolumnie B, czy nie. Jeśli jest obecny, zwróci tę nazwę z kolumny B, w przeciwnym razie zwróci błąd #N/D.
Te nazwy, które zwracają błąd #N/D, to te, których brakuje w kolumnie B.
Funkcja ISERROR zwróci TRUE, jeśli wynik WYSZUKAJ.PIONOWO jest błędem, a FALSE, jeśli nie jest błędem.
Jeśli chcesz uzyskać listę wszystkich nazw, w których nie ma dopasowania, możesz przefiltrować kolumnę wyników, aby uzyskać wszystkie komórki z wartością TRUE.
Możesz również użyć funkcji DOPASUJ, aby zrobić to samo;
=NIE(CZY.LICZBA(DOPASOWANIE(A2,$B$2:$B$10,0)))
Uwaga: Osobiście wolę używać funkcji Dopasuj (lub kombinacji INDEX/MATCH) zamiast funkcji WYSZUKAJ.PIONOWO. Uważam, że jest bardziej elastyczny i mocniejszy. Możesz przeczytać różnicę między Vlookup a Index/Match tutaj.
Porównaj dwie kolumny i pobierz pasujące dane
Jeśli masz dwa zestawy danych i chcesz porównać elementy z jednej listy z drugą i pobrać pasujący punkt danych, musisz użyć formuł wyszukiwania.
Przykład: pobierz pasujące dane (dokładne)
Na przykład na poniższej liście chcę pobrać wartość wyceny rynkowej dla kolumny 2. Aby to zrobić, muszę wyszukać tę wartość w kolumnie 1, a następnie pobrać odpowiednią wartość wyceny rynkowej.
Poniżej znajduje się formuła, która to zrobi:
=WYSZUKAJ.PIONOWO(D2,$A$2:$B$14,2,0)
lub
=INDEKS($A$2:$B$14,DOPASOWANIE(D2,$A$2:$A$14,0),2)
Przykład: Pobierz pasujące dane (częściowo)
W przypadku otrzymania zestawu danych, w którym występuje niewielka różnica w nazwach w dwóch kolumnach, użycie przedstawionych powyżej formuł wyszukiwania nie zadziała.
Te formuły wyszukiwania wymagają dokładnego dopasowania, aby uzyskać właściwy wynik. Istnieje przybliżona opcja dopasowania w funkcji WYSZUKAJ.PIONOWO lub PODAJ.POZYCJĘ, ale nie można jej tutaj użyć.
Załóżmy, że masz zestaw danych, jak pokazano poniżej. Zauważ, że istnieją nazwy, które nie są kompletne w kolumnie 2 (takie jak JPMorgan zamiast JPMorgan Chase i Exxon zamiast ExxonMobil).
W takim przypadku możesz użyć częściowego wyszukiwania, używając znaków wieloznacznych.
Poniższa formuła da właściwy wynik w tym przypadku:
=WYSZUKAJ.PIONOWO("*"&D2&"*",$A$2:$B$14,2,0)
lub
=INDEKS($A$2:$B$14,DOPASUJ("*"&D2&"*",$A$2:$A$14,0),2)
W powyższym przykładzie gwiazdka (*) jest znakiem wieloznacznym, który może reprezentować dowolną liczbę znaków. Gdy wartość wyszukiwania jest otoczona z obu stron, każda wartość w kolumnie 1, która zawiera wartość wyszukiwania w kolumnie 2, zostanie uznana za dopasowanie.
Na przykład *Exxon* odpowiadałby ExxonMobil (ponieważ * może reprezentować dowolną liczbę znaków).
Możesz również polubić następujące porady i samouczki dotyczące programu Excel:
- Jak porównać dwa arkusze Excela (dla różnic)
- Jak wyróżnić puste komórki w programie Excel.
- Zaznacz KAŻDY INNY WIERSZ w programie Excel.
- Zaawansowany filtr programu Excel: kompletny przewodnik z przykładami.
- Wyróżnij wiersze na podstawie wartości komórki w programie Excel.
