Как сравнить два столбца в excel на совпадения впр

Как сопоставить два столбца и вернуть третий в Excel

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

К счастью, это легко сделать с помощью функции ВПР() , которая использует следующий синтаксис:

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

• lookup_value : значение, которое вы хотите найти.
• table_array : диапазон ячеек для просмотра.
• col_index_num : номер столбца в диапазоне, который содержит возвращаемое значение.
• range_lookup : найти приблизительное совпадение (по умолчанию) или точное совпадение.

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

Пример: сопоставление двух столбцов и возврат третьего в Excel

Предположим, у нас есть следующие наборы данных в Excel:

Предположим, мы хотим сопоставить значения команды в столбце A и столбце D и вернуть значения очков в столбце B в столбец E.

Мы можем использовать следующий синтаксис ВПР, чтобы сопоставить первое значение в столбце А:

=VLOOKUP( D2 , $A$2:$B$16 , 2, FALSE) 

На следующем снимке экрана показано, как использовать этот синтаксис на практике:

Обратите внимание, что значение «points» в столбце B, которое соответствует «Suns», равно 96 , поэтому это значение возвращается в столбце E.

Затем мы можем перетащить эту формулу в каждую оставшуюся ячейку в столбце E:

Примечание.Полную документацию по функции ВПР можно найти здесь .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

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

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

=ISNA(VLOOKUP( A2 , $C$2:$C$9 ,1,False)) 

Используя инструмент условного форматирования в Excel, мы можем использовать эту формулу, чтобы выделить каждое значение в столбце A, которое не принадлежит диапазону в столбце C.

В следующем примере показано, как использовать эту формулу на практике.

Пример. Сравнение двух списков с использованием функции ВПР

Предположим, у нас есть следующие два набора данных в Excel:

Предположим, мы хотим идентифицировать команды в наборе данных 1, которых нет в наборе данных 2.

Для этого мы можем выделить каждое значение в столбце A, а затем нажать кнопку « Условное форматирование » на вкладке « Главная » на верхней ленте.

Затем мы можем нажать Новое правило…

В появившемся новом окне выберите параметр « Использовать формулу, чтобы определить, какие ячейки следует форматировать », а затем введите следующую формулу:

=ISNA(VLOOKUP( A2 , $C$2:$C$9 ,1,False)) 

Затем нажмите кнопку « Формат» и выберите цвет для заполнения значений:

После того, как вы нажмете OK , каждое значение в столбце A, которое не отображается в столбце C, будет выделено:

Мы можем вручную проверить правильность выделения нескольких значений:

• Ястребы появляются как в наборе данных 1, так и в наборе данных 2, поэтому они не выделены.
• Mavericks отображаются как в наборе данных 1, так и в наборе данных 2, поэтому они не выделены.
• «Лейкерс » появляются в наборе данных 1, но отсутствуют в наборе данных 2, поэтому он выделен .

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

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Совет: Попробуйте использовать новые функции XLOOKUP и XMATCH , улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их проще и удобнее в использовании, чем их предшественники.

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

Функции ВПР и HLOOKUP , а также INDEX и MATCH являются одними из наиболее полезных функций в Excel.

Примечание: Функция мастера подстановки больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

=ВПР(B2;C2:E7,3,ИСТИНА)

В этом примере B2 является первым аргументом — элементом данных, который должен работать функции. Для ВПР этот первый аргумент является значением, которое требуется найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например smith или 21000. Второй аргумент — это диапазон ячеек C2-:E7, в котором выполняется поиск нужного значения. Третий аргумент — это столбец в диапазоне ячеек, содержащий нужное значение.

Четвертый аргумент необязателен. Введите TRUE или FALSE. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым или ввести TRUE, вы получаете большую гибкость.

В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) ВПР выполняет поиск ячеек в диапазоне C2:E7 (2-й аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбца E (3-й аргумент).

Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

Если вы знакомы с VLOOKUP, функция HLOOKUP также проста в использовании. Вы вводите те же аргументы, но выполняется поиск по строкам, а не по столбцам.

Использование INDEX и MATCH вместо ВПР

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

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования INDEX и MATCH вместо ВПР см. в статье , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билл Джелен (Bill Jelen), Microsoft MVP.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функциями подстановки, прежде чем опробовать их с собственными данными, вот некоторые примеры данных.

Пример ВПР на работе

Скопируйте следующие данные в пустую электронную таблицу.

Совет: Перед вставкой данных в Excel задайте ширину столбцов от A до C равным 250 пикселям и нажмите кнопку Обтекать текст (вкладка Главная , группа Выравнивание ).

Как в Эксель сравнить два столбца на совпадения и найти расхождения

Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.

Как сравнить столбцы в Эксель

Одна из особенностей приложения — возможность в Эксель сравнить столбцы (два и более) на факт отличий и различий, а после вывести результаты в виде подсвечивания цветом. Ниже рассмотрим, как правильно сделать эту работу для разного количества столбцов.

Два

При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.

Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:

• =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
• =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).

При этом в таблице выводится информация о наличии совпадений или отличий.

Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Альтернативный вариант

Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.

Больше двух

По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.

Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).

Поиск совпадений в двух и более столбцах

Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).

В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).

В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».

Как вывести результат в Эксель

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

Для этого в Эксель сделайте следующее:

• Выделите вертикальные секции с данными, которые нужно сравнить.
• Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
• Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».

• В появившемся диалоговом окне выберите слева пункт «Повторяющиеся», а в правом списке укажите, каким цветом будут выделяться данные. Жмите на кнопку «ОК».
• После этого в выделенной колонке подсвечиваются цветом совпадения.

При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:

1. С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
2. Выделите область с информацией в дополнительной колонке.
3. В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
4. Кликните на «Повторяющиеся значения».
5. Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.

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