Обобщение
За да извършите двупосочно приблизително търсене на съвпадение с множество критерии, можете да използвате формула на масив, базирана на 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
(Добавени са прекъсвания на редове само за четливост).
Трудното е, че материалът и твърдостта трябва да се обработват заедно. Трябва да ограничим MATCH до стойностите на твърдост за даден материал (нисковъглеродна стомана в показания пример).
Можем да направим това с функцията 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), а lookup_array е наименованият диаметър на диапазона (D5: H5).
Както преди, MATCH е настроен на приблизително съвпадение, като зададе match_type на 1.
С диаметър, зададен като 0,75, MATCH връща 3, доставени директно на функцията INDEX като номер на колоната. Оригиналната формула вече решава:
=INDEX(data,6,3) // returns 0.015
INDEX връща краен резултат от 0,015, стойността от F11.
