Matrices en Excel [Parte 1]: Qué son las matrices dinámicas

Las matrices dinámicas protagonizan una de las mayores revoluciones que ha tenido Excel en su historia y ya cambiaron las reglas del juego. ¿Qué son, para qué sirven, cómo puedes sacarles el máximo provecho? En esta serie de artículos encontrarás todo lo necesario para volverte experto en esta característica fundamental del Excel moderno.

Qué son las fórmulas matriciales

Nuestra primera experiencia cuando conocemos Excel es simple: Si escribimos una fórmula en una celda, al guardar la fórmula con Enter, esa misma celda nos devuelve un resultado.

Una lógica fácil de comprender y eficiente: una celda, una fórmula, un único resultado.

Lo que muchos tardamos un poco más en comprender (y algunos nunca lo descubren), es que ciertas fórmulas de Excel pueden darnos un conjunto de resultados.

Técnicamente, a un conjunto de datos ordenados (sean el resultado de una fórmula o no), se le llama matriz. Y de ahí viene el nombre de fórmulas matriciales,  con que se designa a las fórmulas que dan como resultado matrices, es decir, un conjunto de datos.

Una consecuencia natural de las fórmulas matriciales es que, para mostrarse en pantalla, los valores devueltos deben “desbordar” la celda de origen y abarcar otras celdas contiguas. Veamos todo esto en la práctica.

Un ejemplo sencillo

Imaginemos que deseamos multiplicar por 2 cada uno de los números contenidos en el rango D2:D6 de una hoja.

La forma clásica de resolver el problema sería escribir en una celda (pongamos por caso, E2) la siguiente fórmula:

Luego, siguiendo el procedimiento habitual, arrastraríamos la fórmula para obtener los cálculos de las celdas E2 hasta E6.

Sin embargo, siempre hubo una segunda forma de obtener resultados similares.

¿Qué tal si en vez de decirle a Excel que haga una multiplicación por cada celda del rango, le dijéramos que multiplicara todo el rango por 2?

La fórmula sería esta:

Si haces esto en una versión moderna de Excel (2021, 2024 o 365). Obtendrás una matriz con los resultados:

¡Con una sola fórmula consigues todos los cálculos!

Pero esto es así de fácil desde 2018. Antes las cosas no eran tan simples. De hecho, si estás usando una versión de Excel anterior a la 2021, lamentablemente, no verás una matriz de resultados de forma automática. En esos casos requerirás un par de pasos más.

Sin embargo, tengas la versión de Excel que tengas, te recomiendo seguir leyendo. Hay algunos conocimientos en Excel que te abren puertas a mundos insospechados. Las matrices son uno de ellos.

La clave del éxito: el desbordamiento

El desbordamiento dinámico –automático– de las matrices apareció en Excel por primera vez en septiembre de 2018, en la versión 365. Antes de eso, las fórmulas matriciales no se desbordaban por sí solas.

El método antiguo realizaba el cálculo correctamente, pero debíamos seleccionar de antemano el rango donde queríamos ver volcados los datos. Si únicamente introducíamos la fórmula matricial en una celda de la forma normal, veríamos en ella un solo resultado. Los demás quedaban ocultos.

Pero preseleccionar las celdas no bastaba. Para obtener el correcto desbordamiento de los datos también había que guardar la fórmula con la secuencia de teclas CTRL+MAYÚS+ENTER. Es decir, en lugar de guardar la fórmula con ENTER, debíamos presionar la tecla CTRL; luego, sin soltarla, presionar MAYÚS (SHIFT, en inglés) y, finalmente, sin soltar ninguna de las anteriores, presionar ENTER.

Sin el uso de esta combinación de teclas, las celdas tampoco se desbordaban y el resultado mostrado podía ser el primero de la matriz o cualquier otro, incluso un valor de error. (Para entender esto último tenemos que hablar de una característica de las fórmulas matriciales, llamada intersección implícita; pero esto lo haremos en un siguiente artículo.)

En resumen: Para que una fórmula matricial funcione en versiones de Excel anteriores a la 2021, debemos dar un paso previo a la escritura de la fórmula y un paso posterior:

1. Antes de escribir la fórmula tenemos que seleccionar el rango donde esperamos los resultados.
2. Después de escribir la fórmula, en vez de presionar ENTER para guardarla, tenemos que presionar CTRL+MAYÚS+ENTER.

Desde 2018, el nuevo motor de matrices dinámicas simplifica muchísimo el proceso, pues no requiere más que la introducción de la fórmula de la manera acostumbrada y ¡listo! todos los resultados se colocan solos en su sitio.

#¡DESBORDAMIENTO!

Las matrices dinámicas trajeron un tipo de error que antes no existía, el error de desbordamiento. El valor #¡DESBORDAMIENTO! aparece en una celda cuando esta contiene una fórmula matricial que no puede desbordarse.

Normalmente ocurre cuando Excel encuentra una celda con datos dentro del rango en el que necesita mostrar los resultados. Por ejemplo, si el desbordamiento de una fórmula necesita ocupar el rango E2:E6 y hay un dato en E5, se producirá el error. La solución obvia consiste en eliminar los datos que obstaculizan el desbordamiento o cambiar de lugar la fórmula a un área con suficiente espacio.

En las versiones en inglés de Excel este valor de error aparecerá como #SPILL!

Ventajas y desventajas de las fórmulas de matriz dinámica

Ventajas

1. Optimizan la forma en que el sistema trabaja. Al solo tener que guardar y procesar una sola fórmula en lugar de varias, obtenemos libros más livianos y rápidos.
2. Simplifican la forma de trabajar de los usuarios. Gracias al nuevo concepto de matrices dinámicas, Microsoft ha podido introducir decenas de funciones nuevas, especialmente diseñadas para evitar muchas fórmulas kilométricas y varias tareas manuales del pasado.

Desventajas

1. No están disponibles en versiones antiguas de Excel, por lo que, si enviamos un archivo con matrices dinámicas a alguien que usa Excel 2019 o anterior es posible que tenga algunos inconvenientes.
2. No pueden usarse dentro de tablas estructuradas, solo sobre rangos. Si intentas poner una fórmula de matriz dinámica dentro de una tabla, Excel te obligará a elegir una estructura o la otra.
3. La rigidez de las matrices dinámicas puede no ser lo idóneo cuando el modelo requiera cierta flexibilidad, especialmente, si se desarrolla para usuarios con conocimientos básicos de Excel.

Nuevas funciones de matriz dinámica

Las funciones de matriz dinámica son las que pueden dar matrices dinámicas como resultado. Veamos algunas de ellas.

La intención aquí es solo ofrecer una primera toma de contacto con dichas funciones, describir su objetivo y la forma más básica de usarlas. No haremos un análisis exhaustivo de cada una, porque el artículo se haría eterno. Pero estamos pensando en hacer ese tipo de análisis a través de cursos u otro tipo de materiales, si encontramos suficiente interés. Dinos qué te parece en los comentarios.

Función UNICOS

Conserva solo la primera aparición de cada elemento de una lista y omite las restantes.

El único argumento obligatorio de esta función es la lista que queremos limpiar de duplicados. Por lo tanto, su sintaxis más básica será así:

Normalmente la lista será un rango de celdas. Pero pudiera ser también una matriz devuelta por otra función. Eso lo veremos luego.

Función ORDENAR

Ordena una serie de datos de forma ascendente o descendente.

Su sintaxis más básica sería esta:

El único argumento obligatorio que pide son los datos que queremos ordenar. Por ejemplo:

Si usamos como argumento un rango o matriz de varias columnas, la sintaxis básica anterior ordenará los datos por la primera columna de forma ascendente.

Para poder ordenar los datos por otra columna y por orden descendente, tenemos dos argumentos adicionales:

Por ejemplo, para ordenar el rango D3:E12 por la columna E (la número 2, en este caso) bastará con escribir:

Para ordenar el mismo rango por la columna 2, pero de forma descendente debemos usar los tres argumentos. El orden descendente se debe indicar con el valor -1:

Para indicar orden ascendente el tercer argumento debe ser un 1 o, simplemente, omitir el argumento.

Función FILTRAR

Filtra un conjunto de datos de acuerdo con uno o más criterios.

Tiene dos argumentos obligatorios:

Esta función admite más variantes que las anteriores. Sin embargo, para profundizar en ella, necesitaremos algunos conceptos que abordaremos en un artículo futuro.

Para ver un ejemplo básico de su uso, imaginemos que en el rango D3:D12 tenemos una lista de los meses y en E3:E12 las ventas mensuales. Si quisiéramos obtener solo las ventas mayores a 700 000, podríamos usar la siguiente fórmula:

El primer argumento indica las columnas que queremos ver en el resultado. El segundo, es la condición que determina el criterio de filtrado.

La forma de indicar la condición en esta función es similar a la de la función SI. Pero, en vez de hacer una comparación entre una celda y un valor, FILTRAR compara todas las celdas de un rango (o todos los elementos de una matriz) con un valor. En el ejemplo, lo que le pedimos es que, de todos los datos del rango D3:E12, solo muestre las filas que contienen valores mayores a 700 000 en el rango E3:E12.

Etapas de lanzamiento de las funciones de matriz dinámica y su nuevo motor de cálculo

ACONTECIMIENTO	MES/AÑO
Anuncio oficial y lanzamiento en Office Insider (canal para usuarios interesados en conocer funcionalidades en período de prueba).	Septiembre 2018
Lanzamiento en el Canal Actual de Office 365(1), para suscriptores estándar con una frecuencia de actualización, cuando menos, mensual.	Enero 2020
Anuncio de disponibilidad para todos los usuarios de Office 365(1) (incluyendo los canales de actualizaciones más tardías, generalmente usados por empresas).	Julio 2020
Lanzamiento en Excel 2021 (versión perpetua de un solo pago y sin actualizaciones posteriores).	Octubre 2021

(1) - Actualmente Office 365 se llama Microsoft 365 Copilot.

Resumen hasta el momento

Hasta ahora hemos visto los siguientes puntos:

1. Una fórmula matricial es aquella capaz de devolver una serie de resultados en lugar de uno solo.
2. Una fórmula de matriz dinámica es aquella que “desborda” los resultados obtenidos sobre todas las celdas adyacentes que necesite, de forma automática.
3. Cuando una fórmula de matriz dinámica no puede desbordarse, la celda devuelve el valor de error #¡DESBORDAMIENTO!
4. Tres de las funciones de matriz dinámica más usadas son UNICOS, ORDENAR y FILTRAR.
5.  Las matrices dinámicas surgieron en 2018, en la versión de Microsoft 365. En la primera versión perpetua que aparecieron fue en la 2021.

No te pierdas lo que sigue

Espero que este artículo te haya sido útil. Sin embargo, esto es solo la punta del iceberg. 

Hay mucho para decir cuando hablamos de matrices en Excel. Realmente mucho. Y, como habrás notado, no se trata de un conocimiento puramente teórico, de interés exclusivo para frikis de Excel.

Las matrices dinámicas cambian las reglas del juego: nos permiten lograr mucho más con menos esfuerzo. Lamentablemente, quienes no evolucionen junto con Excel pronto quedarán en desventaja.

Por eso, te invito a leer la segunda parte de esta serie: Matrices en Excel [Parte-2]: Matrices dinámicas en la práctica También te invito a suscribirte a nuestro boletín gratuito para mantenerte al tanto de los próximos temas que vayamos publicando:

---

La imagen superior toma elementos de un diseño de uso libre de Freepik.

Más contenido que puede interesarte:

Comparte este artículo...