Как сравнить два списка в Excel? (6 лучших методов)
Ниже указаны 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» . В этих случаях все наши формулы и инструменты по умолчанию вообще не распознаются. В этих случаях нам нужно использовать специальный символ звездочки (*) для сопоставления частичных значений.
В списке 1 указаны название компании и сведения о доходах. В Списке 2 указаны названия компаний, но нет точных значений, как в Списке 1. Это непростая ситуация, с которой мы все столкнулись на своем рабочем месте.
В таких случаях мы можем сопоставить данные с помощью специального символа звездочки (*).
Получаем следующий результат.
Теперь перетащите формулу в ячейку E9, чтобы определить другие значения,
Подстановочный знак звездочка (*) использовался для обозначения любого количества символов, поэтому он будет соответствовать полному символу слова «ABC» как «ABC Pvt Ltd».
То, что нужно запомнить
- Использование описанных выше методов сравнения двух списков в Excel зависит от структуры данных.
- Сопоставление строк не лучше всего подходит, если данные не организованы.
- ВПР - это часто используемая формула для сопоставления значений.