Jak porównać dwie listy w Excelu i znaleźć różnice

Masz dwie listy, które powinny się zgadzać: pracownicy w systemie kadrowym i w płacowym, płatności z banku i z ewidencji, wykaz umów u dwóch działów. W praktyce prawie nigdy się nie zgadzają. Ktoś jest tylko na jednej liście, ktoś ma inną kwotę, a kilka nazwisk różni się spacją lub wielkością liter.

Ręczne przeglądanie oko po oku jest wolne i zawodne. Poniżej pokazuję metodę z użyciem zwykłych formuł Excela, która znajduje różnice w kilka minut.

Najpierw ustal klucz

Podstawą porównania jest klucz, czyli kolumna, która jednoznacznie identyfikuje wiersz: numer pracownika, numer faktury, kod towaru. Nazwiska nadają się gorzej, bo powtarzają się i bywają zapisywane różnie. Jeśli to możliwe, wybierz numer.

Krok 1: oczyść klucz

Najczęstszy powód „fałszywych różnic” to niewidoczne drobiazgi: spacja na końcu, podwójna spacja, inna wielkość liter. Dodaj kolumnę pomocniczą:

=LITERY.MAŁE(USUŃ.ZBĘDNE.ODSTĘPY(A2))

Jeśli w danych pojawiają się różne warianty znaków, możesz je ujednolicić funkcją PODSTAW, na przykład zamieniając „ó” na „o”:

=PODSTAW(LITERY.MAŁE(USUŃ.ZBĘDNE.ODSTĘPY(A2));"ó";"o")

Kolumnę pomocniczą utwórz na obu listach i porównuj już wyczyszczone wartości.

Krok 2: czy rekord jest na drugiej liście

Na liście A sprawdź, czy klucz występuje na liście B:

=JEŻELI(LICZ.JEŻELI(B!$D:$D;D2)>0;"Jest na drugiej liście";"Tylko tutaj")

Analogicznie zrób drugą stronę na liście B. Wynik „Tylko tutaj” oznacza rekord, którego brakuje po drugiej stronie.

Krok 3: czy wartości się zgadzają

Jeśli klucz jest po obu stronach, porównaj kwotę lub inny atrybut:

=JEŻELI.BŁĄD(JEŻELI(WYSZUKAJ.PIONOWO(D2;B!$D:$E;2;FAŁSZ)=C2;"Zgodne";"Wartości się różnią");"Tylko tutaj")

Przy dużych listach używaj ograniczonego zakresu, na przykład $D$2:$E$5000, bo odwołania do całych kolumn spowalniają skoroszyt.

Krok 4: duplikaty

Powtórzony klucz to osobny problem. Oznacz go:

=JEŻELI(LICZ.JEŻELI(D:D;D2)>1;"Powtórzenie";"")

Przy duplikatach WYSZUKAJ.PIONOWO zawsze trafia na pierwsze znalezione dopasowanie, więc wynik porównania drugiego wiersza bywa mylący. Najpierw wyjaśnij powtórzenia.

Przykład

Lista A (kadry): 101 Kowalski 4500; 102 Nowak 5200; 103 Wiśniewska 4800. Lista B (płace): 101 Kowalski 4500; 102 Nowak 5000; 104 Zielińska 4700.

• 101: zgodne,
• 102: klucz jest po obu stronach, ale kwota się różni (5200 i 5000 zł),
• 103: tylko na liście A,
• 104: tylko na liście B.

Podsumowanie: 4 różne klucze, 1 zgodny, 1 różnica wartości, po 1 rekordzie tylko w A i tylko w B.

Kiedy formuły to za mało

Gdy klucze różnią się zapisem w sposób, którego nie da się oczyścić, na przykład "Kowalski J." i "Kowalski Jan", trzeba dopasować rekordy ręcznie albo wprowadzić wspólny numer. Dobrą praktyką jest naprawianie danych u źródła, a nie w arkuszu porównawczym.

Najczęstsze błędy

• Porównywanie liczb zapisanych jako tekst z liczbami. Excel uzna 101 i „101” za różne wartości.
• Pomijanie czyszczenia spacji i wielkości liter.
• Szukanie po nazwisku z inicjałem, gdy na drugiej liście jest pełne imię. Zwykłe formuły nie dopasują „Kowalski J.” do „Kowalski Jan”.
• Kasowanie „niepasujących” wierszy, zanim ktoś sprawdzi, który zapis jest prawidłowy.

Gotowy darmowy szablon

Szablon jest darmowy. Wklejasz listy do arkuszy „Lista A” i „Lista B”: klucz i dwie dodatkowe wartości, do 5000 wierszy na każdej. Klucz jest czyszczony automatycznie z zbędnych spacji, wielkość liter jest ujednolicana, a litera Ó traktowana jak O.

Dla każdego wiersza dostajesz wynik: „Jest na drugiej liście”, „Tylko tutaj” albo „Jest, ale wartości się różnią”, a powtórzenia klucza są oznaczane. Uzgodnienie odbywa się z pierwszym znalezionym dopasowaniem na drugiej liście. Arkusz „Podsumowanie” pokazuje razem, zgodne, tylko w A, tylko w B, różnice i duplikaty. Osobne arkusze „Tylko w A”, „Tylko w B” i „Różnice” zawierają gotowe listy z numerami wierszy.

Klucz jest porównywany przez pełną zgodność po oczyszczeniu, bez wyszukiwania przybliżonego. Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr i zewnętrznych powiązań.

FAQ

Czy da się porównać listy bez formuł?

Można użyć formatowania warunkowego z regułą wyróżniania duplikatów, ale zobaczysz tylko, co się powtarza, a nie co jest różne. Formuły dają pełniejszy obraz.

Co zrobić z milionem wierszy?

Excel ma limit około miliona wierszy, a formuły na tak dużych danych będą wolne. Wtedy lepszy jest import do bazy lub narzędzie Power Query.

Jak porównać więcej niż dwie listy?

Porównuj je parami, na przykład A z B, potem A z C. Przy większej liczbie list rozważ jedną tabelę zbiorczą z kolumną „źródło”.

Dlaczego Excel widzi różnicę, której nie widać?

Zwykle chodzi o spacje na końcu, twardą spację lub liczbę zapisaną jako tekst. Wyczyść klucz funkcją USUŃ.ZBĘDNE.ODSTĘPY i sprawdź typ danych.