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

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

Содержание

Немного о функции ВПР
Создаем шаблон
Вставляем функцию ВПР
Заполняем аргументы функции ВПР
Последний штрих…
Завершаем создание шаблона

Немного о функции ВПР

Итак, что же такое ВПР? Думаю, Вы уже догадались, что это одна из множества функций Excel.

Данная статья рассчитана на читателя, который владеет базовыми знаниями о функциях Excel и умеет пользоваться такими простейшими из них как SUM (СУММ), AVERAGE (СРЗНАЧ) и TODAY(СЕГОДНЯ).

По своему основному назначению, ВПР — это функция баз данных, т.е. она работает с таблицами или, проще говоря, со списками объектов в таблицах Excel. Что это могут быть за объекты? Да что угодно! Ваша таблица может содержать список сотрудников, товаров, покупателей, CD-дисков или звёзд на небе. На самом деле, это не имеет значения.

Вот пример списка или базы данных. В данном случае, это список товаров, которые продаёт вымышленная компания:

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

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

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

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

Другими словами, если Вы введёте в ячейку функцию ВПР и передадите ей в качестве аргумента один из уникальных идентификаторов Вашей базы данных, то в результате в ячейке появится какой-то кусок информации, связанный с этим уникальным идентификатором. Применительно к примеру, приведенному выше: если бы мы ввели в качестве аргумента значение из столбца Item Code, то как результат могли бы получить соответствующее ему описание товара (Description), его цену (Price), или наличие (In Stock). Какую именно информацию должна вернуть формула, Вы сможете решить в процессе её создания.

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

Создаем шаблон

Давайте создадим шаблон счёта, который мы сможем использовать множество раз в нашей вымышленной компании.

Для начала, запустим Excel…

… и создадим пустой счёт.

Вот как это должно работать: пользователь шаблона будет вводить коды товаров (Item Code) в столбец А. После чего система будет извлекать для каждого товара описание и цену, а далее рассчитывать итог по каждой строке. Количество необходимо указать самостоятельно.

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

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

Вставляем функцию ВПР

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

Далее делаем активной ту ячейку, в которой должна появиться информация, извлекаемая функцией ВПР из базы данных. Любопытно, что именно на этом шаге многие путаются. Поясню, что мы будем делать далее: мы создадим формулу, которая извлечёт из базы данных описание товара, код которого указан в ячейке A11. Куда мы хотим поместить это описание? Конечно, в ячейку B11. Следовательно, и формулу мы запишем туда же.

Итак, выделите ячейку B11:

Нам требуется открыть список всех существующих функций Excel, чтобы найти в нём ВПР и получить некоторую помощь в заполнении формулы. Для этого зайдите на вкладку Formulas (Формулы) и выберите команду Insert Function (Вставить функцию).

Появляется диалоговое окно, в котором можно выбрать любую существующую в Excel функцию. Чтобы найти то, что нам необходимо, мы можем ввести в поле Search for a function (Поиск функции) слово lookup (или поиск в русскоязычной версии), поскольку нужная нам функция – это функция поиска. Система покажет список всех связанных с этим понятием функций Excel. Найдите в списке VLOOKUP (ВПР), выберите её мышкой и нажмите ОК.

Появится диалоговое окно Function Arguments (Аргументы Функции), предлагающее ввести все необходимые аргументы для функции ВПР. Представьте себе, что это сама функция задаёт Вам следующие вопросы:

Какой уникальный идентификатор Вы ищите в этой базе данных?
Где находится база данных?
Какую информацию Вы бы хотели извлечь из базы данных?

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

Заполняем аргументы функции ВПР

Первый аргумент, который надо указать, это Lookup_value (Искомое_значение). Функция просит нас указать, где искать значение уникального кода товара, описание которого надо извлечь. В нашем случае, это значение в столбце Item code, которое мы ввели раньше в ячейку A11.

Нажмите на иконку выбора справа от строки ввода первого аргумента.

Затем кликните один раз по ячейке, содержащей код товара и нажмите Enter.

Значение ячейки A11 взято в качестве первого аргумента.

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

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

Далее мы выделяем все ячейки таблицы, кроме строки заголовков…

… и нажимаем Enter. В строке для ввода второго аргумента автоматически отобразится диапазон ячеек, в котором содержится вся база данных. В нашем случае это ‘Product Database’!A2:D7.

Теперь займёмся третьим аргументом Col_index_num (Номер_столбца). С помощью этого аргумента мы указываем функции ВПР, какой именно кусок информации из базы данных мы хотим извлечь. В данном случае нам необходимо извлечь описание товара (Description). Если Вы посмотрите на базу данных, то увидите, что столбец Description это второй столбец в таблице. Это значит, что для аргумента Col_index_num (Номер_столбца) мы вводим значение 2:

Важно заметить, что мы указываем значение 2 не потому, что столбец Description находится во втором по счету столбце от начала листа Excel, а потому, что он второй по счёту в диапазоне, который указан в качестве аргумента Table_array (Таблица) функции ВПР (первым является столбец с уникальным идентификатором). Если наша база данных будет начинаться где-то со столбца K листа Excel, то мы всё равно укажем значение 2 в этом поле.

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

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

Так как первый столбец нашей базы данных не отсортирован, мы вводим для этого аргумента значение FALSE (ЛОЖЬ):

Последний штрих…

Вот и всё! Мы ввели всю информацию, которая требуется функции ВПР, чтобы предоставить нам то значение, которое нас интересует. Жмите ОК и обратите внимание, что описание товара, соответствующее коду R99245, появилось в ячейке B11:

Созданная формула выглядит вот так:

Если мы введём другой код в ячейку A11, то увидим действие функции ВПР: в поле Description появится описание, соответствующее новому коду товара.

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

… а формула будет выглядеть так:

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

Если мы решили приобрести 2 единицы товара, то запишем 2 в ячейку D11. Далее вводим простую формулу в ячейку F11, чтобы посчитать итог по этой строке:

=D11*E11

… которая выглядит вот так:

Завершаем создание шаблона

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

Наш шаблон ещё не закончен полностью. Чтобы завершить его создание, сделаем следующее:

Удалим значение кода товара из ячейки A11 и значение 2 из ячейки D11. В результате созданные нами формулы сообщат об ошибке.Мы можем исправить это, разумно применив функции IF (ЕСЛИ) и ISBLANK (ЕПУСТО). Изменим нашу формулу с такого вида:
=VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)
=ВПР(A11;'Product Database'!A2:D7;2;ЛОЖЬ)

на такой вид:

=IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE))
=ЕСЛИ(ЕПУСТО(A11);"";ВПР(A11;'Product Database'!A2:D7;2;ЛОЖЬ))
Нам нужно скопировать формулы из ячеек B11, E11 и F11 на оставшиеся строки нашего шаблона. Обратите внимание, что если мы просто скопируем созданные формулы, то новые формулы не будут работать правильно с нашей базой данных. Это можно исправить, записав ссылки на ячейки как абсолютные. Другой способ, более продвинутый, это создать именованный диапазон для всех ячеек, вмещающих нашу базу данных (назовём его Products) и использовать имя диапазона вместо ссылок на ячейки. Формула превратится из такой:
=IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE))
=ЕСЛИ(ЕПУСТО(A11);"";ВПР(A11;'Product Database'!A2:D7;2;ЛОЖЬ))

… в такую:

=IF(ISBLANK(A11),"",VLOOKUP(A11,Products,2,FALSE))
=ЕСЛИ(ЕПУСТО(A11);"";ВПР(A11;Products;2;ЛОЖЬ))

…теперь можно смело копировать формулы в ячейки остальных строк нашего шаблона.
Также мы можем заблокировать ячейки с формулами (точнее разблокировать все ячейки, кроме нужных) и защитить лист, чтобы быть уверенными, что никто и никогда случайно не удалит наши формулы, когда будет наполнять шаблон.
Сохраним файл как шаблон, чтобы его мог использовать любой желающий в нашей компании.

Если подойти к работе с максимальной ответственностью, то можно создать базу данных всех наших клиентов еще на одном листе документа. А затем вводить идентификатор клиента в ячейку F5, чтобы автоматически заполнять ячейки B6, B7 и B8 данными о клиенте.