Функция ВПР в Excel для чайников

Многие наши ученики говорили нам, что очень хотят научиться использовать функцию ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные «чайники». Итак, приступим!

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

Что такое ВПР?

Прежде всего, функция ВПР позволяет искать определённую информацию в таблицах Excel. Например, если есть список товаров с ценами, то можно найти цену определённого товара.

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

Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:

=VLOOKUP(
=ВПР(

Добавляем аргументы

Теперь добавим аргументы. Аргументы сообщают функции ВПР, что и где искать.

Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame. Так как аргумент текстовый, мы должны заключить его в кавычки:

=VLOOKUP("Photo frame"
=ВПР("Photo frame"

Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16. Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).

=VLOOKUP("Photo frame",A2:B16
=ВПР("Photo frame";A2:B16

Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame. Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.

Третий аргумент – это номер столбца. Здесь проще пояснить на примере, чем на словах. Первый столбец диапазона – это 1, второй – это 2 и так далее. В нашем примере требуется найти цену товара, а цены содержатся во втором столбце. Таким образом, нашим третьим аргументом будет значение 2.

=VLOOKUP("Photo frame",A2:B16,2
=ВПР("Photo frame";A2:B16;2

Четвёртый аргумент сообщает функции ВПР, нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:

=VLOOKUP("Photo frame",A2:B16,2,FALSE)
=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)

Готово! После нажатия Enter, Вы должны получить ответ: 9.99.

Как работает функция ВПР?

Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame, функция переходит во второй столбец, чтобы найти цену.

ВПР – сокращение от Вертикальный ПРосмотр, VLOOKUP – от Vertical LOOKUP.

Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:

=VLOOKUP("T-shirt",A2:B16,2,FALSE)
=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)

или:

=VLOOKUP("Gift basket",A2:B16,2,FALSE)
=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)

Другой пример

Следующий пример будет чуть потруднее, готовы? Представьте, что в таблице появился третий столбец, который хранит категорию каждого товара. На этот раз, вместо цены, мы определим категорию.

Чтобы определить категорию, необходимо изменить второй и третий аргументы в нашей формуле. Во-первых, изменяем диапазон на A2:C16, чтобы он включал третий столбец. Далее, изменяем номер столбца на 3, поскольку категории содержатся в третьем столбце.

=VLOOKUP("Gift basket",A2:C16,3,FALSE)
=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)

Когда Вы нажмёте Enter, то увидите, что товар Gift basket находится в категории Gifts.

Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:

• Цену coffee mug
• Категорию landscape painting
• Цену serving bowl
• Категорию scarf

Теперь Вам известны основы работы с функцией ВПР в Excel. Продвинутые пользователи используют ВПР самыми различными способами, но, на самом деле, многое можно сделать и с теми техниками, что мы описали. Например, если у Вас есть список контактов, то Вы сможете найти телефонный номер человека по его имени. Если же в списке контактов есть столбец с адресом электронной почты или названием компании, Вы можете искать и эти данные, просто изменив второй и третий аргументы, как мы уже делали в предыдущем примере. Возможности Excel безграничны!