Сравнение столбцов в Excel: найдите совпадения и различия!

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

Ниже представлен набор данных (диапазон B2:C8), где необходимо проверить, совпадают ли имена в столбце B с именами в столбце C в одной и той же строке.

В этом разделе приведены два примера, объясняющих, как сравнивать два столбца по строкам.

• Пример1: Сравнение ячеек в одной строке
• Пример2: Сравнение ячеек в одной строке с учетом регистра

Пример1: Сравнение ячеек в одной строке

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

=B2=C2

Нажмите клавишу Enter и протяните маркер автозаполнения до ячейки D8. Если формула возвращает TRUE, значения в одной строке двух столбцов полностью совпадают; если возвращает FALSE — значения различаются.

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

=IF(B2=C2,"Match","No match")

Результат может выглядеть так:

Пример2: Сравнение ячеек в одной строке с учетом регистра

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

IF(EXACT(B2,C2), "Match", "Mismatch")

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

В приведённой формуле вы можете заменить тексты «Совпадает» и «Не совпадает» на свои собственные описания.

Сравнение нескольких столбцов в одной строке

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

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

• Пример1: Сравнение нескольких столбцов и поиск совпадений во всех ячейках одной строки
• Пример2: Сравнение нескольких столбцов и поиск совпадений хотя бы в двух ячейках одной строки

Пример1: Сравнение нескольких столбцов и поиск совпадений во всех ячейках одной строки

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

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

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

Пример2: Сравнение нескольких столбцов и поиск совпадений хотя бы в двух ячейках одной строки

Иногда требуется определить, совпадают ли любые два столбца в одной строке — для этого используйте следующую формулу IF.

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

Нажмите клавишу Enter и протяните маркер автозаполнения до ячейки E7.

Сравнение двух или нескольких столбцов по строкам с выделением совпадений или различий

Если вы хотите сравнить два или более столбцов и выделить совпадения или различия, в этом разделе представлены два способа решения задачи.

Приведены два примера сравнения и выделения совпадений и различий

• Пример1: Сравнение двух столбцов и выделение полных совпадений во всех ячейках одной строки или хотя бы в двух ячейках одной строки
• Пример2: Сравнение двух столбцов и выделение различий в одной строке

Пример1: Сравнение двух столбцов и выделение полных совпадений во всех ячейках одной строки или хотя бы в двух ячейках одной строки

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

1. Выделите нужный диапазон, затем нажмите Главная > Использовать условное форматирование > Новый формат.

2. В диалоговом окне Новый формат выберите

1. Выберите Использовать формулу для определения форматируемых ячеек в разделе Выберите правило
2. Используйте следующую формулу в Форматировать значения, для которых эта формула истинна текстовом поле.
   

   =COUNTIF($B2:$D2, $B2)=3

3. Нажмите Формат.

3. В диалоговом окне Формат ячеек выберите цвет заливки или другой формат для выделения строк. Нажмите OK > OK для закрытия окон.

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

Пример2: Сравнение двух столбцов и выделение различий в одной строке

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

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

2. В появившемся диалоговом окне Перейти к специальному выберите опцию Различия по строкам. Нажмите OK.

Теперь различия по строкам выделены.

3. Оставьте ячейки выделенными, затем нажмите Главная > Цвет заливки и выберите цвет из раскрывающегося меню.

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

В этом разделе представлен набор данных (диапазон B2:C8), и требуется найти все значения, которые одновременно присутствуют в столбцах B и C, либо только значения из столбца B.

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

• Пример1: Сравнение двух столбцов по ячейкам с отображением результата сравнения в отдельном столбце
• Пример2: Сравнение двух столбцов по ячейкам с выделением или выбором дубликатов или уникальных данных с помощью удобного инструмента
• Пример3: Сравнение двух столбцов по ячейкам с выделением дубликатов или уникальных данных
• Пример4: Сравнение двух столбцов по ячейкам с выводом точных дубликатов в отдельный столбец

Пример1: Сравнение двух столбцов по ячейкам с отображением результата сравнения в отдельном столбце

Здесь вы можете использовать формулу, объединяющую функции IF и COUNTIF, чтобы сравнить два столбца и найти значения, которые есть в столбце B, но отсутствуют в столбце C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

Нажмите клавишу Enter и протяните маркер автозаполнения до ячейки D8.

Пример2: Сравнение двух столбцов по ячейкам с выделением или выбором дубликатов или уникальных данных с помощью удобного инструмента

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

После бесплатной установки Kutools для Excel нажмите Kutools > Выбрать > Одномерная таблица. Затем в диалоговом окне Одномерная таблица выполните следующие действия:

1. В разделах Найти значения в и Согласно выберите два столбца по отдельности, которые будут сравниваться.
2. Выберите опцию По строке.
3. Выберите То же значение или Разное значение по необходимости.
4. Укажите, нужно ли выделять выбранные значения цветом, и нажмите OK.

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

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

Пример3: Сравнение двух столбцов по ячейкам с выделением дубликатов или уникальных данных

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

1. Выделите два столбца для сравнения, затем нажмите Главная > Использовать условное форматирование > Правила выделения ячеек > Дублирующиеся значения.

2. В появившемся диалоговом окне Дублирующиеся значения выберите нужный формат выделения из выпадающего списка.

3. Нажмите OK. Теперь дубликаты в двух столбцах будут выделены.

Пример4: Сравнение двух столбцов по ячейкам с выводом точных дубликатов в отдельный столбец

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

1. Активируйте лист, который хотите сравнить, затем нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.

2. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль.

3. Скопируйте и вставьте приведённый ниже код в новый пустой модуль.

VBA: Вывести дубликаты в соседний столбец после сравнения двух столбцов

Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
    For Each xRgF2 In xRgC2
        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
    Next xRgF2
Next xRgF1
End Sub

4. Нажмите клавишу F5 для запуска кода — появятся два диалоговых окна для выбора двух столбцов по отдельности. Затем нажмите OK > OK.

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

Сравнение двух списков с извлечением совпадающих данных

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

• Пример1: Сравнение двух столбцов с извлечением точных совпадений
• Пример2: Сравнение двух столбцов с извлечением частичных совпадений

Пример1: Сравнение двух столбцов с извлечением точных совпадений

Например, есть две таблицы: требуется сравнить столбцы B и E, затем найти соответствующие цены из столбца C и вернуть их в столбец F.

В этом разделе представлены полезные формулы для решения задачи.

В ячейке F2 (куда нужно поместить возвращаемое значение) используйте одну из следующих формул:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Или

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

Нажмите клавишу Enter — будет найдено первое значение. Затем протяните маркер автозаполнения до ячейки F6, все значения будут извлечены.

Пример2: Сравнение двух столбцов с извлечением частичных совпадений

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

В ячейке F2 (куда нужно поместить возвращаемое значение) используйте одну из следующих формул:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Или

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

Нажмите клавишу Enter — будет найдено первое значение. Затем протяните маркер автозаполнения до ячейки F6, все значения будут извлечены.

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

Допустим, есть два столбца: столбец B длиннее, а столбец C короче, как показано на скриншоте ниже. Как найти отсутствующие данные в столбце C по сравнению со столбцом B?

• Пример1: Сравнение двух столбцов с поиском отсутствующих данных
• Пример2: Поиск отсутствующих данных с выводом их в отдельный столбец (с помощью удобного инструмента)
• Пример3: Сравнение двух столбцов с выводом отсутствующих данных ниже

Пример1: Сравнение двух столбцов с поиском отсутствующих данных

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

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Или

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

Нажмите клавишу Enter, затем протяните маркер автозаполнения до ячейки D10. Если данные есть в обоих столбцах B и C, формула вернет FALSE; если данные есть только в столбце B, но отсутствуют в C — формула вернет TRUE.

Пример2: Поиск отсутствующих данных с выводом их в отдельный столбец (с помощью удобного инструмента)

Если после сравнения двух столбцов нужно выполнить дополнительные действия с отсутствующими данными, например, вывести их в отдельный столбец или добавить ниже более короткого столбца, попробуйте удобный инструмент — Одномерная таблица Kutools для Excel.

После установки Kutools для Excel нажмите Kutools > Выбрать > Одномерная таблица. Затем в диалоговом окне Одномерная таблица выполните следующие действия:

1. В разделе Найти значения выберите длинный столбец, содержащий полный список.
2. В разделе Согласно выберите короткий столбец, в котором отсутствуют некоторые данные.
3. Выберите опцию По строке.
4. Выберите опцию Разное значение. Нажмите OK.

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

Теперь вы можете нажать Ctrl + C для копирования выбранных отсутствующих данных и вставить их, нажав Ctrl + V, ниже короткого столбца или в новый столбец по необходимости.

Пример3: Сравнение двух столбцов с выводом отсутствующих данных ниже

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

В ячейке под коротким столбцом, например, в C7, введите следующую формулу:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

Нажмите Shift + Ctrl + Enter, чтобы получить первые отсутствующие данные, затем протяните маркер автозаполнения вниз, пока не появится ошибка #N/A.

Затем удалите ошибку — все отсутствующие данные будут перечислены ниже короткого столбца.

Сравнение двух столбцов с использованием подстановочных знаков

Допустим, в столбце B есть список данных, и требуется посчитать ячейки, содержащие «Apple» или «Candy» в столбце D, как показано на скриншоте ниже:

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

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

Нажмите Shift + Ctrl + Enter, чтобы получить первый результат проверки, затем протяните маркер автозаполнения до ячейки F8.

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

Сравнение двух столбцов (дат) на больше или меньше

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

• Пример1: Сравнение двух столбцов на больше или меньше
• Пример2: Сравнение двух столбцов на больше или меньше с форматированием

Пример1: Сравнение двух столбцов на больше или меньше

Вы можете использовать простую формулу, чтобы быстро определить, позже ли дата1 по сравнению с датой2 в каждой строке.

=IF(B2>C2,"Yes","No")

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

Пример2: Сравнение двух столбцов на больше или меньше с форматированием

Если требуется выделить ячейки в столбце Дата1, которые больше, чем в Дата2, используйте функцию Использовать условное форматирование в Excel.

1. Выделите даты в столбце B (Дата1), затем нажмите Главная > Использовать условное форматирование > Новый формат.

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

 =$B2>$C2

в текстовое поле Форматировать значения, для которых эта формула истинна.

=$B2<$C2.

3. Нажмите кнопку Формат для открытия окна Формат ячеек, затем выберите нужный тип форматирования. Нажмите OK > OK.

Теперь ячейки в столбце Дата1, которые больше, чем в столбце Дата2, будут выделены.

Сравнение двух столбцов с подсчетом совпадений или различий

Ниже приведён пример набора данных для сравнения и подсчёта совпадений или различий.

Формула Сумма позволяет быстро подсчитать совпадения в двух столбцах.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

Нажмите клавишу Enter, чтобы получить результат.

Больше способов подсчёта совпадений и различий смотрите на этой странице: Подсчитать все совпадения / дубликаты между двумя столбцами в Excel

Сравнение двух диапазонов

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

• Пример1: Сравнение двух диапазонов по ячейкам
• Пример2: Сравнение двух диапазонов при одинаковом порядке данных

Пример1: Сравнение двух диапазонов по ячейкам

Есть два диапазона, которые нужно сравнить по ячейкам — используйте функцию Одномерная таблица Kutools для Excel.

После бесплатной установки Kutools для Excel нажмите Kutools > Выбрать > Одномерная таблица. Затем в появившемся окне Одномерная таблица выполните следующие действия:

1. В разделе Найти значения в выберите диапазон, в котором нужно найти совпадения или различия после сравнения двух диапазонов.
2. В разделе Согласно выберите другой диапазон для сравнения.
3. В разделе Метод выберите По ячейке.
4. В разделе Найти выберите тип ячеек, которые хотите выделить или выбрать.
5. В разделе Обработка выбранных результатов можно выделить ячейки цветом фона или цветом шрифта. Если выделять не нужно — не отмечайте флажки. Нажмите OK.

Появится диалоговое окно с информацией о количестве выбранных ячеек/строк, нажмите OK для закрытия.

• Выделение и подсветка уникальных значений
  
• Выделение и подсветка дублирующихся значений
  

Пример2: Сравнение двух диапазонов при одинаковом порядке данных

Допустим, диапазон F2:H7 — это образец, теперь требуется проверить, соответствует ли порядок данных в диапазоне B2:D7 образцу F2:H7.

В этом случае функция Сравнить ячейки Kutools для Excel поможет вам.

После бесплатной установки Kutools для Excel нажмите Kutools > Сравнить ячейки. Затем в окне Сравнить ячейки выполните следующие настройки:

1. Выберите два диапазона в поля Найти значения в и Согласно по отдельности.
2. В разделе Найти выберите тип ячеек, которые хотите выделить.
3. В разделе Обработка выбранных результатов выберите тип выделения. Нажмите OK.

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

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