Jak porównać dwie listy w Excelu?

Excel to wydajny program do przetwarzania danych. A jedną z metod analizy informacji jest porównanie dwóch list. Jeśli poprawnie porównasz dwie listy w Excelu, zorganizowanie tego procesu będzie bardzo łatwe. Wystarczy tylko prześledzić kilka punktów, które będą dziś omawiane. Praktyczne wdrożenie tej metody zależy całkowicie od potrzeb osoby lub organizacji w danym momencie. Dlatego należy rozważyć kilka możliwych przypadków.

Porównanie dwóch list w Excelu

Oczywiście możesz ręcznie porównać dwie listy. Ale zajmie to dużo czasu. Excel posiada własny inteligentny zestaw narzędzi, który pozwoli nie tylko na szybkie porównywanie danych, ale także na uzyskanie informacji, które nie są tak łatwe do zdobycia gołym okiem. Załóżmy, że mamy dwie kolumny ze współrzędnymi A i B. Niektóre wartości się w nich powtarzają.

Sformułowanie problemu

Więc musimy porównać te kolumny. Procedura porównywania dwóch dokumentów wygląda następująco:

1. Jeśli unikalne komórki każdej z tych list są takie same, a całkowita liczba unikalnych komórek jest taka sama, a komórki są takie same, wówczas te listy można uznać za takie same. Kolejność, w jakiej ułożone są wartości na tej liście, nie ma tak dużego znaczenia.
2. O częściowej koincydencji list możemy mówić, jeśli same unikalne wartości są takie same, ale liczba powtórzeń jest inna. Dlatego takie listy mogą zawierać różną liczbę elementów.
3. O tym, że te dwie listy nie pasują, świadczy inny zestaw unikalnych wartości.

Wszystkie te trzy warunki jednocześnie są warunkami naszego problemu.

Rozwiązanie problemu

Wygenerujmy dwa zakresy dynamiczne, aby ułatwić porównywanie list. Każdy z nich będzie odpowiadał każdej z list.

Aby porównać dwie listy, wykonaj następujące czynności:

1. W osobnej kolumnie tworzymy listę unikalnych wartości, które są specyficzne dla obu list. Do tego używamy formuły: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Sama formuła musi być zapisana jako formuła tablicowa.
2. Określmy, ile razy każda unikalna wartość występuje w tablicy danych. Oto wzory na to: =LICZ.JEŻELI(Lista1,D5) i =LICZ.JEŻELI(Lista2,D5).
3. Jeśli zarówno liczba powtórzeń, jak i liczba unikalnych wartości są takie same na wszystkich listach zawartych w tych zakresach, funkcja zwraca wartość 0. Oznacza to, że dopasowanie to XNUMX%. W takim przypadku nagłówki tych list uzyskają zielone tło.
4. Jeśli wszystkie unikalne treści znajdują się na obu listach, to zwracane przez formuły =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) wartość będzie wynosić zero. Jeżeli E1 nie zawiera zera, ale taka wartość jest zawarta w komórkach E2 i F2, to w takim przypadku zakresy zostaną rozpoznane jako pasujące, ale tylko częściowo. W takim przypadku nagłówki odpowiednich list zmienią kolor na pomarańczowy.
5. A jeśli jedna z opisanych powyżej formuł zwróci wartość niezerową, listy będą całkowicie niezgodne.

To jest odpowiedź na pytanie, jak analizować kolumny pod kątem dopasowań za pomocą formuł. Jak widać, za pomocą funkcji można zrealizować niemal każde zadanie, które na pierwszy rzut oka nie jest związane z matematyką.

Przykładowe testy

W naszej wersji tabeli istnieją trzy rodzaje list każdego typu opisanego powyżej. Ma częściowe i całkowite dopasowanie, a także niedopasowanie.

Do porównania danych używamy zakresu A5:B19, w którym naprzemiennie wstawiamy te pary list. O tym, jaki będzie wynik porównania, zrozumiemy kolor oryginalnych list. Jeśli są zupełnie inne, będzie to czerwone tło. Jeśli część danych jest taka sama, to kolor żółty. W przypadku pełnej tożsamości odpowiednie nagłówki będą zielone. Jak zrobić kolor w zależności od efektu? Wymaga to formatowania warunkowego.

Znajdowanie różnic w dwóch listach na dwa sposoby

Opiszmy jeszcze dwie metody znajdowania różnic, w zależności od tego, czy listy są synchroniczne, czy nie.

Opcja 1. Listy synchroniczne

To łatwa opcja. Załóżmy, że mamy takie listy.

Aby określić, ile razy wartości nie były zbieżne, możesz użyć wzoru: =SUMA ILOCZYNÓW(—(A2:A20<>B2:B20)). Jeśli w rezultacie otrzymaliśmy 0, oznacza to, że obie listy są takie same.

Opcja 2: Potasowane listy

Jeśli listy nie są identyczne w kolejności zawartych w nich obiektów, należy zastosować funkcję, taką jak formatowanie warunkowe i pokolorować zduplikowane wartości. Lub użyj funkcji COUNTIF, za pomocą którego określamy ile razy element z jednej listy występuje w drugiej.

Jak porównać 2 kolumny wiersz po wierszu

Kiedy porównujemy dwie kolumny, często musimy porównać informacje znajdujące się w różnych wierszach. W tym celu pomoże nam operator JEŚLI. Przyjrzyjmy się, jak to działa w praktyce. W tym celu przedstawiamy kilka przykładowych sytuacji.

Przykład. Jak porównać 2 kolumny pod kątem dopasowań i różnic w jednym wierszu

Aby przeanalizować, czy wartości, które są w tym samym wierszu, ale w różnych kolumnach są takie same, piszemy funkcję IF. Formuła wstawiana jest do każdego wiersza umieszczonego w kolumnie pomocniczej, w której będą wyświetlane wyniki przetwarzania danych. Ale wcale nie jest konieczne przepisywanie go w każdym wierszu, po prostu skopiuj go do pozostałych komórek tej kolumny lub użyj znacznika autouzupełniania.

Powinniśmy zapisać taką formułę, aby zrozumieć, czy wartości w obu kolumnach są takie same, czy nie: =JEŻELI(A2=B2, „Dopasuj”, „”). Logika tej funkcji jest bardzo prosta: porównuje wartości w komórkach A2 i B2, a jeśli są takie same, wyświetla wartość „Zbieg”. Jeśli dane są różne, nie zwraca żadnej wartości. Możesz także sprawdzić komórki, aby sprawdzić, czy jest między nimi dopasowanie. W tym przypadku użyta formuła to: =JEŻELI(A2<>B2; „Nie pasuje”, „”). Zasada jest taka sama, najpierw przeprowadzana jest kontrola. Jeśli okaże się, że komórki spełniają kryterium, wyświetlana jest wartość „Nie pasuje”.

Możliwe jest również użycie następującej formuły w polu formuły, aby wyświetlić zarówno „Dopasuj”, jeśli wartości są takie same, jak i „Nie pasuj”, jeśli są różne: =JEŻELI(A2=B2; „Dopasuj”, „Nie dopasowuj”). Możesz również użyć operatora nierówności zamiast operatora równości. Jedynie kolejność wyświetlanych wartości będzie w tym przypadku nieco inna: =JEŻELI(A2<>B2; „Nie pasują”, „Pokrywają się”). Po użyciu pierwszej wersji formuły wynik będzie następujący.

Ta odmiana formuły nie uwzględnia wielkości liter. Dlatego jeśli wartości w jednej kolumnie różnią się od innych tylko tym, że są pisane wielkimi literami, program nie zauważy tej różnicy. Aby w porównaniu rozróżniać wielkość liter, musisz użyć funkcji w kryteriach DOKŁADNY. Pozostałe argumenty pozostają bez zmian: =JEŻELI(DOKŁADNIE(A2,B2);"Dopasuj";"Unikatowe").

Jak porównać wiele kolumn pod kątem dopasowań w jednym wierszu

Możliwe jest analizowanie wartości na listach według całego zestawu kryteriów:

1. Znajdź wszędzie te wiersze, które mają te same wartości.
2. Znajdź te wiersze, w których są dopasowania, na zaledwie dwóch listach.

Przyjrzyjmy się kilku przykładom postępowania w każdym z tych przypadków.

Przykład. Jak znaleźć dopasowania w jednym rzędzie w wielu kolumnach tabeli

Załóżmy, że mamy serię kolumn zawierających potrzebne nam informacje. Stoimy przed zadaniem określenia tych wierszy, w których wartości są takie same. Aby to zrobić, musisz użyć następującej formuły: =JEŻELI(ORAZ(A2=B2,A2=C2); "dopasuj", "").

Jeśli w tabeli jest za dużo kolumn, wystarczy użyć ich razem z funkcją IF operator COUNTIF: =JEŻELI(LICZ.JEŻELI($A2:$C2,$A2)=3;”dopasuj”;””). Liczba użyta w tej formule wskazuje liczbę kolumn do sprawdzenia. Jeśli jest inny, musisz napisać tyle, ile jest prawdziwe w Twojej sytuacji.

Przykład. Jak znaleźć dopasowania w jednym wierszu w dowolnych 2 kolumnach tabeli?

Powiedzmy, że musimy sprawdzić, czy wartości w jednym wierszu pasują do dwóch kolumn z tych w tabeli. Aby to zrobić, musisz użyć funkcji jako warunku OR, gdzie naprzemiennie zapisujemy równość każdej z kolumn do drugiej. Oto przykład.

Używamy tej formuły: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Może zaistnieć sytuacja, gdy w tabeli jest dużo kolumn. W takim przypadku formuła będzie ogromna i wybranie wszystkich niezbędnych kombinacji może zająć dużo czasu. Aby rozwiązać ten problem, musisz użyć funkcji COUNTIF: =JEŻELI(LICZ.JEŻELI(B2:D2,A2)+LICZ.JEŻELI(C2:D2,B2)+(C2=D2)=0;"Unikalny ciąg";"Nieunikalny ciąg")

Widzimy, że w sumie mamy dwie funkcje COUNTIF. Pierwszym naprzemiennie określamy, ile kolumn ma podobieństwo do A2, a drugim sprawdzamy liczbę podobieństw o ​​wartości B2. Jeżeli w wyniku obliczenia według tej formuły otrzymamy wartość zero, oznacza to, że wszystkie wiersze w tej kolumnie są unikatowe, jeśli więcej, to są podobieństwa. Dlatego jeśli w wyniku przeliczenia przez dwie formuły i dodania wyników końcowych otrzymamy wartość zero, to zwracana jest wartość tekstowa „Unikalny ciąg”, jeśli ta liczba jest większa, jest napisane, że ten ciąg nie jest unikalny.

Jak porównać 2 kolumny w programie Excel pod kątem dopasowań?

Weźmy teraz przykład. Załóżmy, że mamy tabelę z dwiema kolumnami. Musisz sprawdzić, czy pasują. Aby to zrobić, musisz zastosować formułę, w której funkcja będzie również używana IF, a operator COUNTIF: =JEŻELI(LICZ.JEŻELI($B:$B,$A5)=0,"Brak dopasowań w kolumnie B","Istnieją dopasowania w kolumnie B")

Nie są wymagane żadne dalsze działania. Po obliczeniu wyniku tą formułą otrzymujemy, jeśli wartość trzeciego argumentu funkcji IF mecze. Jeśli nie ma, to zawartość drugiego argumentu.

Jak porównać kolumny 2 w programie Excel pod kątem dopasowań i wyróżnić kolorem?

Aby ułatwić wizualną identyfikację pasujących kolumn, możesz wyróżnić je kolorem. Aby to zrobić, musisz użyć funkcji „Formatowanie warunkowe”. Zobaczmy w praktyce.

Znajdowanie i wyróżnianie dopasowań według koloru w wielu kolumnach

Aby określić dopasowania i je podświetlić, musisz najpierw wybrać zakres danych, w którym zostanie przeprowadzona kontrola, a następnie otworzyć element „Formatowanie warunkowe” w zakładce „Strona główna”. Tam wybierz "Zduplikowane wartości" jako regułę wyboru komórek.

Następnie pojawi się nowe okno dialogowe, w którym na lewej wyskakującej liście znajduje się opcja „Powtarzanie”, a na prawej liście wybieramy kolor, który zostanie użyty do wyboru. Po kliknięciu przycisku „OK” zostanie wybrane tło wszystkich komórek z podobieństwami. Następnie po prostu porównaj kolumny na oko.

Znajdowanie i wyróżnianie pasujących linii

Technika sprawdzania zgodności ciągów jest nieco inna. Najpierw musimy stworzyć dodatkową kolumnę, a tam użyjemy połączonych wartości za pomocą operatora &. Aby to zrobić, musisz napisać formułę formularza: =A2&B2&C2&D2.

Wybieramy kolumnę, która została utworzona i zawiera połączone wartości. Następnie wykonujemy tę samą sekwencję czynności, która została opisana powyżej dla kolumn. Zduplikowane linie zostaną wyróżnione wybranym kolorem.

Widzimy, że nie ma nic trudnego w szukaniu powtórzeń. Excel zawiera wszystkie niezbędne do tego narzędzia. Ważne jest, aby po prostu ćwiczyć przed zastosowaniem całej tej wiedzy w praktyce.