Как работает функция ВПР в Excel. Особенности применения.

Назначение и принцип работы функции  ВПР в Excel.

Использование ВПР В Excel

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

Чтобы разобраться, как работает эта функция, надо немного отойти от Excel в сторону реальной жизни. Каждый из нас делает звонки. Неважно, со смартфона или айфона. Главное тут другое. Редко кто набирает нужный телефонный номер вручную. Наших контактов так много, что запоминать их нет смысла. Мы все эти номера и не запоминаем, а храним всю эту информацию в телефонной книжке нашего гаджета. И вот тут наступает интересный момент. При звонке мы выбираем ИМЯ АБОНЕНТА, с которым хотим соединиться по телефону. А вот сам звонок идет уже по его НОМЕРУ. Как это происходит? Да просто. Наш гаджет проверяет первый столбец телефонной книжки. Как только он находит выбранное нами ИМЯ АБОНЕНТА, он в строчке с найденным ИМЕНЕНМ переходит в столбец с НОМЕРАМИ и получает нужную информацию.

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

Схематично это выглядит следующим образом:

Синтаксис функции ВПР.

Как же использовать функцию ВПР в Excel? Для этого вначале разберемся в порядке указания данных при вводе ВПР, другими словами, в синтаксисе. Указывают по очереди, в скобках после названия, следующие исходные данные, или, по-научному, аргументы.

1. Искомое значение. Именно его Excel будет искать в ПЕРВОМ столбце нашей таблицы. Тут важно отметить такие тонкости. Прежде всего это значение в первом столбце таблицы должно быть уникальным. Само название ВПР расшифровывается не как «Вертикальный Поиск Решения», как многие думают, а как «Вертикальный ПЕРВЫЙ Результат». Именно ПЕРВЫЙ. К примеру, в приведенной раньше на рисунке таблице нельзя для поиска использовать столбец с именем или фамилией. Они могут быть одинаковыми, а вот номер телефона всегда уникальный. Кстати, именно поэтому нельзя в телефонной книге на телефоне указать одинаковые имена для разных людей. Телефон просто не даст это сделать. Другая тонкость в том, что, как ни странно, но Excel будет искать как точное значение, так и близкое к нему. Другими словами, вовсе не факт, что ВПР найдет именно то значение, которое мы задали. Подробнее об этом поговори чуть-чуть попозже.
2. Таблица поиска. Тут надо указать диапазон, в ПЕРВОМ столбце, или, если кому-то так проще, в ПЕРВОЙ КОЛОНКЕ которого Excel станет искать заданное нами искомое значение. В этой таблице может быть любое количество столбцов. Столбец может быть и только один. Обычно такое применяется, когда надо определить, присутствует ли наше значение в списке или нет. С другой стороны, в этом случае проще и быстрее использовать функции ПОИСКПОЗ или СЧЕТЕСЛИ.
3. Номер столбца. Порядковый номер столбца нашей таблицы, из которого мы желаем получить результат. Еще раз обращаю внимание на этот аспект. Наше тестовое значение Excel ищет в первом столбце, а вот результат берет из этой же таблицы, но уже колонки с указанным нами номером. Еще раз отмечу – номер столбца задается ВНУТРИ таблица поиска. Именно поэтому на примере ниже третьим аргументом указана цифра «2», хотя если считать от начала листа, то колонка явно не будет третьей.

4. Интервальный просмотр. Это если говорить официально. А если по-простому, то способ поиска, о чем уже говорилось. Есть два варианта.

• Указать слово ИСТИНА, ввести цифру 1 или вообще ничего не указать. Тогда Excel будет искать как само значение, так и ближайшее к нему, но меньше. Значения в первом столбце нашей таблицы поиска должны располагаться по возрастанию. Данный вариант применяется, когда надо найти не конкретное значение, а обработать некий диапазон. На предыдущем рисунке обрабатываются диапазоны цен, для каждого из которых задан свой процент скидки.
• Написать слово ЛОЖЬ или цифру 0. В этом случае будет идти точный поиск заданного значения. Именно таким образом по указанному номеру мы определили фамилию абонента.

Работа функции ВПР и возможные проблемы.

Часто на просторах интернета можно найти высказывание, что если задать четвертым аргументом значение ИСТИНА ИЛИ 1, то ВПР сработает быстрее. Это неправда. Посмотрите на скриншоты ниже. Обратите внимание, что быстрее оказался вариант, в котором в качестве интервального просмотра использована ЛОЖЬ.

 

На самом деле скорость зависит от расположения заданного нами искомого значения в таблице. Если оно ближе к началу, то быстрее сработает вариант с ЛОЖЬЮ, а если ближе к концу, то быстрее вариант с ИСТИНОЙ. Дело в самом поиске. Если мы укажем четвертым аргументом ИСТИНУ, то Excel ищет наше значение в первом столбце СНИЗУ ВВЕРХ. Если же выбрать вариант ЛОЖЬ, то поиск будет идти СВЕРХУ ВНИЗ.

При выборе варианта ИСТИНА Excel двигается по первому столбцу вверх до тех пор, пока не наткнется либо на указанное нами значение, либо на первое же значение, которое заведомо меньше указанного нами. Как только это произошло, поиск заканчивается.

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

Еще раз повторю. Если мы указываем в конце истину, или единицу, то первый столбец в нашей таблице для поиска ОБЯЗАН быть по возрастанию. Именно с этим связана проблема на скриншоте выше.

В реальных таблицах из жизни, а не учебных, сортировка первого столбца по возрастанию встречается крайне редко. Более того, зачастую она просто невозможна, как в примере ниже. Учитывая это, применяйте вариант с приблизительным поиском, то есть с единицей либо с истиной в качестве интервального просмотра очень осторожно и только тогда, когда это действительно нужно. В том же примере с окладами мы не увидели никакой ошибки, хотя Excel из-за нашей ошибки взял явно не те данные.

Вторая проблема всплывает при указании в качестве искомого значения числового кода. Посмотрите сами на следующее.

Значение в столбце код есть, а ВПР не смог найти расчетную цену. Как же так? А все просто.

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

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

Работа функции ВПР и исправление ошибок.

Вы видите, что не всегда Функция ВПР в Excel работает безошибочно. Как же избежать неверного результата при работе ВПР? Прежде всего создавайте таблицы поиска аккуратно. Если уж вам позарез нужны нули в начале кода и без них ну никак, используйте текстовый формат. Да, Excel пожалуется, что цифры записаны как текст. Уверяю вас, на распечатке это никак не скажется. Excel печатает метки ошибок только если вы сами это указали в настройках печати и в параметрах.

Если же таблица уже готовая, проверьте, имеются ли ошибки при поиске значения, которое в ней явно присутствует. Обычно это как раз числа в виде текста. Если это так, добавьте в начало колонку, в которой принудительно переведите все значения в текст с помощью одноименной функции и используйте для поиска именно этот столбец. Как вариант, примените комбинацию ВПР и ЕСЛИОШИБКА. Например, в следующем примере Excel принудительно ищет заданное значение в первом столбце как число, а если это не удается, то уже как текст.

Обратите внимание, что несовпадение в форматах уже никак не помешало получить результат

Заключение. 

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

Остается только добавить, что, во-первых, в Excel имеется полностью идентичная по написанию и применению функции ВПР функция ГПР. Разница только в том, что если ВПР работает со столбцами, то ГПР работает со строками. Во-вторых, в английском варианте эти функции называются соответственно VLOOKUP и HLOOKUP. Ну и в-третьих, если ваше искомое значение не находится в самом первом столбце, к примеру, если вам в прайсе, скриншот которого приведен выше, надо по названию товара определить его код, то использование ВПР ничего не даст. Надо применять комбинацию функций ИНДЕКС или СМЕЩ с функцией ПОИСКПОЗ. ОБ этом мы поговорим немного попозже.

На этом наш урок закончен. Надеюсь, было полезно не только новичкам. До встречи на страницах нашего сайта!