7 методов сравнить две таблицы в Excel на совпадения и выделить цветом

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

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

На данном сайте вы найдете и другие статьи по работе с программой Excel.

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

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

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

Негативные последствия появление дубликатов данных:

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

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

Метод 1. Простой поиск

Отличный вариант для небольших таблиц, так как в Excel сравнить два столбца небольшой длины вручную не составит труда.

Порядок действий:

1. Открыть программу для работы с таблицей, загрузить файл.
2. В основной главной вкладке найти функцию поиска. Щелкнуть по соответствующей кнопке.

   

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

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

Метод 2. Операторы ЕСЛИ и СЧЕТЕСЛИ

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

Алгоритм работы с оператором также не отличается сложностью и доступен даже сравнительно неопытному пользователю:

1. Поместить столбцы для сравнения в одном листе программы. При этом нет необходимости ставить их рядом.
2. В свободном столбце ввести формулу формата

=ЕСЛИ(СЧЕТЕСЛИ(B:B; A2)>0; «Совпадение»; «Нет совпадения»).

3. Протянуть ее до конца столбца, чтобы применить ко всем строкам. Это позволит проверить, есть ли значение из конкретной ячейки A где либо в столбце B независимо от строки.

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

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

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

=ЕСЛИ(A2=B2; «Совпадение»; «Нет совпадения»)

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

Метод 3. Формула подстановки ВПР

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

Как в Экселе использовать ВПР:

1. Открыть нужную таблицу и подготовить столбцы.
2. В следующую колонку ввести формулу вида =ВПР(H6;$I$6:$I$15;1;0), после чего протянуть ее до нужной строки.

Методика позволяет просматривать и сравнивать данные, находить повторяющиеся значения и устанавливать результат тестирования прямо в нужной строке. В рабочем окне должен появиться результат сравнения значений. Если совпадений при сравнении двух столбцов в Excel не найдено, появится надпись #Н/Д.

Метод 4. Функция СОВПАД

Найти одинаковые значения в данных двух столбцов таблицы в Эксель помогут операторы ИЛИ и СОВПАД. Последний встречается не очень часто, поскольку является весьма специфичной функцией.

Использование операторов:

1. Разместить данные в таблицу, в нужных столбцах.
2. В третьем столбца ввести формулу вида =ИЛИ(СОВПАД(I6;$H$6:$H$19)).
3. Протянуть ее до конца таблицы.

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

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

Одной из особенностей функции СОВПАД является возможность сравнения как чисел, так и других типов данных. Причем система чувствительна к верхнему регистру, что делает ее одной из самых точных.

Метод 5. Сравнение с выделением совпадений цветом

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

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

Порядок определения повторений:

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

   

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

   

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

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

Метод 6. Надстройка Inquire

Размышляя о том, как сравнить две таблицы в Excel на совпадения и выделить цветом, имеет смысл вспомнить также о продвинутой надстройке Inquire, которая входит в состав всех актуальных сборок программы. Она рассчитана не столько на сравнение колонок в таблице, сколько на выявление дубликатов между двумя отдельными файлами .XLS или .XLSX.

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

Принципы использования надстройки:

1. Открыть программу и перейти в параметры.
2. Найти пункт «Надстройки» и в нем отыскать раздел «Управление», выбрав «Надстройки COM».

   

3. В появившемся списке отыскать пункт с названием Inquire, поставить напротив него флажок и подтвердить действия нажатием на кнопку «ОК».

   

4. В таблице открыть вкладку Inquire, после чего нажать на команду Compare Files.
5. Выбрать файлы, которые требуется сравнить между собой. Отметить команду «Compare».

   

6. Осуществить сравнение данных между таблицами в отдельно открывшемся окне.

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

Автоматизация поиска одинаковых значений

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

В больших файлах многочисленные формулы могут замедлить работу с таблицей. Поэтому в них целесообразнее пользоваться дополнительными инструментами по типу Power Query. Он является частью Excel с 2016 года, позволяя автоматизировать обработку данных без использования формул.

Порядок использования инструмента:

1. Открыть вкладку «Данные», перейти в «Получить данные» > «Из файла» > «Из книги».
2. Загрузить первую и вторую таблицы.
3. Открыть Power Query Editor и перейти по пути «Главная» > «Объединить запросы» > «Слияние». Выбрать тип слияния «Внутреннее».
4. Развернуть столбцы для просмотра совпадений.

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

Алгоритм создания макросов:

1. В главном окне программы нажать комбинацию клавиш Alt+F11 для открытия редактора VBA.
2. Нажать на «Вставка», выбрать «Модуль». Поместить следующий код.

   

Sub FindMatchesBetweenColumns()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(«Лист1»)  ‘ Укажите ваш лист

Dim lastRow As Long

lastRow = ws.Cells(ws.Rows.Count, «A»).End(xlUp).Row  ‘ Последняя строка в A

 

Dim i As Long

For i = 2 To lastRow  ‘ Начиная со 2 строки (пропуск заголовка)

If Application.WorksheetFunction.CountIf(ws.Range(«B:B»), ws.Cells(i, 1).Value) > 0 Then

ws.Cells(i, 3).Value = «Совпадение»  ‘ В столбец C

Else

ws.Cells(i, 3).Value = «Нет»

End If

Next i

MsgBox «Поиск завершён!»

End Sub

3. Нажать клавиши Alt+F8, выбрать Sub и нажать «Выполнить».

   

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

Sub FindMatchesBetweenSheets()

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets(«Лист1»)

Set ws2 = ThisWorkbook.Sheets(«Лист2»)

Dim lastRow As Long

lastRow = ws1.Cells(ws1.Rows.Count, «A»).End(xlUp).Row

 

Dim i As Long

For i = 2 To lastRow

If Application.WorksheetFunction.CountIf(ws2.Range(«A:A»), ws1.Cells(i, 1).Value) > 0 Then

ws1.Cells(i, 2).Value = «Совпадение»  ‘ В столбец B на Лист1

Else

ws1.Cells(i, 2).Value = «Нет»

End If

Next i

MsgBox «Поиск завершён!»

End Sub

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

Видеоролик другой способ сравнить две таблицы в Excel