Как сравнить два столбца и выявить отсутствующие значения в Excel?
В Excel часто приходится работать с двумя связанными столбцами данных, чтобы проверить, присутствуют ли все значения из одного столбца в другом. Например, как показано на скриншоте ниже, столбец 1 может содержать полный список элементов, а столбец 2 — лишь его часть. В таких случаях выявление отсутствующих значений помогает поддерживать согласованность и точность данных. В этой статье описаны два эффективных способа сравнения двух столбцов и поиска недостающих значений в Excel — с помощью формул и VBA — чтобы вы могли быстро выбрать наиболее подходящий метод для своего рабочего процесса.
- Сравните два столбца и найдите отсутствующие значения с помощью формулы
- Сравните два столбца и найдите отсутствующие значения с помощью VBA
Сравните два столбца и найдите отсутствующие значения с помощью формулы
В Excel с помощью простой формулы легко проверить, присутствуют ли значения из одного столбца в другом. Этот приём помогает быстро выявить отсутствующие или несовпадающие записи и идеально подходит для проверки данных, очистки списков или подготовки таблиц к объединению.
1.Выберите пустую ячейку (например,)E2), куда нужно вывести результат сравнения. Введите следующую формулу и нажмите Enter:
=IF(COUNTIF(B:B,A2)=0,"Missing",A2)
Примечания:
2. После ввода формулы перетащите маркер заполнения вниз, чтобы применить её ко всем строкам. Новый столбец покажет, какие значения из столбца A есть в столбце B, а отсутствующие пометит как «Missing».
Преимущества и недостатки:
Преимущества: Простота и скорость настройки; чёткое выявление отсутствующих или несовпадающих данных.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Сравните два столбца и найдите отсутствующие значения с помощью VBA
Если вам часто нужно проверять наличие отсутствующих значений между двумя столбцами, макрос VBA легко автоматизирует этот процесс и эффективно справится даже с крупными наборами данных. Подход на основе VBA последовательно перебирает каждую ячейку и помечает записи, отсутствующие в столбце сравнения, экономя ваше время и минимизируя риски ошибок при ручной обработке.
Меры предосторожности и советы: Всегда сохраняйте книгу перед запуском VBA — макросы могут вносить необратимые изменения. Протестируйте работу на пробных данных, чтобы избежать ошибок, например случайной перезаписи исходных столбцов.
1. Перейдите на вкладку Разработчик и нажмите Visual Basic, чтобы открыть редактор Microsoft Visual Basic для приложений. Если вкладка «Разработчик» не отображается, ознакомьтесь с руководством: Отображение вкладки Разработчик в Excel.
2. В редакторе VBA выберите Вставка > Модуль, чтобы создать новый модуль. Затем вставьте следующий код в окно редактора:
Sub CompareColumnsFindMissing()
Dim ws As Worksheet
Dim rngA As Range, rngB As Range, c As Range
Set ws = ActiveSheet
Set rngA = ws.Range("A2:A100") 'Column to check
Set rngB = ws.Range("B2:B100") 'Reference column
For Each c In rngA
If Application.WorksheetFunction.CountIf(rngB, c.Value) = 0 Then
c.Offset(0, 2).Value = "Missing"
Else
c.Offset(0, 2).Value = c.Value
End If
Next c
End SubСовет:диапазоны ()"A2:A100" и "B2:B100") можно изменить в соответствии с размером вашего набора данных.
3. Нажмите F5 или кнопку Выполнить, чтобы запустить макрос. Код выведет результаты в столбец 
C , указав совпадающие значения или отобразив «Missing» для тех, которые не найдены в столбце B.
Преимущества и недостатки:
Преимущества: Автоматизация сравнения больших наборов данных; стабильные и точные результаты.
Недостатки: Требуется включить макросы и иметь базовые навыки работы с VBA; не подходит для быстрых разовых проверок.
См. также:
- Как подсчитать ячейки, соответствующие либо X, либо Y в Excel?
- Как найти совпадающее значение при поиске вверх в Excel?
- Как в Excel сопоставлять даты, учитывая только месяц и год?
- Как проверить, содержится ли значение ячейки в заданном списке в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек