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

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

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

Синтаксис и индивидуальности внедрения функции ВПР

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

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

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

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

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

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

    • Номер столбца. В этом аргументе мы пишем цифру два.
    • Интервальный просмотр. Этот параметр нужен, ежели ищутся лишь приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем 2-ой вариант, так как нам требуется четкая информация.
  4. После этого жмем клавишу «ОК».
  5. Далее функция плодится на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 
Читайте также  Скачать штриховки в автокаде. Штриховка для АвтоКАД скачать

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

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

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

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

В самом конце необходимо подтвердить свои деяния с помощью клавиши «ОК».

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

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

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

А вот и обычная аннотация, как это сделать:

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

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

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

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

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

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

  1. Материал.
  2. Производитель.
Читайте также  Команда автозапуска windows 7. Автозагрузка в Windows 7: порядок в системе со старта

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

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

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

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

Сперва необходимо сделать сам раскрывающийся список:

  1. Кликнуть левой клавишей мыши по ячейке E2, тем самым выделив ее. 
  2. Переместиться на вкладку «Данные», и там отыскать пункт «Проверка данных».
  3. Указать источник инфы. В качестве него у нас употребляются наименования продуктов. Сам тип данных выставляем – список.

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также  Тампермонкей что это за программа. Tampermonkey - что это за программа?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы

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

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

Оцените качество статьи. Нам принципиально ваше мнение:

”&G$2,$C$2:$D$19,2,0) – британская версия

=ВПР($F3&”

Оставьте комментарий

Adblock
detector