Povzetek
Za izvedbo dvosmernega približnega iskanja ujemanja z več merili lahko uporabite formulo matrike, ki temelji na INDEX in MATCH, s pomočjo funkcije IF za uporabo meril. V prikazanem primeru je formula v K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kjer so podatki (D6: H16), premer (D5: H5), material (B6: B16) in trdota (C6: C16) poimenovani obsegi, ki se uporabljajo samo za udobje.
Opomba: to je matrična formula, ki jo je treba vnesti s tipkama Control + Shift + Enter
Pojasnilo
Cilj je poiskati hitrost podajanja glede na material, trdoto in premer svedra. Vrednosti podajanja so v imenovanih podatkih obsega (D6: H16).
To lahko storimo z dvosmerno formulo INDEX in MATCH. Ena funkcija MATCH določi številko vrstice (material in trdota), druga funkcija MATCH pa najde številko stolpca (premer). Funkcija INDEX vrne končni rezultat.
V prikazanem primeru je formula v K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Prelomi vrstic so dodani samo zaradi berljivosti).
Težko je, da je treba material in trdoto obravnavati skupaj. Za določen material moramo omejiti MATCH na vrednosti trdote (nizkoogljično jeklo v prikazanem primeru).
To lahko storimo s funkcijo IF. V bistvu uporabljamo IF, da "vržemo" nepomembne vrednosti, preden iščemo ujemanje.
Podrobnosti
Funkcija INDEX dobi imenovane podatke obsega (D6: H16) kot za matriko. Prva funkcija MATCH določi številko vrstice:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Če želite poiskati pravilno vrstico, moramo natančno ujemati material in približno ujemati trdoto. To naredimo z uporabo funkcije IF, da najprej filtriramo nepomembno trdoto:
IF(material=K5,hardness) // filter
Preizkusimo vse vrednosti v materialu (B6: B16), da ugotovimo, ali se ujemajo z vrednostjo v K5 ("Nizkoogljično jeklo"). V tem primeru se vrednost trdote prenese skozi. Če ne, IF vrne FALSE. Rezultat je matrika, kot je ta:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Upoštevajte, da so edine ohranjene vrednosti tiste, povezane z nizkoogljičnim jeklom. Ostale vrednosti so zdaj FALSE. Ta matrika se vrne neposredno v funkcijo MATCH kot lookup_array.
Iskalna vrednost za ujemanje prihaja iz K6, ki vsebuje dano trdoto, 176. MATCH je konfiguriran za približno ujemanje z nastavitvijo match_type na 1. S temi nastavitvami MATCH ignorira FALSE vrednosti in vrne položaj natančnega ujemanja ali naslednje najmanjše vrednosti .
Opomba: vrednosti trdote je treba razvrstiti po naraščajočem vrstnem redu za vsak material.
S trdoto, ki je 176, MATCH vrne 6, dobavljenih neposredno v INDEX kot številko vrstice. Zdaj lahko prvotno formulo prepišemo tako:
=INDEX(data,6,MATCH(K7,diameter,1))
Druga formula MATCH poišče pravilno številko stolpca tako, da izvede približno ujemanje premera:
MATCH(K7,diameter,1) // get column num
Opomba: vrednosti v premeru D5: H5 je treba razvrstiti po naraščajočem vrstnem redu.
Vrednost iskanja prihaja iz K7 (0,75), in lookup_array je imenovani premer obsega (D5: H5).
Kot prej je tudi MATCH nastavljen na približno ujemanje z nastavitvijo match_type na 1.
Pri premeru 0,75 vrne MATCH 3, ki je neposredno v funkcijo INDEX dostavljen kot številka stolpca. Izvirna formula zdaj rešuje:
=INDEX(data,6,3) // returns 0.015
INDEX vrne končni rezultat 0,015, vrednost iz F11.
