Информация в учебном портале постоянно обновляется!

Функция ВПР

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

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

 

ВПР(искомое_значение,таблица,номер_столбца,[интервальный_просмотр])
Важно! Программа не умеет думать как человек, она четко сопоставляет факты. Если для нас название «Труба1» и «Труба 1» это одно и то же, то для Excel это разные вещи, т.к. в первом случае нет пробела перед 1, а во втором случае есть.

Теперь разжуем что к чему:

  • ВПР – название функции
  • Искомое значение – по этому значению мы будем подтягивать необходимые данные. Искомое значение, как правило, бывает код товара, который идентичный в любой базе данных. Если наименование номенклатуры может различаться, то код товара всегда одинаков, поэтому лучше всего использовать его. Для выделения диапазона данных не нужно мучатся, и выделять точное количество строк. Выделите весь столбец, нажав на номер или букву столбца.
  • Таблица – это диапазон от столбца Искомого значения до подтягиваемых данных со страницы подтягиваемых данных, т.е. с другой страницы или книги. Так же выделяем столбцы. Важно! Искомое значение всегда должно быть справа от подтягиваемых данных.
  • Номер столбца – порядковый номер столбца от искомого значения до подтягиваемых данных. Когда Вы выделяете диапазон, обратите внимание, Excel подсказывает Вам какой номер последнего выделенного столбца рядом с курсором мыши.
  • Интервальный просмотр, что это такое знать вовсе не обязательно, просто ставьте всегда 0

Теперь рассмотрим эту функцию на примере:

Открываем книгу Excel

Давайте сделаем произвольную таблицу с тремя столбцами код товара, номенклатура, сумма период 1

При создании таблицы можно воспользоваться протягиванием. Данные вносим любые…

Функция ВПР

Теперь копируем нашу таблицу на другую страницу и меняем столбцы номенклатура (порядок наименований) и сумма период 2. Вот так, например:

Функция ВПР

Теперь возвращаемся на Лист1 и добавляем столбец Сумма период 2, прописываем формулу в столбце сумма период 2. Ставим курсор на первый товар и пишем =ВПР(, теперь выделяем мышкой столбец «код товара» это есть искомое значение и ставим точку с запятой(;) формула приобретает следующий вид =ВПР(А:А;

Дальше переходим на страницу или книгу с нужными для подтягивания данными и выделяем мышкой диапазон от искомого значения(код товара) до подтягиваемых данных (сумма период 2). Смотрим подсказку – номер столбца (ну или считаем самостоятельно) в нашем конкретном случае это третий столбец. Ставим точку с запятой (;) и пишем 3. Получаем следующий вид формулы:

=ВПР(А:А;Лист2А:С;3;

Проставляем 0 и закрываем скобку =ВПР(А:А;Лист2!А:С;3;0). Протягиваем по всем наименованиям и получаем результат :) Для быстрого протягивания можно воспользоваться комбинациями клавиш:

Ставим курсор на первый столбец, нажимаем Ctrl+стрелочка вниз, оказываемся в низу таблицы. Теперь Ctrl+стрелочка вправо, отпускаем Ctrl и еще один раз вправо. Мы должны оказаться в столбце, где прописана наша формула внизу таблицы. Теперь нажимаем Shift+ Ctrl+стрелочка вверх, таким образом, выделяем диапазон до написанной формулы и нажимаем Ctrl+D. Готово.

Функция ВПР

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

Специальная втсавка, вставить значение

БОНУС - КНОПКА ВСТАВКИ ЗНАЧЕНИЯ.

Для удобства и ускорения работы, кнопку специальной вставки как значение можно вывести на панель Excel. Как это работает? Выделяем полностью столбец или диапазон мышкой нажимаем комбинацию клавиш Ctrl+C (комбинация копирования), далее нажимаем кнопку специальной вставки, которую мы сейчас выведем в панель Excel. И все ГОТОВО!

Итак, выводим кнопку:

Идем в Файл/Параметры Excel/Панель быстрого доступа/

Выбираем нужную кнопку и добавляем в панел быстрого доступа. Нажимаем Ок и готово

Параметры Excel, Функция ВПР

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

СПЕЦИАЛЬНО ДЛЯ ВАШЕГО УДОБСТВА!

Скачать данный файл Функция ВПР

 

Понравилась статья? Отблагодари автора ;)

 

Предыдущая глава Формулы Excel

Следующая глава Функция ЕСЛИ

Комментарии   

0 #8 Ульянкин Дмитрий 26.04.2017 03:54
Цитирую manicure:
After going over a number of the blog posts on your blog, I truly appreciate
your technique of writing a blog. I added it to my bookmark site list
and will be checking back in the near future.
Take a look at my web site as well and let me know what you think.

Thank you!
Do you have an English-language site?
Цитировать
0 #7 manicure 25.04.2017 22:17
After going over a number of the blog posts on your blog, I truly appreciate
your technique of writing a blog. I added it to my bookmark site list
and will be checking back in the near future.
Take a look at my web site as well and let me know what you think.
Цитировать
0 #6 Ульянкин Дмитрий 19.04.2016 05:06
Вы можете выслать свой файл, я посмотрю в чем конкретно проблема. Знаете как со мной связаться?
Цитировать
0 #5 Ульянкин Дмитрий 19.04.2016 05:03
Цитирую Александр:
в некоторых ячейках выдал НД.

Что означает #Н/Д?
Это значит нет данных. Такое может произойти в следующих случаях.
1. Если в одной таблице А:А в текстовом формате, а другой таблице в числовом.
2. Если в столбце А:А первой таблицы есть данные, в таблице 2 этих данных нет.
3. Если в столбце А:А одной из таблиц разное количество знаков.
Excel это программа и для него 00048 и 48 это абсолютно разные вещи. Так же 48 и пробел 48 или 48 пробел, все три показателя разные...
Поэтому я рекомендую подтягивать данные по коду товара который всегда одинаков...
Цитировать
0 #4 Ульянкин Дмитрий 19.04.2016 04:50
Цитирую Александр:
Насколько это корректно?

Здравствуйте, Александр. Если Вы протягиваете 3-столбец по столбцу А:А из Диапазона А:С это никак не повлияет. Фиксация данных не является ошибкой, наоборот в некоторых случаях это делать необходимо.
Если Вы выделяете диапазон A:A и Лист2!A:C; и протягиваете формулу в низ, подтягивая 3-й столбец, то не важно нажимаете Вы F4 или нет. Если Вы собираетесь протянуть формулу в право и подтянуть, например, два столбца, тогда Вам необходимо увеличить и зафиксировать диапазон на Лист2 Лист2!$A$:$D$ и зафиксировать $A$:$A$.
Тогда протянув формулу ВПР($A$:$A$, Лист2!$A$:$D$;3;0)в право, просто меняете номер подтягиваемого столбца: ВПР($A$:$A$, Лист2!$A$:$D$;4;0) И подтягиваете два столбца.
Цитировать
+1 #3 Александр 18.04.2016 18:28
Все делал в соответствии с уроком, но на последнем этапе в некоторых ячейках выдал НД. Я стал разбираться и нашел такое решение, я вбивая формулу получил следующее: =ВПР(А:А, Лист2!$A$:$C$;3;0) то есть я заблокировал значения таблицы на 2 листе при помощи F4, чтобы при перетягивании формулы значения не сдвигались. Насколько это корректно?
Цитировать
+3 #2 Larisa 05.10.2015 18:29
Спасибо, Ваш способ увеличивает скорость написания формулы. Я ранее пользовалась стандартной формой. Теперь пишу формулу сразу... Спасибо!
Цитировать
+3 #1 Veronchik 28.09.2015 11:13
Спасибо! Вы просто супер!!!!
Цитировать

Добавить комментарий


Защитный код
Обновить

КАК ПЛАНИРОВАТЬ ПРОМО АКЦИИ ЗА 5 МИН.

планирование промо акции

ЭКОНОМЬ СВОЕ ВРЕМЯ

БУДЬ В КУРСЕ!

Получайте новые уроки по аналитике продаж в Excel БЕСПЛАТНО!!!

email рассылки Конфиденциальность гарантирована
email рассылки

Яндекс.Метрика