Вместо спойлера: публикую материал с небольшими правками со своего заброшенного блога. В случае положительной реакции читателей - продолжу публикации на данную тему.
Мне часто приходится работать с большими массивами данных в файлах Excel. Зачастую, делая отчет, необходимо воспользоваться выборочными данными из одной таблицы (т.е. фактически вытянуть данные из таблицы, отвечающие нужным требованиям).
Разберем интересный пример, дающий представление о работе с функцией =ВПР().
Задача: сделать систему, для автоматического формирования заказа.
Представим, что у нас есть книга в Excel, один лист которой представляет собой прайс-лист условного товара и имеет следующую структуру:
На другом листе нам необходимо сделать форму, в которой менеджер должен выбрать из списка товар и внести его количество. Наименование товаров, единица измерения каждого вида товара и цена за единицу должны подтягиваться из прайс-листа.
Форма будет иметь следующий вид:
Начнем с того, что присвоим таблице с прайс-листом имя (тут как это делать) - "Прайс". Именованный диапазон должен охватывать все три столбца.
Переходим к листу с формой Заказа.
Далее, нам необходимо сделать ниспадающий список с наименованиями товаров из прайс-листа в столбце "В". Для этого выделяем ячейку, или несколько в столбце В, начиная с 4, чтобы попасть в шаблон формы. Переходим в главном меню во вкладку "Данные" и нажимаем на "Проверку данных". В открывшемся окне выбираем следующие параметры:
Тип данных: Список, Источник - столбец "А" в прайс-листе, за исключением 1 строки с названием столбца. Далее - ОК. Если все сделали правильно, то при выделении ячеек в столбце "Наименование товара" в листе Заказ появятся список, как на рисунке ниже:
Список готов! Теперь настало время использовать =ВПР().
Встроенная в Excel функция =ВПР() позволяет найти в левом столбце таблицы значение и вернуть данные из других столбцов, соответствующие найденному значению.
Синтаксис функции:
=ВПР(искомое значение; таблица; номер_столбца;[интервальный_просмотр])
Где:
1. "искомое значение" - значение, которое необходимо найти в левом столбце таблицы;
2. "таблица"- таблица, в которой осуществляется поиск и из которой возвращаются значения;
3. "номер_столбца" - номер столбца, значение из которого необходимо вернуть. Столбцы нумеруются с лева на право начиная с 1 и далее по порядку.;
4. "интервальный просмотр" - не обязательный параметр. Определяет правила поиска в левом столбце: если "Ложь" то точное совпадение, если "Истина" (оно стоит по умолчанию) - то приблизительное значение
Вернемся к нашей форме Заказов. Мы при помощи списка вытягиваем название товара. Теперь нам нужно вытянуть единицу измерения этого товара и стоимость единицы товара. Для этого, в ячейке "С4" введем формулу: =ВПР(B4;Прайс;2;ЛОЖЬ) для вытягивания из второго столбца типа единицы измерения выбранного товара, а в ячейке "Е4" введем функцию: =ВПР(B4;Прайс;3;ЛОЖЬ) для вытягивания цены за единицу.
Если сейчас внести эти формулы, перед тем, как выбрать товар, функция =ВПР() вернет значение "#Н/Д", т.к. искомое значение не найдено. Для обхода этого, нам нужно будет воспользоваться функцией =ЕСЛИ(), для этого изменим формулы в ячейках "С4" и "Е4" следующим образом: =ЕСЛИ(B4<>0;ВПР(B4;Прайс;2;ЛОЖЬ);"") и =ЕСЛИ(B4<>0;ВПР(B4;Прайс;3;ЛОЖЬ);""), соответственно.
В ячейке "F4" необходимо посчитать произведение ячеек "D" и "E", т.е. =D*E
И формулы и списки в ячейках можно копировать в соседние без всяких изменений. Для примера сделаем это до строки 7.
Для подведения итогов, в ячейках D8 и F8 воспользуемся формулой =СУММ.
В итоге мы получили полностью автоматическую форму для формирования заказов.
Благодаря функции =ВПР() можно существенно упростить себе жизнь, избавится от большого количество бесполезной работы.
Так, воспользовавшись данными из примера, можно легко сделать форму для формирования Счетов. Для этого достаточно скачать типовую форму Счета, создать лист с базой данных поставщиков, со столбцами "Наименование", "Инн", "КПП", "Адрес" и т.д. и при помощи списков и функции =ВПР() подтягивать данные.
У функции =ВПР() есть зеркальный аналог - функция =ГПР(), которая ищет данные по строкам.