Kopsavilkums
Lai veiktu divvirzienu aptuveno atbilstības meklēšanu ar vairākiem kritērijiem, kritēriju piemērošanai varat izmantot masīva formulu, kuras pamatā ir INDEX un MATCH, izmantojot funkcijas IF palīdzību. Parādītajā piemērā formula K8 ir:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kur datus (D6: H16), diametru (D5: H5), materiālu (B6: B16) un cietību (C6: C16) sauc par diapazoniem, kurus izmanto tikai ērtībai.
Piezīme: šī ir masīva formula, un tā jāievada ar Control + Shift + Enter
Paskaidrojums
Mērķis ir meklēt padeves ātrumu, pamatojoties uz materiālu, cietību un urbja diametru. Padeves ātruma vērtības ir nosauktajos diapazona datos (D6: H16).
To var izdarīt ar divvirzienu INDEX un MATCH formulu. Viena funkcija MATCH nosaka rindas numuru (materiāls un cietība), bet otra funkcija MATCH atrod kolonnas numuru (diametru). Funkcija INDEX atgriež gala rezultātu.
Parādītajā piemērā formula K8 ir:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Rindu pārtraukumi pievienoti tikai lasāmībai).
Viltīgs ir tas, ka materiāls un cietība jārisina kopā. Mums ir jāierobežo MATCH līdz cietības vērtībām konkrētam materiālam (zemā oglekļa tērauds parādītajā piemērā).
Mēs to varam izdarīt ar IF funkciju. Būtībā mēs izmantojam IF, lai "izmestu" neatbilstošās vērtības, pirms meklējam maču.
Sīkāka informācija
Funkcijai INDEX tiek piešķirti nosauktie diapazona dati (D6: H16) kā masīvam. Pirmā funkcija MATCH izstrādā rindas numuru:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Lai atrastu pareizo rindu, mums precīzi jāatbilst materiālam un aptuvenai cietībai. Mēs to darām, izmantojot funkciju IF, lai vispirms filtrētu neatbilstošo cietību:
IF(material=K5,hardness) // filter
Mēs pārbaudām visas materiāla vērtības (B6: B16), lai noskaidrotu, vai tās atbilst vērtībai K5 ("Zema oglekļa tērauds"). Ja tā, tad cietības vērtība tiek nodota. Ja nē, IF atgriež FALSE. Rezultāts ir šāds masīvs:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Ievērojiet, ka vienīgās saglabājušās vērtības ir saistītas ar zemu oglekļa tēraudu. Pārējās vērtības tagad ir FALSE. Šis masīvs tiek tieši atgriezts funkcijā MATCH kā lookup_array.
Atbilstības uzmeklēšanas vērtība nāk no K6, kas satur doto cietību 176. MATCH ir konfigurēts aptuvenai atbilstībai, iestatot match_type uz 1. Ar šiem iestatījumiem MATCH ignorē FALSE vērtības un atgriež precīzas atbilstības vai nākamās mazākās vērtības pozīciju .
Piezīme: cietības vērtības katram materiālam jāsakārto augošā secībā.
Norādot cietību kā 176, MATCH atgriež 6, kas tiek piegādāts tieši INDEX kā rindas numurs. Tagad mēs varam pārrakstīt sākotnējo formulu šādi:
=INDEX(data,6,MATCH(K7,diameter,1))
Otra MATCH formula atrod pareizo kolonnas numuru, veicot aptuvenu diametra atbilstību:
MATCH(K7,diameter,1) // get column num
Piezīme: D5: H5 diametra vērtības jāšķiro augošā secībā.
Uzmeklēšanas vērtība nāk no K7 (0,75), un uzmeklēšanas_mērs ir nosauktais diapazona diametrs (D5: H5).
Tāpat kā iepriekš, MATCH ir iestatīts uz aptuveno atbilstību, iestatot match_type uz 1.
Ja diametrs norādīts kā 0,75, MATCH atgriež 3, kas tiek tieši piegādāts funkcijai INDEX kā kolonnas numurs. Sākotnējā formula tagad atrisina:
=INDEX(data,6,3) // returns 0.015
INDEX atgriež galīgo rezultātu 0,015, vērtību no F11.
