Как сравнить две ячейки в 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. Использование формулы рабочего листа

Запустите Excel.
В качестве примера введите в новом листе введите следующие данные (оставьте столбец B пустым):

A	B	C
1	1	3
2	2	5
3	3	8
4	4	2
5	5	0

Повторяющиеся числа отображаются в столбце B, как в следующем примере:

A	B	C
1	1	3
2	2	2	5
3	3	3	8
4	4	2
5	5	5	0

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

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

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

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

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

Введите в качестве примера следующие данные (оставьте столбец B пустым):

A	B	C
1	1	3
2	2	5
3	3	8
4	4	2
5	5	0

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

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

A	B	C
1	1	3
2	2	2	5
3	3	3	8
4	4	2
5	5	5	0

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

Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 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 сравнить данные для двух или более столбцов. В указанных выше случаях информация выводится в последней строчке таблицы и показывает, совпадают данные или различаются в зависимости от задачи и используемой команды. Но существует еще один путь — выделение совпадений одним цветом в двух и более столбцах. В таком случае информация выглядит более наглядно.

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

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

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

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

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

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

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

Допустим вы работаете с таблицей созданной сотрудником, который в неупорядоченный способ заполняет информацию, касающеюся объема продаж по определенным товарам. Одной из ваших задач будет – сравнение. Следует проверить содержит ли столбец таблицы конкретное значение или нет. Конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих клавиш CTRL+F). Однако при регулярной необходимости выполнения поиска по таблице данный способ оказывается весьма неудобным. Кроме этого данный инструмент не позволяет выполнять вычисления с найденным результатом. Каждому пользователю следует научиться автоматически решать задачи в Excel.

Функция СОВПАД позволяет сравнить два столбца таблицы

Чтобы автоматизировать данный процесс стоит воспользоваться формулой с использованием функций =ИЛИ() и =СОВПАД().

Две таблицы.

Чтобы легко проверить наличие товаров в таблице делаем следующее:

СОВПАД.

В ячейку B1 вводим названия товара например – Монитор.
В ячейке B2 вводим следующую формулу:
Обязательно после ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Ведь данная формула должна выполняться в массиве. Если все сделано правильно в строке формул вы найдете фигурные скобки.

В результате формула будет возвращать логическое значение ИСТИНА или ЛОЖЬ. В зависимости от того содержит ли таблица исходное значение или нет.

Разбор принципа действия формулы для сравнения двух столбцов разных таблиц:

Функция =СОВПАД() сравнивает (с учетом верхнего регистра), являются ли два значения идентичными или нет. Если да, возвращается логическое значение ИСТИНА. Учитывая тот факт что формула выполняется в массиве функция СОВПАД сравнивает значение в ячейке B1 с каждым значением во всех ячейках диапазона A5:A10. А благодаря функции =ИЛИ() формула возвращает по отдельности результат вычислений функции =СОВПАД(). Если не использовать функцию ИЛИ, тогда формула будет возвращать только результат первого сравнения.

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

Сравнение на совпадение.

Достаточно ввести массив формул в одну ячейку (E2), потом скопировать его во все остальные ячейки диапазона E3:E8. Обратите внимание, что теперь мы используем абсолютные адреса ссылок на диапазон $A$2:$A$12 во втором аргументе функции СОВПАД.

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

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры