¿Qué son los rangos derramados?

Hace tiempo comencé a experimentar con matrices dinámicas para mis proyectos personales pero nunca había hablado de ellas. Ni siquiera las he utilizado en proyectos de clientes pues en ocasiones no cuentan con Excel 365 (afortunadamente cada vez más personas y empresas se suscriben).

Hace unos años muchos temblabas al oír hablar de fórmulas matriciales o matrices. Ese mundo totalmente desconocido al que no sabías si asomarte porque ignorabas si realmente podrías aplicarlo en tu día a día con Excel. Siempre has pensado que se trataba de una herramienta para expertos o gurús.

Gracias a la actualización del motor de cálculo de Excel las fórmulas matriciales se han vuelto sencillas y fáciles de entender para todos.

Hoy te explico que es esto de las matrices dinámicas en Excel y te cuento cómo usarlas para hacer de tu trabajo una experiencia más relajada y satisfactoria.

Qué son las matrices dinámicas

Una definición ‘de diccionario’ podría decir que se trata de una matriz redimensionable sobre la que se realizan cálculos y automáticamente devuelve valores en varias celdas basándose en una única fórmula introducida en una celda. Déjame que te lo explique con un ejemplo. Imagina que quieres crear una ‘tabla de multiplicar’. Tienes los operandos dispuestos de la siguiente manera:

En las versiones previas a Excel 365, la siguiente fórmula funcionaría solo para la primera celda, a menos que previamente selecciones las celdas en las que vayas a introducirla y presiones las teclas Ctrl + Mayús + Intro.

=B2:K2*A3:A12

Este sería el resultado:

Ahora mira qué ocurre cuando se introduce la misma fórmula en Excel 365. Solo tienes que escribirla en la celda B3 (en el ejemplo), presionar Intro y... voilá! Ya tienes toda la tabla rellena.

El rellenar varias celdas con una sola fórmula se llama derramar (o desbordar) y el rango de celdas afectado recibe el nombre de rango derramado (o desbordado).

Antes de continuar déjame decirte que el concepto de matrices dinámicas no es solo una nueva forma de trabajar. Ha supuesto un cambio revolucionario en todo el motor de cálculo. Gracias a las matrices dinámicas se han añadido varias funciones de matriz dinámica a la biblioteca de Excel. Con el tiempo estas funciones sustituirán a las fórmulas matriciales que has usado hasta ahora y que se introducían con Ctrl + Mayús + Intro.

La mala noticia es que, aun estando publicadas desde enero de 2020 únicamente están disponibles para suscriptores de Office 365 en Windows, Mac, Android, iOS y Windows Mobile. Actualmente también se puede usar en la versión Excel Online.

6 funciones nuevas

Como parte de la nueva característica se introdujeron seis funciones nuevas capaces de manejar matrices  y cuyos resultados se muestran en varias celdas. El rango de celdas de salida es siempre dinámico. Cuando se produce cualquier cambio en los datos originales, los resultados se actualizan automáticamente... y de ahí su nombre: funciones de matrices dinámicas.

Estas nuevas funciones se ocupan de realizar tareas que hasta ahora se consideraban difíciles de resolver. Por ejemplo, pueden eliminar duplicados, extraer y contar valores únicos, filtrar celdas en blanco, generar números aleatorios tanto enteros como decimales, ordenar de forma ascendente o descendente y cientos de etcéteras.

• UNICOS: Devuelve la lista de valores únicos de un rango de celdas.
• FILTRAR: Filtra los datos según un criterio que definas.
• ORDENAR: Ordena un rango de datos según la columna que quieras.
• ORDENARPOR: Ordena un rango de celdas por otro rango o matriz.
• MATRIZALEAT: Genera una matriz de números aleatorios.
• SECUENCIA: Genera una lista de números correlativos

Además Microsoft ha hecho modificaciones en dos funciones para facilitar su integración con las matrices dinámicas:

• BUSCARX: mil veces mejor que BUSCARV y BUSCARH, es capaz de buscar en cualquier columna o fila y devolver varios valores.
• COINCIDIRX: mucho más versátil que COINCIDIR, pues puede hacer búsquedas tanto horizontal como verticalmente y devolver la posición relativa dentro del rango.

Rangos derramados - varios resultados con una función

Como he comentado más arriba un rango derramado es el rango de celdas que contiene los valores devueltos por una fórmula de matriz dinámica.

Como se ve en una imagen anterior cuando seleccionas cualquier celda de un rango derramado aparece un borde azul para indicar que todos los valores que encierra están calculados por la fórmula de la celda superior izquierda. Si eliminas la fórmula de la primera celda, desaparecerán todos los datos.

Ventajas de usar rangos dinámicos.

La gran ventaja de pasarte a las fórmulas de rangos dinámicos es que hacen las fórmulas más potentes y fáciles de usar.

Por ejemplo, en versiones diferentes a Excel 365, para extraer los valores únicos de un rango de celdas tenías que utilizar fórmulas como esta:

=SI.ERROR(INDICE($A$2:$A$10; COINCIDIR(0; CONTAR.SI($B$1:B1;$A$2:$A$10) + (CONTAR.SI($A$2:$A$10; $A$2:$A$10)<>1); 0)); "")

Con Excel 365 basta con escribir =UNICOS(A2:A10) para obtener los valores únicos.

Vamos, no hay diferencia, ¿verdad? ????

Otra ventaja es que no es necesario saber si una función admite matrices o no. Simplemente úsala. Si las admite, Excel se encargará de mostrar todos los resultados. En caso contrario devolverá un único resultado.

Y otra ventaja es la posibilidad de anidación de funciones de matriz dinámica potenciando exponencialmente las posibilidades.

Desventajas de usar rangos dinámicos

Las fórmulas de matriz dinámica son fabulosas, tienen muchas ventajas. A lo largo de las próximas publicaciones iré desvelando usos y trucos para demostrarlo. Sin embargo también debes tener en cuenta algunas consideraciones:

• Los rangos derramados devueltos por una fórmula de matriz dinámica no pueden ordenarse con la herramienta de ordenación de Excel (la antigua). Si intentas hacer cualquier tipo de ordenación de esta forma, obtendrás el error ’No se puede cambiar parte de una matriz’. Para ordenar los resultados de la matriz dinámica tienes que recurrir  a las funciones ORDENAR y ORDENARPOR.
• Los elementos individuales devueltos por una fórmula de matriz dinámica no se pueden separar, modificar o eliminar. También aparecerá el mensaje de error anterior. Es el mismo comportamiento que en las fórmulas matriciales antiguas.
• No se pueden usar en el interior de tablas, solo en rangos normales. Si te encuentras con el error #¡DESBORDAMIENTO! en una tabla, ya sabes por qué es.
• Los resultados de una fórmula de matriz dinámica no pueden cargarse en Power Query. Al abrirse la ventana de Power Query aparecerá el error.

¿Y qué ocurre con las versiones anteriores de Excel?

Cuando abres un libro de Excel con una versión anterior a 365 y este contiene matrices, éstas se convierten en matrices tradicionales agregando las llaves de apertura y cierre {} en la fórmula. Estas llaves no se muestran cuando se usa Excel 365.

En caso de abrir un libro que contiene alguna de las nuevas funciones de matriz dinámica con una versión anterior, verás el prefijo _xlfn para indicar que esa función no es compatible.

Así es como se muestra la función ORDENAR en Excel 2016:

Resumen

Hoy te he presentado la funcionalidad más revolucionaria de Excel en varios años: las matrices dinámicas. Esta nueva característica se complementa con 6 funciones capaces de devolver varios resultados con el mínimo esfuerzo. Fórmulas que antes tenían varias líneas, ahora solo una función (con uno o dos argumentos), ya devuelven el resultado esperado.

Las matrices dinámicas tienen innumerables aplicaciones (te las iré mostrando en siguientes publicaciones. Uno de sus puntos fuertes se encuentra en la posibilidad de combinar dos o más funciones (ya sean de matriz dinámica o normales) para obtener resultados antes imposibles.

Las matrices dinámicas (y sus funciones) solo están disponibles en Excel 365 por tanto toca actualizarse para disfrutar de ellas.