Функция ВПР в Excel с примерами

Функция впр — эта функция Excel относится к направленной на определенную тематику группы «Ссылки и массивы». Заглавие ВПР (VLOOKUP в английской редакции) на сто процентов звучит как «вертикальный просмотр». Её сущность заключается в задании главного значения в одном из столбцов (нередко в первом левом), используя которое, делается поиск соответственной ему инфы на той же строке в столбцах справа.

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

Для сортировки первого столбца следует:

выделить фамилии-имена первого столбца от Морозова до Петровой (зрительное отсутствие выделения фамилии Морозов – кажущееся);
в контекстном меню выделенного первого столбца установить курсор на строчку «Сортировка»;
в её выпадающем перечне щёлкнуть пункт «Сортировка от А до Я».

В последующем окне программка Excel дает юзеру «умную» сортировку». Другими словами, будет произведена сортировка не только лишь первого левого столбца, да и связанных с ним других трёх. Разумеется, что если этого не создать, то перечень служащих будет абсурдным. Потому в этом окне, ничего не меняя, следует щёлкнуть пункт «Сортировка».

ВПР пример 1

Опосля сотворения отсортированного учебного файла щёлкнуть всякую вольную ячейку (к примеру, F9): в ней будет возвращён итог.

На строке формул щёлкнуть f_x.

3. В выпадающем перечне «Категория» избрать «Полный алфавитный список» либо категорию «Ссылки и массивы»: в их в алфавитном порядке будет находиться подходящая нам функция ВПР. Если эта функция не так давно вызывалась, то она будет и в перечне «10 не так давно использовавшихся».

4. Выделить функцию ВПР и кликнуть OK.

5. В показавшемся окне «Аргументы функции» направьте внимание на рекомендацию, подчёркнутую голубым цветом. Её игнорирование делает выдаваемые функцией результаты непредсказуемыми. Правильной будет лишь часть результатов, потому перед началом работы сортировка главного столбца неотклонима.

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

6. Как видно, данное либо ключевое значение в окне аргументов называется «Разыскиваемым». Такое заглавие не кажется мне удачным: оно задаётся вначале, а не ищется, и употребляется как ключ к поиску. Представим, что нас интересует заработная плата, получаемая сотрудницей Ирой Соколовой. Самый обычный метод задать верхний параметр – щёлкнуть на ячейке A8, опосля чего же – кликнуть на строке параметра «Таблица».

7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заглавия столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего перечень зарплат, т. е. 2. Как видно, правильное значение заработной платы сотрудницы Иры Соколовой возникает прямо в окне аргументов.

8. Опосля клика на OK возвращённое функцией значение покажется и в ячейке, зарезервированной для результата.

ВПР пример 2

Естественно, что аналогичным образом могут быть извлечены данные из остальных информационных столбцов. Представим, что нам необходимо выяснить дату рождения всё той же Иры Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на 1-ый взор, непонятное число 29025.

Опосля щелчка на ОК оно покажется и в ячейке результата.

Бывалые юзеры Excel знают, что в данной нам программке даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения приобретенного значения в обычном человеку виде довольно придать ячейке результата формат даты. Для этого поначалу открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».

В показавшемся одноимённом окне:

щёлкнуть пункт «Дата»;

в правом перечне по мере необходимости поменять вариант отображения;
щёлкнуть OK.

Опосля этого отображаемая в ячейке результата дата воспримет «человечий» вид.

ВПР пример 3

Этот пример различается от первого 2-мя аргументами – первым и заполненным крайним. Как видно, в первом аргументе заместо имени применен подстановочный символ звёздочки *. Если не заполнять крайний аргумент, то возвращаемое функцией значение будет неверным. Ввод логического значения ЛОЖЬ сделает итог правильным.

Из крайнего примера вытекают два принципиальных следствия, связанных с необязательным аргументом.

Функция ВПР (VLOOKUP)

Обычный пример работы функции ВПР (VLOOKUP)

A3:D9

В обоих вариантах, в качестве 4-ого параметра стоял «0» — этот параметр именуется «интервальный_просмотр» . Его можно записывать как «0» либо «1», также как «ЛОЖЬ» либо «ИСТИНА». Наиболее тщательно о этом аргументе ниже в разделе «Синтаксис»

Интересно почитать: Функция преобразования в число в excel в

На 1-ый взор может показаться, что смотрится все незначительно запутанно, но, по сути, употреблять эту опцию достаточно просто.

Буковка «В» в заглавии функции ВПР значит «вертикально». Функция ВПР употребляется заместо функции ГПР, если сравниваемые значения размещены в столбце слева от разыскиваемых данных.

Вот иной пример, который наглядно показывает механизм работы функции ВПР:

Механизм работы функции ВПР из группы «Ссылки и массивы»

Синтаксис

Аргументы

Если значение аргумента номер_столбца :

меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;
больше, чем число столбцов в аргументе таблица, функция ВПР возвращает значение ошибки #ССЫЛ!.

Если аргумент интервальный_просмотр имеет значение ИСТИНА либо опущен, то ворачивается четкое либо ориентировочное совпадение. Если четкое совпадение не найдено, то ворачивается наибольшее значение, которое меньше, чем искомое_значение.

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

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ , функция ВПР отыскивает лишь четкое совпадение. Если в первом столбце аргумента таблица есть некоторое количество значений, соответственных аргументу искомое_значение, употребляется 1-ое отысканное значение. Если четкое совпадение не найдено, ворачивается значение ошибки #Н/Д.

Замечания

При поиске в большенном массиве, в особенности, если на листе Excel много формул, у юзера могут возникать препядствия с производительностью. 1-ое, что приходит на мозг — это произвести апдейт компа. Да, функция ВПР считается довольно неспешной, но, не много кто понимает, что ее скорость работы можно прирастить десятикратно, если употреблять поставить аргумент равным ИСТИНА (либо 1). Да, тут есть аспект в виде неотклонимой сортировки, потому такое убыстрение будет работать не постоянно. К слову скорость возрастает из-за сортировки, в этом случае нет необходимости ассоциировать каждое значение из столбца.
Функция ВПР производит поиск постоянно в самом первом (левом) столбце спектра. Данное ограничение можно обойти, там где может быть, естественно, уменьшив аргумент таблица , чтоб разыскиваемый столбец стал самым первым
Поиск ВПР постоянно в самом левом столбце
При поиске текстовых значений в первом столбце аргумента таблица удостоверьтесь, что данные в этом столбце таблицы не содержат исходных пробелов, конечных пробелов, применяемых не по правилам прямых ( ‘ либо » ) и «парных» ( ‘ либо “ ) кавычек либо непечатаемых знаков. В этих вариантах функция ВПР может возвратить неверное либо неожиданное значение
Возвращение ошибки функцией ВПР
При поиске числовых значений либо значений дат удостоверьтесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. В этом случае функция ВПР может возвратить неверное либо неожиданное значение
Если значение аргумента «интервальный_просмотр» — ЛОЖЬ , а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается внедрение подстановочных символов: вопросительного знака (?) и звездочки (*). Вопросительный символ соответствует хоть какому одиночному символу; звездочка — хоть какой последовательности знаков. Если необходимо отыскать сам вопросительный символ либо звездочку, поставьте перед ними символ тильды (

Аспекты использования ВПР

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

Поиск в первом столбце

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

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

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

Поиск до первого совпадения

Индивидуальностью работы ВПР является поиск лишь до первого совпадения. Соответственно, если 1-ый столбец спектра не неповторимый будет найдено лишь 1-ое вхождение. Время от времени конкретно это и необходимо, время от времени — нет, потому необходимо иметь ввиду.

Демонстрация поиска ВПР до первого совпадения

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

Независимость от регистра

Для Excel и ВПР не принципиально в котором регистре (большие либо строчные буковкы) записано разыскиваемое значение и как оно записано в самом спектре.

Независимость от регистра

Излишние пробелы

В замечаниях мы проявили, как неприметный пробел может вызывать ошибку работы данной функции. Если существует риск загромождения ячеек чрезмерным количеством пробелов, следует очистить ячейки при помощи функции СЖПРОБЕЛЫ (TRIM).

Различие в формате данных

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

Интересно почитать: Excel функция найти

Все можно поправить средством приведения характеристик в однообразный формат. Например:

— если в D5 буковкы, а в таблице — числа;

Приведение формата в самой формуле

Существует несколько вариантов, как поменять формат текста на цифру:

Возвести в степень: G2^1;
Двойное отрицание: —G2;
Прибавить ноль: G2+0;
Помножить на один: G2*1.

Прекращение проявления ошибки #Н/Д

Если есть риск появления ошибки при вычислении формулы, можно пользоваться функцией ЕСЛИОШИБКА (IFERROR), которая возвратит определенное значение, в случае появления ошибки

Не зафиксирован массив

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

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

Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Основная», или горячее сочетание Ctrl+T. В этом случае ссылки на таковой спектр будут автоматом становиться абсолютными. Но при работе с таковыми таблицами в Excel есть свои аспекты и, не постоянно это подойдет

Фиксирование спектра в ВПР

Относительный поиск

При использовании ВПР юзеры, обычно постоянно ставят аргумент интервальный_просмотр равным «0» (ЛОЖЬ), что значит, что необходимо выполнить четкий поиск и значения в первом столбце не отсортированы. Но, если поиск осуществляется числовых значений, в неких вариантах, неплохим решением будет отсортировать значения и выполнить не четкий поиск. А в неких вариантах неточный поиск совершенно будет единственным решением. Разглядим подробнее.

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

0 — 60: F
60 — 70: D
70 — 80: C
80 — 90: B
90 — 100: A

Вот так смотрится формула, если для ее построения употреблять логическую функцию ЕСЛИ (да-да, с внедрением ПЕРЕКЛЮЧ либо ЕСЛИМН решение тоже будет лучше, но, допустим, у вас не самый свежайший Excel, ну и ВПР тут выигрывает и у новейших логических функций тоже):

Роскошное решение с ВПР по поиску оценки

поглядите на рисунке на формуле с ЕСЛИ и сравните с такой ВПР и неточным совпадением, крайняя смотрится существенно лучше:

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

ВПР для поиска наибольшего значения

Функция ВПР в Эксель

Редактор Эксель – весьма мощная программка для работы с таблицами. Время от времени случается так, что приходится работать с огромным объемом данных. В таковых вариантах употребляются разные инструменты поиска инфы. Функция «ВПР» в Excel – одна из самых нужных для данной нам цели. Разглядим её наиболее пристально.

Расшифровка

Большая часть юзеров не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На британском функция именуется «VLOOKUP», которая значит «Vertical LOOK UP»

Как воспользоваться функцией

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

Сделайте таблицу, по которой можно будет создать какой-либо поиск инфы.

Добавим несколько полей, которые будем употреблять для демонстрации формул.

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

Потом нас попросят указать «Аргументы функции»:
- В поле «Разыскиваемое выражение» указываем ссылку на ячейку, в какой мы написали подходящую нам фамилию.
- Для того чтоб заполнить поле «Таблица», довольно просто выделить все наши данные с помощью мышки. Ссылка подставится автоматом.
- В графе «Номер столбца» указываем номер 2, так как в нашем случае имя находится во 2-ой колонке.
- Крайнее поле может принимать значения «0» либо «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет находить четкое совпадение по данным аспектам. Если же «1» – то во время поиска не будут учитываться полные совпадения.
Для сохранения кликните на клавишу «OK».

В итоге этого мы получили имя «Томара». Другими словами, всё верно.

Сейчас необходимо пользоваться данной нам же формулой и для других полей. Обычное копирование ячейки с помощью [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka] не подойдёт, так как у нас употребляются относительные ссылки и всякий раз будет изменяться номер столбца.

Для того чтоб всё сработало верно, необходимо создать последующее:

Кликните на ячейку с первой функцией.
Перейдите в строчку ввода формул.
Скопируйте текст с помощью [knopka]Ctrl[/knopka]+[knopka]C[/knopka].

Интересно почитать: Модуль в excel функция

Сделайте активной последующее поле.
Опять перейдите в строчку ввода формул.
Нажмите на жаркие клавиши [knopka]Ctrl[/knopka]+[knopka]V[/knopka].

Лишь таковым методом редактор не изменит ссылки в аргументах функции.

Потом меняем номер столбца на подходящий. В нашем случае это 3. Жмем на кнопку [knopka]Enter[/knopka].

Благодаря этому мы лицезреем, что данные из столбца «Год рождения» обусловились верно.

Опосля этого повторяем те же самые деяния для крайнего поля, но с корректировкой номера подходящего столбца.

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

Другими словами нумерация начинается не с начала листа, а с начала обозначенной области ячеек.

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

Показывать эту возможность будем с помощью 2-ух таблиц. Представим, что у нас на втором листе возникли обновлённые данные о сотрудниках. Наша задачка заключается в том, чтоб выяснить, что конкретно поменялось. Для этого необходимо будет создать последующее.

Добавим 2-ой лист с буквально таковой же таблицей (копировали с помощью жарких кнопок [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka]).
Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сопоставления.

Добавим ещё один столбец в нашу старенькую таблицу.

Перебегаем в первую клеточку новейшего столбца и вводим там последующую формулу.

$B$3:$B$11 – для поиска употребляются все значения первой колонки (используются абсолютные ссылки);
Лист2! – эти значения необходимо находить на листе с обозначенным заглавием;
$B$3:$E$11 – таблица, в какой необходимо находить (спектр ячеек);
4 – номер столбца в обозначенной области данных;
ЛОЖЬ – находить четкие совпадения.

Новенькая информация выведется в том месте, где мы указали формулу.
Итог будет последующим.

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

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

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

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

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

Функция «ВПР» и выпадающие списки

Разглядим примеры использования этих 2-ух инструментов сразу. Для этого необходимо выполнить последующие деяния.

Перейдите в ячейку, в какой происходит выбор фамилии.
Откройте вкладку «Данные».
Кликните на обозначенный инструмент и изберите пункт «Проверка данных».

В новеньком окне в графе «Тип данных» изберите пункт «Перечень».

Опосля этого покажется новое поле «Источник». Кликните туда.
Потом выделите 1-ый столбец. Ссылка на ячейки подставится автоматом.
Для продолжения нажмите на «OK».

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

Вы увидите, что данные автоматом поменялись. При всем этом всё подставится согласно избранной фамилии.

Сводные таблицы таковой возможностью повытрепываться не могут. Функция «ВПР» – весьма мощнейший инструмент для поиска хоть какой инфы.

Ошибки #Н/Д

С схожей неувязкой сталкиваются почти все юзеры, которые лишь начинают воспользоваться данной нам функцией. Обычно, ошибка «#Н/Д» возникает в последующих вариантах:

вы включили четкий поиск (крайний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
вы включили неточный поиск (крайний параметр 1/ИСТИНА), но при всем этом данные не отсортированы по возрастанию (если употребляется ориентировочный поиск, то создатели Microsoft советуют употреблять упорядоченные данные);
аргументы функции имеют разный формат (к примеру, что-то в текстовом виде, а остальное – в числовом);
в формуле находятся опечатки либо излишние знаки (пробелы, непечатаемые знаки, переносы и так дальше).

Отличие от функции «ГПР»

Данный инструмент фактически буквально таковой же, лишь отыскивает по горизонтали. Наиболее тщательно о нем можно выяснить на официальном веб-сайте Microsoft.

Заключение

В данной статье мы пошагово разглядели, как воспользоваться функцией «ВПР» в редакторе Excel. Не считая этого, было показано несколько примеров. Данная {инструкция} нацелена на новичков (чайников).

Если у вас что-то не выходит, может быть, вы некорректно указываете аргументы функции. К примеру, неверный номер столбца либо неправильный спектр ячеек. Попытайтесь повторить описанные выше деяния поновой, но сейчас наиболее пристально. И у вас всё получится.

Видеоинструкция

Для тех, кому пошаговая {инструкция} не посодействовала, мы приготовили особое видео, в каком всё рассказывается еще наиболее детально с доп замечаниями. Может быть, так для вас будет проще разобраться.