Функция ВПР

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

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

    ВПР(искомое_значение,таблица,номер_столбца,[интервальный_просмотр])
    Важно! Программа не умеет думать как человек, она четко сопоставляет факты. Если для нас название «Труба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

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

    © 2013-2019 НВ ВЕБ МАРКЕТИНГ. ИП Ульянкин Дмитрий Владимирович ИНН 402806997547