Jak porównać dwie kolumny w Excelu?

Na pewno choć raz zdarzyła Ci się sytuacja, gdy musisz porównać zawartość dwóch lub więcej kolumn w Excelu. Duża część osób porównuje zawartość kolumn wzrokowo. Czy jest to najlepsza metoda? Niestety można stwierdzić, że jest to wręcz metoda najgorsza z możliwych. O ile do porównania jest dosłownie kilka rekordów, jest niewielkie ryzyko popełnienia błędu. Ale co w sytuacji kiedy kolumny do porównania zawierają kilka tysięcy rekordów? Wzrok nie ogranie takiej ilości informacji, dlatego należy użyć funkcjonalności Excela. Istnieją różne metody porównywania zawartości kolumn. Jedne z nich wykorzystują funkcje i formuły, drugie z kolei formatowanie warunkowe. Na pewno pozwalają one zaoszczędzić czas, uniknąć błędów i z pewnością są o wiele bardziej dokładne niż ludzkie oko.



Porównanie kolumn za pomocą wartości logicznych

Otrzymujesz listę imion i nazwisk wszystkich pracowników firmy. Ty jednak już posiadasz taką listę, ale chcesz sprawdzić czy jest ona aktualna. W tym celu należy porównać zawartość kolumn zawierających dane pracowników z dwóch plików. Metoda ta może być wykorzystana tylko wtedy kiedy wiemy, że układ i kolejność danych sprawdzanych list jest podobna i ewentualnie są niewielkie różnice. Raczej jest ona używana w arkuszach z niezbyt dużą ilością danych.

Lista pracowników składa się z kolumny zawierającej imiona i nazwiska pracowników. Użyjemy tu bardzo prostej formuły do porównania zawartości kolumn (rys. 1). W wybranej komórce sprawdzimy czy dana komórka z pierwszej listy jest równa odpowiadającej jej komórce z drugiej listy.

  • Zaznacz wybraną komórkę i wpisz do niej formułę (1):

=A2=D2

  • W formule stosujemy odwołania względne (czyli nie wstawiamy znaków dolara), ponieważ trzeba ją przeciągnąć do całej kolumny, aby sprawdziła całą listę.
  • Formułę zatwierdzamy klikając ENTER.
  • W wyniku formuły otrzymujemy wynik PRAWDA lub FAŁSZ (2). Jak łatwo się domyślić, PRAWDA oznacza, że zawartość komórek z formuły jest taka sama, natomiast FAŁSZ oznacza różnicę w komórkach.

Rys. 1. LISTA PRACOWNIKÓW. Zwykłe porównanie komórek.

Pamiętaj, że jeśli kolejność osób na liście jest na pewno inna, to ta metoda nie jest skuteczna. Jeśli np. jeden pracownik zostanie pominięty na jednej z list, to prawdopodobnie w kolejnych wierszach również otrzymasz FAŁSZ.
 

Zbędne spacje

Należy tu uważać, ponieważ komórki czasami zawierają zbędne spacje na początku lub końcu tekstu w komórce. W tym przypadku (rys. 2-2), mimo że dla nas jest to niewidoczne, Excel uzna, że komórki są różne. Aby uniknąć takiej sytuacji można zastosować funkcję USUŃ.ZBĘDNE.ODSTĘPY. Wystarczy adresu komórki użyć jako argumentu tej funkcji, a ta usunie początkowe i końcowe spacje z tekstu w formule (rys. 3-4). Można więc składnię formuły wpisać do komórki jako (rys. 3-3):

=USUŃ.ZBĘDNE.ODSTĘPY(A2)=USUŃ.ZBĘDNE.ODSTĘPY(D2)

Rysunek 2. Lista pracowników z dopisanymi na końcu spacjami.

Rysunek 3. Lista pracowników. Użycie funkcj USUŃ.ZBĘDNE.ODSTĘPY.


Porównanie kolumn za pomocą PORÓWNAJ

Dwie osoby w pewnym dziale mają za zadanie prowadzić listę pracowników według daty zatrudnienia. Teoretycznie listy te powinny być identyczne, ale oczywiście należy to sprawdzić. W tym przypadku użyjemy funkcji PORÓWNAJ. Tak jak i w poprzednim przypadku metoda ta może być wykorzystana, jeśli układ i kolejność danych sprawdzanych list jest podobna i ewentualnie są niewielkie różnice.

Rysunek 4. Lista pracowników. Użycie funkcji PORÓWNAJ, która zwraca uwagę na wielkość znaków.

PORÓWNAJ porównuje dwa teksty, liczby, znaki lub odwołania do komórek. Posiada dwa argumenty, którymi są porównywane teksty. Jeśli pierwszy argument jest identyczny jak drugi, funkcja zwróci wartość PRAWDA, a jeśli nie to wynikiem będzie FAŁSZ. Funkcja ta różni się tym od poprzedniego rozwiązania, że rozróżnia wielkie i małe litery. Zobacz w powyższym przykładzie (rys. 4), że w komórce D5 imię jest zapisane z małej litery i Excel stwierdza przez to, że te dwie wartości (w A5 i w D5) są od siebie różne). Rozróżnia również wstawione odstępy, ale ignoruje różnice w formatowaniu.

Mając dwie listy pracowników, w wybranej komórce wprowadź funkcję PORÓWNAJ, podając jako jej argumenty adresy porównywanych komórek:

=PORÓWNAJ(A2;D2)

Zatwierdź funkcję klikając ENTER i przeciągnij ją do dołu.


Porównanie kolumn za pomocą ZNAJDŹ

Kolejna metoda porównania kolumn w Excelu polega na użyciu funkcji ZNAJDŹ. Może ona być używana do porównania całych treści zawartych w kolumnach lub jej fragmentów. Tak jak i poprzednio, metoda może być wykorzystana jeśli układ i kolejność danych sprawdzanych list jest podobna. My zajmiemy się porównaniem fragmentu jednej kolumny do drugiej kolumny. W przykładzie nazwa producenta jest poprzedzona innym słowem na liście 1, natomiast na drugiej jest tylko nazwa. Musimy określić czy nazwy z listy 2 stanowią elementy odpowiadających komórek listy 1.

Rysunek 5. Lista firm z dopiskiem i bez dopisku.

Zapisujemy pierwszy fragment formuły, czyli funkcję ZNAJDŹ, która odszukuje tekst wewnątrz innego tekstu i oddaje pozycję początkową pierwszego znaku odszukanego tekstu. Jeżeli nie znajdzie takiego tekstu, to pokazuje błąd, co widać na rysunku 6 (komórka D6).

=ZNAJDŹ(D2;A2)

Rysunek 6. Lista firm. Szukanie „czystej” nazwy firmy za pomocą ZNAJDŹ.

Następnie trzeba rozpoznać, czy w komorce jest wspomniany numer, czy błąd. Użyjemy do tego funkcji CZY.LICZBA – posiada jeden argument, którym jest wynik naszej funkcji ZNAJDŹ. Jeśli w komórce jest liczba, to otrzymamy PRAWDA (czyli nazwa firmy się zgadza), a jeśli błąd to FAŁSZ (nazwa firmy nie została odnaleziona w odpowiadającej komórce).

=CZY.LICZBA(ZNAJDŹ(D2;A2))

Rysunek 7. Dodanie funkcji CZY.LICZBA, aby otrzymać informację PRAWDA lub FAŁSZ.


Porównanie kolumn za pomocą WYSZUKAJ.PIONOWO

Chcąc porównać dwie kolumny można także użyć WYSZUKAJ.PIONOWO. Użycie tej funkcji da dokładniejsze rezultaty i pozwoli wyszukać elementy nawet gdy dwie listy nie są tak samo posortowane. Funkcję tą można stosować na zarówno małych i jak i dużych zakresach danych.

Posłużmy się przykładem, w którym mamy dwie listy z produktami, które są dostępne w danym sklepie. Należy je porównać, znaleźć odpowiedniki z pierwszej listy i oznaczyć produkty, których nie ma na liście pierwszej, ale są na liście drugiej. Zwróć uwagę na charakterystyczne cechy tego przykładu:

  • Listy są różnych długości – nie będzie to dla nas problemem.
  • Listy są inaczej posortowane – nie będzie to dla nas problemem.
  • Niektóre z elementów pojawiają się na obu listach, a niektóre tylko na jednej – chcemy je właśnie rozpoznać.

Rysunek 8. Dwie listy produktów, które są tak samo posortowane.

Funkcja WYSZUKAJ.PIONOWO jest jedną z popularniejszych w Excelu. Posiada ona cztery argumenty, które opisane są poniżej na podstawie pierwszego produktu z listy 2 (komórka D2):

  1. Pierwszy to szukana wartość, czyli adres komórki, której zawartość chcemy odnaleźć w danym zakresie. W naszym przykładzie to komórka D2.

Rysunek 9. Pierwszy argument funkcji WYSZUKAJ.PIONOWO.

2. Drugi to zakres, który będzie przeszukiwany. Będziemy szukać tego produktu na liście nr 1, czyli zaznaczamy kolumnę A. Blokujemy ją od razu znakami „$”.

Rysunek 10. Drugi argument funkcji WYSZUKAJ.PIONOWO.

3. Trzecim argumentem jest numer kolumny, która zawiera wynik wyszukiwania. W naszym przykładzie zaznaczyliśmy tylko jedną kolumnę, więc tutaj będzie po prostu liczba 1.

Rysunek 11. Trzeci argument funkcji WYSZUKAJ.PIONOWO.

4. Czwarty argument jest opcjonalny i tu określa się, czy dopasowanie ma być dokładne (FAŁSZ lub 0) czy przybliżone (PRAWDA lub 1). My chcemy szukać dokładnie, więc wstawiamy 0.

Rysunek 12. Czwarty argument funkcji WYSZUKAJ.PIONOWO.

Cała formuła ma następującą postać:

=WYSZUKAJ.PIONOWO(D2;$A:$A;1;0)

Efekt formuły jest taki, jak na poniższym rysunku – jeśli funkcja znalazła wartość z listy nr 2 również na liście pierwszej, to wtedy ta wartość zostaje powtórzona. Jeśli nie znalazła, to wyświetlany jest błąd. Jest to dla nas już jasny, jednoznaczny komunikat. Jeśli jednak chcesz wyświetlić to w bardziej estetycznej formie, możesz kontynuować czytanie kolejnego akapitu.

Rysunek 13. Wynik funkcji WYSZUKAJ.PIONOWO.

Wyświetlone błędy nie prezentują się zbyt estetycznie, dlatego najlepiej będzie zastąpić je jakimś słowem. W tym celu wpisaną funkcję wbudujmy w kolejną funkcję – JEŻELI.BŁĄD. Funkcja ta będzie zwracać wynik działania, jeśli będzie on poprawny (nie będzie błędem), a jeżeli nie, zwróci wskazaną przez nas wartość. Pierwszym argumentem tej funkcji będzie zbudowana wcześniej funkcja WYSZUKAJ.PIONOWO, drugim będzie wartość, która ma pojawić się w komórce zamiast błędu

Rysunek 14. Użycie JEŻELI.BŁĄD, aby „schować” wartości błędu.Teraz formuła wygląda tak:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2;$A:$A;1;0);"nie występuje")

Analogicznie formułę można zastosować dla obydwu list.


Porównanie kolumn za pomocą LICZ.JEŻELI

Kolejną funkcją, którą można użyć do porównania kolumn jest LICZ.JEŻELI. Tak jak WYSZUKAJ.PIONOWO, może ona być stosowana dla większych zakresów danych. Czym się różni jej zastosowanie do porównania kolumn od WYSZUKAJ.PIONOWO? Wynik LICZ.JEŻELI zwróci nam ilość powtórzeń w porównywanej kolumnie, a WYSZUKAJ.PIONOWO powie tylko czy element został odnaleziony czy nie.

Funkcja LICZ.JEŻELI posiada dwa argumenty. Pierwszy z nich to zakres komórek, który ma być przeszukiwany, natomiast drugi to wartość, której ilość wystąpień ma być zliczona w tym zakresie.

Posłużmy się podobnym przykładem, jak dla WYSZUKAJ.PIONOWO, żeby zobaczyć różnicę.Kolejny raz będziemy porównywać listy z nazwami produktów. Przygotujmy też od razu kolumnę, która będzie pokazywała wynik porównania kolumn, nazwaną u nas „ile razy na liście 1”.

Rysunek 15. Listy produktów, różnie posortowane.

 Do pierwszej komórki dodatkowej kolumny wprowadźmy funkcję, zaczynając od znaku „=”. Zakresem przeszukiwanym są komórki z listy 1, zawierające nazwy produktów. Natomiast elementem, którego ilość wystąpień na liście 1 ma być zliczana jest pojedyncza komórka z listy 2, zawierająca produkt. Zakres listy 1 powinien być oczywiście zablokowany. Poprawna funkcja powinna mieć postać:

=LICZ.JEŻELI($A$2:$A$11;D2)

Rysunek 16. Użycie LICZ.JEŻELI.

Po zatwierdzeniu funkcji przeciągnij ją w dół. Jak widać na zdjęciu, ilość powtórzeń każdego produktu z listy 2 na liście 1 została zliczona.

Ten sposób porównania kolumn w Excelu można oczywiście dopracować i udoskonalić, w zależności od naszych potrzeb i intencji. Kiedy zależy nam tylko na pokazaniu czy firma wystąpiła na liście 2, można LICZ.JEŻELI zagnieździć w JEŻELI. Prawidłowo skonstruowany test logiczny powinien brzmieć: jeśli ilość wystąpień danego produktu na liście 1 jest większa od 0, wtedy produkt jest obecny na tej liście, w przeciwnym razie nie jest. Tak więc, pierwszym argumentem JEŻELI jest sformułowane wcześniej LICZ.JEŻELI większe od 0. Kolejnym jest wyrażenie, które ma się pojawić, kiedy warunek jest spełniony (u nas słowo „jest”) i następnie wyrażenie pojawiające się w razie wyniku FAŁSZ (u nas słowo „nie ma”). Cała formuła powinna być wyglądać jak poniżej:

=JEŻELI(LICZ.JEŻELI($A$2:$A$11;D2)>0;”jest”;”nie ma”)

Oczywiście wyrażenia, które będą się pojawiały w wyniku można dowolnie zmieniać. Po zatwierdzeniu formuły przeciągnij ją w dół.


Porównanie kolumn za pomocą formatowania warunkowego

Formatowanie warunkowe może świetnie sprawdzić się w porównywaniu kolumn Excela. Dzięki niemu można oznaczyć elementy unikatowe, różne na listach oraz te powtarzające się. Wcześniej omówionych zostało kilka formuł porównujących kolumny, wśród których mamy wybór, co do zastosowania. Tak samo, jest kilka możliwości użycia formatowania warunkowego do porównania kolumn w Excelu. Zajmijmy się tymi przypadkami bazując dalej na liście z nazwami produktów.

Oznaczenie elementów unikatowych w kolumnach

Załóżmy, że istnieje lista produktów dostępnych w sprzedaży oraz lista wzorcowa. Należy je porównać i oznaczyć elementy, które się nie powtarzają (występują tylko na jednej liście).

Zaznaczmy obydwa obszary zawierające nazwy marek z dwóch kolumn (rys. 17). Następnie na karcie Narzędzia główne wybieramy Formatowanie warunkowe, a potem Duplikujące się wartości (rys. 18).

Rysunek 17. Zaznaczenie obu kolumn (przytrzymując klawisz CTRL).

Rysunek 18. Wybranie opcji Duplikujące się wartości.W wyświetlonym oknie wybierz z listy Unikatowe i wybierz jak te komórki mają być formatowane.

Rysunek 19. Wybranie opcji Unikatowe.

Po kliknięciu OK wartości, które występują tylko raz (albo na jednej liście, albo na drugiej) zostaną sformatowane wybranym kolorem.

Rysunek 20. Efekt formatowania unikatowych wartości na obu listach.

Oznaczenie w kolumnie elementów różnych

W poprzednim przykładzie formatowanie warunkowe zaznaczało unikaty w obu kolumnach. Teraz zajmijmy się przypadkiem, kiedy zaznaczone mają być elementy tylko jednej kolumny i porównywane będą komórki w korespondujących wierszach na drugiej liście.

Zaznacz wszystkie komórki listy 1, które mają być objęte formatowaniem. Będziemy je porównywać do listy drugiej.

Rysunek 21. Dwie listy produktów, które powinny być posortowane tak samo.

Teraz wybierz Formatowanie warunkowe i Nowa reguła i określ jej typ jako Użyj formuły do określenia komórek, które należy sformatować.

Rysunek 22. Przejście do opcji umożliwiającej użycie formuły.

W oknie Nowa reguła formatowania w obszarze Edytuj opis reguły trzeba wpisać formułę, która wybierze komórki do sformatowania. W przypadku stosowania formuł w formatowaniu warunkowym trzeba pamiętać, że powinny one dawać końcowy wynik PRAWDA lub FAŁSZ. Niestety podczas edycji reguł formatowania nie wyświetlają się podpowiedzi dotyczące formuł. Jeśli sprawia nam trudność pisanie formuły w taki sposób, zawsze formułę można wpisać w dowolną komórkę arkusza i przekopiować ją do docelowego miejsca. My szukamy komórek różnych, a więc należy wpisać:

=A2<>D2

Trzeba pamiętać, aby nie blokować tu adresów komórek, gdyż formatowanie warunkowe musi w sposób płynny przeszukać wszystkie komórki listy nr 1 i listy nr 2. W tym miejscu wybierz także w jaki sposób mają być formatowane różne komórki, klikając przycisk Formatuj.

W wyniku otrzymujemy formatowanie komórek z listy nr 1, które nie są identyczne z odpowiadającą komórką listy nr 2.

Rysunek 23. Efekt zastosowania formuły w formatowaniu warunkowym, szukając elementów różniących obie listy.

Oznaczenie braków w kolumnie

Oznaczmy teraz produkty z listy 1, które w ogóle nie znajdują się na liście 2.

Rysunek 24. Dwie listy różnie uporządkowanych produktów.

Zaznacz obszar listy 1 (on zostanie objęty formatowaniem). Następnie wybierz Formatowanie warunkowe i Nowa reguła i dalej Użyj formuły do określenia komórek, które należy sformatować. W oknie Nowa reguła formatowania w obszarze Edytuj opis reguły wpiszemy formułę, która wyselekcjonuje komórki do sformatowania. Posłużymy się tutaj funkcją CZY.BŁĄD i WYSZUKAJ.PIONOWO. Będziemy wyszukiwać wartości z listy 1 na liście 2, a jeśli formuła zwróci błąd, oznacza to, że produkt nie został znaleziony na liście 2. W takim przypadku CZY.BŁĄD zwróci wynik PRAWDA i zostanie zastosowane formatowanie. Prawidłowo zagnieżdżone funkcje mają postać:

=CZY.BŁĄD(WYSZUKAJ.PIONOWO(A2;$D$2:$D$10;1;0))

Oczywiście komórka A2, czyli pierwsza komórka z zaznaczonego zakresu, nie może być zablokowana znakami $, żeby mogła dynamicznie się zmieniać podczas wyszukiwania. Zakres, w którym szukana będzie dana wartość musi być jednak zablokowany. W przeciwnym razie uległby on przesunięciu i wynik operacji byłby błędny. Numer kolumny, która ma być przeszukiwana to oczywiście 1 i wpisujemy dopasowanie dokładne, czyli 0. Trzeba także wybrać format, jaki ma być zastosowany dla oznaczenia nieznalezionych nazw.

W wyniku otrzymujemy listę marek, która zawiera oznaczone komórki z wartościami, które nie zostały odnalezione na liście wzorcowej.

Rysunek 25. Zaznaczenie elementów z listy 1, których nie ma na liście 2. Użycie WYSZUKAJ.PIONOWO.

Oznaczenie elementów powtarzających się w dwóch kolumnach

Jeśli chodzi o oznaczenie duplikatów, tak aby wyszukane zostały powtarzające się elementy w obydwu kolumnach, należy postępować analogicznie do oznaczenia elementów unikatowych w dwóch kolumnach. Jedyna różnica polega na zaznaczeniu w oknie dialogowym Duplikujące się wartości, aby formatowane były zduplikowane wartości.

Rysunek 26. Wybranie opcji Zduplikowane.

Oznaczenie elementów równych z drugą kolumną

Kiedy chcemy oznaczyć elementy, które zgadzają się z na obu listach, tzn. znajdują się w odpowiadającej komórce w kolumnie 1 i w kolumnie 2, możemy posłużyć się analogicznym sposobem jak dla oznaczania elementów różnych w kolumnach. Różnica polega na stworzeniu odpowiedniej formuły. Tak więc, należy sprawdzić czy komórka z listy 1 jest taka sama z odpowiadającą jej komórką na liście 2. W tym przypadku formuła powinna mieć postać:

=A2=D2

Oczywiście pamiętajmy, aby pozbyć się blokady adresów komórek. Formatowanie dotyczyć będzie tylko kolumny zaznaczonej.

W ten sposób otrzymujemy zaznaczenie komórek zawierających równe elementy z listą 2.

Oznaczenie elementów obecnych w drugiej kolumnie

Mając dwie listy, nie zawsze kolejność elementów na nich będzie taka sama. Aby odnaleźć i zaznaczyć wartości z listy 1 na liście 2 użyjemy formatowania warunkowego z wykorzystaniem LICZ.JEŻELI. Efekt będzie taki sam, jak w przypadku formatowania warunkowego z użyciem WYSZUKAJ.PIONOWO, ale to rozwiązanie może być dla niektórych osób wygodniejsze.

Rysunek 27. Dwie listy nieuporządkowanych produktów.

Zaznacz obszar listy 1, który ma być objęty formatowaniem. Następnie wybierz Formatowanie warunkowe i Nowa reguła i dalej Użyj formuły do określenia komórek, które należy sformatować. W oknie dialogowym Nowa reguła formatowania w obszarze Edytuj opis reguły wpiszemy formułę, która wyselekcjonuje komórki do sformatowania. Jeśli ilość wystąpień danej wartości z listy 1 na liście 2 będzie większa niż 0, wtedy zastosowane będzie formatowanie warunkowe. Prawidłowa składnia formuły powinna mieć postać:

=LICZ.JEŻELI($D$2:$D$10;A2)>0

Zakres listy 2 musi być zablokowany ($D$2:$D$10. Adres komórki stanowiącej kryterium musi być natomiast odblokowany (A2), co umożliwi dynamiczne sprawdzenie wszystkich wartości z zaznaczonego początkowo zakresu. Należy także ustawić żądane formatowanie, klikając przycisk Formatuj.

W wyniku otrzymujemy kolor w komórkach listy 1, gdy ich wartości znajdują się także na liście 2.

Porównanie kolumn za pomocą tabeli przestawnej

Wróćmy do przykładu z listą produktów sprzedawanych w sklepie i porównajmy kolumny w inny sposób, a mianowicie za pomocą tabeli przestawnej.

W dwóch osobnych kolumnach mamy listy produktów sprzętów ze sklepu. Podklejmy wszystkie produkty z listy 2 pod produkty z listy 1. W kolumnie obok wpiszmy nazwę listy, z której pochodzi produkt. Kolumna z numerem listy musi być zatytułowana (B1). Kolejnym krokiem jest stworzenie tabeli przestawnej.

Rysunek 28. Stworzenie jednej tabeli z dwóch osobnych list i dopisanie kolumny z nazwą listy.

Tabelę przestawną tworzy się z poziomu karty Wstawianie, gdzie należy kliknąć Tabela przestawna. W polu Zaznacz tabelę lub zakres zaznacz tabelę z produktami i nazwą listy i kliknij OK. Pole „Produkty” przeciągnij w obszar wierszy, a pole „Nazwa listy” w obszar kolumn. Następnie raz jeszcze przeciągnij pole „produkty”, ale tym razem w obszar wartości.

Rysunek 29. Gdzie przeciągnąć pola w tabeli przestawnej.

W tabeli przestawnej zobaczysz:

  1. pierwszą kolumnę – tutaj jest lista wszystkich produktów z obu list (każdy produkt wymieniony jest tylko 1 raz, nawet gdy pojawił się na listach kilka razy)
  2. kolumnę drugą – informacja o tym ile razy dany produkt pojawił się na liście 1
  3. kolumnę trzecią – informacja o tym ile razy dany produkt pojawił się na liście 2
  4. kolumnę czwartą – informacja o tym ile razy dany produkt pojawił się w sumie na liście 1 i na liście 2

Możesz teraz sobie przeanalizować te dane w wygodny sposób i porównać zawartość obu list.

Rysunek 30. Ostateczny obraz tabeli przestawnej.

200 godzin szkoleń online z Excela

Wypróbuj platformę szkoleniową calculatic.pl i obejrzyj ponad 1300 filmów