Функция ВПР в Экселе – пошаговая инструкция

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

О нас

Университет Алексея Полянского предлагает индивидуальное обучение по MS Office (Excel, Word, PowerPoint) и Google Таблицам. Работаем с 2013 года, выпустили более 5000 учеников.

Команда профессионалов поможет разобраться, объяснит доступным языком, наглядно покажет и закрепит теорию на практике. Мы постоянно дорабатываем курсы и следим за тенденциями.

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

Excel

Как создать функцию ВПР в Excel

Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

  1. Выделить ячейку и вписать функцию.

  2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

аргументы функции ВПР

Синтаксис функции ВПР выглядит так:

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

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы ВПР в Эксель

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Функция ВПР, искомое значение

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

Функция ВПР, таблица

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат. 

  1. Формула сканирует таблицу по вертикали.

  2. Находит в самом левом столбце совпадение с искомым значением.

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

  4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Функция ВПР, принцип работы

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Как вызвать функцию ВПР. Функция ВПР в Excel

В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.

Функция ВПР в MS Excel. Описание и примеры использования.

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

Функция ВПР в MS Excel. Описание и примеры использования.

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

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия

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

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

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

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Программа курса:

Модуль 1 Основы работы с Excel

Описание

создание простых таблиц, автоматическое заполнение, простые вычисления, фильтрация и сортировка данных, настройка печати документа с MS Excel

Вы научитесь

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

Модуль 2 Сводные таблицы

Описание

создание сводных таблиц из базы данных, анализ и вычисления в сводных таблицах, создание отчетов, работа с таблицами Excel

Вы научитесь

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

Модуль 3 Функции для работы с данными

Описание

функции для поиска и подстановки данных (ВПР, ИНДЕКС+ПОИСКПОЗ), функции для работы с динамичными диапазонами данных (СМЕЩ, ДВССЫЛ), выпадающие списки

Вы научитесь

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

Модуль 4 Функции для работы с датами и текстом

Описание

функции для работы с датой и временем, функции для работы с текстом

Вы научитесь

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

Модуль 5 Функции с ЕСЛИ

Описание

логические функции (ЕСЛИ, И, ИЛИ, ЕСЛИМН или многоуровневое ЕСЛИ), ЕСЛИОШИБКА; математические функции с условиями (СУММЕСЛИ(МН), СЧЁТЕСЛИ(МН), СРЗНАЧЕСЛИ(МН), АГРЕГАТ)

Вы научитесь

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

Модуль 6 Визуализация данных

Описание

создание диаграмм, сводных диаграмм, настройка дашборда

Вы научитесь

  • быстро анализировать информацию с помощью диаграмм
  • создавать красивые и информативные диаграммы
  • вставлять кнопки и переключатели для удобного пользования
  • создавать дашборды для быстрого анализа

Модуль 7 Формулы массива

Описание

работа с массивами диапазонов, вычисления с массивами

Вы научитесь

  • проводить сложные вычисления в одной ячейке
  • в пару кликов вычислять несколько массивов данных

Специальные инструменты для ВПР в Excel.

Несомненно, ВПР – одна из самых мощных и полезных функций Excel, но она также одна из самых запутанных. Чтобы сделать работу с ней проще, можно использовать надстройку Ultimate Suite for Excel с инструментом «Мастер ВПР», позволяющим значительно сэкономить время на поиск нужных данных.

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

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

ВПР и приблизительный интервальный просмотр

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

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

Задача для Excel, ВПР

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Окно для формулы ВПР в Excel

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Сортировка в Excel с помощью ВПР

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Некорректная работа функции ВПР

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...