rezumat
Pentru a efectua o căutare de potrivire aproximativă bidirecțională cu mai multe criterii, puteți utiliza o formulă matrice bazată pe INDEX și MATCH, cu ajutorul funcției IF pentru a aplica criterii. În exemplul prezentat, formula din K8 este:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
unde datele (D6: H16), diametrul (D5: H5), materialul (B6: B16) și duritatea (C6: C16) sunt denumite intervale utilizate numai pentru comoditate.
Notă: aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter
Explicaţie
Scopul este de a căuta o viteză de alimentare bazată pe material, duritate și diametrul burghiului. Valorile ratei de alimentare se află în datele din intervalul numit (D6: H16).
Acest lucru se poate face cu o formulă bidirecțională INDEX și MATCH. O funcție MATCH lucrează numărul rândului (material și duritate), iar cealaltă funcție MATCH găsește numărul coloanei (diametrul). Funcția INDEX returnează rezultatul final.
În exemplul prezentat, formula din K8 este:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Pauzele de linie au fost adăugate numai pentru lizibilitate).
Punctul dificil este că materialul și duritatea trebuie manipulate împreună. Trebuie să restrângem MATCH la valorile de duritate pentru un anumit material (oțel cu emisii reduse de carbon în exemplul prezentat).
Putem face acest lucru cu funcția IF. În esență, folosim IF pentru a „arunca” valori irelevante înainte de a căuta o potrivire.
Detalii
Funcției INDEX i se dau datele de interval denumite (D6: H16) ca pentru matrice. Prima funcție MATCH rezolvă numărul rândului:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Pentru a localiza rândul corect, trebuie să facem o potrivire exactă pe material și o potrivire aproximativă pe duritate. Facem acest lucru folosind funcția IF pentru a filtra mai întâi duritatea irelevantă:
IF(material=K5,hardness) // filter
Testăm toate valorile din material (B6: B16) pentru a vedea dacă se potrivesc cu valoarea din K5 („Oțel cu emisii reduse de carbon”). Dacă da, valoarea durității este trecută. Dacă nu, IF returnează FALS. Rezultatul este o matrice ca aceasta:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Observați că singurele valori supraviețuitoare sunt cele asociate cu oțelul cu conținut scăzut de carbon. Celelalte valori sunt acum FALSE. Această matrice este returnată direct la funcția MATCH ca lookup_array.
Valoarea de căutare pentru potrivire provine de la K6, care conține duritatea dată, 176. MATCH este configurat pentru potrivire aproximativă setând match_type la 1. Cu aceste setări, MATCH ignoră valorile FALSE și returnează poziția unei potriviri exacte sau a celei mai mici valori următoare .
Notă: valorile durității trebuie să fie sortate în ordine crescătoare pentru fiecare material.
Cu duritatea dată 176, MATCH returnează 6, livrat direct la INDEX ca număr de rând. Acum putem rescrie formula originală astfel:
=INDEX(data,6,MATCH(K7,diameter,1))
A doua formulă MATCH găsește numărul corect de coloană efectuând o potrivire aproximativă cu diametrul:
MATCH(K7,diameter,1) // get column num
Notă: valorile cu diametrul D5: H5 trebuie sortate în ordine crescătoare.
Valoarea de căutare provine de la K7 (0,75), iar căutare_array este diametrul intervalului denumit (D5: H5).
La fel ca înainte, MATCH-ul este setat să se potrivească aproximativ, setând match_type la 1.
Cu diametrul dat ca 0,75, MATCH returnează 3, livrat direct la funcția INDEX ca număr de coloană. Formula originală se rezolvă acum la:
=INDEX(data,6,3) // returns 0.015
INDEX returnează un rezultat final de 0,015, valoarea de la F11.
