Jak znaleźć różnice między dwiema wersjami arkusza kalkulacyjnego

Aby znaleźć, co się zmieniło między dwiema wersjami arkusza kalkulacyjnego, dopasuj każdy wiersz w starym pliku do jego odpowiednika w nowym, a potem porównaj komórki każdej dopasowanej pary. Najtrudniejsze jest dopasowanie. Porównanie wiersz po wierszu według pozycji przestaje działać, gdy tylko zostanie wstawiony wiersz albo plik zostanie ponownie posortowany, bo każdy kolejny wiersz trafia do niewłaściwej pary. Dopasowanie po kolumnie klucza, na przykład numerze zamówienia, zachowuje parowanie każdego wiersza z jego własnym rekordem, nawet gdy wiersze się przesuwają.

Porównanie arkuszyZnajdź zmiany między dwoma plikamiZrób to za darmo

Dlaczego kolejność wierszy psuje zwykłe porównanie

Porównanie wiersza 2 z wierszem 2 jest szybkie, ale łatwo wprowadza w błąd. Dodaj jeden wiersz na początku, a każdy kolejny zostanie sparowany z niewłaściwym. Wynik to setki „zmian”, które są w rzeczywistości jedną zmianą. Ten sam problem powoduje sortowanie. Ponowne sortowanie nie zmienia danych, a mimo to porównanie oparte na pozycji zgłosi każdy przesunięty wiersz jako zmieniony. Jeśli różnica wygląda na zbyt dużą, najpierw sprawdź kolejność.

Użyj kolumny klucza

Kolumna klucza identyfikuje wiersz i nie zmienia się dla danego wiersza: numer zamówienia, kod produktu, ID klienta. Wybierz kolumnę, w której każda wartość występuje tylko raz. Jeśli klucz się powtarza, porównanie nie rozpozna, który wiersz jest który, więc poszukaj lepszej kolumny albo połącz dwie.

Przy kluczu każdy wiersz trafia do jednej z czterech grup:

Dodane. Klucz występuje tylko w nowym pliku.
Usunięte. Klucz występuje tylko w starym pliku.
Zmienione. Klucz występuje w obu plikach, a co najmniej jedna komórka się różni.
Bez zmian. Klucz występuje w obu plikach, a wszystkie porównywane komórki są takie same.

Porównaj dwa pliki pozwala wybrać kolumnę klucza, pokazuje każdą grupę z liczbą wierszy i wypisuje, które komórki różnią się w każdym zmienionym wierszu.

Zmienione komórki i kolumny, które zawsze się zmieniają

Zmieniony wiersz może różnić się w jednej komórce na trzydzieści, więc patrz na komórki. Zmiana ceny z 12.50 na 13.00 to inny rodzaj zmiany niż nowy adres w tym samym wierszu. Ignoruj kolumny, które zmieniają się przy każdym zapisie, na przykład znacznik „data ostatniej modyfikacji”, żeby nie zaśmiecały listy.

Wielkość liter i spacje też mają znaczenie. Dla komputera „Berlin” i „berlin ” to dwie różne wartości. Ignorowanie wielkości liter i spacji na początku i na końcu usuwa te fałszywe zmiany, ale poprawiona pisownia jest zmianą prawdziwą, więc używaj tych opcji tylko wtedy, gdy różnica nie ma dla Ciebie znaczenia.

Liczby, które wyglądają inaczej, a są równe

Dwie komórki mogą pokazywać różny tekst i zawierać tę samą wartość. To najczęstsze źródło fałszywych zmian:

Separatory tysięcy. 1,000 i 1000 to ta sama liczba.
Miejsca po przecinku. 12.5 wyświetlone jako 12.50 to ta sama wartość w innym zapisie.
Formaty walut. Komórka sformatowana jako £5 przechowuje liczbę 5. Komórka wpisana jako tekst ze znakiem £ już nie.
Daty. Arkusz zapisuje datę jako liczbę seryjną i pokazuje ją w wybranym formacie, więc 03/04/2026 i 2026-04-03 mogą oznaczać ten sam dzień.

Narzędzie do porównania odczytuje liczby tak, jak zapisuje je arkusz, i używa wartości zapisanych przez Excela, a nie formuł, które je wytworzyły. Liczba zapisana jako tekst porównywana jest jako tekst, więc sprawdź typ danych w każdej kolumnie, zanim zaufasz porównaniu tej kolumny.

Komputery przechowują też ułamki w systemie binarnym, więc 0.1 + 0.2 nie jest dokładnie równe 0.3 w większości języków programowania. Ustawienie tolerancji traktuje liczby jako równe, jeśli różnią się nie więcej niż o wybraną wartość, na przykład 0.01 dla jednego grosza.

Gdy nie ma wspólnego klucza

Użyj najbliższej stabilnej pary, jaką masz, na przykład imienia i nazwiska wraz z datą urodzenia, i sprawdź próbkę ręcznie. Jeśli mimo to porównujesz po pozycji, czytaj wynik jako „te wiersze różnią się w tym samym miejscu”, a nie jako dowód tego, co się zmieniło.

Łączenie i dopasowywanie: pokrewne zadania

Porównanie pyta, co różni się między dwiema wersjami jednej tabeli. Łączenie pyta, jak dwie różne tabele do siebie pasują. VLOOKUP to najprostsze łączenie: znajduje pasujący klucz w innej tabeli i zwraca z niej wartość. Dwie pułapki odpowiadają za większość jego niespodzianek. Patrzy tylko na pierwszą kolumnę zakresu, więc klucz musi znajdować się po lewej stronie. Zwraca też pierwsze znalezione dopasowanie, więc klucz występujący dwa razy po cichu sprowadza się do jednego wiersza. Pominięcie ostatniego argumentu przełącza go dodatkowo na dopasowanie przybliżone. XLOOKUP nie wymaga klucza po lewej stronie, ale również zwraca jedno dopasowanie na wyszukanie.

Połącz dwa arkusze po wspólnej kolumnie wykonuje to samo zadanie bez formuł. Wybierz klucz w każdej tabeli i typ złączenia:

Zachowaj wszystkie wiersze z lewej zachowuje każdy wiersz pierwszej tabeli i dodaje pasujące kolumny tam, gdzie istnieją.
Tylko dopasowania zachowuje wyłącznie wiersze znalezione w obu tabelach.
Wiersze z lewej bez dopasowania wypisuje to, czego brakuje w drugiej tabeli, co często jest najbardziej przydatnym wynikiem.

Przy powtarzających się kluczach Pierwsze dopasowanie działa jak VLOOKUP. Wszystkie dopasowania dodaje wiersz za każdym dopasowaniem, więc klient, który pojawia się po prawej stronie trzy razy, pojawi się w wyniku trzy razy. Policz wiersze przed i po złączeniu i najpierw sprawdź duplikaty.

Nazwy zapisane inaczej

Dokładne klucze zawodzą, gdy to samo zapisuje się na dwa sposoby: „Jon Smith” i „John Smith”, „Müller” i „Mueller”. Dopasowanie nazw z list przypisuje każdej nazwie z jednej listy jej najbliższą odpowiednik z drugiej, z oceną. Ocena to wyższy z dwóch wyników. Jeden porównuje litery po kolei i nagradza wspólny początek, co pasuje do „Smith” i „Smyth”. Drugi porównuje słowa, więc nazwa, której wszystkie słowa występują w drugiej nazwie, dostaje 100. Przy 100 zachowaj ostrożność, gdy jedna nazwa ma dodatkowe słowa. Narzędzie ignoruje akcenty i interpunkcję i może pominąć określenia firm, takie jak Inc czy Ltd. Ocena to nadal przybliżenie, więc przeczytaj pary, zanim je zaakceptujesz.

Przypadki brzegowe

Niewidoczne znaki sprawiają, że klucze wyglądające tak samo są różne. Usuń ukryte znaki, jeśli klucze wyglądające identycznie nie chcą się dopasować.
Zera wiodące znikają, gdy plik otworzy się w arkuszu, więc „00421” nie pasuje już do swojej wersji tekstowej. Zaznacz Ignoruj zera wiodące w złączeniu, aby oba zapisy liczyły się jako ten sam klucz.
Ukryte kolumny nadal biorą udział w porównaniu, chyba że je zignorujesz.

Krótka metoda

Wybierz kolumnę klucza w obu plikach i sprawdź, czy jest unikalna.
Porównuj po kluczu i włączaj reguły wielkości liter i spacji tylko wtedy, gdy różnica nie ma znaczenia.
Ignoruj kolumny, które zmieniają się przy każdym zapisie.
Sprawdź sumy. Przy unikalnych kluczach liczba bez zmian plus zmienione plus usunięte równa się liczbie wierszy w starym pliku, a bez zmian plus zmienione plus dodane równa się liczbie wierszy w nowym pliku.

Szybkie odpowiedzi

Dlaczego jeden wstawiony wiersz oznacza wszystko jako zmienione?

Porównanie po pozycji paruje każdy wiersz z tym, który ma ten sam numer, a wstawienie przesuwa każdy kolejny wiersz. Porównaj po kolumnie klucza.

Dlaczego dwa pliki z tymi samymi liczbami pokazują zmianę?

Wartości są prawdopodobnie zapisane jako tekst albo sformatowane inaczej. Sprawdź typ kolumny.