1918
14 Мая 2018 в 12:0014.05.2018 в 12:00

Функция ВПР в Excel

Содержание

Для студентов, экономистов и всех тех, кто в ходе своей работы использует достаточно большие массивы данных (таблицы), была создана очень полезная функция Excel вертикальный поиск решения (ВПР). Она применяется для поиска нужного значения в таблицах и имеет 4 аргумента:

  • Искомое_значение – содержимое ячейки, которое необходимо найти в крайнем левом столбце таблицы.
  • Таблица – это массив данных, в рамках которого происходит поиск нужного значения. Координаты задаются адресами верхней левой и нижней правой ячеек в Аргументах функции.
  • Номер_столбца – аргумент отсчитывается от крайнего левого столбца Таблицы (предыдущий параметр функции). Он всегда задается числом.
  • Интервальный_просмотр – необязательный параметр, определяющий вид поиска. Может принимать два значения: ЛОЖЬ и ИСТИНА. ЛОЖЬ подразумевает поиск точного совпадения, ИСТИНА задает интервальный поиск.

Поиск значения в таблице с помощью функции ВПР

Рассмотрим, как работает функция ВПР, на простом примере. Допустим, имеется таблица с двумя столбцами: в первом проставлены номера по порядку, во втором содержатся фамилии. Необходимо по номеру найти фамилию. Если в таблице тысячи строк или нужно отыскать много фамилий, ручной процесс будет долгим и трудоемким. В таком случае выручит функция ВПР в Экселе. Пошаговая инструкция ее применения выглядит следующим образом:

  1. Заносим в ячейку вне таблицы, например D1, номер, по которому необходимо найти фамилию.
  2. Ставим курсор в ячейку D2 и нажимаем значок fx (располагается слева от строки формул).

задачть формулу  excel.png

  1. В окне Мастера функций находим категорию Ссылки и массивы, выбираем функцию ВПР и нажимаем ОК

EXCEL_20180521_155500.png

  1. Задаем аргументы. Три из четырех являются обязательными, они выделены жирным шрифтом в окне

Аргументы функции

chrome_20180521_161833.png

Искомое_значение: указываем ячейку D1, где хранится искомый номер.
Таблица: задаем ее координатами левой верхней ячейки D3 и правой нижней E7.
Номер_столбца: в исходной таблице столбец с фамилиями является вторым.

Важно

Номер_столбца отсчитывается относительно крайнего левого столбца исходной таблицы, он может не совпадать с номером столбца Excel.

Интервальный_просмотр: записываем ЛОЖЬ, что означает поиск точного совпадения. Значение ИСТИНА задает поиск в интервале, такой вариант рассмотрим ниже.

Важно

Если аргумент Интервальный_просмотр не указан, то по умолчанию будет осуществляться поиск в интервале.

После указания всех аргументов получаем формулу вида =ВПР(G3;D3:E7;2;0). Таким образом, функция ВПР возьмет значение Климов из ячейки G3, найдет в первом столбце исходной таблицы точное совпадение и выведет значение из второго столбца. В данном случае это 12. Меняя значение ячейки G3, можно получать различные значения

Поиск в интервале для функции ВПР

Рассмотрим подробнее поиск в интервале для функции ВПР. Например, есть таблица с фамилиями, а вместо номеров проставлены дробные коэффициенты. При этом данные отсортированы по возрастанию

Алгоритм действий

  1. В ячейку D1 заносим дробное число, по которому будет осуществляться поиск.
  2. В ячейке D2 с помощью Мастера функций находим ВПР и по аналогии с предыдущим примером указываем все аргументы, кроме Интервального_просмотра. В нем пишем ИСТИНА.
    Получится формула =ВПР(D1;A1:B6;2;ИСТИНА).

При указании значения ИСТИНА в аргументе Интервальный_просмотр функция ВПР будет работать так:

  • если вводить в ячейку D1 любые значения в диапазоне от 4,1 до 5,02, не включая само число 5,02, то результат будет один – Сидоров;
  • при вводе чисел меньше 1,256, будет выдаваться сообщение об ошибке #Н/Д;
    если в D1 будет значение больше, чем 10,45, функция вернет последнюю фамилию в списке.
Важно

Если таблица отсортирована неправильно, то и функция ВПР не будет работать корректно.

Если бы таблица была отсортирована правильно, то значение 8 в ячейке D1 привело бы к выводу результата Симонова по итогам работы функции ВПР в ячейке D2. Поэтому поиск в интервале следует использовать крайне осторожно. В большинстве случаев аргументу Интервальный_просмотр лучше присваивать значение ЛОЖЬ.

Объединение таблиц с помощью функции ВПР

Помимо поиска нужных значений, функция ВПР в Эксель может применяться для объединения таблиц. Рассмотрим ее использование на примере двух таблиц. Первая содержит номера, фамилии и суммы зарплат, она размещается на Листе1. Во вторую внесены номера, фамилии, суммы премий, она находится на Листе2. Порядок расположения номеров и фамилий по строкам в таблицах может различаться.
Чтобы в таблицу на Листе1 быстро перенести значения премий сотрудников, используем функцию ВПР:

  1. Устанавливаем курсор в ячейку D1 Листа1 и запускаем Мастер функций. В аргументе Искомое_значение указываем ячейку A1.
  2. В аргументе Таблица в данном случае необходимо указать данные массива с Листа2, так как именно в этой таблице функция должна находить и брать информацию. Для этого, не закрывая окно Аргументы функций, переходим на Лист2 и привычным способом отмечаем границы таблицы (рис. 11).
Важно

Чтобы не было проблем с копированием формулы в дальнейшем, адреса угловых ячеек таблицы делаем абсолютными с помощью значка $. То есть вместо A1:C6 пишем $A1:1:C$6.

  1. В аргументе Номер_столбца указываем число, соответствующее номеру столбца с информацией о премии в таблице на Листе2. В нашем примере это 3.
  2. В Интервальный_просмотр пишем логическое значение ЛОЖЬ, чтобы функция искала точно совпадающие значения.
  3. Подтверждаем ввод аргументов нажатием кнопки ОК. В итоге в ячейке D1 будет записана формула =ВПР(A1;Лист2!$A1:1:C$6;3;ЛОЖЬ).
  4. Выделяем ячейки от D1 и ниже, нажимаем Ctrl+D для копирования формулы. При этом будет меняться только первый аргумент: A2, A3 и так далее.

Таким образом, значения из таблицы на Листе2 перенесены на Лист1.
Очевидно, что функция ВПР в Excel может применяться при решении таких трудоемких задач, как поиск информации и объединение таблиц. Мы рассмотрели ее работу на простых примерах, однако с помощью ВПР можно производить гораздо более сложные расчеты быстро и качественно.

Подробнее о функциях программы Excel и их применении читайте здесь. Ответы на другие вопросы о работе в Excel читайте на Справочнике!

+0
-0
Нет комментариев
Не можешь разобраться в этой теме?
Обратись за помощью к экспертам
Гарантированные бесплатные доработки
Быстрое выполнение от 2 часов
Проверка работы на плагиат

Интересные статьи за сегодня

Легко ли принимать документы и общаться с абитуриентами?
1537 +542
1
Как провести последнюю ночь накануне экзамена с пользой? Выспаться!
1978 +161
2
Личный опыт подростка, уехавшего на обучение в другую страну.
383 +143
0
С тех пор как ЕГЭ по математике разделили на базу и профиль, ученикам стало легче жить...
7075 +51
0
Какие предметы подверглись нововведениям, а какие остались нетронутыми?
23183 +43
0
Хотите выполнять заказы?
Стать автором
Хотите заказать работу?
Разместить заказ