Vba excel сравнить две таблицы на разных листах

Обновлено: 07.07.2024

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

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

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

После установки надстройки у вас появится новая вкладка с командой вызова функции. При нажатии на команду Сравнение диапазонов появляется диалоговое окно для ввода параметров.

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

Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.

Чтобы сравнить таблицы необходимо выполнить следующие действия:

Указать диапазоны таблиц.
Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
Указать тип сравнения.
Выбрать вариант выдачи результатов.

Тип сравнения таблиц

Программа позволяет выбрать несколько типов сравнения таблиц:

Найти строки одной таблицы, которые отсутствуют в другой таблице

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

Найти совпадающие строки

При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.

Пример работы программы в данном режиме представлен справа на картинке.

Сопоставить таблицы на основе выбранной

В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.

Сравнение таблиц по четырем и более столбцам

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

Создайте в таблицах по пустому столбцу.
В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.

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

Application.ScreenUpdating = False
Selection.Interior.ColorIndex = xlNone

MsgBox "Данные проверены"
End Sub

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

Возникла задача, сравнить две таблицы на соответствие числовых значений. Две таблицы отчётности, в ячейках значения примерно с 10 знаками после запятой. Если есть несоответствие в какой-то ячейке, нужно подкрасить её, либо подкрасить все ячейки, где значения соответствуют. Есть макрос на сравнение, но он возможно не точно сравнивает, т.к. бывает, что значения в одной и той же ячейке в двух таблицах идентичные, но отмечает как несоответствие. Хотелось бы узнать у знающих, в чём может быть проблема.
[vba]

Application.ScreenUpdating = False
Selection.Interior.ColorIndex = xlNone

MsgBox "Данные проверены"
End Sub

Application.ScreenUpdating = False
Selection.Interior.ColorIndex = xlNone

MsgBox "Данные проверены"
End Sub

[/vba] Автор - skrpv1
Дата добавления - 29.01.2020 в 11:35

Добрый день.
Ваш исходный макрос ( в коде ниже - Вар1 ) подсвечивает все ячейки , если находит в них строку из CompareRange .

Например , если в ячейке Листа 1 будет цифра 46.10056546546 (это x) , а в ячейке из диапазона CompareRange будет цифра 100 (это y ) ,

то debug.Print instr(1,46.10056546546,100,vbTextCompare) выдаст 4

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

For Each y In CompareRange

MsgBox "Данные проверены"
End Sub

то debug.Print instr(1,46.10056546546,100,vbTextCompare) выдаст 4

For Each y In CompareRange

MsgBox "Данные проверены"
End Sub

то debug.Print instr(1,46.10056546546,100,vbTextCompare) выдаст 4

For Each y In CompareRange

MsgBox "Данные проверены"
End Sub

[/vba] Автор - t330
Дата добавления - 30.01.2020 в 14:49

Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Огромное вам спасибо. Да, мне подходит 3 вариант вашего кода. Но такой вопрос. Таблица оригинальная, к которой я это применяю имеет диапазон ячеек для проверки B7:R291, как мне сделать проверку каждой ячейки листа 1 с каждой ячейкой листа 2 такого же диапазона? В первоначальном случае это делалось циклом "for", если я правильно понимаю. И получается в вашем 3-ем варианте, диапазон где подсвечиваются ячейки (х), он выделяется сам, т.е. целиком лист 1? Или мне самому нужно прописывать необходимые значения? Извините за глупые вопросы, повторюсь, нахожусь в стадии изучения.

Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Обратите внимание , что в вашем исходном файле форматы некоторых чисел в листах не совпадают, хотя сами числа равны друг другу.

Например в ячейке B7 и в первом и во втором листе стоит цифра 11,6633333541895
но функция =ВПР(Лист1!B7;Лист2!B:B;1;0) показывает Н/Д (см в ячейке Лист1!B15)

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

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

On Error Resume Next

Next y
MsgBox "Данные проверены"
End Sub

On Error Resume Next

Next y
MsgBox "Данные проверены"
End Sub

On Error Resume Next

Я новичок в VBA и искал помощь в написании вспомогательного кода или кода, который может сравнивать один и тот же столбец (B) двух таблиц на 2 разных листах и объединять их в одну таблицу на первом листе. Я рассмотрел способы сделать это, и меня действительно смущает использование диапазонов или объединений в качестве решения. Я хочу, чтобы он нашел элементы, отсутствующие в столбце b на листе 2 (который будет иметь динамическое, но известное имя, сохраненное в переменной), и добавить всю эту строку на лист 1 (названный «Дамп» с дополнительным комментарием к столбцу d, а также проверить наличие строк в «Дамп», но не присутствующих на другом листе. Необходимо только сравнить столбец b двух листов, поскольку столбец b является ключевым.

Вот пример того, что я ищу, учитывая 2 листа данных и окончательный результат.

Неважно, где находится строка, она будет отсортирована позже. Большое спасибо за помощь

2 ответа

Вот, у меня есть для тебя. Мой код может дать правильный ответ на соответствие двух листов. Но порядок не совпадает с вашим. Я думаю, что неважно, в каком порядке будет строка результата. Хорошо, давай проверим мой код:

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

Данные из дампа:

Данные таблицы ICD:

Это результат:

Я знаю, что этот ответ не совпадает с вашим. Но я верю, что это будет вам полезно.

Самый быстрый способ сравнить данные, существующие на двух листах (в случае наличия ключа), - использовать объекты ADODB. Пожалуйста, посмотрите пример и прочтите комментарии в коде.

Примечание. Это не совсем то, что вам нужно, но . Предполагая, что 1 означает, что данные существуют в списке, а null означает, что данных не существует: c44 существует только на > список и c49 существует только в списке Dump .

Читайте также: