Porównanie 2 plików w Excelu pod kątem różnic

Każdy użytkownik może napotkać sytuację, w której będzie musiał porównać dwie tabele. Cóż, w ostateczności każdy musi porównać dwie kolumny. Tak, oczywiście praca z plikami Excela jest bardzo wygodna i wygodna. Przepraszamy, to nie jest porównanie. Oczywiście wizualne sortowanie małej tabeli jest możliwe, ale gdy liczba komórek idzie w tysiące, trzeba skorzystać z dodatkowych narzędzi analitycznych.

Niestety nie została jeszcze otwarta magiczna różdżka, która umożliwia automatyczne porównywanie wszystkich informacji jednym kliknięciem. Dlatego musisz pracować, a mianowicie zbierać dane, określać niezbędne formuły i wykonywać inne czynności, które pozwalają przynajmniej trochę zautomatyzować porównania.

Takich działań jest wiele. Przyjrzyjmy się niektórym z nich.

Jaki jest cel porównywania plików Excela?

Przyczyn porównywania kilku plików Excela może być wiele. Wcześniej czy później każdy użytkownik staje przed taką potrzebą i nie ma takich pytań. Na przykład możesz chcieć porównać dane z dwóch raportów dla różnych kwartałów, aby sprawdzić, czy dane finansowe poszły w górę lub w dół.

Albo, alternatywnie, nauczyciel musi zobaczyć, którzy studenci zostali wyrzuceni z uniwersytetu, porównując skład grupy studentów w zeszłym roku i w tym roku.

Takich sytuacji może być bardzo dużo. Przejdźmy jednak do praktyki, bo temat jest dość złożony.

Wszystkie sposoby na porównanie 2 tabel w programie Excel

Choć temat jest złożony, jest łatwy. Tak, nie zdziw się. Jest złożony, ponieważ składa się z wielu części. Ale same te części są łatwe do zrozumienia i wykonania. Spójrzmy, jak możesz porównać dwa arkusze Excela, bezpośrednio w praktyce.

Wzór równości i test fałszu-prawdy

Zacznijmy oczywiście od najprostszej metody. Ta metoda porównywania dokumentów jest możliwa i w dość szerokim zakresie. Możesz porównywać nie tylko wartości tekstowe, ale także liczbowe. Weźmy mały przykład. Załóżmy, że mamy dwa zakresy z komórkami w formacie liczbowym. Aby to zrobić, po prostu napisz wzór równości =C2=E2. Jeśli okaże się, że są równe, w komórce zostanie napisane „PRAWDA”. Jeśli się różnią, to FAŁSZ. Następnie musisz przenieść tę formułę do całego zakresu za pomocą znacznika autouzupełniania.

Teraz różnica jest widoczna gołym okiem.

Podkreślanie odrębnych wartości

Możesz również sprawić, by wartości różniące się od siebie były podświetlone specjalnym kolorem. To także całkiem proste zadanie. Jeśli wystarczy, że znajdziesz różnice między dwoma zakresami wartości lub całymi tabelami, musisz przejść do zakładki „Strona główna” i wybrać tam element „Znajdź i zaznacz”. Zanim go klikniesz, zaznacz zestaw komórek przechowujących informacje do porównania. 

W wyświetlonym menu kliknij menu „Wybierz grupę komórek…”. Następnie otworzy się okno, w którym jako kryterium musimy wybrać różnice według linii.

Porównanie 2 tabel przy użyciu formatowania warunkowego

Formatowanie warunkowe to bardzo wygodna i co ważne funkcjonalna metoda, która pozwala wybrać kolor, który podkreśli inną lub tę samą wartość. Możesz znaleźć tę opcję na karcie Strona główna. Tam znajdziesz przycisk o odpowiedniej nazwie i na wyświetlonej liście wybierz „Zarządzaj regułami”. Pojawi się menedżer reguł, w którym musimy wybrać menu „Utwórz regułę”.

Następnie z listy kryteriów musimy wybrać to, w którym jest napisane, że musimy użyć formuły, aby określić komórki, które zostaną sformatowane w specjalny sposób. W opisie reguły musisz podać formułę. W naszym przypadku jest to =$C2<>$E2, po czym potwierdzamy nasze działania wciskając przycisk „Format”. Następnie ustawiamy wygląd komórki i sprawdzamy czy nam się podoba przez specjalne mini okienko z próbką. 

Jeśli wszystko pasuje, kliknij przycisk „OK” i potwierdź działania.

W Menedżerze reguł formatowania warunkowego użytkownik może znaleźć wszystkie reguły formatowania obowiązujące w tym dokumencie. 

Funkcja LICZ.JEŻELI + reguły porównywania tabel

Wszystkie opisane wcześniej metody są wygodne dla tych formatów, których format jest taki sam. Jeśli tabele nie były wcześniej uporządkowane, najlepszą metodą jest porównanie dwóch tabel za pomocą funkcji COUNTIF i zasady. 

Wyobraźmy sobie, że mamy dwa zakresy z nieco różnymi informacjami. Stoimy przed zadaniem porównania ich i zrozumienia, która wartość jest inna. Najpierw musisz wybrać go w pierwszym zakresie i przejść do zakładki „Strona główna”. Tam znajdujemy znaną wcześniej pozycję „Formatowanie warunkowe”. Tworzymy regułę i ustawiamy regułę, aby używała formuły. 

W tym przykładzie formuła jest pokazana na tym zrzucie ekranu.

Następnie ustawiamy format zgodnie z powyższym opisem. Ta funkcja analizuje wartość zawartą w komórce C1 i sprawdza zakres określony w formule. Odpowiada drugiej kolumnie. Musimy wziąć tę zasadę i skopiować ją w całym zakresie. Hurra, wszystkie komórki z nie powtarzającymi się wartościami są podświetlone.

Funkcja WYSZUKAJ.PIONOWO do porównania 2 tabel

W tej metodzie rozważymy funkcję VPR, który sprawdza, czy w dwóch tabelach są jakieś dopasowania. Aby to zrobić, musisz wprowadzić wzór pokazany na poniższym obrazku i przenieść go do całego zakresu, który służy do porównania.

Ta funkcja iteruje po każdej wartości i sprawdza, czy istnieją jakieś duplikaty od pierwszej kolumny do drugiej. Cóż, po wykonaniu wszystkich operacji ta wartość jest zapisywana w komórce. Jeśli go tam nie ma, otrzymujemy błąd #N/A, który wystarcza, aby automatycznie zrozumieć, która wartość nie będzie pasować.

JEŚLI funkcja

Funkcja logiczna JEŚLI - to kolejny dobry sposób na porównanie dwóch zakresów. Główną cechą tej metody jest to, że możesz użyć tylko części tablicy, która jest porównywana, a nie całej tabeli. Oszczędza to zasoby zarówno komputera, jak i użytkownika.

Weźmy mały przykład. Mamy dwie kolumny – A i B. Musimy porównać ze sobą niektóre zawarte w nich informacje. Aby to zrobić, musimy przygotować kolejną kolumnę serwisową C, w której zapisana jest następująca formuła.

Korzystanie z formuły wykorzystującej funkcje IF, JEŻELI BŁĄD и WIĘCEJ EKSPOZYCYJNE możesz iterować po wszystkich żądanych elementach kolumny A, a następnie w kolumnie B. Jeśli został znaleziony w kolumnach B i A, jest zwracany do odpowiedniej komórki.

Makro VBA

Makro to najbardziej złożona, ale także najbardziej zaawansowana metoda porównywania dwóch tabel. niektóre opcje porównania nie są generalnie możliwe bez skryptów VBA. Pozwalają zautomatyzować proces i zaoszczędzić czas. Wszystkie niezbędne operacje przygotowania danych, jeśli zostały raz zaprogramowane, będą nadal wykonywane.

Na podstawie problemu do rozwiązania możesz napisać dowolny program, który porównuje dane bez interwencji użytkownika.

Jak porównywać pliki w Excelu

Jeśli użytkownik postawił sobie zadanie (cóż, lub otrzymał jedno), aby porównać dwa pliki, można to zrobić dwoma metodami jednocześnie. Pierwszy z nich wykorzystuje wyspecjalizowaną funkcję. Aby wdrożyć tę metodę, postępuj zgodnie z instrukcjami:

1. Otwórz pliki, które chcesz porównać.
2. Otwórz zakładkę „Widok” – „Okno” – „Widok obok siebie”.

Następnie dwa pliki zostaną otwarte w jednym dokumencie Excel.

To samo można zrobić za pomocą zwykłych narzędzi Windows. Najpierw musisz otworzyć dwa pliki w różnych oknach. Następnie weź jedno okno i przeciągnij je na lewą stronę ekranu. Następnie otwórz drugie okno i przeciągnij je na prawą stronę. Następnie dwa okna będą obok siebie. 

Formatowanie warunkowe do porównania 2 plików Excel

Bardzo często porównywanie dokumentów oznacza wyświetlanie ich obok siebie. Ale w niektórych przypadkach można zautomatyzować ten proces za pomocą formatowania warunkowego. Dzięki niemu możesz sprawdzić, czy istnieją różnice między arkuszami. Pozwala to zaoszczędzić czas, który można wykorzystać na inne cele.

Najpierw musimy przenieść porównywane arkusze do jednego dokumentu. 

Aby to zrobić, musisz kliknąć odpowiedni arkusz prawym przyciskiem myszy, a następnie kliknąć przycisk „Przenieś lub skopiuj” w menu podręcznym. Następnie pojawi się okno dialogowe, w którym użytkownik może wybrać dokument, w którym ma zostać wstawiony ten arkusz.

Następnie musisz wybrać wszystkie żądane komórki, aby wyświetlić wszystkie różnice. Najłatwiej to zrobić, klikając lewą górną komórkę, a następnie naciskając kombinację klawiszy Ctrl + Shift + End.

Następnie przejdź do okna formatowania warunkowego i utwórz nową regułę. Jako kryterium posługujemy się formułą odpowiednią w konkretnym przypadku, następnie ustalamy format.

Uwaga: adresy komórek należy wskazać te, które znajdują się na innym arkuszu. Można to zrobić za pomocą menu wprowadzania formuł.

Porównywanie danych w Excelu na różnych arkuszach

Załóżmy, że mamy listę pracowników, która zawiera również ich pensje. Ta lista jest aktualizowana co miesiąc. Ta lista jest kopiowana do nowego arkusza.

Załóżmy, że musimy porównać pensje. W takim przypadku możesz użyć tabel z różnych arkuszy jako danych. Użyjemy formatowania warunkowego, aby podkreślić różnice. Wszystko jest proste.

Dzięki formatowaniu warunkowemu możesz dokonywać skutecznych porównań, nawet jeśli nazwiska pracowników są w innej kolejności.

Jak porównać 2 arkusze w arkuszu kalkulacyjnym Excel?

Porównanie informacji znajdujących się na dwóch arkuszach odbywa się za pomocą funkcji WIĘCEJ EKSPOZYCYJNE. Jako pierwszy parametr jest para wartości, których należy szukać na arkuszu odpowiadającym za kolejny miesiąc. Mówiąc najprościej, marzec. Oglądany zakres możemy wyznaczyć jako zbiór komórek, które są częścią nazwanych zakresów, połączonych parami.

Możesz więc porównywać ciągi według dwóch kryteriów – nazwiska i wynagrodzenia. Cóż, lub jakikolwiek inny, zdefiniowany przez użytkownika. W przypadku wszystkich znalezionych dopasowań w komórce, w której wprowadzono formułę, wpisywana jest liczba. W przypadku programu Excel ta wartość zawsze będzie prawdziwa. Dlatego, aby formatowanie zostało zastosowane do tych komórek, które były różne, musisz zastąpić tę wartość KŁAMLIWY, korzystając z funkcji =NIE().

Narzędzie do porównywania arkuszy kalkulacyjnych

Excel ma specjalne narzędzie, które umożliwia porównywanie arkuszy kalkulacyjnych i automatyczne zaznaczanie zmian. 

Należy zauważyć, że to narzędzie jest dostępne tylko dla tych użytkowników, którzy zakupili pakiety biurowe Professional Plus.

Możesz go otworzyć bezpośrednio z zakładki "Strona główna", wybierając element "Porównaj pliki".

Następnie pojawi się okno dialogowe, w którym musisz wybrać drugą wersję książki. Możesz również podać adres internetowy, pod którym znajduje się ta książka.

Po wybraniu dwóch wersji dokumentu musimy potwierdzić nasze działania klawiszem OK.

W niektórych przypadkach może zostać wygenerowany błąd. Jeśli się pojawi, może to oznaczać, że plik jest chroniony hasłem. Po kliknięciu OK zostaniesz poproszony o jego wprowadzenie. 

Narzędzie do porównywania wygląda jak dwa arkusze kalkulacyjne Excel obok siebie w tym samym oknie. W zależności od tego, czy informacje zostały dodane, usunięte, czy też nastąpiła zmiana w formule (a także inne rodzaje akcji), zmiany są wyróżnione różnymi kolorami. 

Jak interpretować wyniki porównania

To bardzo proste: różne rodzaje różnic są oznaczone różnymi kolorami. Formatowanie może obejmować zarówno wypełnienie komórki, jak i sam tekst. Jeśli więc dane zostały wprowadzone do komórki, wypełnienie jest zielone. Jeśli coś staje się niejasne, sama usługa ma symbole, które pokazują, jaki rodzaj zmiany jest podświetlony jakim kolorem.