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

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

Функция ВПР на РусскомФункция VLOOKUP на Английском
VLOOKUP ВПР

Синтаксис 

Синтаксис функции ВПР
ВПР (заданное значение, таблица, номер столбца, [интервальный просмотр])

Аргументы: 

  • заданное значение – исходный ключ для поиска соответствующих данных в правых столбцах;  
  • таблица (организованная по вертикали) – совокупность столбцов, в которых будет производиться поиск; 
  • номер столбца – номер столбца, содержащего возвращаемую (искомую) величину; 
  • [необязательно] интервальный просмотр – параметр степени точности производимого поиска. 

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

Таблица – область поиска, содержащая и ключевой столбец; 

Номер столбца указывает, какие именно данные следует извлечь из строки. 

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

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

Функция ВПР не чувствительна к регистру символов. 

Примеры использования функции ВПР

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

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

Предварительная сортировка 

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

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

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

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

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

ВПР пример 1 

  1. После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат. 
  1. На строке формул щёлкнуть fx
Функция ВПР в Excel с примерами

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

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

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

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

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

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

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

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

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

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

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

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

ВПР пример 2 

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

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

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

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

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

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

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

  • щёлкнуть пункт «Дата»; 
  • в правом списке при необходимости поменять вариант отображения; 
  • щёлкнуть OK.  
Функция ВПР в Excel с примерами

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

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

ВПР пример 3 

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

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

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

  1. При использовании функцией ВПР подстановочных знаков её последний аргумент обязательно должен использоваться, и иметь значение ЛОЖЬ. 
  1. В более широком смысле то же самое нужно делать и в других случаях, когда, при корректном заполнении обязательных аргументов функции ВПР, возвращаемый ею результат ошибочен.  
Оцените статью
Добавить комментарий