Fórmulas de matriz dinámicas

Desde finales de 2018 Microsoft ha ido añadiendo las funciones de matrices dinámicas (Dynamic Array Functions), lo que conlleva un cambio radical en la forma de utilizar las funciones de Excel.

A partir de ahora se pueden dar 2 posibilidades cuando Excel realiza un cálculo:

Que una fórmula devuelva un solo valor, mostrándose su resultado en una sola celda.
Que una fórmula devuelva más de una valor, utilizará varias celdas para mostrar el resultado.

¿Qué son las fórmulas de matriz dinámica?

Las fórmulas que pueden devolver matrices de tamaño variable se denominan fórmulas de matriz dinámica.

Estas fórmulas mostrarán el resultado en tantas celdas como lo necesite, ajustándose dinámicamente el tamaño del rango de salida y colocando los resultados en cada celda dentro de ese rango. Es aquí cuando hablaremos del Rango de desbordamiento.

El rango de desbordamiento

El Intervalo o Rango de desbordamiento, en inglés Spill range, es el rango en el que se muestran los resultados de las fórmulas que precisan de varias celdas para mostrar el resultado. El rango de desbordamiento puede incluir varias filas o columnas.

Al seleccionar el Rango de desbordamiento éste mostrará con un borde color azul.

Las fórmulas de Excel que devuelven un conjunto de valores, también conocidos como matrices, devuelven estos valores a celdas cercanas. Este proceso se llama desbordamiento.

El desbordamiento significa que una fórmula ha dado como resultado varios valores y esos valores se han colocado en las celdas cercanas.

Características de las fórmulas de matriz dinámica

Al presionar Enter para confirmar la fórmula, Excel ajustará dinámicamente el tamaño del rango de salida y colocará los resultados en cada celda dentro de ese rango.

Una vez que escriba una fórmula de matriz desbordada, al seleccionar cualquier celda dentro del área de desbordamiento, Excel colocará un borde resaltado alrededor del rango. El borde desaparecerá cuando seleccione una celda fuera del área.

Solo se puede editar la primera celda del área de desbordamiento. Si selecciona otra celda en el área de desbordamiento, la fórmula será visible en la barra de fórmulas, pero el texto es «fantasma», de gris claro, y no se puede cambiar. Si necesita actualizar la fórmula, debe seleccionar la celda superior izquierda en el rango de matriz, cambiarla según sea necesario y, entonces, Excel actualizará automáticamente el resto del área de desbordamiento cuando presione Entrar.

El error #¡DESBORDAMIENTO!

Este error se producirá cuando tengamos celdas que obstruyan al Rango de desbordamiento. Es decir, si la fórmula devuelve 7 filas de resultado, como vemos en la imagen, pero hay celdas con valores dentro en ese rango, en lugar de sobrescribirse se mostrará el error.

En la imagen vemos que en la cuarta fila había un nombre escrito, por lo que la fórmula de matriz dinámica no puede introducirse, devolviendo el error #DESBORDAMIENTO!, que indica que hay un bloqueo.

Excel nos sugerirá borrar el contenido de las celdas que obstruyen.

Una vez eliminemos los datos que bloquean el rango de desbordamiento, la fórmula se desbordará según lo esperado.

Las fórmulas de matriz heredadas

Las fórmulas de matriz heredadas escritas a través de CTRL+MAYÚS+ENTRAR (CSE) siguen siendo compatibles por motivos de compatibilidad inversa, pero ya no deben usarse. Si quiere, puede convertir fórmulas de matriz heredadas en fórmulas de matriz dinámicas. Para ello, busque la primera celda del rango de matriz, copie el texto de la fórmula, elimine todo el rango de la matriz heredada y vuelva a escribir la fórmula en la celda superior izquierda.

Hacer referencia al Rango de desbordamiento

Cuando tengamos un rango de desbordamiento será muy sencillo hacer referencia a él. Simplemente si en una celda se escribe el signo de igual, seguido por la celda donde comienza el Rango de desbordamiento y al final el símbolo de almohadilla. Siguiendo el ejemplo de la imagen anterior sería =E1#

Funciones de matrices dinámicas

Las primeras funciones de matrices dinámicas fueron las siguientes:

SECUENCIA: Permite generar una lista de números secuenciales en una matriz.
MATRIZALEAT: Devuelve una matriz de números aleatorios.
ORDENAR: Ordena el contenido de un rango o matriz.
ORDENARPOR: Ordena contenido de un rango o matriz en función de los valores de un rango o matriz correspondiente.
UNICOS: Devuelve una lista de valores únicos de una lista o rango.
FILTRAR: Permite filtrar un rango de datos en función de los criterios que defina.

En el año 2022, Microsoft ha liberado nuevas funciones, en su mayor parte de matrices dinámicas:

TEXTOANTES: Muestra el texto que aparece antes de una cadena o un carácter determinado.
TEXTODESPUES: Muestra el texto que aparece después de una cadena o un carácter determinado.
DIVIDIRTEXTO: Divide las cadenas de texto mediante delimitadores de columna y fila.
TOMAR: Devuelve un número especificado de filas o columnas contiguas desde el principio o el final de una matriz.
EXCLUIR: Excluye un número especificado de filas o columnas del inicio o el final de una matriz.
EXPANDIR: Expande o rellena una matriz a las dimensiones de fila y columna especificadas.
ENCOL: Devuelve la matriz en una sola columna.
ENFILA: Devuelve la matriz en una sola fila.
ELEGIRCOLS: Devuelve las columnas especificadas de una matriz.
ELEGIRFILAS: Devuelve las filas especificadas de una matriz.
APILARV: Anexa matrices verticalmente y en secuencia para devolver una matriz más grande.
APILARH: Anexa matrices de manera horizontal y en secuencia para devolver una matriz mayor.
AJUSTARCOLS: Ajusta por columnas la fila o columna de valores proporcionada después de un número especificado de elementos para formar una nueva matriz.
AJUSTARFILAS: Ajusta la fila o columna de valores proporcionada por filas después de un número especificado de elementos para formar una nueva matriz.