Резиме
Да бисте извршили двосмерно приближно тражење подударања са више критеријума, можете користити формулу низа засновану на ИНДЕКС и МАТЦХ, уз помоћ функције ИФ да примените критеријуме. У приказаном примеру, формула у К8 је:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
при чему су подаци (Д6: Х16), пречник (Д5: Х5), материјал (Б6: Б16) и тврдоћа (Ц6: Ц16) именовани опсези који се користе само ради погодности.
Напомена: ово је формула низа и мора се унети са Цонтрол + Схифт + Ентер
Објашњење
Циљ је потражити брзину додавања на основу материјала, тврдоће и пречника сврдла. Вредности протока налазе се у именованим подацима опсега (Д6: Х16).
То се може урадити двосмерном формулом ИНДЕКС и МАТЦХ. Једна функција МАТЦХ обрађује број реда (материјал и тврдоћа), а друга функција МАТЦХ проналази број колоне (пречник). Функција ИНДЕКС враћа коначни резултат.
У приказаном примеру, формула у К8 је:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Преломи редова додани само ради читљивости).
Шкакљиво је то што материјал и тврдоћу треба руковати заједно. Морамо ограничити МАТЦХ на вредности тврдоће за дати материјал (челик са ниским угљеником у приказаном примеру).
То можемо учинити помоћу функције ИФ. У основи користимо ИФ да „бацимо“ небитне вредности пре него што потражимо подударање.
Детаљи
Функцији ИНДЕКС дају се именовани подаци о опсегу (Д6: Х16) као за низ. Прва функција МАТЦХ обрађује број реда:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Да бисмо лоцирали тачан ред, треба да направимо тачно подударање материјала и приближно подударање тврдоће. То радимо помоћу функције ИФ да бисмо прво филтрирали небитну тврдоћу:
IF(material=K5,hardness) // filter
Тестирамо све вредности у материјалу (Б6: Б16) да бисмо видели да ли се подударају са вредностима у К5 („Челик са ниским угљеником“). Ако је то случај, вредност тврдоће се преноси. Ако није, АКО враћа ФАЛСЕ. Резултат је низ попут овог:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Приметите да су једине преживеле вредности оне повезане са ниско-угљеничним челиком. Остале вредности су сада ФАЛСЕ. Овај низ се враћа директно у функцију МАТЦХ као лоокуп_арраи.
Вриједност претраживања за подударање долази из К6, који садржи задану тврдоћу, 176. МАТЦХ је конфигурисан за приближно подударање постављањем матцх_типе на 1. Помоћу ових поставки МАТЦХ занемарује ФАЛСЕ вредности и враћа положај тачног подударања или следеће најмање вредности .
Напомена: Вредности тврдоће морају се сортирати у растућем редоследу за сваки материјал.
Са тврдоћом наведеном као 176, МАТЦХ враћа 6, испоручених директно ИНДЕКС-у као број реда. Сада можемо преписати оригиналну формулу овако:
=INDEX(data,6,MATCH(K7,diameter,1))
Друга формула МАТЦХ проналази тачан број колоне извођењем приближног подударања пречника:
MATCH(K7,diameter,1) // get column num
Напомена: вредности у пречнику Д5: Х5 морају се сортирати у растућем редоследу.
Вредност претраживања долази из К7 (0,75), а низ_тражења је именовани пречник опсега (Д5: Х5).
Као и раније, МАТЦХ је постављен на приближно подударање постављањем матцх_типе на 1.
Са пречником датим као 0,75, МАТЦХ враћа 3, испоручена директно функцији ИНДЕКС као број колоне. Оригинална формула сада решава:
=INDEX(data,6,3) // returns 0.015
ИНДЕКС враћа коначни резултат од 0,015, вредност из Ф11.
