Резюме
Щоб виконати двосторонній приблизний пошук відповідності з кількома критеріями, ви можете використовувати формулу масиву на основі INDEX та MATCH, за допомогою функції IF для застосування критеріїв. У наведеному прикладі формула в K8 має вигляд:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
де дані (D6: H16), діаметр (D5: H5), матеріал (B6: B16) і твердість (C6: C16) називаються діапазонами, що використовуються лише для зручності.
Примітка: це формула масиву, і її потрібно вводити за допомогою Control + Shift + Enter
Пояснення
Метою є пошук швидкості подачі на основі матеріалу, твердості та діаметра свердла. Значення швидкості подачі знаходяться в названих даних діапазону (D6: H16).
Це можна зробити за допомогою двосторонньої формули INDEX та MATCH. Одна функція MATCH визначає номер рядка (матеріал і твердість), а інша функція MATCH знаходить номер стовпця (діаметр). Функція INDEX повертає кінцевий результат.
У наведеному прикладі формула в K8 має вигляд:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Розриви рядків додані лише для читабельності).
Хитрощі в тому, що матеріал і твердість потрібно обробляти разом. Нам потрібно обмежити МАТЧ до значень твердості для даного матеріалу (низьковуглецева сталь у наведеному прикладі).
Ми можемо зробити це за допомогою функції IF. По суті, ми використовуємо IF, щоб "викинути" нерелевантні значення, перш ніж шукати збіг.
Деталі
Функція INDEX отримує іменовані дані діапазону (D6: H16) як для масиву. Перша функція MATCH опрацьовує номер рядка:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Щоб знайти правильний рядок, нам потрібно зробити точну відповідність матеріалу та приблизну відповідність твердості. Ми робимо це за допомогою функції IF, щоб спочатку відфільтрувати невідповідну твердість:
IF(material=K5,hardness) // filter
Ми перевіряємо всі значення в матеріалі (B6: B16), щоб перевірити, чи відповідають вони значенням у K5 ("Низьковуглецева сталь"). Якщо так, значення твердості передається. Якщо ні, IF повертає FALSE. Результат - такий масив:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Зверніть увагу, що єдиними збереженими значеннями є ті, що пов'язані з низьковуглецевою сталлю. Інші значення тепер FALSE. Цей масив повертається безпосередньо до функції MATCH як lookup_array.
Значення пошуку для збігу походить від K6, який містить задану твердість, 176. MATCH налаштовується на приблизний збіг, встановлюючи match_type на 1. За допомогою цих налаштувань MATCH ігнорує FALSE значення і повертає позицію точного збігу або наступного найменшого значення .
Примітка: значення твердості повинні бути відсортовані за зростанням для кожного матеріалу.
З твердістю, заданою як 176, MATCH повертає 6, доставлених безпосередньо до INDEX як номер рядка. Тепер ми можемо переписати вихідну формулу так:
=INDEX(data,6,MATCH(K7,diameter,1))
Друга формула MATCH знаходить правильний номер стовпця, виконуючи приблизний збіг по діаметру:
MATCH(K7,diameter,1) // get column num
Примітка: значення в діаметрі D5: H5 потрібно сортувати за зростанням.
Значення пошуку походить від K7 (0,75), а масив пошуку - це названий діаметр діапазону (D5: H5).
Як і раніше, для MATCH встановлено приблизний збіг, встановивши match_type на 1.
З діаметром, заданим як 0,75, MATCH повертає 3, доставлених безпосередньо до функції INDEX як номер стовпця. Оригінальна формула тепер вирішує:
=INDEX(data,6,3) // returns 0.015
INDEX повертає кінцевий результат 0,015, значення з F11.
