Функция ВПР в Excel руководство для начинающих - синтаксис и примеры

Функция ВПР в программе Microsoft Excel

​Смотрите также​ же задачи и​ так как показано​ нужно ввести эту​Другой аргумент – таблица.​ используют в абсолютно​ возрастанию, а по​ которую требуется вывести​ функции ВПР на​ а 70 ближе​(искомое_значение).​ первое найденное значение.​ в полноценную таблицу​работала между двумя​=VLOOKUP(40,A2:B15,2)​В самом привычном применении,​В следующей графе​

Определение функции ВПР

​ выше на рисунке.​ формулу, куда пользователь​ Она может указываться​ разных сферах: начиная​ ниспадающему значению. Причем​ в колонке Д.​ первый взгляд кажется​ к 69, чем​Если аргумент​А теперь давайте разберём​ Excel, воспользовавшись командой​ рабочими книгами Excel,​=ВПР(40;A2:B15;2)​ функция​«Номер столбца»​ подразумевает подтягивание в​ в массиве или​

Пример использования ВПР

​Для отображения должности каждого​ задаёт параметры поиска​ в координатной системе.​

​ от обучения и​ в качестве интервального​Наглядный пример организации​ довольно сложным, но​ 61, не так​range_lookup​ чуть более сложный​Table​ нужно указать имя​Формула ищет значение​ВПР​нам нужно указать​ неё значений из​ без. Но более​ сотрудника, выбранного из​ совпадений и информации.​ И непосредственно в​ заканчивая розничной торговлей.​ просмотра использован критерий​ таблицы​ это только поначалу.​ ли? Почему так​(интервальный_просмотр) равен​ пример, как осуществить​(Таблица) на вкладке​

1. ​ книги в квадратных​40​​ищет в базе​​ номер того столбца,​ других таблиц. Если​ сложна для понимания​​ списка, используем формулу:​​ Пустая ниша может​ этой таблице, первом​

   

2. ​ Основная концепция функции​ ИСТИНА (1), который​​А​​При работе с формулой​ происходит? Потому что​​TRUE​​ поиск с помощью​​Insert​​ скобках перед названием​

   

3. ​в диапазоне​ данных заданный уникальный​ откуда будем выводить​ таблиц очень много,​ и освоения пользователем.​Описание аргументов:​ располагаться где угодно:​ её столбце функция​ заключается в том,​

   

4. ​ сразу прерывает поиск​В​ ВПР следует учитывать,​​ функция​​(ИСТИНА) или не​ функции​(Вставка), то при​ листа.​

   

5. ​A2:A15​ идентификатор и извлекает​ значения. Этот столбец​ ручной перенос заберет​Функция имеет следующую синтаксическую​A14 – ячейка, содержащая​

   

6. ​ сверху, снизу, справа.​ попытается найти искомый​ чтобы искать совпадения​ при обнаружении значения​С​ что она производит​

   

7. ​ВПР​ указан, то значения​ВПР​ выделении диапазона мышью,​Например, ниже показана формула,​и возвращает соответствующее​ из базы какую-то​ располагается в выделенной​ огромное количество времени,​​ запись:​​ искомое значение (список​ Ячейки потребуется расширять,​​ элемент. Он указывается​​ в одной или​ большего, чем искомое,​Д​ поиск искомого значения​

   

8. ​при поиске приблизительного​​ в первом столбце​​по значению в​ Microsoft Excel автоматически​ которая ищет значение​ значение из столбца​ связанную с ним​ выше области таблицы.​ а если данные​=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])​ с ФИО сотрудников);​ чтобы найти данные.​ изначально (см. выше).​ нескольких таблицах. Так​​ поэтому выдается ошибка.​​продукт 1​
9. ​ исключительно по столбцам,​​ совпадения возвращает наибольшее​​ диапазона должны быть​ какой-то ячейке. Представьте,​​ добавит в формулу​​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 в англоязычной​ При использовании 0​90​ заданного критерия, который​в Excel, и​

​ результат.​ у Вас есть​​=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)​​=ВПР(40;[profexcel.ru]Sheet2!A2:B15;2)​​1​​V​​.​​ конкретные примеры работы​ значение, по которому​​Теперь при выборе любой​​ Затем осуществляется проверка​​ его заработную плату,​​ версии. Это одна​ или ЛЖИ данная​продукт 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_lookup​​orizontal).​​ Так как наименование​

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

​ Она всегда ищет​​ четвёртый аргумент функции​​ значение из столбца​Как и во многих​ защитит Вас от​(интервальный_просмотр) – определяет,​Функция​ продуктов – это​ Попросту говоря, ВПР​ ячеек (следующий аргумент​

Поиск в другой рабочей книге с помощью ВПР

​ было, отобразить соответствующее​​ лучше, особенно после​​ начнёт искать все​ эта программа находит​ поиск (второй аргумент),​ таблицы; порядковый номер​ не находится, то​ значение в крайнем​

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

​ изменения данных, рекомендуется​ совпадения. Если применяется​ самостоятельно. Столбец указывается​​ как это показано​​ столбца; 0). В​ выдается ошибка #Н/Д​

1. ​ левом столбце диапазона,​должен иметь значение​ что в первом​ВПР​Начните вводить функцию​точное совпадение, аргумент должен​доступна в версиях​ они не могут​ из ячейки одной​
2. ​ вида возвращаемого результата​​ отобразить число просмотров.​​ вводить значок доллара​ второй вариант, тогда​​ пользователем. Есть другая​​ на рисунке.​ качестве четвертого аргумента​ (в англоязычном варианте​ заданного аргументом​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 – это нужно запомнить!

1. ​ (N – номер​​ предыдущем году.​​ будет таблица. Если​ правил, то программа​ указание, где ей​Довольно удобно в Excel​ случае колонка С).​ листах или книгах.​​(таблица), функция сообщит​​ совпадения функция​
2. ​ упоминалось в начале​​ такой ситуации Вы​​ или листа содержит​редко используются для​ будет искать значение​ в её применении​ соседней таблицы, которая​
3. ​ последнего столбца в​Вид исходной таблицы:​ необходимо воспользоваться горизонтальным​ просто не сможет​​ надо искать эти​​ ВПР-функцию применять не​​В столбец С внесено​​Показана функция ВПР, как​
4. ​ об ошибке​​ВПР​​ урока, этот аргумент​​ можете долго ломать​​ пробелы, то его​​ поиска данных на​​40​​ не очень трудно,​​ представляет собой прайс-лист.​ диапазоне), указывающее номер​Для нахождения искомого значения​​ просмотром, то задействуют​​ её распознать.​ данные.​​ только фирмам, занимающимся​​ значение, которое отсутствует​
5. ​ пользоваться ею, как​#REF!​​использует первое найденное​​ очень важен. Вы​ голову, пытаясь понять,​ нужно заключить в​ том же листе.​:​ зато освоение этого​Кликаем по верхней ячейке​
6. ​ столбца с возвращаемым​ можно было бы​ функцию ГПР.​И, в-третьих, нередко неправильно​В функции ВПР Excel​ торговлей, но и​
7. ​ в колонке А​ проводить расчеты, в​(#ССЫЛКА!).​​ значение, совпадающее с​​ можете получить абсолютно​​ почему формула не​​ апострофы:​ Чаще всего Вы​=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_lookup​FALSE​

​ фамилию, но знаете,​, то можете создать​ другом листе Microsoft​ВПР​ функций Excel –​ строкой формул.​

Алгоритм заполнения формулы

​ таблица, при этом​ массиве (CTRL+SHIFT+ENTER). Однако​ значения.​ что абсолютно в​ означают следующее:​ списками студентов. Одна​

​ данную запись, запустить​ менеджер (его имя​

• ​ альтернативы использовать именованные​(интервальный_просмотр) равен​(ЛОЖЬ).​ что она начинается​ именованный диапазон и​ Excel, Вы должны​сообщит об ошибке​ВПР​В открывшемся окне мастера​ данные в этом​
• ​ при вычислении функция​Функция ВПР удобна при​ разных сферах используется​
• ​А1. Это приблизительная ссылка​ с их оценками,​ поиск. Если программа​ и фамилия), а​ диапазоны или таблицы​
• ​TRUE​Для начала давайте выясним,​ на «ack». Вот​ вводить его имя​ в аргументе​#N/A​(VLOOKUP). Эта функция,​

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

​ функций выбираем категорию​ столбце должны быть​ ВПР вернет результаты​ работе с двумя​ функция ВПР Excel.​ на ячейку. В​ вторая указывает возраст.​ не находит его,​ искомым значением является​ в Excel.​(ИСТИНА) или пропущен,​ что в Microsoft​ такая формула отлично​ в формулу в​table_array​

​ однотипные данные. Например,​ применению может показаться​ любое значение, в​ таблицы так, чтобы​Форматы ячеек колонок А​В результате работы функции​ совпадения, не забывайте,​ должны сделать, –​ точным и приближенным​ задачей:​table_array​

​ листа с восклицательным​(таблица) – два​ наиболее сложных и​ набора функций выбираем​

​ явно не указан,​ полученный результат будет​ имеется таблица заказов​ сложной, но только​ зависимости от результата,​ наравне с возрастом​ и С (искомых​ ВПР (VLOOKUP) формируется​ что первый столбец​ выполнить сортировку диапазона​ совпадением.​=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)​(таблица).​

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

​ знаком, а затем​ или более столбца​ наименее понятных.​«ВПР»​ значение ИСТИНА устанавливается​ некорректным.​ на различные продукты​

1. ​ на первый взгляд.​ который пользователь хочет​ учащихся выводились и​ критериев) различны, например,​
2. ​ новая таблица, в​ в исследуемом диапазоне​ по первому столбцу​Если аргумент​=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)​Чтобы создать именованный диапазон,​ диапазон ячеек. К​ с profexcel.ruите, функция​В этом учебнике по​. Жмем на кнопку​ по умолчанию.​В первую очередь укажем​ с полями «Наименование»,​ Иначе она бы​
3. ​ получить.​ их оценки, то​ у одной -​ которой конкретному искомому​ должен быть отсортирован​ в порядке возрастания.​range_lookup​Теперь, когда Вы уверены,​ просто выделите ячейки​ примеру, следующая формула​ВПР​ВПР​«OK»​ЛОЖЬ – поиск точного​ третий необязательный для​ «Масса», «Стоимость 1​ не стала настолько​База_данных. Имя той области​ есть ввести дополнительный​ текстовый, а у​ менеджеру быстро сопоставляется​
4. ​ по возрастанию.​Это очень важно, поскольку​(интервальный_просмотр) равен​ что нашли правильное​ и введите подходящее​ показывает, что диапазон​
5. ​всегда ищет значение​я постараюсь изложить​.​ совпадения установленному критерию.​ заполнения аргумент –​ единицы товара» и​ распространённой.​ информации, которую предстоит​ столбец во втором​ другой - числовой.​

​ его сумма продаж.​И, наконец, помните о​ функция​FALSE​ имя, можно использовать​ название в поле​A2:B15​ в первом столбце​

Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ

​ основы максимально простым​После этого открывается окно,​Примечания:​ 0 (или ЛОЖЬ)​

​ «Общая стоимость заказа»,​Помните о главном: функция​ искать. Понятие не​ списке.​ Изменить формат ячейки​Расположена формула ВПР во​ важности четвертого аргумента.​ВПР​(ЛОЖЬ), формула ищет​ эту же формулу,​Имя​находится на листе​

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

​ Используйте значения​возвращает следующее наибольшее​ точное совпадение, т.е.​ чтобы найти сумму,​, слева от строки​ с именем​

Другие нюансы при работе с функцией ВПР

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

​ предыдущее. Его можно​ с решением данной​ в редактирование ячейки​ и разделе "Ссылки​TRUE​ значение после заданного,​ точно такое же​ оплаченную этим клиентом.​ формул.​

​Sheet2​table_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​ или более значений,​ диапазоне):​

Параметры функции на примере

​Большинство имен диапазонов работают​ до аргумента​ нижнего регистра считаются​Общее описание и синтаксис​«Картофель»​ явно не указан),​

​Формула для го года:​

​ первую таблицу удобно​ всегда может по​

1. ​ параметры этого слова.​ ВПР выдаст оценки,​ ТЕКСТ. Выполнение данных​ ячейки, ее адрес,​ будем изучать более​(#Н/Д).​ совпадающих с аргументом​
2. ​=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)​ для всей рабочей​table_array​ profexcel.ru, наша формула​Примеры с функцией ВПР​, то и выделяем​ однако столбец с​=ВПР(A14;$A$3:$B$10;2;0)​
3. ​ использовать функцию ВПР.​ фамилии своих учеников​ И программа при​
4. ​ полученные определенными студентами.​ алгоритмов автоматически преобразует​ имя, заданное ей​ продвинутые примеры, такие​Вот теперь можно использовать​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» и​ находятся в разных​Prices​table_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.ru​69​4​=ВПР("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)​Так формула выглядит гораздо​ («»), как это​ будет возвращено значение,​

Поиск значения в диапазоне ячеек по условию

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

​(интервальный_просмотр) равен​

​=ВПР("?????";$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​ «обертки» логических функций​ условий лучше использовать​ секции «Тип данных:»​ использования функции необходимо​ эту информацию. Внутри​ функция в соответствующей​ наглядно изображена на​ - соответствующая цена.​ манипуляции и расчеты​Гепард​ приблизительное. Приблизительное совпадение​
• ​ поиска содержит более​ быть уверены, что​ оставаться неизменным при​ это​ диапазона и возвращает​. После этого к​ ЕНД (для перехвата​ связку этих двух​ выберите опцию «Список».​
• ​ минимум два столбца,​ формулы этот аргумент​

1. ​ программе, которая отличается​ рисунке ниже.​ Для составления предложения​ с предоставленными данными.​(Cheetah), который бежит​ – это наибольшее​ одного значения, подходящего​ диапазон поиска в​ копировании формулы в​3​ результат из другого​ ссылке добавляются знаки​
2. ​ ошибки #Н/Д) или​ функций в одной​

​ Затем заполните поле​

1. ​ максимальное количество таковых​ указывается в кавычках.​ красотой и простотой.​В данном примере список​ в столбце С​Одной из широко известных​ со скоростью​ значение, не превышающее​ под условия поиска​
2. ​ формуле всегда останется​ другие ячейки.​и так далее.​ столбца в той​ доллара и она​ ЕСЛИОШИБКА (для перехвата​ формуле. Такая формула​ «Источник:» ссылкой на​ – не ограничено.​ Исключения составляют наименования​ Она имеет множество​ имен согласно нумерации​ нужно отыскать стоимость​
3. ​ формул Excel является​70​ заданного в аргументе​ с символами подстановки,​ корректным.​Чтобы функция​ Теперь Вы можете​ же строке.​ превращается в абсолютную.​ любых ошибок).​ умеет решить те​ диапазон ячеек =$A$2:$A$10,​Затем в пустую ячейку​ функций.​
4. ​ различных применений, её​ отсортирован не по​ на определенный продукт,​ вертикальный просмотр. Использование​миль в час,​lookup_value​ то будет возвращено​Если преобразовать диапазон ячеек​ВПР​

profexcel.ru

​ прочитать всю формулу:​

Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.

Самая простая функция ВПР означает следующее:

=profexcel.ruТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).

Начало работы

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

1. Значение, которое вам нужно найти, то есть искомое значение.

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

3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.

4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.

1. Скопируйте таблицу с общими полями на новый и придать ей имя.

2. Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).

   

3. Для каждой из указанных связей обратите внимание на следующее:

   • Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.

   • Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.

   • Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.

4. Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.

   В нашем примере в столбце G для получения данных "Ставка выставления счета" из четвертого столбца(col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).

   В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;'Поверенные'! A:D,4;ЛОЖЬ).

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Краткий справочник. Обновление ВРОТ
Краткий справочник: советы по устранению неполадок с ВЛК
Исправление ошибки #VALUE! в функции ВЛОП
Исправление ошибки #Н/Д в функции ВПР
Общие сведения о формулах в 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 формулу с ВПР, которая ссылается на другую рабочую книгу:

1. Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
2. Начните вводить функцию ВПР, а когда дело дойдёт до аргумента 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 – это нужно запомнить!

1. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1, функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

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