В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Она ищет значение в первом столбце массива таблица и возвращает значение в той же строке из другого столбца массива «таблица».
«В» в ВПР обозначает «вертикальный». ВПР используется вместо ГПР, если значения для сравнения находятся в столбце слева от данных, которые нужно найти.
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение. Значение, которое должно быть найдено в первом столбце массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) «таблица». Искомое_значение может быть значением или ссылкой. Если искомое значение меньше наименьшего значения в первом столбце массива «таблица», ВПР возвращает значение ошибки #Н/Д.
Таблица. Два или более столбцов данных. Можно использовать ссылку на интервал или имя интервала. Значения в первом столбце массива «таблица» являются значениями, поиск которых выполняется с помощью аргумента «искомое_значение». Эти значения могут быть текстовыми строками, числами или логическими значениями. Текстовые строки сравниваются без учета регистра букв. Если функция ВПР будет использована для большой таблицы и ее внесение будет осуществлено процедурой заполнения, необходимо ссылку на диапазон сделать АБСОЛЮТНОЙ.
Номер_столбца. Номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца»:
Меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!.
Больше, чем количество столбцов массива «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.
Интервальный_просмотр. Логическое значение, которое определяет, нужно ли, чтобы функция ВПР искала точное или приближенное соответствие:
Если этот аргумент имеет значение ИСТИНА или опущен, возвращается точное или приблизительно соответствующее значение. Если точное соответствие не найдено, то возвращается следующее максимальное значение, которое меньше, чем искомое_значение.
Если значение этого аргумента равно ЛОЖЬ, ВПР вернет только точное соответствие. В этом случае значения в первом столбце массива «таблица» не обязательно должны быть отсортированы. Если в первом столбце массива «таблица» аргументу «искомое_значение» соответствует два и более значений, используется первое найденное значение. Если найти точное соответствие не удается, то возвращается значение ошибки #Н/Д.
Задание к работе
1) Создайте таблицу по образцу табл. 5.1.
2) Заполните произвольно столбец продаж табл. 5.1 на 50 клиентов.
3) Скопируйте на второй лист рабочей книги созданную таблицу.
4) Удалите в скопированной таблице произвольно строки по 10 клиентам.
5) На первом листе в столбце «Признак потери» с помощью функции ВПР определить удаленных клиентов.
Таблица 5.1
Клиенты | Объем продаж | Признак потери |
Клиент1 |
|
|
Клиент2 |
|
|
Клиент3 |
|
|
Клиент4 |
|
|
.. |
|
|
.. |
|
|
Клиент49 |
|
|
Клиент50 |
|
|
Контрольные вопросы
1) Основное применение функции «ВПР».
2) Каково назначение параметра функции «ВПР» интервальный_просмотр?
3) Как и когда еще можно использовать функцию «ВПР»?