Jak porównać dwa arkusze Excela (dla różnic)

Obejrzyj wideo - jak porównać dwa arkusze Excela pod kątem różnic

Porównanie dwóch plików Excela (lub porównywanie dwóch arkuszy w tym samym pliku) może być trudne, ponieważ skoroszyt Excela pokazuje tylko jeden arkusz na raz.

Staje się to trudniejsze i podatne na błędy, gdy masz dużo danych, które trzeba porównać.

Na szczęście w programie Excel jest kilka fajnych funkcji, które umożliwiają otwieranie i łatwe porównywanie dwóch plików Excela.

W tym samouczku Excela pokażę Ci wiele sposobów porównaj dwa różne pliki Excel (lub arkusze) i sprawdź różnice. Wybrana metoda będzie zależeć od struktury danych i rodzaju porównania, którego szukasz.

Zacznijmy!

Porównaj dwa arkusze Excela w oddzielnych plikach Excela (obok siebie)

Jeśli chcesz porównać dwa oddzielne pliki programu Excel obok siebie (lub dwa arkusze w tym samym skoroszycie), w programie Excel dostępna jest wbudowana funkcja, która to umożliwia.

To jest Zobacz obok siebie opcja.

Jest to zalecane tylko wtedy, gdy masz mały zestaw danych, a ręczne porównywanie tych plików może być mniej czasochłonne i podatne na błędy. Jeśli masz duży zestaw danych, zalecam użycie metody warunkowej lub metody formuły opisanej w dalszej części tego samouczka.

Zobaczmy, jak tego użyć, gdy musisz porównać dwa oddzielne pliki lub dwa arkusze w tym samym pliku.

Załóżmy, że masz dwa pliki z dwóch różnych miesięcy i chcesz sprawdzić, jakie wartości różnią się w tych dwóch plikach.

Domyślnie, gdy otwierasz plik, prawdopodobnie zajmuje on cały ekran. Nawet jeśli zmniejszysz rozmiar, zawsze zobaczysz jeden plik Excela na górze.

Dzięki opcji widoku obok siebie możesz otworzyć dwa pliki, a następnie ułożyć je poziomo lub pionowo. Pozwala to na łatwe porównywanie wartości bez przełączania się tam iz powrotem.

Poniżej znajdują się kroki, aby wyrównać dwa pliki obok siebie i porównać je:

1. Otwórz pliki, które chcesz porównać.
2. W każdym pliku wybierz arkusz, który chcesz porównać.
3. Kliknij kartę Widok
4. W grupie Windows kliknij opcję „Wyświetl obok siebie”. Staje się to dostępne tylko wtedy, gdy masz otwarte co najmniej dwa pliki programu Excel.

Gdy tylko klikniesz opcję Widok obok siebie, program Excel ułoży skoroszyt poziomo. Oba pliki będą widoczne i możesz je edytować/porównywać, gdy są ułożone obok siebie.

Jeśli chcesz rozmieścić pliki pionowo, kliknij opcję Rozmieść wszystko (na karcie Widok).

Spowoduje to otwarcie okna dialogowego „Rozmieść okna”, w którym możesz wybrać „Pionowo”.

W tym momencie, jeśli przewiniesz jeden z arkuszy, drugi pozostanie bez zmian. Możesz to zmienić, aby podczas przewijania jednego arkusza drugi przewijał się w tym samym czasie. Ułatwia to porównywanie wiersz po wierszu i wykrywanie wszelkich różnic.

Ale aby to zrobić, musisz włączyć Synchroniczne przewijanie.

Aby włączyć przewijanie synchroniczne, kliknij kartę Widok (w dowolnym skoroszycie), a następnie kliknij opcję przewijania synchronicznego. To jest przycisk przełączania (więc jeśli chcesz go wyłączyć, po prostu kliknij go ponownie).

Porównywanie wielu arkuszy w oddzielnych plikach Excela (obok siebie)

Dzięki opcji „Wyświetl obok siebie” możesz za jednym razem porównać tylko dwa pliki Excela.

Jeśli masz otwartych wiele plików Excela, po kliknięciu opcji Wyświetl obok siebie zostanie wyświetlone okno dialogowe „Porównaj obok siebie”, w którym możesz wybrać plik, który chcesz porównać z aktywnym skoroszytem.

Jeśli chcesz porównać więcej niż dwa pliki za jednym razem, otwórz wszystkie te pliki, a następnie kliknij opcję Rozmieść wszystko (znajduje się na karcie Widok).

W oknie dialogowym Rozmieść okna wybierz Pionowo/Poziomo, a następnie kliknij OK.

Spowoduje to uporządkowanie wszystkich otwartych plików Excela w wybranej kolejności (pionowej lub poziomej).

Porównaj dwa arkusze (obok siebie) w tym samym skoroszycie programu Excel

Jeśli chcesz porównać dwa oddzielne arkusze w tym samym skoroszycie, nie możesz użyć funkcji Widok obok siebie (ponieważ działa ona tylko w przypadku osobnych plików programu Excel).

Ale nadal możesz wykonać to samo porównanie side-by-side.

Jest to możliwe dzięki „Nowa funkcja Windows w programie Excel, który umożliwia otwarcie dwóch wystąpień w tym samym skoroszycie. Po otwarciu dwóch instancji możesz je ułożyć obok siebie, a następnie porównać.

Załóżmy, że masz skoroszyt programu Excel, który zawiera dwa arkusze dla dwóch różnych miesięcy (styczeń i luty) i chcesz je porównać obok siebie, aby zobaczyć, jak zmieniła się sprzedaż w poszczególnych sklepach:

Poniżej znajdują się kroki, aby porównać dwa arkusze w programie Excel:

1. Otwórz skoroszyt zawierający arkusze, które chcesz porównać.
2. Kliknij kartę Widok
3. W grupie Okno kliknij opcję „Nowe okno”. Spowoduje to otwarcie drugiego wystąpienia tego samego skoroszytu.
4. W zakładce „Widok” kliknij „Rozmieść wszystko”. Spowoduje to otwarcie okna dialogowego Rozmieść okna
5. Wybierz „Pionowo”, aby porównać dane w kolumnach (lub wybierz Poziomo, jeśli chcesz porównać dane w wierszach).
6. Kliknij OK.

Powyższe kroki ułożyłyby oba wystąpienia skoroszytu w pionie.

W tym momencie oba skoroszyty będą miały wybrany ten sam arkusz. W jednym ze skoroszytów wybierz drugi arkusz, który chcesz porównać z aktywnym arkuszem.

Jak to działa?

Po kliknięciu Nowe okno ponownie otwiera ten sam skoroszyt z nieco inną nazwą. Na przykład, jeśli nazwa skoroszytu to „Test” i klikniesz Nowe okno, zostanie nazwany już otwarty skoroszyt „Test – 1”, a drugie wystąpienie jako „Test – 2”.

Zauważ, że to wciąż ten sam skoroszyt. Jeśli wprowadzisz jakiekolwiek zmiany w którymkolwiek z tych skoroszytów, zostanie to odzwierciedlone w obu.

A kiedy zamkniesz jedno wystąpienie otwartego pliku, nazwa powróci do oryginału.

Możesz także włączyć przewijanie synchroniczne, jeśli chcesz (klikając opcję „Przewijanie synchroniczne” w zakładce „Widok”)

Porównaj dwa arkusze i zaznacz różnice (przy użyciu formatowania warunkowego)

Chociaż możesz użyć powyższej metody, aby wyrównać skoroszyty i ręcznie przeglądać dane wiersz po wierszu, nie jest to dobry sposób, jeśli masz dużo danych.

Również ręczne wykonanie tego poziomu porównania może prowadzić do wielu błędów.

Zamiast więc robić to ręcznie, możesz użyć mocy formatowania warunkowego, aby szybko wyróżnić wszelkie różnice w dwóch arkuszach Excela.

Ta metoda jest naprawdę przydatna, jeśli masz dwie wersje w dwóch różnych arkuszach i chcesz szybko sprawdzić, co się zmieniło.

Zauważ, że ty NIE MOGĘ porównaj dwa arkusze w różnych skoroszytach.

Ponieważ formatowanie warunkowe nie może odnosić się do zewnętrznego pliku programu Excel, arkusze, które chcesz porównać, muszą znajdować się w tym samym skoroszycie programu Excel. Jeśli tak nie jest, możesz skopiować arkusz z innego pliku do aktywnego skoroszytu, a następnie dokonać porównania.

W tym przykładzie załóżmy, że masz zestaw danych, jak pokazano poniżej, dla dwóch miesięcy (styczeń i luty) w dwóch różnych arkuszach i chcesz szybko porównać dane w tych dwóch arkuszach i sprawdzić, czy ceny tych elementów uległy zmianie, czy nie.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz dane w arkuszu, w którym chcesz wyróżnić zmiany. Ponieważ chcę sprawdzić, jak zmieniły się ceny od stycznia do lutego, wybrałem dane w arkuszu luty.
2. Kliknij kartę Strona główna
3. W grupie Style kliknij „Formatowanie warunkowe”
4. W wyświetlonych opcjach kliknij „Nowa reguła”
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki sformatować”
6. W polu formuły wprowadź następującą formułę: =B2Sty!B2
7. Kliknij przycisk Formatuj
8. W wyświetlonym oknie dialogowym Formatowanie komórek kliknij kartę „Wypełnij” i wybierz kolor, w którym chcesz podświetlić niedopasowane dane.
9. Kliknij OK
10. Kliknij OK

Powyższe kroki natychmiast podświetliłyby wszelkie zmiany w zestawie danych w obu arkuszach.

Jak to działa?

Formatowanie warunkowe wyróżnia komórkę, gdy dana formuła dla tej komórki zwraca PRAWDA. W tym przykładzie porównujemy każdą komórkę w jednym arkuszu z odpowiadającą komórką w drugim arkuszu (za pomocą operatora nierównego we wzorze).

Gdy formatowanie warunkowe wykryje jakąkolwiek różnicę w danych, podkreśla to w arkuszu Jan (tym, w którym zastosowaliśmy formatowanie warunkowe).

Zauważ, że w tym przykładzie użyłem odniesienia względnego (A1 a nie $A$1 lub $A1 lub A$1).

Używając tej metody do porównywania dwóch arkuszy w programie Excel, pamiętaj o następujących kwestiach;

• Ta metoda jest dobra do szybkiego identyfikowania różnic, ale nie można jej używać na bieżąco. Na przykład, jeśli wprowadzę nowy wiersz w dowolnym zestawie danych (lub usunę wiersz), otrzymam nieprawidłowe wyniki. Po wstawieniu/usunięciu wiersza wszystkie kolejne wiersze są traktowane jako różne i odpowiednio podświetlane.
• Możesz porównać tylko dwa arkusze w tym samym pliku Excel
• Możesz tylko porównać wartość (nie różnicę w formule lub formatowaniu).

Porównaj dwa pliki/arkusze Excela i uzyskaj różnice za pomocą formuły

Jeśli interesuje Cię tylko szybkie porównywanie i identyfikowanie różnic między dwoma arkuszami, możesz użyć formuły, aby pobrać tylko te wartości, które są różne.

W przypadku tej metody będziesz potrzebować osobnego arkusza roboczego, w którym możesz pobrać różnice.

Ta metoda zadziała, jeśli chcesz porównać dwa oddzielne skoroszyty lub arkusze programu Excel w tym samym skoroszycie.

Pokażę ci przykład, w którym porównuję dwa zestawy danych w dwóch arkuszach (w tym samym skoroszycie).

Załóżmy, że masz zestaw danych pokazany poniżej w arkuszu o nazwie Jan (i podobne dane w arkuszu o nazwie Luty) i chcesz wiedzieć, jakie wartości są różne.

Aby porównać te dwa arkusze, najpierw wstaw nowy arkusz (nazwijmy ten arkusz „Różnica”).

W komórce A1 wprowadź następującą formułę:

=JEŻELI(sty!A1lut!A1,"styczeńWartość:"&sty!A1&CHAR(10)&"lutywartość:"&luty!A1"")

Skopiuj i wklej tę formułę dla zakresu, aby obejmowała cały zestaw danych w obu arkuszach. Ponieważ mam mały zestaw danych, skopiuję i wkleję tę formułę tylko w zakresie A1:B10.

Powyższa formuła wykorzystuje warunek JEŻELI do sprawdzenia różnic. Jeśli nie ma różnicy w wartościach, zwróci puste, a w przypadku różnicy, zwróci wartości z obu arkuszy w oddzielnych wierszach w tej samej komórce.

Dobrą rzeczą w tej metodzie jest to, że daje tylko różnice i pokazuje dokładnie, na czym polega różnica. W tym przykładzie łatwo widzę, że ceny w komórkach B4 i B8 są różne (podobnie jak dokładne wartości w tych komórkach).

Porównaj dwa pliki / arkusze Excela i uzyskaj różnice za pomocą VBA

Jeśli musisz dość często porównywać pliki lub arkusze Excela, dobrym pomysłem jest posiadanie gotowego kodu VBA makr Excela i używanie go za każdym razem, gdy chcesz dokonać porównania.

Możesz także dodać makro do paska narzędzi szybkiego dostępu, aby uzyskać dostęp za pomocą jednego przycisku i od razu wiedzieć, jakie komórki są różne w różnych plikach/arkuszach.

Załóżmy, że masz dwa arkusze Jan i Luty i chcesz porównać i podkreślić różnice w arkuszu Jan, możesz użyć poniższego kodu VBA:

Sub CompareSheets() Dim rngCell As Range dla każdego rngCell In Worksheets("Jan").UsedRange If Not rngCell = Worksheets("Feb").Cells(rngCell.Row, rngCell.Column) Then rngCell.Interior.Color = vbYellow End Jeśli następny rngCell End Sub

Powyższy kod wykorzystuje pętlę For Next do przechodzenia przez każdą komórkę w arkuszu Jan (cały używany zakres) i porównuje ją z odpowiednią komórką w arkuszu Luty. W przypadku znalezienia różnicy (która jest sprawdzana za pomocą instrukcji If-Then), podświetla te komórki na żółto.

Możesz użyć tego kodu w zwykłym module w edytorze VB.

A jeśli musisz to robić często, lepiej zapisać ten kod w skoroszycie makr osobistych, a następnie dodać go do paska narzędzi szybkiego dostępu. W ten sposób będziesz mógł dokonać tego porównania jednym kliknięciem przycisku.

Oto kroki, aby uzyskać skoroszyt makr osobistych w programie Excel (domyślnie nie jest dostępny, więc musisz go włączyć).

Oto kroki, aby zapisać ten kod w skoroszycie makr osobistych.

I tutaj znajdziesz kroki, aby dodać ten kod makra do QAT.

Korzystanie z narzędzia innej firmy - komparatora XL

Innym szybkim sposobem porównania dwóch plików Excela i sprawdzenia zgodności i różnic jest użycie bezpłatnego narzędzia innej firmy, takiego jak XL Comparator.

Jest to narzędzie internetowe, w którym możesz przesłać dwa pliki Excela i utworzy plik porównawczy, który będzie zawierał wspólne dane (lub inne dane w zależności od wybranej opcji.

Załóżmy, że masz dwa pliki zawierające zestawy danych klientów (takie jak imię i nazwisko oraz adres e-mail) i chcesz szybko sprawdzić, którzy klienci znajdują się w pliku 1, a nie w pliku 2.

Poniżej znajduje się sposób porównywania dwóch plików Excela i tworzenia raportu porównawczego:

1. Otwórz https://www.xlcomparator.net/
2. Użyj opcji Wybierz plik, aby przesłać dwa pliki (maksymalny rozmiar każdego pliku może wynosić 5 MB)
3. Kliknij przycisk Dalej.
4. Wybierz wspólną kolumnę w obu tych plikach. Narzędzie użyje tej wspólnej kolumny do wyszukiwania dopasowań i różnic
5. Wybierz jedną z czterech opcji, niezależnie od tego, czy chcesz uzyskać pasujące dane, czy inne dane (na podstawie Pliku 1 lub Pliku 2)
6. Kliknij Dalej
7. Pobierz plik porównawczy, który będzie zawierał dane (na podstawie wybranej opcji w kroku 5)

Poniżej znajduje się film, który pokazuje, jak działa narzędzie XL Comparator.

Jedną z obaw, jakie możesz mieć podczas korzystania z narzędzia innej firmy do porównywania plików Excel, jest prywatność. Jeśli posiadasz poufne dane, a prywatność jest dla niego naprawdę ważna, lepiej skorzystać z innych metod przedstawionych powyżej. Zwróć uwagę, że witryna XL Comparator wspomina, że ​​usuwa wszystkie pliki po 1 godzinie od porównania.

Oto niektóre z metod, których możesz użyć do porównania dwóch różnych plików Excel (lub arkuszy roboczych w tym samym pliku Excel). Mam nadzieję, że ten samouczek programu Excel okazał się przydatny.