861
14 Мая в 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 может применяться при решении таких трудоемких задач, как поиск информации и объединение таблиц. Мы рассмотрели ее работу на простых примерах, однако с помощью ВПР можно производить гораздо более сложные расчеты быстро и качественно.

+0
-0
Комментарии
Нет комментариев

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

Как составить резюме молодому специалисту

Что написать в резюме, если вы только окончили вуз и у вас нет опыта?
51 +39
0

Как защищать курсовую работу

О том, как преподнести себя публике и уберечь нервную систему (не только свою), представляя свое научное детище, – читайте в нашей статье.
199 +27
1

Кто в России счастлив на работе

Кто в России счастлив на работе?
435 +27
4

Антиплагиат: что это такое

Детальный обзор систем антиплагиата: как с ними работать и как повысить уникальность
3538 +26
0
Хотите выполнять заказы?
Стать автором
Хотите заказать работу?
Разместить заказ