Lost your password?


Хитрости »
Основные понятия (26)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (20)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (4)

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


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

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


Если еще не знаете про функцию ВПР(VLOOKUP) и что она делает – лучше начать со статьи Как найти значение в другой таблице или сила ВПР. Вкратце – эта функция ищет указанное значение в первом столбце другой таблицы и при нахождении в качестве результата записывает значение из заданного столбца этой таблицы.
И в этой функции есть очень интересный последний аргумент: интервальный_просмотр(range_lookup). Для большинства распространенных задач применяется значение ЛОЖЬ(FALSE) или 0. Что означает искать точное соответствие заданному значению. Многие считают, что если указать интервальный просмотр как ИСТИНА(TRUE) или 1, то ВПР будет искать приблизительное соответствие искомому значению и можно будет найти «Иванов» даже если он будет записан как «Ивонов». На самом же деле все не совсем так(но и не совсем не так). Мы этот пример рассмотрим в самом конце, а сначала попробуем разобраться с этим интервальным просмотром: когда применять, как правильно и как это все вообще работает. И после этого будет понятно что произойдет с неверно записанным "Ивановым" :)
Для начала возьмем классический пример: у нас есть таблица заказов, в которой для каждого клиента указана сумма заказа:
ВПР интервальный просмотр - Исходные данные
B столбце «Скидка» необходимо проставить размер скидки для клиента, в зависимости от суммы заказа:
От 0 до 1000$ - скидки нет
От 1000$ до 2000$ - скидка 2%
От 2000$ до 3000$ - скидка 3%
От 3000$ до 4000$ - скидка 5%
От 4000$ до 5000$ - скидка 7%
И т.д.

Мы, конечно, могли бы создать несколько условий ЕСЛИ: если «Стоимость заказа» < 1000, то 0; если «Стоимость заказа» >= 1000 и < 2000, то 2%; если «Стоимость заказа» >=2000 и < 3000, то 3% и т.д. Многие так и делают. А что если у нас таких условий 10, а то и больше? А если при этом условия еще могут меняться? Каждый раз вдумываться в формулу и подправлять при таком наборе условий будет сложно, да и читабельность формулы тоже оставляет желать лучшего. Вот здесь нам и пригодится интервальный просмотр. Чтобы его использовать создадим предварительно таблицу условий: ВПР интервальный просмотр - Таблица условий
Я создал её рядом с исходной таблицей для наглядности – реально же она может быть на другом листе и даже в другой книге. Теперь запишем нашу формулу ВПР(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. Если получится так, что осталось только два значения и искомое в диапазоне между ними – то в качестве результата будет принята строка, значение которой меньше искомого
  5. Если значение не было найдено - шаги 1-4 повторяются с оставшейся частью таблицы(верхней или нижней) до тех пор, пока значение не будет определено.

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


Ну и вернемся к примеру, который я упомянул в самом начале - ответим на вопрос, поможет ли ВПР с интервальным просмотром найти фамилию "Иванов" в списке, где такой фамилии нет, но есть "Ивонов" и другие похожие. Ответ не будет очевидным - все будет зависеть как от самих данных, так и от их количества. Если, к примеру, у нас будет такой список:
ВПР интервальный просмотр - Поиск ФИО
то формула =ВПР("Иванов";A2:A7;1;1) вернет нам фамилию Васильев, что очень далеко от ожидаемого значения. А все потому, что уже при первом делении на две части, Ивонов у нас окажется в нижней части, которая будет отброшена, т.к. фамилия Ивенов - будет уже больше чем Иванов.
Почему Ивенов больше? Потому что отличие всего в одной букве: е вместо а. А е в алфавитном порядке(если быть точнее - в бинарной сетке символов, но в данном случае это совпадает с алфавитным) находится позже а, а значит она больше, чем а.
А почему же тогда Ивенов не стал результатом? Потому что у нас пошел второй круг деления таблицы: Васечкин, Васильев, Ивенов. Ивенов больше, чем Иванов, а Васильев - меньше. Т.е. мы достигли нашего интервала и берется последнее значение верхней части таблицы - т.е. Васильев.
Что из этого следует учесть? А то, что использовать интервальный просмотр лучше всего на числовых данных - так результат будет более прогнозируемый. Если все же решили работать с текстом, то стоит учитывать описанный выше алгоритм и не надеяться, что ВПР будет искать похожие значения - ВПР будет отбирать по принципу больше-меньше и никак иначе.
Скачать файл, используемый в видеоуроке:

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

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

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


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

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

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

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2022 Excel для всех   Войти