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

Сравнить два столбца построчно

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

Этот раздел содержит два примера, демонстрирующих, как сравнивать два столбца построчно

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

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

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

=B2=C2

Нажмите клавишу Enter и перетащите маркер заполнения до ячейки D8. Если формула возвращает ИСТИНА, значения в соответствующей строке обоих столбцов полностью совпадают; если ЛОЖЬ — значения различаются.

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

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

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

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

Если вы хотите сравнить два столбца построчно с учётом регистра, используйте формулу, сочетающую функции ЕСЛИ и ТОЧНО.

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(COUNTIF($B2:$D2,$B2)>,=2,"Match","No match")

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

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

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

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

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

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

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

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

2. В диалоговом окне Создание правила форматирования

1. Выберите Использовать формулу для определения форматируемых ячеекв разделе Выбор типа правила
2. Введите приведённую ниже формулу в поле Форматировать значения, для которых формула принимает значение ИСТИНА.
   

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

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

3. В диалоговом окне Установить формат ячейки выберите один из вариантов — например, Цвет заполнения или другой формат ячеек для выделения строк. Нажмите ОК > ОК, чтобы закрыть диалоговые окна.

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

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

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

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

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

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

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

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

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

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

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

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

Здесь можно использовать формулу, сочетающую функции ЕСЛИ и СЧЁТЕСЛИ, чтобы сравнить два столбца и найти значения, которые есть в столбце 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.

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

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

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

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

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

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

3. Нажмите ОК. После этого дубликаты в двух столбцах будут выделены.

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

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

1. Активируйте лист, на котором нужно сравнить два столбца, затем нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

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

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, чтобы запустить код. Последовательно появятся два диалоговых окна для выбора двух столбцов. Затем нажмите ОКОК.

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

Сравнить два списка и извлечь совпадающие данные

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

• Пример 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: Find the missing data points and list them in another column (using a handy tool)
• Пример 3: Сравнить два столбца и перечислить пропущенные данные ниже

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

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

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

Или

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

Нажмите клавишу Enter, затем перетащите маркер автозаполнения до ячейки D10. Теперь формула вернёт значение ЛОЖЬ, если данные присутствуют и в столбце B, и в столбце C; если же данные есть только в столбце B, но отсутствуют в столбце C — вернёт ИСТИНА.

Пример 2: Find the missing data points and list them in another column (using a handy tool)

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

После установки Kutools для Excel щёлкните Kutools > Выбрать > Выбрать одинаковые/разные ячейки. Затем в диалоговом окне «Выбрать одинаковые/разные ячейки» выполните следующие действия:

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

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

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

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

Если вы хотите вывести отсутствующие данные под более короткий столбец после сравнения двух столбцов, на помощь придёт мощная формула ИНДЕКС.

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

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

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

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

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

Предположим, в столбце 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. Нажмите кнопку Формат, чтобы открыть диалоговое окно «Установить формат ячейки», выберите нужный формат и нажмите ОК.

4. Теперь ячейки в столбце «Дата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.

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

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

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

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

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

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

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

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

Приведённая выше информация подробно объясняет, как сравнивать столбцы в Excel. Надеемся, вы найдёте её по-настоящему полезной и ценной! Чтобы открыть для себя ещё больше неоценимых советов и приёмов Excel, которые преобразят вашу работу с данными,переходите сюда.