Как сравнить два столбца в Excel с помощью ВПР

При работе с большими наборами данных в Excel может потребоваться сравнить два столбца, чтобы найти сходства или различия.

ВПР — это мощная функция, позволяющая искать совпадающие данные между двумя столбцами. Функция позволяет искать значения в одном столбце, которые появляются в другом.

В этой статье показано, как сравнивать два столбца с помощью функции ВПР, чтобы вы могли эффективно анализировать свои данные.

Оглавление

• Основы функции ВПР
  • Функция ВПР
• Подготовка ваших данных для ВПР
  • Форматирование данных
• Как использовать функцию ВПР для сравнения двух столбцов
  • Обработка других ошибок
• Альтернативы ВПР для сравнения столбцов
  • 1. Использование функций индекса и сопоставления
  • 2. Использование XLOOKUP в Excel
• Пять советов по улучшению производительности функции ВПР
  • 1. Ограничьте диапазон поиска
  • 2. Используйте абсолютные ссылки
  • 3. Сортируйте данные
  • 4. Используйте двойную ВПР
  • 5. Используйте условное форматирование
• Расширенные возможности использования функции ВПР
• Наше последнее слово

Основы функции ВПР

Предположим, у вас есть электронная таблица с двумя списками элементов в столбце A и столбце B. Вы хотите найти элементы в списке 1, которые также отображаются в списке 2.

Вы можете себе представить, что ручная работа со списками из тысяч элементов отнимет очень много времени. К счастью, Excel предлагает ВПР на помощь!

Термин ВПР означает вертикальный просмотр. Функция сравнивает два столбца, находит совпадения между ними и возвращает связанные значения.

Функция ВПР

Вот основной синтаксис функции ВПР:

=ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])

Это разбивка элементов:

• lookup_value : значение, которое вы хотите найти в первом столбце table_array.

• table_array : диапазон ячеек, содержащих данные, в которых вы хотите выполнить поиск.

• col_index_num : номер столбца в table_array, из которого вы хотите вернуть значение.

• range_lookup : необязательно. Это либо ИСТИНА (приблизительное совпадение), либо ЛОЖЬ (точное совпадение). Значение по умолчанию — ИСТИНА.

Подготовка ваших данных для ВПР

Прежде чем использовать функцию ВПР для сравнения двух столбцов в Excel , необходимо подготовить данные.

Создайте на листе два отдельных столбца, в которых вы хотите сравнить значения. В этой статье для наших примеров используются столбец A и столбец B.

Вот примеры данных, использованные в этой статье:

Это образец таблицы с заголовком, который нужно добавить в раздел «Подготовка данных для ВПР»:

Список 1 Список 2
Пункт 1 Пункт
3 Пункт 2
Пункт 4 Пункт 3 Пункт 1
Пункт 4 Пункт 5

Форматирование данных

Важно убедиться, что данные в обоих столбцах отформатированы одинаково. Функция ВПР чувствительна к регистру, а это означает, что важны заглавные и строчные буквы.

В конечном результате могут возникнуть ошибки сопоставления, если форматирование несовместимо.

Также рекомендуется удалить повторяющиеся значения или пустые ячейки, чтобы свести к минимуму риск ошибок.

Чтобы удалить повторяющиеся значения:

1. Выберите столбец.

2. Перейдите на вкладку «Данные» на верхней ленте.

3. Нажмите «Удалить дубликаты» в разделе «Инструменты данных».

Кнопку может быть немного трудно увидеть. Эта картинка поможет вам:

Чтобы удалить пустые ячейки:

1. Выберите столбец.

2. Перейдите на вкладку «Главная» на верхней ленте.

3. Разверните меню «Найти и выбрать».

4. Выберите «Перейти к специальному».

5. Выберите «Пробелы» из вариантов.

6. Нажмите «Удалить» в разделе «Ячейки».

Как использовать функцию ВПР для сравнения двух столбцов

После подготовки данных вы можете написать формулу ВПР для сравнения двух столбцов в Excel и выявления совпадений. Следуй этим шагам:

1. Выберите ячейку в новом столбце, где вы хотите отобразить результаты сравнения (например, ячейка C2).

2. Введите следующую формулу: =ВПР(A2, B:B, 1, ЛОЖЬ))

3. Нажмите Enter, чтобы применить формулу.

4. В случае совпадения значение будет отображаться в той же строке столбца результатов (например, столбец C).

5. Перетащите формулу вниз из C2, чтобы скопировать ее в нужное количество ячеек.

Если вы используете наши образцы данных, поместите результаты сравнения в третий столбец. Формула найдет три совпадения из четырех элементов в списке 1.

Обратите внимание, что элемент 2 отображается как #N/A. Excel сообщает нам, что совпадение неприменимо, т. е. не может быть найдено.

Это правильно и является полезной информацией об отсутствующих значениях. Однако некоторые пользователи Excel могут подумать, что есть какая-то проблема с данными или ошибка функции.

Хорошей практикой является отображение другого признака того, что совпадений не найдено. Это может быть просто пробел.

Для этого объедините функцию VLOOKUP с функцией IFNA следующим образом:

=ЕСЛИНА(ВПР(A1, B:B, 1, ЛОЖЬ), "")

Функция IFNA обнаруживает ошибку #N/A и заменяет вывод пустым пространством («»). Вы также можете использовать функцию ISNA или использовать дополнительную логику с функцией IF.

Обработка других ошибок

ВПР иногда может генерировать другие ошибки в результатах сравнения. #REF ! Ошибка в Excel — это настоящая проблема с вашими данными.

Обычно это происходит, когда указанный вами диапазон неверен. В нашем примере указан весь столбец, но вы также можете использовать вертикальные диапазоны ячеек.

Убедитесь, что диапазон поиска, на который вы ссылаетесь, охватывает все значения, которые вы хотите сравнить.

Альтернативы ВПР для сравнения столбцов

Существуют две основные альтернативные функции поиска при сравнении двух столбцов в Excel для поиска совпадений.

1. Использование функций индекса и сопоставления

Вместо использования ВПР вы можете сравнить два столбца в Excel, комбинируя функции ИНДЕКС и ПОИСКПОЗ.

Этот метод обеспечивает более гибкий способ поиска данных и особенно полезен при работе с несмежными столбцами или когда индекс столбца может измениться.

Это синтаксис для размещения в столбце результатов:

=ИНДЕКС(возврат_диапазон, ПОИСКПОЗ(искомое_значение, искомый_диапазон, 0))

• Return_range : диапазон ячеек, содержащих данные, которые вы хотите вернуть.

• Искомое_значение : значение, которое вы хотите найти в диапазоне поиска.

• Lookup_range : диапазон ячеек, в котором вы хотите найти искомое значение.

Используя те же данные, что и в предыдущих примерах, заменим формулу ВПР следующим образом:

=(ИНДЕКС(B:B, ПОИСКПОЗ(A2, B:B, 0)))

Функция ПОИСКПОЗ возвращает относительное положение искомого значения в диапазоне поиска, а функция ИНДЕКС возвращает соответствующее значение из возвращаемого диапазона.

Результаты для тех же данных будут такими же, как и при ВПР, которую мы использовали ранее. На этом рисунке показаны используемые функции:

Вы также можете заменить ошибку #N/A собственным сообщением. Вот пример использования функции ЕСЛИОШИБКА.

=ЕСЛИОШИБКА(ИНДЕКС(B:B, СОВПАДЕНИЕ(A2, B:B, 0)), "Не найдено")

2. Использование XLOOKUP в Excel

Для пользователей Excel 365 или Excel 2019 XLOOKUP — это альтернативный логический тест для VLOOKUP для поиска общих значений в двух столбцах.

XLOOKUP дает несколько преимуществ. Вы можете использовать его для поиска данных как по горизонтали, так и по вертикали, работать с несмежными столбцами и задавать пользовательские значения для ошибок.

Синтаксис XLOOKUP:

=XLOOKUP(искомое_значение, искомый_диапазон, возвращаемый_диапазон, [if_not_found], [match_mode], [search_mode])

• lookup_value : значение, которое вы хотите найти в диапазоне поиска.

• lookup_range : диапазон ячеек, в котором вы хотите найти искомое значение.

• return_range : диапазон ячеек, содержащих данные, которые вы хотите вернуть.

• При необходимости добавьте пользовательские значения ошибок, параметры режима соответствия и режима поиска.

XLOOKUP найдет искомое значение в пределах диапазона поиска и вернет соответствующее значение из возвращаемого диапазона.

Формула особенно полезна, когда у вас есть два списка, встроенных в наборы данных из нескольких столбцов. В наших предыдущих примерах списки находились в первом и втором столбцах, но на вашем листе может быть больше данных.

Вот пример с двумя списками в первом и третьем столбцах электронной таблицы:

Формула XLOOKUP выглядит следующим образом:

=XLOOKUP(A2,C:C,C:C,"")

На этом рисунке показан результат сравнения в столбце D. Первое значение присутствует, а второе отсутствует.

Обратите внимание, что у меня нет дополнительных формул ошибок, но отсутствующее значение отображается как пустое. Это потому, что я использую четвертый параметр, который является пользовательским значением ошибки. В данном случае это пустая строка.

В качестве бонуса я покажу вам, как использовать XLOOKUP для сравнения строк. Если у вас есть два списка в строке 1 и строке 2, простейшее использование формулы выглядит так:

=XПРОСМОТР(B1, 2:2, 2:2, "")

На этом рисунке показаны результаты для двух строк.

Благодаря улучшенной функциональности и гибкости XLOOKUP является отличной альтернативой VLOOKUP для сравнения списков в Excel для определения наличия совпадений.

Пять советов по улучшению производительности функции ВПР

Оптимизация функции ВПР поможет вам избежать длительного ожидания и повысить общую скорость отклика листа Excel.

Вот шесть советов и приемов, которые можно применить для повышения производительности функции ВПР в Excel.

1. Ограничьте диапазон поиска

Я использовал целые столбцы в приме��ах, чтобы сделать их простыми. Если вы работаете с большим объемом данных, этого следует избегать. Использование полных столбцов может замедлить процесс вычислений в Excel.

Вместо этого попробуйте использовать точный диапазон, необходимый для ваших данных (например, A1:A100). Это уменьшает количество ячеек, которые должна оценить ваша функция ВПР.

2. Используйте абсолютные ссылки

При указании диапазона данных (например, ячейки от B2 до B5) используйте абсолютные ссылки. Это гарантирует, что формула непротиворечива и данные существуют, когда вы копируете ее в несколько ячеек.

Вот пример:

=ВПР(A2, $B$2:$B$5, 1, ЛОЖЬ)

3. Сортируйте данные

Если вы знаете, что данные в столбце подстановки отсортированы в порядке возрастания, вы можете использовать ВПР с ИСТИНА или 1 для аргумента «диапазон_просмотра».

Это заставит Excel выполнить приблизительное совпадение, которое быстрее, чем точное совпадение во всех ячейках. Однако будьте осторожны при использовании этого параметра, так как неправильная сортировка может привести к неправильным результатам.

4. Используйте двойную ВПР

Вы можете использовать две функции ВПР, чтобы ускорить процесс поиска в Excel.

Первая функция ВПР определит, существует ли искомое значение, установив для параметра «col_index_num» значение 1, а для параметра «range_lookup» — значение «ИСТИНА».

Если он возвращает TRUE, вторая функция VLOOKUP извлекает желаемое значение с параметром range_lookup, установленным на TRUE.

5. Используйте условное форматирование

Вы можете использовать условное форматирование на листе Excel, чтобы выделить совпадающие или отсутствующие значения в указанном столбце. Вы также можете применять цвета к уникальным значениям. Это облегчает чтение ваших данных.

Вы найдете меню условного форматирования в группе стилей на вкладке Главная.

Расширенные возможности использования функции ВПР

Парные выборки Т-тесты используются для сравнения средних значений двух связанных выборок. В этом видео показано их использование в Excel.

Если у вас есть дополнительные данные или переменные, на которые нужно ссылаться во время анализа, вы можете использовать функцию ВПР для извлечения необходимых значений из другой таблицы или рабочего листа.

Например, вы можете использовать ВПР для получения демографической информации или условий лечения для каждого парного наблюдения в вашем наборе данных.

Наше последнее слово

К настоящему времени у вас есть четкое представление об использовании функции ВПР. Эта мощная функция позволяет быстро находить различия и совпадающие значения между двумя списками, делая анализ данных более эффективным и точным.

Вы также видели примеры других функций поиска и ссылок, таких как XLOOKUP, INDEX и MATCH. Добавление этих элементов к вашим навыкам еще больше расширит ваши возможности анализа данных.

Продолжайте практиковаться и совершенствовать свои навыки ВПР, и вскоре вы станете экспертом в сравнении столбцов в Excel, экономя время и повышая производительность.