Sommario
Per eseguire una ricerca di corrispondenza approssimativa bidirezionale con più criteri, è possibile utilizzare una formula di matrice basata su INDICE e CONFRONTA, con l'aiuto della funzione SE per applicare i criteri. Nell'esempio mostrato, la formula in K8 è:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
dove i dati (D6: H16), il diametro (D5: H5), il materiale (B6: B16) e la durezza (C6: C16) sono intervalli denominati utilizzati solo per comodità.
Nota: questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio
Spiegazione
L'obiettivo è cercare una velocità di avanzamento basata su materiale, durezza e diametro della punta. I valori della velocità di avanzamento sono nei dati dell'intervallo denominato (D6: H16).
Questo può essere fatto con una formula INDICE e CONFRONTA a due vie. Una funzione MATCH calcola il numero di riga (materiale e durezza) e l'altra funzione MATCH trova il numero di colonna (diametro). La funzione INDICE restituisce il risultato finale.
Nell'esempio mostrato, la formula in K8 è:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Interruzioni di riga aggiunte solo per la leggibilità).
La parte difficile è che il materiale e la durezza devono essere gestiti insieme. Dobbiamo limitare MATCH ai valori di durezza per un dato materiale (acciaio a basso tenore di carbonio nell'esempio mostrato).
Possiamo farlo con la funzione IF. Essenzialmente, usiamo IF per "buttare via" valori irrilevanti prima di cercare una corrispondenza.
Dettagli
La funzione INDICE riceve i dati dell'intervallo denominato (D6: H16) come per l'array. La prima funzione MATCH calcola il numero di riga:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Per individuare la riga corretta, dobbiamo fare una corrispondenza esatta sul materiale e una corrispondenza approssimativa sulla durezza. Lo facciamo utilizzando la funzione IF per filtrare prima la durezza irrilevante:
IF(material=K5,hardness) // filter
Testiamo tutti i valori nel materiale (B6: B16) per vedere se corrispondono al valore in K5 ("acciaio a basso tenore di carbonio"). In tal caso, il valore di durezza viene trasmesso. In caso contrario, IF restituisce FALSE. Il risultato è un array come questo:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Si noti che gli unici valori sopravvissuti sono quelli associati all'acciaio a basso tenore di carbonio. Gli altri valori ora sono FALSE. Questo array viene restituito direttamente alla funzione MATCH come lookup_array.
Il valore di ricerca per corrispondenza proviene da K6, che contiene la durezza data, 176. MATCH è configurato per una corrispondenza approssimativa impostando match_type su 1. Con queste impostazioni, MATCH ignora i valori FALSE e restituisce la posizione di una corrispondenza esatta o il valore più piccolo successivo .
Nota: i valori di durezza devono essere ordinati in ordine crescente per ogni materiale.
Con una durezza data come 176, CONFRONTA restituisce 6, consegnato direttamente a INDICE come numero di riga. Ora possiamo riscrivere la formula originale in questo modo:
=INDEX(data,6,MATCH(K7,diameter,1))
La seconda formula MATCH trova il numero di colonna corretto eseguendo una corrispondenza approssimativa sul diametro:
MATCH(K7,diameter,1) // get column num
Nota: i valori di diametro D5: H5 devono essere ordinati in ordine crescente.
Il valore di ricerca proviene da K7 (0,75) e lookup_array è il diametro dell'intervallo denominato (D5: H5).
Come prima, MATCH è impostato per approssimare la corrispondenza impostando match_type su 1.
Con il diametro fornito come 0,75, CONFRONTA restituisce 3, fornito direttamente alla funzione INDICE come numero di colonna. La formula originale ora si risolve in:
=INDEX(data,6,3) // returns 0.015
INDICE restituisce un risultato finale di 0,015, il valore da F11.
