Zhrnutie
Ak chcete vykonať obojsmerné približné vyhľadanie zhody s viacerými kritériami, môžete použiť maticový vzorec založený na indexoch INDEX a MATCH s pomocou funkcie IF na uplatnenie kritérií. V zobrazenom príklade je vzorec v K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kde údaje (D6: H16), priemer (D5: H5), materiál (B6: B16) a tvrdosť (C6: C16) sú pomenované rozsahy používané iba pre uľahčenie.
Poznámka: toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter
Vysvetlenie
Cieľom je vyhľadať rýchlosť posuvu na základe materiálu, tvrdosti a priemeru vrtáka. Hodnoty rýchlosti posuvu sú v pomenovaných údajoch rozsahu (D6: H16).
To je možné vykonať pomocou obojsmerného vzorca INDEX a MATCH. Jedna funkcia MATCH zisťuje číslo riadku (materiál a tvrdosť) a druhá funkcia MATCH vyhľadáva číslo stĺpca (priemer). Funkcia INDEX vráti konečný výsledok.
V zobrazenom príklade je vzorec v K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Konce riadkov pridané iba kvôli čitateľnosti).
Zložité je, že s materiálom a tvrdosťou je potrebné zaobchádzať spoločne. Musíme obmedziť MATCH na hodnoty tvrdosti pre daný materiál (v zobrazenom príklade nízkouhlíková oceľ).
Môžeme to urobiť pomocou funkcie IF. V podstate používame IF na „vyhodenie“ irelevantných hodnôt skôr, ako hľadáme zhodu.
Detaily
Funkcia INDEX má pomenované údaje rozsahu (D6: H16) ako pre pole. Prvá funkcia MATCH zistí číslo riadku:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Aby sme našli správny riadok, musíme urobiť presnú zhodu s materiálom a približnú zhodu s tvrdosťou. Robíme to tak, že pomocou funkcie IF najskôr odfiltrujeme irelevantnú tvrdosť:
IF(material=K5,hardness) // filter
Testujeme všetky hodnoty v materiáli (B6: B16), aby sme zistili, či sa zhodujú s hodnotami v K5 („nízkouhlíková oceľ“). Ak je to tak, hodnota tvrdosti sa odovzdá. Ak nie, IF vráti FALSE. Výsledkom je pole ako je toto:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Všimnite si, že jediné zachované hodnoty sú hodnoty spojené s nízkouhlíkovou oceľou. Ostatné hodnoty sú teraz FALSE. Toto pole sa vracia priamo do funkcie MATCH ako lookup_array.
Vyhľadávacia hodnota pre zhodu pochádza z K6, ktorá obsahuje danú tvrdosť 176. MATCH je nakonfigurovaný na približnú zhodu nastavením match_type na 1. Pri týchto nastaveniach MATCH ignoruje FALSE hodnoty a vráti pozíciu presnej zhody alebo najbližšej najmenšej hodnoty .
Poznámka: Hodnoty tvrdosti musia byť pre každý materiál zoradené vzostupne.
Pri tvrdosti 176 vráti MATCH 6, ktoré sa doručia priamo na INDEX ako číslo riadku. Teraz môžeme prepísať pôvodný vzorec takto:
=INDEX(data,6,MATCH(K7,diameter,1))
Druhý MATCH vzorec nájde správne číslo stĺpca vykonaním približnej zhody na priemere:
MATCH(K7,diameter,1) // get column num
Poznámka: hodnoty v priemere D5: H5 musia byť zoradené vzostupne.
Vyhľadávacia hodnota pochádza z K7 (0,75) a lookup_array je pomenovaný priemer rozsahu (D5: H5).
Rovnako ako predtým je MATCH nastavený na približnú zhodu nastavením match_type na 1.
S priemerom 0,75 vráti MATCH 3, doručené priamo do funkcie INDEX ako číslo stĺpca. Pôvodný vzorec je teraz vyriešený takto:
=INDEX(data,6,3) // returns 0.015
INDEX vráti konečný výsledok 0,015, hodnotu z F11.
