Как сравнить два столбца в excel на совпадения и выделить цветом

Сравнение данных в двух столбцах для поиска дубликатов в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Office для бизнеса Excel 2010 Excel 2007 Еще. Меньше

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

Способ 1. Использование формулы рабочего листа

Способ 2. Использование макроса Visual Basic

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

Чтобы использовать макрос Visual Basic для сравнения данных в двух столбцах, выполните действия, описанные в следующем примере.

1. Запустите Excel.
2. Нажмите ALT+F11, чтобы запустить редактор Visual Basic.
3. В меню Вставка выберите Модуль.
4. Введите следующий код на листе модуля:

Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub

Примечание: Если вы не видите вкладку Разработчик, возможно, вам понадобится включить ее. Для этого выберите Файл > Параметры > Настроить ленту, а затем выберите вкладку Разработчик в поле настройки справа.

Как в Эксель сравнить два столбца на совпадения и найти расхождения

Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.

Как сравнить столбцы в Эксель

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

Два

При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.

Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:

• =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
• =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).

При этом в таблице выводится информация о наличии совпадений или отличий.

Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Альтернативный вариант

Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.

Больше двух

По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.

Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).

Поиск совпадений в двух и более столбцах

Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).

В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).

В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».

Как вывести результат в Эксель

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

Для этого в Эксель сделайте следующее:

• Выделите вертикальные секции с данными, которые нужно сравнить.
• Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
• Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».

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

При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:

1. С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
2. Выделите область с информацией в дополнительной колонке.
3. В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
4. Кликните на «Повторяющиеся значения».
5. Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.

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

Сравнение столбцов в Excel на совпадения в строках

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

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

Читайте также в Комьюнити: Как удалить дубликаты в Microsoft Excel

Вариант 1: Инструмент выделения группы ячеек

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

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

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

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Вариант 2: Условное форматирование

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

1. Если вы еще не отформатировали ваш диапазон данных как таблицу, рекомендую сделать это сейчас, поскольку так вы значительно упростите дальнейшую работу с условным форматированием.
2. После этого достаточно будет выделить всего одну ячейку из вашей таблицы, затем развернуть настройку «‎Условное форматирование» и выбрать действие «‎Создать правило».
3. В списке с выбором типа правила вас интересует «‎Использовать формулу для определения форматирования ячеек». Сейчас нужно просто выделить это правило, кликнув по нему левой кнопкой мыши.
4. В качестве формулы для форматирования используйте =$A2=$B2, где A2 и B2 – первые строки в вашей таблице для сравнения.
5. Теперь условное форматирование уже работает корректно, но пока не применяет никаких внешних изменений к ячейкам. Для исправления этой ситуации нажмите по кнопке «‎Формат».
6. Вы можете задать любое изменение для совпадений, поменяв шрифт или его форматирование, добавив границы для ячеек или выполнив стандартную заливку. В этом случае я остановлюсь на последнем варианте, поскольку он будет самым наглядным.
7. Сейчас условное форматирование еще не работает, поскольку не был задан диапазон для проверки. Выделите всю таблицу, снова откройте «‎Условное форматирование» и выберите «‎Управление правилами».
8. В строке «‎Применяется к» укажите весь диапазон ваших двух столбцов и примените изменения.
9. Если что, то вы можете как выделить его вручную, так и написать в виде диапазона по номерам ячеек.
10. После применения условного форматирования вы увидите, что совпадения в обоих столбцах выделены по вашим правилам изменения внешнего вида. По необходимости вы всегда можете вернуться в настройку правила, указать там целую умную таблицу или повторно выбрать нужный диапазон для проверки.

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

Как сравнить два столбца в Excel и выделить совпадающие ячейки?

Есть столбец в листе 2 содержащий значения которые надо сравнить со значениями в ячейках столбца 2 листа 1 и выделить цветом совпадающие ячейки.
Как это сделать?

PS
Excel почти не пользуюсь полный нуб в этом деле)

• Вопрос задан более года назад
• 137 просмотров

2 комментария

Простой 2 комментария

как это сделать ?

Условное форматирование по пользовательской формуле. Например, =НЕ(ЕОШИБКА(ВПР()))

Григорий Боев @ProgrammerForever Куратор тега Excel
Условное форматирование
=A1=B1
Решения вопроса 0
Ответы на вопрос 1

Системный администратор
Ответ написан более года назад
Комментировать
Нравится 1 Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

• Excel

Как суммировать n первых значений с условием выборки массива по нужному условию?

• 2 подписчика
• 16 нояб.
• 45 просмотров