ВПР

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

Замечания

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

Пример 1

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

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

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

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

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

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

В данном примере выполняется поиск значений в столбце «Плотность» для нахождения соответствующих значений в столбцах «Вязкость» и «Температура» в таблице атмосферных свойств. (Значения даны для воздуха при температуре 0°C и давлении в 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) Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке (2,17)
=ВПР(1;A2:C10;3;ИСТИНА) Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке (100)
=ВПР(0,7;A2:C10;3;ЛОЖЬ) Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке (#Н/Д)
=ВПР(0,1;A2:C10;2;ИСТИНА) Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке (#Н/Д)
=ВПР(2;A2:C10;2;ИСТИНА) Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равно 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке (1,71)

Пример 2

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

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

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

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

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

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

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

 
1
2
3
4
5
6
A B C D
Код изделия Изделие Стоимость Наценка
ST-340 Коляска 1 456,70р. 30%
BI-567 Нагрудник 35,60р. 40%
DI-328 Пеленки 214,50р. 35%
WI-989 Салфетки 51,20р. 40%
AS-469 Аспиратор 25,60р. 45%
Формула Описание (результат)
= ВПР("DI-328"; A2:D6; 3; ЛОЖЬ) * (1 + ВПР("DI-328"; A2:D6; 4; ЛОЖЬ)) Вычисление розничной цены пеленок путем добавления процента наценки к стоимости (289,6р.)
= (ВПР("WI-989"; A2:D6; 3; ЛОЖЬ) * (1 + ВПР("WI-989"; A2:D6; 4; ЛОЖЬ))) * (1 - 20%) Вычисление отпускной цены салфеток путем вычитания указанной скидки из розничной цены (57,3р.)
=ЕСЛИ(ВПР(A2; A2:D6; 3; ЛОЖЬ) >= 200; "Наценка составляет " & 100 * ВПР(A2; A2:D6; 4; ЛОЖЬ) &"%"; "Стоимость меньше 200р.") Если стоимость изделия больше или равна 200р., отображается строка «Наценка составляет nn%»; в противном случае отображается строка «Стоимость меньше 200р.» (наценка составляет 30%)
= ЕСЛИ(ВПР(A3; A2:D6; 3; ЛОЖЬ) >= 200; "Наценка составляет: " & 100 * ВПР(A3; A2:D6; 4; ЛОЖЬ) &"%"; "Стоимость составляет " & ВПР(A3; A2:D6; 3; ЛОЖЬ)) Если стоимость изделия больше или равна 200р., отображается строка «Наценка составляет nn%»; в противном случае отображается строка «Стоимость составляет n,nn» (стоимость составляет 35,6)

Пример 3

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

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

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

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

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

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

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

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

При наличии сотрудника под номером 5 отображается его фамилия; в противном случае отображается сообщение «Сотрудник не найден» (Песоцкий).

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

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

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

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

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