Как сравнить два списка в Excel? (6 лучших методов)
Ниже перечислены шесть различных методов, которые используются для сравнения двух списков столбца в Excel на предмет совпадений и различий.
Метод 1. Сравните два списка с помощью оператора знака равенства
Метод 2: сопоставление данных с помощью метода разницы строк
Метод 3: сопоставление разницы строк с помощью условия ЕСЛИ
Метод 4: сопоставление данных, даже если есть разница в строках
Метод 5: выделите все совпадающие данные с помощью условного форматирования
Метод 6: Техника частичного сопоставления
Теперь давайте подробно обсудим каждый из методов на примере —
Вы можете скачать этот шаблон Excel для сравнения двух списков здесь — Шаблон Excel для сравнения двух списков
# 1 Сравните два списка с помощью оператора знака равенства
Шаг 1: В следующем столбце сразу после двух столбцов вставьте новый столбец под названием «Статус».
Шаг 2: Теперь поместите формулу в ячейку C2 как = A2 = B2.
Шаг 3: Эта формула проверяет, равно ли значение ячейки A2 ячейке B2. Если оба значения ячеек совпадают, то в результате мы получим ИСТИНА, иначе мы получим результат как ЛОЖЬ.
Шаг 4: Теперь перетащите формулу в ячейку C9, чтобы определить другие значения,
Где бы у нас ни были одинаковые значения в общих строках, мы получали результат ИСТИНА или ЛОЖЬ.
# 2 Сопоставление данных с помощью метода разницы строк
Возможно, вы не использовали технику «разницы строк» на своем рабочем месте, но сегодня я покажу вам, как использовать эту технику для сопоставления данных строка за строкой.
Шаг 1: Чтобы выделить несовпадающие ячейки строка за строкой, сначала выберите все данные.
Шаг 2: Теперь нажмите горячую клавишу Excel «F5», Чтобы открыть инструмент« Перейти к специальному ».
Шаг 3: Нажмите клавишу F5, чтобы открыть это окно. Теперь в окне «Перейти» нажмите вкладку «Специальные».
Шаг 4: В следующем окне «Перейти к специальному» выберите вариант «Разница в строках». Выбрав «Разница в строках», нажмите «ОК».
Получаем следующий результат.
Как вы можете видеть в приведенном выше окне, он выбрал ячейки везде, где есть разница в строках. Теперь залейте его цветом, чтобы выделить значения разницы строк.
# 3 Соответствие разности строк с помощью условия ЕСЛИ
Как мы можем пропустить условие IF, если мы хотим сопоставить данные строка за строкой. В первом примере в результате мы получили ИСТИНА или ЛОЖЬ, но что, если нам нужен результат разницы вместо результатов по умолчанию «ИСТИНА или ЛОЖЬ». Предположим, вам нужен результат типа «Соответствие», если нет разницы в строках, и результат должен быть «Не совпадает», если есть разница в строках.
Шаг 1: Сначала откройте условие IF в ячейке C2.
Шаг 2: Примените логический тест как A2 = B2.
Шаг 3: Если этот логический тест — ИСТИНА, введите критерии результата. В этом сценарии критерием результата является «Соответствие», и если строка не совпадает, нам нужен результат как «Несоответствие».
Шаг 4: Примените формулу, чтобы получить результат.
Шаг 5: Теперь перетащите формулу в ячейку C9, чтобы определить другие значения,
# 4 Данные сопоставления, даже если есть разница в строках
Сопоставление данных по методам разности строк может работать не всегда; значение может быть и в других ячейках, поэтому нам нужно использовать другие технологии в этих сценариях.
Теперь посмотрите на данные ниже.
На изображении выше у нас есть два списка чисел. Нам нужно сравнить список 2 со списком 1. Итак, давайте воспользуемся нашей любимой функцией VLOOKUP.
Итак, если данные совпадают, мы получаем число; в противном случае мы получили бы значение ошибки как # N / A.
Отображение значений ошибок выглядит не очень хорошо. Вместо того, чтобы показывать ошибку, давайте заменим их словом «Недоступно». Для этого используйте функцию ЕСЛИОШИБКА в excel.
# 5 Выделите все совпадающие данные
Если вы не являетесь поклонником формул Excel, не волнуйтесь; мы по-прежнему можем сопоставлять данные без формулы. Используя простое условное форматирование в Excel, мы действительно можем выделить все совпадающие данные двух списков.
Шаг 1: Для этого сначала выберите данные.
Шаг 2: Теперь перейдите в «Условное форматирование» и выберите «Выделить правила ячеек >> Повторяющиеся значения».
Шаг 3: Теперь вы увидите окно форматирования «Повторяющиеся значения ячеек».
Шаг 4: Фактически вы можете выбрать разные цвета форматирования из раскрывающегося списка в Excel. Выберите первый цвет форматирования и нажмите кнопку ОК.
Шаг 5: Это выделит все совпадающие данные из двух списков.
Шаг 6: На всякий случай, вместо выделения всех совпадающих данных, если вы хотите выделить несоответствующие данные, тогда в окне «Повторяющиеся значения» выберите вариант «Уникальный».
Это выделит все несовпадающие значения.
№6 Техника частичного соответствия
Мы видели проблему неполных или одинаковых данных в двух списках. Например, если данные списка 1 имеют «ABC Pvt Ltd» а в списке 2 мы имеем «ABC» только. В этих случаях все наши формулы и инструменты по умолчанию вообще не распознаются. В этих случаях нам нужно использовать звездочку специального символа
Пример 6
В списке 1 указаны название компании и сведения о доходах. В списке 2 у нас есть названия компаний, но нет точных значений, как в списке 1. Это непростая ситуация, с которой мы все столкнулись на своем рабочем месте.
.
Получаем следующий результат.
Теперь перетащите формулу в ячейку E9, чтобы определить другие значения,
Сравните два списка в Excel, пример 6.3.0
Подстановочный знак звездочка
был использован для представления любого количества символов, поэтому он будет соответствовать полному символу слова «ABC» как «ABC Pvt Ltd.»
То, что нужно запомнить
Использование описанных выше методов сравнения двух списков в Excel зависит от структуры данных.
Сопоставление строк не лучше всего подходит, если данные не организованы.
ВПР — это часто используемая формула для сопоставления значений.