Как сравнить два списка в 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, поэтому он выделен .
Обратите внимание, что мы решили выделить значения, которые не принадлежат обоим наборам данных, но мы также могли применить другой стиль, например полужирный текст, увеличенный размер шрифта, рамку вокруг ячеек и т. д.
Как сопоставить два столбца и вернуть третий в 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:
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Как сравнить два столбца в Excel — методы сравнения данных Excel
Опубликовано 29.04.2014 Автор Ренат Лотфуллин
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в выявлении повторяющихся значений, корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Использование условного оператора ЕСЛИ
Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:
Разместите оба столбца для сравнения в колонках A и B рабочего листа.
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в использовании формулы ВПР, вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.
Использование макроса VBA
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
Создайте новый модуль кода VBA и введите следующий код. О том, как писать макросы вы можете прочитать более подробно в статье написания VBA кода.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Find_Matches()
Dim CompareRange As Variant , x As Variant , y As Variant
‘ Установка переменной CompareRangeравной сравниваемому диапазону
Set CompareRange = Range( «B1:B11» )
‘ Если сравниваемый диапазон находится на другом листе или книге,
‘ используйте следующий синтаксис
‘ Set CompareRange = Workbooks(«Книга2»). _
‘ Worksheets(«Лист2»).Range(«B1:B11»)
‘
‘ Сравнение каждого элемента в выделенном диапазоне с каждым элементом
‘ переменной CompareRange
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 2) = x
Next y
Next x
End Sub
В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.
Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.
После выполнения макроса, результат должен быть следующим:
Использование надстройки Inquire
С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.
Итог
Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.
Вам также могут быть интересны следующие статьи
- Что если отобразить скрытые строки в Excel не работает
- Печать таблицы в Excel — настройка области и границ печати Excel
- Формулы таблиц Excel
- Создание связи между таблицами Excel
- Функция СЖПРОБЕЛЫ в Excel с примерами использования
- Четыре способа использования ВПР с несколькими условиями
- Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
- Пятый урок курса по основам Excel — Печать в программе
- Шестой урок онлайн курса по основам Excel — Управление рабочим листом
- Четвертый урок курса по основам Excel — Изменение ячеек
Рубрика: Основы | Метки: Анализ, основы, Формулы | 53 комментария | Permalink
53 комментария
Спасибо, у вас очень понятно и красиво оформлено, глаз радует
для меня трудность- понять работу ПОИСКОЗ. Если не трудно сделайте пост с пояснениями по данной формуле.
Как сравнить два столбца в excel на совпадения впр
Доброго всем времени суток. Мне необходимо найти в друх столбцах совпадения и в случаее совпадения вставить значение из третьего столбца. Вручную делать долго ибо это около 1500 строк. Прикрепляю образец того, что надо сделать. Значения в столбцах буквы с цифрами. Заранее спасибо.
Прикрепленные файлы
- post_327500.xls (19 КБ)
Пользователь
Сообщений: 8196 Регистрация: 21.12.2012
16.04.2012 11:54:49
Функция ВПР() поможет.
«..Сладку ягоду рвали вместе, горьку ягоду я одна.»
Пользователь
Сообщений: 2 Регистрация: 01.01.1970
16.04.2012 11:58:54
А по подробнее можно как это будет выглядеть?
Пользователь
Сообщений: 23778 Регистрация: 22.12.2012
16.04.2012 12:04:30
Это если «мастер формул» не помог 🙂
16.04.2012 12:05:02
=ВПР(A1;$D$1:$E$9;2;0)
17.04.2012 02:57:45
Спасибо за ответы!
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |