Впр это в excel: инструкция на примере / Skillbox Media

Функция ВПР в Excel — ExcelGuide: Про Excel и не только

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

 Синтаксис у функции следующий:

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

— Искомое значение — это то значение, которое мы будем искать в таблице с данными

— Таблица — диапазон данных, в первом столбце которого мы будем искать искомое значение

— Номер столбца — этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата

— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.

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

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

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

Итак, в качестве первого параметра мы указали, что будем искать фамилию, вторым параметром указали тот диапазон, в котором будем искать. Обратите внимание, что ячейки диапазона зафиксированы. Это сделано для того, что бы потом можно было скопировать формулу в другие ячейки простым протягиванием. Если вы не знаете, как зафиксировать ячейки в формулу, прочитайте вот эту статью на нашем сайте: «Как зафиксировать ячейку в формуле Excel». Далее мы указываем с какого столбца мы возвращаем значение, в случае, показанном на картинке сверху, нам надо получить имя, в таблице это второй столбец, соответственно указываем 2. Последним параметром указываем 0, т.е. соответствие должно быть точным.

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

P.S. Если вы опытный пользователь и знакомы с языком программирования Python, то рекомендую изучить как можно сделать такой же функционал при помощи библиотеки Pandas в нашей статье: «Excel и Python. Аналог ВПР в Pandas.»

ВПР и интервальный просмотр(range_lookup)

Хитрости »

25 Март 2022       Дмитрий       1027 просмотров

  ВПР с интервальным просмотром (160,5 KiB, 90 скачиваний)

Если еще не знаете про функцию

ВПР(VLOOKUP)

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

интервальный_просмотр(range_lookup)

. Для большинства распространенных задач применяется значение

ЛОЖЬ(FALSE)

или 0. Что означает искать точное соответствие заданному значению. Многие считают, что если указать интервальный просмотр как

ИСТИНА(TRUE)

или 1, то ВПР будет искать приблизительное соответствие искомому значению и можно будет найти

«Иванов»

даже если он будет записан как

«Ивонов»

. На самом же деле все не совсем так(но и не совсем не так). Мы этот пример рассмотрим в самом конце, а сначала попробуем разобраться с этим интервальным просмотром: когда применять, как правильно и как это все вообще работает. И после этого будет понятно что произойдет с неверно записанным «Ивановым» 🙂
Для начала возьмем классический пример: у нас есть таблица заказов, в которой для каждого клиента указана сумма заказа:

B столбце «Скидка» необходимо проставить размер скидки для клиента, в зависимости от суммы заказа:

От 0 до 1000$ — скидки нет
От 1000$ до 2000$ — скидка 2%
От 2000$ до 3000$ — скидка 3%
От 3000$ до 4000$ — скидка 5%
От 4000$ до 5000$ — скидка 7%
И т. д.

Мы, конечно, могли бы создать несколько условий ЕСЛИ: если «Стоимость заказа» = 1000 и =2000 и
Я создал её рядом с исходной таблицей для наглядности – реально же она может быть на другом листе и даже в другой книге. Теперь запишем нашу формулу

ВПР(VLOOKUP)

, используя эту таблицу и интервальный просмотр равный 1(или

ИСТИНА(TRUE)

):

=ВПР(C2;$G$2:$H$12;2;1)
=VLOOKUP(C2,$G$2:$H$12,2,1)

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

Т.е. ВПР берет из первой таблицы(

А:Е

) сумму столбца «Стоимость заказа» и ищет эту сумму в первом столбце(

G

) таблицы условий(

G:H

). При этом ищет, определяя в какой именно диапазон сумм входит искомая. Как только находит – возвращает значение из столбца

Н

таблицы условий, т.е. процент.
Но чтобы результат был корректный, необходимо соблюдать важное условие: таблица условий в обязательном порядке должна быть отсортирована в порядке возрастания(от меньшего к большему) по первому столбцу – т.е. по тому столбцу, в котором просматриваем искомое значение(в нашем случае это столбец G — Стоимость).
А теперь попробуем разобраться как это все работает и почему же так важна сортировка. Подобный поиск данных не просто так называется интервальным(хотя в мире программирования он больше известен как двоичный поиск). Дело в том, что ВПР в данном случае не сравнивает с искомой суммой каждое значение таблицы условий, а последовательно делит таблицу условий на две части и смотрит в какую часть попадает искомое значение. Общий алгоритм можно представить так:

1. Искомое значение сначала сравнивается со значением в середине таблицы условий. Если получится так, что значения равны – поиск завершен, нужная строка найдена
2. Если искомое значение больше значения из середины – игнорируется нижняя часть таблицы условий
3. Если искомое значение меньше значения из середины – игнорируется верхняя часть таблицы
4. Если получится так, что осталось только два значения и искомое в диапазоне между ними – то в качестве результата будет принята строка, значение которой меньше искомого

Если значение не было найдено — шаги 1-4 повторяются с оставшейся частью таблицы(верхней или нижней) до тех пор, пока значение не будет определено.

Разберем на конкретном примере из нашей таблицы.
Возьмем значение из ячейки С8 – 1 483,30 и последовательно сделаем все шаги поиска с интервальным просмотром. ВПР сделает следующее – поделит таблицу условий на две примерно равные части:

Далее ВПР возьмет значения на границах разделенных таблиц – 5 000(для верхней части) и 6 000(для нижней части). Т.к. 5 000 уже больше искомого значения 1 483,30 – значит ВПР эту часть таблицы оставит, а нижнюю отбросит из просмотра и больше к ней не вернется.
Далее ВПР поделит оставшуюся верхнюю таблицу еще на две части и сравнит их:

Т.к. 2000 больше искомого значения 1 483,30 – ВПР оставит верхнюю часть таблицы и опять поделит её на две части:

Последнее значение верхней части таблицы(1 000) меньше искомого 1 483,30, а первое значение второй части больше(2 000). Это значит, что искомый интервал найден и ВПР возьмет процент из строки со значением, которое меньше искомого, т.е. строка со значением 1000, в которой у нас 2%.
И так с каждой суммой. Теперь становится понятно, почему этот метод просмотра требует сортировки – ведь если данные будут в хаотичном порядке, то при делении таблицы на две части вполне может получиться так, что значения 1000 и 2000 могут вполне оказаться вместе в нижней части таблицы еще на первом этапе деления(в таблице после 6000) и вовсе не попадут в отбор. В итоге в лучшем случае получим ошибку #Н/Д(#N/A), а в худшем – неверный результат.
Ознакомившись с принципом поиска, может показаться, что такой поиск происходит очень долго. Но на самом деле – это один из самых быстрых алгоритмов поиска значений, т.к. ВПР не просматривает каждое отдельное значение, а смотрит в какой части таблицы он находится и с каждым шагом таблица сокращается в два раза. Это позволяет практический мгновенно находить значения даже в очень больших массивах данных.

Ну и вернемся к примеру, который я упомянул в самом начале — ответим на вопрос, поможет ли ВПР с интервальным просмотром найти фамилию «Иванов» в списке, где такой фамилии нет, но есть «Ивонов» и другие похожие. Ответ не будет очевидным — все будет зависеть как от самих данных, так и от их количества. Если, к примеру, у нас будет такой список:

то формула

=ВПР(«Иванов»;A2:A7;1;1)

вернет нам фамилию Васильев, что очень далеко от ожидаемого значения. А все потому, что уже при первом делении на две части, Ивонов у нас окажется в нижней части, которая будет отброшена, т.к. фамилия Ивенов — будет уже больше чем Иванов.

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

А почему же тогда Ивенов не стал результатом? Потому что у нас пошел второй круг деления таблицы:

Васечкин, Васильев, Ивенов

. Ивенов больше, чем Иванов, а Васильев — меньше. Т.е. мы достигли нашего интервала и берется последнее значение верхней части таблицы — т.е. Васильев.
Что из этого следует учесть? А то, что использовать интервальный просмотр лучше всего на числовых данных — так результат будет более прогнозируемый. Если все же решили работать с текстом, то стоит учитывать описанный выше алгоритм и не надеяться, что ВПР будет искать похожие значения — ВПР будет отбирать по принципу больше-меньше и никак иначе.

Скачать файл, используемый в видеоуроке:

  ВПР с интервальным просмотром (160,5 KiB, 90 скачиваний)

P.S. Кстати, точно такой же принцип заложен и в функцию ГПР(HLOOKUP), а так же и в ПОИСКПОЗ(MATCH). Но в случае с ПОИСКПОЗ(MATCH), последним аргументом можно указать 1 или -1(разница лишь в том, будет ли взято значение меньше искомого или больше).

Так же см.:
Как найти значение в другой таблице или сила ВПР
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

Офис вице-президента по исследованиям — Офис вице-президента по исследованиям

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

Офис вице-президента по новостям исследований

Новая страница управления ИТ и данными на веб-сайте офиса VPR

Офис вице-президента по исследованиям недавно перезапустил страницу управления ИТ и данными на веб-сайте VPR. Этот новый раздел предоставляет исследователям ресурсы и информацию, и теперь он более доступен для исследовательского сообщества.

Еженедельное исследование: четверг, 27 октября 2022 г. Собрание мэрии по подкове сегодня днем, пригласите преподавателей подать заявку на программы Excel и RIFP и назовите рецензентов для внутренних программ.

Проверьте искусственный интеллект: игра меняет или игра окончена? видео

Ранее в этом месяце Офис вице-президента по исследованиям провел уникальное панельное обсуждение «Искусственный интеллект: игра меняет или игра окончена?» Фото и видео с мероприятия уже доступны.

Еженедельное исследование: четверг, 20 октября 2022 г.

В обновлении этой недели мы приглашаем преподавателей подать заявку на участие в программах Excel и RIFP, приглашаем читателей присоединиться к VPR Fridriksson на собрании мэрии, посвященном подкове на следующей неделе, и призываем читателей посетить мероприятия Недели открытого доступа университетских библиотек 2022 года.

Еженедельное исследование: четверг, 13 октября 2022 г. семинар по подаче заявки на финансирование PCORI на следующей неделе, призвать преподавателей сообщить своим студентам о подаче заявки на получение стипендии Magellan Scholar и пригласить читателей присоединиться к VPR Fridriksson на встрече в мэрии, посвященной подкове, в этом месяце.

Посмотреть все новости

ИТ и управление данными
Управление спонсируемыми наградами
Исследования и разработка грантов
Управление бакалавриата
Управление соответствия требованиям исследований

Подпишитесь, чтобы получать нашу электронную рассылку и/или веб-журнал Breakthrough
версию по электронной почте.

  (* обязательное поле)

ООО «Впр Менеджмент» 13F-HR Manager Holdings Сентябрь 2022 г.

ИСТОРИЯ
ИНСТРУМЕНТЫ

СИК: 1894203

Пожалуйста, подождите, пока мы загрузим запрошенный отчет 13F-HR, или нажмите на ссылку ниже:0003

Просмотрите различия, сделанные друг с другом, чтобы оценить финансовую траекторию ООО «Впр Менеджмент» прочитав наши основные моменты, чтобы увидеть, какой текст и таблицы были      удалены    ,    добавлены     и    изменены    Vpr Management Llc.

Продолжать

Оценка того, как управленческая команда Vpr Management Llc получает оплату из своих годовых доверенностей

Пример скриншота фактического заявления о доверенности

Окончательное заявление о доверенности Vpr Management Llc (форма DEF 14A), поданное после годового отчета 10-K за 2022 год, включает:

• Процедуры голосования
• Члены правления
• Исполнительная команда
• Заработная плата, бонусы, привилегии
• Коллеги/конкуренты

Продолжать

Скриншот внутренней стоимости AT&T (2019)

Функция пользователя Last10K. com

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

Продолжить

Снимок экрана Gmail для нового годового отчета 10-K

Функция пользователя Last10K.com

Получить электронное письмо, как только компания представит годовой отчет, квартальный отчет или новые корпоративные новости 8-K

Продолжить

Снимок экрана из годового отчета Wynn 10-K за 2018 г.

Функция пользователя Last10K.com

Читать положительные и отрицательные замечания, сделанные руководством, полностью, без поиска их в 10-K/Q

Продолжить

Снимок экрана из ежеквартального отчета Adobe Inc. за 10 кварталов

Функция пользователя Last10K.com

Удалить столбцы данных и элементы навигации, чтобы увидеть гораздо больше файлов и таблиц в одном представлении

Продолжить

Скриншот из годового отчета Lumber Liquidators 10-K

Функция пользователя Last10K.com

Учитывайте как скрытые возможности, так и ранние признаки потенциальных проблем, не находя их в 10-K/Q

Продолжить

Скриншоты фактических файлов 10-K и 10-Q SEC в форматах PDF, Word и Excel

Функция пользователя Last10K.com

Экспорт годовых и квартальных отчетов в файлы Adobe Acrobat (PDF), Microsoft Word (DOCX), Excel (XLSX) и файлы с разделителями-запятыми (CSV) для автономного просмотра, аннотаций и анализа

Продолжить

Скриншоты фактических файлов 10-K и 10-Q SEC в форматах PDF, Word и Excel

Функция пользователя Last10K. com

Экспорт годовых и квартальных отчетов в файлы Adobe Acrobat (PDF), Microsoft Word (DOCX), Excel (XLSX) и файлы с разделителями-запятыми (CSV) для автономного просмотра, аннотаций и анализа

Продолжить

Скриншот отчета о финансовой стабильности Coco-Cola (2019)

Функция пользователя Last10K.com

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

Продолжить

Доступные финансовые коэффициенты

Функция пользователя Last10K.com

Посмотрите, как 70 Рост, рентабельность и финансовые показатели работают в течение 10 лет

Продолжить

Скриншот взят из Euronet (EEFT)

Функция пользователя Last10K.