Впр excel расшифровка аббревиатуры

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

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

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то искомое_значение должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

Первый столбец в диапазоне ячеек должен содержать Искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.

Номер столбца (начиная с 1 для самого левого столбца массива « инфо_таблица»), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР ("Смит"; a1: B100; 2; ложь).

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

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

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

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

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

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

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

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Неправильное возвращаемое значение

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

Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Это конкретная ошибка #SPILL! Обычно это означает, что формула полагается на неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, = ВПР ( а:а, а:к, 2; ложь). Проблему можно устранить, заменив ссылку для подстановки с помощью оператора @ следующим образом: = ВПР ( @ а:а, а:к, 2, ложь). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку, а не на весь столбец: = ВПР ( a2; а:к; 2; ложь).

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (

) перед искомым символом.

Например, функция = ВПР ("Ивано?"; B2: E7; 2; ложь) будет искать все вхождения Иванов с последней буквой, которая может измениться.

Убедитесь, что данные не содержат ошибочных символов.

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

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Что такое ВПР в Excel?

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

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

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

Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

Как пользоваться ВПР в Excel?

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

К счастью, особых сложностей с доступом к VLOOKUP не возникнет, а несколько приведённых ниже примеров помогут полностью освоиться с применением этой полезнейшей функции Excel.

Пример №1

Имеется сводная таблица с наименованиями товаров, датами и объёмами поставок и сроками годности. Требуется, не прибегая к опции поиска, вывести в любую свободную ячейку информацию о конкретном продукте.

Решение:

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

• В любом удобном месте (для облегчения работы — лучше на новой странице) создать таблицу с искомыми параметрами. Названия могут совпадать с исходными или отличаться от них; главное — чтобы было прямое соответствие значений, иначе выведенная информацию окажется неверной или бесполезной.

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

• Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

• Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

• В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».

• Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

• Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».

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

• Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

• В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

• …Или сам столбец, выделив его аналогичным образом.

• В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

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

• В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

• …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

• Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

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

• Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…

• …После чего вставить её в нужную строку…

• …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

• Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.

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

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

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

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

• Но главное — другое: теперь, единожды настроив выдачу в таблице Excel, можно вписывать в верхнюю строчку любые требуемые наименования продуктов — искомые значения появятся в строках автоматически в уже установленном формате.

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

Пример №2

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

Решение:

• Первый шаг, как и в предыдущем случае, — открыть обе таблицы. В идеале их следует разместить на одном листе — так будет проще проверить правильность объединения по одному или нескольким выбранным позициям.

• Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

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

• В первой строке диалогового окна следует указать наименование позиции из первой таблицы — той, в которую добавляются данные.

• Во второй — вставить выделением всю вторую таблицу, включая названия столбцов и строк.

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

• Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

• Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.

• Соответствия сохранятся даже при пересортировке строк в первой таблице.

• А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

Вопросы от новичков

Выше было подробно рассказано, как работать с основными функциями ВПР. Их должно хватить для самых простых операций; инструкции, приведённые в следующих подзаголовках, помогут разобраться в тонкостях настройки VPR.

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

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

• Открыть обе таблицы, а при необходимости — перенести их на один лист.

• Добавить к одной из них новый столбец, название которого отражает произошедшие изменения.

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

• Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

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

Выпадающие списки — крайне удобная функция Excel, позволяющая не вводить каждый раз наименование искомой позиции, а выбирать его из перечня. Самый простой выпадающий список можно сделать следующим образом:

• Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

• Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

• Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

• Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

• В строке «Источник» указать требуемый диапазон наименований, после чего нажать «ОК».

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

Важно: при изменении параметров в исходной таблице соответствующие коррективы будут внесены Excel и в «результирующую».

Подводим итоги

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Сегодня мы рассмотрим:

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

Впр excel — расшифровка и пояснение

Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP. По названию уже можно понять, что функция считывает информацию в столбцах по вертикали, начиная слева направо, и является идентификатором этой информации.
ВПР в эксель — это один из самых быстрых и простых способов найти в таблице интересующие данные. Например, артикул определенного товара в списке. Или его цену. Простыми словами ВПР — это самая обычная команда, которую можно задать с помощью составленной определенным образом формулы — и получить искомый ответ.

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

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

Что еще можно сделать при помощи ВПР в эксель:

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

ВПР можно использовать для работы в любой версии excel . Также эта формула подходит для некоторых версий гугл доков.

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

Вот самый простой пошаговый пример с картинками, как с использованием ВПР в excel найти нужную информацию:

1. Открыть таблицу эксель.
2. В любой свободной ячейке справа — на картинке-примере ячейка «Е», — указать формулу.
3. Формула **ВПР **начинается со знака «=».
4. Далее нужно задать функцию — это и есть ВПР (соответствует VLOOKUP).
5. Затем открываем круглые скобки, вводим аргументы и закрываем круглые скобки.

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

Третий элемент — номер столбца, в котором следует искать нужный ответ. У нас это цена предмета, находится в третьем столбце. Значит, задать нужно сначала название элемента, указанного в первом столбце, затем диапазон поиска, и номер столбца, в котором будет находиться искомая информация.

Важно! Функция ВПР в excel всегда ищет, начиная с левого столбца. То есть, сначала ВПР отсортирует элементы по названию. Потом в заданном диапазоне будет искать номер нужного столбца и данные в нем.

Есть еще четвертый аргумент. Он определяет, следует искать точное совпадение или все приблизительные. Если мы ищем точное совпадение, то четвертым аргументом указываем False (ложь). Если же достаточно приблизительных совпадений, то ввести следует True (истина).
Вот и все. На этом формула для ВПР в эксель составлена. Осталось нажать клавишу «энтер», и получить нужную информацию.

А теперь оцените все преимущества этой функции. Если формула уже составлена и задана, вы можете отыскать любую информацию в любом столбце, просто меняя аргументы. Но не забывайте, если добавились столбцы с категориями товаров или другими данными, изменять соответственно диапазон поиска. Если же поиск нужно продолжить на следующем листе, то между функцией и диапазоном столбцов просто вводится «Лист 1» или любой другой.
Надеемся, даже чайники поняли смысл и особенности применения ВПР в excel. А если у вас есть свои интересные применения ВПР, поделитесь с нами в комментариях.