Функция ВПР в Excel для чайников и не только

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

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

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

Итак, чтобы решить задачу в нашем примере нам необходимо сначала заполнить столбец «С» в таблице заказов, т.е найти цену товаров в таблице «Прайс лист», а затем, чтобы узнать стоимость — перемножить цену на количество товаров.

Синтаксис функции ВПР (VLOOKUP) в Excel

[ads]

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

Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

Разберем функцию на нашем примере. Вставим курсов в ячейку C3 и наберем формулу =ВПР(A3;$F$2:$H$22;3;0)

в английской версии =VLOOKUP(A3;$F$2:$H$22;3;0)

В данной формуле с ВПР (англ. VLOOKUP):

A3 – искомое_значение. В нашем случае это «Ведро», т.е нам необходимо найти «ведро» в таблице «Прайс лист»

$F$2:$H$22 – таблица. В нашем примере это таблица «Прайс лист» (F2:H22). В диапазон данной таблицы вставлены знаки $ для его закрепления, чтобы он не сдвигался вниз, когда мы будем протягивать формулу. Знак доллара в Excel превращает относительный диапазон в абсолютный.

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

0 – интервальный просмотр. Может принимать только два значения 0 или 1: 0 – ищет точное совпадение, 1 – приблизительное. В 99% случаях требуется искать точное значение (в нашем случае нам необходимо искать слово «Ведро»). Поэтому практически всегда указывается цифра 0.

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

После этого переходим в ячейку D3 и находит стоимость товаров. Прописываем формулу =C3*B3, т.е перемножаем цену товара на количество.

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

Все делаете правильно, а ВПР не работает? Читайте статью «Почему может не работать функция ВПР«.

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

Спасибо за внимание.

Поделиться:

Функция ВПР в Excel. Поиск и возвращение текста по списку

Categories: Формулы

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

  • Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
  • Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
  • Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
  • [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

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

Итак, напишем функцию для нашей задачи:

=ВПР(E:E;A:B;2;ЛОЖЬ)

E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B —  вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

Комментарии по ВПР

  • Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
  • Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
  • Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.
  • Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ

Скачать пример

Что в итоге:

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

Пишите комментарии, буду рад помочь!

3 кит Excel — это сводные таблицы. Подробнее здесь.

Поделитесь нашей статьей в ваших соцсетях:

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

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

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

Рассмотрим структуру ВПР, какие аргументы она задействует.  Как и любая другая функция в Excel начинается она со знака равенства (=). Далее имя функции и аргументы, заключенные в круглые скобки.

ВПР содержит 4 аргумента.image

Функция ведет поиск искомого значения в крайнем левом столбце и производит возврат значения в той же строке из указанного столбца.image

Вторым — указывается именно та таблица (или диапазон ячеек), в которой следует произвести этот поиск.

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

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ, 1 —  ИСТИНА. отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1приблизительный.

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

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

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

  Как настроить группировку строк и столбцов в Excel

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

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

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.

Для второго аргумента выделяем диапазон таблицы.

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК.

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример.

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней200-300 и т.д.

Искомым значением в данном случае будет количество, Таблицу выбираем диапазон КритерийПартия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

Читайте также:

Задайте вопрос Быстрый доступ

Система Microsoft Office  >  Office 2016
  • Вопрос

  • Столкнулся с отсутствием функции ВПР в Excel 2016. 

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

    21 июля 2016 г. 14:07 Ответить | Цитировать

Ответы

  • Да как бы все есть

    The opinion expressed by me is not an official position of Microsoft

    • Изменено21 июля 2016 г. 18:29
    • Предложено в качестве ответа31 июля 2016 г. 14:33
    • Помечено в качестве ответа8 августа 2016 г. 10:08

    21 июля 2016 г. 18:25 Ответить | Цитировать

Все ответы

  • Да как бы все есть

    The opinion expressed by me is not an official position of Microsoft

    • Изменено21 июля 2016 г. 18:29
    • Предложено в качестве ответа31 июля 2016 г. 14:33
    • Помечено в качестве ответа8 августа 2016 г. 10:08

    21 июля 2016 г. 18:25 Ответить | Цитировать

  • А Excel 2016 у Вас по ключу или по подписке Office 365?

    У меня по ключу (все обновления установлены). Отсутствие ВПР через поиск функции подтверждаю:

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

    8 августа 2016 г. 10:01 Ответить | Цитировать

  • Если вопрос адресован мне то это подписка

    Выбирите категорию как показано выше и покажите скрин

    The opinion expressed by me is not an official position of Microsoft

    8 августа 2016 г. 10:05 Ответить | Цитировать

  • В полном алфавитном перечне присутствует, проблема именно в поиске… 8 августа 2016 г. 10:11 Ответить | Цитировать
  • Обновила офис по подписке, всё тоже самое. Вообще поиск работает довольно странно, большинство функций через него найти не получается. 8 августа 2016 г. 10:19 Ответить | Цитировать
  • Подтверждаю, есть точно такая же проблема, как победить – неясно. 27 сентября 2017 г. 8:34 Ответить | Цитировать
  • Подтверждаю, есть точно такая же проблема, как победить – неясно.

    если Вы знаете как называется функция станьте на поле “Выберите функцию” и введите первые буквы функции, поиск работает по ключевым словам в описании функции о чем написано в подсказке для этого поля (что мс включило в индекс большой вопрос, но работает все ровно так как и заявлено, хоть и кривовато)

    The opinion expressed by me is not an official position of Microsoft

    27 сентября 2017 г. 8:58 Ответить | Цитировать

category Компьютеры / Программное обеспечение

Прикладная программа Excel популярна благодаря своей доступности и простоте, так как не требует особых знаний и навыков. Табличный вид предоставления информации понятен любому пользователю, а широкий набор инструментов, включающих “Мастер функции”, позволяет проводить любые манипуляции и расчеты с предоставленными данными.

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

Как работает ВПР Excel

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

Функция ВПР производит поиск заданного критерия, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не находится, то выдается ошибка #Н/Д (в англоязычном варианте #N/А).

Необходимость использования

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

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

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

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

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

Расположена формула ВПР во вкладке “Мастер функций” и разделе “Ссылки и массивы”. Диалоговое окно функции имеет следующий вид:image

Аргументы в формулу вносятся в порядке очереди:

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

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

Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В – соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.

Наглядный пример организации таблицы
А В С Д
продукт 1 90 продукт 3 60
продукт 2 120 продукт 1 90
продукт 3 60 продукт 4 100
продукт 4 100 продукт 2 120

Формула, записанная в Д, будет выглядеть так: =ВПР (С1- А1:В5- 2- 0), то есть =ВПР (искомое значение- диапазон данных таблицы- порядковый номер столбца- 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.

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

Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1- $А$1:$В$5- 2- 0).

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

Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:

  1. Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
  2. В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню “Правка” – “Найти” вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
  3. Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной – текстовый, а у другой – числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
  4. В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
  5. Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.

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

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

Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.

image

В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. Причем в качестве интервального просмотра использован критерий ИСТИНА (1), который сразу прерывает поиск при обнаружении значения большего, чем искомое, поэтому выдается ошибка.

При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.

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

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

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

image

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

Другой вариант – озаглавить – подразумевает выделение диапазона данных, потом переход в меню “Вставка”- “Имя”- “Присвоить”.

Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР (А1- Лист2!$А$1:$В$5- 2- 0), где Лист2! – является ссылкой на требуемый лист книги, а $А$1:$В$5 – адрес диапазона поиска данных.

Пример организации учебного процесса с ВПР

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

Существуют две таблицы со списками студентов. Одна с их оценками, вторая указывает возраст. Необходимо сопоставить обе таблицы так, чтобы наравне с возрастом учащихся выводились и их оценки, то есть ввести дополнительный столбец во втором списке.

image

Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком “Оценки” записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.

image

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

Пример организации поисковой системы с ВПР

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

image

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

Внимание, только СЕГОДНЯ! KAKrufb.ru В» Компьютеры В» Программное обеспечение В» Функция ВПР. Использование функции ВПР. Excel – ВПР

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий