souhrn
Chcete-li provést obousměrné přibližné vyhledání shody s více kritérii, můžete použít maticový vzorec založený na INDEXU a MATCH, s pomocí funkce IF použít kritéria. V zobrazeném příkladu je vzorec v K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kde data (D6: H16), průměr (D5: H5), materiál (B6: B16) a tvrdost (C6: C16) jsou pojmenované rozsahy používané pouze pro přehlednost.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter
Vysvětlení
Cílem je vyhledat rychlost posuvu na základě materiálu, tvrdosti a průměru vrtáku. Hodnoty rychlosti posuvu jsou v pojmenovaných datech rozsahu (D6: H16).
To lze provést pomocí oboustranného vzorce INDEX a MATCH. Jedna funkce MATCH vypočítá číslo řádku (materiál a tvrdost) a druhá funkce MATCH najde číslo sloupce (průměr). Funkce INDEX vrací konečný výsledek.
V zobrazeném příkladu je vzorec v K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Konce řádků přidány pouze pro čitelnost).
Složité je, že s materiálem a tvrdostí je třeba zacházet společně. Musíme omezit MATCH na hodnoty tvrdosti pro daný materiál (v uvedeném příkladu nízkouhlíková ocel).
Můžeme to udělat pomocí funkce IF. V zásadě používáme IF k „vyhodení“ irelevantních hodnot, než budeme hledat shodu.
Detaily
Funkce INDEX má pojmenovaná data rozsahu (D6: H16) jako pro pole. První funkce MATCH vypočítá číslo řádku:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Abychom našli správný řádek, musíme provést přesnou shodu s materiálem a přibližnou shodu s tvrdostí. Děláme to tak, že nejprve použijeme funkci IF k odfiltrování irelevantní tvrdosti:
IF(material=K5,hardness) // filter
Vyzkoušíme všechny hodnoty v materiálu (B6: B16), abychom zjistili, zda odpovídají hodnotám v K5 („nízkouhlíková ocel“). Pokud ano, hodnota tvrdosti prochází. Pokud ne, IF vrátí FALSE. Výsledkem je toto pole:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Všimněte si, že jediné přežívající hodnoty jsou hodnoty spojené s nízkouhlíkovou ocelí. Ostatní hodnoty jsou nyní FALSE. Toto pole se vrací přímo do funkce MATCH jako lookup_array.
Vyhledávací hodnota pro shodu pochází z K6, která obsahuje danou tvrdost, 176. MATCH je nakonfigurován pro přibližnou shodu nastavením match_type na 1. S těmito nastaveními MATCH ignoruje FALSE hodnoty a vrací pozici přesné shody nebo nejbližší nejmenší hodnoty .
Poznámka: hodnoty tvrdosti musí být pro každý materiál řazeny vzestupně.
S tvrdostí udanou jako 176 vrátí MATCH 6, doručeno přímo do INDEXU jako číslo řádku. Nyní můžeme přepsat původní vzorec takto:
=INDEX(data,6,MATCH(K7,diameter,1))
Druhý MATCH vzorec najde správné číslo sloupce provedením přibližné shody na průměru:
MATCH(K7,diameter,1) // get column num
Poznámka: hodnoty v průměru D5: H5 je třeba seřadit vzestupně.
Hodnota vyhledávání pochází z K7 (0,75) a lookup_array je pojmenovaný průměr rozsahu (D5: H5).
Stejně jako dříve je MATCH nastaven na přibližnou shodu nastavením match_type na 1.
S průměrem daným jako 0,75 vrátí MATCH 3, doručené přímo do funkce INDEX jako číslo sloupce. Původní vzorec se nyní vyřeší na:
=INDEX(data,6,3) // returns 0.015
INDEX vrací konečný výsledek 0,015, což je hodnota z F11.
