Изучите основы использования функции ВПР.
Использование ВПР
-
В строке формул введите =ВПР().
-
В скобках введите значение подстановки, а затем запятую. Это может быть фактическое значение или пустая ячейка, которая будет содержать значение : (H2,
-
Введите массив таблицы или таблицу подстановки, диапазон данных, которые требуется выполнить поиск, и запятую (H2, B3:F25,
-
Введите номер индекса столбца. Это столбец, в котором вы думаете ответы, и он должен быть справа от ваших значений поиска: (H2,B3:F25;3,
-
Введите значение подстановки диапазона : TRUE или FALSE. TRUE находит частичные совпадения, FALSE — точные совпадения. Готовая формула выглядит примерно так: =VLOOKUP(H2;B3:F25;3;FALSE)
Хотите узнать больше?
Функция ВПР
Краткий справочный карта: средство обновления ВПР
Краткие справочные карта: советы по устранению неполадок ВПР
Функция ВПР в программе Microsoft Excel
Смотрите такжеи с «кокосом» функция наш прайс-лист. Для крайнем левом столбце определенного материала в численную и процентную «подтянуть» в первую подставит цену из наибольшее число, которое находит значение, которое не должен содержатьтаблицеИскомое_значение из одной таблицы функциональную клавишу функции ВПР из
Работа с обобщающей таблицейТЕКСТ
Определение функции ВПР
попытается найти товар ссылки используем собственное указанной таблицы (прайс-листа) графе цена появлялась разницу. таблицу. «Интервальный просмотр» второй таблицы в меньше или равно больше искомого, то повторов (в этомотсортирован в алфавитном- это значение, в другую, сF4 соседней таблицы, которая подразумевает подтягивание вдля преобразования форматов
Пример использования ВПР
с наименованием, которое имя «Прайс» данное двигаясь сверху-вниз и,
соответствующая цифра. СтавимДо сих пор мы — ЛОЖЬ. Т.к. первую. И посредством заданному. она выводит значение, смысл артикула, однозначно порядке или по которое Вы пытаетесь помощью функции ВПР.. После этого к представляет собой прайс-лист. неё значений из данных. Выглядеть это максимально похоже на ранее. Если вы найдя его, выдает курсор в ячейку предлагали для анализа нам нужны точные, обычного умножения мыЕсли нужно найти по которое расположено на определяющего товар). В возрастанию. Это способ
- найти в столбцеКак видим, функция ВПР ссылке добавляются знакиКликаем по верхней ячейке других таблиц. Если будет примерно так: «кокос» и выдаст не давали имя, содержимое соседней ячейки
- Е9 (где должна только одно условие а не приблизительные найдем искомое. настоящему ближайшее к строку выше его). противном случае будет используется в функции с данными.
- не так сложна, доллара и она (C3) в столбце таблиц очень много,=ВПР(ТЕКСТ(B3);прайс;0) цену для этого то можно просто (23 руб.) Схематически будет появляться цена).
- – наименование материала. значения.Алгоритм действий: искомому значению, то ВПР() тутПредположим, что нужно найти выведено самое верхнее по умолчанию, еслиИскомое_значение
- как кажется на превращается в абсолютную.«Цена» ручной перенос заберетФункция не может найти наименования. В большинстве
- выделить таблицу, но работу этой функцииОткрываем «Мастер функций» и На практике жеНажимаем ОК. А затемПриведем первую таблицу в
- не поможет. Такого товар, у которого значение. не указан другой.может быть числом или первый взгляд. РазобратьсяВ следующей графев первой таблице. огромное количество времени, нужного значения, потому случаев такая приблизительная не забудьте нажать можно представить так: выбираем ВПР. нередко требуется сравнить «размножаем» функцию по нужный нам вид.
- рода задачи решены цена равна илиПри решении таких задачНиже в статье рассмотрены текстом, но чаще в её применении«Номер столбца» Затем, жмем на а если данные что в коде подстановка может сыграть потом клавишуДля простоты дальнейшего использованияПервый аргумент – «Искомое несколько диапазонов с всему столбцу: цепляем
- Добавим столбцы «Цена» в разделе Ближайшее наиболее близка к ключевой столбец лучше популярные задачи, которые всего ищут именно не очень трудно,нам нужно указать значок постоянно обновляются, то присутствуют пробелы или с пользователем злуюF4 функции сразу сделайте значение» — ячейка данными и выбрать мышью правый нижний и «Стоимость/Сумма». Установим ЧИСЛО. Там же можно искомой. предварительно отсортировать (это также можно решить с число. Искомое значение должно зато освоение этого номер того столбца,
«Вставить функцию» это уже будет невидимые непечатаемые знаки шутку, подставив значение, чтобы закрепить ссылку одну вещь - с выпадающим списком. значение по 2, угол и тянем денежный формат для найти решение задачиЧтобы использовать функцию ВПР()
поможет сделать Выпадающий использованием функции ВПР(). находиться в первом инструмента сэкономит вам откуда будем выводить
, который расположен перед сизифов труд. К (перенос строки и не того товара, знаками доллара, т.к. дайте диапазону ячеек Таблица – диапазон 3-м и т.д. вниз. Получаем необходимый новых ячеек.
о поиске ближайшего
lumpics.ru
Использование функции ВПР
для решения этой список нагляднее). КромеПусть дана исходная таблица (самом левом) столбце массу времени при значения. Этот столбец строкой формул. счастью, существует функция т.п.). В этом который был на в противном случае
Использование функции ВПР
прайс-листа собственное имя. с названиями материалов
Основные элементы функции ВПР
критериям. результат.Выделяем первую ячейку в
Поиск значений на другом листе
при несортированном ключевом задачи нужно выполнить того, в случае
Копирование формулы с функцией ВПР
(см. файл примера диапазона ячеек, указанного работе с таблицами. располагается в выделеннойВ открывшемся окне мастера
Содержание курса
ВПР, которая предлагает случае можно использовать самом деле! Так
support.office.com
Функция ВПР() в MS EXCEL
она будет соскальзывать Для этого выделите и ценами. Столбец,Таблица для примера:Теперь найти стоимость материалов столбце «Цена». В столбце. несколько условий:
несортированного списка, ВПР() с лист Справочник). вАвтор: Максим Тютюшев
выше области таблицы. функций выбираем категорию возможность автоматической выборки текстовые функции что для большинства при копировании нашей все ячейки прайс-листа
Синтаксис функции
соответственно, 2. Функция
Предположим, нам нужно найти, не составит труда: нашем примере –ПримечаниеКлючевой столбец, по которому параметромЗадача состоит в том,таблицеУзнайте, как использовать функцию Так как таблица«Ссылки и массивы» данных. Давайте рассмотримСЖПРОБЕЛЫ (TRIM) реальных бизнес-задач приблизительный формулы вниз, на кроме «шапки» (G3:H19),
приобрела следующий вид: по какой цене количество * цену. D2. Вызываем «Мастер. Для удобства, строка должен производиться поиск,Интервальный_просмотр чтобы, выбрав нужный. ВПР для поиска состоит из двух. Затем, из представленного конкретные примеры работыи поиск лучше не остальные ячейки столбца выберите в меню . привезли гофрированный картонФункция ВПР связала две функций» с помощью таблицы, содержащая найденное должен быть самымИСТИНА (или опущен) Артикул товара, вывести
Таблица - данных в большой столбцов, а столбец набора функций выбираем этой функции.ПЕЧСИМВ (CLEAN) разрешать. Исключением является D3:D30.Вставка — Имя -
Нажимаем ВВОД и наслаждаемся от ОАО «Восток». таблицы. Если поменяется кнопки «fx» (в решение, выделена Условным форматированием. левым в таблице; работать не будет. его Наименование иссылка на диапазон таблице и на с ценами является«ВПР»Скачать последнюю версиюдля их удаления: случай, когда мыНомер_столбца (Column index number) Присвоить (Insert - результатом.
Нужно задать два прайс, то и начале строки формул) Это можно сделать
Задача1. Справочник товаров
Ключевой столбец должен бытьВ файле примера лист Справочник Цену.
ячеек. В левом других листах в вторым, то ставим. Жмем на кнопку Excel
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0) ищем числа, а- порядковый номер Name — Define)
Изменяем материал – меняется условия для поиска изменится стоимость поступивших или нажав комбинацию с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10). обязательно отсортирован по также рассмотрены альтернативныеПримечание столбце таблицы ищется большой книге. В номер«OK»Название функции ВПР расшифровывается,
=VLOOKUP(TRIM(CLEAN(B3));прайс;0) не текст - (не буква!) столбцаили нажмите
цена: по наименованию материала на склад материалов горячих клавиш SHIFT+F3.Примечание возрастанию; формулы (получим тот. Это «классическая» задача дляИскомое_значение этом видеоролике рассматриваются«2». как «функция вертикальногоДля подавления сообщения об например, при расчете в прайс-листе изCTRL+F3Скачать пример функции ВПР и по поставщику.
(сегодня поступивших). Чтобы В категории «Ссылки: Если в ключевомЗначение параметра же результат) с использования ВПР() (см., а из столбцов все аргументы функции.
После этого открывается окно, просмотра». По-английски её ошибке Ступенчатых скидок. которого будем братьи введите любое в ExcelДело осложняется тем, что этого избежать, воспользуйтесь и массивы» находим столбце имеется значение
Интервальный_просмотр использованием функций ИНДЕКС(), статью Справочник). расположенных правее, выводится ВПР и даныВ последней графе в которое нужно наименование звучит –#Н/Д (#N/A)Все! Осталось нажать значения цены. Первый имя (без пробелов),Так работает раскрывающийся список от одного поставщика «Специальной вставкой». функцию ВПР и
совпадающее с искомым, нужно задать ИСТИНА или ПОИСКПОЗ() и ПРОСМОТР(). ЕслиДля вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е. соответствующий результат (хотя, рекомендации о том,«Интервальный просмотр» вставить аргументы функции. VLOOKUP. Эта функция
в тех случаях,ОК столбец прайс-листа с например в Excel с поступает несколько наименований.Выделяем столбец со вставленными жмем ОК. Данную то функция с
вообще опустить. ключевой столбец (столбец значение параметра в принципе, можно как избежать ошибок.нам нужно указать Жмем на кнопку, ищет данные в когда функция неи скопировать введенную названиями имеет номерПрайс функцией ВПР. ВсеДобавляем в таблицу крайний
Задача2. Поиск ближайшего числа
ценами. функцию можно вызвать параметром Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) с артикулами) не
Интервальный_просмотр вывести можно вывестиИзучите основы использования функции значение
- расположенную справа от левом столбце изучаемого может найти точно функцию на весь
- 1, следовательно нам. Теперь в дальнейшем происходит автоматически. В
- левый столбец (важно!),Правая кнопка мыши – перейдя по закладкеИнтервальный_просмотр
Для вывода найденной цены (она
является самым левымможно задать ЛОЖЬ значение из левого ВПР. (2:37)
«0» поля ввода данных, диапазона, а затем соответствия, можно воспользоваться столбец. нужна цена из можно будет использовать течение нескольких секунд. объединив «Поставщиков» и «Копировать». «Формулы» и выбрать =ЛОЖЬ вернет первое найденное не обязательно будет в таблице, то или ИСТИНА или столбца (в этомПросмотрев этот видеоролик, вы(ЛОЖЬ) или чтобы приступить к возвращает полученное значение функцией
Функция столбца с номером это имя для Все работает быстро «Материалы».Не снимая выделения, правая из выпадающего списка значение, равное искомому, совпадать с заданной) используйте функция ВПР() не вообще опустить). Значение случае это будет ознакомитесь со всеми
«1» выбору аргумента искомого в указанную ячейку.ЕСЛИОШИБКАВПР (VLOOKUP) 2. ссылки на прайс-лист. и качественно. НужноТаким же образом объединяем кнопка мыши – «Ссылки и массивы».
а с параметром формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА) применима. В этом параметра само аргументами функции. (3:04)
(ИСТИНА). В первом значения. Попросту говоря, ВПР(IFERROR)возвращает ошибку #Н/ДИнтервальный_просмотр (Range Lookup)Теперь используем функцию только разобраться с искомые критерии запроса: «Специальная вставка».Откроется окно с аргументами =ИСТИНА — последнее
Как видно из картинки случае нужно использоватьномер_столбцаискомое_значениеВы узнаете, как искать случае, будут выводитьсяТак как у нас
excel2.ru
Функция ВПР в Excel для чайников и не только
позволяет переставлять значения. Так, например, вот (#N/A) если:- в этоВПР этой функцией.
Теперь ставим курсор вПоставить галочку напротив «Значения». функции. В поле (см. картинку ниже). выше, ВПР() нашла
Как пользоваться функцией ВПР в Excel
альтернативные формулы. Связканужно задать =2,)). Часто левый столбец значения на других только точные совпадения,
искомое значение для из ячейки одной такая конструкция перехватывает
Включен точный поиск (аргумент поле можно вводить. Выделите ячейку, кудаКому лень или нет нужном месте и ОК. «Искомое значение» -Если столбец, по которому
наибольшую цену, которая
- функций ИНДЕКС(), ПОИСКПОЗ() образуют так называемый т.к. номер столбца называется листах. (2:37) а во втором ячейки C3, это
- таблицы, в другую любые ошибки создаваемыеИнтервальный просмотр=0 только два значения: она будет введена времени читать - задаем аргументы дляФормула в ячейках исчезнет. диапазон данных первого производится поиск не меньше или равна «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1) Наименование равен 2ключевымВы узнаете, как использовать — наиболее приближенные.«Картофель» таблицу. Выясним, как
- ВПР и заменяет) и искомого наименования ЛОЖЬ или ИСТИНА: (D3) и откройте смотрим видео. Подробности функции: . Excel Останутся только значения. столбца из таблицы самый левый, то заданной (см. файл
- В файле примера лист Справочник показано, что (Ключевой столбец всегда. Если первый столбец абсолютные ссылки на Так как наименование, то и выделяем пользоваться функцией VLOOKUP их нулями: нет вЕсли введено значение
- вкладку и нюансы - находит нужную цену. с количеством поступивших ВПР() не поможет. примера лист «Поиск
- формулы применимы и номер 1). не содержит ячейки, чтобы скопировать продуктов – это соответствующее значение. Возвращаемся в Excel.=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)Таблице0
Формулы — Вставка функции в тексте ниже.Рассмотрим формулу детально:Функция помогает сопоставить значения материалов. Это те В этом случае ближайшего числа»). Это
для ключевых столбцовДля вывода Цены используйтеискомое_значение
формулу вниз по текстовые данные, то к окну аргументовВзглянем, как работает функция=IFERROR(VLOOKUP(B3;прайс;2;0);0).или (Formulas — Insert
- Итак, имеем две таблицыЧто ищем.
- в огромных таблицах. значения, которые Excel
- нужно использовать функции связано следует из содержащих текстовые значения,
- аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ,
столбцу. (3:30) они не могут
функции.
Быстрое сравнение двух таблиц с помощью ВПР
ВПР на конкретномЕсли нужно извлечь неВключен приблизительный поиск (ЛОЖЬ (FALSE) Function) -
- Где ищем. Допустим, поменялся прайс.
- должен найти во ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР(). того как функция т.к. артикул частономер_столбцато функция возвращаетДополнительные курсы см. на быть приближенными, вТочно таким же образом примере. одно значение аИнтервальный просмотр=1, то фактически это. В категориитаблицу заказовКакие данные берем.
Нам нужно сравнить второй таблице.Функция ВПР в Excel производит поиск: если функция ВПР() находит
Функция ВПР в Excel с несколькими условиями
бывает текстовым значением.нужно задать =3). значение ошибки сайте Обучение работе отличие от числовых кликаем по значкуУ нас имеется две сразу весь набор), но означает, что разрешенСсылки и массивы (Lookup
и
Допустим, какие-то данные у старые цены сСледующий аргумент – «Таблица». позволяет данные из значение, которое больше Также задача решенаКлючевой столбец в нашем #Н/Д.
с Microsoft Office. данных, поэтому нам справа от поля
- таблицы. Первая из (если их встречаетсяТаблица поиск только
- and Reference)прайс-лист
- нас сделаны в новыми ценами. Это наш прайс-лист. одной таблицы переставить искомого, то она
для несортированного ключевого
- случае содержит числа
- Номер_столбца
- Функция ВПР(), английский вариант
Функция ВПР и выпадающий список
нужно поставить значение ввода данных, для них представляет собой несколько разных), то, в которой происходитточного соответствиянайдите функцию:
виде раскрывающегося списка.
- В старом прайсе делаем Ставим курсор в в соответствующие ячейки
- выводит значение, которое столбца.
- и должен гарантировано- номер столбца VLOOKUP(), ищет значение«0»
- выбора таблицы, откуда таблицу закупок, в
придется шаманить с поиск не отсортирована, т.е. если функцияВПР (VLOOKUP)Задача — подставить цены В нашем примере столбец «Новая цена». поле аргумента. Переходим
- второй. Ее английское расположено на строку
- Примечание содержать искомое значениеТаблицы в первом (в. Далее, жмем на будут подтягиваться значения. которой размещены наименования формулой массива. по возрастанию наименований.
- не найдет ви нажмите
из прайс-листа в – «Материалы». Необходимо
Выделяем первую ячейку и на лист с
наименование – VLOOKUP. выше его. Как. Для удобства, строка (условие задачи). Если первый, из которого нужно самом левом) столбце кнопкуВыделяем всю область второй продуктов питания. В
exceltable.com
Использование функции ВПР (VLOOKUP) для подстановки значений
Усовершенствованный вариант функции ВПРФормат ячейки, откуда берется прайс-листе укзанного вОК таблицу заказов автоматически,
Постановка задачи
настроить функцию так, выбираем функцию ВПР. ценами. Выделяем диапазонОчень удобная и часто следствие, если искомое таблицы, содержащая найденное
столбец не содержит искомый выводить результат. Самый таблицы и возвращает«OK» таблицы, где будет следующей колонке после (VLOOKUP 2).
Решение
искомое значение наименования таблице заказов нестандартного. Появится окно ввода ориентируясь на название чтобы при выборе Задаем аргументы (см. с наименованием материалов используемая. Т.к. сопоставить значение меньше минимального решение, выделена Условным форматированием. артикул левый столбец (ключевой) значение из той. производиться поиск значений, наименования расположено значениеБыстрый расчет ступенчатых (диапазонных) (например B3 в товара (если будет аргументов для функции:
товара с тем, наименования появлялась цена. выше). Для нашего и ценами. Показываем, вручную диапазоны с в ключевом столбце, (см. статью Выделение, имеет номер 1 же строки, ноКак видим, цена картофеля кроме шапки. Опять количества товара, который скидок при помощи нашем случае) и введено, например, «Кокос»),Заполняем их по очереди: чтобы потом можноСначала сделаем раскрывающийся список: примера: . Это какие значения функция десятками тысяч наименований
то функцию вернет строк таблицы вто функция возвращает значение (по нему производится другого столбца таблицы. подтянулась в таблицу возвращаемся к окну требуется закупить. Далее функции ВПР. формат ячеек первого то она выдастИскомое значение (Lookup Value) было посчитать стоимость.Ставим курсор в ячейку значит, что нужно должна сопоставить. проблематично. ошибку
MS EXCEL в
- ошибки поиск).Функция ВПР() является одной из прайс-листа. Чтобы аргументов функции. следует цена. ИКак сделать «левый ВПР» столбца (F3:F19) таблицы ошибку #Н/Д (нет
- - то наименованиеВ наборе функций Excel, Е8, где и взять наименование материалаЧтобы Excel ссылался непосредственноДопустим, на склад предприятия#Н/Д. зависимости от условия #Н/Д. Параметр из наиболее используемых не проделывать такуюДля того, чтобы выбранные в последней колонке с помощью функций отличаются (например, числовой данных). товара, которое функция в категории будет этот список. из диапазона А2:А15, на эти данные,
- по производству тарыНайденное значение может быть в ячейке).Это может произойти, например,интервальный_просмотр в EXCEL, поэтому сложную процедуру с значения сделать из – общая стоимость ИНДЕКС и ПОИСКПОЗ и текстовый). ЭтотЕсли введено значение
- должна найти вСсылки и массивыЗаходим на вкладку «Данные». посмотреть его в ссылку нужно зафиксировать.
-
- и упаковки поступили далеко не самымПримечание при опечатке приможет принимать 2 рассмотрим ее подробно. другими товарными наименованиями, относительных абсолютными, а закупки конкретного наименованияКак при помощи функции случай особенно характерен1 крайнем левом столбце(Lookup and reference) Меню «Проверка данных». «Новом прайсе» в Выделяем значение поля
- материалы в определенном ближайшим. Например, если. Никогда не используйте вводе артикула. Чтобы не ошибиться значения: ИСТИНА (ищетсяВ этой статье выбран просто становимся в это нам нужно, товара, которая рассчитывается ВПР (VLOOKUP) заполнять при использовании вместоили прайс-листа. В нашемимеется функцияВыбираем тип данных – столбце А. Затем «Таблица» и нажимаем количестве. попытаться найти ближайшую ВПР() с параметром с вводом искомого значение ближайшее к критерию нестандартный подход: акцент нижний правый угол чтобы значения не по вбитой уже бланки данными из текстовых наименований числовыхИСТИНА (TRUE) случае — словоВПР «Список». Источник – взять данные из
F4. Появляется значокСтоимость материалов – в цену для 199,Интервальный_просмотр артикула можно использовать Выпадающий
Ошибки #Н/Д и их подавление
или совпадающее с ним) сделан не на заполненной ячейки, чтобы сдвинулись при последующем
- в ячейку формуле списка кодов (номера счетов,, то это значит, «Яблоки» из ячейки(VLOOKUP)
- диапазон с наименованиями второго столбца нового $. прайс-листе. Это отдельная то функция вернет ИСТИНА (или опущен) если список (см. ячейку
- и ЛОЖЬ (ищется значение саму функцию, а появился крестик. Проводим изменении таблицы, просто умножения количества наКак вытащить не первое, идентификаторы, даты и что Вы разрешаете B3.. материалов. прайса (новую цену)В поле аргумента «Номер таблица. 150 (хотя ближайшее ключевой столбец неЕ9 в точности совпадающее на те задачи, этим крестиком до выделяем ссылку в цену. А вот
а сразу все - т.п.) В этом поиск не точного,Таблица (Table Array)Эта функция ищетКогда нажмем ОК – и подставить их столбца» ставим цифруНеобходимо узнать стоимость материалов, все же 200). отсортирован по возрастанию,). с критерием). Значение ИСТИНА которые можно решить
самого низа таблицы.
поле
цену нам как значения из таблицы случае можно использовать а- таблица из заданное значение (в сформируется выпадающий список. в ячейку С2. «2». Здесь находятся поступивших на склад. Это опять следствие т.к. результат формулыПонятно, что в нашей предполагает, что первый с ее помощью.
Таким образом мы подтянули
«Таблица»
P.S.
раз и придетсяФункции VLOOKUP2 и VLOOKUP3 функцииприблизительного соответствия которой берутся искомые нашем примере этоТеперь нужно сделать так,
Ссылки по теме
- Данные, представленные таким образом, данные, которые нужно
- Для этого нужно того, что функция находит непредсказуем (если функция ВПР()
- задаче ключевой столбец столбец вВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
- все нужные данные, и жмем на подтянуть с помощью из надстройки PLEX
- Ч, т.е. в случае значения, то есть
- слово «Яблоки») в чтобы при выборе
planetaexcel.ru
можно сопоставлять. Находить
Всем привет! Сегодня мы будем подробно разбирать функцию ВПР в Excel. Я постарался написать максимально понятно для чайников конкретным, понятным и простым примером. Для начала коротко – что же такое функция ВПР, для чего она нужна и как работает?
ВПР (Функция вертикального просмотра) – это функция, которая позволяет сделать «объединение» таблиц по какому-то значению столбца. Например, в одной таблице есть столбец код товара, а во втором мы можем понять его количество и цену. В английской версии ВПР имеет название VLOOKUP.
Скорее всего вы сейчас ничего не поняли. Оно и понятно, так как подобные функции нужно разбирать на примере. Именно его я и подготовил в статье ниже. Поехали!
Читаем – как сделать умную таблицу в Excel.
Содержание
- Инструкция
- Видео
- Задать вопрос автору статьи
Инструкция
Дабы вам было проще всего разобраться с функцией ВПР с Excel, я для вас подготовил пошаговую инструкцию с примером. Я постараюсь писать все как можно подробнее. Посмотрите на скриншот ниже.
В нашем примере у нас есть две таблицы, взятые из разных источников. Одну таблицу мы взяли на складе, где у нас расположен товар. Кладовщик его подсчитал, создал табличку и отправил бухгалтеру. У бухгалтера есть вторая таблица с ценой на каждый товар.
И тут сразу же встает вопрос – а как на теперь совместить эти таблички, чтобы цены из второй подставились к первой таблице?
Конечно, мы можем просто копировать и вставить, но тут есть небольшая проблема в том, что во второй таблице есть также и товары, которых нет на складе, а значит мы их не можем учитывать. В таком случае для сопряжения обеих таблиц нам поможет формула ВПР в Excel. Если вы уже поняли суть нашей проблемы, давайте приступим к практике.
- Так как нам нужно вставить цену из второй таблицы в первую – выделяем первую пустую ячейку столбца «Цена» и жмем по кнопке вставки функции, которая находится рядом со строкой значений.
- Выбираем «Категорию» – «Ссылки и массивы» и находим заветную функцию. Чтобы её вставить, выделите её левой кнопкой мыши и кликните по кнопке «ОК».
- А теперь нам нужно её заполнить. Кликните сначала на «Искомое значение», так чтобы там начал отображаться мигающий текстовый курсор для ввода. Далее выберите ячейку с названием товара из первого столбца. В строке «Искомое значение» обычно указывается ячейка со значением, которое есть, как в первой, так и во второй таблице. Именно по этому значению мы и должны получить цену из второй таблицы.
- Теперь кликните по строке «Таблица» и полностью выделите вторую табличку, из которой мы хотим получить цену.
- Очень важный момент – выделите все адреса ячеек строки «Таблица» и нажмите по клавише:
F4
- Мы сделаем адреса абсолютными. Но зачем? – это нужно для того, чтобы в дальнейшем использовать автозаполнение для остальных товаров. Сейчас же мы работаем только с «Шоколадом» (из моего примера). Если вы пока ничего не понимаете, ничего страшного – далее я покажу все на примере.
- «Номер столбца» – здесь ставим цифру два (2), так как мы хотим получить цену из второго столбца правой таблички.
- «Интервальный просмотр» – ставим 0 (то есть ЛОЖЬ), так как в первом столбце наименованием товаров может иметь только точное значение, ведь это текст, а не приблизительное, как это бывает у чисел.
- В конце жмем «ОК». Как видите теперь мы имеем цену для шоколада из второй таблицы. А теперь применяем маркер автозаполнения.
Читаем – что такое маркер автозаполнения и как им пользоваться.
А вот и разъяснение нашего предыдущего вопроса – зачем мы ставили абсолютные ссылки для второй таблицы. Если бы мы этого не сделали, то при использовании маркера, все адреса ячеек начали бы сдвигаться, и мы получили неправильную цену. Ради интереса попробуйте убрать знаки доллара ($) из аргумента «Таблица» прям в формуле ВПР.
Вот такой вот классный пример мы с вами разобрали. Теперь вы знаете, как использовать формулу ВПР в Excel, и вы теперь не чайник, а настоящий гуру в этом вопросе. Если вы столкнулись с какой-то трудностью – напишите об этом в комментариях, и преподаватели портала WiFiGiD.RU постараются вам помочь.
Видео
С помощью функции ВПР (в переводе на английский VLOOKUP) пользователи программы Exсel имеют возможность переставлять данные из одной таблицы в другую со схожими параметрами. Эта услуга подойдёт для тех, кому приходится работать с большими списками. Ведь вписывать каждое значение отдельно может занять очень большое количество времени.
Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.
Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.
Первая – это список предметов, единицы их измерения и количество.
Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.
Чтобы подсчитать сколько стоит продукция, следует информацию из второй вставить в первую, и с помощью простого умножения произвести расчёт.
Этапы работы (инструкция):
- Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
- Отметить верхнее поле в новом.
- Выбрать раздел формулы, и нажать «Вставить функцию».
- Из предложенных категорий Excel отметить «Ссылки и массивы».
- Найти ВПР, и нажать «ОК».
- Заполнить открывшееся окно «Аргументы».
– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.
– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).
Важно! Эти показатели фиксируются, чтобы именно по ним производились расчёты программой Эксель.
Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.
Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).
Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».
В конечном итоге, окно «Аргументы» выглядит так:
- Нажать «ОК».
Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.
Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.
Как сравнить две таблицы: пошаговая инструкция для «чайников»
Функция ВПР поможет сравнить две таблицы Excel в считанные секунды, даже если данные занимают не один десяток значений. Пошаговая инструкция:
Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?
Делается это в несколько шагов:
- Открыть первую со старой информацией.
- Добавить дополнительный столбик для новых данных «Новая стоимость».
- Выделить первое пустое поле в созданном столбце (С2).
- Выбрать раздел «ВПР Формулы» и «Вставить функцию».
- Найти категорию Excel «Ссылки и массивы».
- Выбрать ВПР.
- Задать «Аргументы».
Инструкция:
– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).
– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.
Номер столбца – второй, так как именно стоимость переносится в новую.
Интервальный просмотр – ЛОЖЬ.
Заполненное окно выглядит так:
После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.
Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.
Узнайте также:
- Как перевести файл Excel в PDF,
- Как перевести файл Pdf в Ворд онлайн,
- Как делать ссылки в Word: пошаговая инструкция.
Поиск с помощью ВПР по нескольким условиям
Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников (инструкция).
Итак, имеется документ, в котором обозначены: компании, товары и цены.
Нужно найти цену на конкретный товар – гелевая ручка. Но так как каталог может быть огромным, а гелевые ручки быть не у одной компании, поиск стоимости в Эксель лучше проводить через ВПР с несколькими условиями: название компании и предмета.
Чтобы осуществить поиск следует:
- Создать слева новый столбец с объединёнными данными (название компании и товара).
Делается это просто:
- выделить крайнюю левую ячейку (А1);
- щёлкнуть ПКМ и выбрать «Вставить»;
- отметить добавление столбца и нажать «ОК».
- Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
- Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
- Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.
Инструкция:
– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).
– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).
Номер столбца – из какого столбца вывести результат (4).
Интервальный просмотр – ЛОЖЬ.
После нажатия команды «ОК», программа отобразит результат.
Как сделать выпадающий список через функцию ВПР
Чтобы сделать выпадающий список из существующего нужно следовать инструкции:
- Выбрать поле, в котором будет сформированы показатели. Например, Е2.
- Зайти в раздел «Данные», и выбрать «Проверка данных».
- Установить тип данных, как список.
- В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).
Выпадающий список готов.
Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).
- Создать новое поле с названием «Цена».
- Вставить аргументы.
– ячейка Excel, в которой находится выпадающий список (Е2).
– выделенный фрагмент с предметами и ценами (А2-В5).
Номер столбца – 2 (в нём находятся цены).
Интервальный просмотр – ЛОЖЬ.
После подтверждения команды можно пользоваться списком и просмотром цены.
Таким образом, с помощью несложных инструкций, каждый может разобраться, как пользоваться ВПР. Смотрим видео.
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
-
Выделить ячейку и вписать функцию.
-
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
В нашем случае получится такая формула:
=ВПР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
=ВПР(A2;
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
=ВПР(A2;$G$2:$H$11
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
-
Формула сканирует таблицу по вертикали.
-
Находит в самом левом столбце совпадение с искомым значением.
-
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
-
Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
=ВПР(A2;$G$2:$H$11;2
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
=ВПР(A2;$G$2:$H$11;2;0)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
-
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
-
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает
Итоги
-
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
-
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
-
Функцию можно вписать вручную или в специальном окне (Shift + F3).
-
Искомое значение – относительная ссылка, а таблица – абсолютная.
-
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
-
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
-
Порядок работы с функцией подходит для Гугл-таблиц.