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