Metody porównywania tabel w programie Microsoft Excel

Dosyć często użytkownicy programu Excel mają za zadanie porównywanie dwóch tabel lub list w celu zidentyfikowania różnic lub brakujących elementów w nich. Każdy użytkownik radzi sobie z tym zadaniem na swój sposób, ale coraz częściej poświęca się sporo czasu na rozwiązanie tego problemu, ponieważ nie wszystkie podejścia do tego problemu są racjonalne. Jednocześnie istnieje kilka sprawdzonych algorytmów działania, które umożliwiają porównywanie list lub tablic tablic w stosunkowo krótkim czasie przy minimalnym wysiłku. Przyjrzyjmy się bliżej tym opcjom.

Czytaj także: Porównanie dwóch dokumentów w MS Word

Metody porównania

Istnieje wiele sposobów porównywania obszarów tabel w programie Excel, ale wszystkie można podzielić na trzy duże grupy:

W oparciu o tę klasyfikację dobierane są przede wszystkim metody porównania, a także określone działania i algorytmy do wykonania zadania. Na przykład podczas porównywania w różnych książkach należy jednocześnie otworzyć dwa pliki Excel.

Ponadto należy stwierdzić, że porównywanie pól tabeli ma sens tylko wtedy, gdy mają podobną strukturę.

Metoda 1: Prosta formuła

Najprostszym sposobem porównania danych w dwóch tabelach jest użycie prostej formuły równości. Jeśli dane się pokrywają, to daje TRUE wskaźnik, a jeśli nie, to - FALSE. Możesz porównać zarówno dane liczbowe, jak i tekst. Wadą tej metody jest to, że można jej używać tylko wtedy, gdy dane w tabeli są sortowane lub sortowane identycznie, zsynchronizowane i mają taką samą liczbę linii. Zobaczmy, jak zastosować tę metodę w praktyce na przykładzie dwóch tabel umieszczonych na jednym arkuszu.

Mamy więc dwie proste tabele z listami pracowników przedsiębiorstwa i ich pensjami. Konieczne jest porównanie list pracowników i zidentyfikowanie niespójności między kolumnami, w których umieszczane są imiona.

1. Do tego potrzebujemy dodatkowej kolumny na arkuszu. Wpisujemy tam znak "=" . Następnie kliknij imię, które chcesz porównać na pierwszej liście. Ponownie umieść symbol "=" na klawiaturze. Następnie kliknij pierwszą komórkę kolumny, którą porównujemy w drugiej tabeli. Otrzymano następujące wyrażenie:

   =A2=D2

   Chociaż, oczywiście, w każdym konkretnym przypadku współrzędne będą różne, ale esencja pozostanie taka sama.

2. Klikamy klawisz Enter , aby uzyskać wyniki porównania. Jak widać, podczas porównywania pierwszych komórek obu list program wskazywał wskaźnik "TRUE" , co oznacza, że ​​dane pasują.
3. Teraz musimy wykonać podobną operację z pozostałymi komórkami obu tabel w kolumnach, które porównujemy. Ale możesz po prostu skopiować formułę, która zaoszczędzi dużo czasu. Szczególnie ten czynnik jest ważny przy porównywaniu list z dużą liczbą linii.

   Najłatwiejszym sposobem wykonania kopiowania jest użycie uchwytu do wypełniania. Przesuwamy kursor w prawy dolny róg komórki, gdzie mamy wskaźnik "PRAWDA" . W takim przypadku musi zostać przekształcony w czarny krzyż. To jest znacznik wypełnienia. Kliknij lewym przyciskiem myszy i przeciągnij kursor w dół o liczbę linii w porównywanych tablicach tablic.

4. Jak widać, teraz w dodatkowej kolumnie wyświetlane są wszystkie wyniki porównania danych w dwóch kolumnach tablic tablicowych. W naszym przypadku dane nie pasowały tylko do jednej linii. Porównując je, formuła dała wynik "LIE" . Dla wszystkich innych linii, jak widzimy, wzór porównania został wydany przez wskaźnik "PRAWDA" .
5. Ponadto możliwe jest obliczenie liczby niedopasowań za pomocą specjalnej formuły. Aby to zrobić, wybierz element arkusza, w którym będzie on wyprowadzany. Następnie kliknij ikonę "Wstaw funkcję" .
6. W oknie Czarodziej Funkcji , w grupie "Matematyczny" operatorów, wybierz nazwę SUMPRODUCT . Klikamy przycisk "OK" .
7. Otwiera się okno argumentu funkcji SUMPRODUCT , którego głównym zadaniem jest obliczenie sumy produktów z wybranego zakresu. Ale ta funkcja może być używana do naszych celów. Składnia jest dość prosta:

   =СУММПРОИЗВ(массив1;массив2;…)

   Łącznie do argumentów można użyć maksymalnie 255 tablic. Ale w naszym przypadku użyjemy tylko dwóch tablic, dodatkowo jako jednego argumentu.

   Ustawiamy kursor w polu "Array1" i wybieramy na arkuszu porównywany zakres danych w pierwszym obszarze. Następnie umieść znak "nie równy" ( <> ) w polu i wybierz porównywany zakres drugiego obszaru. Następnie zawiń wynikowe wyrażenie w nawiasy, przed którymi wstawiamy dwa znaki "-" . W naszym przypadku wyrażenie było:

   --(A2:A7<>D2:D7)

   Klikamy przycisk "OK" .

8. Operator oblicza i wyświetla wynik. Jak widać, w naszym przypadku wynik jest równy liczbie "1" , to znaczy, że na porównywanych listach znaleziono jedną niezgodność. Jeśli listy były całkowicie identyczne, wynik byłby równy liczbie "0" .

W ten sam sposób możesz porównywać dane w tabelach znajdujących się na różnych arkuszach. Ale w tym przypadku pożądane jest, aby linie w nich były ponumerowane. W przeciwnym razie procedura porównania jest prawie dokładnie taka sama jak opisana powyżej, z wyjątkiem faktu, że po wprowadzeniu formuły musisz przełączać się między arkuszami. W naszym przypadku wyrażenie będzie wyglądać tak:

=B2=Лист2!B2

Oznacza to, że jak widać, przed współrzędnymi danych, które znajdują się na innych arkuszach, różni się od miejsca, w którym wyświetlany jest wynik porównania, wyświetlany jest numer arkusza i wykrzyknik.

Metoda 2: Wybór grup komórek

Możesz dokonać porównania za pomocą narzędzia do wyboru grupy komórek. Dzięki niemu możesz także porównywać tylko listy zsynchronizowane i uporządkowane. Ponadto w tym przypadku listy powinny być umieszczone obok siebie na jednym arkuszu.

1. Wybieramy porównywane tablice. Przejdź do zakładki "Strona główna" . Następnie kliknij ikonę "Znajdź i wybierz " , która znajduje się na wstążce w polu narzędziowym "Edycja" . Otworzy się lista, w której wybierasz element "Wybór grupy komórek ..." .

   Ponadto można również uzyskać dostęp do okna wyboru grupy komórek w inny sposób. Ta opcja będzie szczególnie przydatna dla użytkowników, którzy mają wersję programu wcześniej niż Excel 2007, ponieważ metody te nie obsługują za pomocą przycisku "Znajdź i podświetl" . Wybierz tablice, które chcesz porównać, i naciśnij klawisz F5 .

2. Zostaje aktywowane małe okno przejścia. Klikamy przycisk "Wybierz ..." w lewym dolnym rogu.
3. Następnie, niezależnie od dwóch powyższych opcji, otwiera się okno wyboru grup komórek. Ustaw przełącznik w pozycji "Wybierz rzędy" . Kliknij przycisk "OK" .
4. Jak widać, niedopasowane wartości linii zostaną podświetlone innym odcieniem. Ponadto, jak można sądzić z zawartości linii formuły, program aktywuje jedną z komórek aktywnych we wskazanych nie dopasowanych rzędach.

Metoda 3: formatowanie warunkowe

Możesz dokonać porównania, stosując metodę formatowania warunkowego. Podobnie jak w poprzedniej metodzie, porównywane obszary muszą znajdować się na tym samym arkuszu programu Excel i być zsynchronizowane ze sobą.

1. Przede wszystkim wybieramy obszar tabel, który rozważymy jako główny i jak szukać różnic. Zróbmy to drugie w drugiej tabeli. Dlatego wybieramy listę pracowników, którzy w nim znajdują. Po przejściu do zakładki "Strona główna" klikamy przycisk "Formatowanie warunkowe" , który ma lokalizację na wstążce w bloku "Style" . Z rozwijanej listy przejdź do "Zarządzaj regułami".
2. Okno menedżera reguł zostało aktywowane. Kliknij przycisk "Utwórz regułę" .
3. W oknie, które się otworzy, dokonujemy wyboru pozycji "Użyj wzoru" . W polu "Formatuj komórki" zapisujemy formułę zawierającą adresy pierwszych komórek przedziałów porównywanych kolumn, rozdzielonych znakiem "nie równa się" ( <> ). Tuż przed tym wyrażeniem pojawi się znak "=" . Ponadto do wszystkich współrzędnych kolumny w tym wzorze należy zastosować adresowanie bezwzględne. Aby to zrobić, wybierz formułę za pomocą kursora i trzykrotnie naciśnij klawisz F4 . Jak widać, wokół wszystkich adresów kolumn był znak dolara, co oznacza przekształcenie linków w bezwzględne. W naszym konkretnym przypadku formuła ma następującą postać:

   =$A2<>$D2

   Piszemy to wyrażenie w powyższym polu. Następnie kliknij przycisk "Formatuj ..." .

4. Okno "Formatuj komórki" jest aktywne. Przejdź do zakładki "Wypełnij" . Tutaj na liście kolorów zatrzymujemy wybór koloru, który chcemy pomalować tymi elementami, w których dane nie będą się pokrywać. Kliknij przycisk "OK" .
5. Wracając do okna tworzenia reguły formatowania, kliknij przycisk "OK" .
6. Po automatycznym przejściu do okna "Menedżer reguł" kliknij przycisk "OK" i w nim.
7. Teraz w drugiej tabeli elementy, które mają dane, które nie odpowiadają odpowiednim wartościom pierwszego obszaru tabeli, zostaną podświetlone wybranym kolorem.

Istnieje inny sposób zastosowania formatowania warunkowego do zadania. Podobnie jak w poprzednich wersjach, wymaga położenia obu porównywanych obszarów na jednym arkuszu, ale w przeciwieństwie do wcześniej opisanych metod, synchronizacja lub sortowanie danych nie będzie obowiązkowe, co odróżnia tę opcję od wcześniej opisanych.

1. Dokonujemy wyboru obszarów, które wymagają porównania.
2. Przechodzimy do zakładki "Strona główna" . Klikamy przycisk "Formatowanie warunkowe" . Na aktywowanej liście wybierz pozycję "Reguły wyboru komórki" . W następnym menu dokonujemy wyboru pozycji "Zduplikowane wartości" .
3. Otworzy się okno do ustawiania wyboru zduplikowanych wartości. Jeśli zrobiłeś wszystko poprawnie, to w tym oknie pozostaje tylko kliknięcie przycisku "OK" . Chociaż w razie potrzeby możesz wybrać inny kolor podświetlenia w odpowiednim polu tego okna.
4. Po wykonaniu określonej czynności wszystkie powtarzające się elementy zostaną podświetlone wybranym kolorem. Te elementy, które nie pasują, pozostaną przyciemnione w ich oryginalnym kolorze (domyślnie jest to kolor biały). Dzięki temu możesz od razu zobaczyć, jaka jest różnica między tablicami.

Jeśli chcesz, możesz, przeciwnie, pomalować niedopasowane elementy, a wskaźniki, które się ze sobą pokrywają, zostawić z tym samym odcieniem koloru. W tym przypadku algorytm działań jest prawie taki sam, ale w oknie ustawień wyboru duplikatów wartości w pierwszym polu, zamiast parametru "Duplikuj" , wybierz opcję "Unikatowy" . Następnie kliknij przycisk "OK" .

Dlatego te wskaźniki, które się nie pokrywają, zostaną podświetlone.

Lekcja: Formatowanie warunkowe w programie Excel

Metoda 4: Złożona formuła

Można również porównywać dane za pomocą złożonej formuły opartej na funkcji LICZ.JEŻELI . Za pomocą tego narzędzia można policzyć, ile każdego elementu z wybranej kolumny drugiej tabeli zostanie powtórzone w pierwszym.

Operator COUNTRY odnosi się do grupy statystycznej funkcji. Jego zadaniem jest policzyć liczbę komórek, których wartości spełniają dany warunek. Składnia tego operatora to:

=СЧЁТЕСЛИ(диапазон;критерий)

Argument "Range" jest adresem tablicy, w której obliczane są pasujące wartości.

Argument kryterium ustawia warunek dopasowania. W naszym przypadku będzie reprezentował współrzędne określonych komórek w obszarze pierwszego obszaru tabel.

1. Wybierz pierwszy element dodatkowej kolumny, w której liczba zliczeń zostanie policzona. Następnie kliknij ikonę "Wstaw funkcję" .
2. Uruchomi się Kreator funkcji . Przejdźmy do kategorii "Statystyczne" . Na liście znajduje się nazwa "RADA" . Po wybraniu kliknij przycisk "OK" .
3. Wyświetlane jest okno argumentów operatora COUNCIL . Jak widać, nazwy pól w tym oknie odpowiadają nazwom argumentów.

   Ustaw kursor w polu "Zakres" . Następnie przytrzymując lewy przycisk myszy, wybierz wszystkie wartości kolumny z nazwami drugiej tabeli. Jak widać, współrzędne natychmiast mieszczą się w określonym polu. Ale dla naszych celów ten adres powinien być absolutny. Aby to zrobić, wybierz współrzędne w polu i naciśnij klawisz F4 .

   Jak widać, link przyjął bezwzględną formę, która charakteryzuje się obecnością znaków dolara.

   Następnie przejdź do pola "Kryterium" , ustawiając tam kursor. Klikamy pierwszy element o nazwach w pierwszym zakresie tabel. W takim przypadku zostawiamy odniesienie względne. Po wyświetleniu w polu możesz kliknąć przycisk "OK" .

4. Wynik jest wyświetlany w elemencie arkusza. Jest równa liczbie "1" . Oznacza to, że na liście nazw drugiej tabeli występuje jedno nazwisko "Grinev VP" , które jest pierwsze na liście pierwszej tablicy tablicowej.
5. Teraz musimy stworzyć podobne wyrażenie dla wszystkich innych elementów pierwszej tabeli. Aby to zrobić, wykonaj kopiowanie za pomocą znacznika wypełnienia, tak jak zrobiliśmy to wcześniej. Umieść kursor w dolnej prawej części elementu arkusza, który zawiera funkcję LICZ.JEŻELI , a po przekształceniu go w znacznik wypełnienia, blokujemy lewy przycisk myszy i przeciągamy kursor w dół.
6. Jak widać, program dokonał obliczenia zbieżności, porównując każdą komórkę pierwszej tabeli z danymi znajdującymi się w drugim zakresie tabel. W czterech przypadkach wynik wynosił "1" , aw dwóch przypadkach "0" . Oznacza to, że program nie mógł znaleźć w drugiej tabeli dwóch wartości, które są dostępne w pierwszej tablicy tablicowej.

Oczywiście to wyrażenie w celu porównania wskaźników tabelarycznych można zastosować w istniejącej formie, ale istnieje możliwość poprawy.

Uczynimy wartości, które są dostępne w drugiej tabeli, ale nie w pierwszej tabeli, zostaną wyświetlone na osobnej liście.

1. Po pierwsze, przerobimy naszą formułę na KRAJ , nieco , zróbmy z tego jeden z argumentów operatora IF . Aby to zrobić, wybierz pierwszą komórkę, w której znajduje się operator KRAJ . W linii formuł przed nim dodajemy wyrażenie "IF" bez cudzysłowów i otwieramy nawias. Ponadto, aby ułatwić nam pracę, wybieramy wartość "JEŻELI" w linii wzoru i klikamy ikonę "Wstaw funkcję" .
2. Otworzy się okno argumentów funkcji IF . Jak widać, pierwsze pole okna jest już wypełnione wartością operatora COUNCIL . Ale musimy skończyć z czymś innym w tej dziedzinie. Ustawiamy tam kursor i dodajemy "= 0" bez cudzysłowów do już istniejącego wyrażenia.

   Następnie przejdź do pola "Wartość, jeśli jest prawdziwa". Tutaj użyjemy jeszcze jednej zagnieżdżonej funkcji - STRING . Wprowadzamy słowo "LINIA" bez cudzysłowów, następnie otwieramy nawiasy i podajemy współrzędne pierwszej komórki z nazwą w drugiej tabeli, a następnie zamykamy nawiasy. W szczególności w naszym przypadku w polu "Wartość, jeśli jest prawdziwe", otrzymano następujące wyrażenie:

   СТРОКА(D2)

   Teraz instrukcja LINE zgłasza funkcję JEŻELI numer linii, w której znajduje się dane nazwisko, oraz w przypadku spełnienia warunku określonego w pierwszym polu, funkcja IF wypisze tę liczbę do komórki. Kliknij przycisk "OK" .

3. Jak widać, pierwszy wynik jest wyświetlany jako "FALSE" . Oznacza to, że wartość ta nie spełnia warunków operatora IF . Oznacza to, że pierwsze nazwisko występuje na obu listach.
4. Używając znacznika wypełnienia, kopiujemy wyrażenie operatora IF do całej kolumny w zwykły sposób. Jak widać, dla dwóch elementów, które są obecne w drugiej tabeli, ale nie w pierwszej, formuła podaje numery linii.
5. Wracamy z obszaru tabel po prawej stronie i wypełniamy kolumny numerami w kolejności od 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Kreator funkcji otworzy się. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Klikamy przycisk "OK" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Klikamy przycisk "OK" .

9. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой ( ; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
12. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

    В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

    Kliknij przycisk "OK" .

13. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Lekcja: Как открыть Эксель в разных окнах

Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.

• ← Zapytania SQL w Microsoft Excel
• Praca z połączonymi tabelami w programie Microsoft Excel →