Вся суть функции ВПР в Excel (для начинающих пользователей)

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

Синтаксис и особенности применения функции ВПР

Предположим, вы заведующий складом, в котором хранятся разные материалы, такие как упаковки и контейнеры для хранения разных предметов. И вам завезли продукцию в конкретном количестве, и оно задается в Excel.

В другой же таблице приводится цена каждого из материалов.

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

Если говорить более точно, последовательность действий следующая:

1. Приводим внешний вид таблицы в нужный нам вид путем вставки двух колонок, которые называются «Цена» и «Стоимость/Сумма». При этом нужно применить к ячейкам денежный формат. 
2. Нажимаем на ячейку, являющуюся первой в нашей колонке «Цена». В случае с нами она имеет адрес D2. С использованием мастера функций пользователь всегда может найти ВПР в категории «Ссылки и массивы», независимо от версии Excel. А для вызова мастера функций есть два метода. Первый – это нажать на кнопку fx рядом со строкой ввода формулы. Второй же – комбинация клавиш SHIFT + F3. После того, как нужная нам функция будет выбрана, надо нажать на клавишу ОК, чтобы подтвердить свои действия. Есть еще один способ вызова этой функции. Нужно перейти на вкладку «Формулы» и там найти тот же пункт «Ссылки и массивы».

   

3. Далее нам нужно настроить функцию, введя в нее свои параметры. Для этого появится специальное окошко, в котором приведено несколько аргументов, в которые пользователь может ввести собственные значения:
   • Искомое значение. В случае с нашей таблицей им выступает перечень наименований товара, то бишь, первая колонка. Именно эта информация и должна будет искаться во второй таблице.

     

   • Таблица. Это набор ячеек, в которых будет осуществляться поиск. В данном примере это вторая таблица с прайс-листом. Осуществляем переход на нее и выбираем необходимые значения. 

     

     Данные нужно обязательно фиксировать, чтобы Excel не менял ссылки в зависимости от местонахождения ячейки, в которой вставлена формула. Для этого необходимо сделать ее абсолютной, что легко делается с помощью клавиши F4. То, что все получилось, мы поймем по специфическому значку доллара перед ссылками на строки и колонки.

   • Номер столбца. В этом аргументе мы пишем цифру два.
   • Интервальный просмотр. Этот параметр нужен, если ищутся только приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем второй вариант, поскольку нам требуется точная информация.

     

4. После этого нажимаем кнопку «ОК».
5. Далее функция размножается на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 

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

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

Как это сделать? Можно применить «Специальную вставку». Последовательность действий следующая:

1. Выделяем нужную колонку и делаем правый клик мыши.
2. Копируем колонку.
3. Оставляем выделение, опять делаем правый клик мыши и нажимаем «Специальная вставка», после чего появится меню, в котором нужно установить радиокнопку возле пункта «Значения».

В самом конце нужно подтвердить свои действия с помощью кнопки «ОК».

Теперь не будет формулы в ячейках, и поэтому значения не будут изменяться при коррекции цены товара.

Экспресс-сравнение двух диапазонов

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

А вот и простая инструкция, как это сделать:

1. Создать столбец «Новая цена» в старом прайсе.

   

2. Делаем клик по первой ячейке и вставляем функцию ВПР описанным выше способом (через кнопку fx), поскольку он наиболее удобный для новичка. По мере обретения профессионализма можно вводить формулу вручную. В нашем случае она будет выглядеть следующим образом. =ВПР($A$2:$A$15;’новый прайс’!$A$2:$B$15;2;ЛОЖЬ). Простыми словами, нам нужно сравнить диапазон А2:А15 в двух прайсах. После этого вставить новую информацию в старый в колонку «Новая цена».

   

Дальше информация о ценах сопоставляется в ручном режиме или с помощью логических операторов Excel. Также можно применить условное форматирование и выделить красным цветом те строки, в которых цена изменилась. Но это уже уровень профессионала. 

Использование нескольких условий для формулы ВПР

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

Вот небольшая таблица для наглядности.

Допустим, перед нами стоит задача определить, по какой стоимости был завезен гофрированный картон от производителя «ОАО «Восток». В этом случае нам требуется два условия:

1. Материал.
2. Производитель.

Но это еще не все, потому что каждый производитель завозит сразу несколько товаров. Как можно выкрутиться в этой ситуации? А вот, как:

1. К таблице присоединяется крайний левый столбец, чтобы поставщики и материалы были в одной группе.

   

2. Критерии также нужно объединить.

   

3. Курсор устанавливается в требуемом месте, и в скобках указываются аргументы функции (или же через соответствующее диалоговое окно). =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ). После этого Excel определит необходимую стоимость.

   

ВПР и выпадающий список

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

Сперва нужно создать сам раскрывающийся список:

1. Кликнуть левой кнопкой мыши по ячейке E2, тем самым выделив ее. 
2. Переместиться на вкладку «Данные», и там найти пункт «Проверка данных».

   

3. Указать источник информации. В качестве него у нас используются названия товаров. Сам тип данных выставляем – список.

   

После того, как мы нажмем кнопку «ОК», появится выпадающий список.

Следующая задача – добиться того, чтобы в графе с ценой автоматически записывалось нужное значение, как только пользователь выберет необходимый товар. Чтобы сделать это, нужно кликнуть по ячейке E9, поскольку именно там записывается цена в нашей таблице, и следовать таким инструкциям:

1. Нажимаем на кнопку fx, что позволит открыть мастер функций. В этом диалоговом окне выбирается функция ВПР.
2. В качестве первого аргумента указываем ту ячейку, в которой содержится выпадающий список. В качестве второго – диапазон с наименованиями продукции и ее стоимостью. Столбец второй. Функция, в результате, обретает такой вид. =ВПР(E8;A2:B16;2;ЛОЖЬ)
3. После нажатия клавиши «ВВОД» получаем необходимый результат.

   

После каждой коррекции продукции изменяется и цена.

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

Почему функция не работает

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

Нужно точное совпадение

В последнем аргументе «Интервальный просмотр» нет острой необходимости, но важно понимать, что значение по умолчанию – ИСТИНА. Следовательно, чтобы функция без этого аргумента работала правильно, значения должны быть отсортированы по возрастанию.

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

Очень хорошо это видно на примере ниже. Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать.

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

Но какой бы трагичной не казалась эта ситуация, у нее есть сразу два варианта решения. Если изменения в таблицу после того, как будет создана финальная версия документа, не требуются, можно его просто заблокировать. Тогда пользователи, которые читают документ, не смогут вставить лишнюю колонку.

Но так бывает не всегда. Тогда на помощь придет второе решение. Мы знаем, что в качестве аргумента функции может использоваться другая функция. Вот это и решение. Нужно просто использовать функцию ПОИСКПОЗ, которая возвращает правильный номер столбца. 

Увеличение размеров таблицы

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

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

Функция не умеет анализировать данные слева

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

Решение этой проблемы находится вообще вне плоскости функции ВПР. Простыми словами, ее вообще не нужно использовать. В качестве альтернативы, не имеющей такого ограничения, можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ. 

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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

Выводы

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

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