Как сопоставить два списка в Excel? (с внедрением 6 наилучших способов)
Как сопоставить два списка в Excel? (с внедрением 6 наилучших способов)
Как сопоставить два списка в Excel? (6 наилучших способов)
Ниже указаны 6 разных способов, которые употребляются для сопоставления 2-ух списков столбца в Excel на предмет совпадений и различий.
- Способ 1. Сравните два списка при помощи оператора знака равенства
- Способ 2: сравнение данных при помощи способа различия строк
- Способ 3: сравнение различия строк при помощи условия ЕСЛИ
- Способ 4: сравнение данных, даже если есть разница в строчках
- Способ 5: выделите все совпадающие данные при помощи условного форматирования
- Способ 6: Техника частичного сравнения
Сейчас давайте тщательно обсудим любой из способов на примере —
Вы сможете скачать этот шаблон Excel для сопоставления 2-ух списков тут — Шаблон Excel для сопоставления 2-ух списков
# 1 Сравните два списка при помощи оператора знака равенства
- Шаг 1. В последующий столбец сходу опосля 2-ух столбцов вставьте новейший столбец под заглавием «Статус».
- Шаг 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, мы вправду можем выделить все совпадающие данные 2-ух списков.
- Шаг 1: Для этого поначалу изберите данные.
- Шаг 2: Сейчас перейдите к «Условному форматированию» и изберите «Выделить правила ячеек >> Повторяющиеся значения».
- Шаг 3: Сейчас вы увидите окно форматирования «Повторяющиеся значения ячеек».
- Шаг 4: Практически вы сможете избрать различные цвета форматирования из раскрывающегося списка в Excel. Изберите 1-ый цвет форматирования и нажмите клавишу ОК.
- Шаг 5: Это выделит все совпадающие данные из 2-ух списков.
- Шаг 6: На всякий вариант, заместо выделения всех совпадающих данных, если вы желаете выделить несоответствующие данные, тогда в окне «Повторяющиеся значения» изберите вариант «Неповторимый».
Это выделит все несовпадающие значения.
# 6 Техника частичного соответствия
Мы лицезрели делему неполных либо схожих данных в 2-ух перечнях. К примеру, если в данных списка 1 обозначено «ABC Pvt Ltd», а в перечне 2 — лишь «ABC» . В этих вариантах все наши формулы и инструменты по дефлоту совершенно не распознаются. В этих вариантах нам необходимо применять особый знак звездочки (*) для сравнения частичных значений.
В перечне 1 указаны заглавие компании и сведения о доходах. В Перечне 2 указаны наименования компаний, но нет четких значений, как в Перечне 1. Это сложная ситуация, с которой мы все столкнулись на собственном рабочем месте.
В таковых вариантах мы можем сравнить данные при помощи специального знака звездочки (*).
Получаем последующий итог.
Сейчас перетащите формулу в ячейку E9, чтоб найти остальные значения,
Подстановочный символ звездочка (*) употреблялся для обозначения хоть какого количества знаков, потому он будет соответствовать полному символу слова «ABC» как «ABC Pvt Ltd».
Сравнить 2 списка в excel
Вот два списка имен, сейчас я желаю сопоставить эти два списка, а потом добавить недостающие имена в List2, как показано ниже.
Сравните два списка и добавьте пропущенные значения при помощи формулы ИНДЕКС
Тут формула ИНДЕКС дозволяет ассоциировать два списка, а потом добавлять недостающие данные в конец наиболее недлинного.
В ячейке под нижней частью наиболее недлинного списка введите эту формулу
В формуле A2: A11 — наиболее длиннющий перечень, а C2: C7 — наиболее маленький перечень.
Потом нажмите Shift + Ctrl + Enter клавиши, чтоб добавить 1-ые отсутствующие данные, потом перетащите дескриптор автозаполнения вниз, чтоб заполнить все отсутствующие данные, пока не покажется значение ошибки # Н / Д.
И вы сможете удалить значение # N / A опосля наполнения всех пропущенных значений.
Отыскать недостающие порядковые номера в перечне Excel
Сравните два списка и добавьте недостающие значения при помощи комфортного инструмента сопоставления
Если у вас есть Kutools for Excel установлен в Excel, вы сможете применять Изберите схожие и различные ячейки Утилита для резвого поиска недостающих данных меж 2-мя перечнями, а потом их копирования и вставки в наиболее маленький перечень.
Опосля бесплатная установка Kutools for Excel, сделайте последующее:
1. Нажмите Kutools > Изберите > Изберите схожие и различные ячейки. Смотрите снимок экрана:
2. Потом в Изберите схожие и различные ячейки В диалоговом окне «Отыскать значения в» изберите наиболее длиннющий перечень, потом в разделе «Согласно» изберите наиболее маленький перечень, потом установите флажки «Любая строчка» и «Разные значения».
3. Нажмите Ok > OK, сейчас недостающие данные в наиболее маленьком перечне выделены, нажмите Ctrl + C скопировать их. Потом изберите ячейку понизу наиболее недлинного списка и нажмите Ctrl + V чтоб вставить эти недостающие данные.
Эталон файла
Остальные операции (статьи)
Сравните примыкающие ячейки с наборами значков условного форматирования в Excel
Когда вы сравниваете два столбца данных, вы желаете применять наборы значков условного форматирования, дающие зрительное представление сопоставления. К примеру, если данные в столбце B больше, чем в столбце A, покажется значок стрелки ввысь; если столбец B меньше столбца A, покажется значок стрелки вниз; либо если столбцы B и A равны, будет отображаться значок стрелки на право.
Сравните даты, если они больше иной даты в Excel
Представим, у вас есть перечень дат и вы желаете сопоставить эти даты с обозначенной датой, чтоб выяснить дату, которая больше обозначенной даты в перечне, что бы вы сделали? В данной нам статье мы покажем для вас способы сопоставления дат, если они больше иной даты в Excel.
Асоциируйте листы Excel рядом
Если в книжках есть два либо наиболее листа, которые нужно сопоставить вместе, для вас быть может неприятно переключать их один за остальным для сопоставления. Тут я расскажу о приемах сопоставления листов по горизонтали либо вертикали.
Сравните, если несколько ячеек равны в Excel
Как мы все знаем, для сопоставления, если две ячейки равны, мы можем применять формулу A1 = B1. Но если вы желаете проверить, имеют ли несколько ячеек одно и то же значение, эта формула не будет работать. Сейчас я расскажу о неких формулах для сопоставления, если несколько ячеек равны в Excel.
Сравните два списка в Excel
Как сопоставить два списка в Excel? (6 наилучших способов)
Ниже перечислены 6 разных способов, которые употребляются для сопоставления 2-ух списков столбца в Excel на предмет совпадений и различий.
- Способ 1. Сравните два списка при помощи оператора знака равенства
- Способ 2: сравнение данных при помощи способа различия строк
- Способ 3: сравнение различия строк при помощи условия ЕСЛИ
- Способ 4: сравнение данных, даже если есть разница в строчках
- Способ 5: выделите все совпадающие данные при помощи условного форматирования
- Способ 6: Техника частичного сравнения
Сейчас давайте тщательно обсудим любой из способов на примере —
# 1 Сравните два списка при помощи оператора знака равенства
- Шаг 1: В последующем столбце сходу опосля 2-ух столбцов вставьте новейший столбец под заглавием «Статус».
- Шаг 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, мы вправду можем выделить все совпадающие данные 2-ух списков.
- Шаг 1: Для этого поначалу изберите данные.
- Шаг 2: Сейчас перейдите в «Условное форматирование» и изберите «Выделить правила ячеек >> Повторяющиеся значения».
- Шаг 3: Сейчас вы увидите окно форматирования «Повторяющиеся значения ячеек».
- Шаг 4: Практически вы сможете избрать различные цвета форматирования из раскрывающегося списка в Excel. Изберите 1-ый цвет форматирования и нажмите клавишу ОК.
- Шаг 5: Это выделит все совпадающие данные из 2-ух списков.
- Шаг 6: На всякий вариант, заместо выделения всех совпадающих данных, если вы желаете выделить несоответствующие данные, тогда в окне «Повторяющиеся значения» изберите вариант «Неповторимый».
Это выделит все несовпадающие значения.
№6 Техника частичного соответствия
Мы лицезрели делему неполных либо схожих данных в 2-ух перечнях. К примеру, если данные списка 1 имеют «ABC Pvt Ltd» а в перечне 2 мы имеем «ABC» лишь. В этих вариантах все наши формулы и инструменты по дефлоту совершенно не распознаются. В этих вариантах нам необходимо применять звездочку специального знака
(*2*)
В перечне 1 указаны заглавие компании и сведения о доходах. В перечне 2 у нас есть наименования компаний, но нет четких значений, как в перечне 1. Это сложная ситуация, с которой мы все столкнулись на собственном рабочем месте.
Получаем последующий итог.
Сейчас перетащите формулу в ячейку E9, чтоб найти остальные значения,
Сравните два списка в Excel, пример 6.3.0
- Подстановочный символ звездочка
- был применен для представления хоть какого количества знаков, потому он будет соответствовать полному символу слова «ABC» как «ABC Pvt Ltd.»
- То, что необходимо уяснить
Внедрение обрисованных выше способов сопоставления 2-ух списков в Excel зависит от структуры данных.
Сравнение строк не идеальнее всего подступает, если данные не организованы.
