В статье показано, как использовать формулу ВПР, чтобы найти совпадения или различия в двух столбцах Excel. При этом мы получим список совпадающих или отличающихся значений.
Если у вас есть данные в двух разных списках, вам часто может потребоваться сравнить их, чтобы увидеть, какая информация отсутствует в одном из них или какие данные присутствуют в обоих. Чтобы сравнить столбцы в Excel на совпадения или различия, можно использовать разные методы. Какой их них лучше использовать, зависит от того, какой именно результат вы хотите получить.
Как найти совпадения в двух столбцах с помощью ВПР
Если у вас есть два столбца данных и вы хотите узнать, какие значения из одного из них присутствуют в другом, вы можете использовать функцию ВПР для сравнения этих списков на предмет совпадений.
Чтобы создать формулу ВПР, вам нужно сделать следующее:
- Для искомого_значения (1-й аргумент) используйте самую верхнюю ячейку из списка 1.
- Для table_array (2-й аргумент) укажите весь список 2.
- Для col_index_num (3-й аргумент) используйте 1, поскольку мы рассматриваем только один столбец.
- Для range_lookup (4-й аргумент) установите ЛОЖЬ или 0 — точное совпадение.
Предположим, у вас есть имена всех сотрудников в столбце А (Список 1) и имена прослушавших курс маркетинга в столбце В (Список 2). Вы хотите сопоставить эти два списка, чтобы определить, какие участники из группы А теперь являются более квалифицированными специалистами. Для этого используйте следующую формулу.
=ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ)
Записываем ее в ячейку E2, а затем копируем вниз на столько ячеек, сколько сотрудников находится у нас в списке 1.
Обратите внимание, что параметр таблица зафиксирован абсолютными ссылками ($C$2:$C$9), поэтому адрес этот остается неизменным при копировании формулы.
Как видите, имена прослушавших курс отображаются в столбце E. Для остальных участников появляется ошибка #Н/Д, указывающая, что их имена не найдены и потому недоступны в Списке 2.
Скрываем сообщения об ошибке #Н/Д
Рассмотренная выше формула ВПР прекрасно выполняет свою основную задачу — возвращает общие значения в двух списках и указывает на их отличия. Однако она выделяет отличия при помощи ошибок #Н/Д, которые могут сбить с толку неопытных пользователей, заставив их подумать, что с формулой что-то не так.
Чтобы заменить ошибки пустыми ячейками , используйте ВПР в сочетании с функцией ЕСЛИОШИБКА или ЕСНД следующим образом:
=ЕСНД(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ); "")
или
=ЕСЛИОШИБКА(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ); "")
Наша улучшенная формула возвращает пустую строку ("") вместо #Н/Д. Вы также можете вернуть свой собственный текст, например «Нет в списке», «Нет» или «Отсутствует». Например:
=ЕСЛИОШИБКА(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ); "Отсутствует")
Это базовая формула ВПР для сравнения двух столбцов в Excel на совпадения. В зависимости от вашей конкретной задачи ее можно изменить, как показано в примерах ниже.
Сравнить два столбца в разных листах Excel.
В реальной жизни столбцы, которые нужно сравнить, не всегда находятся на одном листе. В небольшом наборе данных вы можете попробовать обнаружить совпадения и различия вручную, просматривая два листа рядом. Для этого выберите на ленте меню Вид, затем – Упорядочить все. Во всплывающем окне выберите чекбокс Рядом.
Для поиска на другом листе или в другой книге при помощи формул необходимо использовать внешнюю ссылку. Лучше всего начать вводить формулу на основном листе, затем переключиться на другой лист и выбрать нужные ячейки при помощи мыши. В формуле автоматически будет добавлена соответствующая внешняя ссылка на диапазон. Так вы избежите ненужных ошибок, вводя имя книги и листа вручную.
Предполагая, что общий список находится в столбце A на листе Лист 1 , а список 2 — в столбце A на листе Лист 2 , вы можете сравнить эти два столбца и найти совпадения, используя эту формулу:
=ЕСНД(ВПР(A2; 'Лист 2'!$A$2:$A$9; 1; ЛОЖЬ); "")
Возвращаем совпадения в двух столбцах Excel
В предыдущих примерах мы обсуждали формулу ВПР в ее простейшей форме:
=ЕСНД(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ); "")
Результатом этой формулы является список значений, среди которых есть и пустые вместо значений, не найденных во втором столбце.
Чтобы получить список совпадающих значений обоих столбцов без пробелов между ними, достаточно применить к полученному столбцу автофильтр и отфильтровать пустые ячейки.
В Excel для Microsoft 365 и Excel 2021, поддерживающих динамические массивы, вы можете использовать функцию ФИЛЬТР для динамического отсеивания пробелов. Для этого используйте формулу ЕСНД ВПР в качестве критерия в функции ФИЛЬТР:
=ФИЛЬТР(A2:A14;ЕСНД(ВПР(A2:A14; $C$2:$C$9; 1; ЛОЖЬ); "")<>"")
Обратите внимание, что в этом случае мы передаем весь список 1 (A2:A14) в аргумент искомое_значениефункции ВПР. Функция сравнивает каждое из искомых значений со списком 2 (C2:C9) и возвращает массив совпадений и ошибок #Н/Д, представляющих несовпадающие значения. Функция ЕСНД заменяет ошибки пустыми строками и передает результаты функции ФИЛЬТР, которая отфильтровывает пустоты (<>"") и выводит массив совпадений в качестве окончательного результата.
Чтобы сформировать список несовпадающих значений (то есть, в нашем случае людей, которые не прослушали курс), просто поменяйте в формуле <> на =.
В качестве альтернативы вы можете использовать функцию ЕНД, чтобы проверить результат ВПР и отфильтровать элементы, оценивающие значение ЛОЖЬ, т. е. получить все значения, кроме ошибок #Н/Д:
=ФИЛЬТР(A2:A14;ЕНД(ВПР(A2:A14; $C$2:$C$9; 1; ЛОЖЬ))=ЛОЖЬ)
Того же результата можно добиться с помощью функции ПРОСМОТРX, которая еще больше упрощает формулу. Благодаря способности ПРОСМОТРX самостоятельно обрабатывать ошибки #Н/Д(необязательный аргумент если_ничего_не_найдено), мы можем обойтись без обертывания формулы поиска в ЕСНД или ЕНД:
=ФИЛЬТР(A2:A14; ПРОСМОТРX(A2:A14; C2:C9; C2:C9;"")<>"")
Сравните два столбца и найдите различия
Чтобы сравнить два столбца в Excel и найти различия, вы можете сделать следующие шаги:
- Напишите основную формулу для поиска первого значения из списка 1 (A2) в списке 2 ($C$2:$C$9):
ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ)
- Вложите приведенную выше формулу в функцию ЕНД, чтобы проверить результат формулы ВПР на наличие ошибок #Н/Д. В случае ошибки ЕНД выдает ИСТИНА, в противном случае – ЛОЖЬ:
ЕНД(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ))
- Используйте полученную в шаге 2 формулу как логическое условие в функции ЕСЛИ. Если результат теста равен ИСТИНА (то есть получена ошибка #Н/Д), верните значение из списка 1, которое находится в той же строке. Если результат теста равен ИСТИНА (обнаружено совпадение в списке 2), верните пустую строку.
Полная формула принимает такой вид:
=ЕСЛИ(ЕНД(ВПР(A2; $C$2:$C$9; 1; ЛОЖЬ)); A2; "")
Чтобы избавиться от пустых ячеек в столбце, примените фильтр Excel, как мы уже описали ранее.
В Excel 365 и Excel 2021 список результатов можно фильтровать динамически. Для этого просто поместите формулу ЕНД ВПР в аргумент условие функции ФИЛЬТР:
=ФИЛЬТР(A2:A14; ЕНД(ВПР(A2:A14; C2:C9; 1; ЛОЖЬ)))
Еще один способ — использовать ПРОСМОТРX (XLOOKUP) для критериев: функция возвращает пустые строки («») для найденных различий, и вы фильтруете значения в списке 1, для которых ПРОСМОТРX возвращал пустые строки («»):
=ФИЛЬТР(A2:A14; ПРОСМОТРX(A2:A14; C2:C9; C2:C9;"")="")
Выделить совпадения и различия данных между двумя столбцами
Предположим, вы хотите выделить совпадения в двух столбцах Excel при помощи текстовых меток, указывающих, какие значения доступны во втором списке, а какие нет. Используйте формулу ВПР вместе с функциями ЕСЛИ и ЕСНД/ЕОШИБКА.
Например, чтобы найти имена, которые находятся в обоих столбцах A и D, а также выделить имена, которые записаны только в столбце A, используется формула:
=ЕСЛИ(ЕНД(ВПР(A2; $D$2:$D$9; 1; ЛОЖЬ)); "Нет"; "Да")
Здесь функция ЕНД определяет ошибки #Н/Д, полученные от функции ВПР, и передает этот промежуточный результат функции ЕСЛИ, чтобы она возвращала нужный текст для ошибок и какой-то другой текст, если найдено совпадение.
В этом примере мы использовали сообщения «Да»/«Нет». Вы можете заменить их на и любые другие слова, которые вы считаете наиболее подходящими.
Эту формулу лучше всего вставить в столбец, соседний со списком 1, и скопировать вниз на столько ячеек, сколько имеется элементов в вашем основном списке.
Еще один способ сравнить столбцы на совпадения и различия — использовать функцию ПОИСКПОЗ :
=ЕСЛИ(ЕНД(ПОИСКПОЗ(A2; $D$2:$D$9; 0)); "Нет"; "Да")
Как сравнить два столбца и вернуть значение из третьего
При работе с таблицами, содержащими связанные данные, иногда может потребоваться сравнить два столбца из двух разных таблиц и вернуть соответствующее значение из другого столбца. Фактически, это основное использование функции ВПР, та цель, для которой она была разработана.
Например, чтобы сравнить имена в столбцах A и D в двух таблицах и в случае совпадения значений вернуть оценку из столбца E, используйте формулу:
=ЕСНД(ВПР(A3; $D$3:$E$10; 2; ЛОЖЬ); "")
Пример вы можете видеть на скриншоте ниже.
Чтобы скрыть ошибки #Н/Д, используем уже проверенное решение — функцию ЕСНД.
Вместо пробелов вы можете вернуть любой текст для отсутствующих значений — просто введите его в последнем аргументе. Например:
=ЕСНД(ВПР(A3; $D$3:$E$10; 2; ЛОЖЬ); "Отсутствует")
Помимо ВПР, эту задачу можно выполнить с помощью нескольких других функций поиска.
Лично я бы полагался на более гибкую формулу ИНДЕКС-ПОИСКПОЗ :
=ЕСНД(ИНДЕКС($E$3:$E$10; ПОИСКПОЗ(A3; $D$3:$D$10; 0)); "")
Или используйте современную версию ВПР — функцию ПРОСМОТРX, доступную в Excel 365 и Excel 2021:
=ПРОСМОТРX(A3; $D$3:$D$10; $E$3:$E$10; "")
Инструменты, которые помогут найти совпадение значений в столбцах Excel
Если вы часто сравниваете файлы или данные в Excel, эти умные инструменты, включенные в Ultimate Suite , могут значительно сэкономить ваше время!
Сравнить таблицы — быстрый способ найти дубликаты (совпадения) и уникальные значения (разницы) в любых двух наборах данных, таких как столбцы, списки или таблицы.
Сравнить два листа — найдите и выделите различия между двумя листами.
Надеюсь, эти рекомендации помогут вам быстро сравнить два столбца в Excel и найти все совпадения и различия между ними.
