Функция ВПР. Использование функции ВПР. Excel — ВПР

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

  1. Функция подбирает заданные параметры, используя критерий из первой таблицы, обращается ко второму диапазону, откуда будут «подтягиваться» значения.
  2. С самой верхней ячейки колонки критериев, просматривает все значения.
  3. Отыскав совпадения с заданными критериями, отсчитывает заданное число колонок вправо и попадает в ячейку, где расположено искомое значение, которое «затягивается» в ту ячейку, где указана формула.

вставить функцию впр

Пример применения функции ВПР

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

Исходная информация может содержаться в разных диапазонах и другом порядке. Первая таблица указывает на количество реализованного товара:

Вторая — на цены:

При совпадении товаров в обеих таблицах, путем использования комбинации клавиш Ctrl+C и Ctrl+V, показатели цен можно было бы легко подставить к количеству. Но в таблицах разная очередность позиций.

сравнение 2-х таблиц

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

Как подставить цены из второй таблицы к соответствующим показателям количества из первой, то есть цену позиции А к количеству позиции А, цену Б к количеству Б.

подставление значений

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

Порядок действий:

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

Функцию ВПР вызывают через Мастера функций или прописывают вручную.

Вызов опции через Мастера заключается в активации ячейки, где будет указана формула, и нажимают на кнопку f(x) в начале строки формул. В появившемся диалоговом окне Мастера из предложенного перечня требуется указать ВПР.

поиск функции впр

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

  1. Первая графа «Искомое значение» позволяет установить критерии для ячейки, где будет прописана формула. В приведенном примере ячейка содержит товар «А».
  2. Следующая строка «Таблица». При внесении диапазона данных, она позволит отыскать нужные значения. Для примера использовалась вторая таблица с ценами. Так как цены «подтягивают» к количеству. При этом важно учесть необходимость в содержании крайним левым (первым слева) столбцом выделяемого диапазона аналогичных критериев для поиска. В примере это колонка с названием товара. Потом таблица выделяется вправо до колонки, где содержатся искомые данные (цены). Можно продлить выделение вправо, но это ни на что не повлияет, так как колонка с искомыми показателями будет однозначно определена следующим параметром. Важно, чтобы выделенные таблицы начинались с колонки критерий и захватывали интересующий столбец с данными.
  3. «Номер столбца» — числа, на которые колонки с искомыми данными (ценами) отстоят от колонки с критериями (названием товара). Отсчет начинается с самой колонки критериев. Если во второй таблице обе колонки расположены рядом, следует указать цифру 2 (первая – критерии, вторая — цены). Возможно размещение данных по отношению к критериям на 10 или 20 колонок. Это не имеет значения, Ексель произведет верные расчеты.
  4. Последняя графа «Интервальный просмотр», где указаны варианты поиска: точные (0) или приблизительные (1) совпадения критериев. Сейчас следует указать 0 (или ЛОЖЬ).

искомое значение

Остается кликнуть по кнопке ОК или «Ввод». При правильном внесении данных и наличии критерия в обеих таблицах, на месте указанной формулы отобразится некоторый показатель. Достаточно протянуть (или скопировать) формулу вниз до последней строчки таблицы.

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

Альтернативным вариантом выступает прописывание формулы ВПР в ячейке, прописав между параметрами знак «;».

прописать знак ;

Допускается прописывание наименования «впр» с помощью маленьких букв, реестр не имеет значения.

Пример использования функции

Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В — соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.
Наглядный пример организации таблицы

АВСД
продукт 190продукт 360
продукт 2120продукт 190
продукт 360продукт 4100
продукт 4100продукт 2120

Формула, записанная в Д, будет выглядеть так: =ВПР (С1; А1:В5; 2; 0), то есть =ВПР (искомое значение; диапазон данных таблицы; порядковый номер столбца; 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.

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

Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).

Как использовать специальную вставку?

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

  • Выделить столбец с указанными ценами.
  • Правой кнопкой мыши – «Скопировать».
  • Не убирая выделений, правой кнопкой мышки – «Специальные вставки».
  • Установить галку против «Значения». ОК.

значение

В ячейках останутся лишь значения, формула аннулируется.

Пример организации учебного процесса с ВПР

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

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

функция ВПР

Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком «Оценки» записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.

Использование функции ВПР

В результате выполнения функция ВПР выдаст оценки, полученные определенными студентами.

Как быстро сравнить две таблицы с помощью ВПР?

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

материалы в таблице

Порядок действий:

В старом прайсе требуется создать колонку «Новые цены».

Выделить первую ячейку и выбрать формулу ВПР. Задать аргументы (см. выше). Например:

Указанная формула сообщает о необходимости взять название товара из диапазона А2:А15, осуществить его просмотр в «Новом прайсе», используя колонку А. Затем воспользоваться данными из второй колонки нового прайса (новыми ценами) и внести их в ячейку С2.

определилась новая цена

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

Как пользоваться функцией ВПР, если данные на разных листах

Как пользоваться VLOOKUP в Excel на разных листах, в разных файлах?. Часто пользователи создают некие справочники, с которыми необходимо сравнить и найти соответствия. Различия работы в таких условиях заключается в несколько ином виде поля «Таблица » в окне аргументов функций. Перед введением диапазона необходимо проставить номер листа (если данные находятся в одной книге) или наименование файла (если данные не в одной книге). Если в вышеуказанном примере прайс скопировать в отдельный файл, назвать его «Прайс» и, при помощи функции ВПР, искать там цены, это будет выглядеть следующим образом:

Все вышеуказанные действия можно сделать при помощи ссылки. Нужно поставить курсор в поле «Таблица», перейти в нужный файл и мышью выделить диапазон. Иногда бывает, что таблица не вставляется как ссылка в окно. Тогда необходимо сделать следующее: открыть первоначальную таблицу, где нужно произвести расчеты, затем через меню «Файл» — «Открыть» найти вторую таблицу. Открытые таким образом файлы взаимодействуют безотказно.

Все вышеуказанные действия можно сделать при помощи ссылки. Нужно поставить курсор в поле «Таблица», перейти в нужный файл и мышью выделить диапазон. Иногда бывает, что таблица не вставляется как ссылка в окно. Тогда необходимо сделать следующее: открыть первоначальную таблицу, где нужно произвести расчеты, затем через меню «Файл» — «Открыть» найти вторую таблицу. Открытые таким образом файлы взаимодействуют безотказно.

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

Формула ВПР в Ексель с рядом условий

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

поставщик материал цена

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

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

Пошаговая инструкция включает ряд действий:

Добавление в таблицу крайнего левого столбца (важно!), объединение «Поставщиков» и «Материалов».

первое действие

Объединение искомых критериев по аналогии:

Размещение курсора в нужном месте и установка аргументов для формулы:

Ексель осуществляет поиск нужной цены.

Детальное рассмотрение формулы:

  1. Что искать.
  2. Где искать.
  3. Какие данные брать.

Возможность работы с несколькими условиями

Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

  1. Создать два (или, при необходимости, более) условий для поиска.
  2. Добавить новый столбец, в который в процессе работы функции добавятся все прочие столбцы, по которым происходит поиск товара.
  3. В полученном столбце, по вышеописанному алгоритму, вводим уже знакомую нам формулу функции VLOOKUP.

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

Формулы ВПР и выпадающие списки

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

Для создания раскрывающегося списка следует произвести ряд действий:

Поставить курсор в ячейку Е8, где планируется размещение списка.

Открыть закладку «Данные». Меню «Проверить данные».

Выбрать тип данных – «Списки». Источники – диапазон с названиями материалов.

параметры список источники

При нажатии кнопки ОК – будет создан раскрывающийся список.

Остается задать функцию, позволяющую при выборе материалов, отображать в графе цена соответствующие значения. Следует установить курсор в ячейке Е9 (где будет отображаться цена).

Открыть «Мастер функций» и выбрать ВПР.

Первый аргумент – «Искомое значение» — ячейки с раскрывающимися списками. Таблица – диапазон с наименованием материала и цен. Колонка — 2. Функция будет отображаться в следующем формате:

Остается нажать ВВОД и наслаждаться результатом.

При смене материала – изменяется цена:

Так функционирует выпадающий список в Excel с функцией ВПР. Все осуществляется в авторежиме, в считанные секунды.

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

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

Особенности работы с формулой ВПР

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

  1. При использовании опции для ряда ячеек путем указания формулы в одной из них и копированием в остальные, важно контролировать относительность и абсолютность ссылок. В ВПР критерии (первые поля) должны иметь относительные ссылки (без $), что определяется наличием собственных критериев у каждой ячейки. Диапазоны должны иметь абсолютные ссылки (адреса диапазонов указывают через $). В противном случае при копировании формул диапазон «поплывет» вниз и многие показатели не отобразятся в поиске, так как искать будет негде.
  2. Номера столбцов, указываемые в третьем поле «Номер столбца» при использовании Мастера функций, должны отсчитываться с колонки критериев.
  3. При отсутствии критериев в таблицах, где осуществляется поиск данных, выпадает ошибка #Н/Д, вызывающая сложности при подсчете итогов (суммы, средней и др.). Для решения проблемы можно воспользоваться функцией СУММЕСЛИ (вместо ВПР) или ЕСЛИОШИБКА (поставить перед ВПР).
  4. При использовании числовых значений вместо критериев (кодов, артикул), то формула ВПР имеет повышенную чувствительность к форматам ячейки. При наличии в одной таблице критериев в числовом формате, а в другой в текстовом, то при полном совпадении показателей отобразится ошибка #Н/Д. Достаточно осуществить проверку совпадений формата полей с критериями и сделать их идентичными или воспользоваться функцией СУММЕСЛИ (для нее формат не имеет значения).
  5. Не рекомендуется использовать длинные критерии с целью уменьшения вероятности «случайных» различий. Например, наличие лишнего пробела между словами или одной неправильной буквы приведет к отсутствию сопоставимости одинаковых значений критериев. Артикулы или штрихкоды товаров годятся, но названия из нескольких слов не рекомендованы для критериев.
  6. Функции ВПР из таблицы с искомыми данными выдают первый сверху показатель. Если во второй таблице, откуда «подтягиваются» данные, имеется ряд ячеек с одинаковыми критериями, то в пределах выделенного диапазона ВПР захватывается первый сверху показатель. Это важно учитывать. Например, при необходимости к цене товаров подтянуть количество из другого диапазона, а там этот товар повторяется в нескольких графах, тогда к цене подтянутся первые сверху показатели (количество), другие останутся проигнорированными.
  7. Наличие последнего параметра в виде цифры 0 (нуль) обязательно. В противном случае формула криво работает.
  8. После применения функции ВПР, формулу рекомендуется сразу удалять, оставив лишь полученные результаты. Порядок действий требует выделения диапазона с полученными результатами, использования кнопки «скопировать» и установки значений на это место с помощью специальной вставки. При размещении таблиц в разных книгах Excel, более удобным вариантом станет разрыв внешних связей (оставив данные) с помощью инструмента, в разделе Данные → Изменить связи.

изменить связи

После вызова функции разрыва внешних связей отобразится диалоговое окно, где остается активировать кнопку «Разрыв связи» и «Закрыть».

разорвать свзь

Это способствует удалению сразу всех внешних ссылок.

  1. Альтернативным вариантом ВПР выступает опция ГПР. Разница состоит в просмотре списка данных по горизонтали.

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

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

  1. Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
  2. В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню «Правка» — «Найти» вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
  3. Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной — текстовый, а у другой — числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
  4. В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
  5. Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.

Причем при организации новой сводной таблицы заданные искомые критерии могут находиться в любом порядке и последовательности и не обязательно вмещаться полным списком (частичная выборка).

Отзывы

Анна Будкова, 43 года, Новосибирск

Давно работаю с таблицами Ексель и для решения проблемы с засорением ячеек лишними пробелами, использую функцию очистки СЖПРОБЕЛЫ (TRIM).

лишние пробелы

При возникновении разного формата данных, рекомендую обратить внимание:

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если в A7 указан текстовый формат, а в таблице — числовые значения;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — в обратном порядке.

Для перевода текста в числовые значения предусмотрено несколько способов:

  • Двойного отрицания —D7.
  • Умножения на один — D7*1.
  • Суммирования с нулём D7+0.
  • Возведения в 1-ю степень D7^1.

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

Инна Кремпович, 31 год, Сочи

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

Для построения синтаксиса функции ВПР, рекомендую подготовить следующую информацию:

  • Значений для поиска.
  • Диапазона, где указан искомый показатель (в первой колонке таблицы). Если искомый параметр имеется в ячейке C2, таблица должна начинаться с C.
  • Номер колонки в таблице, где имеется «подтягиваемое» значение. Так, если диапазон задан B2:D11, я считаю B первой колонкой, C — второй.
  • Для получения приблизительного совпадения можно указать слово ИСТИНА, для точного — ЛОЖЬ. Если я ничего не указываю, по умолчанию подбирается вариант ИСТИНА.

Остается объединить все перечисленные выше аргументы:

=ВПР(искомый показатель; диапазон с искомыми данными; номер колонки в таблице с «подтягиваемыми данными; ИСТИНА/ЛОЖЬ).

Привожу ниже пример настройки функции ВПР для получения цены тормозных дисков, равной 85,73.

истина ложь

  • Ячейка D13 включает искомое_значение.
  • Ячейки B2:E11 (с желтой заливкой) выступают в качестве таблицы или диапазона, содержащего искомое значение.
  • 3 — номер_столбца в диапазоне, содержащем «подтягиваемое» значение. Третий столбец указывает на цены деталей, поэтому результат формулы укажет на значения из этой колонки.
  • Аргумент интервальный_просмотр (ЛОЖЬ) позволяет получить точные совпадения.

Результат формулы ВПР отображает цену тормозных дисков, равную 85,73.

Анна Будкова, 25 лет, Новосибирск

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

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

Ольга Новикова, 32 года, СПб

Из практики знаю, что многие часто забывают сделать ссылку массива абсолютной, что приводит к его «плывучести» при протягивании. Например, вместо A3:C4 важно указывать $A$3:$C$4. Я рекомендую размещать справочный массив на отдельных листах рабочей книги. Он не будет путаться под руками, да и лучше сохранится. А еще лучше, объявить эту таблицу именованным диапазоном.

При указании массива многие пользуются конструкцией типа A:C, отображая столбцы полностью. Это позволяет предотвратить отслеживание того факта, что массив включает все необходимые строчки. При добавлении строк на лист с исходными данными, диапазон, заданный, как A:C, не потребует корректировки. Очень удобно, рекомендую.

Аргументы функции

Канал ДНЕВНИК ПРОГРАММИСТА

Жизнь программиста и интересные обзоры всего. Подпишись, чтобы не пропустить новые видео.

Для применения функции необходимо встать в результирующую ячейку, выбрать на ленте вкладку «Формулы» — «Ссылки и массивы» — «ВПР». В ячейке появилась надпись «=ВПР(«. Теперь необходимо правильно ввести аргументы функции. Можно сделать это через точку с запятой прямо в строке формул. Однако начинающему пользователю удобнее это сделать через диалоговое окно аргументов функции.

Вам будет интересно:BYD S6: фото, характеристики, особенности автомобиля и отзывы владельцев

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

  • искомое значение — что искать;
  • таблица — где искать;
  • номер столбца — в каком столбце искать;
  • интервальный просмотр — отсортировано.

Как работает ВПР. Полезный пример

Чтобы лучше понять принцип работы VLOOKUP, перейдём к рассмотрению конкретных примеров. Возьмём простейшую таблицу с двумя колонками. Пусть она обозначает код и наименование товара.

После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP.

Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.

Рис.4 – пример поиска в простой таблице

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

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

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

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

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

Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.

Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.

Рис.5 – второй пример для ВПР

Рейтинг
( 2 оценки, среднее 4.5 из 5 )
Понравилась статья? Поделиться с друзьями: