Функция ВПР в Excel: пошаговая инструкция с примерами для чайников 

ВПР применяют для получения одного массива из 2 или большего количества меньших по размеру, что позволяет упростить сравнение данных вручную. Особенно если таблицы состоят из нескольких сотен и даже тысяч строк, когда их объединение другими способами, без функции ВПР, может привести к ошибкам. 

Особенности использования функции 

Принцип действия и синтаксис функции не меняются в зависимости от версии табличного процессора – она остается актуальной и для пакета MS Office 1997, и для Офис 365. Но для того, чтобы лучше разобраться с ее работой, стоит рассмотреть ВПР в Excel для чайников с примерами – такими, как списки продуктов на складе магазина. 

Предположим, что есть две отдельные таблицы с одинаковыми наименованиями – но в одной указаны только цены, а в другой представлено количество товара. 

Чтобы объединить таблицы, пользователь должен воспользоваться таким алгоритмом: 

1. Привести одну из таблиц, на базе которой будет создаваться объединенный массив в подходящий вид. В том числе добавить столбец «Стоимость», изменив его формат на денежный. Одновременно можно создать колонку для подсчета общей стоимости.  
2. Выделить самую верхнюю ячейку в новой колонке. Вызвать «Мастер функций», нажав на кнопку «fx» или комбинацию Shift + F3. 
3. Найти и выделить в списке функций, который появится в открывшемся окне, опцию ВПР. Нажать «ОК». Если среди недавно использовавшихся функций такого варианта нет, найти его можно в категории «Ссылки и массивы». Причем не только в окне вставки, но и на панели «Формулы». 
4. Начать заполнение окна с аргументами функций. Первое, что стоит указать – «Искомое значение» или «Lookup Value». В данном случае это будут названия товаров – то есть столбец от D3 до D17. Именно их функция будет искать во второй таблице и сравнивать с данными первой. 
5. Указать аргумент под названием «Таблица», включающий в себя информацию из второго массива. Чтобы функция сработала, следует выделить столбец с названиями, которые должны совпадать с указанными в «Искомом значении». Вторую колонку (с единицами) придется убрать, после чего окажется, что в качестве аргумента должны быть подставлены значения от D3 до E17. Чтобы упростить задачу при работе с несколькими таблицами или постоянном использовании одних и тех же данных, массиву можно дать свое название – например, «Прайс». 
6. Указать в поле «Номер столбца» ту колонку второй таблицы, из которой будут браться и подставляться в общий массив нужные данные. Здесь следует указывать только цифры. В данном случае, это «2», потому что подставляемая информация находится во втором столбце («Цена, руб.») из выделенной области. 
7. Ввести в поле «Интервальный просмотр» логическое значение – «ИСТИНА» или «ЛОЖЬ». При выборе первого («ИСТИНА», «TRUE» или «1») будет выполняться поиск не по точному соответствию, а по похожим значениям. Из-за этого может произойти ошибка, и при сравнении текста указывать «1» не стоит. Чтобы функция работала правильно, в указанном примере указывается «0» («ЛОЖЬ» или «FALSE»). Это позволит искать только точные соответствия. 
8. Нажать «ОК», после чего результаты поиска будут указаны только в нужной ячейке. Для распространения на всю таблицу формулу придется «размножить». Для этого достаточно захватить крестик в правом нижнем углу ячейки и протянуть его вниз. 

Результатом становится заполненный новый столбец общего массива, где теперь указано не только количество товаров, но и цена за единицу измерения. Все данные точно соответствуют колонке «Наименование», независимо от их сортировки. Даже если названия продуктов во второй таблице будут расположены по-другому, например, в алфавитном порядке. 

Теперь можно закончить процесс подстановки и расчета общей стоимости продуктов. Для этого достаточно указать в верхней ячейке соответствующей колонки простую формулу: «Стоимость» = «Кол-во на складе» * «Цена, руб.». В данном случае это будет: «=F3*G3». 

Данные «размножаются» стандартным способом: захватите крест и протяните его до конца колонки. 

Если нужно, чтобы результат оставался постоянным, можно создать дополнительный столбец, скопировав данные из «Общей стоимости» и выполнив специальную вставку – только «Значений». Правда, данные не будут изменяться и при уменьшении или увеличении количества продуктов, потому для нашего примера этот способ не подходит. 

Принцип действия функции 

Чтобы понять, почему не имеет никакого значения сортировка, стоит рассмотреть принцип действия функции ВПР в Excel для чайников: 

1. Сначала функция выполняет проверку верхней ячейки колонки «Наименования» первого массива с соответствующим столбцом второй таблицы. В данном случае информация («Баклажан») совпадает сразу, и к количеству «120» добавляется цена «80». 
2. Проверка продолжается, и теперь проверяется значение «Петрушка». Функция продолжает сравнивать данные до тех пор, пока не доходит до 10-й строчки списка. После этого к количеству «12» добавляется соответствующая цена «270». 
3. Процесс продолжается до тех пор, пока нужные сведения не будут найдены. 

Из-за того, что при настройке работы функции был указан параметр «ЛОЖЬ», данные в колонках «Наименование» должны точно соответствовать друг другу. Даже если в столбцах будет изменение на 1 знак (например, «Помидоры 1 сорт» и «Помидоры 2 сорт»), сравнение получится не корректным и сопоставления данных не произойдет. 

Применение функции в выпадающих списках 

Применять ВПР можно и при использовании выпадающих списков. Например, для того, чтобы при выборе из перечня нужного значения одновременно с ним в соответствующей ячейке появлялись связанные с ним данные. В выбранном примере это может быть название и цена. 

Лучший способ рассмотреть действие функции ВПР в Excel – пошаговая инструкция для заданных условий. От пользователя требуется выполнить такие действия: 

1. Поставить курсор в ячейку, где будет располагаться список. 
2. Перейти на вкладку «Данные» и выбрать меню проверки данных. 
3. Указать тип данных «Список», а в качестве источника – диапазон с наименованиями. 
4. Нажать «ОК». 
5. Перейти к ячейке, где должна появляться цена (обычно это соседняя с той, в которой находится выпадающий список). 
6. Открыть «Мастер функций» нажатием на кнопку «fx» и выбрать «ВПР». 
7. Указать среди аргументов ячейку, где находится выпадающий список, диапазон названий и цен, столбец №2 и «ЛОЖЬ». 
8. Нажать «ОК». 

Теперь после выбора в ячейке с выпадающим списком определенного значения рядом будут автоматически появляться связанные данные. В примере это – названия продуктов и цены, соответственно. 

Обе ячейки можно копировать попарно в любое место, получая такой же выпадающий список и связанное значение, меняющееся после соответствующего изменения данных в массиве. 

Ошибки при использовании 

Разобравшись с функцией ВПР Эксель, как пользоваться этим способом для совмещения таблиц и создания выпадающих списков со связанными значениями, стоит познакомиться и с возможными ошибками. Функция будет возвращать #Н/Д или #N/A в таких ситуациях: 

• Включен точный поиск (аргумент «ЛОЖЬ» или «0» в качестве последнего аргумента функции, но нужного значения во втором массиве не нашлось). 

• Форматы сравниваемых ячеек отличаются. Например, в одной это «текстовый», а в другой – «числовой». Чтобы исправить ошибку, достаточно преобразования формата данных в нужный. 
• Пользователь допустил ошибки при вводе функции. Такое иногда происходит, если вводить аргументы вручную, а не с помощью специальной формы. 
• Неправильно указан номер столбца. Значение этого аргумента начинается с 1 и не должно превышать общее число колонок в выбранной для сравнения части массива. 

Чтобы избежать появления сообщения #Н/Д, можно добавить к формуле условие «ЕСЛИОШИБКА». Например, такая конструкция, как «=ЕСЛИОШИБКА(ВПР(D14:D28;Прайс;2;0);0)» подставит в соответствующую ячейку ноль.

Подведение итогов 

Применение ВПР позволяет автоматизировать сопоставление табличных данных для списков с одинаковыми наименованиями – например, товаров, услуг или компаний. И если в небольшой таблице такие действия можно выполнить и вручную, для большого массива поисковая функция сэкономит время, позволит избежать случайных опечаток или ошибок. Поэтому очень важно знать, как работает ВПР в Excel. 

Читайте также:

• Альтернативы Google Docs: куда перенести документы, если Гугл заблокируют
• Как прибавить проценты в Эксель: 3 метода с примерами