Sažetak
Da biste izvršili dvosmjerno traženje približnog podudaranja s više kriterija, možete upotrijebiti formulu niza temeljenu na INDEX i MATCH, uz pomoć funkcije IF za primjenu kriterija. U prikazanom primjeru formula u K8 je:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
gdje su podaci (D6: H16), promjer (D5: H5), materijal (B6: B16) i tvrdoća (C6: C16) imenovani rasponi koji se koriste samo radi praktičnosti.
Napomena: ovo je formula niza i mora se unijeti Control + Shift + Enter
Obrazloženje
Cilj je potražiti brzinu dodavanja na temelju materijala, tvrdoće i promjera svrdla. Vrijednosti protoka nalaze se u navedenim podacima raspona (D6: H16).
To se može učiniti dvosmjernom formulom INDEX i MATCH. Jedna funkcija MATCH obrađuje broj retka (materijal i tvrdoća), a druga funkcija MATCH pronalazi broj stupca (promjer). Funkcija INDEX vraća konačni rezultat.
U prikazanom primjeru formula u K8 je:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Prelomi redaka dodani su samo radi čitljivosti).
Lukavo je to što materijal i tvrdoću treba rješavati zajedno. Moramo ograničiti MATCH na vrijednosti tvrdoće za određeni materijal (čelik s niskim ugljikom u prikazanom primjeru).
To možemo učiniti s funkcijom IF. U osnovi koristimo IF da bismo "bacili" nebitne vrijednosti prije nego što potražimo podudaranje.
Pojedinosti
Funkcija INDEX dobiva imenovane podatke raspona (D6: H16) kao za niz. Prva funkcija MATCH obrađuje broj retka:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Da bismo pronašli točan red, moramo točno podudarati materijal, a približno tvrdoću. To radimo pomoću funkcije IF da bismo najprije filtrirali nebitnu tvrdoću:
IF(material=K5,hardness) // filter
Testiramo sve vrijednosti u materijalu (B6: B16) kako bismo provjerili odgovaraju li vrijednosti u K5 ("Čelik s niskim ugljikom"). Ako je tako, prolazi se kroz vrijednost tvrdoće. Ako nije, IF vraća FALSE. Rezultat je niz poput ovog:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Primijetite da su jedine preživjele vrijednosti one povezane s nisko-ugljičnim čelikom. Ostale su vrijednosti sada NETOČNE. Taj se niz vraća izravno u funkciju MATCH kao lookup_array.
Vrijednost pretraživanja za podudaranje dolazi iz K6, koji sadrži zadanu tvrdoću, 176. MATCH je konfiguriran za približno podudaranje postavljanjem match_type na 1. Pomoću ovih postavki MATCH zanemaruje FALSE vrijednosti i vraća položaj točnog podudaranja ili sljedeće najmanje vrijednosti .
Napomena: vrijednosti tvrdoće moraju se sortirati uzlazno za svaki materijal.
S tvrdoćom navedenom kao 176, MATCH vraća 6, isporučenih izravno u INDEX kao broj retka. Sada možemo prepisati izvornu formulu ovako:
=INDEX(data,6,MATCH(K7,diameter,1))
Druga formula MATCH pronalazi točan broj stupca izvođenjem približnog podudaranja promjera:
MATCH(K7,diameter,1) // get column num
Napomena: vrijednosti u promjeru D5: H5 moraju se sortirati u rastućem redoslijedu.
Vrijednost pretraživanja dolazi iz K7 (0,75), a niz_traženja je imenovani promjer raspona (D5: H5).
Kao i prije, MATCH se postavlja na približno podudaranje postavljanjem match_type na 1.
S promjerom navedenim kao 0,75, MATCH vraća 3, isporučenih izravno u funkciju INDEX kao broj stupca. Izvorna formula sada se rješava na:
=INDEX(data,6,3) // returns 0.015
INDEX vraća konačni rezultat od 0,015, vrijednost iz F11.
