Методы сравнения таблиц в Microsoft Excel

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

Способы сравнения

Есть несколько способов сравнить области таблиц в Excel, но все они могут быть разделены на три большие группы:

• сравнение списков на одном листе;
• сравнение таблиц, размещенных на разных листах;
• сравнение диапазонов таблиц в разных файлах.

именно на основе этой классификации в первую очередь выбираются методы сравнения, а также определяются действия и алгоритмы, специфичные для деятельности. Например, при сравнении разных книг вам нужно открыть два файла Excel одновременно.

Также следует сказать, что сравнивать табличные пространства имеет смысл только тогда, когда они имеют схожую структуру.

Способ 1: простая формула

Самый простой способ сравнить данные в двух таблицах — использовать простую формулу равенства. Если данные совпадают, возвращается ИСТИНА, иначе — ЛОЖЬ. Вы можете сравнивать как числовые, так и текстовые данные. Недостатком этого метода является то, что его можно использовать только в том случае, если данные в таблице отсортированы или отсортированы одинаково, синхронно и имеют одинаковое количество строк. Давайте посмотрим, как использовать этот метод на практике на примере двух таблиц, размещенных на листе.

Итак, у нас есть две простые таблицы со списками сотрудников компании и их заработной платой. Вам необходимо сравнить списки сотрудников и выявить несоответствия между столбцами, в которые вводятся имена.

1. Для этого нам понадобится дополнительный столбец на листе. Ставим там знак «=». Затем мы щелкаем по первому элементу для сравнения в первом списке. Еще раз вводим с клавиатуры символ «=». Затем щелкните первую ячейку сравниваемого столбца во второй таблице. В результате получается выражение следующего типа:

   = LA2 = RE2

   

   Хотя, конечно, в каждом конкретном случае координаты будут разными, но суть останется прежней.

2. Нажмите клавишу Enter, чтобы получить результаты сравнения. Как видите, при сравнении первых ячеек обоих списков программа указала флаг «ИСТИНА», что означает совпадение данных.
3. Теперь нам нужно выполнить аналогичную операцию с остальными ячейками обеих таблиц в столбцах, которые мы сравниваем. Но вы можете просто скопировать формулу, что сэкономит много времени. Этот фактор особенно важен при сравнении списков с большим количеством строк.

   Процедуру копирования проще выполнить с помощью дескриптора заполнения. Наведите курсор на правый нижний угол ячейки, где мы получили индикатор «ИСТИНА». В этом случае его следует превратить в черный крест. Это индикатор заполнения. Нажмите левую кнопку мыши и перетащите курсор вниз до количества строк в массивах сравниваемых таблиц.

4. Как видите, теперь в дополнительном столбце отображаются все результаты сравнения данных в двух столбцах табличного массива. В нашем случае данные не совпадали в одной строке. Сравнивая их, формула вернула результат «ЛОЖЬ». Для всех остальных строк, как видите, формула сравнения дала показателю «ИСТИНА».
5. Кроме того, количество перекосов можно рассчитать по специальной формуле. Для этого выберите элемент листа, в котором он появится. Затем щелкните значок «Вставить функцию».
6. В окне Мастера функций в группе операторов «Математика» выберите имя СУММПРОИЗВ. Щелкните кнопку «ОК».
7. Активизируется окно аргументов функции СУММПРОИЗВ, основная задача которой — вычислить сумму произведений выбранного диапазона. Но эту функцию можно использовать и для наших целей. Его синтаксис довольно прост:

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

   Всего в качестве аргументов адреса можно использовать до 255 массивов. Но в нашем случае мы будем использовать только два массива, причем в качестве аргумента.

   Мы помещаем курсор в поле «Array1» и выбираем диапазон сравниваемых данных в первой области листа. Затем введите в поле знак «не равно» () и выберите сравниваемый диапазон второй области. Далее заключаем полученное выражение в круглые скобки, перед которыми ставим два знака «-». В нашем случае мы получили следующее выражение:

   — (A2: A7 D2: D7)

   Щелкните кнопку «ОК».

8. Оператор вычисляет и отображает результат. Как видите, в нашем случае результат равен числу «1», т.е это означает, что в сравниваемых списках обнаружено несоответствие. Если бы списки были полностью идентичны, результат был бы равен числу «0».

Точно так же вы можете сравнивать данные в таблицах, которые находятся на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения почти такая же, как описано выше, за исключением того, что при вводе формулы вам нужно будет переключаться между листами. В нашем случае выражение будет выглядеть так:

= B2 = Лист2! B2

То есть, как видно, перед координатами данных, которые расположены на других листах, кроме тех, где отображается результат сравнения, указывается номер листа и восклицательный знак.

Способ 2: выделение групп ячеек

Сравнение можно произвести с помощью инструмента выбора группы ячеек. Это также позволяет сравнивать только синхронизированные и отсортированные списки. Также в этом случае списки должны находиться рядом друг с другом на одном листе.

1. Выбираем сравниваемые массивы. Перейдите на вкладку «Главная». Затем щелкните значок «Найти и выбрать», расположенный на ленте в панели инструментов «Редактировать». Откроется список, в котором необходимо выбрать пункт «Выбрать группу ячеек…».

   

   Кроме того, мы можем получить доступ к окну, необходимому для выбора группы ячеек, другим способом. Этот параметр будет особенно полезен для тех пользователей, которые установили версию программы более раннюю, чем Excel 2007, поскольку эти приложения не поддерживают метод кнопки «Найти и выбрать». Выберите массивы, которые мы хотим сравнить, и нажмите клавишу F5.

2. Активизируется небольшое переходное окно. Нажмите кнопку «Выбрать…» в нижнем левом углу.
3. Впоследствии, какой бы из двух предыдущих вариантов вы ни выбрали, запускается окно выбора групп ячеек. Установите переключатель в положение «Выбрать по линиям». Щелкните кнопку «ОК».
4. Как вы увидите позже, несовпадающие значения строк будут выделены другим оттенком. Кроме того, как вы можете судить по содержимому строки формул, программа активирует одну из ячеек, находящихся в указанных несоответствующих строках.

Способ 3: условное форматирование

Сравнения можно проводить с использованием метода условного форматирования. Как и в предыдущем методе, сравниваемые области должны находиться на одном листе Excel и синхронизироваться друг с другом.

1. В первую очередь выбираем, какая область таблицы будет считаться основной, а в какой искать отличия. Последнее делаем во второй таблице. Поэтому выбираем в нем список сотрудников. Перейдя на вкладку «Главная», нажмите кнопку «Условное форматирование», расположенную на ленте в блоке «Стили». В раскрывающемся списке выберите «Управление правилами».
2. Окно диспетчера правил активировано. Нажмите в нем на кнопку «Создать правило».
3. В открывшемся окне выберите пункт «Использовать формулу». В поле «Формат ячеек» напишите формулу, содержащую адреса первых ячеек в диапазонах сравниваемых столбцов, разделенных знаком «не равно» (). Только перед этим выражением на этот раз будет знак «=». Кроме того, абсолютная адресация должна применяться ко всем координатам столбцов в этой формуле. Для этого выделите формулу курсором и трижды нажмите клавишу F4. Как видите, рядом со всеми адресами столбцов появился знак доллара, что означает, что ссылки конвертируются в абсолютные. В нашем конкретном случае формула будет иметь следующий вид:

   = $ A2 $ D2

   Мы пишем это выражение в поле выше. Затем нажмите кнопку «Форматировать…».

4. Окно «Форматирование ячеек» активировано. Перейдите на вкладку Заливка. Здесь, в списке цветов, мы останавливаем выбор на цвете, которым мы хотим раскрасить те элементы, где данные не будут совпадать. Щелкните кнопку «ОК».
5. Вернувшись в окно создания правила форматирования, нажмите кнопку «ОК».
6. После автоматического перехода в окно «Диспетчер правил» нажмите кнопку «ОК» и войдите в него.
7. Теперь во второй таблице элементы, данные которых не соответствуют соответствующим значениям в первой области таблицы, будут выделены выбранным цветом.

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

1. Мы делаем подборку областей, которые нужно сравнить.
2. Переходим на вкладку «Главная». Нажимаем на кнопку «Условное форматирование». В активированном списке выберите пункт «Правила выбора ячейки». В следующем меню выбираем пункт «Повторяющиеся значения».
3. Откроется окно настройки выбора повторяющегося значения. Если вы все сделали правильно, в этом окне остается только нажать на кнопку «ОК». Однако при желании вы можете выбрать другой цвет выделения в соответствующем поле этого окна.
4. После выполнения указанного действия все повторяющиеся элементы будут выделены выбранным цветом. Не совпадающие элементы останутся окрашенными в свой исходный цвет (по умолчанию белый). Таким образом, вы сразу можете визуально увидеть, в чем разница между массивами.

И наоборот, при желании вы можете раскрасить несовпадающие элементы и оставить маркеры, соответствующие заливке, одного цвета. В этом случае алгоритм действий практически такой же, но в окне настроек для выделения повторяющихся значений в первом поле вместо параметра «Дублировать» выберите параметр «Уникальный». Затем нажмите кнопку «ОК».

Поэтому будут выделены именно те показатели, которые не совпадают.

Способ 4: комплексная формула

Вы также можете сравнить данные, используя сложную формулу, основанную на функции СЧЁТЕСЛИ. Используя этот инструмент, вы можете подсчитать, сколько каждый элемент выбранного столбца второй таблицы повторяется в первой.

Оператор СЧЁТЕСЛИ принадлежит к статистической группе функций. Его задача — подсчитать количество ячеек, в которых значения удовлетворяют заданному условию. Синтаксис этого оператора следующий:

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

Диапазон — это адрес массива, в котором нужно подсчитывать совпадающие значения.

Аргумент критерия определяет условие совпадения. В нашем случае это будут координаты конкретных ячеек в первой области таблицы.

1. Выбираем первый элемент дополнительного столбца, в котором будет подсчитываться количество совпадений. Затем щелкните значок «Вставить функцию».
2. Мастер запускается. Перейдите в категорию «Статистика». Находим в списке имя «СЧЁТЕСЛИ». Выбрав его, нажмите кнопку «ОК».
3. Откроется окно аргументов оператора СЧЁТЕСЛИ. Как видите, названия полей в этом окне соответствуют названиям аргументов.

   Установите курсор в поле «Диапазон». Затем, удерживая левую кнопку мыши, выделите все значения столбца с названиями второй таблицы. Как видите, координаты сразу попадают в указанное поле. Но для наших целей этот адрес следует сделать абсолютным. Для этого выделите эти координаты в поле и нажмите клавишу F4.

   Как видите, ссылка приобрела абсолютную форму, характеризующуюся наличием знака доллара.

   Затем перейдите в поле «Политика», поместив туда курсор. Щелкаем по первому элементу с фамилиями в первом диапазоне таблиц. В этом случае мы оставляем относительную ссылку. После того, как он отобразится в поле, вы можете нажать кнопку «ОК».

4. Результат выводится в элементе листа. Он равен числу «1». Это означает, что в списке имен второй таблицы один раз появляется фамилия «Гринев В.П.», которая является первой в списке массива первой таблицы.
5. Теперь нам нужно создать подобное выражение для всех остальных элементов первой таблицы. Для этого мы копируем с помощью маркера заливки, как делали раньше. Помещаем курсор в нижнюю правую часть элемента листа, содержащего функцию СЧЁТЕСЛИ, и после преобразования его в маркер заливки, удерживая левую кнопку мыши, перетаскиваем курсор вниз.
6. Как видите, программа рассчитывала совпадения, сравнивая каждую ячейку первой таблицы с данными, найденными во втором диапазоне таблицы. В четырех случаях результат был «1», а в двух — «0». То есть программа не смогла найти во второй таблице два значения, которые находятся в первом массиве таблицы.

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

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

1. Прежде всего, мы немного переделаем нашу формулу СЧЁТЕСЛИ, то есть сделаем её одним из аргументов оператора ЕСЛИ. Для этого выберите первую ячейку, в которой находится оператор СЧЁТЕСЛИ. В строке формул перед ним добавьте выражение «ЕСЛИ» без кавычек и откройте скобки. Кроме того, чтобы упростить нашу работу, выберите значение «ЕСЛИ» в строке формул и щелкните значок «Вставить функцию».
2. Откроется окно аргументов функции ЕСЛИ. Как видите, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно добавить что-то еще в это поле. Поместите туда курсор и добавьте «= 0» без кавычек к существующему выражению.

   Затем перейдите в поле «Значение, если истинно». Здесь мы будем использовать еще одну вложенную функцию — STRING. Вставляем слово «СТРОКА» без кавычек, затем открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, затем скобки закрываем. В частности, в нашем случае в поле «Значение, если истина» получилось следующее выражение:

   СТРОКА (D2)

   Теперь оператор LINE сообщит функции IF номер строки, в которой находится определенная фамилия, и в случае выполнения условия, указанного в первом поле, функция IF вернет это число в ячейку. Щелкните кнопку «ОК».

3. Как видите, первый результат отображается как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора IF. То есть первая фамилия присутствует в обоих списках.
4. Используя маркер заполнения, обычным способом копируем выражение оператора IF во весь столбец. Как видите, для двух позиций, которые присутствуют во второй таблице, но не в первой, формула дает номера строк.
5. Давайте вернемся из области таблицы вправо и заполним столбец числами по порядку, начиная с 1. Число чисел должно совпадать с числом строк во второй сравниваемой таблице. Вы также можете использовать индикатор заполнения, чтобы ускорить процесс нумерации.
6. Затем выберите первую ячейку справа от столбца с числами и щелкните значок «Вставить функцию».
7. Мастер откроется. Перейдите в категорию «Статистика» и выберите название «МАЛЕНЬКИЙ». Щелкните кнопку «ОК».
8. Функция SMALL, окно аргументов которой было открыто, предназначена для отображения наименьшего значения, указанного параметром count.

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

   Поле «K» указывает, какая учетная запись является наименьшим значением для отображения. Здесь мы указываем координаты первой ячейки столбца с нумерацией, которую мы недавно добавили. Оставляем относительный адрес. Щелкните кнопку «ОК».

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

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

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

    Щелкните кнопку «ОК».

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

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

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

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