Функция ВПР, пожалуй, одна из самых красивых функций в наборе Excel. Вариантов ее применения множество. Основное применение - поиск совпадений в разных списках (базах данных). Синтаксис написания формулы следующий:
=ВПР(A1;База_данных;2;ЛОЖЬ)
Рассмотрим параметры функции ВПР:
A1 - это относительная ссылка на ячейку листа Excel, в которой находится искомое значение. Под относительной ссылкой понимается то, что при копировании формулы по столбцам/строкам ссылка будет меняться соответственно. Чтобы при копировании формулы в другие столбцы/строки ссылка на столбец/строку не изменялась ее можно сделать абсолютной по одному/обоим параметрам. Например:
$A$1 - абсолютная ссылка по столбцу и по строке;
$A1 - абсолютная по столбцу, относительная по строке;
A$1 - относительная по столбцу, абсолютная по строке;
A1 - относительная ссылка по столбцу и строке.
Значек $ можно вставить в формулу вручную, либо выделить в строке формул ссылку и последовательно нажимая F4 добиться нужного результата.
База_данных - имя области данных в первом столбце которой, производится поиск совпадения значения с параметром A1. Столбец по которому производится поиск всегда должен быть первым. Область данных можно также задать указав адрес левой верхней и правой нижней ячейки. Например:
=ВПР(A1;Лист2!A1:H30;2;ЛОЖЬ)
В приведенном примере ссылка на область данных относительная, это означает что при копировании формулы адрес области данных будет изменяться, что приведет к ошибкам, поэтому ссылку надо делать абсолютной. Например:
=ВПР(A1;Лист2!$A$1:$H$30;2;ЛОЖЬ)
Третий параметр функции ВПР - "2" - номер столбца откуда функция будет брать данные при нахождении искомого значения A1. Если номер столбца указан неверно (напрмер в области всего 8 столбцов а указали 9), функция ВПР вернет значение #ССЫЛКА!.
Параметр ЛОЖЬ означает что функция ВПР будет искать точное совпадение значения в ячейке A1 со значениями в первом столбце области данных. При нахождении такого значения функция вернет значение из заданного столбца (в данном примере - второй столбец) области данных. Если значение A1 не найдено, функция ВПР вернет #Н/Д - нет данных. Вторым значением этого параметра может быть ИСТИНА. В этом случае функция ВПР будет искать ближайшее к искомому значению A1. Честно говоря механизм определения этого ближайшего значения до конца не ясен, особенно когда искомое значение текст. Поэтому применение функции ВПР с параметром "ИСТИНА" встречается достаточно редко.
ДОбрый день!
ОтветитьУдалитьА как сделать так, чтобы он искал не в первом столбце, а во втором и тп?
В принципе есть функции к Excel которые позволяют искать не по первому столбцу. Я, как то, нашел в И-нете, ссылка не сохранилась, но и без этого можно обойтись, ведь всегда можно задать область данных таким образом, что бы столбец с искомыми данными был первым, в конце концов его можно просто скопировать в нужное место
ОтветитьУдалитьДобрый день! А если третий параметр вункции-столбец состоит из ссылок,возможно ли,что бы ссылка и выдавлась?
ОтветитьУдалитьСтолбец может быть ссылкой. Однако непонятно что значит, "что бы ссылка и выдавалась"?
ОтветитьУдалитьЕсли речь идет о том, чтобы при копировании функции по ячейкам менялся и номер столбца, то проще использовать вместо номера столбца функцию СТОЛБЕЦ()+N или СТОЛБЕЦ()-N, где N некоторое постоянное число позволяющее задать соответствие номеров столбцов откуда берутся данные и номеров столбцов куда они помещаются. Если не угадал, уточните свой вопрос.
ВПР выдаёт только текст в ячейке,а желательно ссылку
ОтветитьУдалитьФункция ВПР возвращает содержимое ячейки, а не ее адрес. Боюсь заставить ее возвращать адрес не получится. Пришлите мне по электронке файл с примером того, что Вы хотите. Возможно это можно решить другими методами.
ОтветитьУдалить