Для студентов, экономистов и всех тех, кто в ходе своей работы использует достаточно большие массивы данных (таблицы), была создана очень полезная функция Excel вертикальный поиск решения (ВПР). Она применяется для поиска нужного значения в таблицах и имеет 4 аргумента:
- Искомое_значение – содержимое ячейки, которое необходимо найти в крайнем левом столбце таблицы.
- Таблица – это массив данных, в рамках которого происходит поиск нужного значения. Координаты задаются адресами верхней левой и нижней правой ячеек в Аргументах функции.
- Номер_столбца – аргумент отсчитывается от крайнего левого столбца Таблицы (предыдущий параметр функции). Он всегда задается числом.
- Интервальный_просмотр – необязательный параметр, определяющий вид поиска. Может принимать два значения: ЛОЖЬ и ИСТИНА. ЛОЖЬ подразумевает поиск точного совпадения, ИСТИНА задает интервальный поиск.
Поиск значения в таблице с помощью функции ВПР
Рассмотрим, как работает функция ВПР, на простом примере. Допустим, имеется таблица с двумя столбцами: в первом проставлены номера по порядку, во втором содержатся фамилии. Необходимо по номеру найти фамилию. Если в таблице тысячи строк или нужно отыскать много фамилий, ручной процесс будет долгим и трудоемким. В таком случае выручит функция ВПР в Экселе. Пошаговая инструкция ее применения выглядит следующим образом:
- Заносим в ячейку вне таблицы, например D1, номер, по которому необходимо найти фамилию.
- Ставим курсор в ячейку D2 и нажимаем значок fx (располагается слева от строки формул).
- В окне Мастера функций находим категорию Ссылки и массивы, выбираем функцию ВПР и нажимаем ОК
- Задаем аргументы. Три из четырех являются обязательными, они выделены жирным шрифтом в окне
Аргументы функции
Искомое_значение: указываем ячейку D1, где хранится искомый номер.
Таблица: задаем ее координатами левой верхней ячейки D3 и правой нижней E7.
Номер_столбца: в исходной таблице столбец с фамилиями является вторым.
Номер_столбца отсчитывается относительно крайнего левого столбца исходной таблицы, он может не совпадать с номером столбца Excel.
Интервальный_просмотр: записываем ЛОЖЬ, что означает поиск точного совпадения. Значение ИСТИНА задает поиск в интервале, такой вариант рассмотрим ниже.
Если аргумент Интервальный_просмотр не указан, то по умолчанию будет осуществляться поиск в интервале.
После указания всех аргументов получаем формулу вида =ВПР(G3;D3:E7;2;0). Таким образом, функция ВПР возьмет значение Климов из ячейки G3, найдет в первом столбце исходной таблицы точное совпадение и выведет значение из второго столбца. В данном случае это 12. Меняя значение ячейки G3, можно получать различные значения
Поиск в интервале для функции ВПР
Рассмотрим подробнее поиск в интервале для функции ВПР. Например, есть таблица с фамилиями, а вместо номеров проставлены дробные коэффициенты. При этом данные отсортированы по возрастанию
Алгоритм действий
- В ячейку D1 заносим дробное число, по которому будет осуществляться поиск.
- В ячейке D2 с помощью Мастера функций находим ВПР и по аналогии с предыдущим примером указываем все аргументы, кроме Интервального_просмотра. В нем пишем ИСТИНА.
Получится формула =ВПР(D1;A1:B6;2;ИСТИНА).
При указании значения ИСТИНА в аргументе Интервальный_просмотр функция ВПР будет работать так:
- если вводить в ячейку D1 любые значения в диапазоне от 4,1 до 5,02, не включая само число 5,02, то результат будет один – Сидоров;
- при вводе чисел меньше 1,256, будет выдаваться сообщение об ошибке #Н/Д;
если в D1 будет значение больше, чем 10,45, функция вернет последнюю фамилию в списке.
Если таблица отсортирована неправильно, то и функция ВПР не будет работать корректно.
Если бы таблица была отсортирована правильно, то значение 8 в ячейке D1 привело бы к выводу результата Симонова по итогам работы функции ВПР в ячейке D2. Поэтому поиск в интервале следует использовать крайне осторожно. В большинстве случаев аргументу Интервальный_просмотр лучше присваивать значение ЛОЖЬ.
Объединение таблиц с помощью функции ВПР
Помимо поиска нужных значений, функция ВПР в Эксель может применяться для объединения таблиц. Рассмотрим ее использование на примере двух таблиц. Первая содержит номера, фамилии и суммы зарплат, она размещается на Листе1. Во вторую внесены номера, фамилии, суммы премий, она находится на Листе2. Порядок расположения номеров и фамилий по строкам в таблицах может различаться.
Чтобы в таблицу на Листе1 быстро перенести значения премий сотрудников, используем функцию ВПР:
- Устанавливаем курсор в ячейку D1 Листа1 и запускаем Мастер функций. В аргументе Искомое_значение указываем ячейку A1.
- В аргументе Таблица в данном случае необходимо указать данные массива с Листа2, так как именно в этой таблице функция должна находить и брать информацию. Для этого, не закрывая окно Аргументы функций, переходим на Лист2 и привычным способом отмечаем границы таблицы (рис. 11).
Чтобы не было проблем с копированием формулы в дальнейшем, адреса угловых ячеек таблицы делаем абсолютными с помощью значка $. То есть вместо A1:C6 пишем $AC$6.
- В аргументе Номер_столбца указываем число, соответствующее номеру столбца с информацией о премии в таблице на Листе2. В нашем примере это 3.
- В Интервальный_просмотр пишем логическое значение ЛОЖЬ, чтобы функция искала точно совпадающие значения.
- Подтверждаем ввод аргументов нажатием кнопки ОК. В итоге в ячейке D1 будет записана формула =ВПР(A1;Лист2!$AC$6;3;ЛОЖЬ).
- Выделяем ячейки от D1 и ниже, нажимаем Ctrl+D для копирования формулы. При этом будет меняться только первый аргумент: A2, A3 и так далее.
Таким образом, значения из таблицы на Листе2 перенесены на Лист1.
Очевидно, что функция ВПР в Excel может применяться при решении таких трудоемких задач, как поиск информации и объединение таблиц. Мы рассмотрели ее работу на простых примерах, однако с помощью ВПР можно производить гораздо более сложные расчеты быстро и качественно.
Подробнее о функциях программы Excel и их применении читайте здесь. Ответы на другие вопросы о работе в Excel читайте на Справочнике!
Не знаете, где заказать написание статьи по информатике на заказ? Авторы Студворк к вашим услугам!
Комментарии