Функции Excel

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

Мастер-класс.

 

ВПР

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

 

Допустим, имеется прайс-лист на продукцию.

 

 

Вам поступает заказ от клиента, в котором тот указал несколько  артикулов товаров и нужное их кол-во.

 

 

 

 

Клиент просит рассчитать общую  стоимость и подготовить счет. Как это сделать? Можно вручную просмотреть прайс-лист и подобрать для каждого артикула наименование и цену, перемножить цену на количество и получить общую стоимость.

 

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

 

Для начала скопируем заказ и  прайс-лист на разные закладки одного документа эксель.

 

 

Для подбора цены для товара в колонке, предназначенной для цены товара напишем «=ВПР(». Затем мышкой указываем ячейку в которой находится артикул товара. Затем пишем «;».

 

 

 

 

 

 

 

Теперь идем на закладку с прайс-листом и выделяем на ней таблицу, начинающуюся со столбца с артикулом товара и включающую в себя колонку с ценой товара. При этом лучше выделять целиком столбцы, иначе придется дополнительно дорабатывать формулу с использованием знаков «$» (это тема для мастер-класса по написанию произвольных формул).

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

 

 

Ставим точку с запятой «;»  и пишем цифру «3» - номер колонки, в которой находится интересующее нас значение — цена товара.

 

 

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

 

 

Ставим закрывающую скобку и  нажимаем клавишу «Enter». Видно, что  программа указала какую-то цифру. Давайте теперь зайдем на закладку с прайс-листом и проверим, правильную ли цену предложила программа. Очевидно, что всё правильно.

 

 

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

 

 

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

 

 

 

Видно, что программа подтянула  цену для всех товаров в заказ.

 

Теперь в соседней колонке считаем  стоимость и общую сумму.

 

 

 

 

 

 

 

Задание 1

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

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

 

 

#Н/Д при использовании  ВПР.

При использовании функции ВПР  вы можете столкнуться с тем, что  вместо искомого значения программа выдала «#Н/Д» («нет данных»). Это происходит, если программа не нашла значения в указанной таблице. Причин для этого может быть несколько:

• искомое значение находится не в первом столбце указанной таблицы
• искомое значение в заказе и прайс-листе на самом деле не совпадают с точностью до символа, например, где-то введены лишние пробелы.
• таблица была выбрана не целиком по столбцам, а была указана как прямоугольник с ограниченным числом строк и сползла при протягивании. Фактически товар есть в исходно прайс-листе но не попал в указанный в функции ВПР диапазон. Здесь два решения — выделять целиком столбцы или до протягивания дорабатывать формулу символами «$».
• искомого значения просто нет в таблице.

 

Возьмем указанный выше пример и  добавим в него артикул, которого нет в прайс-листе. После протягивания формул мы получили вместо цены #Н/Д что даже не позволило нам увидеть общую сумму.

 

 

Как сделать, чтобы такой товар не портил документ и позволял видеть общую сумму. Вариантов два — либо просто убрать его из документа, либо воспользоваться функцией «ЕСЛИ» в сочетании с функцией «ЕНД».

 

 

 

В данном случае, если вместо цены программа  показала #Н/Д, то вместо суммы будет выведено пустое значение, а рядом с суммой программа высветит надпись — не найден товар по артикулу. Формула «=ЕСЛИ(ЕНД(C8);"";B8*C8)» читается следующим образом: если в ячейке C8 стоит значение #Н/Д, то ставить пустое значение «””», иначе ставить результат умножения значения ячеек B8 и C8.

 

 

 

Задание 2

Возьмите файл, созданный вами в  задании 1, и в артикул первого товара допишите цифру «1». Посмотрите, что изменилось. Теперь доработайте файл так, чтобы для всех артикулов, которых нет в прайс-листе в поле «сумма» программа выводила не «#Н/Д», а пустое значение, а рядом выводила надпись «нет в прайсе». Поэкспериментируйте с результатами, меняя артикулы товаров — добавляя к ним лишние символы и т. п. Результат покажите инструктору.

 

 

Подготовлено Прохором Лейкиным.