Функция ВПР (VLOOKUP) в Excel, Google Sheets, LibreOffice, OpenOffice - База знаний Учебного центра BRP ADVICE

Общая информация о ВПР (VLOOKUP)

Функция ВПР - это одна из наиболее популярных функций ссылок и массивов. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется VLOOKUP.

Уровень сложности по шкале BRP ADVICE - 3 из 7.

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

Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.

Как работают функции ссылок и массивов

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:

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

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

Как указать, где находится внутренняя таблица? Для этого вам понадобится указать ее адрес при помощи стандартных ссылок вида A1 или R1C1. На картинке выше адрес внутренней таблицы - это диапазон J17:O25.

Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.

Что же делают функции ссылок и массивов? Их конечная цель - получить значение по его внутреннему адресу. При этом функции ссылок и массивов могут как найти нужную строку и нужный столбец сами, так и использовать введенные пользователем значения. Для разных задач используются разные функции. Но в конечном счете получается примерно так, Excel, Google Sheets, LibreOffice, OpenOffice определяют, что вам нужна пятая строка и третий столбец и выдают значение из такой ячейки:

Как работает ВПР (VLOOKUP)

Самый простой способ разобраться с ВПР (VLOOKUP) - это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример - с приблизительным поиском.

Пример 1 - точный поиск

Ссылка на файл-пример приведена в конце описания этого примера.

Допустим, есть таблица по сотрудникам вашей организации и их окладам. В этой таблице указан каждый сотрудник (его ФИО) и его оклад. Первый столбец этой таблицы - ФИО сотрудника, второй - оклад. То есть ваша таблица выглядит так:

ВПР (VLOOKUP) позволит вам найти оклад, указывая ФИО сотрудника. Конечно, когда у вас в таблице 5 строк и задача разовая, глазами вы найдете нужное значение очень быстро. А если у вас сотрудников 200? Или 5000? ВПР (VLOOKUP) поможет упростить вам жизнь.

Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?

Надо написать формулу

=ВПР("Иванов С.А.";C3:D9;2;ЛОЖЬ)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=VLOOKUP("Иванов С.А.";C3:D9;2;FALSE) .

Кстати, в некоторых версиях Excel, вместо ";" должна использоваться ",".

После этого программа вернет вам ответ 21 000.

Что значат все аргументы ВПР (VLOOKUP)?

1. Искомое значение. В нашем примере это "Иванов С.А." - это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы - только в первом столбце внутренней таблицы.

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

2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае - оклад). Посчитать вам его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый - это ФИО, второй - оклад. Значит ставим цифру 2.

4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос "Мы же приблизительно ищем, верно?". ИСТИНА (TRUE) означает "да, приблизительно", ЛОЖЬ (FALSE) - "нет, мы ищем именно это". То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере №2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE).

Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.

Что именно делает ВПР (VLOOKUP) в этом примере?

ВПР (VLOOKUP) просматривает каждую ячейку первого столбца сверху вниз. Он смотрит в первой строке "Газоев И.В."="Иванов С.А."? Видит, что нет. Тогда смотрит вторую: "Ромашкина Б.О."=Иванов С.А."? И так далее, пока не доходит до нужной строки. Когда ВПР (VLOOKUP) видит, что "Иванов С.А."="Иванов С.А.", он останавливается и запоминает номер строки, в которой это произошло. В нашем примере это 4 строка. Четвертая, потому что подсчет начинается не с начала листа, а с начала внутренней таблицы. И, наконец, ВПР (VLOOKUP) возвращает значение внутренней таблицы, которое находится на пересечении 4 строки (то, что он нашел) и 2 столбца (то, что мы указали ему аргументом). А это значение 21 000. Все, задача решена.

А вот этот текст - это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой "Начать заново" на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Пример 2 - приблизительный поиск

Ссылка на файл-пример приведена в конце описания этого примера.

Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия - 5%. Больше 300 - 6%. Больше 400 - 7%. И при любых продажах больше 500 - 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.

Для этого вначале нам потребуется составить таблицу с уровнями плана и ставками премии. Такая таблица будет выглядеть вот так:

Теперь мы можем найти премию Иванова С.А. в зависимости от его фактических результатов.

Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.

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

=ВПР(350;C3:D8;2;ИСТИНА)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=VLOOKUP(350;C3:D9;2;TRUE) .

Не забывайте, что в некоторых версиях Excel, вместо ";" должна использоваться ",".

После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.

Что значат все аргументы ВПР (VLOOKUP)?

1. Искомое значение. В нашем примере это 350 - это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы - только в первом столбце внутренней таблицы.

Кстати, на этот раз 350 у нас написано без кавычек, потому что 350 - это число, а в кавычки мы берем только текст.

2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае - ставка премии). Посчитать его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый - это "При продажах больше", второй - "ставка премии". Значит ставим цифру 2.

4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос "Мы же приблизительно ищем, верно?". ИСТИНА (TRUE) означает "да, приблизительно", ЛОЖЬ (FALSE) - "нет, мы ищем именно это". Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).

Что именно делает ВПР (VLOOKUP) в этом примере?

Как и в прошлый раз ВПР (VLOOKUP) последовательно просматривает все ячейки первого столбца нашей таблицы. Но на этот раз он не ищет точного соответствия, а выполняет такие проверки:

100, указанное в таблице, меньше 350? Да, - отвечает ВПР (VLOOKUP), - тогда запоминаем строку 1 и смотрим следующее значение.

200 меньше 350? Да, тогда забываем строку 1, запоминаем 2 и смотрим следующее.

300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее.

400 меньше 350? Нет! Тогда возвращаемся к строке 3.

И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).

Примерно вот так, можно описать логику вычислений ВПР (VLOOKUP) при работе с приблизительным поиском. Обратите внимание, что ВПР (VLOOKUP) не просматривает всю таблицу, а смотрит до тех пор пока значения не оказываются больше искомого. Иными словами, ВПР (VLOOKUP) ищет ближайшее наименьшее. Но для того, чтобы все работало правильно, вам нужно отсортировать таблицу по возрастанию значений в первом столбце.

Типичные ошибки

Какие ошибки мы чаще всего встречаем при работе с ВПР (VLOOKUP)?

1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: "Мы ищем приблизительно такое же значение, верно?", пусть это будет вам подсказкой.

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

Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так:

=ВПР("Иванов С.А.";C3:D9;2)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=VLOOKUP("Иванов С.А.";C3:D9;2) .

В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском).

Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так:

=ВПР(350;C3:D8;2;)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=VLOOKUP(350;C3:D9;2;) .

В этом случае ВПР (VLOOKUP) решит, что 4 аргумент равен нулю. А ноль ВПР (VLOOKUP) превратит в ЛОЖЬ (FALSE) и будет искать именно 350 в вашей таблице, а не ближайшее меньшее.

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

2. Другая частая ошибка - это попытка применять ВПР (VLOOKUP), чтобы найти второе или более позднее совпадение во внутренней таблице. Например, есть таблица для ведения складского учета, в которой отражаются поступающие материалы и цены на них. Выглядеть она будет примерно так:

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

3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).

Что происходит, когда ВПР (VLOOKUP) не находит значение?

В случаях, когда Excel, Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.

Как убрать ошибку #Н/Д (#N/A)

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

Во-вторых, проверьте правильно ли вы указали тип интервального просмотра: ИСТИНА (TRUE) или ЛОЖЬ (FALSE).

В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:

=ЕСЛИОШИБКА(ВПР("Иванова А.О.";C3:D9;2);"нет такого сотрудника")

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IFERROR(VLOOKUP("Иванова А.О.";C3:D9;2);"нет такого сотрудника") .

В этом случае Excel, Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.

Чем дополнить и заменить ВПР (VLOOKUP)?

Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET).

Основные варианты замены функции ВПР (VLOOKUP): функция ПРОСМОТР (LOOKUP), комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), а также в некоторых случаях - функция ГПР (HLOOKUP), функция СУММЕСЛИ (SUMIF).

При использовании функции ВПР (VLOOKUP) большую автоматизацию и надежность вашим файлам может добавить проверка данных и выпадающие списки, а также условное форматирование.

Быстрые ссылки на файлы-примеры:

Пример 1 - применение ВПР с точным поиском

Пример 2 - применение ВПР с приблизительным поиском

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Желаем вам успешной работы!

Ваш Виктор Рыбцев

и команда Учебного центра BRP ADVICE.