Hay Paula, czy możesz porównać listy w Excelu?
lub
Jaki jest najlepszy sposób na porównanie dwóch zestawów danych w Excelu?
Bardzo często istnieje wymóg w Excelu, aby porównać dwie listy lub dwa zestawy danych, aby znaleźć brakujące lub pasujące elementy. Ponieważ jest to Excel, zawsze jest więcej niż jeden sposób, aby zrobić rzeczy, w tym porównywanie danych. Od formuł i formatowania warunkowego do Power Query. W tym artykule mamy zamiar spojrzeć na kilka sposobów, aby porównać dwie listy w programie Excel i będziemy również spojrzeć na porównywanie całych wierszy zestawu danych.
Może twój preferowany sposób nie jest zawarte poniżej. Jeśli nie, dlaczego nie upuść komentarz poniżej i podzielić się z nami, jak chcesz porównać dwie listy lub zestawy danych w programie Excel. Z niecierpliwością czekam na Twoje komentarze.
Chcemy porównać listę 1 z listą 2.
Treść
Szybkie formatowanie warunkowe w celu porównania dwóch kolumn danych
Usuwanie formatowania warunkowego
Porównanie danych w Excelu za pomocą funkcji MATCH
Porównanie 2 list w Excelu 365 za pomocą funkcji MATCH lub XMATCH jako Funkcja tablicy dynamicznej
MATCH i tablice dynamiczne do porównywania 2 list
XMATCH Excel 365 do porównywania dwóch list
Tabele – porównywanie list w Excelu, w których rozmiary zakresów mogą się zmieniać
Wyświetlanie różnic w listach przy użyciu funkcji Niestandardowe formatowanie warunkowe
Kopiowanie formuły do niestandardowego formatowania warunkowego
Inne formuły używane do porównywania dwóch list w Excelu
VLOOKUP do porównywania dwóch list w Excelu
XLOOKUP do porównywania dwóch list w Excelu
COUNTIF do porównywania dwóch list w Excelu
Jak porównać 2 zestawy danych w Excelu
Porównywanie list lub zestawów danych za pomocą Power Query
Szybkie formatowanie warunkowe do porównania dwóch kolumn danych
Formatowanie warunkowe pozwoli Ci wyróżnić komórki lub zakres na podstawie wcześniej zdefiniowanych kryteriów. Najszybszym i najprostszym sposobem na szybkie wizualne porównanie tych dwóch kolumn jest użycie predefiniowanej reguły podświetl duplikat wartości.
Zacznij od wybrania dwóch kolumn danych.
Na karcie Strona główna wybierz z listy rozwijanej Formatowanie warunkowe. Następnie wybierz opcję Reguły wyróżniania komórek. Następnie wybierz Duplikuj wartości.
Otworzy się okno ustawień Duplikuj wartości, w którym można zdefiniować formatowanie i wybrać pomiędzy Duplikuj lub Unikatowe wartości.
Wybierając Duplikuj, wszystkie powtarzające się wpisy zostaną ustawione na wybrane formatowanie. Teraz możesz szybko zobaczyć elementy z listy 1, które znajdują się na liście 2, ponieważ są to elementy sformatowane. Można również szybko zobaczyć pozycje na liście 2, które nie znajdują się na liście 1, ponieważ nie mają one zastosowanego formatowania.
Jednakże można również sformatować pozycje Unikatowe. Można to osiągnąć poprzez wybranie opcji Unikatowe z okna ustawień Duplikaty wartości.
W tym przypadku zastosowaliśmy dwa różne formaty warunkowe. Czerwony oznaczający duplikaty i zielony oznaczający unikalne elementy.
Zauważ, że nie wziąłem tylko komórek z danymi, złapałem wszystkie kolumny od A do C. Kolumna B nie zawiera danych, więc nie może mieć wpływu na wyniki. Jednak te komórki zawierają zastosowane formatowanie warunkowe, więc lepszą praktyką byłoby wybranie tylko tych komórek, których potrzebujesz.
Czyszczenie formatowania warunkowego
Aby wyczyścić całe formatowanie warunkowe, najpierw wybierz komórkę lub zakres. Następnie wybierz rozwijaną listę Formatowanie warunkowe na wstążce Strona główna. Następnie wybierz polecenie Wyczyść reguły. Na koniec wybierz polecenie Wyczyść reguły z wybranych komórek.
Jeśli masz więcej niż jedno formatowanie warunkowe zastosowane jednocześnie i chcesz usunąć tylko jedno z nich, wybierz polecenie Zarządzaj regułami z listy rozwijanej Formatowanie warunkowe. Zaznacz regułę, którą chcesz usunąć, a następnie wybierz Usuń regułę.
Naciskając OK, reguła zostanie usunięta z Menedżera reguł, a komórki nie będą już zawierać formatowania.
Więc jest to najbardziej podstawowy sposób, w jaki można porównać dwie listy w Excelu. Jest on szybki, prosty i skuteczny. Możesz również zastosować formatowanie warunkowe oparte na formułach, którym przyjrzymy się w dalszej części artykułu.
Dopasowywanie danych w Excelu za pomocą funkcji DOPASUJ
Istnieje wiele formuł wyszukujących, których możesz użyć do porównania dwóch zakresów lub list w Excelu. Pierwszą, której się przyjrzymy, jest funkcja MATCH.
Funkcja MATCH zwraca względną pozycję na liście. Liczba na podstawie jego pozycji, jeśli znaleziono, w tablicy lookup.
Składnia funkcji MATCH jest
=MATCH (wartość lookup, tablica Lookup, typ dopasowania)
Gdzie wartość lookup jest wartością, dla której chcesz znaleźć dopasowanie. Tablica Lookup jest listą, w której szukasz dopasowania. Typ dopasowania pozwala na wybór pomiędzy dopasowaniem dokładnym lub przybliżonym.
Chcemy napisać formułę dopasowującą, aby sprawdzić, czy elementy z listy 2 znajdują się na liście 1.
W komórce E3 możemy wpisać formułę
=MATCH(C2, $A$2:$A$21,0)
Wypełniając tę formułę, dla wartości, w których Excel znajdzie dopasowanie, zwrócona zostanie pozycja tego dopasowania. W przypadku braku dopasowania wartością zwracaną będzie #N/A.
Bardzo często pozycja względna lub #N/A nie mają dla nas żadnej wartości i musimy przekształcić te wartości na true lub false. Aby to zrobić możemy łatwo rozszerzyć naszą formułę Match używając funkcji logicznej. Ponieważ funkcja Dopasuj zwraca liczbę, możemy użyć funkcji ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Porównaj 2 listy w Excelu 365 za pomocą funkcji MATCH lub XMATCH jako tablicy dynamicznej
Jeśli używasz programu Excel 365, masz dalsze alternatywy podczas używania funkcji MATCH do porównywania list lub danych. Ponieważ Excel 365 myśli w tablicach, możemy teraz przekazać tablicę jako wartość wyszukiwania w funkcji MATCH, a nasze wyniki będą się rozsypywać za nas. To usuwa potrzebę kopiowania formuły w dół i tylko 1 formuła, arkusz będzie mniej podatne na błędy i bardziej kompaktowy.
MATCH i Dynamiczne tablice do porównania 2 listy
Jeśli nie jesteś jeszcze zaznajomiony z Dynamiczne tablice, proponuję przeczytać ten artykuł: Excel Dynamic Arrays – Nowy sposób na modelowanie arkuszy Excela: aby lepiej zrozumieć, jak działają i jak rozlewają zakresy.
Jedyną zmianą w formule dopasowania jest to, że zamiast wybrać komórkę C2 jako naszą wartość odnośnika, wybierzemy zakres C2:12
=ISNUMBER(MATCH(C2:C12,$A$2:$A$21,0))
Jak widać, jedna formuła rozlewa wyniki w dół kolumny E.
XMATCH Excel 365 do porównywania dwóch list
Excel 365 wprowadza również nową funkcję XMATCH. Podobnie jak funkcja MATCH funkcja XMATCH zwraca względną pozycję na liście. Teraz jesteś zaznajomiony z funkcją XLOOKUP, która zastępuje starą funkcję VLOOKUP, wiesz, że XLOOKUP ma dodatkową moc. To przychodzi w postaci nowych warunków w składni formuły, takich jak tryb wyszukiwania i typy dopasowania. Cóż, XMATCH również jako ta dodatkowa moc w stosunku do swojego poprzednika MATCH.
Składnia funkcji XMATCH jest następująca
XMATCH (Lookup Value, Lookup Array, ,)
Gdzie
Lookup Value jest wartością, którą chcesz znaleźć w pozycji względnej
Lookup Array jest wierszem lub kolumną, która zawiera Lookup Value
Tryb dopasowania jest opcjonalny. W przeciwieństwie do starej funkcji MATCH, domyślnie jest to dokładne dopasowanie. Można również wybrać pomiędzy
- Dokładne dopasowanie lub następne najmniejsze
- Dokładne dopasowanie lub następne największe
- Pasowanie wieloznaczne
Tryb wyszukiwania jest również opcjonalny. Domyślnie (i jedyna opcja w starej funkcji MATCH) jest szukanie od góry w dół. Możesz również wybrać wyszukiwanie od ostatniego do pierwszego oraz wyszukiwanie binarne. Jeśli pracujesz z wyszukiwaniem binarnym. Opcja dopasowania wieloznacznego nie działa.
Z XMATCH możemy użyć albo tablic dynamicznych albo odwołań do komórek, aby utworzyć formułę, tak jak patrzyliśmy na to z MATCH. W tym przykładzie użyjemy tablic dynamicznych. Formuła jest bardzo podobna do tej, której używaliśmy z MATCH; z wyjątkiem tego, że nie musimy wybierać 0 dla dokładnego dopasowania, ponieważ w XMATCH jest to ustawienie domyślne. Pomieszajmy trochę rzeczy tym razem i spójrzmy na znalezienie elementów na liście 2 nie na liście 1.
W tym przypadku możemy użyć formuły
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Gdzie not zamieni tru na false a false na tru.
Tabele – porównywanie list w Excelu, w których rozmiary zakresów mogą się zmieniać
W każdej z formuł, którym przyglądaliśmy się do tej pory, w naszych funkcjach Dopasuj wybieraliśmy zakres komórek, który nie jest dynamiczny. Oznacza to, że jeśli dodamy nowe dane do jednej z list, musimy ręcznie zaktualizować naszą formułę, aby uwzględnić nowe dane.
Aby przekonwertować listy na tabele, wybierz jedną z list i naciśnij klawisz CTRL. Jest to skrót klawiaturowy do konwersji na tabelę. Jeśli wybrałeś nagłówek w zakresie komórek, upewnij się, że zaznaczyłeś pole wyboru, aby potwierdzić, że Twoja tabela ma nagłówki.
Tabele ze swej natury wykorzystują nazewnictwo strukturalne. Dlatego, gdy piszesz formułę i wybierasz kolumnę z tabeli, nie będą wyświetlane odwołania do komórek, ale nazwa kolumny.
Patrząc na naszą poprzednią formułę używającą XMATCH do znalezienia elementów na liście 2 nie znajdujących się na liście 1, możemy teraz przepisać tę funkcję używając odwołań do naszej tabeli
=NOT (ISNUMBER(XMATCH(list2,list1)))
Teraz, ponieważ użyliśmy tabel, jeśli dodamy nowy wiersz do którejkolwiek z tabel, nasz zakres rozlania również się zwiększy, aby uwzględnić nowe dane.
Podkreślanie różnic w listach za pomocą niestandardowego formatowania warunkowego
Wcześniej w tym artykule przyjrzeliśmy się bardzo szybkiemu sposobowi porównywania tych dwóch list za pomocą predefiniowanej reguły dla duplikatów. Możemy jednak również użyć niestandardowego formatowania warunkowego. Jeśli nie jesteś zaznajomiony z niestandardowym formatowaniem warunkowym, sugeruję, abyś zapoznał się z tym artykułem: Excel Dynamiczne formatowanie warunkowe Sztuczki:
Musimy spojrzeć na dwa różne podejścia tutaj, aby podkreślić różnice. Jeśli nie używamy tabel i utworzyliśmy formułę prawda/fałsz, aby zidentyfikować różnice, możemy wykonać kopię formuły i dodać ją do naszego formatowania niestandardowego. Jednak w przypadku tabel musimy wymusić użycie odwołań do komórek.
Kopiuj formułę do Niestandardowego formatowania warunkowego
Zacznij od wykonania kopii formuły. Ponieważ przetestowaliśmy formułę w arkuszu kalkulacyjnym, możemy zobaczyć, że działa, zanim użyjemy jej w formatowaniu warunkowym. Jest to najlepsza praktyka, ponieważ bardzo często przy względnych i bezwzględnych odwołaniach do komórek może być trudno uzyskać poprawną formułę.
Zaznacz komórki, do których chcesz zastosować formatowanie niestandardowe. Następnie na wstążce Strona główna wybierz polecenie rozwijane Formatowanie warunkowe i wybierz opcję Nowa reguła
Otworzy się okno ustawień nowej reguły formatowania i wybierz opcję Użyj formuły, aby określić, które komórki mają być sformatowane. Następnie wklej formułę i ustaw typ formatowania
Spowoduje to, że wszystkie komórki znajdujące się na obu listach zostaną sformatowane do wybranego przez Ciebie formatu.
Pamiętaj, że wybraliśmy zakres komórek do zastosowania tego formatowania warunkowego i nie jest ono dynamiczne. Gdybyśmy użyli tabel, formatowanie to byłoby aktualizowane bez konieczności zmiany czegokolwiek!
Inne formuły używane do porównywania dwóch list w Excelu
Istnieje wiele formuł, których możesz użyć do porównania dwóch list w Excelu. Przyjrzeliśmy się już MATCH i XMATCH, ale teraz przyjrzymy się jeszcze kilku innym. Każda z funkcji lookup będzie naprawdę działać wraz z kilkoma innymi!
VLOOKUP do porównania dwóch list w Excelu
Jeśli nie jesteś zaznajomiony z VLOOKUP, możesz przeczytać o tym tutaj. Mówiąc najprościej VLOOKUP zwróci odpowiednią wartość z komórki, jeśli nie ma odpowiadającej wartości zostanie zwrócony błąd #N/A. W naszym przykładzie pracujemy z tekstem. Możemy więc wykonać VLOOKUP i sprawdzić, czy zwraca on tekst. Gdybyśmy używali liczb, moglibyśmy zamienić ISTEXT na ISNUMBER.
Moglibyśmy użyć funkcji =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Albo gdybyśmy używali tablic dynamicznych w Excelu 365, moglibyśmy użyć funkcji
=ISTEXT(VLOOKUP(C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP do porównywania dwóch list w Excelu
XLOOKUP został wprowadzony w Excelu 365 i możesz dowiedzieć się więcej na jego temat tutaj. Bardzo podobnie jak VLOOKUP, XLOOKUP zwróci odpowiednią wartość z komórki i możesz zdefiniować wynik, jeśli wartość nie zostanie znaleziona. Używając tablic dynamicznych, funkcja byłaby następująca
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF do porównania dwóch list w Excelu
Funkcja COUNTIF policzy, ile razy wartość lub tekst znajduje się w danym zakresie. Jeśli wartość nie zostanie znaleziona, zwracane jest 0. Możemy połączyć to z instrukcją JEŻELI, aby zwrócić wartości true i false.
=IF(COUNTIF (A2:A21,C2:C12)<>0, „True”, „False”)
Jak porównać 2 zestawy danych w Excelu
Porównanie dwóch list jest dość proste i przyjrzeliśmy się teraz kilku sposobom, aby to zrobić. Ale porównanie dwóch zestawów danych może być nieco trudniejsze.
Przyjrzyjrzyjmy się przykładowi. Mamy dwie tabele danych, z których każda zawiera te same nagłówki kolumn. Patrząc na obrazek, widzimy, że dopasowanie tych dwóch tabel wymagałoby spojrzenia na więcej niż jedną kolumnę.
Gdy trzeba spojrzeć na więcej niż jedną kolumnę, rozwiązaniem jest utworzenie kolumny złożonej, łączącej dane w jedną kolumnę. Stworzy to unikalną kolumnę dla każdego wiersza, którą możemy następnie użyć jako kolumnę dopasowującą
W tym przykładzie moglibyśmy połączyć Nazwę i DoB, aby nadać każdej tabeli unikalny identyfikator
Istnieje wiele sposobów łączenia zawartości komórki, w tym przypadku wykonamy proste konkatenowanie. Ponieważ korzystamy z tabel, formuła będzie wykorzystywać format nazewnictwa tabel.
= &”-„&
Powtórz kroki dla drugiej tabeli.
Teraz możemy użyć dowolnego z powyższych przykładów, aby dopasować te dwie nowe kolumny danych. Tam, gdzie pasują, wiemy, że pasują do całego wiersza.
Porównywanie list lub zbiorów danych za pomocą Power Query
Można również porównywać listy i zbiory danych za pomocą Excela Power Query. Łącząc się z tabelami, a następnie łącząc tabele, używając różnych typów złączeń możemy porównać obie listy.
W tym filmie dowiesz się jak porównać lub uzgodnić dwa różne zestawy danych używając Excels Power Query
Do filmu dołączony jest zestaw danych, który możesz pobrać z tego artykułu.