Функция впр в Excel пошаговая инструкция

Функция подстановки значений (ВПР)

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

Так же мы имеем в отдельном файле прайс-лист на рекламную продукции.

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

Обратите внимание на то, что для корректной работы функции ВПР в заголовках таблицы не должно быть объединенных ячеек! Выберем ячейку в которую будем подставлять значения, взятые из другого файла. В начем случае это будет колонка «Цена». Выделим ячейку D3 (первую ячейку диапазона, в который необходимо подставить значения). В закладке Формулы найдем кнопку fx и нажмем ее. Появится диалоговое окно мастера фукнций.

Выберем категорию Ссылки и массивы, найдем там функцию ВПР и выделим ее.

Нажмем ОК. Появится окно для ввода аргументов функции. Первый аргумент называется «Искомое значение». В поле ввода напротив этого аргумента укажем блок ячеек столбца В. Сделаем это просто щелкнув на заголовке столбца мышкой.

Второй аргумент называется Таблица. Там необходимо указать диапазон ячеек таблицы, в которой содержится аргумент «Искомое значение» и то значение, которое нам необходимо подставить. Поскольку прайс-лист у нас находится на другом листе, то переходим на лист с названием «прас-лист» и выделяем там полностью столбцы А и В, т. к. в столбце А содержится аргумент «искомое значение» (тот параметр по которому EXCEL поймет, что определенная цена привязана именно к этому товару), а в столбце В содержится то значение, которое нам необходимо подставить в первую таблицу к каждому «искомому значению».

В поле ввода аргумента «Номер столбца» указываем «2», т.к. колонка с ценой в прайс-листе у нас — это второй по счету столбец.

В поле «Интервальный просмотр» пишем ЛОЖЬ, т.к. нам необходимо перенести точные значения, а не приблизительные

Нажмем ОК.

Мы видим, что в ячейку D3 подставилась цена, соответствующая в прайс-листе позиции «Каталог формат А4». Теперь нам осталось только растянуть эту формулу на весь диапазон ячеек, куда нам необходимо поставить значение цены.

Вот что у нас должно получиться.

В принципе можно было бы сказать, что на этом задача выполнена. Но есть одно маленькое НО. В столбце D3 работает функция, мы можем видеть это в строке формул. Это означает, что наши 2 файла неразрывно связаны, т.е. если поменять данные в прайс-листе, то обязательно изменятся и данные в нашем файле, за 15.01.2011. Этого лучше не допускать. Для этого выделим весь диапазон ячеек, в который мы подставили данные, щелкнем правой кнопкой мыши и выберем опцию «Копировать»

Далее не сбрасывая выделения с области ячеек снова щелкнем по ней правой кнопкой мыши и выберем опцию «Специальная вставка».

В появившемся окне установим галочку напротив опции «значения». Нажмем ОК.

Теперь мы видим, что в строке формул подставлены числовые значения, а не формула. Это значит, что связи между двумя файлами нет, а значит и нет угрозы изменения или исчезновения подтянутых нами значений при изменении, закрытии или перемещении «прайс-листа».

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть