В Excel есть функция, которая вызывает массу вопросов у начинающих пользователей программы – это функция ВПР.
В этой серии заметок я расскажу все, что вам нужно знать, чтобы начать использовать функцию ВПР.
Разберемся с принципами работы функции ВПР, рассмотрим примеры ее использования, поговорим о проблемах и ошибках, с которыми чаще всего сталкиваются пользователи при работе с ВПР.
Итак, начнем и в первой заметке проясню, для чего нужна функция ВПР и как она работает.
Зачем нужна функция ВПР
Excel позволяет не только производить расчеты, но и консолидировать данные, то есть объединять данные из разных таблиц, листов или даже документов в одну таблицу.
Для этого в программе есть ряд функций, одной из которых и является функция ВПР.
В английском варианте функция называется VLOOKUP (Vertical Look Up), что, собственно, и отразилось в переводе - ВПР является своеобразным сокращением от «Вертикальный ПРосмотр».
Из названия не понятно, что именно делает функция ВПР, а описание в окне функций лишь еще больше набрасывает тень на плетень:
Хотя на самом деле все не так уж и сложно - функция ВПР позволяет по определенному критерию подтягивать данные из одной таблицы в другую.
Например, у нас есть две таблицы - прайс-лист с ценами товаров и таблица с конкретным заказом. Мы можем подставить цены из прайс-листа самым обычным образом, сославшись на соответствующие ячейки.
Но это крайне неудобно и занимает массу времени, особенно если таблицы большие да еще и расположены на разных листах или в разных документах.
С помощью функции ВПР мы сможем автоматизировать весь процесс и подтянуть цены из прайс-листа к заказу, опираясь на наименование товара в чеке.
То есть в качестве критерия, который связывает данные из двух таблиц, выступает наименование товара, ведь оно одинаково и в прайс-листе, и в заказе.
Ну а чтобы корректно применять функцию ВПР стоит разобраться в том, как она работает.
Как работает функция ВПР
Максимально просто работу функции ВПР можно объяснить на примере телефонного справочника или любого другого алфавитного указателя.
Например, нам нужно найти телефон какой-то организации. Открываем соответствующую букву в справочнике и начинаете поиск по порядку сверху вниз.
Проходим все названия на странице и доходим до нужной организации.
Далее обращаем внимание на соседнюю колонку, в которой отражен телефон. Задача выполнена.
Именно так и работает функция ВПР - она проходит сверху вниз крайний левый столбец выделенного диапазона в поисках заданного пользователем критерия. Как только его находит, то перемещается на указанное пользователем количество столбцов вправо и выдает значение ячейки из этого столбца.
Давайте рассмотрим синтаксис функции ВПР и ее аргументы.
Синтаксис функции ВПР
Функция имеет четыре аргумента. Рассмотрим их на примере простейшей телефонной книги.
Итак, у нас есть небольшая таблица в четыре столбца. В первом расположены порядковые номера записей, во втором перечислены имена друзей, в третьем адреса и в последнем телефоны.
В этом массиве данных нужно найти телефон Ивана.
Воспользуемся функцией ВПР.
В подсказке через точку с запятой указаны четыре аргумента.
Первый - искомое_значение. Это то, ЧТО мы должны искать в массиве данных, то есть в нашем случае имя «Иван».
Второй аргумент - таблица, но фактически здесь мы должны указать диапазон значений в котором будет производиться поиск, а не таблицу целиком. То есть это то, ГДЕ будет производиться поиск.
Если, как в нашем случае, в первой строке размещены заголовки столбцов, то в них поиск осуществляться не должен, поэтому мы их выделять не будем. При этом функция ВПР производит поиск ТОЛЬКО в крайнем левом столбце выделенного диапазона, а значит первый столбец таблицы нас тоже не интересует. Указываем диапазон с именами, адресами и телефонами.
Но почему мы выбираем не только столбец с именами, в котором ищем нужный критерий?
А дело в том, что указанный нами критерий будет действительно искаться только в первом столбце выделенного диапазона, но в итоге функция должна вернуть не его, а значение, находящееся с ним в одной строке таблицы - в нашем примере телефон Ивана. Поэтому мы расширяем диапазон на все столбцы до нужного нам и в него попадает столбец с адресами.
Но как указать функции, какой именно столбец нас интересует? То есть, что нам нужен телефон, а не адрес?
В этом поможет третий аргумент функции - номер_столбца.
И тут важным является то, что имеется в виду номер столбца в выделенном диапазоне, а не номер столбца на листе Excel.
В рассматриваемом примере был выделен диапазон, состоящий из трех столбцов, поэтому здесь первым будет столбец с именами, второй с адресами, а третий с телефонами.
Еще раз подчеркну, что отсчет ведется от первого столбца выделенного диапазона, а не от столбца А листа Excel.
Так как нам нужно, чтобы функция ВПР вернула номер телефона, который находится в третьем столбце выделенного диапазона, то вводим 3.
И последний самый загадочный аргумент - интервальный_просмотр.
Этот аргумент логический и может принимать только два значения - ИСТИНА или ЛОЖЬ. Для простоты эти значения можно записывать цифрами, соответственно, 1 и 0.
Данный аргумент необязательный и если его не указать, то по умолчанию Excel будет его принимать за ИСТИНУ.
Из описания следует, что значение ИСТИНА соответствует приблизительному совпадению, а значение ЛОЖЬ - точному.
С точным совпадением вроде бы все интуитивно понятно - найденное значение должно в точности соответствовать искомому. Так как мы ищем имя и нас интересует именно точное совпадение, то сейчас используем его. О приблизительном совпадении поговорим чуть позже (в одной из следующих заметок) и разберем его на другом примере.
Нажимаем Enter и получаем результат.
Давайте проанализируем, как функция ВПР работает.
В качестве искомого значения мы задали имя Иван. Напомню, что функция ВПР всегда производит поиск искомого значения в крайнем левом столбце выделенного диапазона (B2:D7), поэтому перемещаясь сверху вниз по столбцу B она сравнивает заданное нами искомое значение со значениями, которые в нем находятся.
Как только она находит нужное значение, то поиск по вертикали заканчивается и функция смещает свое внимание на ячейку, которая задается третьим аргументом. Мы указали третий столбец и в итоге получили значение из его ячейки (D5).
Итак, с синтаксисом разобрались и в следующей заметке разберем пример использования функции ВПР.