Приблизительный поиск с неточным совпадением значений в Excel

Чтобы поисковые функции Excel: ВПР, ГПР и ПОИСКПОЗ выполняли точный поиск с точным совпадением искомого и проверяемого значения ячеек или возвращали ошибку, в последнем третьем их аргументе должно быть указано – ЛОЖЬ или 0. При этом независимо отсортирован ли просматриваемый список значений или нет.

Формула приблизительного поиска неточных совпадений в Excel

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

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

Формула использует три функции ВПР для считывания 3-х значений с таблицы. В последнем аргументе каждой функции находится логическое значение ИСТИНА. Это значит, что необходимо найти приблизительное значение, а необязательно точное совпадение.

Чтобы любая поисковая функция выборки в Excel: ВПР, ГПР либо ПОИСКПОЗ со значением ИСТИНА в третьем аргументе возвращала правильный результат вычисления, данные в просматриваемом столбце (в данном примере это диапазон ячеек B2:B9) должны быть отсортированы по возрастанию. Функция ВПР поочередно проверяет все значения и закончит поиск, если следующее значение будет больше чем искомое. После чего поиск прекращается. Вот почему нужно сортировать исходный диапазон по возрастанию. Таким способом найдено наибольшее значение, которое одновременно с тем меньше чем искомое.

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

Внимание! Если данные в просматриваемом функцией столбце не отсортированы по возрастанию, поисковая функция при выборке вернет не ошибку, а только лишь ошибочный результат (что еще хуже ошибки)! Функции, предназначенные для приблизительного поиска соответствий, используют в своих алгоритмах бинарный метод. Согласно этому алгоритму поиск начинается от середины столбца, а в процессе происходит проверка находится ли искомое значение в верхней или нижней части. Когда определена более подходящая часть столбца, она снова делится на половину и снова проверяется от своей середины. Данный процесс повторяется пока не будет найден результат.

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

В выше приведенном примере функция ВПР закончит поиск на второй строке просматриваемого столбца так как число 1023 является наибольшим числом, которое меньше от искомого числа 2003,89. Условно формулу можно разделить на 3, которые последовательно выполняют следующие операции:

1. Первая функция ВПР возвращает базовую налоговую ставку с третьего столбца таблицы, то есть число 69,80.
2. Следующая функция ВПР ищет тоже самое приблизительное значение для числа 2003,89, но уже по первому столбцу «Зарплата от». После чего найденное приближенное значение вычитаемое от искомого.
3. Третья функция ВПР возвращает процентную ставку с четвертого столбца таблицы. Полученная ставка умножается на чистую зарплату netto – после всех вычетов, а результат прибавляется к базовой ставке.

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

=69,80+(2003,89-1023,00)*15,0%

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



Пример формулы для приблизительного поиска ИНДЕКС и ПОИСКПОЗ в Excel

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

Ниже на рисунке приведена та же таблица с налоговыми ставками, но отсортирована по убыванию. Новая формула в ячейке … использует формулу функций ИНДЕКС и ПОИСКПОЗ возвращает правильный результат:

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

В тоже время новая формула в ячейке D17 возвращает правильный итоговый результат вычислений.

В отличии от других поисковых функций последним аргументом функции ПОИСКПОЗ может быть и отрицательное число, а точнее одно из трех вариантов: -1, 0, 1:

1. Отрицательное значение -1 используется в случаях работы с данными отсортированными по убыванию. Функция возвращает из просматриваемого столбца наименьшее значение, которое является большим по отношению к искомому. Нельзя использовать отрицательную единицу с минусом (-1) в третьем аргументе для поисковых функций выборки данных ВПР и ГПР. Там нет соответственного режима работы функции.
2. Значение 0 используется для обработки неотсортированных списков данных, с целью поиска точного совпадения значений с искомым. Поведение функции ПОИСКПОЗ с нулевым значением в третьем аргументе (0) – соответствует поведению функций ВПР и ГПР с тратим аргументом равному ЛОЖЬ или 0.
3. Значение 1 применяется к спискам данных отсортированных по возрастанию. В таком случае функция возвращает из просматриваемого столбца наибольшее значение, которое меньше от искомого. Положительное число 1 в третьем аргументе функции ПОИСКПОЗ работает аналогично как ИСТИНА или 1 для функций ВПР и ГПР в этом же аргументе.

Так как функция ПОИСКПОЗ с последним аргументом равным отрицательному число -1 ищет значение больше чем искомое к возвращаемому результату следует добавить число +1, чтобы получить правильный номер строки для функции ИНДЕКС.