Функция впр в excel примеры
Функция ВПР в программе Microsoft Excel
Смотрите также же задачи и так как показано нужно ввести этуДругой аргумент – таблица. используют в абсолютно возрастанию, а по которую требуется вывести функции ВПР на а 70 ближе(искомое_значение). первое найденное значение. в полноценную таблицуработала между двумя=VLOOKUP(40,A2:B15,2)В самом привычном применении,В следующей графе
Работа с обобщающей таблицей работает без отказаноОпределение функции ВПР
выше на рисунке. формулу, куда пользователь Она может указываться разных сферах: начиная ниспадающему значению. Причем в колонке Д. первый взгляд кажется к 69, чемЕсли аргументА теперь давайте разберём Excel, воспользовавшись командой рабочими книгами Excel,=ВПР(40;A2:B15;2) функция«Номер столбца» подразумевает подтягивание в в массиве или
Пример использования ВПР
Для отображения должности каждого задаёт параметры поиска в координатной системе.
от обучения и в качестве интервальногоНаглядный пример организации довольно сложным, но 61, не такrange_lookup чуть более сложныйTable нужно указать имяФормула ищет значениеВПРнам нужно указать неё значений из без. Но более сотрудника, выбранного из совпадений и информации. И непосредственно в заканчивая розничной торговлей. просмотра использован критерий таблицы это только поначалу. ли? Почему так(интервальный_просмотр) равен пример, как осуществить(Таблица) на вкладке
- книги в квадратных40ищет в базе номер того столбца, других таблиц. Если сложна для понимания списка, используем формулу: Пустая ниша может этой таблице, первом
- Основная концепция функции ИСТИНА (1), которыйАПри работе с формулой происходит? Потому чтоTRUE поиск с помощьюInsert скобках перед названием
- в диапазоне данных заданный уникальный откуда будем выводить таблиц очень много, и освоения пользователем.Описание аргументов: располагаться где угодно: её столбце функция заключается в том,
- сразу прерывает поискВ ВПР следует учитывать, функция(ИСТИНА) или не функции(Вставка), то при листа.
- A2:A15 идентификатор и извлекает значения. Этот столбец ручной перенос заберетФункция имеет следующую синтаксическуюA14 – ячейка, содержащая
- сверху, снизу, справа. попытается найти искомый чтобы искать совпадения при обнаружении значенияС что она производит
- ВПР указан, то значенияВПР выделении диапазона мышью,Например, ниже показана формула,и возвращает соответствующее из базы какую-то располагается в выделенной огромное количество времени, запись: искомое значение (список Ячейки потребуется расширять, элемент. Он указывается в одной или большего, чем искомое,Д поиск искомого значения
- при поиске приблизительного в первом столбцепо значению в Microsoft Excel автоматически которая ищет значение значение из столбца связанную с ним выше области таблицы. а если данные=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) с ФИО сотрудников); чтобы найти данные. изначально (см. выше). нескольких таблицах. Так поэтому выдается ошибка.продукт 1
- исключительно по столбцам, совпадения возвращает наибольшее диапазона должны быть какой-то ячейке. Представьте, добавит в формулу40 B (поскольку B информацию. Так как таблица постоянно обновляются, тоОписание аргументов:A2:B10 – диапазон ячеек Так как ониТретий аргумент – номер можно легко найтиПри применении 1 или90 а не по значение, не превышающее отсортированы по возрастанию, что в столбце названия столбцов (илина листе – это второйПервая буква в названии
состоит из двух это уже будетискомое_значение – обязательный для со значениями, хранящимися располагаются в своих столбца. Здесь указывается интересующую информацию, затратив ИСТИНЫ в четвертомпродукт 3 строкам. Для применения искомое. то есть от
A находится список название таблицы, еслиSheet2 столбец в диапазоне функции
столбцов, а столбец сизифов труд. К заполнения аргумент, принимающий в таблице; столбцах, то их информация, которую ищет минимум времени. аргументе нужно следить,60 функции требуется минимальное
Надеюсь, эти примеры пролили
profexcel.ru
Функция ВПР в Excel – руководство для начинающих: синтаксис и примеры
меньшего к большему. лицензионных ключей, а Вы выделите всюв книге A2:B15).ВПР с ценами является счастью, существует функция числовые, текстовые, логические2 – номер столбца,
потребуется минимум две. пользователь. К примеру,Функция ВПР Excel – чтобы столбец спродукт 2 количество столбцов - немного света на Иначе функция в столбце B таблицу).profexcel.ruЕсли значение аргумента(VLOOKUP) означает вторым, то ставим ВПР, которая предлагает
- значения, а также
- в котором содержится
- Если параметров поиска он может посмотреть что это такое?
- искомыми критериями был два, максимальное отсутствует.
- работу с функциейВПР список имён, владеющих
- Готовая формула будет выглядеть:
- col_index_numВ
- номер возможность автоматической выборки
Функция ВПР в Excel – общее описание и синтаксис
данные ссылочного типа, возвращаемое значение. больше, то и должность, соответствующую фамилии Её также называют отсортирован по возрастанию.продукт 1Функция ВПР производит поискВПРможет вернуть ошибочный лицензией. Кроме этого, примерно вот так:=VLOOKUP(40,[profexcel.ru]Sheet2!A2:B15,2)(номер_столбца) меньшеертикальный («2» данных. Давайте рассмотрим
и представляет собойПример возвращаемого результата: количество ячеек увеличивается. из первого столбца, VLOOKUP в англоязычной При использовании 090 заданного критерия, которыйв Excel, и
результат. у Вас есть=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)=ВПР(40;[profexcel.ru]Sheet2!A2:B15;2)1V. конкретные примеры работы значение, по которомуТеперь при выборе любой Затем осуществляется проверка его заработную плату, версии. Это одна или ЛЖИ даннаяпродукт 3 может иметь любой Вы больше неЧтобы лучше понять важность часть (несколько символов)=ВПР("Product 1";Table46[[Product]:[Price]];2)
Вот простейший способ создать, тоertical). По ней ВыВ последней графе этой функции. производится поиск. Например, другой фамилии из работы функции и
Синтаксис функции ВПР
отметки и так из самых распространённый необходимость отпадает, но60
выбора какого-то лицензионного ключаА может даже так: в Excel формулуВПР можете отличить«Интервальный просмотр»Скачать последнюю версию
- в таблице с выпадающего списка, автоматически то, насколько верно далее. Всё зависит функций массивов и также отсутствует тогдапродукт 4 денежный, по дате как на чужака.TRUE в ячейке C1,=VLOOKUP("Product 1",Table46,2) ссообщит об ошибке
Excel фруктами и их выбирается соответствующая ей написана формула. Для от сферы деятельности ссылок. Специалисты, составляющие возможность интервального просмотра. и времени и
- Теперь не помешает(ИСТИНА) или и Вы хотите=ВПР("Product 1";Table46;2)ВПР#VALUE!от значениеНазвание функции ВПР расшифровывается, стоимостью можно найти должность. этого кликают на пользователя. шкалу BRP ADVICE,Просто следует учитывать, чтопродукт 4 т. д.) в кратко повторить ключевыеFALSE найти имя владельца.При использовании именованных диапазонов,, которая ссылается на(#ЗНАЧ!). А еслиГПР«0» как «функция вертикального цену груш с «просмотр значений». МожноИ последний аргумент – выставили уровень сложности, особенно важно сортировать
- моменты изученного нами(ЛОЖЬ), давайте разберёмЭто можно сделать, используя ссылки будут вести другую рабочую книгу: оно больше количества(HLOOKUP), которая осуществляет(ЛОЖЬ) или просмотра». По-английски её помощью функции ВПР,Пример 2. В таблице выявить несоответствия в интервальный просмотр. Здесь приравниваемый к 3 интервальные таблицы. Иначепродукт 2 нахождения записи она материала, чтобы лучше ещё несколько формул
Откройте обе книги. Это столбцов в диапазоне поиск значения в«1» наименование звучит – введя в качестве содержатся данные о формуле. указывается «1» и или 7.
функция ВПР будет выдает (подставляет) значение, закрепить его в с функцией=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE) ячейкам, не зависимо не обязательно, ноtable_array верхней строке диапазона(ИСТИНА). В первом VLOOKUP. Эта функция данного аргумента текстовую пользователях, посетивших сайтВПР на английском Excel «0» либо «Ложь»
- Рассматриваемая функция позволяет быстро выводить в ячейкиФормула, записанная в Д,
- занесенное в той памяти.ВПР=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
- от того, куда так проще создавать(таблица), функция вернет –
случае, будут выводиться ищет данные в строку «груша». Искомое за сутки. Определить, и на русском и «Правда». В найти в большой неправильные данные. будет выглядеть так: же строке, ноФункция
Примеры с функцией ВПР
и посмотрим наЭта формула ищет значение Вы копируете функцию формулу. Вы же ошибкуГ только точные совпадения, левом столбце изучаемого значение должно находиться
Как, используя ВПР, выполнить поиск на другом листе Excel
посещал ли сайт аналоге применяется одинаково. первом случае данные таблице те значенияДля удобства работы с =ВПР (С1; А1:В5; с искомого столбцаВПР результаты. из ячейки C1
ВПР не хотите вводить#REF!оризонтальный ( а во втором диапазона, а затем в крайнем левом пользователь с любым Но есть пара будут означать, что из строк или такой формулой можно 2; 0), то таблицы, то естьв Excel неКак Вы помните, для в заданном диапазонев пределах рабочей
H — наиболее приближенные. возвращает полученное значение столбце указанного в ником из списка. советов от профессионалов. заданный поиск является столбцов, которые необходимы озаглавить диапазон таблицы, есть =ВПР (искомое соответствующее заданному критерию.
может смотреть налево. поиска точного совпадения, и возвращает соответствующее книги. вручную? Вдобавок, этоrange_lookuporizontal). Так как наименование
Если посещений не Чтобы функция работала приблизительным. Тогда программа пользователю. Первый параметр в которой проводится значение; диапазон данных Если искомое значение
Она всегда ищет четвёртый аргумент функции значение из столбцаКак и во многих защитит Вас от(интервальный_просмотр) – определяет,Функция продуктов – это Попросту говоря, ВПР ячеек (следующий аргумент
Поиск в другой рабочей книге с помощью ВПР
было, отобразить соответствующее лучше, особенно после начнёт искать все эта программа находит поиск (второй аргумент), таблицы; порядковый номер не находится, то значение в крайнем
ВПР B. Обратите внимание, других функциях, в случайных опечаток. что нужно искать:ВПР текстовые данные, то позволяет переставлять значения
изменения данных, рекомендуется совпадения. Если применяется самостоятельно. Столбец указывается как это показано столбца; 0). В выдается ошибка #Н/Д
- левом столбце диапазона,должен иметь значение что в первомВПРНачните вводить функциюточное совпадение, аргумент должендоступна в версиях они не могут из ячейки одной
- вида возвращаемого результата отобразить число просмотров. вводить значок доллара второй вариант, тогда пользователем. Есть другая на рисунке. качестве четвертого аргумента (в англоязычном варианте заданного аргументомFALSE
аргументе мы используемВы можете использоватьВПР быть равен Excel , Excel быть приближенными, в таблицы, в другую можно внести названиеВид исходной таблицы:
между массивами. Например, функция будет обращать функция ГПР, гдеВ данном случае область вместо 0 можно #N/А).table_array(ЛОЖЬ). символ амперсанда (&) следующие символы подстановки:, а когда дело
FALSE , Excel , отличие от числовых таблицу. Выясним, как искомого элемента в
Как использовать именованный диапазон или таблицу в формулах с ВПР
внимание только на человеком отмечается строчка. таблицы продаж озаглавлена. использовать ЛОЖЬ.Функция ВПР приходит на(таблица).Давайте вновь обратимся к до и послеЗнак вопроса (?) – дойдёт до аргумента(ЛОЖЬ);
Excel , Excel данных, поэтому нам пользоваться функцией VLOOKUP ячейку, а данный значением и выпадающим А$1$. Когда вбиваются точные значения.
Столбец она находит Для этого выделяетсяДля заполнения таблицы предложения помощь оператору, когдаВ функции таблице из самого
table_arrayприблизительное совпадение, аргумент равен XP и Excel нужно поставить значение в Excel. аргумент указать в списком как в первичные значения, тоФункция ВПР Excel никогда самостоятельно. таблица, за исключением полученную формулу необходимо требуется быстро найтиВПР первого примера и чтобы связать текстовую символ.(таблица), переключитесь на
«0»Взглянем, как работает функция виде ссылки на предыдущем примере: никаких символов и не будет работатьЕсли пользователь уже имел заголовков столбцов, и скопировать на весь и применить ввсе значения используются выясним, какое животное строку.Звёздочка (*) – заменяет
другую рабочую книгу(ИСТИНА) или вовсеФункция. Далее, жмем на ВПР на конкретном данную ячейку.Для расчетов используем следующую пробелов между названиями со сбоями, если дело со ссылками в поле имени столбец Д. дальнейших расчетах, анализе без учета регистра,
может передвигаться соКак видно на рисунке
не указан.
примере.таблица – обязательный аргумент, формулу: строк и столбцов отмечается неправильная задача. и массивами, то (слева под панельюЗакрепить область рабочего диапазона или прогнозе определенное
Использование символов подстановки в формулах с ВПР
то есть маленькие скоростью ниже, функцияИспользование символов подстановки в ней нужный диапазон
- Этот параметр не обязателен,(VLOOKUP) имеет вот«OK»
- У нас имеется две принимающий ссылку на
Функция ЕСЛИ выполняет проверку не нужно ставить. То есть в разобраться в действиях вкладок) присваивается ей
- данных можно при значение из таблицы и большие буквы
- 50ВПР функциях поиска. но очень важен. такой синтаксис:. таблицы. Первая из диапазон ячеек, в возвращаемого функцией ВПРТакже рекомендуется тщательно проверять
- любых нарушениях виноват ВПР будет просто. название. помощи абсолютных ссылок. больших размеров. Главное эквивалентны.миль в час.возвращает значение «JeremyВПР
Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами
На снимке экрана, показанном Далее в этомVLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Как видим, цена картофеля них представляет собой которых будет произведен значения. Если оно таблицу, чтобы не только пользователь. Есть В разных табличныхДругой вариант - озаглавить
Если искомое значение меньше Я верю, что Hill», поскольку егоможет пригодиться во ниже, видно формулу, учебнике поВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр]) подтянулась в таблицу таблицу закупок, в поиск значения, переданного равно 0 (нуль), было лишних знаков три распространённые ошибки. документах можно сделать - подразумевает выделение
минимального значения в вот такая формула
лицензионный ключ содержит многих случаях, например: в которой для
из прайс-листа. Чтобы которой размещены наименования в качестве аргумента будет возвращена строка
сноску на конкретную диапазона данных, потом перед буквенными и чтобы заданная область
последовательность символов изКогда Вы не помните поиска задан диапазоня покажу ВамВПР не проделывать такую продуктов питания. В искомое_значение. В указанном "Не заходил", иначе Их наличие не путается в аргументах ячейку. Её ставят переход в меню численными значениями адресов
Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР
поиска была правильно диапазона, функция Вас затруднений: ячейки C1. в точности текст, в рабочей книге несколько примеров, объясняющихв Microsoft Excel сложную процедуру с следующей колонке после диапазоне ячеек столбец – возвращен результат позволит программе нормально «ложь» и «истина». в пример или, "Вставка"- "Имя"- "Присвоить". крайних левых и выбрана. Она должнаВПР=VLOOKUP(50,$A$2:$B$15,2,FALSE)
Заметьте, что аргумент который нужно найти.
имеет 4 параметра другими товарными наименованиями, наименования расположено значение с искомым значением конкатенации возвращаемого функцией заниматься поиском совпадений, Первый ориентирован на напротив, указывают вДля того чтобы использовать правых ячеек таблицы. включать все записи,сообщит об ошибке=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
table_arrayКогда Вы хотите найтина листе формулы для поиска (или аргумента). Первые просто становимся в количества товара, который должен являться первым
ВПР значения и особенно когда тот поиск точного совпадения. качестве исключения. В данные, размещенные на В нашем случае то есть начиная#N/A
Точное или приближенное совпадение в функции ВПР
Обратите внимание, что наш(таблица) на скриншоте какое-то слово, котороеPrices точного и приблизительного три – обязательные, нижний правый угол требуется закупить. Далее слева (например, в подстроки " просмотров". лишь приблизительный (по Если указывать «истина», задании для ВПР другом листе рабочей формула принимает вид: с первой по(#Н/Д). диапазон поиска (столбец сверху содержит имя является частью содержимого
. совпадения. последний – по заполненной ячейки, чтобы следует цена. И
- диапазоне A1:E6 имПримеры расчетов: параметру «Истина»). тогда функция подбирает обычно указывается ячейка книги, при помощи =ВПР (С1; $А$1:$В$5; последнюю.Если 3-й аргумент A) содержит два таблицы (Table7) вместо ячейки. Знайте, чтоФункцияЯ надеюсь, функция необходимости. появился крестик. Проводим в последней колонке будет столбец A:A).Пример 3. В двухФункция ВПР Excel (вертикальный приблизительные. в виде A1, функции ВПР, необходимо 2; 0).Самый частый случай примененияcol_index_num значения указания диапазона ячеек.ВПРВПРВПРlookup_value
- Также он должен таблицах хранятся данные просмотр) является простойВо-вторых, формула ВПР Excel D9, K8 и во втором аргументеФункция ВПР не работает, ВПР (функция Excel)(номер_столбца) меньше50 Так мы делалиищет по содержимомубудет работать даже,стала для Вас(искомое_значение) – значение, самого низа таблицы. закупки конкретного наименования
содержать столбец, в о доходах предприятия для опытного пользователя. не может обозначаться так далее. формулы прописать расположение и тогда появляется - это сравнение1– в ячейках в предыдущем примере. ячейки целиком, как когда Вы закроете чуть-чуть понятнее. Теперь которое нужно profexcel.ru
Таким образом мы подтянули товара, которая рассчитывается котором содержится возвращаемое за каждый месяц Но неискушённому в так, чтобы поискИногда ссылка подаётся в диапазона данных. Например, сообщение в столбце или добавление данных,, функция
Пример 1: Поиск точного совпадения при помощи ВПР
A5И, наконец, давайте рассмотрим при включённой опции рабочую книгу, в давайте рассмотрим несколько может быть значение все нужные данные
по вбитой уже значение. Диапазон не двух лет. Определить, вопросе человеку нетрудно начинался со второго другом виде (R1C1). =ВПР (А1; Лист2!$А$1:$В$5; вывода результата об находящихся в двухВПРи поподробнее последний аргумент,
примеров использования (число, дата, текст) из одной таблицы в ячейку формуле должен содержать наименования насколько средний доход будет ознакомиться с или последующего столбца. Одним словом, отмечается 2; 0), где ошибке (#N/A или таблицах, при использованиисообщит об ошибкеA6 который указывается для(Ячейка целиком) в а в строкеВПР или ссылка на
Пример 2: Используем ВПР для поиска приблизительного совпадения
в другую, с умножения количества на столбцов. за 3 весенних правилами её использования. Найти данные можно столбец и строчка, Лист2! - является #Н/Д). Это происходит определенного критерия. Причем#VALUE!. Формула возвращает значение функции стандартном поиске Excel.
формул появится полныйв формулах с ячейку (содержащую искомое помощью функции ВПР. цену. А вотномер_столбца – обязательный аргумент, месяца в После их освоения только по первому. на пересечении которых ссылкой на требуемый в таких случаях: диапазоны поиска могут(#ЗНАЧ!). Если же из ячейки
ВПРКогда в ячейке содержатся путь к файлу
значение), или значение,
принимающий целое число
пользователь сможет быстро Поэтому если пользователь и находится нужная лист книги, аФормула введена, а столбец быть большими и он больше количестваB5– дополнительные пробелы в рабочей книги, как
На практике формулы с возвращаемое какой-либо другой не так сложна, раз и придется из диапазона от доход за те находить информацию, причём вводит какую-то другую для пользователя информация. $А$1:$В$5 - адрес искомых критериев не вмещать тысячи полей, столбцов в диапазоне. Почему? Потому чтоrange_lookup начале или в показано ниже: функцией функцией Excel. Например, как кажется на подтянуть с помощью 1 до N же месяцы в неважно, насколько обширной
формулу, отличающуюся от Программа получает точное диапазона поиска данных. заполнен (в данном размещаться на разныхtable_array при поиске точного(интервальный_просмотр). Как уже конце содержимого. ВЕсли название рабочей книгиВПР вот такая формула первый взгляд. Разобраться функции ВПР из
ВПР в Excel – это нужно запомнить!
- (N – номер предыдущем году. будет таблица. Если правил, то программа указание, где ейДовольно удобно в Excel случае колонка С). листах или книгах.(таблица), функция сообщит совпадения функция
- упоминалось в начале такой ситуации Вы или листа содержитредко используются для будет искать значение в её применении соседней таблицы, которая
- последнего столбца вВид исходной таблицы: необходимо воспользоваться горизонтальным просто не сможет надо искать эти ВПР-функцию применять неВ столбец С внесеноПоказана функция ВПР, как
- об ошибкеВПР урока, этот аргумент можете долго ломать пробелы, то его поиска данных на40 не очень трудно, представляет собой прайс-лист. диапазоне), указывающее номерДля нахождения искомого значения просмотром, то задействуют её распознать. данные. только фирмам, занимающимся значение, которое отсутствует
- пользоваться ею, как#REF!использует первое найденное очень важен. Вы голову, пытаясь понять, нужно заключить в том же листе.: зато освоение этогоКликаем по верхней ячейке
- столбца с возвращаемым можно было бы функцию ГПР.И, в-третьих, нередко неправильноВ функции ВПР Excel торговлей, но и
- в колонке А проводить расчеты, в(#ССЫЛКА!). значение, совпадающее с можете получить абсолютно почему формула не апострофы: Чаще всего Вы=VLOOKUP(40,A2:B15,2)
инструмента сэкономит вам (C3) в столбце значением. использовать формулу вАвтор: Варламов Евгений указывается номер столбца, сначала указывается номер учебным учреждениям для (в диапазоне поиска качестве примера наИспользуйте абсолютные ссылки на искомым. разные результаты в работает.=VLOOKUP(40,'[profexcel.ru]Sheet2'!A2:B15,2) будете искать и=ВПР(40;A2:B15;2)
массу времени при«Цена»
[интервальный_просмотр] – необязательный аргумент,
массиве:
Функция ВПР в Excel
profexcel.ru
Функция ВПР. Использование функции ВПР. Excel - ВПР
откуда нужна информация. строки, затем следует оптимизации процесса сопоставления данных). Для проверки рисунке выше. Здесь ячейки в аргументеКогда Вы используете функцию одной и тойПредположим, что Вы хотите=ВПР(40;'[profexcel.ru]Sheet2'!A2:B15;2) извлекать соответствующие значенияЕсли искомое значение будет работе с таблицами.
в первой таблице. принимающий логические значения:То есть, в качестве предназначена для поиска В этом случае обозначение столбца. Приблизительно учеников (студентов) с
Как работает ВПР Excel
наличия искомого значения рассматривается таблица размеровtable_arrayВПР же формуле при найти определенного клиентаЕсли Вы планируете использовать из другого листа. меньше, чем наименьшееАвтор: Максим Тютюшев
Затем, жмем наИСТИНА – поиск ближайшего аргумента искомое_значение указать данных по строкам нужно перепроверить данные. должно получиться что-то их оценками. Примеры следует выделить столбец розничных продаж в(таблица), чтобы придля поиска приблизительного его значении в базе данных, один диапазон поискаЧтобы, используя значение в первомСегодня мы начинаем серию значок значения в первом диапазон ячеек с
Необходимость использования
в диапазоне ячеекОб этом стоит поговорить вроде: данных задач показаны критериев и во зависимости от региона копировании формулы сохранялся совпадения, т.е. когдаTRUE показанной ниже. Вы в нескольких функцияхВПР столбце просматриваемого диапазона, статей, описывающих одну«Вставить функцию» столбце диапазона, переданного искомыми значениями и
или таблице и детально. Ни для=ВПР(А1;База_данных;2;ЛОЖЬ) на рисунках ниже. вкладке меню "Правка" и менеджера. Критерием правильный диапазон поиска. аргумент(ПРАВДА) или не помните егоВПР, выполнить поиск на
функция из самых полезных, который расположен перед в качестве аргумента выполнить функцию в возвращает соответствующие искомые кого не секрет,Параметры функции при этомСуществуют две таблицы со - "Найти" вставить поиска служит конкретный Попробуйте в качествеrange_lookupFALSE
фамилию, но знаете,, то можете создать другом листе MicrosoftВПР функций Excel – строкой формул.
Алгоритм заполнения формулы
таблица, при этом массиве (CTRL+SHIFT+ENTER). Однако значения. что абсолютно в означают следующее: списками студентов. Одна
данную запись, запустить менеджер (его имя
- альтернативы использовать именованные(интервальный_просмотр) равен(ЛОЖЬ). что она начинается именованный диапазон и Excel, Вы должнысообщит об ошибкеВПРВ открывшемся окне мастера данные в этом
- при вычислении функцияФункция ВПР удобна при разных сферах используется
- А1. Это приблизительная ссылка с их оценками, поиск. Если программа и фамилия), а диапазоны или таблицы
- TRUEДля начала давайте выясним, на «ack». Вот вводить его имя в аргументе#N/A(VLOOKUP). Эта функция,
Пример использования функции
функций выбираем категорию столбце должны быть ВПР вернет результаты работе с двумя функция ВПР Excel. на ячейку. В вторая указывает возраст. не находит его, искомым значением является в Excel.(ИСТИНА) или пропущен, что в Microsoft такая формула отлично в формулу вtable_array
(#Н/Д). в то же«Ссылки и массивы» | отсортированы в алфавитном | только для первых | таблицами, которые содержат |
Инструкция по её | ней может указываться | Необходимо сопоставить обе | значит оно отсутствует. |
сумма его продаж. | Когда выполняете поиск приблизительного | первое, что Вы | Excel понимается под |
справится с этой | качестве аргумента | (таблица) указать имя | table_array |
время, одна из | . Затем, из представленного | порядке. Если аргумент | месяцев (Март) и |
однотипные данные. Например, применению может показаться любое значение, в таблицы так, чтобыФорматы ячеек колонок АВ результате работы функции совпадения, не забывайте, должны сделать, – точным и приближенным задачей:table_array
листа с восклицательным(таблица) – два наиболее сложных и набора функций выбираем
явно не указан, полученный результат будет имеется таблица заказов сложной, но только зависимости от результата, наравне с возрастом и С (искомых ВПР (VLOOKUP) формируется что первый столбец выполнить сортировку диапазона совпадением.=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)(таблица).
Ошибки при использовании
знаком, а затем или более столбца наименее понятных.«ВПР» значение ИСТИНА устанавливается некорректным. на различные продукты
- на первый взгляд. который пользователь хочет учащихся выводились и критериев) различны, например,
- новая таблица, в в исследуемом диапазоне по первому столбцуЕсли аргумент=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)Чтобы создать именованный диапазон, диапазон ячеек. К с profexcel.ruите, функцияВ этом учебнике по. Жмем на кнопку по умолчанию.В первую очередь укажем с полями «Наименование», Иначе она бы
- получить. их оценки, то у одной - которой конкретному искомому должен быть отсортирован в порядке возрастания.range_lookupТеперь, когда Вы уверены, просто выделите ячейки примеру, следующая формулаВПРВПР«OK»ЛОЖЬ – поиск точного третий необязательный для «Масса», «Стоимость 1 не стала настолькоБаза_данных. Имя той области есть ввести дополнительный текстовый, а у менеджеру быстро сопоставляется
- по возрастанию.Это очень важно, поскольку(интервальный_просмотр) равен что нашли правильное и введите подходящее показывает, что диапазон
- всегда ищет значениея постараюсь изложить. совпадения установленному критерию. заполнения аргумент – единицы товара» и распространённой. информации, которую предстоит столбец во втором другой - числовой.
его сумма продаж.И, наконец, помните о функцияFALSE имя, можно использовать название в полеA2:B15 в первом столбце
Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ
основы максимально простымПосле этого открывается окно,Примечания: 0 (или ЛОЖЬ)
«Общая стоимость заказа»,Помните о главном: функция искать. Понятие не списке. Изменить формат ячейкиРасположена формула ВПР во важности четвертого аргумента.ВПР(ЛОЖЬ), формула ищет эту же формулу,Имянаходится на листе
диапазона, заданного в языком, чтобы сделать в которое нужноЕсли в качестве аргумента иначе ВПР вернет заполненными являются только ищет информацию по настолько обширное, какФункция ВПР отлично справляется можно, если перейти вкладке "Мастер функций"
Используйте значениявозвращает следующее наибольшее точное совпадение, т.е. чтобы найти сумму,, слева от строки с именем
Другие нюансы при работе с функцией ВПР
аргументе процесс обучения для вставить аргументы функции. [интервальный_просмотр] было передано некорректный результат. Данный два первых столбца. вертикали, то есть
предыдущее. Его можно с решением данной в редактирование ячейки и разделе "СсылкиTRUE значение после заданного, точно такое же оплаченную этим клиентом. формул.
Sheet2table_array неискушённых пользователей максимально Жмем на кнопку, значение ЛОЖЬ (точное
аргумент требует от В отдельной таблице – по столбцам. использовать только по задачи. В столбце (F2). Такие проблемы и массивы". Диалоговое(ИСТИНА) или а затем поиск значение, что задано Для этого достаточноТеперь Вы можете записать.(таблица). В просматриваемом понятным. Кроме этого,
Пример организации учебного процесса с ВПР
расположенную справа от совпадение поисковому критерию), функции возвращать точное содержатся поля «Наименование» Её применяют в первым строкам или G под заголовком обычно возникают при окно функции имеетFALSE
останавливается. Если Вы в аргументе изменить третий аргумент вот такую формулу=VLOOKUP(40,Sheet2!A2:B15,2) диапазоне могут быть мы изучим несколько поля ввода данных, а в диапазоне совпадение надетого результата, и «Стоимость 1 разных ситуациях:
столбцам. "Оценки" записывается соответствующая импортировании данных с следующий вид:(ЛОЖЬ) обдуманно, и пренебрежете правильной сортировкой,lookup_value функции для поиска цены=ВПР(40;Sheet2!A2:B15;2)
различные данные, например, примеров с формулами чтобы приступить к
Пример организации поисковой системы с ВПР
ячеек (аргумент таблица) а не ближайшее единицы товара». ТакимКогда надо найти информацию2. Это порядковый номер формула: =ВПР (Е4, других прикладных программ.Аргументы в формулу вносятся Вы избавитесь от дело закончится тем,(искомое_значение). Если вВПР
товараКонечно же, имя листа текст, даты, числа, Excel, которые продемонстрируют выбору аргумента искомого искомое значение отсутствует, по значению. Вот образом, вторая таблица в большой таблице столбца, откуда программа В3:С13, 2, 0). Для избежания подобного в порядке очереди:
многих головных болей.
profexcel.ru
ВПР Excel - что это такое? Узнайте, как работает функция ВПР в Excel
что Вы получите первом столбце диапазонана номер нужногоProduct 1 не обязательно вводить логические значения. Регистр наиболее распространённые варианты значения. функция ВПР вернет почему иногда не представляет собой прайс. либо отыскать все будет черпать информацию. Ее нужно скопировать рода ошибок вИскомое значение - то,В следующих статьях нашего очень странные результаты
Общая информация
t столбца. В нашем: вручную. Просто начните символов не учитывается использования функцииТак как у нас код ошибки #Н/Д. работает функция ВПР Чтобы перенести значения повторяющиеся данные иЛОЖЬ. Указывает на поиск
на всю колонку формулу ВПР есть что должна найти учебника по функции или сообщение обable_array случае это столбец=VLOOKUP("Product 1",Products,2) вводить формулу, а функцией, то естьВПР искомое значение дляЕсли аргумент [интервальный_просмотр] принимает
в Excel у стоимости единицы товара совпадения. точного совпадения. Иногда таблицы. возможность встраивать следующие функция, и вариантамиВПР ошибке(таблица) встречается два C (3-й в=ВПР("Product 1";Products;2) когда дело дойдёт символы верхнего и. ячейки C3, это значение ИСТИНА (или
некоторых пользователей. из прайса вВ преподавательской среде. Учитель указываются другие дополнительныеВ результате выполнения функция функции: ЗНАЧЕН или которого являются значенияв Excel мы#N/A или более значений, диапазоне):
Параметры функции на примере
Большинство имен диапазонов работают до аргумента нижнего регистра считаютсяОбщее описание и синтаксис«Картофель» явно не указан),
Формула для го года:
первую таблицу удобно всегда может по
- параметры этого слова. ВПР выдаст оценки, ТЕКСТ. Выполнение данных ячейки, ее адрес, будем изучать более(#Н/Д). совпадающих с аргументом
- =VLOOKUP("ack*",$A$2:$C$11,3,FALSE) для всей рабочейtable_array profexcel.ru, наша формулаПримеры с функцией ВПР, то и выделяем однако столбец с=ВПР(A14;$A$3:$B$10;2;0)
- использовать функцию ВПР. фамилии своих учеников И программа при
- полученные определенными студентами. алгоритмов автоматически преобразует имя, заданное ей продвинутые примеры, такиеВот теперь можно использоватьlookup_value=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ) книги Excel, поэтому
(таблица), переключитесь на будет искать значениеКак, используя ВПР, выполнить соответствующее значение. Возвращаемся искомым значением содержитИ для го года: Также данную функцию в считаные мгновения этом будет искатьЕще один пример применения
Аргументы ВПР
формат ячеек. оператором. В нашем как выполнение различных одну из следующих(искомое_значение), то выбраноВот ещё несколько примеров нет необходимости указывать нужный лист и40 поиск на другом к окну аргументов неотсортированные данные, функция=ВПР(A14;$D$3:$E$10;2;0) часто используют для найти их посещаемость, все совпадения и функции ВПР -В коде функции присутствуют случае - это
вычислений при помощи формул: будет первое из с символами подстановки: имя листа для выделите мышью требуемыйв ячейках от листе Excel функции.
вернет код ошибкиПолученные значения: сравнения данных двух успеваемость и другую определять ближайшие значения. это организация поисковой непечатные знаки или фамилия и имяВПР=VLOOKUP(69,$A$2:$B$15,2,TRUE) них. Если совпадения~
аргумента диапазон ячеек.A2Поиск в другой рабочейТочно таким же образом #Н/Д. Для полученияС использованием функции СРЗНАЧ таблиц. информацию. В особенностиК сожалению, функция не системы, когда в пробелы. Тогда следует менеджера., извлечение значений изили
Распространённые ошибки
не найдены, функцияНаходим имя, заканчивающеесяtable_arrayФормула, показанная на скриншотедо книге с помощью кликаем по значку корректных результатов необходимо определим искомую разницуПример 1. В таблице полезной она является, может работать более базе данных согласно внимательно проверить формулуТаблица - диапазон строк нескольких столбцов и
=VLOOKUP(69,$A$2:$B$15,2) сообщит об ошибке на «man»:(таблица), даже если ниже, ищет текстA15 ВПР справа от поля выполнить сортировку таблицы доходов: хранятся данные о когда список студентов профессионально. Некоторые данные
заданному критерию следует на наличие ошибок и столбцов, в другие. Я благодарю=ВПР(69;$A$2:$B$15;2;ИСТИНА)
Когда используют функцию ВПР?
#N/A=VLOOKUP("*man",$A$2:$C$11,1,FALSE) формула и диапазон «Product 1» в, потому что AКак использовать именованный диапазон ввода данных, для или в качестве=СРЗНАЧ(EE15)-СРЗНАЧА(DD15) сотрудниках (ФИО и большой, а преподаватель пользователю нужно держать найти соответствующее ему
ввода. котором ищется критерий. Вас за то,или(#Н/Д).Например, следующая формула=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
- поиска находятся на столбце A (это – это первый или таблицу в выбора таблицы, откуда
- аргумента [интервальный_просмотр] указатьПолученный результат: занимаемая должность). Организовать не может запомнить в голове (номер значение. Так, наЗадан приблизительный поиск, тоНомер столбца - его что читаете этот=ВПР(69;$A$2:$B$15;2) сообщит об ошибке~
- разных листах книги. 1-ый столбец диапазона столбец диапазона A2:B15, формулах с ВПР будут подтягиваться значения. значение ЛОЖЬ.Как видно, в некоторых
более компактный вид каждого из них. строки или столбца). рисунке показан список есть четвертый аргумент
Как использовать ВПР в таблице?
порядковое число, в учебник, и надеюсьКак видите, я хочу#N/AНаходим имя, начинающееся Если же они A2:B9) на листе заданного в аргументеИспользование символов подстановки вВыделяем всю область второй
Если форматы данных, хранимых случаях функция ВПР исходной таблицы вВ розничной торговле. Если В противном случае с кличками животных функции ВПР имеет котором располагается сумма встретить Вас снова выяснить, у какого(#Н/Д), если в на «ad» и находятся в разныхPricestable_array формулах с ВПР таблицы, где будет в ячейках первого может вести себя одну строку, первой использовать функцию для он не сможет и их принадлежность значение 1 или продаж, то есть
Рекомендации по использованию функции
на следующей неделе! из животных скорость диапазоне A2:A15 нет заканчивающееся на «son»: книгах, то перед.(таблица):Точное или приближенное совпадение производиться поиск значений, столбца таблицы, в непредсказуемо, а для ячейке которой содержится поиска цены, состава найти нужную ему к определенному виду. ИСТИНА, а таблица результат работы формулы.
Урок подготовлен для Вас ближе всего к значения=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE) именем диапазона нужно=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)=VLOOKUP(40,A2:B15,2) в функции ВПР кроме шапки. Опять которой выполняется поиск
Заключение
расчетов в данном список ФИО сотрудников, или артикула товара, информацию.При помощи ВПР создается не отсортирована поИнтервальный просмотр. Он вмещает командой сайта profexcel.ru694=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ) указать название рабочей=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)=ВПР(40;A2:B15;2)ВПР в Excel –
возвращаемся к окну
profexcel.ru
Примеры функции ВПР в Excel для выборки значений по условию
с помощью функции примере пришлось создавать а во второй то человек сможетЧтобы говорить о том, новая таблица, в восходящему значению. В
значение либо ЛОЖЬ,Источник: profexcel.ruмилям в час.:~ книги, к примеру,Пожалуйста, помните, что приcol_index_num это нужно запомнить! аргументов функции. ВПР, и переданного дополнительную таблицу возвращаемых будет выводится занимаемая быстро ответить на как работает функция которой легко найти этом случае столбец либо ИСТИНА. ПричемПеревел: Антон Андронов И вот какой=VLOOKUP(4,A2:B15,2,FALSE)Находим первое имя вот так: поиске текстового значения(номер_столбца) – номерИтак, что же такоеДля того, чтобы выбранные
Примеры использования функции ВПР в Excel
в качестве аргумента значений. Данная функция им должность. расспросы покупателей. ВПР в Excel, по кличке животного искомых критериев требуется ЛОЖЬ возвращает толькоАвтор: Антон Андронов результат мне вернула=ВПР(4;A2:B15;2;ЛОЖЬ) в списке, состоящее
=VLOOKUP("Product 1",profexcel.ru!Products,2)
Вы обязаны заключить столбца в заданномВПР значения сделать из искомое_значение отличаются (например, удобна для выполненияВид исходной таблицы:Одним словом, в любой
нужно ознакомиться с его вид. Актуальны отсортировать по возрастанию. точное совпадение, ИСТИНАПрикладная программа Excel популярна функцияЕсли аргумент из 5 символов:=ВПР("Product 1";profexcel.ru!Products;2)
его в кавычки диапазоне, из которого? Ну, во-первых, это
относительных абсолютными, а
- искомым значением является простого поиска илиСоздадим компактный вариант таблицы
- среде, когда нужно её аргументами. Первым подобные поисковые системы
- Причем при организации новой - разрешает поиск благодаря своей доступности
ВПР
range_lookup=VLOOKUP("?????",$A$2:$C$11,1,FALSE)Так формула выглядит гораздо («»), как это будет возвращено значение,
функция Excel. ЧтоПоиск значения в диапазоне ячеек по условию
это нам нужно, число, а в выборки данных из критериев с выпадающим найти данные из является искомое значение. при работе с сводной таблицы заданные приблизительного значения. и простоте, так:
(интервальный_просмотр) равен
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ) понятнее, согласны? Кроме обычно делается в находящееся в найденной
она делает? Она чтобы значения не
первом столбце таблицы таблиц. А там, списком. Чтобы создать таблицы, можно использовать Оно задаёт параметры большими списками. Для искомые критерии могутФункция ВПР пример использования как не требуетКак видите, формула возвратила
TRUE
Функция ВПР и сравнение двух таблиц в Excel если не работает
Чтобы функция того, использование именованных формулах Excel. profexcel.ruй левый столбец ищет заданное Вами сдвинулись при последующем содержатся текстовые строки), где не работает выпадающий список перейдите ВПР. поиска, который будет того чтобы вручную
находиться в любом
может иметь следующий: особых знаний и результат(ИСТИНА), формула ищет
ВПР диапазонов – этоДля аргумента в заданном диапазоне значение и возвращает изменении таблицы, просто функция вернет код функция ВПР в в ячейку D2Разобраться в этом нетрудно. искать программа в не пересматривать все
порядке и последовательности при ведении дел навыков. Табличный видАнтилопа приблизительное совпадение. Точнее,с символами подстановки хорошая альтернатива абсолютнымtable_array – это соответствующее значение из выделяем ссылку в ошибки #Н/Д. Excel следует использовать и выберите инструмент Для того чтобы
первом столбце таблицы.
записи, можно быстро
и не обязательно
торгового предприятия в
предоставления информации понятен
(Antelope), скорость которой сначала функция работала правильно, в
ссылкам, поскольку именованный
(таблица) желательно всегда
1 другого столбца. Говоря полеДля отображения сообщений о формулу из функций «ДАННЫЕ»-«Работа с данными»-«Проверка формула ВПР Excel Она не может воспользоваться поиском и вмещаться полным списком таблицах Excel в любому пользователю, а61ВПР качестве четвёртого аргумента диапазон не меняется использовать абсолютные ссылки, второй столбец – техническим языком,«Таблица» том, что какое-либо ИНДЕКС и ПОИСКПОЗ. данных». действительно работала, первоначально работать со вторым получить требуемый результат. (частичная выборка). столбце А записано широкий набор инструментов,миля в час,
Как работает функция ВПР в Excel при выборке из таблицы значений?
ищет точное совпадение, всегда нужно использовать
при копировании формулы
(со знаком $).
- этоВПР, и жмем на значение найти не Для поиска сВ появившемся окне «Проверка нужно сделать таблицу. и последующими, функцияАвтор: Marina BratslavskayОшибка под №5 является наименование продукции, а включающих "Мастер функции", хотя в списке а если такоеFALSE в другие ячейки. В таком случае2ищет значение в функциональную клавишу удалось, можно использовать более сложными критериями вводимых значений» в Также для полноценного попросту не найдётВПР Excel – это довольно распространенной и в колонке В позволяет проводить любые
- есть также не найдено, выбирает(ЛОЖЬ). Если диапазон Значит, Вы можете диапазон поиска будет, третий столбец – первом столбце заданногоF4 «обертки» логических функций условий лучше использовать секции «Тип данных:» использования функции необходимо эту информацию. Внутри функция в соответствующей наглядно изображена на - соответствующая цена. манипуляции и расчетыГепард приблизительное. Приблизительное совпадение
- поиска содержит более быть уверены, что оставаться неизменным при это диапазона и возвращает. После этого к ЕНД (для перехвата связку этих двух выберите опцию «Список».
- минимум два столбца, формулы этот аргумент
- программе, которая отличается рисунке ниже. Для составления предложения с предоставленными данными.(Cheetah), который бежит – это наибольшее одного значения, подходящего диапазон поиска в копировании формулы в3 результат из другого ссылке добавляются знаки
- ошибки #Н/Д) или функций в одной
Затем заполните поле
- максимальное количество таковых указывается в кавычках. красотой и простотой.В данном примере список в столбце СОдной из широко известных со скоростью значение, не превышающее под условия поиска
- формуле всегда останется другие ячейки.и так далее. столбца в той доллара и она ЕСЛИОШИБКА (для перехвата формуле. Такая формула «Источник:» ссылкой на – не ограничено. Исключения составляют наименования Она имеет множество имен согласно нумерации нужно отыскать стоимость
- формул Excel является70 заданного в аргументе с символами подстановки, корректным.Чтобы функция Теперь Вы можете же строке. превращается в абсолютную. любых ошибок). умеет решить те диапазон ячеек =$A$2:$A$10,Затем в пустую ячейку функций.
- различных применений, её отсортирован не по на определенный продукт, вертикальный просмотр. Использованиемиль в час,lookup_value то будет возвращеноЕсли преобразовать диапазон ячеекВПР
profexcel.ru
прочитать всю формулу:
Функция ВПР
Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=profexcel.ruТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Например:
=ВLOOKUP(A2;AC20;2;ИСТИНА)
=ВПР("Иванов";B2:E7;2;ЛОЖЬ)
=ВLOOKUP(A2;'Сведения о клиенте'! A:F;3;ЛОЖЬ)
Имя аргумента | Описание |
---|---|
искомое_значение (обязательный) | Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array. Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B. Искомое_значение может являться значением или ссылкой на ячейку. |
таблица (обязательный) | Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе. Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти. Узнайте, как выбирать диапазоны на листе . |
номер_столбца (обязательный) | Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину. |
интервальный_просмотр (необязательный) | Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
|
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц. |
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.
Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.
В нашем примере в столбце G для получения данных "Ставка выставления счета" из четвертого столбца(col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;'Поверенные'! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. Если вы пытаетесь подготовить книгу, содержащую функции данных, которые используют несколько таблиц, измените источник данных функции на новую таблицу.
Проблема | Возможная причина |
---|---|
Неправильное возвращаемое значение | Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия. |
#Н/Д в ячейке |
Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР. |
#ССЫЛКА! в ячейке | Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!. Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!. |
#ЗНАЧ! в ячейке | Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!. Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР. |
#ИМЯ? в ячейке | Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе. Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?. |
Ошибки #ПЕРЕНОС! в ячейке | Эта конкретная #SPILL! обычно означает, что формула использует неявное пересечение для искомого значения и использует целый столбец в качестве ссылки. Например, =ВКП(A:A;A:C;2;ЛОЖЬ). Эту проблему можно устранить, привязав ссылку на подытовку к оператору @: =В ПРОСМОТР(@A:A,A:C;2;ЛОЖЬ). Кроме того, можно использовать традиционный метод ВЛКП и ссылаться на одну ячейку,а не весь столбец: =ВЛИ ПРОСМОТР( A2;A:C;2;ЛОЖЬ). |
Действие | Примечания |
---|---|
Используйте абсолютные ссылки в аргументе интервальный_просмотр | Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки. |
Не сохраняйте числовые значения или значения дат как текст. | При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение. |
Сортируйте первый столбец | Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы. |
Используйте подстановочные знаки | Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом. Например, =ВЛП("Шрифт?";B2:E7;2;ЛОЖЬ) будет искать все экземпляры Шрифтаны с последней буквой, которая может отличаться. |
Убедитесь, что данные не содержат ошибочных символов. | При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (' или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ. |
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Краткий справочник. Обновление ВРОТ
Краткий справочник: советы по устранению неполадок с ВЛК
Исправление ошибки #VALUE! в функции ВЛОП
Исправление ошибки #Н/Д в функции ВПР
Общие сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Функция ВПР (бесплатное ознакомительное видео)
Функция ВПР в Excel – руководство для начинающих: синтаксис и примеры
Сегодня мы начинаем серию статей, описывающих одну из самых полезных функций Excel – ВПР (VLOOKUP). Эта функция, в то же время, одна из наиболее сложных и наименее понятных.
В этом учебнике по ВПР я постараюсь изложить основы максимально простым языком, чтобы сделать процесс обучения для неискушённых пользователей максимально понятным. Кроме этого, мы изучим несколько примеров с формулами Excel, которые продемонстрируют наиболее распространённые варианты использования функции ВПР.
Функция ВПР в Excel – общее описание и синтаксис
Итак, что же такое ВПР? Ну, во-первых, это функция Excel. Что она делает? Она ищет заданное Вами значение и возвращает соответствующее значение из другого столбца. Говоря техническим языком, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.
В самом привычном применении, функция ВПР ищет в базе данных заданный уникальный идентификатор и извлекает из базы какую-то связанную с ним информацию.
Первая буква в названии функции ВПР (VLOOKUP) означает Вертикальный (Vertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Горизонтальный (Horizontal).
Функция ВПР доступна в версиях Excel , Excel , Excel , Excel , Excel XP и Excel
Синтаксис функции ВПР
Функция ВПР (VLOOKUP) имеет вот такой синтаксис:
Как видите, функция ВПР в Microsoft Excel имеет 4 параметра (или аргумента). Первые три – обязательные, последний – по необходимости.
- lookup_value (искомое_значение) – значение, которое нужно profexcel.ru может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40:
Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
- table_array (таблица) – два или более столбца с profexcel.ruите, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются profexcel.ru, наша формула будет искать значение 40 в ячейках от A2 до A15, потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):
- col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной profexcel.ruй левый столбец в заданном диапазоне – это 1, второй столбец – это 2, третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:
Формула ищет значение 40 в диапазоне A2:A15 и возвращает соответствующее значение из столбца B (поскольку B – это второй столбец в диапазоне A2:B15).
Если значение аргумента col_index_num (номер_столбца) меньше 1, то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).
- range_lookup (интервальный_просмотр) – определяет, что нужно искать:
- точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
- приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.
Этот параметр не обязателен, но очень важен. Далее в этом учебнике по ВПР я покажу Вам несколько примеров, объясняющих как правильно составлять формулы для поиска точного и приблизительного совпадения.
Примеры с функцией ВПР
Я надеюсь, функция ВПР стала для Вас чуть-чуть понятнее. Теперь давайте рассмотрим несколько примеров использования ВПР в формулах с реальными данными.
Как, используя ВПР, выполнить поиск на другом листе Excel
На практике формулы с функцией ВПР редко используются для поиска данных на том же листе. Чаще всего Вы будете искать и извлекать соответствующие значения из другого листа.
Чтобы, используя ВПР, выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2.
Конечно же, имя листа не обязательно вводить вручную. Просто начните вводить формулу, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на нужный лист и выделите мышью требуемый диапазон ячеек.
Формула, показанная на скриншоте ниже, ищет текст Product 1 в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices.
Пожалуйста, помните, что при поиске текстового значения Вы обязаны заключить его в кавычки (), как это обычно делается в формулах Excel.
Для аргумента table_array (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В таком случае диапазон поиска будет оставаться неизменным при копировании формулы в другие ячейки.
Поиск в другой рабочей книге с помощью ВПР
Чтобы функция ВПР работала между двумя рабочими книгами Excel, нужно указать имя книги в квадратных скобках перед названием листа.
Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге profexcel.ru:
Вот простейший способ создать в Excel формулу с ВПР, которая ссылается на другую рабочую книгу:
- Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
- Начните вводить функцию ВПР, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на другую рабочую книгу и выделите в ней нужный диапазон поиска.
На снимке экрана, показанном ниже, видно формулу, в которой для поиска задан диапазон в рабочей книге profexcel.ru на листе Prices.
Функция ВПР будет работать даже, когда Вы закроете рабочую книгу, в которой производится поиск, а в строке формул появится полный путь к файлу рабочей книги, как показано ниже:
Если название рабочей книги или листа содержит пробелы, то его нужно заключить в апострофы:
Как использовать именованный диапазон или таблицу в формулах с ВПР
Если Вы планируете использовать один диапазон поиска в нескольких функциях ВПР, то можете создать именованный диапазон и вводить его имя в формулу в качестве аргумента table_array (таблица).
Чтобы создать именованный диапазон, просто выделите ячейки и введите подходящее название в поле Имя, слева от строки формул.
Теперь Вы можете записать вот такую формулу для поиска цены товара Product 1:
Большинство имен диапазонов работают для всей рабочей книги Excel, поэтому нет необходимости указывать имя листа для аргумента table_array (таблица), даже если формула и диапазон поиска находятся на разных листах книги. Если же они находятся в разных книгах, то перед именем диапазона нужно указать название рабочей книги, к примеру, вот так:
Так формула выглядит гораздо понятнее, согласны? Кроме того, использование именованных диапазонов – это хорошая альтернатива абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Значит, Вы можете быть уверены, что диапазон поиска в формуле всегда останется корректным.
Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).
Готовая формула будет выглядеть примерно вот так:
А может даже так:
При использовании именованных диапазонов, ссылки будут вести к тем же ячейкам, не зависимо от того, куда Вы копируете функцию ВПР в пределах рабочей книги.
Использование символов подстановки в формулах с ВПР
Как и во многих других функциях, в ВПР Вы можете использовать следующие символы подстановки:
- Знак вопроса (?) – заменяет один любой символ.
- Звёздочка (*) – заменяет любую последовательность символов.
Использование символов подстановки в функциях ВПР может пригодиться во многих случаях, например:
- Когда Вы не помните в точности текст, который нужно найти.
- Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
- Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.
Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами
Предположим, что Вы хотите найти определенного клиента в базе данных, показанной ниже. Вы не помните его фамилию, но знаете, что она начинается на ack. Вот такая формула отлично справится с этой задачей:
Теперь, когда Вы уверены, что нашли правильное имя, можно использовать эту же формулу, чтобы найти сумму, оплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР на номер нужного столбца. В нашем случае это столбец C (3-й в диапазоне):
Вот ещё несколько примеров с символами подстановки:
~ Находим имя, заканчивающееся на man:
~ Находим имя, начинающееся на ad и заканчивающееся на son:
~ Находим первое имя в списке, состоящее из 5 символов:
Чтобы функция ВПР с символами подстановки работала правильно, в качестве четвёртого аргумента всегда нужно использовать FALSE (ЛОЖЬ). Если диапазон поиска содержит более одного значения, подходящего под условия поиска с символами подстановки, то будет возвращено первое найденное значение.
Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР
А теперь давайте разберём чуть более сложный пример, как осуществить поиск с помощью функции ВПР по значению в какой-то ячейке. Представьте, что в столбце A находится список лицензионных ключей, а в столбце B список имён, владеющих лицензией. Кроме этого, у Вас есть часть (несколько символов) какого-то лицензионного ключа в ячейке C1, и Вы хотите найти имя владельца.
Это можно сделать, используя вот такую формулу:
Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.
Как видно на рисунке ниже, функция ВПР возвращает значение Jeremy Hill, поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.
Заметьте, что аргумент table_array (таблица) на скриншоте сверху содержит имя таблицы (Table7) вместо указания диапазона ячеек. Так мы делали в предыдущем примере.
Точное или приближенное совпадение в функции ВПР
И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).
Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.
- Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:
- Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).
Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.
Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты.
Пример 1: Поиск точного совпадения при помощи ВПР
Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).
Давайте вновь обратимся к таблице из самого первого примера и выясним, какое животное может передвигаться со скоростью 50 миль в час. Я верю, что вот такая формула не вызовет у Вас затруднений:
Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5. Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, совпадающее с искомым.
Пример 2: Используем ВПР для поиска приблизительного совпадения
Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.
Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д).
Вот теперь можно использовать одну из следующих формул:
или
или
Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69 милям в час. И вот какой результат мне вернула функция ВПР:
Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.
Надеюсь, эти примеры пролили немного света на работу с функцией ВПР в Excel, и Вы больше не смотрите на неё, как на чужака. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.
ВПР в Excel – это нужно запомнить!
- Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
- В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
- Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
- Если 3-й аргумент col_index_num (номер_столбца) меньше 1, функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
- Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
- Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
- И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.
В следующих статьях нашего учебника по функции ВПР в Excel мы будем изучать более продвинутые примеры, такие как выполнение различных вычислений при помощи ВПР, извлечение значений из нескольких столбцов и другие. Я благодарю Вас за то, что читаете этот учебник, и надеюсь встретить Вас снова на следующей неделе!
.