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

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

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

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

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

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

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

Функция ВПР в Excel

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

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

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

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

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

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

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

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

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

Функция ВПР в Excel

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

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

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

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

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

=D11*E11

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

Функция ВПР в Excel

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

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

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

  1. Удалим значение кода товара из ячейки A11 и значение 2 из ячейки D11. В результате созданные нами формулы сообщат об ошибке.

    Функция ВПР в Excel

    Мы можем исправить это, разумно применив функции 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;ЛОЖЬ))

  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;ЛОЖЬ))

    …теперь можно смело копировать формулы в ячейки остальных строк нашего шаблона.

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

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

Функция ВПР в Excel

03.02.2015 12:48
9176

Комментарии

Нет комментариев. Ваш будет первым!