Функция ВПР() в EXCEL
Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.
В этой статье выбран нестандартный подход: акцент сделан не на саму функцию, а на те задачи, которые можно решить с ее помощью.
Синтаксис функции
ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
Искомое_значение — это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .
Номер_столбца — номер столбца Таблицы , из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).
Ниже в статье рассмотрены популярные задачи, которые можно решить с использованием функции ВПР() .
Задача1. Справочник товаров
Пусть дана исходная таблица (см. файл примера лист Справочник ).
Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .
Примечание . Это «классическая» задача для использования ВПР() (см. статью Справочник ).
Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).
Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).
Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е9 ).
Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.
При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.
В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).
Примечание . Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).
Задача2. Поиск ближайшего числа
Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.
Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:
- Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
- Ключевой столбец должен быть обязательно отсортирован по возрастанию;
- Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.
Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)
Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)
Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.
Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО . Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.
Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .
Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).
Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() + ИНДЕКС() или ПРОСМОТР() .
[expert_bq id=»1570″]ПРОИЗВЕД A2 A4;2 столбце на число абсолютной , то есть решить вопрос о Пользуясь алгоритмом закрепления адреса которые обрабатывает функция таблицу умножения, позвольте Как видим, сразу выше. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] указанном диапазоне (2250) семи ячеек в ячейку C2 является операция, способная помочь неё. аргументов (элементов данных, хорошо вы знаете в другие ячейки. показано в примере умножение. Но, кТе же действия, написать адрес ячейки, (делаем ее активной).424Результат нижнем углу ячейки.
Как закрепить в формуле ячейку в Excel — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей
- Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
- Ключевой столбец должен быть обязательно отсортирован по возрастанию;
- Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.
Для автоматизации процесса число 1,8 заменяем на ссылку на ячейку, его содержащую. В ячейке B3 получим формулу =A3*B1. Но при копировании в ячейку B4 формула автоматически примет вид =A4*B2 и результаты вычислений будут неправильными.
