Общая формула
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Резюме
Чтобы найти наиболее близкое соответствие в числовых данных, вы можете использовать ИНДЕКС и ПОИСКПОЗ с помощью функций ABS и MIN. В показанном примере формула в F5, скопированная вниз, имеет следующий вид:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
где поездка (B5: B14) и стоимость (C5: C14) - это именованные диапазоны.
В F5, F6 и F7 формула возвращает поездку, ближайшую по стоимости к 500, 1000 и 1500, соответственно.
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
По сути, это формула ИНДЕКС и ПОИСКПОЗ: ПОИСКПОЗ определяет положение ближайшего совпадения, передает позицию в ИНДЕКС, а ИНДЕКС возвращает значение в этой позиции в столбце Поездки. Тяжелая работа выполняется с помощью функции ПОИСКПОЗ, которая тщательно настроена для соответствия "минимальной разнице", например:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Пошагово, значение поиска вычисляется с помощью MIN и ABS следующим образом:
MIN(ABS(cost-E5)
Сначала значение в E5 вычитается из стоимости именованного диапазона (C5: C14). Это операция с массивом, и поскольку в диапазоне 10 значений, результатом является массив с 10 значениями, подобными этому:
(899;199;250;-201;495;1000;450;-101;500;795)
Эти числа представляют собой разницу между каждой стоимостью в C5: C15 и стоимостью в ячейке E5, 700. Некоторые значения отрицательны, поскольку стоимость ниже, чем значение в E5. Чтобы преобразовать отрицательные значения в положительные, мы используем функцию ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
который возвращает:
(899;199;250;201;495;1000;450;101;500;795)
Мы ищем самое близкое совпадение, поэтому используем функцию MIN, чтобы найти наименьшее различие, которое составляет 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Это становится значением поиска внутри MATCH. Поисковый массив создается, как и раньше:
ABS(cost-E5) // generate lookup array
который возвращает тот же массив, который мы видели ранее:
(899;199;250;201;495;1000;450;101;500;795)
Теперь у нас есть то, что нам нужно, чтобы найти позицию ближайшего совпадения (наименьшее различие), и мы можем переписать часть MATCH формулы следующим образом:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Если в качестве значения поиска используется 101, ПОИСКПОЗ возвращает 8, поскольку 101 находится на 8-й позиции в массиве. Наконец, эта позиция передается в ИНДЕКС в качестве аргумента строки с именованным отключением диапазона в качестве массива:
=INDEX(trip,8)
а INDEX возвращает восьмую поездку в диапазоне "Испания". Когда формула копируется в ячейки F6 и F7, она находит самое близкое соответствие с 1000 и 1500, «Франция» и «Таиланд», как показано.
Примечание: если есть ничья, эта формула вернет первое совпадение.
С XLOOKUP
Функция XLOOKUP предоставляет интересный способ решения этой проблемы, поскольку для типа соответствия 1 (точное совпадение или следующее по величине) или -1 (точное совпадение или следующее по величине) не требуется сортировка данных. Это означает, что мы можем написать такую формулу:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Как и выше, мы используем абсолютное значение (cost-E5) для создания массива поиска:
(899;199;250;201;495;1000;450;101;500;795)
Затем мы настраиваем XLOOKUP для поиска нуля с типом соответствия, установленным на 1, для точного совпадения или следующего по величине. Мы предоставляем именованное путешествие по диапазону в качестве возвращаемого массива, поэтому результатом будет, как и прежде, «Испания».
