Как пользоваться функцией ВПР в Excel

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

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

1. Для этих целей, необходим параметр первой таблицы привести в соответствующий нам вид. Для удобства расчета добавляем столбцы и присваиваем им соответствующую буквенную функцию: «Цена», а также «Стоимость/Сумма» и указываем денежный эквивалент новых ячеек.

2. Далее, обычным действием выделяем ячейку «Цена» и В нашем конкретном примере эта ячейка будет под номером D2. После этого мы вызываем команду «Мастер функций», нажав при этом на «fx», то есть на те кнопки, которые расположены в начале строки. Можно также использовать стандартную команду, нажав одновременно на кнопки SHIFT+F3. После этого отправляемся в категорию «Ссылки и массивы», и находим нам требуемую опцию ВПР. Далее нажимаем на кнопку подтверждения ОК. Можно также воспользоваться этой функцией, путем нажатия на кнопки, используя переход из массива древа закладок «Формулы». Здесь нам потребуется снова найти категорию «Ссылки и массивы» и выполнить указанный механизм выше.

3. После этого перед нами откроется окно с назначенными аргументами искомой функции. Обращаем внимание на поле «Искомые значения», и выберем данные из ранее сформированного первого столбца с табличной функцией поступившего на наш склад материала. Для нас это будет тот блок данных, который необходим для расчета в Excel, то есть для поиска во второй таблице.

4. Далее переходим к расчету данных аргумента – то есть блок «Таблица». Это наш разработанный прайс-лист на начальном этапе работы. Устанавливаем курсор в поле аргумента. После этого осуществляем режим перехода на поле листа с ценами. Снова выделяем диапазон с названиями материалов и ценами. Указываем таблице, какие функции необходимо сопоставить.

5. Для того, чтобы наш Excel смог ссылаться непосредственно на эти параметры, рекомендуется зафиксировать указанную ссылку. Для этого снова выделяем функцию ячейки «Таблица» и нажимая клавишу F4. У нас появится искомый значок в виде $.

6. Далее мы снова обращаемся к функции поля аргумента под названием «Номер столбца», и присваиваем ему цифровое значение «2». Тут будут находиться те метаданные, которые нам потребуется затем «подтянуть» в первую таблицу. Обратите внимание, что появление функции «Интервальные значения» - это будет для нас ЛОЖЬ. То есть, учитываем, что нам нужны ТОЧНЫЕ параметры, а не приблизительные.

Далее нам нужно будет нажать на кнопку подтверждения ОК, после чего механически «размножаем» по всему указанному столбцу, и механикой «цепляния» мыши захватываем правый нижний угол и тянем его вниз. В итоге у нас получается вот такая вот таблица.

Из этого следует, что теперь найти нам необходимую стоимость товара не составит труда, то есть «количество» * «цена».
Отсюда видно, что функция ВПР Excel связала нам метаданные двух разнотипных ячеек. Но, если изменится база данных прайс-листа, то соответственно будут изменены в ценовом эквиваленте поступившие на склад материалы и товары., то есть поступившие по факту, или «на сегодня». Чтобы избежать этого досадного факта, нужно будет выполнить следующий порядок для таблицы.

1. Столбец с указанными ценами выделяем принудительно.
2. При помощи кнопки правой мыши выполняем операцию для таблицы – «Копировать».
3. Не снимая образованное выделение, при помощи правой кнопки мыши ищем опцию – «Специальная вставка».
4. Нам потребуется установить галочку напротив аргумента – «Значение» и нажимаем ОК.

Все в ячейках исчезли ненужные нам формулы, останутся только указанные значения.

Широкие возможности функции ВПР позволяет выполнить огромное количество задач:

• сопоставить данные таблицы;
• выбрать расчет операций таблицы не по всем идентификаторам, а по 2,3 и т.д.;
• настроить опции так, чтобы была видна одна база метаданных таблицы, например, «цена».

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