Изучим работу функции ВПР в Excel, которая связывает две таблицы и позволяет подтянуть данные из одной таблицы в другую.
Приветствую всех, дорогие читатели блога Tutorexcel.ru!
Мне кажется, что если подбирать ассоциацию к слову Excel, то с большим преимуществом победит вариант ВПР. Впрочем, уверен и в обратном, услышав слово ВПР сразу становится понятно, что речь идет про Excel.
Вот и мы сегодня поговорим про работу этой замечательной функции в Excel, которая позволяет быстро сопоставлять данные между несколькими таблицами, и на самом деле используется примерно в 100% всех файлов, наверное за исключением пустых книг 🙂
В общем давайте начинать и на примерах разберем пошаговую инструкцию по функции ВПР в Excel.
Функция ВПР в Excel с примерами
- Искомое значение(обязательный аргумент) — значение, которое должно быть найдено в первом столбце массива;
Обычно это числовое или текстовое значение. - Таблица(обязательный аргумент) — таблица с текстом, числами или логическими значениями, в которой производится поиск данных;
- Номер столбца(обязательный аргумент) — номер столбца в таблице, из которого нужно вернуть значение;
- Интервальный просмотр(необязательный аргумент) — логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА) должен производиться поиск в первом столбце.
Здесь необходимо цифрой проставить, в каком по счету столбце, от самого левого, необходимо взять значение для подстановки как результат исчисления. В вышеуказанном примере — это второй столбец, в строку аргументов необходимо проставить цифру 2. Если бы между столбцом «Код» и «Цена» был бы еще один столбец, то нужно было бы проставить цифру 3 и так далее.
Использование ВПР в программе Excel
Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.
Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.
Первая – это список предметов, единицы их измерения и количество.
Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.
Чтобы подсчитать сколько стоит продукция, следует информацию из второй вставить в первую, и с помощью простого умножения произвести расчёт.
– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.
– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).
Важно! Эти показатели фиксируются, чтобы именно по ним производились расчёты программой Эксель.
Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.
Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).
Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».
Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.
Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.
Программа подстановки данных из одного файла в другой (замена функции ВПР) | Программы и макросы для Excel
Игорь, добрый день. Ставлю в программе в настройках галочку «Запускать настройку автоматически вместе с эксель» но все равно, после каждого закрытия эксель настройка перестает подтягиваться. Ее каждый раз надо запускать вновь. Почему? Раньше было все ок
ВПР эксель как пользоваться
Функцию ВПР обычно вставляют в ячейку, в которой будет итоговый результат, в нашем случае зарплата конкретного сотрудника в ведомости.
Теперь нужно последовательно заполнить четыре аргумента функции ВПР.
Искомое значение – значение для поиска. Значение, которое вы хотите найти. Поиск этого значения будет в первом столбце диапазона ячеек, указанного в аргументе — Таблица.
В нашем примере с зарплатной ведомостью – это табельный номер первого сотрудника в ведомости. Поэтому мы в этом аргументе можем просто указать адрес ячейки табельного в таблице ведомости.
Таблица – диапазон ячеек, в котором будет выполнен поиск искомого значения и возвращаемого значения с помощью функции ВПР. Первый столбец в диапазоне ячеек должен содержать искомое значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.
В нашем случае – это таблица общего реестра предприятия. При этом первым мы будем делать столбец, в котором функция будет искать табельный номер. Последний столбец не так важен, главное, чтобы в таблицу входил столбец, из которого мы будем забирать значение.
Если Ваша формула будет протягиваться, то обязательно закрепите диапазон таблицы используя клавишу F4.
Номер столбца – номер столбца начиная с 1 для самого левого столбца Таблицы, содержащего возвращаемое значение.
В текущем примере у нас таблица начинается со столбца «Табельный номер» и заканчивается столбцом «Зарплата», поэтому номер столбца будет 2. Т.к. он второй в нашей таблице.
Интервальный просмотр – число 0 или 1.
0 (Ложь [False]) Точное совпадение–осуществляет поиск точного значения в первом столбце(если не находит –#Н/Д [#N/A]).
1 (Истина [True]) Приближенное соответствие–если нет совпадения, то выдает max_значение
В примере с ведомостью нам нужно строгое соответствие табельного номера, как и в большинстве случаев на практике. Мы обязательно сделаем отдельную статью, про то, как правильно использовать 1 в этом аргументе.
А пока просто запомните, что здесь всегда ставим 0.
Аналог впр в vba
При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G500:$H$12 теперь $I500:$J$14 .
