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