EXCEL: Aproximación bidireccional con criterios múltiples

Para realizar una búsqueda de coincidencia aproximada bidireccional con múltiples criterios, puede usar una fórmula de matriz basada en INDICE y COINCIDIR, con la ayuda de la función SI para aplicar criterios. En el ejemplo que se muestra, la fórmula en K8 es:

=INDICE(data,COINCIDIR(K6,SI(material=K5,hardness),1),COINCIDIR(K7,diameter,1))

donde los datos (D6: H16), el diámetro (D5: H5), el material (B6: B16) y la dureza (C6: C16) se denominan rangos utilizados solo por conveniencia.

Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter

Cómo funciona esta fórmula

El objetivo es buscar una velocidad de alimentación basada en el material, la dureza y el diámetro de la broca. Los valores de velocidad de alimentación están en los datos del rango con nombre (D6: H16).

Esto se puede hacer con una fórmula bidireccional INDICE y COINCIDIR. Una función COINCIDIR calcula el número de fila (material y dureza), y la otra función COINCIDIR encuentra el número de columna (diámetro). La función INDICE devuelve el resultado final.

En el ejemplo que se muestra, la fórmula en K8 es:

=INDICE(data,
COINCIDIR(K6,SI(material=K5,hardness),1), // get row
COINCIDIR(K7,diameter,1)) // get column

(Saltos de línea agregados solo para facilitar la lectura).

La parte difícil es que el material y la dureza deben manejarse juntos. Necesitamos restringir COINCIDIR a los valores de dureza para un material dado (acero con poco carbono en el ejemplo que se muestra).

Podemos hacer esto con la función SI. Esencialmente, usamos SI para "desechar" valores irrelevantes antes de buscar una coincidencia.

Detalles

La función INDICE recibe los datos de rango con nombre (D6: H16) como para la matriz. La primera función COINCIDIR resuelve el número de fila:

COINCIDIR(K6,SI(material=K5,hardness),1) // get row num

Para ubicar la fila correcta, necesitamos hacer una coincidencia exacta en el material y una coincidencia aproximada en la dureza. Hacemos esto usando la función SI para filtrar primero la dureza irrelevante:

SI(material=K5,hardness) // filter

Probamos todos los valores en el material (B6: B16) para ver si coinciden con el valor en K5 ("Acero con poco carbono"). Si es así, se pasa el valor de dureza. Si no, SI devuelve FALSO. El resultado es una matriz como esta:

{FALSO;FALSO;FALSO;85;125;175;225;FALSO;FALSO;FALSO;FALSO}

Observe que los únicos valores supervivientes son los asociados con el acero con bajo contenido de carbono. Los otros valores ahora son FALSO. Esta matriz se devuelve directamente a la función COINCIDIR como lookup_array.

El valor de búsqueda para la coincidencia proviene de K6, que contiene la dureza dada, 176. COINCIDIR se configura para una coincidencia aproximada estableciendo match_type en 1. Con esta configuración, COINCIDIR ignora los valores FALSO y devuelve la posición de una coincidencia exacta o el siguiente valor más pequeño. .

Nota: los valores de dureza deben clasificarse en orden ascendente para cada material.

Con una dureza dada como 176, COINCIDIR devuelve 6, entregados directamente a INDICE como el número de fila. Ahora podemos reescribir la fórmula original de esta manera:

=INDICE(data,6,COINCIDIR(K7,diameter,1))

La segunda fórmula COINCIDIR encuentra el número de columna correcto al realizar una coincidencia aproximada en el diámetro:

COINCIDIR(K7,diameter,1) // get column num

Nota: los valores de diámetro D5: H5 deben clasificarse en orden ascendente.

El valor de búsqueda proviene de K7 (0,75), y la matriz de búsqueda es el diámetro del rango con nombre (D5: H5).

Como antes, el COINCIDIR está configurado para una coincidencia aproximada estableciendo match_type en 1.

Con un diámetro dado como 0.75, COINCIDIR devuelve 3, entregados directamente a la función INDICE como el número de columna. La fórmula original ahora resuelve a:

=INDICE(data,6,3) // returns 0.015

INDICE devuelve un resultado final de 0.015, el valor de F11.