Uso de ÍNDICE para extraer un valor de un resultado de fórmulas de matriz de Excel

No todas las fórmulas de matriz devuelven matrices con varias columnas y / o varias filas a la hoja de trabajo. Pero cuando lo hacen, puede suceder que esté interesado en ver solo un valor en la matriz. Puede usar la función INDICE de Excel para ayudar con eso.

Por ejemplo, LINEST es una de las funciones de la hoja de trabajo que funcionará correctamente solo si ingresa en una matriz la fórmula que contiene la función. Pero suponga que desea acceder a un solo valor de celda en los resultados de LINEST, tal vez para acomodar un diseño de hoja de trabajo en un informe de rutina. En ese caso, no necesariamente desea el conjunto completo de resultados de LINEST, y puede usar la función INDICE de Excel para extraer y mostrar solo el que le interesa mostrar.

Por ejemplo, así es como puede ingresar una matriz LINEST para una regresión múltiple:

= LINEST (A2: A51, B2: D51`` VERDADERO)

Si ingresa en matriz esa fórmula en un rango de 5 filas por 4 columnas, la intersección de la tercera fila y la primera columna de ese rango contiene el valor R cuadrado de la regresión. Entonces, si selecciona solo una celda e ingresa la siguiente fórmula, obtendrá solo el valor R cuadrado:

= ÍNDICE (LINEST (A2: A51, B2: D51`` VERDADERO), 3,1)

Aquí, está proporcionando INDEX con la matriz de valores devueltos por la función LINEST. Ese es el primer argumento de INDEX. El segundo y tercer argumento de INDEX son los números 3 y 1, que le indican a INDEX que busque el valor en la tercera fila y la primera columna de la matriz y lo devuelva a la hoja de trabajo.

Puede ingresar la fórmula de ÍNDICE completa como se acaba de dar normalmente, con una matriz de resultados de LINEST como su primer argumento, sin la combinación Ctrl y Shift y Enter, es decir, sin ingresar la matriz. (Pruébelo en ambos sentidos, tanto la matriz ingresando como ingresando normalmente).

Y, sin embargo, si intenta ingresar la siguiente fórmula de matriz de celda única, produce el error # ¡VALOR! si intenta ingresarlo normalmente:

= SI (H44639: H44644> 0, G44639: G44644,0)

Cuando la fórmula llama a una función que Excel espera que tome una matriz como argumento, la fórmula se puede ingresar normalmente. Ese es el caso de esta fórmula:

= ÍNDICE (LINEST (A2: A51, B2: D51`` VERDADERO), 3,1)

Los resultados de LINEST se anidan dentro de la función INDICE, donde actúan como su primer argumento. Excel espera que INDEX tome una matriz de valores como su primer argumento; el análisis de una matriz es para lo que nació INDEX. Por lo tanto, no es necesario ingresar la fórmula tal como se indica en una matriz.

Por el contrario, esta fórmula de matriz de celda única debe ingresarse en matriz:

= PROMEDIO (SI (A2: A25 = "Zig", B2: B25, ""))

En este caso, Excel no espera que la función SI tome una matriz de valores como argumento, pero aquí presentamos no una, sino dos matrices de valores para SI: el rango A23: A25 y B2: B25. (Incluso puede tomar la posición de que hay una matriz de 24 instancias de "" implícitas en los dos primeros argumentos). Debido a que la fórmula no cumple con la expectativa inicial de Excel de los argumentos a SI, debe llamar la atención de Excel sobre la situación, y lo hace ingresando una matriz en la fórmula.