Как найти значения одного столбца с другими

Хитрости »

15 Май 2011              515559 просмотров

Как найти значение в другой таблице или сила ВПР

• Задача и её решение при помощи ВПР
• Описание аргументов ВПР
• Что важно всегда помнить при работе с ВПР
• Как избежать ошибки #Н/Д(#N/A) в ВПР?
• Как при помощи ВПР искать значение по строке, а не столбцу?
• Решение при помощи ПОИСКПОЗ
• Работа с критериями длиннее 255 символов

Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности.
ВПР ищет заданное нами значение(аргумент искомое_значение) в первом столбце указанного диапазона(аргумент таблица). Поиск значения всегда происходит сверху вниз(собственно, поэтому функция и называется ВПР: Вертикальный ПРосмотр). Как только функция находит заданное значение – поиск прекращается, ВПР берет строку с найденным значением и смотрит на аргумент номер_столбца. Именно из этого столбца берётся значение, которое мы и видим как итог работы функции. Т.е. в нашем конкретном случае, для ячейки С2 второй таблицы, функция берет фамилию “Петров С.А.”(ячейка $A2 второй таблицы) и ищет её в первом столбце указанной таблицы(Лист1!$A$1:$C$4), т.е. в столбце А. Как только находит(это ячейка А3)

ВПР может вернуть только одно значений – первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет ошибка #Н/Д(#N/A). Не надо этого бояться – это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой – как числа. Или наоборот.

Описание аргументов ВПР

• Искомое_значение($A2) – это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет Петров С.А.. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках – =ВПР(“Петров С.А”;Лист1!$A$1:$C$4;3;0), либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: “*” и “?”. Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью “Петров С.А”, а ввести лишь фамилию и знак звездочки – “Петров*”. Тогда будет выведена любая запись, которая начинается на “Петров”. Если же надо найти запись, в которой в любом месте строки встречается фамилия “Петров”, то можно указать так: “*петров*”. Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: “Иванов ?.?.”.
  Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A2 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A2&”*”. Эта запись будет равнозначна “Иванов*”. В A2 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
  A2&”*” =>
  “Иванов”&”*” =>
  “Иванов*”
  А полная формула в итоге будет выглядеть так: =ВПР(A2&”*”;Лист1!$A$1:$C$4;3;0)
  Очень удобно, если значений для поиска много.
  Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: “*”&A1&”*”
• Таблица(Лист1!$A$1:$C$4) – указывается диапазон ячеек, в первом столбце которых будет просматриваться аргумент Искомое_значение. Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100. Диапазон в аргументе таблица всегда должен быть “закреплен”, т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4).
• Номер_столбца(3) – указывается номер столбца в аргументе Таблица, значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия – т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументе Таблица.
  

  если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне:  =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ(Лист1!$A$1:$C$4);0). К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе: =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ($A$1:$C$4);0).

• Интервальный_просмотр(0) – очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Так же допускается указать 1 или 0. 1 = ИСТИНА, 0 = ЛОЖЬ. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Искомому_значению. Это не имеет никакого отношения к знакам подстановки(“*” и “?”). Если же использовать 1 или ИСТИНА, то…Совсем в двух словах не объяснить. Если вкратце – ВПР будет искать наиболее похожее значение, подходящее под Искомомое_значение. Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр равен ИСТИНА или 1. Если же 0 или ЛОЖЬ – сортировка не нужна. Этот аргумент необходимо использовать осторожно – не стоит указывать 1 или ИСТИНА, если нужно найти точное соответствие и уж тем более не стоит использовать, если не понимаете принцип его работы.
  Подробнее про работу ВПР с интервальным просмотром, равным 1 или ИСТИНА можно ознакомиться в статье ВПР и интервальный просмотр(range_lookup)

• Таблица всегда должна начинаться с того столбца, в котором ищем Искомое_значение. Т.е. ВПР не умеет искать значение во втором столбце таблицы, а значение возвращать из первого. В лучшем случае ничего найдено не будет и получим ошибку #Н/Д(#N/A), а в худшем результат будет совсем не тот, который должен быть
• аргумент Таблица должен быть “закреплен”, т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4). Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки и жмете клавишу F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы из одной ячейки в остальные аргумент Таблица будет “съезжать” и результат может быть совсем не таким, какой ожидался(в лучшем случае получите ошибку #Н/Д(#N/A)
• номер_столбца не должен превышать общее кол-во столбцов в аргументе таблица, а сама Таблица соответственно должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере указана Лист1!$A$1:$C$4 – всего 3 столбца(A, B, C). Значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца. Т.е. если мы запишем формулу так: =ВПР($A2;Лист1!$A$1:$C$4;4;0) – мы получим ошибку #ССЫЛКА!(#REF!).
  Если аргументом Таблица указан диапазон $B$1:$C$4 и необходимо вернуть данные из столбца С, то правильно будет указать номер столбца 2. Т.к. аргумент Таблица($B$1:$C$4) содержит только два столбца – В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА!(#REF!), т.к. третьего столбца в указанном диапазоне просто нет.

Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца: =ВПР($A2;Лист1!$A$1:$C$4;СТОЛБЕЦ();0). Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.

Как избежать ошибки #Н/Д(#N/A) в ВПР?

 
Еще частая проблема – многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это можно обойти при помощи специальных функций.
Для пользователей Excel 2003 и старше:

=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$1:$C$4;3;0));””;ВПР($A2;Лист1!$A$1:$C$4;3;0))
=IF(ISNA(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)),””,VLOOKUP($A2,Лист1!$A$1:$C$4,3,0))

Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше будет удобнее использовать функцию

ЕСЛИОШИБКА(IFERROR)

:

=ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$1:$C$4;3;0);””)
=IFERROR(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0);””)

Подробнее про различие между использованием ЕСЛИ(ЕНД и ЕСЛИОШИБКА я разбирал в статье: Как в ячейке с формулой вместо ошибки показать 0
Но я бы не рекомендовал использовать

ЕСЛИОШИБКА(IFERROR)

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

• искомое значение состоит более чем из 255 символов(решение этой проблемы приведено ниже в этой статье: Работа с критериями длиннее 255 символов)
• искомое значение является числом с большим кол-вом знаков после запятой. Excel не может правильно воспринимать такие числа и в итоге ВПР может вернуть ошибку. Правильным решением здесь будет округлить искомое значение хотя бы до 4-х или 5-ти знаков после запятой(конечно, если это допустимо):
  =ВПР(ОКРУГЛ($A2;5);Лист1!$A$1:$C$4;3;0)
  =VLOOKUP(ROUND($A2,2),Лист1!$A$1:$C$4,3,0)
• искомое значение содержит специальные или непечатаемые символы.
  В этом случае придется либо избавиться от непечатаемых символов в искомом аргументе:
  =ВПР(ПЕЧСИМВ($A2);Лист1!$A$1:$C$4;3;0)
  =VLOOKUP(CLEAN($A2),Лист1!$A$1:$C$4,3,0)
  либо добавить перед всеми специальными символами(такими как звездочка или вопр.знак) знак тильды(~), чтобы сделать эти знаки просто знаками, а не знаками специального значения(так же работа со специальными(служебными) символами описывалась в статье: Как заменить/удалить/найти звездочку). Добавить символ перед знаком той же тильды можно при помощи функции ПОДСТАВИТЬ(SUBSTITUTE):
  =ВПР(ПОДСТАВИТЬ($A2;”~”;”~~”);Лист1!$A$1:$C$4;3;0)
  =VLOOKUP(SUBSTITUTE(A2,”~”,”~~”),Лист1!$A$1:$C$4,3,0)
  Если необходимо добавить тильду сразу перед несколькими знаками, то делает это обычно так(на примере подстановки одновременно для тильды и звездочки):
  =ВПР(ПОДСТАВИТЬ(ПОДСТАВИТЬ($A2;”~”;”~~”);”*”;”~*”);Лист1!$A$1:$C$4;3;0)
  =VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,”~”,”~~”),”*”,”~*”),Лист1!$A$1:$C$4,3,0)

• Искомое_значение($A2) – непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше – то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки * и ? и ровно в таком же исполнении.
• Просматриваемый_массив(Лист1!$A$1:$A$4) – указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искать Искомое_значение. Если попытаться указать более одного столбца, то функция вернет ошибку.Справедливости ради надо отметить, что можно указать либо столбец, либо строку
• Тип_сопоставления(0) – то же самое, что и Интервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС(INDEX)(которая возвращает из указанного диапазона значение на пересечении заданных строки и столбца) – то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Массив(Лист1!$A$2:$C$4). В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не обязательно или он всегда будет равен 1(столбец-то всего один). К слову – данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если не преследуются иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0));””;ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2);””)

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$1:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$1:$A$4=$A2;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

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

В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.

Скачать пример

  Tips_All_VLookUp.xls (26,0 KiB, 17 469 скачиваний)

Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Как проверить или найти, существует ли значение в другом столбце?

Проверьте, существует ли значение в другом столбце с формулой

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

1. Во-первых, вы можете скопировать два столбца данных и вставить их в столбец A и столбец C отдельно на новом листе, оставив столбец B пустым, чтобы ввести следующую формулу.

2. Введите эту формулу: = ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (A4; $ C $ 4: $ C $ 14; 1; ЛОЖЬ)); «Не существует»; «Существует») в ячейку B2 и нажмите Enter ключ, см. снимок экрана:

Внимание: в приведенной выше формуле, A4 – это ячейка первого столбца, которую вы хотите проверить, находится ли она в другом столбце, и 4 канадских доллара: 14 канадских долларов столбец, с которым вы хотите сравнивать.

3. Затем выберите ячейку B4 и перетащите маркер заполнения к ячейкам, которые вам нужны, чтобы применить эту формулу, вы получите следующий результат: Существовать означает, что значение в списке A существует и в списке B, и Не существует указывает, что значение отсутствует в списке B.

Проверьте, существует ли значение в другом столбце, и выделите их с помощью условного форматирования

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

1. Выделите ячейки в списке A, которые вы хотите выделить также ячейки, из которых выходят значения, в списке B.

2. Затем нажмите Главная > Условное форматирование > Новое правило, В Новое правило форматирования диалоговое окно, щелкните Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила раздел, а затем введите эту формулу = ПОИСКПОЗ (A4; $ C $ 4: $ C $ 14,0) в Формат значений, где эта формула истинна текстовое поле, см. снимок экрана:

3. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговом окне и выберите нужный цвет под Заполнять вкладку, см. снимок экрана:

4. Затем нажмите OK > OK чтобы закрыть диалоговые окна, и теперь вы можете видеть, что значения как в списке A, так и в списке B выделены из списка A, см. снимок экрана:

Проверьте, существует ли значение в другом столбце, и сразу затените их с помощью Kutools for Excel

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

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Выберите > Выберите одинаковые и разные ячейки, см. снимок экрана:

2. В Выберите одинаковые и разные ячейки диалоговом окне выполните следующие действия:

A: Нажмите  с Найдите значения в: (Диапазон A) раздел, чтобы выбрать столбец данных, который вы хотите сравнить с другим столбцом. И нажмите  под Согласно: (Диапазон B) раздел, чтобы выбрать ячейки, с которыми вы хотите сравнивать.

B: Выбрать Каждый ряд под на основании .

C: Уточнить Те же ценности из Арендовать .

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

3. После завершения настроек нажмите Ok все значения в столбце A и столбце C затенены в столбце A, как показано на следующем снимке экрана:

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

Скачать и бесплатную пробную версию Kutools for Excel Сейчас !

Проверьте, существует ли значение в другом столбце, и сразу затените их с помощью Kutools for Excel