Sammanfattning
För att utföra en tvåvägs ungefärlig matchning med flera kriterier kan du använda en matrisformel baserad på INDEX och MATCH, med hjälp av IF-funktionen för att tillämpa kriterier. I exemplet som visas är formeln i K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
där data (D6: H16), diameter (D5: H5), material (B6: B16) och hårdhet (C6: C16) är angivna områden som endast används för bekvämlighet.
Obs: detta är en matrisformel och måste anges med Control + Shift + Enter
Förklaring
Målet är att leta efter en matningshastighet baserat på material, hårdhet och borrdiameter. Matningstaktvärden är i de nämnda avståndsuppgifter (D6: H16).
Detta kan göras med en tvåvägs INDEX- och MATCH-formel. En MATCH-funktion räknar ut radnumret (material och hårdhet), och den andra MATCH-funktionen hittar kolumnnumret (diameter). INDEX-funktionen returnerar det slutliga resultatet.
I exemplet som visas är formeln i K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Radbrytningar har lagts till för läsbarhet endast).
Det knepiga är att material och hårdhet måste hanteras tillsammans. Vi måste begränsa MATCH till hårdhetsvärdena för ett visst material (lågkolstål i exemplet som visas).
Vi kan göra detta med IF-funktionen. I huvudsak använder vi IF för att "kasta bort" irrelevanta värden innan vi letar efter en matchning.
Detaljer
INDEX funktionen ges de nämnda avståndsuppgifter (D6: H16) som för array. Den första MATCH-funktionen räknar ut radnumret:
MATCH(K6,IF(material=K5,hardness),1) // get row num
För att hitta rätt rad måste vi göra en exakt matchning på material och en ungefärlig matchning på hårdhet. Vi gör detta genom att använda IF-funktionen för att först filtrera bort irrelevant hårdhet:
IF(material=K5,hardness) // filter
Vi testar alla värden i material (B6: B16) för att se om de matchar värdet i K5 ("Low Carbon Steel"). I så fall skickas hårdhetsvärdet igenom. Om inte, returnerar FALSKT. Resultatet är en array som denna:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Observera att de enda överlevande värdena är de som är associerade med lågkolstål. De andra värdena är nu FALSE. Denna matris returneras direkt till MATCH-funktionen som uppslagningsmatris.
Sökningsvärdet för matchning kommer från K6, som innehåller den givna hårdheten, 176. MATCH konfigureras för ungefärlig matchning genom att ställa in match_type till 1. Med dessa inställningar ignorerar MATCH FALSE-värden och returnerar positionen för en exakt matchning eller nästa minsta värde .
Obs: hårdhetsvärdena måste sorteras i stigande ordning för varje material.
Med hårdhet angiven 176 returnerar MATCH 6, levereras direkt till INDEX som radnummer. Vi kan nu skriva om den ursprungliga formeln så här:
=INDEX(data,6,MATCH(K7,diameter,1))
Den andra MATCH-formeln hittar rätt kolumnnummer genom att utföra en ungefärlig matchning på diametern:
MATCH(K7,diameter,1) // get column num
Obs: värden i diameter D5: H5 måste sorteras i stigande ordning.
Uppslagsvärdet kommer från K7 (0,75), och uppslagnings är det namngivna området diameter (D5: H5).
Som tidigare är MATCH inställd på ungefärlig matchning genom att ställa match_type till 1.
Med diametern 0,75 returnerar MATCH 3, levereras direkt till INDEX-funktionen som kolumnnummer. Den ursprungliga formeln löser nu:
=INDEX(data,6,3) // returns 0.015
INDEX returnerar ett slutresultat på 0,015, värdet från F11.
