El término fórmulas matriciales es equívoco. En español, el cálculo matricial es una cosa que no tiene nada que ver con lo que aquí vamos a analizar y explicar. Vamos a ver fórmulas array en una celda a través de una colección de ejemplos que hemos preparado. Si no las has visto antes, te sorprenderás de su potencia. Si ya las conoces, por favor, comparte tus ejemplos.
Fórmulas array en una celda
Se trata de fórmulas que residen en una celda y que ofrecen un resultado. Nada más. La formula array puede realizar varios cálculos sobre los elementos que contienen.
Ya sabemos que para introducir un array tenemos que presionar CONTROL-MAYÚSCULAS-INTRO y que la fórmula se verá entre llaves { }. Hasta aquí correcto. En este tipo de fórmulas, Excel realiza cálculos en memoria que no se ven. Por lo tanto, necesitamos entender la lógica con la que Excel trabaja para confiar en que el resultado es correcto porque estamos acostumbrados a ver paso a paso cómo el programa va haciendo lo que nosotros queremos.
Suma de un rango que contiene valores de error
Si intentas usar la función SUMA con un rango que contiene un valor erróneo, el resultado será un error como ves en la celda B14. En Excel tienes la posibilidad de usar la función AGREGAR o de usar una fórmula array como se muestra en la celda D6. Lo que hace la fórmula array es recorrer el rango E6:E13; añade cada elemento del rango a un array interno, si el elemento es un error lo pasa como valor ” “; y después suma el contenido del array interno.
Suma de los tres valores menores de un rango
Para hacer esto, podríamos obtener separadamente los valores menores para después sumarlos. Así, la fórmula en D5 obtiene el valor más pequeño, en D6, el segundo valor más pequeño y, en D7 el tercer valor más pequeño. En la fórmula array de la celda D12 lo que hacemos es decir exáctamente lo mismo: Excel crea un array interno con los 3 valores más pequeños y después los suma.
Muy importante. Observa que 1,2,3 están también entre llaves. Lo están porque son un array que hemos creado manualmente. Las llaves, en este caso, se introducen manualmente.
Suma de los tres valores mayores de un rango
Una variación del ejemplo anterior, pero ahora con la función K.ESIMO.MAYOR. En este ejemplo, la forma de decir a la fórmula array el número de valores que queremos que sume se hace con la función INDIRECTO. Yo escibiría los números si tengo pocos (¿hasta 5?) y utilizaría la función indirecto cuando tenga más de 5. Más que nada por claridad.
Suma de valores positivos
Si quisiera sumar los valores positivos de un rango que contiene valores negativos puedo crear una fórmula array como la de la celda D7. Excel revisa cada elemento del rango B6:B13, si es mayor que 0 lo pone en el array interno que está creando y si es menor o igual a 0, pasa al array ” “. Después devuelve la suma del array interno creado.
Media con valores 0
Ciertamente, hay que tener mucho cuidado cuando calculamos una media. Si contamos el número de datos sin más nos podemos encontrar con que hay valores 0 que reducen la media y estaría mal calculada. En Excel podemos usar la fórmula de la celda C10 que calcula el número de observaciones distintas de 0, o bien, la fórmula array de la celda D6. En este caso, la fórmula mira cada elemento en el rango B6:B13, si es distinto de cero, lo pasa al array interno y, después, calcula el PROMEDIO del array interno.
Media de las diferencias
Una de las ventajas de las fórmulas array es que elimina la necesidad de columnas adicionales como la creada en la columna D. La fórmula array en la celda F9 ha creado un array interno con las diferencias entre los dos rangos B6:B13 y C6:C13, y después ha calculado el promedio. Utilizando las funciones MAX (máximo) y MIN (mínimo) podríamos obtener directamente dos valores de la dispersión de esa media. Y todo ello, como veis, sin necesidad del cálculo de la columna intermedia.
Último valor en una columna
Cuando estamos introduciendo en una tabla larga valores, es posible que queramos saber cuál ha sido el último valor introducido. Con una función de Excel lo podríamos conocer utilizando DESREF como se ve en la celda D9. También podríamos obtenerlo con una función array como la mostrada en la celda D6.
A veces también puede ser interesante saber el número de fila en la que se encuentra el último valor introducido. Lo único que tendríamos que hacer es introducir la función FILA, según se ve en la celda D11.
Te puedes bajar el archivo con los ejercicios en este enlace.
Final
No es nada intuitivo entender las funciones array. Se debe uno ejercitar durante un tiempo. Así que os animo a intentarlo. Mandar vuestros ejemplos de funciones array para compartirlas.