Функция ВПР в Excel
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Аргументы функции ВПР
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
Находит в самом левом столбце совпадение с искомым значением.
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
Итоги
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
Функцию можно вписать вручную или в специальном окне (Shift + F3).
Искомое значение – относительная ссылка, а таблица – абсолютная.
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
[expert_bq id=»1570″]Количество строк и столбцов в массиве ИНДЕКС должно соответствовать значениям в параметрах номер_строки и или номер_столбца функции ПОИСКПОЗ соответственно. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В формуле выше используется функция MATCH (ПОИСКПОЗ) , которая отвечает за порядковый номер колонки с данными. MATCH (ПОИСКПОЗ) принимает название учебного предмета как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.
Как сделать поиск в Экселе по столбцу? Онлайн журнал Жизнь и работа
Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :
