ВПР (вертикальный просмотр) — одна из самых популярных поисковых функций. Она помогает находить значения в одной таблице и переносить их в другую. Это удобно для сопоставления данных. Например, чтобы быстро посчитать выручку, когда прайс-лист и список проданного товара находятся на разных листах. Рассмотрим функцию ВПР подробнее.
Логика и синтаксис
Работу ВПР можно сравнить с поиском места в кинотеатре. Зал кинотеатра — диапазон поиска, ряд — ключ поиска, а номер кресла — индекс столбца. Так же как мы идем по ряду к нужному сиденью, ВПР просматривает выбранный диапазон по вертикали до значения-идентификатора. Когда видит его, «забирает» искомое значение и выводит в заданную ячейку.
У ВПР три обязательных аргумента:
- Ключ поиска (что ищем?) — значение для поиска в первом столбце диапазона.
- Диапазон (где ищем?) — диапазон ячеек, из которого функция будет брать данные для искомого значения.
- Индекс столбца — номер столбца в диапазоне, из которого будет возвращено значение.
- Тип сопоставления — необязательный, но важный аргумент, который отвечает на вопрос «отсортирован ли по возрастанию первый столбец диапазона поиска?». Если отсортирован, мы указываем значение ИСТИНА или 1, в противном случае — ЛОЖЬ или 0. Когда параметр опущен, он по умолчанию равен 1.
Прежде чем начать работу с ВПР, убедитесь, что в таблице нет объединенных ячеек, пустых строк и столбцов, а в каждом столбце находятся данные одного типа.
Рассмотрим пример. У нас есть таблица с тремя столбцами: артикул, наименование товара и его стоимость.
Допустим, нам нужно найти стоимость товара с артикулом 2056. На картинке видно, что она равна 2857, но когда в таблице тысячи строк, поиск усложняется. С функцией ВПР разобраться будет легче.
Введем функцию через знак равенства в свободной ячейке.
Ключ поиска — артикул — может быть прописан в виде ссылки на ячейку (E2) или как числовое значение 2056 (если нужно найти текстовое значение, выделяем его кавычками).
Указываем диапазон таблицы — это все данные из ячеек со стоимостью, наименованием товаров и артикулами (A2:C6).
Остался индекс столбца. В нашем случае стоимость товара указана в третьем по счету столбце.
Мы записали функцию в ячейке F2:
=ВПР(E2; A2:C6; 3)
Четвертый аргумент — тип сопоставления — в этом примере пропущен, и функция по умолчанию решила, что наша таблица отсортирована по возрастанию номера артикула. Это соответствует действительности, поэтому мы получили значение, которое ожидали, — 2857. Ниже мы разберем пример, где подобное не сработало.
От теории к практике
У нас есть объемная таблица с данными клиентов интернет-магазина.
Наша задача — идентифицировать покупателя по номеру телефона и вывести его ФИО в таблицу на другом листе. То есть номер — ключ поиска, первый аргумент ВПР (ячейка B1).
Диапазоном будут все данные в листе c телефонами, адресами и ФИО клиентов (A2:G24). ФИО клиентов указаны в столбце D, по счету он 4-й. Значит, индекс столбца — 4.
Записываем функцию:
=ВПР(B1; 'клиенты'!A2:G24;4;0)
На место четвертого аргумента мы подставили ноль, чтобы задать точный поиск указанного номера телефона в неотсортированной таблице.
Завершаем ввод функции клавишей Enter и получаем результат.
Аналогичным образом найдем адрес покупателя. Вы можете скопировать уже заполненную формулу ВПР в ячейку B3, скорректировав индекс столбца. Адреса записаны во втором столбце, поэтому мы меняем цифру 4 на 2.
Теперь, когда мы получили адрес и ФИО клиента, мы можем найти данные любого покупателя по номеру телефона.
Итоги
- Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
- Функция ВПР полезна при работе с большими таблицами, благодаря ей можно быстро найти нужную информацию.
- Синтаксис ВПР: =ВПР(ключ_поиска;диапазон;индекс_столбца;тип_сопоставления).
- Четвертый аргумент функции важно указывать, чтобы задать поиск в неотфильтрованной таблице.
- Если в таблице есть объединенные ячейки, пустые строки или столбцы, ВПР может работать некорректно.
Подписывайтесь на наш канал, где мы делимся лайфхаками о работе с документами.
А скачать редакторы МойОфис можно на нашем сайте. Это бесплатно, быстро и не требует регистрации.
