Функция ВПР в формате Excel

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

В англоязычной версии программы можно встретить VOOKUP – это одна и та же функция. Расшифровывается данная аббревиатура, как вертикальный просмотр (Vertical LookUp). При задании этой функции в формуле, Excel проводит поиск по столбцам и ищет совпадение комбинации символов (цифр) путем обычного перебора, начиная с первой строки по всем колонкам. Существует также функция ГПР – горизонтальный просмотр, однако она менее востребована, ведь, как правило, колонок в таблице всегда гораздо меньше, а соответственно и поиск с ВПР будет осуществлен быстрее.

Формат написания формулы с применением ВПР

Правильность составления формулы будет гарантировать успешный поиск (или отсутствие результатов в случае  если искомой комбинации нет в таблице), в противном случае, в ячейке с прописанной формулой будет отображаться лишь набор символов, который будет лишь текстом, не более того. Для того, чтобы не ошибиться в написании формулы, лучше всего использовать табличный процессор:

  • откройте документ, в котором необходимо произвести поиск;
  • в строке поиска формулы наберите ВПР (VOOKUP для аннглийской версии Excel);
  • откройте полный перечень формул и кликните по кнопке «Найти»;

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

В первом поле необходимо внести значение, которое необходимо найти.

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

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

Четвертое поле не обязательно к заполнению. Оно содержит логическое значение и по умолчанию установлено «ИСТИНА» (или цифра 1).

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

Например, нам нужно в таблице (см. скриншот) по коду товара найти и отобразить наименование.

Примечание: На картинке формула (в колонке Е) прописана лишь для того, чтобы наглядно увидеть, каким образом работает программа.

В примере 1 и 3 указано значение, которое отсутствует в заданном диапазоне. Так, как четвертый параметр не был задан (не прописывалась цифра 0 или слово ЛОЖЬ), программа анализирует содержимое ячеек и выводит в результате то значение, которое наиболее близко по значению (в данном случае вместо кода товара 2 и 14 на экран были выведены значения строк с кодом 1 и 13). Если же пользователю необходим результат лишь точного совпадения, в четвертом параметре необходимо указывать значение «0». Это можно наглядно увидеть по следующему скриншоту (значение 2 не было найдено, поэтому в ячейке отображено #Н/Д).

Также, в качестве искомой величины можно использовать непосредственно ячейку. Иными словами: при вводе адреса ячейки осуществляется поиск значения, которое прописано в ней. Более наглядно это видно на скриншоте.

Долго ли может осуществляться поиск?

Множество пользователей не прописывают четвертый параметр (не вводят значение 0). Считается, что в этом случае процесс поиска осуществляется быстрее. Если же массив значений огромный, поиск может затянуться на значительное время, при этом, при вводе значения ЛОЖЬ, операция будет длиться действительно чуть ли не в два раза дольше, чем без заполнения четвертого поля в окне «Аргументы функции». Некоторые пользователи отписывались на тематических форумах, что при указании нуля, программа могла полностью подвисать, без принудительного завершения работы Excel просто висел и самостоятельно выгружаться из системы не хотел.

Если в Вашей таблице имеются сотни, а то и тысячи формул, лучше всего выполнить сортировку первого столбца. Это поможет сократить время отображения значений с формулами, так называемого пересчета, ВПР в 4-5 раз. Это обусловлено тем, что программе, как и человеку, проще иметь дело с уже отсортированными данными. В этом случае начинает работать, так называемый, бинарный поиск. Кстати говоря, при вводе значения 0 в качестве 4-го параметра, бинарный поиск не работает. Это действительно проблема, из-за чего и время поиска значительно больше. Разработчики пытаются каким-то образом оптимизировать эту задачу, но на данный момент этого нет.

 

Комментарий “Функция ВПР в формате Excel

Добавить комментарий для Владислав Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *