Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.
Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.
В этой статье выбран нестандартный подход: акцент сделан не на саму функцию, а на те задачи, которые можно решить с ее помощью.
Синтаксис функции
ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
Искомое_значение - это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .
Таблица - ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Часто левый столбец называется ключевым . Если первый столбец не содержит искомое_значение , #Н/Д.
Номер_столбца - номер столбца Таблицы , из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).
Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.
Ниже в статье рассмотрены популярные задачи, которые можно решить с использованием функции ВПР() .
Задача1. Справочник товаров
Пусть дана исходная таблица (см. файл примера лист Справочник ).
Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .
Примечание . Это "классическая" задача для использования ВПР() (см. статью ).
Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).
Для вывода Цены
используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ)
(значение параметра номер_столбца
нужно задать =3).
Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул, то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать (см. ячейку Е9 ).
Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.
При решении таких задач ключевой столбец лучше предварительно (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.
В также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)
В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
Примечание . Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).
Задача2. Поиск ближайшего числа
Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.
Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:
- Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
- Ключевой столбец должен быть обязательно отсортирован по возрастанию;
- Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.
Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)
Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)
Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист "Поиск ближайшего числа" ). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.
Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.
Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе . Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.
Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена . Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .
Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА - последнее (см. картинку ниже).
Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() +ИНДЕКС() или ПРОСМОТР() .
ВПР – это функция Excel, позволяющая выполнять поиск в определенном столбце по данным из другого столбца. Функция ВПР в Excel используется также и для переноса данных из одной таблицы в другую. Существует три условия:
- Таблицы должны располагаться в одной книге Excel.
- Искать можно только среди статических данных (не формул).
- Условие поиска должно располагаться в первом столбце используемых данных.
Формула ВПР в Excel
Синтаксис ВПР в русифицированном Excel имеет вид:
ВПР (критерий поиска; диапазон данных; номер столбца с результатом; условие поиска)
В скобках указаны аргументы, необходимые для поиска итогового результата.
Критерий поиска
Адрес ячейки листа Excel, в которой указываются данные для осуществления поиска в таблице.
Диапазон данных
Все адреса, среди которых осуществляется поиск. В качестве первого столбца следует указать тот, в котором расположен критерий поиска.
Номер столбца для итогового значения
Номер столбца, откуда будет браться значение при найденном совпадении.
Условие для поиска
Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).
ВПР в Excel: примеры функции
Принцип работы функции прост. Первый аргумент содержит критерий для поиска. Как только найдено совпадение в таблице (второй аргумент), то из нужного столбца (третий аргумент) найденной строки берется информация и подставляется в ячейку с формулой.
Простое применение ВПР – поиск значений в таблице Excel. Он имеет значение в больших объемах данных.
Найдем количество фактически выпущенной продукции по названию месяца.
Результат выведем справа от таблицы. В ячейке с адресом H3 будем вводить искомое значение. В примере здесь будет указываться название месяца.
В ячейке H4 введем саму функцию. Это можно делать вручную, а можно воспользоваться мастером. Для вызова поставьте указатель на ячейку H4 и нажмите значок Fx около строки формул.
Откроется окно мастера функций Excel. В нем необходимо найти ВПР. Выберите в выпадающем списке «Полный алфавитный перечень» и начните набирать ВПР. Выделите найденную функцию и нажмите «ОК».
Появится окно ВПР для таблицы Excel.
Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке H3. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.
Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» - берутся все адреса между первым и последним.
Переводите курсор в третью строку и считайте, из какого столбца будут браться данные при найденном совпадении. В нашем примере это 3.
Последнюю строку оставьте пустой. По умолчанию значение будет равно 1, посмотрим, какое значение выведет наша функция. Нажмите «ОК».
Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке H3, и функция ищет пустое значение.
Введем название месяца и значение изменится.
Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.
Нажимайте «ОК». Как видим, результат изменился.
Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.
Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.
ВПР в Excel «понимает» только фиксированные значения.
Сравнение данных двух таблиц Excel
ВПР в Excel может быть использована для сравнения данных двух таблиц. Например, пусть у нас есть два листа с данными о выпуске продукции двумя цехами. Мы можем сопоставить фактический выпуск для обоих. Напомним, что для переключения между листами служат их ярлыки в нижней части окна.
На двух листах мы имеем одинаковые таблицы с разными данными.
Как видим, план выпуска у них одинаков, а вот фактический отличается. Переключаться и сравнивать построчно даже для небольших объемов данных очень неудобно. На третьем листе создадим таблицу с тремя столбцами.
В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.
Растяните формулу на весь столбец.
Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2».
После копирования Вы получите сводный отчет с двух листов.
Подстановка данных из одной таблицы Excel в другую
Выполняется это действие аналогично. Для нашего примера можно не создавать отдельную таблицу, а просто ввести функцию в столбец любой из таблиц. Покажем на примере первой. Установите указатель в последний столбец.
И в ячейку G3 поместите функцию ВПР. Диапазон опять берем с соседнего листа.
В результате столбец второй таблицы будет скопирован в первую.
Вот и вся информация о незаметной, но полезной функции ВПР в Excel для чайников. Надеемся, она поможет Вам при решении задач.
Отличного Вам дня!
Примеры использования формулы Excel с функцией ВПР позволяют узнать ее принцип действия и избежать возникновения ошибок:
- Функция подбирает заданные параметры, используя критерий из первой таблицы, обращается ко второму диапазону, откуда будут «подтягиваться» значения.
- С самой верхней ячейки колонки критериев, просматривает все значения.
- Отыскав совпадения с заданными критериями, отсчитывает заданное число колонок вправо и попадает в ячейку, где расположено искомое значение, которое «затягивается» в ту ячейку, где указана формула.
Пример применения функции ВПР
В приведенном примере требуется узнать стоимость проданного товара. Для ее расчета следует найти произведение количества и цен (колонки с данными размещены в соседних столбцах). В пустой колонке рядом прописывают формулу произведения двух ячеек и протягивают вниз до конца списка товаров.
Исходная информация может содержаться в разных диапазонах и другом порядке. Первая таблица указывает на количество реализованного товара:
Вторая - на цены:
При совпадении товаров в обеих таблицах, путем использования комбинации клавиш Ctrl+C и Ctrl+V, показатели цен можно было бы легко подставить к количеству. Но в таблицах разная очередность позиций.
Информация по многим товарам не соответствует расположенным рядом показателям. В данном случае исключена возможность прописывания формулы умножения и «протягивания» вниз для всех позиций.
Как подставить цены из второй таблицы к соответствующим показателям количества из первой, то есть цену позиции А к количеству позиции А, цену Б к количеству Б.
При использовании функции ВПР, цены из второй таблицы «подтягивают» к количеству из первой таблицы, согласно названиям товара.
Порядок действий:
В первой таблице требуется добавить новый столбец, где будут проставлены цены с помощью формулы ВПР.
Функцию ВПР вызывают через Мастера функций или прописывают вручную.
Вызов опции через Мастера заключается в активации ячейки, где будет указана формула, и нажимают на кнопку f(x) в начале строки формул. В появившемся диалоговом окне Мастера из предложенного перечня требуется указать ВПР.
Формула ВПР в Excel для чайников требует правильности заполнения полей в диалоговом окне Мастера функций:
- Первая графа «Искомое значение» позволяет установить критерии для ячейки, где будет прописана формула. В приведенном примере ячейка содержит товар «А».
- Следующая строка «Таблица». При внесении диапазона данных, она позволит отыскать нужные значения. Для примера использовалась вторая таблица с ценами. Так как цены «подтягивают» к количеству. При этом важно учесть необходимость в содержании крайним левым (первым слева) столбцом выделяемого диапазона аналогичных критериев для поиска. В примере это колонка с названием товара. Потом таблица выделяется вправо до колонки, где содержатся искомые данные (цены). Можно продлить выделение вправо, но это ни на что не повлияет, так как колонка с искомыми показателями будет однозначно определена следующим параметром. Важно, чтобы выделенные таблицы начинались с колонки критерий и захватывали интересующий столбец с данными.
- «Номер столбца» - числа, на которые колонки с искомыми данными (ценами) отстоят от колонки с критериями (названием товара). Отсчет начинается с самой колонки критериев. Если во второй таблице обе колонки расположены рядом, следует указать цифру 2 (первая – критерии, вторая - цены). Возможно размещение данных по отношению к критериям на 10 или 20 колонок. Это не имеет значения, Ексель произведет верные расчеты.
- Последняя графа «Интервальный просмотр», где указаны варианты поиска: точные (0) или приблизительные (1) совпадения критериев. Сейчас следует указать 0 (или ЛОЖЬ).
Остается кликнуть по кнопке ОК или «Ввод». При правильном внесении данных и наличии критерия в обеих таблицах, на месте указанной формулы отобразится некоторый показатель. Достаточно протянуть (или скопировать) формулу вниз до последней строчки таблицы.
Дальнейший расчет производится путем определения произведения количества и цены, протягивания формулы вниз до конца таблицы, так как пары цена-количество уже совпадают.
Альтернативным вариантом выступает прописывание формулы ВПР в ячейке, прописав между параметрами знак «;».
Допускается прописывание наименования «впр» с помощью маленьких букв, реестр не имеет значения.
Как использовать специальную вставку?
В результате использования функции ВПР остаются связанными две таблицы. При внесении изменений в прайс, поменяется и стоимость поступившего на склад товара. Во избежание подобной ситуации следует воспользоваться «Специальной вставкой».
- Выделить столбец с указанными ценами.
- Правой кнопкой мыши – «Скопировать».
- Не убирая выделений, правой кнопкой мышки – «Специальные вставки».
- Установить галку против «Значения». ОК.
В ячейках останутся лишь значения, формула аннулируется.
Как быстро сравнить две таблицы с помощью ВПР?
Функция используется для сопоставления значений в больших таблицах. Например, при наличии изменений в прайсе. Требуется сравнить старые цены с новыми.
Порядок действий:
В старом прайсе требуется создать колонку «Новые цены».
Выделить первую ячейку и выбрать формулу ВПР. Задать аргументы (см. выше). Например:
Указанная формула сообщает о необходимости взять название товара из диапазона А2:А15, осуществить его просмотр в «Новом прайсе», используя колонку А. Затем воспользоваться данными из второй колонки нового прайса (новыми ценами) и внести их в ячейку С2.
Выбор способа отображения данных позволяет их сопоставлять, определять разницу в числах и процентах.
Формула ВПР в Ексель с рядом условий
Выше были рассмотрены примеры анализа с одним условием – названием товара. На практике может появиться необходимость в сравнении нескольких диапазонов с данными и выборе значений по 2, 3-м критериям. Ниже приведена таблица для наглядного обзора:
Для поиска цены, по которой привезен гофрокартон от ОАО «Восток», следует создать 2 условия: по названию материала и по поставщикам.
Основная сложность заключается в наличии нескольких названий товара от одного поставщика.
Пошаговая инструкция включает ряд действий:
Добавление в таблицу крайнего левого столбца (важно!), объединение «Поставщиков» и «Материалов».
Объединение искомых критериев по аналогии:
Размещение курсора в нужном месте и установка аргументов для формулы:
Ексель осуществляет поиск нужной цены.
Детальное рассмотрение формулы:
- Что искать.
- Где искать.
- Какие данные брать.
Формулы ВПР и выпадающие списки
Пусть, определенные данные внесены в виде выпадающего списка. В приведенном примере ими являются «Материалы». Важно произвести настройку функции так, чтобы при выборе названия отображалась цена.
Для создания раскрывающегося списка следует произвести ряд действий:
Поставить курсор в ячейку Е8, где планируется размещение списка.
Открыть закладку «Данные». Меню «Проверить данные».
Выбрать тип данных – «Списки». Источники – диапазон с названиями материалов.
При нажатии кнопки ОК – будет создан раскрывающийся список.
Остается задать функцию, позволяющую при выборе материалов, отображать в графе цена соответствующие значения. Следует установить курсор в ячейке Е9 (где будет отображаться цена).
Открыть «Мастер функций» и выбрать ВПР.
Первый аргумент – «Искомое значение» - ячейки с раскрывающимися списками. Таблица – диапазон с наименованием материала и цен. Колонка - 2. Функция будет отображаться в следующем формате:
Остается нажать ВВОД и наслаждаться результатом.
При смене материала – изменяется цена:
Так функционирует выпадающий список в Excel с функцией ВПР. Все осуществляется в авторежиме, в считанные секунды.
Особенности работы с формулой ВПР
Перед тем, как пользоваться функцией ВПР в Excel, следует ознакомиться с ее особенностями:
- При использовании опции для ряда ячеек путем указания формулы в одной из них и копированием в остальные, важно контролировать относительность и абсолютность ссылок. В ВПР критерии (первые поля) должны иметь относительные ссылки (без $), что определяется наличием собственных критериев у каждой ячейки. Диапазоны должны иметь абсолютные ссылки (адреса диапазонов указывают через $). В противном случае при копировании формул диапазон «поплывет» вниз и многие показатели не отобразятся в поиске, так как искать будет негде.
- Номера столбцов, указываемые в третьем поле «Номер столбца» при использовании Мастера функций, должны отсчитываться с колонки критериев.
- При отсутствии критериев в таблицах, где осуществляется поиск данных, выпадает ошибка #Н/Д, вызывающая сложности при подсчете итогов (суммы, средней и др.). Для решения проблемы можно воспользоваться функцией СУММЕСЛИ (вместо ВПР) или ЕСЛИОШИБКА (поставить перед ВПР).
- При использовании числовых значений вместо критериев (кодов, артикул), то формула ВПР имеет повышенную чувствительность к форматам ячейки. При наличии в одной таблице критериев в числовом формате, а в другой в текстовом, то при полном совпадении показателей отобразится ошибка #Н/Д. Достаточно осуществить проверку совпадений формата полей с критериями и сделать их идентичными или воспользоваться функцией СУММЕСЛИ (для нее формат не имеет значения).
- Не рекомендуется использовать длинные критерии с целью уменьшения вероятности «случайных» различий. Например, наличие лишнего пробела между словами или одной неправильной буквы приведет к отсутствию сопоставимости одинаковых значений критериев. Артикулы или штрихкоды товаров годятся, но названия из нескольких слов не рекомендованы для критериев.
- Функции ВПР из таблицы с искомыми данными выдают первый сверху показатель. Если во второй таблице, откуда «подтягиваются» данные, имеется ряд ячеек с одинаковыми критериями, то в пределах выделенного диапазона ВПР захватывается первый сверху показатель. Это важно учитывать. Например, при необходимости к цене товаров подтянуть количество из другого диапазона, а там этот товар повторяется в нескольких графах, тогда к цене подтянутся первые сверху показатели (количество), другие останутся проигнорированными.
- Наличие последнего параметра в виде цифры 0 (нуль) обязательно. В противном случае формула криво работает.
- После применения функции ВПР, формулу рекомендуется сразу удалять, оставив лишь полученные результаты. Порядок действий требует выделения диапазона с полученными результатами, использования кнопки «скопировать» и установки значений на это место с помощью специальной вставки. При размещении таблиц в разных книгах Excel, более удобным вариантом станет разрыв внешних связей (оставив данные) с помощью инструмента, в разделе Данные → Изменить связи.
После вызова функции разрыва внешних связей отобразится диалоговое окно, где остается активировать кнопку «Разрыв связи» и «Закрыть».
Это способствует удалению сразу всех внешних ссылок.
- Альтернативным вариантом ВПР выступает опция ГПР. Разница состоит в просмотре списка данных по горизонтали.
Многие наши ученики говорили нам, что очень хотят научиться использовать функцию ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные “чайники”. Итак, приступим!
Прежде чем приступить к изучению, Вы должны понять основы работы функций. Обратите внимание на раздел Формулы и функции нашего самоучителя по Microsoft Excel . ВПР работает одинаково во всех версиях Excel, она работает даже в других электронных таблицах, например, в Google Sheets.
Что такое ВПР?
Прежде всего, функция ВПР позволяет искать определённую информацию в таблицах Excel. Например, если есть список товаров с ценами, то можно найти цену определённого товара.
Сейчас мы найдём при помощи ВПР цену товара Photo frame . Вероятно, Вы и без того видите, что цена товара $9.99 , но это простой пример. Поняв, как работает функция ВПР , Вы сможете использовать ее в более сложных таблицах, и тогда она окажется действительно полезной.
Мы вставим формулу в ячейку E2 , но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:
VLOOKUP(
=ВПР(
Добавляем аргументы
Теперь добавим аргументы. Аргументы сообщают функции ВПР , что и где искать.
Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame . Так как аргумент текстовый, мы должны заключить его в кавычки:
VLOOKUP("Photo frame"
=ВПР("Photo frame"
Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16 . Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).
VLOOKUP("Photo frame",A2:B16
=ВПР("Photo frame";A2:B16
Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame . Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.
Третий аргумент – это номер столбца. Здесь проще пояснить на примере, чем на словах. Первый столбец диапазона – это 1 , второй – это 2 и так далее. В нашем примере требуется найти цену товара, а цены содержатся во втором столбце. Таким образом, нашим третьим аргументом будет значение 2 .
VLOOKUP("Photo frame",A2:B16,2
=ВПР("Photo frame";A2:B16;2
Четвёртый аргумент сообщает функции ВПР , нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:
VLOOKUP("Photo frame",A2:B16,2,FALSE)
=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)
Готово! После нажатия Enter , Вы должны получить ответ: 9.99 .
Как работает функция ВПР?
Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame , функция переходит во второй столбец, чтобы найти цену.
ВПР – сокращение от В ертикальный ПР осмотр, VLOOKUP – от V ertical LOOKUP .
Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:
VLOOKUP("T-shirt",A2:B16,2,FALSE)
=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)
VLOOKUP("Gift basket",A2:B16,2,FALSE)
=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)
Другой пример
Следующий пример будет чуть потруднее, готовы? Представьте, что в таблице появился третий столбец, который хранит категорию каждого товара. На этот раз, вместо цены, мы определим категорию.
Чтобы определить категорию, необходимо изменить второй и третий аргументы в нашей формуле. Во-первых, изменяем диапазон на A2:C16 , чтобы он включал третий столбец. Далее, изменяем номер столбца на 3 , поскольку категории содержатся в третьем столбце.
VLOOKUP("Gift basket",A2:C16,3,FALSE)
=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)
Когда Вы нажмёте Enter , то увидите, что товар Gift basket находится в категории Gifts .
Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:
- Цену coffee mug
- Категорию landscape painting
- Цену serving bowl
- Категорию s carf
Теперь Вам известны основы работы с функцией ВПР в Excel . Продвинутые пользователи используют ВПР самыми различными способами, но, на самом деле, многое можно сделать и с теми техниками, что мы описали. Например, если у Вас есть список контактов, то Вы сможете найти телефонный номер человека по его имени. Если же в списке контактов есть столбец с адресом электронной почты или названием компании, Вы можете искать и эти данные, просто изменив второй и третий аргументы, как мы уже делали в предыдущем примере. Возможности Excel безграничны!