Jak porównać dwa arkusze Excela i znaleźć różnice
Porównaj arkusze Excela według unikalnego identyfikatora, aby znaleźć zmienione wartości, nowe wiersze i brakujące rekordy. Prześledź przykład, sprawdź duplikaty i przygotuj precyzyjne polecenie dla AI.
Dwa eksporty zamówień mogą zawierać taką samą liczbę wierszy, a mimo to opisywać różne zamówienia. Porównanie A2 z A2 nie wystarczy, jeśli ktoś posortował jeden z arkuszy lub wstawił rekord.
W przypadku danych biznesowych najpierw dopasuj rekordy według stałego identyfikatora, a następnie porównaj pola należące do tego identyfikatora. Rozdziel trzy wyniki: rekordy zmienione, rekordy występujące tylko w starym arkuszu oraz rekordy występujące tylko w nowym arkuszu. Zachowaj oba źródła i umieść raport w osobnym arkuszu.
Ustal, co chcesz porównać
| Pytanie | Odpowiednia metoda |
|---|---|
| Czy kwota zamówienia się zmieniła? | Dopasuj identyfikator zamówienia, a następnie porównaj kwotę |
| Które zamówienia dodano lub usunięto? | Sprawdź obecność identyfikatorów w obu kierunkach |
| Czy zmieniła się formuła lub format komórki? | Porównaj wersje skoroszytu, a nie tylko zwracane wartości |
| Czy dwa niewielkie arkusze wyglądają inaczej? | Wyświetl je obok siebie, a następnie zbadaj różnice |
Funkcja Microsoftu do wyświetlania arkuszy obok siebie pomaga w kontroli wzrokowej. Nie dopasowuje jednak rekordów według ich identyfikatorów biznesowych. Poniższy przykład porównuje wartości, a nie formatowanie czy treść formuł.
Zacznij od unikalnego klucza
Użyj identyfikatora zamówienia, identyfikatora pracownika lub innego oznaczenia, które powinno występować w każdym źródle tylko raz. Same nazwiska często się do tego nie nadają. Jeśli zamówienie ma kilka pozycji, sam identyfikator zamówienia nie jest unikalny: użyj udokumentowanej kombinacji, na przykład identyfikatora zamówienia i numeru pozycji.
Przed dopasowaniem sprawdź puste identyfikatory, duplikaty, zera wiodące oraz różnice między tekstem a liczbami. Zachowaj oryginalny identyfikator obok każdej jego oczyszczonej wersji. Nie usuwaj istotnych spacji ani zer wiodących tylko po to, aby wymusić dopasowanie. Nasz poradnik dopasowywania kolumn opisuje pokrewne zadanie: dołączanie pól do istniejącej tabeli.
Poniższe formuły używają angielskich nazw funkcji i przecinków. Lokalna wersja Excela może wymagać przetłumaczonych nazw funkcji lub średników. W przykładzie wykorzystano proste identyfikatory bez symboli wieloznacznych oraz niepuste kwoty liczbowe.
Prześledź niewielki przykład
W kopii skoroszytu utwórz dwa arkusze o nazwach Old i New. Wprowadź poniższe fikcyjne rekordy w kolumnach A i B, z nagłówkami w wierszu 1. Kolejność wierszy celowo jest różna.
| ID w Old | Kwota w Old | ID w New | Kwota w New |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
Pierwsze dwie kolumny należą do Old, a ostatnie dwie do New. To dane przykładowe, nie wynik uzyskany przez klienta ani test szybkości.
Najpierw w każdym arkuszu użyj kolumny pomocniczej, aby policzyć wystąpienia identyfikatora z danego wiersza w jego własnym źródle:
=COUNTIF($A$2:$A$5,A2)
Każdy niepusty identyfikator w tym przykładzie powinien zwrócić 1. Zbadaj wartości większe niż 1, zanim wykonasz wyszukiwanie. COUNTIF nie rozróżnia wielkości liter i rozpoznaje symbole wieloznaczne w kryteriach. Jeśli wielkość liter odróżnia Twoje identyfikatory lub zawierają one *, ? albo ~, te proste formuły wymagają innej reguły dopasowania; nie używaj ich bez zmian.
Następnie w Old!D2 policz dopasowania w nowym źródle i skopiuj formułę w dół:
=COUNTIF(New!$A$2:$A$5,A2)
0 oznacza, że identyfikator występuje tylko w Old; 1 oznacza jednego kandydata do dopasowania; wynik większy niż 1 oznacza niejednoznaczne dopasowanie. W Old!E2 pobierz nową kwotę:
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
Porównuj E z B tylko wtedy, gdy D wynosi 1, a obie kwoty są prawidłowymi liczbami. Nie traktuj brakującego rekordu, pustej kwoty i rzeczywistego zera jako tego samego. W przypadku kwot obliczanych ustal odpowiednią regułę zaokrąglania lub tolerancję, zanim sklasyfikujesz różnice.
XLOOKUP zwraca pierwszy pasujący element i nie rozwiązuje problemu duplikatów. Funkcja nie jest też dostępna w Excelu 2016 ani 2019. W tych wersjach, po przeprowadzeniu tych samych kontroli unikalności, użyj w E2 poniższej alternatywy z dokładnym dopasowaniem:
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
Informacje o działaniu i zgodności funkcji znajdziesz w dokumentacji Microsoftu dotyczącej XLOOKUP oraz w poradniku INDEX i MATCH.
Na koniec w New policz wystąpienia każdego identyfikatora w Old, aby znaleźć nowe rekordy:
=COUNTIF(Old!$A$2:$A$5,A2)
Filtrowanie tej kolumny do 0 pozwoli znaleźć A105. Sprawdzanie wyłącznie od Old do New pominęłoby ten rekord.
Przygotuj raport wyjaśniający każdą różnicę
W tym przykładzie osobny raport powinien zawierać:
| ID | Kwota w Old | Kwota w New | Status |
|---|---|---|---|
| A101 | 120 | 120 | Bez zmian |
| A102 | 80 | 95 | Zmieniony |
| A103 | 75 | 75 | Bez zmian |
| A104 | 50 | — | Tylko w Old |
| A105 | — | 60 | Tylko w New |
Mamy pięć różnych identyfikatorów: dwa bez zmian, jeden zmieniony, jeden tylko w Old i jeden tylko w New. Oba źródła zawierają po cztery wiersze. Porównanie wierszy według ich pozycji dałoby mylący obraz.
Jeśli porównujesz kilka pól, dla każdej zmiany zapisz nazwę pola oraz starą i nową wartość. Zachowaj odwołania do wierszy źródłowych, a nie tylko identyfikatory. Puste lub zduplikowane klucze umieść na osobnej liście do sprawdzenia, zamiast usuwać je bez ostrzeżenia. Przeczytaj, jak bezpiecznie usuwać duplikaty, zanim zmienisz którekolwiek źródło.
Zleć GetSheetAI porównanie o jasno określonym zakresie
W panelu bocznym GetSheetAI w Excelu określ klucz, pola, miejsce wyników i reguły zamiast prosić jedynie o „porównanie tych arkuszy”. Zastąp nazwy pól w tym poleceniu swoimi rzeczywistymi nagłówkami; pomiń Status, jeśli źródło zawiera tylko kolumnę z kwotą:
Porównaj Old i New według Order ID, korzystając z faktycznie wypełnionych zakresów. Najpierw zgłoś puste i zduplikowane identyfikatory w każdym źródle. Jeśli identyfikatory są niejednoznaczne, zatrzymaj się i zapytaj, jak je dopasować. Porównaj tylko Amount i Status. Rozróżniaj brakujące rekordy, puste wartości i wartości zerowe. Utwórz nowy arkusz Comparison zawierający identyfikator, zmienione pole, starą wartość, nową wartość, kategorię wyniku i odwołania do wierszy źródłowych. Uwzględnij rekordy występujące tylko po jednej stronie. Nie zmieniaj żadnego z arkuszy źródłowych. Podsumuj liczby rekordów i pokaż wszystkie nierozstrzygnięte wiersze.
Sprawdź proponowaną regułę dopasowania przed zaakceptowaniem zmian. AI może pomóc uporządkować porównanie, ale bez reguły od Ciebie nie może rozstrzygnąć, czy dwa różne identyfikatory oznaczają to samo zamówienie.
Sprawdź raport przed użyciem
- Zweryfikuj jeden rekord bez zmian, jeden zmieniony oraz po jednym rekordzie występującym wyłącznie w każdym ze źródeł.
- Uzgodnij liczbę różnych identyfikatorów ze wszystkimi kategoriami wyników; nierozstrzygnięte identyfikatory policz osobno.
- Upewnij się, że sortowanie któregokolwiek źródła nie zmienia klasyfikacji.
- Sprawdź, czy uwzględniono całe wypełnione zakresy, a nie tylko widoczne lub odfiltrowane wiersze.
- Zachowaj oryginalne eksporty, aby inna osoba mogła odtworzyć porównanie.
Jeżeli kolejnym krokiem jest dopasowanie faktur do płatności, a nie porównywanie wersji, skorzystaj z procedury uzgadniania faktur i płatności. Wiele płatności za jedną fakturę wymaga innych reguł niż ten przykład z jednym rekordem na identyfikator.
