Что такое впр в excel

Краткий справочник: функция ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше

Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЗПРОБЕЛО, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.

ВПР — это одна из наиболее распространенных и полезных функций Excel, однако при редком использовании ее формулу трудно вспомнить.

Если вам нужен только синтаксис функции ВПР, вот он:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Чтобы скачать справочную карточку с объяснением того, что означают аргументы и как их использовать, щелкните ссылку ниже. Краткий справочник по функции ВПР откроется в виде PDF-файла в Adobe Reader. Вы можете напечатать справочник или сохранить его у себя на компьютере для последующего использования.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Использование функции ВПР

Важно: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу. Эта функция доступна только при наличии подписки на Microsoft 365.. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

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

Использование ВПР

1. В строке формул введите =ВПР().
2. В скобках введите значение подстановки, а затем запятую. Это может быть фактическое значение или пустая ячейка, которая будет содержать значение : (H2,
3. Введите массив таблицы или таблицу подстановки, диапазон данных, которые требуется выполнить поиск, и запятую (H2, B3:F25,
4. Введите номер индекса столбца. Это столбец, в котором вы думаете ответы, и он должен быть справа от ваших значений поиска: (H2,B3:F25;3,
5. Введите значение подстановки диапазона : TRUE или FALSE. TRUE находит частичные совпадения, FALSE — точные совпадения. Готовая формула выглядит примерно так: =VLOOKUP(H2;B3:F25;3;FALSE)

Функция ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Совет: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

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

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

=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).

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

Технические подробности

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

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

• =ВПР(A2;A10:C20;2;ИСТИНА)
• =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)
• =ВПР(A2;’Сведения о клиенте’!A:F;3;ЛОЖЬ)

Имя аргумента

искомое_значение (обязательный)

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

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

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

таблица (обязательный)

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

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

номер_столбца (обязательный)

Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение.

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

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

• Вариант Приблизительное совпадение — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВПР(90;A1:B100;2;ЛОЖЬ).
• Вариант Точное совпадение — 0/ЛОЖЬ осуществляет поиск точного значения в первом столбце. Например, =ВПР(«Иванов»;A1:B100;2;ЛОЖЬ).

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

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

1. Значение, которое вам нужно найти, то есть искомое значение.
2. Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

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

=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Объединение данных из нескольких таблиц на одном листе с помощью функции ВПР

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

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

1. Скопируйте таблицу с общими полями на новый лист и присвойте имя.
2. Щелкните Данные >Работа с данными >Отношения, чтобы открыть диалоговое окно «Управление отношениями».

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

Распространенные неполадки

Возможная причина

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендации

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

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

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

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

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

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

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

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

Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться.

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

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

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как пользоваться функцией ВПР в Excel

Excel многие не любят за сложность. Но если знать хитрости и приемы, например, формулу ВПР или Vlookup, то программа станет вашим надежным другом.

• Зачем нужна эта функция
• Как и что считать
• Примеры расчета
• Нюансы

Зачем нужна эта функция

Представим себе, что у нас есть две таблицы. Это могут быть прайс-листы или ведомости с оценками студентов. Суть в том, что данные таблиц иногда нужно объединить, сравнить, перемножить, – короче, выполнить нехитрое математическое действие с большим количеством данных или наименований. Для этого и нужна ВПР или по-английски VLookup. Расшифровывается ВПР как « вертикальный просмотр » .

Самая мелкая русская монета выпущена в 1700 году. Она была медной и равнялась 1/3 копейки. Спустя год выпуск был прекращен из-за невыгодности.

Другой факт

Как и что считать

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

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

Что такое облачные технологии и как они работают

Примеры расчета

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

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

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

• Далее в первой таблице нужно добавить столбик «Стоимость». Дальше применяем функцию вертикального просмотра. Выделяем первую ячейку в столбце «стоимость». Нажимаем fx в строке над ячейками или вызываем мастер функций при помощи горячих клавиш Shift+F3. Путь: Ссылки и массивы – ВПР – Ок.

• Открывшееся окно – это аргументы функции. «Искомое значение» – диапазон первого столбца первой таблицы. Вводим значения крайних ячеек через знак «:» и без пробелов.
• Следующий аргумент называется «таблица». Здесь будут значения цены из листа 2. Чтобы они появились в окне, где мы вводим аргументы, нужно просто перейти на лист 2 и выделить нужные ячейки курсором. Чтобы таблица при расчете не поползла вниз, используют значок доллара, $. Выделите аргумент «таблица» и нажмите F4 или проставьте перед каждым символом $ вручную.

Значок доллара напоминает о накопленных и припрятанных деньгах? Самое время открыть вклад в Совкомбанке.

Заставьте свои сбережения работать и приносить вам пассивный доход! В Совкомбанке есть линейка вкладов с гибкими условиями — вы сможете подобрать подходящий вариант. Высокая ставка до 15% убережет деньги от инфляции и поможет быстрее накопить на крупные покупки. Подайте заявку онлайн!

• В аргумент «номер столбца» вносим тот столбец, где находятся данные, которые нужно из второй таблицы внести в первую. В нашем случае – 2.
• В поле «искомый аргумент» пишем «ЛОЖЬ».
• Растягиваем функцию, выделив нужные ячейки.

Нюансы

В версиях MS Word 2003, 2007, 2010 и 2013 ВПР может выдавать ошибку. Я тоже столкнулась с этим при подсчете бананов для Ирины. Вот как это выглядело.

Пути решения могут быть следующими:

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

Ошибка в вычислении поправима, но если сломан рабочий ноутбук, порой помочь сможет только дорогостоящий ремонт. Вылечите компьютер прямо сейчас с кредитом от Совкомбанка, не откладывая на потом.

Зачем откладывать деньги долгие месяцы, если можно получить желаемое прямо сейчас? Возьмите кредит в Совкомбанке, оформите услугу «Гарантия минимальной ставки» и получите шанс вернуть проценты по истечении срока кредитования. Для этого расплачивайтесь Халвой каждый месяц и не допускайте просрочек по кредиту. Оставить заявку вы можете в два клика.

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

В моем случае максимально сокращенная готовая таблица выглядит так:

Далее нужно посчитать стоимость товаров, воспользовавшись простой формулой умножения.

Теперь вы знаете один из секретов Excel и сможете применять его для расчетов.

Статьи про технологии у вас в почте

Подпишитесь на подборку статей на тему технологий. Все самое интересное из рубрики Технологии будем присылать вам на почту.