Excel vpr: инструкция на примере / Skillbox Media

Функция ВПР в Excel — TutorExcel.Ru

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

Приветствую всех, дорогие читатели блога Tutorexcel.ru!

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

Вот и мы сегодня поговорим про работу этой замечательной функции в Excel, которая позволяет быстро сопоставлять данные между несколькими таблицами, и на самом деле используется примерно в 100% всех файлов, наверное за исключением пустых книг 🙂

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

Функция ВПР в Excel. Синтаксис

Изучение любой функции начинаем с синтаксиса, функция ВПР (VLOOKUP в английской версии):

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

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

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

Познакомимся с каждым из аргументов по отдельности:

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

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

Со схемой познакомились, самое время перейти к практике.

Формула ВПР в Excel. Пример 1

Давайте рассмотрим пример, пусть у нас имеется таблица с прайс-листом товаров в магазине (артикул, наименование, цена в рублях, объем в литрах и вес в килограммах):

К нам в магазин приходит заказ и наша задача посчитать общую сумму заказа в деньгах:

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

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

Определимся с аргументами, в качестве искомого значения — артикул (G2), таблицы — исходная таблица с данными (A1:E11), номера столбца — третий (3), интервального просмотра — ЛОЖЬ (0, так как ищем точное соответствие), и записываем полученную формулу:

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

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

Ошибки при работе с функцией ВПР в Excel

Не закрепление диапазона в таблице

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

В нашем примере если мы забудем зафиксировать диапазон таблицы A1:E11, то при протягивании формулы он сначала превратится в A2:E12, затем в A3:E13 и т.д.

В итоге для одного из товаров мы получим ошибку, так как нужного артикула в смещенной таблице уже нет:

Поиск не по первому столбцу

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

В нашем случае, к примеру, если мы хотим искать по наименованию (которые находятся в столбце B), то в качестве таблицы нужно выбирать диапазон B1:E11 (не A1:E11), так как именно по столбцу B будет делаться поиск и именно он является первым столбцом в таблице B1:E11.

Пропуск интервального просмотра

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

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

В нашем случае пропуск параметра приводит к тому, что ВПР находит совершенно не то, что нужно, как раз из-за приближенного поиска:

Смена номера столбца

Аргумент номер столбца для функции очень часто прописывается как число в явном виде (например, 3, 4 или 7). Проблема может возникнуть тогда, когда в исходной таблице поменяется порядок столбцов. Например, мы решим добавить в таблицу новый столбец или наоборот, удалить лишний столбец, и то, что раньше было, к примеру, третьим столбцом может стать четвертым или вторым.

Если в наш пример добавить в середину таблицы новый столбец, то исходный третий столбец с ценой станет четвертым, а уже новый третий столбец станет пустым, поэтому формула ВПР вместо цены вернет пустые значения:

Идем дальше.

Функция ВПР в Excel. Пример 2

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

Немного модифицируем таблицу из предыдущего примера и рассмотрим 2 варианта таблицы (старый и новый прайс-листы), так как достаточно часто встречается задача сравнить одинаковые по структуре данные, которые могли быть видоизменены:

Так же как видим в правой таблице для усложнения перемешаны строчки с товарами (теперь они уже идут не по порядку), чтобы задача решалась не простым вычитанием, а все-таки с помощью более интересных инструментов 🙂

Давайте подтянем справа от новой цены старую, для этого прописываем функцию ВПР, в качестве аргумента искомого значения указываем название товара (D3), таблицы — левую часть исходной таблицы (A2:B12), номера столбца — второй (2), интервального просмотра — ЛОЖЬ (0) и не забываем закреплять диапазоны в случае необходимости, чтобы ссылки не сбились:

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

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

Скачать файл с примером.

Спасибо за внимание!
Если у вас остались вопросы, то обязательно делитесь ими в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

Формулы,Функции

ВПР и числа-как-текст

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

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

Рассмотрим классический пример — подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

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

Давайте рассмотрим различные сценарии, с которыми мы здесь можем столкнуться и решения для каждого из них.

Примечание: Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда — нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа — по правому.

Вариант 1. Числа-как-текст в искомых значениях

Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.

Исправить ситуацию можно, если заставить Excel принудительно конвертировать все исходные артикулы с помощью выполнения над ними любой математической операции, типа:

• умножения или деления на 1
• прибавления или вычитания 0
• двойного знака минус перед артикулом (равносильно двойному умножению на -1)

Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

Вариант 2. Числа-как-текст в таблице, где ищем

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

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

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

Вариант 3. Пропадание начальных нулей

Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:

Вариант 4.

Числа-как-текст вперемешку с числами в обеих таблицах

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

Звучит страшно, но решается легко — нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция  прокачает обе версии ВПР — «текстовую» и «числовую» — и выдаст ту, которая не приводит к ошибке #Н/Д:

Вот и всё — и никаких больше ошибок :)

Ссылки по теме

• Как выполнить левый ВПР
• Многоразовый ВПР, извлекающий сразу все значения
• Функция ПРОСМОТРХ как преемник ВПР

Исследовательский университет штата Нью-Мексико

Перейти к основному содержанию

Будь смелым. Формируйте будущее.

Внутри исследований НМГУ

Внутри исследований НМГУ ▸

Проверьте это!

Дайджест RCSI

Дайджест RCSI ▸

Проверьте это!

Высшая школа

Высшая школа ▸

Проверьте это!

Расходы на исследования в размере 110,6 млн долларов

Расходы на исследования в размере 110,6 млн долларов ▸

Проверьте это!

60+ исследовательских центров

60+ исследовательских центров ▸

Проверьте это!

5 университетских музеев

5 университетских музеев ▸

Проверьте это!

Государственный университет Нью-Мексико — это Консорциум земельных грантов, космических грантов и учреждение, обслуживающее латиноамериканцев, основанное в 1888 году и стремящееся стать великим исследовательским университетом. Мы стремимся преуспеть в успеваемости и социальной мобильности наших студентов, достичь наивысшего исследовательского статуса Карнеги (R1) и поддерживать нашу классификацию участия сообщества Карнеги. Миссия офиса вице-президента по исследованиям — способствовать успеху университета в исследованиях, стипендиях и творческой деятельности посредством образцового обслуживания преподавателей, сотрудников и студенческого корпуса. Наше видение заключается в продвижении культуры передового опыта в области исследований, научных исследований и творческой деятельности.

ограниченных заявок | Офис вице-президента по исследованиям

Что такое «ограниченное представление?»

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

Просмотреть список соревнований с ограниченным количеством заявок  

Имейте в виду, что мы прилагаем все усилия, чтобы выявлять и публиковать ограниченные конкурсы, доступные для Университета Айовы. Однако, если вам станет известно о возможности финансирования с ограниченными требованиями к подаче заявок, которая не указана в списке, отправьте электронное письмо по адресу [email protected], указав название программы, срок выполнения и URL-адрес. Также свяжитесь с нами, если вы заинтересованы в возможности, и внутренний крайний срок истек, но крайний срок спонсора является текущим.

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

Как работает процесс номинации организаций?

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

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

Внутренние конкурсы номинаций, независимо от того, проводятся ли они отдельными колледжами или через OVPR, рекламируются в Бюллетене грантов, а также через Funding Focus, еженедельный электронный дайджест ограниченных возможностей подачи заявок, доступных сообществу Университета Айовы. Коллегиальные заместители деканов по исследованиям также распространяют новости о каждом конкурсе в своих колледжах. Нажмите здесь, чтобы подписаться на дайджест Funding Focus.

Календарь избранных повторяющихся возможностей

Обратите внимание, что количество месяцев указано приблизительно и все критерии приемлемости могут быть изменены.