Функция впр — эта функция Excel относится к тематической категории «Ссылки и массивы». Название ВПР (VLOOKUP в англоязычной редакции) полностью звучит как «вертикальный просмотр». Её суть заключается в задании ключевого значения в одном из столбцов (часто в первом левом), используя которое, производится поиск соответствующей ему информации на той же строке в столбцах справа.
| Функция ВПР на Русском | Функция VLOOKUP на Английском |
|---|---|
| VLOOKUP | ВПР |
Синтаксис
Аргументы:
- заданное значение – исходный ключ для поиска соответствующих данных в правых столбцах;
- таблица (организованная по вертикали) – совокупность столбцов, в которых будет производиться поиск;
- номер столбца – номер столбца, содержащего возвращаемую (искомую) величину;
- [необязательно] интервальный просмотр – параметр степени точности производимого поиска.
Заданное или ключевое значение – ориентир, используемый для поиска информации на той же строке в других столбцах, расположенных правее.
Таблица – область поиска, содержащая и ключевой столбец;
Номер столбца указывает, какие именно данные следует извлечь из строки.
Необязательный «интервальный просмотр» относится к диапазону просмотра. Он может иметь два логических значения – ИСТИНА или ЛОЖЬ. В первом случае ищется приблизительное совпадение в диапазоне, а во втором – точное. При отсутствии заполнения этого параметра по умолчанию предполагается значение ИСТИНА.
При наличии в ключевом столбце нескольких одинаковых заданных значений функция ВПР будет оперировать с первым сверху из них.
Функция ВПР не чувствительна к регистру символов.
Примеры использования функции ВПР
Уяснение работы рассматриваемой функции будем проводить на примере учебного файла, представленного ниже. Как видно, он представляет собой список сотрудников воображаемого учреждения, содержащий в четырёх последовательных столбцах их фамилии-имена, зарплату, пол и дату рождения. Количество сотрудников практически не ограничено, но для раскрытия основных особенностей функции достаточна показанная десятка.
Предварительная сортировка
Прежде чем приступить к использованию функции ВПР, необходимо выполнить её обязательное предварительное требование. Левый столбец таблицы (в данном случае – с фамилиями и именами) должен быть отсортирован по возрастанию (в данном случае – в алфавитном порядке). Позже будет пояснена настоятельная необходимость этого.
Для сортировки первого столбца следует:
- выделить фамилии-имена первого столбца от Морозова до Петровой (визуальное отсутствие выделения фамилии Морозов – кажущееся);
- в контекстном меню выделенного первого столбца установить курсор на строку «Сортировка»;
- в её выпадающем списке щёлкнуть пункт «Сортировка от А до Я».
В следующем окне программа Excel предлагает пользователю «умную» сортировку». Иными словами, будет произведена сортировка не только первого левого столбца, но и связанных с ним остальных трёх. Очевидно, что если этого не сделать, то список сотрудников будет абсурдным. Поэтому в этом окне, ничего не меняя, следует щёлкнуть пункт «Сортировка».
ВПР пример 1
- После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат.
- На строке формул щёлкнуть fx.
3. В выпадающем списке «Категория» выбрать «Полный алфавитный перечень» или категорию «Ссылки и массивы»: в них в алфавитном порядке будет присутствовать нужная нам функция ВПР. Если эта функция недавно вызывалась, то она будет и в списке «10 недавно использовавшихся».
4. Выделить функцию ВПР и кликнуть OK.
5. В появившемся окне «Аргументы функции» обратите внимание на рекомендацию, подчёркнутую синим цветом. Её игнорирование делает выдаваемые функцией результаты непредсказуемыми. Правильной будет только часть результатов, поэтому перед началом работы сортировка ключевого столбца обязательна.
В окне аргументов необходимо корректно заполнить три обязательных параметра, выделенных жирным шрифтом (последний необязательный параметр будет рассмотрен в примере 3).
6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица».
7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов.
8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата.
ВПР пример 2
Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.
После щелчка на ОК оно появится и в ячейке результата.
Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».
В появившемся одноимённом окне:
- щёлкнуть пункт «Дата»;
- в правом списке при необходимости поменять вариант отображения;
- щёлкнуть OK.
После этого отображаемая в ячейке результата дата примет «человеческий» вид.
ВПР пример 3
Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.
Из последнего примера вытекают два важных следствия, связанных с необязательным аргументом.
- При использовании функцией ВПР подстановочных знаков её последний аргумент обязательно должен использоваться, и иметь значение ЛОЖЬ.
- В более широком смысле то же самое нужно делать и в других случаях, когда, при корректном заполнении обязательных аргументов функции ВПР, возвращаемый ею результат ошибочен.
Нужен