ВПР с интервальным просмотром (160,5 КиБ, 375 скачиваний)
Если еще не знаете про функцию
И в этой функции есть очень интересный последний аргумент:
Для начала возьмем классический пример: у нас есть таблица заказов, в которой для каждого клиента указана сумма заказа:
B столбце «Скидка» необходимо проставить размер скидки для клиента, в зависимости от суммы заказа:
От 1000$ до 2000$ - скидка 2%
От 2000$ до 3000$ - скидка 3%
От 3000$ до 4000$ - скидка 5%
От 4000$ до 5000$ - скидка 7%
И т.д.
Мы, конечно, могли бы создать несколько условий ЕСЛИ: если «Стоимость заказа» < 1000, то 0; если «Стоимость заказа» >= 1000 и < 2000, то 2%; если «Стоимость заказа» >=2000 и < 3000, то 3% и т.д. Многие так и делают. А что если у нас таких условий 10, а то и больше? А если при этом условия еще могут меняться? Каждый раз вдумываться в формулу и подправлять при таком наборе условий будет сложно, да и читабельность формулы тоже оставляет желать лучшего. Вот здесь нам и пригодится интервальный просмотр. Чтобы его использовать создадим предварительно таблицу условий:
Я создал её рядом с исходной таблицей для наглядности – реально же она может быть на другом листе и даже в другой книге. Теперь запишем нашу формулу
В итоге мы получим ровно тот результат, которого добивались – размер скидки проставлен в зависимости от суммы заказа:
Т.е. ВПР берет из первой таблицы(
Но чтобы результат был корректный, необходимо соблюдать важное условие: таблица условий в обязательном порядке должна быть отсортирована в порядке возрастания(от меньшего к большему) по первому столбцу – т.е. по тому столбцу, в котором просматриваем искомое значение(в нашем случае это столбец G - Стоимость).
А теперь попробуем разобраться как это все работает и почему же так важна сортировка. Подобный поиск данных не просто так называется интервальным(хотя в мире программирования он больше известен как двоичный поиск). Дело в том, что ВПР в данном случае не сравнивает с искомой суммой каждое значение таблицы условий, а последовательно делит таблицу условий на две части и смотрит в какую часть попадает искомое значение. Общий алгоритм можно представить так:
- Искомое значение сначала сравнивается со значением в середине таблицы условий. Если получится так, что значения равны – поиск завершен, нужная строка найдена
- Если искомое значение больше значения из середины – игнорируется нижняя часть таблицы условий
- Если искомое значение меньше значения из середины – игнорируется верхняя часть таблицы
- Если получится так, что осталось только два значения и искомое в диапазоне между ними – то в качестве результата будет принята строка, значение которой меньше искомого
Если значение не было найдено - шаги 1-4 повторяются с оставшейся частью таблицы(верхней или нижней) до тех пор, пока значение не будет определено.
Разберем на конкретном примере из нашей таблицы.
Возьмем значение из ячейки
Далее ВПР возьмет значения на границах разделенных таблиц – 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) и вовсе не попадут в отбор. В итоге в лучшем случае получим ошибку
Ознакомившись с принципом поиска, может показаться, что такой поиск происходит очень долго. Но на самом деле – это один из самых быстрых алгоритмов поиска значений, т.к. ВПР не просматривает каждое отдельное значение, а смотрит в какой части таблицы он находится и с каждым шагом таблица сокращается в два раза. Это позволяет практический мгновенно находить значения даже в очень больших массивах данных.
Ну и вернемся к примеру, который я упомянул в самом начале - ответим на вопрос, поможет ли ВПР с интервальным просмотром найти фамилию "Иванов" в списке, где такой фамилии нет, но есть "Ивонов" и другие похожие. Ответ не будет очевидным - все будет зависеть как от самих данных, так и от их количества. Если, к примеру, у нас будет такой список:
то формула
А почему же тогда Ивенов не стал результатом? Потому что у нас пошел второй круг деления таблицы:
Что из этого следует учесть? А то, что использовать интервальный просмотр лучше всего на числовых данных - так результат будет более прогнозируемый. Если все же решили работать с текстом, то стоит учитывать описанный выше алгоритм и не надеяться, что ВПР будет искать похожие значения - ВПР будет отбирать по принципу больше-меньше и никак иначе.
ВПР с интервальным просмотром (160,5 КиБ, 375 скачиваний)
P.S. Кстати, точно такой же принцип заложен и в функцию ГПР(HLOOKUP), а так же и в ПОИСКПОЗ(MATCH). Но в случае с ПОИСКПОЗ(MATCH), последним аргументом можно указать 1 или -1(разница лишь в том, будет ли взято значение меньше искомого или больше).
Так же см.:
Как найти значение в другой таблице или сила ВПР
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
