ВПР

Ищет значение в первом столбце массива таблица и возвращает значение в той же строке из другого столбца массива «таблица».

«В» в ВПР обозначает «вертикальный». ВПР используется вместо ГПР, если значения для сравнения находятся в столбце слева от данных, которые нужно найти.

Синтаксис

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Искомое_значение.    Значение, которое должно быть найдено в первом столбце массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) « таблица». Искомое_значение может быть значением или ссылкой. Если искомое значение меньше наименьшего значения в первом столбце массива «таблица», ВПР возвращает значение ошибки #Н/Д.

Таблица.    Два или более столбцов данных. Можно использовать ссылку на интервал или имя интервала. Значения в первом столбце массива «таблица» являются значениями, поиск которых выполняется с помощью аргумента «искомое_значение». Эти значения могут быть текстовыми строками, числами или логическими значениями. Текстовые строки сравниваются без учета регистра букв.

Номер_столбца.    Номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца»:

  • Меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!.
  • Больше, чем количество столбцов массива «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотр.    Логическое значение, которое определяет, нужно ли, чтобы функция ВПР искала точное или приближенное соответствие:

  • Если этот аргумент имеет значение ИСТИНА или опущен, возвращается точное или приблизительно соответствующее значение. Если точное соответствие не найдено, то возвращается следующее максимальное значение, которое меньше, чем искомое_значение.

Значения в первом столбце массива «таблица» должны быть отсортированы по возрастанию. В противном случае ВПР может возвратить неправильные результаты. Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По возрастанию. Дополнительные сведения см. в разделе Порядок сортировки по умолчанию.

  • Если значение этого аргумента равно ЛОЖЬ, ВПР вернет только точное соответствие. В этом случае значения в первом столбце массива «таблица» не обязательно должны быть отсортированы. Если в первом столбце массива «таблица» аргументу «искомое_значение» соответствует два и более значений, используется первое найденное значение. Если найти точное соответствие не удается, то возвращается значение ошибки #Н/Д.

Замечания

  • При поиске текстовых значений в первом столбце массива «таблица» убедитесь, что в данных в первом столбце массива «таблица» отсутствуют пробелы в начале и конце строки, несовместимые знаки прямых ( ' или " ) и изогнутых ( ‘ или “) кавычек или непечатаемые знаки. В подобных случаях ВПР может вернуть неправильное или неожиданное значение.
  • При поиске числовых значений или дат убедитесь, что данные в первом столбце массива «таблица» хранятся не как текстовые значения. В этом случае ВПР может вернуть неправильное или неожиданное значение. Дополнительные сведения см. в разделе Преобразование чисел из текстового формата в числовой.
  • Если массив «интервальный_просмотр» имеет значение ЛОЖЬ, а значения массива «интервальный_просмотр» имеют текстовый формат, в массиве «интервальный_просмотр» можно использовать подстановочные знаки, вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти вопросительный знак или звездочку, то следует поставить перед ними знак тильда (~).

Пример 1

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

ПоказатьКопирование примера

  • Создайте пустую книгу или лист.
  • Выделите пример в разделе справки.

 Примечание.   Не выделяйте заголовок строки или столбца.

Выделение примера в справке

Выделение примера в справке
  • Нажмите сочетание клавиш CTRL+C.
  • На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
  • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (тупое ударение) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Отобразить формулы.

В данном примере выполняется поиск в столбце «Плотность» таблицы свойств атмосферы, чтобы найти соответствующие значения в столбцах «Вязкость» и «Температура». (Значения приведены для воздуха при 0 градусов Цельсия на уровне моря, или давлении 1 атмосфера.)

 
1
2
3
4
5
6
7
8
9
10
А B C
Плотность Вязкость Температура 
0,457 3,55 500
0,525 3,25 400
0,616 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
1,09 1,95 50
1,29 1,71 0
Формула Описание (результат)
=ВПР(1;A2:C10;2) Используя приближенное соответствие, ищет значение 1 в столбце A, находит максимальное значение, меньшее или равное 1 в столбце A (0,946), а затем возвращает значение из столбца B в той же строке (2,17).
=ВПР(1;A2:C10;3;ИСТИНА) Используя приближенное соответствие, ищет значение 1 в столбце A, находит максимальное значение, меньшее или равное 1 в столбце A (0,946), а затем возвращает значение из столбца C в той же строке (100).
=ВПР(0,7;A2:C10;3;ЛОЖЬ) Используя точное соответствие, ищет значение 0,7 в столбце A. Так как в столбце A точное соответствие отсутствует, возвращается сообщение об ошибке (#Н/Д).
=ВПР(0,1;A2:C10;2;ИСТИНА) Используя приближенное соответствие, ищет значение 0,1 в столбце A. Так как 0,1 меньше, чем наименьшее значение в столбце A, возвращается сообщение об ошибке (#Н/Д).
=ВПР(2;A2:C10;2;ИСТИНА) Используя приближенное соответствие, ищет значение 2 в столбце A, находит максимальное значение, меньшее или равное 2 в столбце A (1,29), а затем возвращает значение из столбца C в той же строке (1,71).

Пример 2

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

ПоказатьКопирование примера

  • Создайте пустую книгу или лист.
  • Выделите пример в разделе справки.

 Примечание.   Не выделяйте заголовок строки или столбца.

Выделение примера в справке

Выделение примера в справке
  • Нажмите сочетание клавиш CTRL+C.
  • На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
  • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (тупое ударение) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Отобразить формулы.

В данном примере выполняется поиск в столбце «Код товара» таблицы товаров для детей и определяются соответствующие значения в столбцах «Цена» и «Наценка» для определения цен и проверки условий ошибок.

 
1
2
3
4
5
6
A B C D
Код товара Товар Цена Наценка
ST-340 Легкая коляска $145,67 30%
BI-567 Нагрудник $3,56 40%
DI-328 Пеленки $21,45 35%
WI-989 Платки $5,12 40%
AS-469 Аспиратор $2,56 45%
Формула Описание (результат)
= ВПР("DI-328", A2:D6, 3, ЛОЖЬ) * (1 + ВПР("DI-328", A2:D6, 4, ЛОЖЬ)) Вычисляет розничную цену пеленок путем добавления доли наценки к цене ($28,96).
= (ВПР("WI-989", A2:D6, 3, ЛОЖЬ) * (1 + ВПР("WI-989", A2:D6, 4, ЛОЖЬ))) * (1 - 20%) Вычисляет отпускную цену платков путем вычитания указанной скидки из розничной цены ($5,73).
= ЕСЛИ(ВПР(A2, A2:D6, 3, ЛОЖЬ) >= 20, "Наценка равна " & 100 * ВПР(A2, A2:D6, 4, ЛОЖЬ) &"%", "Цена ниже $20,00") Выводит строку «Наценка равна nn%», если цена товара больше или равна $20,00, в противном случае выводит строку «Цена ниже $20,00» (наценка равна 30%).
= ЕСЛИ(ВПР(A3, A2:D6, 3, ЛОЖЬ) >= 20, "Наценка равна: " & 100 * ВПР(A3, A2:D6, 4, ЛОЖЬ) &"%", "Цена равна $" & ВПР(A3, A2:D6, 3, ЛОЖЬ)) Выводит строку «Наценка равна nn%», если цена товара больше или равна $20,00, в противном случае выводит строку «Цена равна $n,nn» (цена равна $3,56).

Пример 3

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

ПоказатьКопирование примера

  • Создайте пустую книгу или лист.
  • Выделите пример в разделе справки.

 Примечание.   Не выделяйте заголовок строки или столбца.

Выделение примера в справке

Выделение примера в справке
  • Нажмите сочетание клавиш CTRL+C.
  • На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
  • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (тупое ударение) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Отобразить формулы.

В данном примере выполняется поиск кода столбца таблицы сотрудников и соответствующих значений в других столбцах для вычисления возраста и проверки условий ошибок.

 
1
2
3
4
5
6
7
A B C D E
Код Фамилия Фамилия Должность Дата рождения
1 Белова Мария Представитель 08.12.1968
2 Новиков Павел Вице-президент 19.02.1952
3 Крылова Ольга Представитель 30.08.1963
4 Воронова Дарья Представитель 19.09.1958
5 Кротов Андрей Менеджер по продажам 04.03.1955
6 Акбаев Иван Представитель 02.07.1963
Формула Описание (результат)
=ЦЕЛОЕ(ДОЛЯГОДА(ДАТА(2004,6,30), ВПР(5,A2:E7,5, ЛОЖЬ), 1)) Для 2004 финансового года находит возраст сотрудника с кодом равным 5. Использует функцию ДОЛЯГОДА для вычитания даты рождения из даты окончания финансового года и выводит результат как целое с помощью функции ЦЕЛОЕ (49).
=ЕСЛИ(ЕНД(ВПР(5,A2:E7,2,ЛОЖЬ)) = ИСТИНА, "Сотрудник не найден", ВПР(5,A2:E7,2,ЛОЖЬ))

При наличии сотрудника с кодом 5 выводит фамилию сотрудника, в противном случае выводит сообщение «Сотрудник не найден» (Кротов).

Функция ЕНД возвращает значение ИСТИНА, когда функция ВПР возвращает значение ошибки #Н/Д.

=ЕСЛИ(ЕНД(ВПР(15,A3:E8,2,ЛОЖЬ)) = TRUE, "Сотрудник не найден", ВПР(15,A3:E8,2,ЛОЖЬ))

При наличии сотрудника с кодом 15 выводит фамилию сотрудника, в противном случае выводит сообщение «Сотрудник не найден» (Сотрудник не найден).

Функция ЕНД возвращает значение ИСТИНА, когда функция ВПР возвращает значение ошибки #Н/Д.

=ВПР(4,A2:E7,3,ЛОЖЬ) & " " & ВПР(4,A2:E7,2,ЛОЖЬ) & " — это " & ВПР(4,A2:E7,4,ЛОЖЬ) & "." Для сотрудника с кодом 4 объединяет значения трех ячеек в законченное предложение (Дарья Воронова — это Представитель.).

 Примечание.   Первая формула в приведенном выше примере использует функцию ДОЛЯГОДА. Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа».

ПоказатьИнструкции

  1. В меню Сервис выберите команду Надстройки.
  2. В списке Доступные надстройки выберите Analysis ToolPak и нажмите кнопку ОК.
  3. Выполните инструкции программы установки, если это необходимо.
 
 
Применимо к:
Excel 2003