Загальна формула
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Резюме
Щоб знайти найближчу відповідність у числових даних, ви можете використовувати INDEX та MATCH, за допомогою функцій ABS та MIN. У наведеному прикладі формула в F5, скопійована вниз, має вигляд:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
де поїздка (B5: B14) і вартість (C5: C14) називаються діапазонами.
У F5, F6 та F7 формула повертає подорож, найближчу за вартістю, до 500, 1000 та 1500 відповідно.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
Пояснення
По суті, це формула INDEX і MATCH: MATCH визначає позицію найближчого збігу, подає позицію до INDEX, а INDEX повертає значення в цій позиції у стовпці Trip. Наполеглива робота виконується за допомогою функції MATCH, яка ретельно налаштована на відповідність "мінімальній різниці", наприклад:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Беручи покрокові дії, значення підрахунку обчислюється з МІН та АБС так:
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 підстановки MATCH повертає 8, оскільки 101 знаходиться на 8-й позиції масиву. Нарешті, ця позиція подається в INDEX як аргумент рядка, а як масив називається відключення діапазону :
=INDEX(trip,8)
і INDEX повертає 8-ту поїздку в діапазоні "Іспанія". Коли формула копіюється до комірок 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, для точного відповідності або наступного найбільшого. Ми називаємо вказаний діапазон відхилення як масив повернення, тому результат є "Іспанія", як і раніше.
