В прошлой заметке я подробно разобрал предназначение и синтаксис функции ВПР. Сейчас давайте рассмотрим хоть и упрощенный, но вполне реальный пример ее использования.
Есть три таблицы - прайс-лист, перечень сотрудников и таблица заказов.
Все три таблицы вполне могут располагаться на разных листах книги или даже находится в разных файлах. Для удобства и простоты я их разместил на одном листе.
Наша задача состоит в следующем:
1. Подтянуть цены из прайс-листа в таблицу с заказами
2. Рассчитать сумму каждого заказа
3. Определить город заказа.
Последнюю задачу легко сделать по перечню сотрудников, так как каждый менеджер отвечает за отдельный город.
Приступим.
Подтянем цену товаров из прайс-листа. Для этого создадим формулу с функцией ВПР.
Мы должны искать цену товара, поэтому в качестве искомого значения укажем ячейку с его наименованием. Далее нужно определить диапазон, в котором будет производиться поиск - это таблица прайс-листа.
Напомню, что нам нужны только значения, без заголовков самой таблицы.
Таблица прайс-листа состоит из двух столбцов. В первом мы производим поиск, а из второго берем значение цены, поэтому указываем 2. Ну и нам нужно точное совпадение, поэтому последний параметр - 0 или ЛОЖЬ.
Видим, что цена была подтянута верно. Осталось раскопировать формулу по диапазону и тут может случиться проблема.
Дело в том, что в качестве таблицы в формуле мы указали диапазон ячеек прайс-листа, но оставили ссылки относительными. Это означает, что при копировании формулы автозаполнением «сползал» и выбранный ранее диапазон.
Для решения проблемы сделаем ссылку на прайс-лист абсолютной - откорректируем формулу в первой ячейке столбца, выделим диапазон и нажмем клавишу F4.
Повторим копирование формулы и получим верный результат.
Теперь произведем расчет суммы заказа, умножив количество на цену.
По аналогии заполним столбец с городами.
У нас диапазон включает всего три столбца, но при выборе большого диапазона, который находится не вначале листа и, возможно, по ширине больше, нежели ваш экран, может возникнуть проблем с определением количеств столбцов в диапазоне. Ведь после выбора диапазона мы должны будем указать номер нужного нам столбца. Ориентироваться на размер выделяемого диапазона можно по подсказке, которая появляется ниже.
В ней выводится количество выделенных строк (Row) и столбцов (Column), поэтому легко можно определить, какой именно номер столбца диапазона нам будет нужен.
Не забываем зафиксировать выбранный диапазон абсолютными ссылками. Нужный нам столбец будет третьим в выделенном диапазоне, поэтому указываем 3.
Задача выполнена.
Ну а в следующей заметке расскажу об основных причинах, почему функция ВПР не работает или работает неверно, а также о недостатках этой функции.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм