Как использовать ВПР (VLOOKUP) для сравнения двух списков, расположенных на разных листах?
Предположим, у вас есть два листа, каждый из которых содержит список имён, как показано на скриншотах выше. Возможно, вы захотите проверить, какие имена из Names-1 также присутствуют в Names-2. Выполнение такого сравнения вручную — особенно при работе с длинными списками — может быть утомительным и крайне подверженным ошибкам. В этой статье представлены несколько эффективных методов, которые помогут вам быстро и точно сравнить два списка и найти совпадающие значения на разных листах.
Сравнение двух списков на отдельных листах с помощью функции ВПР (VLOOKUP) и формул
Сравнение двух списков на отдельных листах с помощью ВПР (VLOOKUP) и Kutools для Excel
Использовать условное форматирование с формулой между листами
Код VBA — автоматическое сравнение списков и выделение или извлечение совпадений
Сравнение двух списков на отдельных листах с помощью функции ВПР (VLOOKUP) и формул
Один из самых практичных и простых способов сравнить списки с разных листов Excel — использовать функцию ВПР (VLOOKUP). Этот метод позволяет быстро находить и выделять все имена, присутствующие одновременно в Names-1 и Names-2:
1. На листе Names-1выберите ячейку рядом со своим списком данных (например, ячейку)B2) и введите следующую формулу:
=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)Затем нажмите клавишу Enter. Если имя в текущей строке есть в Names-2, формула вернёт это имя; если нет — появится ошибка #Н/Д. Пример ниже:
2. Скопируйте формулу вниз, перетащив маркер заполнения, чтобы сравнить каждое имя из Names-1 со всеми именами из Names-2. Совпадающие записи покажут имя, а не найденные — значение ошибки:
Примечания:
1. Для большей наглядности можно использовать следующую альтернативную формулу, которая возвращает индикаторы «Да» или «Нет» в зависимости от наличия совпадений:
=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")Эта формула отображает «Да» для имен, присутствующих на обоих листах, и «Нет» для имен, найденных только в Names-1:
2. При использовании этих формул замените A2 на первую ячейку вашего списка, Names-2 — на имя эталонного листа, а также скорректируйте $A$2:$A$19, чтобы диапазон соответствовал фактическому диапазону данных на вашем листе. Помните: диапазоны должны охватывать ровно столько строк, сколько необходимо для включения всех ваших данных.
3. Советы по использованию: Если вы видите ошибки #Н/Д там, где должны быть совпадения, внимательно проверьте возможные причины: лишние пробелы, различия в форматировании данных (текст вместо чисел) или опечатки в списках. При необходимости используйте функции СЖПРОБЕЛЫ (TRIM) или ПЕЧСИМВ (CLEAN) во вспомогательном столбце для очистки данных.
4. Чтобы избежать случайной перезаписи, рекомендуется создать резервную копию данных перед массовым применением формул. Кроме того, после сравнения вы можете воспользоваться фильтром Фильтр в столбце с результатами формулы, чтобы быстро просмотреть все совпадения или уникальные элементы.
Сравнение двух списков на отдельных листах с помощью ВПР (VLOOKUP)
Если у вас установлен Kutools для Excel, с помощью его функции Выбрать одинаковые/разные ячейки вы сможете найти и выделить одинаковые или разные значения из двух отдельных листов всего за несколько щелчков. Эта функция значительно снижает риск ошибок при ручной обработке и экономит массу времени, особенно при работе с большими объемами данных.Нажмите, чтобы скачать Kutools для Excel!
Kutools для Excel: более чем 300 удобных надстроек для Excel, бесплатная пробная версия без ограничений на 30 дней.Скачайте и начните бесплатный пробный период прямо сейчас!
Сравнение двух списков на отдельных листах с помощью ВПР (VLOOKUP) и Kutools для Excel
Если у вас установлен Kutools для Excel, его функция Выбрать одинаковые/разные ячейки поможет быстро сравнить два списка с разных листов и выделить общие имена между ними — без необходимости вводить сложные формулы. Этот метод особенно эффективен при работе с большими объёмами данных или когда нужен наглядный цветовой результат, который легко интерпретировать с первого взгляда.
После установки Kutools для Excelвыполните следующие шаги, чтобы легко сравнить ваши списки:
1. Перейдите на вкладку Kutools, затем нажмите Выделить > Выбрать одинаковые/разные ячейки, как показано ниже:
2. В открывшемся диалоговом окне Выбрать одинаковые/разные ячейки:
(1.) В разделе Искать значения ввыберите диапазон из листа Names-1, который нужно сравнить;
(2.) В разделе Сравнивать свыберите диапазон из листа Names-2, с которым выполняется сравнение;
(3.) В разделе На основевыберите пункт Каждая строка, чтобы сравнивать строки соответственно;
(4.) В разделе Найтивыберите Одинаковые значения, чтобы выявить и выделить совпадающие имена;
(5.) При необходимости задайте фон или цвет шрифта, чтобы визуально выделить результаты и сделать совпадения более заметными.
3. Нажмите кнопку Ok, и вы увидите окно с информацией о количестве найденных и выделенных совпадающих ячеек. Все имена, присутствующие в обоих списках, будут выделены и визуально подчеркнуты — это упрощает дальнейший анализ или редактирование:
Нажмите, чтобы скачать Kutools для Excel и сразу начать бесплатный пробный период!
Практические советы: Если ваши листы содержат большие объёмы данных, используйте функцию фильтрации после выделения — так вы сможете быстро просмотреть только совпадающие строки. Кроме того, перед запуском сравнения обязательно убедитесь, что диапазоны выделены правильно и не включают заголовки (если это не требуется), поскольку их наличие может исказить результаты.
В редких случаях, если функция не возвращает ожидаемые результаты, убедитесь, что оба списка отформатированы одинаково (например, оба как текст и без скрытых пробелов в начале или конце), поскольку различия в форматировании могут привести к пропуску совпадений.
Использовать условное форматирование с формулой между листами
Если вы не хотите использовать формулы в столбцах и не планируете устанавливать надстройки, воспользуйтесь функцией Использовать условное форматирование с пользовательской формулой, чтобы визуально выявить совпадающие имена на одном листе на основе данных другого листа. Этот метод прост, не требует VBA и не создаёт отдельного списка результатов — он просто выделяет совпадения для быстрого визуального анализа.
Применимые сценарии: Это решение идеально подходит пользователям, которым нужен ненавязчивый визуальный индикатор совпадающих значений и которые не хотят изменять структуру листа. Ограничение заключается в том, что правила условного форматирования не могут напрямую ссылаться на другую книгу, а межлистовые ссылки в формулах работают только в пределах одного файла.
Шаги:
1. На листе Names-1выделите диапазон, к которому вы хотите применить выделение (например,)A2:A19).
2. Перейдите в меню Главная > Условное форматирование > Создать правило > Использовать формулу для определения форматируемых ячеек.
3. Введите в поле формулы следующую формулу:
=COUNTIF('Names-2'!$A$2:$A$19,A2)>0Эта формула проверяет, существует ли значение из ячейки A2 листа Names-1 где-либо в диапазоне Names-2!A2:A19.
4. Нажмите Формат, чтобы выбрать цвет выделения, затем нажмите ОК, чтобы применить правило. Все совпадения будут автоматически выделены в выбранном диапазоне.
Практические советы: Вы можете скорректировать диапазоны в соответствии с вашими фактическими данными, а шаг с функцией СЧЁТЕСЛИ — объединить с фильтрацией, чтобы сосредоточиться только на выделенных ячейках. Убедитесь, что оба листа находятся в одной и той же книге при настройке ссылок между ними: Excel не поддерживает правила условного форматирования, ссылающиеся на внешние файлы.
Напоминания об ошибках: Если выделение отображается некорректно, убедитесь, что диапазоны ячеек и межлистовые ссылки выбраны правильно. Проверьте, нет ли начальных или конечных пробелов, а также несоответствий форматов, из-за которых совпадения могут пропускаться. При необходимости используйте функцию СЖПРОБЕЛЫ (TRIM) во вспомогательном столбце, чтобы очистить списки и обеспечить точное сравнение.
Код VBA — автоматическое сравнение списков и выделение или извлечение совпадений
Для пользователей, знакомых с макросами, код VBA открывает высоко гибкий и автоматизированный способ сравнения двух списков на разных листах. Этот подход позволяет легко выделять совпадающие имена или переносить совпадающие значения в новое место — особенно полезно при работе с большими объемами данных или когда результаты нужно оперативно обновлять по мере изменения списков.
Применимые сценарии: Это решение особенно эффективно, когда нужно многократно выполнять сравнения, обрабатывать очень большие объемы данных, автоматизировать отчетность или гибко настраивать способ обработки и отображения совпадений. Хотя для его реализации требуются знания VBA, вы получаете полную автоматизацию и контроль над процессом. Главный недостаток — необходимость включения макросов в книге, что в некоторых средах может быть запрещено из-за политик безопасности.
Как запустить макрос для выделения совпадений в Names-1, если они присутствуют в Names-2:
1. Нажмите Инструменты разработчика > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. В открывшемся окне выберите Вставка > Модуль и вставьте следующий код в новый модуль:
Sub HighlightMatchingNames()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cell As Range
Dim matchFound As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws1 = Worksheets("Names-1")
Set ws2 = Worksheets("Names-2")
Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
For Each cell In rng1
Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not matchFound Is Nothing And cell.Value <> "" Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub2. В редакторе VBA нажмите кнопку 
для запуска кода. Этот макрос просканирует имена в столбце A листа «Names-1» и, если имя также встречается в столбце A листа «Names-2», выделит соответствующую ячейку на листе «Names-1» желтым цветом заливки. Все предыдущие выделения в указанном диапазоне будут удалены перед новым сравнением.
Устранение неполадок: Если ячейки не выделяются, убедитесь, что оба листа точно названы «Names-1» и «Names-2», а диапазон ваших данных начинается с ячейки A2. Проверьте, включены ли макросы, и убедитесь, что ни один из листов не защищён и не отфильтрован. Этот метод легко настраивается: вы можете изменить цвет выделения или адаптировать код для копирования найденных совпадений на другой лист или в другой столбец.
Итог и рекомендации: В зависимости от ваших потребностей и уровня технической подготовки вы можете выбрать встроенные решения на основе формул, автоматизацию с помощью макросов, интеллектуальные надстройки, такие как Kutools, или простую визуализацию с помощью условного форматирования. При использовании формул или VBA всегда проверяйте данные на наличие лишних пробелов или несогласованного форматирования — это частые причины ошибок. Создавайте резервную копию данных перед выполнением массовых изменений, особенно при первом использовании макросов или надстроек. Если возникают проблемы — например, формулы не обновляются или определяются неверные совпадения, — проверьте корректность использования относительных и абсолютных ссылок и убедитесь, что имя листа указано правильно. Выбрав метод, соответствующий вашему рабочему процессу, вы сможете эффективно и быстро сравнивать списки на разных листах в Excel.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек