3392
14 Мая 2018 в 12:00 14.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 часов
Проверка работы на плагиат

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

Где лучше всего студентам живется?
4705 +34
1
Несколько способов, которые помогут обратить критику себе на пользу. Проверено — работают.
480 +25
0
Форму набора каждый выбирает сам – по возможностям или по желанию. Спешим обрадовать: у коммерции тоже есть плюсы.
4980 +24
0
Сегодня вы студент, а завтра уже нет. Как вернуться к учебе после отчисления?
5779 +20
0
Воспоминания эксперта Студворк.
178 +14
0
Автор
Хотите выполнять заказы? Стать автором
Заказчик
Хотите заказать работу? Разместить заказ
Доверьте свою работу экспертам
Разместите заказ
Наша система отправит ваш заказ на оценку 31 626 авторам
Первые отклики появятся уже в течение 10 минут