Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

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

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

Это учебное пособие охватывает

1. Сравните два столбца построчно.

1.1 Сравните ячейки в одной строке на предмет точного совпадения

1.2 Сравнить ячейки в одной строке на предмет точного совпадения или совпадения без учета регистра (с использованием формулы ЕСЛИ)

1.3 Сравните ячейки в одной строке и выделите совпадающие или несоответствующие данные (используя условное форматирование)

1.4 Сравните два столбца строка за строкой и выделите значения несоответствия (используя VBA)

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

2.1. Сравнить ячейки с двумя столбцами по ячейкам и отобразить результат сравнения в другом столбце (используя формулу)

2.2 Сравнение и выделение повторяющихся или уникальных данных (с использованием условного форматирования)

2.3 Сравните и выберите или выделите повторяющиеся или уникальные данные (с помощью удобного инструмента)

2.4 Сравните два столбца и укажите точные дубликаты в другом столбце (используя код VBA)

2.5 Сравните два столбца и выделите дубликаты (используя код VBA)

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

3.1. Найдите полные совпадения во всех ячейках одной строки (используя формулу ЕСЛИ)

3.2 Найдите совпадения в любых двух ячейках в одной строке (используя формулу ЕСЛИ)

3.3 Найдите полные совпадения во всех ячейках одной строки и выделите их (используя условное форматирование)

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

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

4.1 Сравните и найдите недостающие точки данных (с помощью формулы ВПР или ПОИСКПОЗ)

4.2 Сравните два столбца и перечислите недостающие данные ниже (используя формулу ИНДЕКС)

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

4.4 Сравните два столбца и перечислите недостающие данные ниже (используя VBA)

5. Сравните два столбца (даты), если больше или меньше

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

5.2 Сравните два столбца, если они больше или меньше, чем формат (с использованием условного форматирования)

6. Сравните два столбца и верните значение в другом столбце

6.1 Получите точные совпадающие данные (используя формулу или удобный инструмент)

6.2 Получение данных частичного совпадения (используя формулу)

7. Сравните два столбца и подсчитайте совпадения или различия.

7.1 Сравните два столбца и подсчитайте совпадения (используя формулу СУММПРОИЗВ)

7.2 Сравните два столбца и подсчитайте совпадения или различия (с помощью удобного инструмента)

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

9. Удалите различия или совпадения.

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

11. Сравните два диапазона

11.1 Сравнить два диапазона по ячейке

11.2 Сравните два диапазона, если данные в одном порядке

Внимание

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


1. Сравните два столбца построчно.

Ниже приведен набор данных, в котором мне нужно проверить в той же строке, совпадают ли имена в столбце A с именами в столбце B или нет.
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

1.1 Сравните ячейки в одной строке на предмет точного совпадения


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

= B2 = C2

док сравнить столбцы 1.1 1

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

1.2 Сравнить ячейки в одной строке на предмет точного совпадения или совпадения без учета регистра (с использованием формулы ЕСЛИ)


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

Сравнение ячеек в одной строке на предмет точного совпадения

Если вы хотите использовать тексты «Совпадение» и «Несоответствие» для описания результатов сравнения, воспользуйтесь приведенной ниже формулой:

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

док сравнить столбцы 1.1 1

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

Сравнение ячеек в одной строке на предмет соответствия без учета регистра

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

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

док сравнить столбцы 1.1 1

Нажмите Enter , чтобы получить первый результат, затем перетащите дескриптор автозаполнения в ячейку E8.
док сравнить столбцы 1.1 1

замечание

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

1.3 Сравните ячейки в одной строке и выделите совпадающие или несоответствующие данные (используя условное форматирование)


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

1. Выберите два столбца, которые используются для сравнения (B2: C8, за исключением заголовков столбцов), затем щелкните Главная > Условное форматирование > Новое правило.
док сравнить столбцы 1.1 1

2. Во всплывающем Новое правило форматирования диалоговое окно, щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем введите = $ B2 = $ C2 в текстовое поле Формат значений, где эта формула истинна.
док сравнить столбцы 1.1 1

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

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

4. Нажмите OK > OK чтобы закрыть диалоговые окна, ячейки в одной строке будут выделены, если они совпадают.
док сравнить столбцы 1.1 1

Если вы хотите выделить значения несоответствия, вы можете использовать это в = $ B2 <> $ C2 в Формат значений, где эта формула истинна текстовое поле в Изменить правило форматирования Диалог.
док сравнить столбцы 1.1 1

Тогда различия двух столбцов в одной строке будут выделены заданным цветом.
док сравнить столбцы 1.1 1

1.4 Сравните два столбца строка за строкой и выделите значения несоответствия (используя VBA)


Если вы хотите сравнить два столбца строка за строкой с кодом VBA, это руководство вас удовлетворит.

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

2. В появившемся диалоговом окне щелкните Вставить > модуль.
док сравнить столбцы 1.1 1

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

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

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

док сравнить столбцы 1.1 1

4. нажмите F5 нажмите клавишу для запуска кода, затем появится диалоговое окно для выбора двух столбцов.
док сравнить столбцы 1.1 1

5. Нажмите OK. Затем различия двух столбцов были выделены цветом фона.
док сравнить столбцы 1.1 1

замечание

Вы можете изменить цвет выделения по своему усмотрению, изменив индекс цвета в коде, справочник по индексу цвета:
док сравнить столбцы 1.1 1


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

В этой части набор данных показан ниже, и вы хотите найти все значения, которые находятся одновременно в столбце B и столбце C, или найти значения только в столбце B.
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

2.1. Сравнить ячейки с двумя столбцами по ячейкам и отобразить результаты сравнения в другом столбце (используя формулу)


Здесь вы можете использовать формулу в сочетании с функцией ЕСЛИ и СЧЁТЕСЛИ, чтобы сравнить два столбца и найти значения, которые находятся в столбце B, но не в столбце C.

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

док сравнить столбцы 1.1 1

Нажмите Enter и перетащите маркер автозаполнения в ячейку D8.
док сравнить столбцы 1.1 1

замечание

1. Эта формула сравнивает два столбца без учета регистра.

2. Вы можете изменить описание «Нет в C» и «Да в C» на другие.

3. Если вы хотите сравнить два целых столбца, измените фиксированный диапазон $ C $ 2: $ C $ 8 на $ C: $ C.

2.2 Сравнение и выделение повторяющихся или уникальных данных (с использованием условного форматирования)


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

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

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

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

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

замечание

Если вы хотите выделить уникальные значения (различия) в двух столбцах, также нажмите Главная > Условное форматирование > Выделите правила ячеек > Дублирующие значения для отображения Дублирующие значения диалог, измените Дублировать в Уникальное Произведение в левом раскрывающемся списке, затем выберите другой формат из значений в раскрывающемся списке, нажмите OK.
док сравнить столбцы 1.1 1

Уникальные значения будут выделены.
док сравнить столбцы 1.1 1

Найдите и выделите значения в столбце B, если они также находятся в столбце C.

Если вы хотите выделить значения в столбце B, которые также находятся в столбце C, Cтрадиционное форматирование функция также может оказать вам услугу.

1. Выберите столбец B2: B8, щелкните Главная > Условное форматирование > Новое правило.
док сравнить столбцы 1.1 1

2. в Новое правило форматирования диалога, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила раздел, затем введите = СЧЁТЕСЛИ ($ C $ 2: $ C $ 8, $ B2)> 0 в текстовое поле Формат значений, где эта формула истинна.
док сравнить столбцы 1.1 1

3. щелчок Формат , чтобы перейти к Формат ячеек диалога под Заполнять на вкладке выберите один цвет, чтобы выделить совпадения.

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

4. Нажмите OK > OK. Затем значения в столбце B, которые также существуют в столбце C, были выделены указанным цветом.
док сравнить столбцы 1.1 1

Если вы хотите выделить значения, которые только в столбце B, но не в столбце C, повторите указанные выше шаги, но измените формулу на шаге 2 на = СЧЁТЕСЛИ ($ C $ 2: $ C $ 8, $ B2) = 0 в Новое правило форматирования Диалог.
док сравнить столбцы 1.1 1

Затем выберите другой цвет для форматирования значений.
док сравнить столбцы 1.1 1

замечание

Здесь условное форматирование сравнивает два столбца без учета регистра.

2.3 Сравните и выберите или выделите повторяющиеся или уникальные данные (с помощью удобного инструмента)


Иногда после сравнения двух столбцов вы можете предпринять другие действия с совпадениями или различиями, такие как выбор, удаление, копирование и т. Д. В этом случае удобный инструмент - Выберите одинаковые и разные ячейки of Kutools for Excel может напрямую выбирать совпадения или разницу для лучшего выполнения следующей операции, а также может напрямую выделять значения.
док сравнить столбцы 1.1 1

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки.
док сравнить столбцы 1.1 1

2. Затем в Выберите одинаковые и разные ячейки диалоговое окно, выполните следующие действия:

1). Найдите значения в и Согласно информации разделы, выберите два столбца, с которыми хотите сравнить;

2) Выберите Каждый ряд вариант;

3) Выберите Те же значения or Разные ценности как вам нужно;

4) Если вы хотите выделить значения, выберите нужный вам цвет.
док сравнить столбцы 1.1 1

3. Нажмите Ok, появится диалоговое окно с напоминанием о количестве найденных значений, щелкните OK чтобы закрыть диалог. При этом значения были выбраны, теперь вы можете удалять или копировать или выполнять другие операции.
док сравнить столбцы 1.1 1

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

замечание

Если вы хотите сравнить с чувствительностью к регистру, отметьте Деликатный случай вариант.

Этот инструмент поддерживает сравнение двух столбцов на разных листах.

2.4 Сравните два столбца и укажите точные дубликаты в другом столбце (используя код VBA)


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

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

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

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

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

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
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

док сравнить столбцы 1.1 1

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

Внимание: Сначала выберите левый столбец, затем выберите правый столбец во втором диалоговом окне, иначе дубликаты заменят исходные данные во втором столбце.
док сравнить столбцы 1.1 1док сравнить столбцы 1.1 1

5. Нажмите OK > OK, то совпадения будут автоматически перечислены в правом столбце двух столбцов.
док сравнить столбцы 1.1 1

замечание

Код VBA сравнивает два столбца с учетом регистра.

2.5 Сравните два столбца и выделите дубликаты (используя код VBA)


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

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

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

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

VBA: сравнить два столбца и выделить дубликаты

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

док сравнить столбцы 1.1 1

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

5. Нажмите OK. Во втором диалоговом окне выберите столбец, в котором вы хотите выделить повторяющиеся значения.
док сравнить столбцы 1.1 1

6. Нажмите OK. Теперь дубликаты во втором столбце были выделены цветом фона после сравнения с первым столбцом.
док сравнить столбцы 1.1 1

замечание

1. Код сравнивает столбцы с учетом регистра.

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


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

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

образец документаНажмите, чтобы загрузить образец файла

3.1. Найдите полные совпадения во всех ячейках одной строки (используя формулу ЕСЛИ)


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

=IF(AND(B2=C2, B2=D2), "Full match", "Not")

 док сравнить столбцы 1.1 1

Если ячейки в одной строке совпадают, отображается «Полное совпадение» или «Нет».

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

замечание

1. Формула сравнивает столбцы без учета регистра.

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

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

 док сравнить столбцы 1.1 1

В формуле 3 - это количество столбцов, вы можете изменить его по своему усмотрению.

3.2 Найдите совпадения в любых двух ячейках в одной строке (используя формулу ЕСЛИ)


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

=IF(OR(B2=C2, C2=D2, B2=D2), "Match", "Not")

 док сравнить столбцы 1.1 1

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

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

замечание

1. Эта формула не поддерживает регистр без учета.

2. Если необходимо сравнить много столбцов, сравнение любой пары ячеек в формуле может оказаться слишком длинным. В этом случае вы можете использовать приведенную ниже формулу, которая объединяет функции ЕСЛИ и СЧЁТЕСЛИ.

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,"Not","Match")

 док сравнить столбцы 1.1 1

3.3 Найдите полные совпадения во всех ячейках одной строки и выделите их (используя условное форматирование)


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

1. Выберите диапазон, который вы используете, затем щелкните Главная > Условное форматирование > Новое правило.
док сравнить столбцы 1.1 1

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

=AND($B2=$C2, $B2=$D2)

Or

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

 док сравнить столбцы 1.1 1

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

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

3. Нажмите Формат Нажмите кнопку, чтобы перейти к диалоговому окну «Формат ячеек», затем выберите один цвет заливки или другое форматирование ячейки, чтобы выделить строки.
док сравнить столбцы 1.1 1

4. Нажмите OK > OK, теперь будут выделены только строки, в которых совпадают все ячейки.
док сравнить столбцы 1.1 1

замечание

Приведенные выше формулы не поддерживают регистр.

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


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

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

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

3. Нажмите OK. Теперь различия строк выделены.
док сравнить столбцы 1.1 1

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

замечание

Этот метод сравнивает ячейки без учета регистра.


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

Предположим, что есть два столбца, столбец B длиннее, а столбец C короче, как показано ниже. По сравнению со столбцом B, как найти недостающие данные в столбце C?
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

4.1 Сравните и найдите недостающие точки данных (с помощью формулы ВПР или ПОИСКПОЗ)


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

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

Or

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

 док сравнить столбцы 1.1 1

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

замечание

Приведенные выше две формулы сравнивают данные без учета регистра.

4.2 Сравните два столбца и перечислите недостающие данные ниже (используя формулу ИНДЕКС)


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

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

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

 док сравнить столбцы 1.1 1

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

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

замечание

Эта формула сравнивает ячейки без учета регистра.

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


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

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки.
док сравнить столбцы 1.1 1

2. в Выберите одинаковые и разные ячейки диалог, сделайте как показано ниже:

1) В Найдите значения в раздел, выберите более длинный столбец, содержащий полный список.
In Согласно информации раздел, выберите более короткий столбец, в котором отсутствуют некоторые данные.

2) Выберите Каждый ряд вариант.

3) Выберите Разные ценности вариант.
док сравнить столбцы 1.1 1

3. Нажмите Ok, появится диалоговое окно с напоминанием о количестве недостающих данных, нажмите OK чтобы закрыть это. Затем были выбраны недостающие данные.
док сравнить столбцы 1.1 1

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

замечание

Отметив Без учета регистра опция в диалоговом окне «Выбрать одинаковые и разные ячейки» сравнивает два столбца с учетом регистра.

4.4 Сравните два столбца и перечислите недостающие данные ниже (используя VBA)


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

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

2. Нажмите Вставить > модуль чтобы создать новый модуль для размещения под кодом VBA.
док сравнить столбцы 1.1 1

VBA: сравнить два столбца и заполнить недостающие данные

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

док сравнить столбцы 1.1 1

3. Затем нажмите F5 нажмите клавишу для запуска кода, появится диалоговое окно для выбора двух сравниваемых столбцов.
док сравнить столбцы 1.1 1

4. Нажмите OK. Теперь недостающие данные перечислены под двумя столбцами.
док сравнить столбцы 1.1 1

замечание

Код сравнивает ячейки без учета регистра.


5. Сравните два столбца (даты), если больше или меньше

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

образец документаНажмите, чтобы загрузить образец файла

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


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

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

 док сравнить столбцы 1.1 1

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

замечание

1. В Excel даты хранятся в виде числовых рядов, по сути, это числа. Таким образом, вы применяете формулу для прямого сравнения дат.

2. Если вы хотите сравнить, если дата 1 раньше даты 2 в каждой строке, измените символ. > в < в формуле.

5.2 Сравните два столбца, если они больше или меньше, чем формат (с использованием условного форматирования)


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

1. Выберите даты в столбце B (Date1), затем щелкните Главная > Условное форматирование > Новое правило.
док сравнить столбцы 1.1 1

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем введите формулу = $ B2> $ C2 в текстовое поле Формат значений, где эта формула истинна.
док сравнить столбцы 1.1 1

Если вы хотите выделить ячейки в столбце B, размер которых меньше, чем в столбце C, используйте формулу = $ B2 <$ C2.

3. Нажмите Формат , чтобы открыть Формат ячеек диалоговое окно, затем выберите нужный тип формата.
док сравнить столбцы 1.1 1

4. Нажмите OK > OK. Затем были выделены ячейки в столбце Date1, которые больше, чем в столбце Date2.
док сравнить столбцы 1.1 1


6. Сравните два столбца и верните значение в другом столбце

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

образец документаНажмите, чтобы загрузить образец файла

6.1 Получите точные совпадающие данные (используя формулу или удобный инструмент)


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

Формульный метод

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

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

Or

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

 док сравнить столбцы 1.1 1

Нажмите Enter ключ, и первое значение было найдено. Затем перетащите дескриптор автозаполнения в ячейку F6, все значения были извлечены.
док сравнить столбцы 1.1 1

замечание

1. Формулы не поддерживают регистр.

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

3. Если формулы не могут найти относительное значение, возвращается значение ошибки # N / A.
док сравнить столбцы 1.1 1

Удобный инструментальный метод

Если вы запутались с формулами, вы можете попробовать удобный инструмент - Формула Помощник of Kutools for Excel, который содержит несколько формул для решения большинства задач в Excel. С его помощью вам нужно только выбрать диапазон, но не нужно помнить, как используются формулы.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите ячейку F2 (ячейку, в которую нужно поместить возвращаемое значение) и щелкните Kutools > Формула Помощник > Поиск и справка > Найдите значение в списке.
док сравнить столбцы 1.1 1

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

Чаевые: Не забудьте изменить абсолютную ссылку в Lookup_value поле к относительному, или вы можете найти только первое значение.
док сравнить столбцы 1.1 1

3. Нажмите Ok, первое значение было найдено, затем перетащите дескриптор автозаполнения в ячейку F6.
док сравнить столбцы 1.1 1

6.2 Получение данных частичного совпадения (используя формулу)


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

Для решения этой задачи выберите одну из формул ниже:

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

Or

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

 док сравнить столбцы 1.1 1

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

замечание

1. Формулы не поддерживают регистр.

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

3. Если формулы не могут найти относительное значение, возвращается значение ошибки # N / A.

4. * в формуле - это подстановочный знак, который используется для обозначения любого символа или строки.


7. Сравните два столбца и подсчитайте совпадения или различия

Ниже приведен набор данных для сравнения и подсчета совпадений или различий.
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

7.1 Сравните два столбца и подсчитайте совпадения (используя формулу СУММПРОИЗВ)


Формула СУММПРОИЗВ может быстро подсчитать совпадения в двух столбцах.

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

 док сравнить столбцы 1.1 1

Нажмите Enter ключ для получения результата.
док сравнить столбцы 1.1 1

замечание

Формула подсчитывает ячейки без учета регистра.

7.2 Сравните два столбца и подсчитайте совпадения или различия (с помощью удобного инструмента)


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

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки.
док сравнить столбцы 1.1 1

2. в Выберите одинаковые и разностные ячейки диалоговом окне выберите два диапазона столбцов в Найдите значения в и Согласно информации разделов отдельно, затем выберите Каждый ряд, и выберите Те же значения or Разные ценности вариант как вам нужно.
док сравнить столбцы 1.1 1

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

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

Разные клетки
док сравнить столбцы 1.1 1


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

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

образец документаНажмите, чтобы загрузить образец файла

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

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

 док сравнить столбцы 1.1 1

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

Теперь, если связанная ячейка содержит одно или несколько значений в столбце D, результат отображает число больше 0, если оно не содержит никакого значения в столбце D, возвращается 0.

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

замечание

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

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

Эту формулу нужно только нажать Enter и перетащите маркер автозаполнения.

2. В формулах * - это подстановочный знак, обозначающий любой символ или строку.


9. Удалите различия или совпадения.

Если вы хотите удалить различия или совпадения после сравнения двух столбцов, как показано на скриншоте ниже:
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

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

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),"Difference","Match")

док сравнить столбцы 1.1 1

Нажмите Enter и перетащите дескриптор автозаполнения в ячейку D8.
док сравнить столбцы 1.1 1

Затем примените Фильтр функцию в Excel чтобы отфильтровать различия или совпадения по значению.

Выберите столбец формулы, затем щелкните Данные > Фильтр.
док сравнить столбцы 1.1 1

Затем в столбце C появится кнопка фильтра, нажмите кнопку фильтра, чтобы развернуть раскрывающееся меню, выберите разница or Совпадение как вам нужно. Затем нажмите OK заканчивать.
док сравнить столбцы 1.1 1

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

Теперь нажмите Данные > Фильтр еще раз, чтобы очистить фильтр.
док сравнить столбцы 1.1 1

Удалите столбец формулы, если он вам больше не нужен.
док сравнить столбцы 1.1 1


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

Есть два столбца: один содержит исходные цены, а другой - цены продажи. Теперь в этой части вводится формула для сравнения этих двух столбцов, а затем вычисляется процентное изменение между двумя столбцами.
док сравнить столбцы 1.1 1

образец документаНажмите, чтобы загрузить образец файла

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

=(C2-B2)/B2

 док сравнить столбцы 1.1 1

Нажмите Enter , чтобы получить число, затем перетащите дескриптор автозаполнения в ячейку D7.
док сравнить столбцы 1.1 1

Затем отформатируйте результат формулы в процентах. Выберите результаты, нажмите Главная вкладка и перейдите к Число группа для нажатия Процентный стиль.
док сравнить столбцы 1.1 1

Результаты формулы были отформатированы в процентах.
док сравнить столбцы 1.1 1


11. Сравните два диапазона

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

образец документаНажмите, чтобы загрузить образец файла

11.1 Сравнить два диапазона по ячейке


Вот два диапазона, которые необходимо сравнить по ячейкам, вы можете использовать Выберите одинаковые и разные ячейки утилита Kutools for Excel, чтобы справиться с этим.
док сравнить столбцы 1.1 1

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки.
док сравнить столбцы 1.1 1

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

1). Найдите значения в В разделе выберите диапазон, в котором вы хотите найти совпадения или различия после сравнения двух диапазонов.

2). Согласно информации раздел, выберите другой диапазон, используемый для сравнения диапазона.

3) В на основании раздел, выбрать Однокамерная.

4) Затем в Найдите раздел, выберите тип ячеек, которые вы хотите выделить или выделить.

5). Обработка результатов Вы можете выделить ячейки цветом фона заливки или цветом шрифта, если выделять не нужно, не ставьте галочки.
док сравнить столбцы 1.1 1

3. Нажмите Ok. Появится диалоговое окно с напоминанием о том, сколько ячеек / строк было выбрано, щелкните OK чтобы закрыть это. Теперь ячейки, которые отличаются от ячеек из другого диапазона, выделены и выделены.
док сравнить столбцы 1.1 1

Выделение одинаковых значений
док сравнить столбцы 1.1 1

замечание

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

11.2 Сравните два диапазона, если данные в одном порядке


Если вы хотите сравнить два диапазона по строкам, Функция сравнения ячеек Kutools for Excel может вам помочь.

После бесплатная установка Kutools for Excel, сделайте следующее:

Предположим, диапазон F2: H7 является моделью, теперь вы хотите определить, находятся ли данные в диапазоне B2: D7 в правильном порядке в соответствии с диапазоном F2: H7.
док сравнить столбцы 1.1 1

1. Нажмите Kutools > Сравнить ячейки.
док сравнить столбцы 1.1 1

2. в Сравнить ячейки диалог, установите как показано ниже:

1) Выберите два диапазона в Найдите значения в и Согласно информации коробки отдельно.

2) Выберите тип ячейки, которую хотите выделить в Найдите раздел.

3) Выберите тип выделения в Обработка результатов раздел.
док сравнить столбцы 1.1 1

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


Вы также можете быть заинтересованы в

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

Сравните два листа рядом
В этой статье это поможет вам быстро сравнить два листа, чтобы найти совпадения и различия.

Vlookup для сравнения двух списков на отдельных листах
Здесь будет представлена ​​функция VLOOKUP для сравнения двух списков на разных двух листах.

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



  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Emma Olivia · 10 days ago
    HERBAL DR OKUOFOH WHO PREPARE HERBAL MEDICINE TO CURE ALL KINDS OF DISEASES INCLUDING HERPES DISEASE.
    I have been battling this Herpes disease for almost A year now....I tried all possible means to get cure from my Herpes Disease but all to be in vain until i saw a post in a health forum about a herbal Dr. Okuofoh who prepare herbal medicine to cure all kind of diseases including Herpes Disease, at first i doubted if it was real but decided to give it a try...when i contact Dr Okuofoh via his email (drokuofohherbalhomehealing@gmail.com ) write him and reply me explain how the process work so after ordering for the medicine I got it within 3/4 working days through DHL Delivery and I took it according to the way Dr Okuofoh instructed, I was so happy after 2 week I took the medicine there was very big change in my health when I was done with the process I go for test, I found out I am negative...Herpes patients should also get in touch with this herbalist Dr. Okuofoh to get rid of these Herpes Virus forever his whatsapp number +2349050141449.