Cómo usar fórmulas matriciales dinámicas en Microsoft Excel

Si trabajas con hojas de cálculo a diario, habrás notado que en cuanto empiezan a crecer en filas y columnas, las fórmulas clásicas de Excel se quedan algo cortas para hacer cálculos complejos de forma cómoda. Ahí es donde entran en juego las fórmulas matriciales dinámicas, una de esas novedades que, una vez las dominas, cuesta no usar en casi todos los libros.

En las versiones modernas de Excel (especialmente en Microsoft 365) disponemos de un nuevo motor de cálculo que permite que una sola fórmula genere múltiples resultados a la vez, ocupando varias celdas adyacentes sin que tengas que copiarla a mano. Gracias a este comportamiento de “desbordamiento” de resultados en bloque, ahora es muchísimo más fácil ordenar, filtrar, generar listas o hacer cálculos avanzados sin recurrir a trucos raros ni a combinaciones de teclas como Ctrl+Mayús+Entrar.

Qué es el desbordamiento en las fórmulas matriciales dinámicas

En el nuevo modelo de cálculo de Excel, una fórmula puede devolver no solo un valor, sino todo un conjunto organizado de resultados; a ese conjunto se le suele llamar matriz o rango de salida desbordado. Cuando esto ocurre, Excel coloca automáticamente esos valores en celdas cercanas, expandiendo la fórmula hacia abajo, hacia la derecha o en ambas direcciones, según el tamaño del resultado.

Por ejemplo, al escribir la fórmula =ORDENAR(D2:D11;1;-1) en una sola celda (por ejemplo F2), Excel genera una lista ordenada descendente basada en el rango D2:D11. El resultado ocupa 10 filas, pero tú solo introduces la fórmula en una celda; el resto de posiciones aparecen rellenadas por el propio Excel mediante este mecanismo de desbordamiento.

Las fórmulas que pueden cambiar el tamaño de su resultado en función de los datos de origen se conocen como fórmulas de matriz dinámica. Cuando estas fórmulas devuelven un rango de resultados que se extiende más allá de la celda donde se ha escrito la fórmula, se habla de que la fórmula se ha “desbordado” y el área que ocupa se denomina rango de desbordamiento.

En la práctica, esto significa que ahora muchas funciones, como ORDENAR, FILTRAR, MATRIZALEAT, SECUENCIA o UNICOS, están diseñadas para devolver matrices completas de datos listas para usar, de forma mucho más directa y legible que con las fórmulas matriciales antiguas.

Cómo se comporta el rango de desbordamiento en Excel

Cuando confirmas una fórmula matricial dinámica pulsando solo la tecla Intro, Excel analiza el resultado y ajusta automáticamente el tamaño del rango de salida para que quepan todos los valores que la fórmula devuelve. A continuación, coloca cada elemento de la matriz en su celda correspondiente dentro de ese rango desbordado.

Si escribes una de estas fórmulas sobre una lista o tabla de datos, puede ser bastante práctico convertir los datos de origen en una tabla de Excel con referencias estructuradas. Las tablas se adaptan automáticamente cuando añades o quitas filas, de modo que las fórmulas matriciales dinámicas que las utilizan se actualizan sin necesidad de que toques nada.

Conviene saber que las fórmulas con desbordamiento no funcionan dentro de las tablas propiamente dichas: dentro de una tabla de Excel no se admite el desbordamiento. En su lugar, debes colocar esas fórmulas en la cuadrícula normal (fuera de la tabla) y usar la tabla solo como fuente de datos. Las tablas están pensadas para almacenar registros, no para que dentro de ellas se expanda un rango de salida completo.

Siempre que hagas clic en cualquier celda perteneciente al rango de desbordamiento, Excel dibuja un recuadro resaltado alrededor de todo el conjunto de celdas afectadas. Gracias a este detalle es muy sencillo ver de un vistazo hasta dónde llega la fórmula y qué celdas dependen de ella; en cuanto seleccionas una celda fuera de ese rango, el borde desaparece.

Otro detalle importante es que, en un rango de desbordamiento, solo la primera celda (la de la esquina superior izquierda) contiene de verdad la fórmula. El resto de celdas muestran resultados pero, si seleccionas cualquiera de ellas, verás la fórmula atenuada en la barra de fórmulas y no podrás modificarla directamente. Siempre deberás editar la celda de origen; después de cambiarla y pulsar Intro, Excel recalculará todo el rango desbordado de una sola vez.

Errores de superposición y mensaje #DESBORDAMIENTO!

Para que una fórmula matricial dinámica pueda expandirse sin problemas, Excel necesita que el área de salida esté libre. Si hay datos, fórmulas u otros elementos ocupando alguna de las celdas donde deberían aparecer los resultados, se produce una superposición del rango de desbordamiento y Excel no puede completar la operación.

En ese caso, en lugar del resultado esperado, verás un mensaje de error #DESBORDAMIENTO! en la celda donde introdujiste la fórmula. Esta es la manera que tiene Excel de avisarte de que hay un bloqueo impidiendo que la matriz se coloque en todas las celdas que necesita para albergar sus resultados.

Si seleccionas la fórmula en cuestión, Excel te mostrará con un borde de puntos el rango donde pretendería desbordarse, incluyendo aquellas celdas que están bloqueando el proceso. Esta vista te permite localizar fácilmente las celdas problemáticas para poder vaciarlas o mover su contenido a otro lugar.

Una vez elimines los datos que impiden la expansión (por ejemplo, valores sueltos o fórmulas previas), al volver a calcular la hoja, Excel hará que la fórmula se desborde como estaba previsto. En muchas situaciones, basta con borrar un par de celdas para que el error #DESBORDAMIENTO! desaparezca al instante.

También puede darse el caso de que la propia fórmula esté mal planteada y devuelva una matriz demasiado grande para la zona disponible. En esos escenarios conviene revisar tanto la lógica de la fórmula como el espacio libre alrededor para asegurarse de que el rango de salida puede crecer lo que haga falta.

Diferencias entre fórmulas matriciales dinámicas y fórmulas CSE heredadas

Antes de la llegada de las matrices dinámicas, las fórmulas de matriz se introducían con la famosa combinación Ctrl+Mayús+Entrar (CSE). Esas fórmulas heredadas siguen siendo compatibles en Excel por motivos de retrocompatibilidad, pero el enfoque recomendado hoy en día es trabajar con el nuevo modelo dinámico, que resulta más sencillo y menos propenso a errores.

Una de las grandes ventajas de las fórmulas de matriz dinámica es que solo necesitas introducir la fórmula una vez en la celda superior izquierda. El resto de resultados aparecen automáticamente gracias al desbordamiento. En las fórmulas CSE antiguas, tenías que seleccionar todo el rango donde querías que aparecieran los resultados y luego confirmar la fórmula con Ctrl+Mayús+Entrar.

Las fórmulas dinámicas, además, son capaces de ajustar su tamaño cuando cambian los datos de origen. Si añades más filas a la fuente de datos, la matriz puede ampliarse; si eliminas información, puede contraerse, todo ello sin que tengas que editar manualmente el rango. Con las matrices CSE heredadas, si el área de devolución era demasiado pequeña, se truncaban los resultados; y si era excesiva, podías encontrarte con errores como #N/A.

Otra diferencia interesante es que muchas funciones clásicas, como ALEATORIO, FILA o COLUMNA, ahora se evalúan en un contexto de una sola celda (1×1). Si quieres generar varios resultados aleatorios o secuencias de números basadas en filas y columnas, se recomienda utilizar funciones como MATRIZALEAT o SECUENCIA, que están pensadas para devolver matrices completas y trabajan perfectamente con el nuevo motor dinámico.

Adicionalmente, en el pasado existía un fenómeno conocido como “interrupción CSE”, por el cual ciertas fórmulas matriciales heredadas que dependían unas de otras podían calcularse de forma independiente y devolver resultados inconsistentes o difíciles de depurar. Con las matrices dinámicas, este comportamiento desaparece: si hay referencias circulares, Excel las señalará como tales en lugar de romper la lógica de la fórmula.

Modificar una fórmula de matriz dinámica también es más cómodo. Solo tienes que editar la celda de origen y el resto se actualiza automáticamente; con las fórmulas CSE, era necesario editar todo el rango afectado de una sola vez, lo que en rangos grandes resultaba bastante engorroso. Además, cuando en una hoja existía un rango activo con una fórmula CSE, no se permitía insertar ni eliminar filas o columnas que interfiriesen en ese rango hasta que eliminaras o cambiaras primero la fórmula matricial heredada.

Uso de funciones clave con matrices dinámicas

Entre las funciones más potentes que aprovechan las matrices dinámicas destacan FILTRAR, ORDENAR, ORDENARPOR, UNICOS, MATRIZALEAT y SECUENCIA. Todas ellas devuelven rangos completos, por lo que encajan perfectamente con el comportamiento de desbordamiento y existen herramientas de ayuda como Excel Formula Bot que facilitan su uso.

Con la función FILTRAR, por ejemplo, puedes extraer solo las filas que cumplen ciertos criterios desde una tabla de datos, devolviendo un conjunto de resultados que se actualiza automáticamente cuando cambian los datos de origen. Esta función se combina muy bien con UNICOS, que permite obtener listas de valores sin duplicados a partir de columnas con muchos datos repetidos.

La función MATRIZALEAT genera un rango de números aleatorios en bloque, ideal para realizar simulaciones o pruebas; SECUENCIA, por su parte, devuelve matrices con series de números consecutivos, personalizando el incremento y el tamaño de la matriz según tus necesidades. Ambas se basan en el nuevo modelo matricial y están pensadas para rellenar de golpe grandes áreas de la hoja.

Por último, ORDENAR y ORDENARPOR facilitan mucho la creación de listados ordenados a partir de columnas o tablas existentes. En lugar de usar ordenaciones manuales o complejas combinaciones de funciones, ahora puedes escribir una sola fórmula que devuelve los datos ya ordenados y se adapta automáticamente a los cambios en los valores originales.

Todas estas funciones se combinan entre sí y, gracias al desbordamiento, permiten construir soluciones muy avanzadas sin necesidad de macros ni de fórmulas matriciales heredadas difíciles de mantener, y, para automatizar tus libros, Office Scripts en Excel Web puede ser de gran ayuda.

Diferencias de cálculo entre matrices dinámicas y matrices heredadas

Si todavía trabajas con libros antiguos que usan fórmulas matriciales CSE, es importante que tengas en cuenta que al convertirlas a su equivalente dinámico puede cambiar ligeramente el comportamiento en algunos casos. Aunque en la mayoría de escenarios la conversión es directa, conviene revisar el resultado.

La forma habitual de pasar de una matriz heredada a una dinámica consiste en localizar la primera celda del rango matricial, copiar el texto de la fórmula, eliminar todo el rango antiguo y, a continuación, volver a escribir la fórmula solo en la celda superior izquierda usando el nuevo enfoque. Excel se encargará de desbordar el resultado automáticamente.

Durante la transición, fíjate especialmente en las funciones que en el pasado se apoyaban en el comportamiento de intersección implícita o en evaluaciones especiales dentro de fórmulas CSE. Excel ahora dispone del operador de intersección implícita (@), que sirve para replicar en ciertos casos el comportamiento antiguo, pero la recomendación general sigue siendo reescribir las fórmulas de forma explícita aprovechando las nuevas funciones.

Excel también ofrece un soporte limitado cuando una matriz dinámica hace referencia a datos que están en otro libro. Solo se garantiza el funcionamiento correcto cuando ambos archivos se encuentran abiertos a la vez. Si cierras el libro de origen, las fórmulas de matriz dinámica vinculadas pueden devolver el error #REF! al intentar actualizarse, por lo que es importante tenerlo en cuenta en modelos complejos con varias referencias externas; en esos casos consulta cómo guardar y compartir libros de trabajo para evitar problemas.

Aunque las fórmulas CSE seguirán existiendo para compatibilidad hacia atrás, la propia Microsoft recomienda dejar de crearlas en nuevos proyectos y apostar por las matrices dinámicas. La legibilidad, facilidad de mantenimiento y robustez de las nuevas fórmulas las convierten en la opción preferente de cara al futuro.

Rango de desbordamiento y edición práctica en la hoja

Cuando una fórmula se desborda, el área que ocupa se conoce como rango de desbordamiento. En este rango, como ya hemos comentado, solo la primera celda contiene la fórmula real. El resto de celdas muestran resultados, pero están “controladas” desde la celda de origen, lo que hace que toda la matriz se comporte como una unidad.

Por ejemplo, si escribes la función =MAYUSC(E7:E19) en una sola celda, verás cómo Excel devuelve, en varias filas, el texto de ese rango pasado a mayúsculas. Si seleccionas cualquiera de las celdas del rango de salida, verás el resultado, pero al mirar la barra de fórmulas notarás que el contenido aparece atenuado, indicando que no es editable directamente. Cualquier modificación debe hacerse en la celda donde escribiste la fórmula por primera vez.

Este comportamiento tiene una ventaja clara: se evita que, por despiste, modifiques solo una parte del rango y dejes el resto sin actualizar, lo que suele generar errores difíciles de localizar. Si necesitas cambiar la fórmula, la editas una sola vez en la celda de origen, pulsas Intro y Excel recalcula de forma coherente todo el bloque.

En el día a día, esto también influye a la hora de borrar o mover datos. Si intentas eliminar una sola celda dentro del rango de desbordamiento, Excel te avisará de que estás afectando a una matriz dinámica. Para evitar problemas, lo habitual es borrar o cortar directamente la celda de origen, que arrastra el resto del rango, o bien ajustar la fórmula para que devuelva una matriz de distinto tamaño.

Al combinar rangos de desbordamiento con otras fórmulas, hay que tener presente que esas celdas se recalculan de forma conjunta. Cualquier referencia a una matriz dinámica debe hacerse con cuidado, aprovechando las ventajas del nuevo motor sin provocar referencias circulares innecesarias ni conflictos con otras partes de la hoja.

Ejemplos de fórmulas de matriz avanzadas con datos reales

Muchas de las fórmulas matriciales clásicas siguen teniendo sentido, especialmente cuando trabajas con rangos grandes y necesitas operaciones condicionales complejas. Veamos varios ejemplos típicos que te pueden resultar muy útiles en informes y modelos.

Imagina que tienes un rango con nombre Datos que incluye algunos valores de error como #N/A. Si aplicas la función SUMA directamente sobre ese rango, obtendrás un error. Para evitarlo, puedes utilizar una matriz que ignore los errores con una fórmula del estilo =SUMA(SI(ESERROR(Datos);»»;Datos)), que crea internamente una matriz donde los errores se sustituyen por cadenas vacías antes de sumar.

Siguiendo esa misma idea, también puedes contar cuántos errores hay en un rango. Una fórmula como =SUMA(SI(ESERROR(Datos);1;0)) genera una matriz con 1 cuando hay error y 0 cuando no lo hay. La suma total te indica el número de celdas erróneas. Incluso puedes simplificarla a =SUMA(SI(ESERROR(Datos);1)) e ir un paso más allá hasta =SUMA(SI(ESERROR(Datos)*1)), aprovechando que VERDADERO*1 es 1 y FALSO*1 es 0.

Otro escenario habitual es sumar valores en función de condiciones. Por ejemplo, podrías tener un rango llamado Ventas y querer sumar solo los valores positivos con una fórmula como =SUMA(SI(Ventas>0;Ventas)). Aquí, la función SI genera una matriz con los valores que cumplen la condición y con valores falsos para el resto, que SUMA ignora en la práctica.

Si necesitas aplicar más de una condición a la vez, puedes multiplicar expresiones lógicas para emular un “Y” (AND) y sumar después. Un caso típico sería =SUMA((Ventas>0)*(Ventas<=5)*(Ventas)), que calcula la suma de las ventas mayores que 0 y menores o iguales que 5. Eso sí, esta aproximación requiere que el rango no contenga celdas de texto, o de lo contrario podrías encontrarte con errores.

Para emular un “O” (OR), puedes usar sumas de expresiones lógicas: por ejemplo, =SUMA(SI((Ventas<5)+(Ventas>15);Ventas)), donde se suman los valores menores que 5 o mayores que 15. De esta forma, puedes llevar a cabo operaciones avanzadas sin utilizar directamente las funciones Y y O, que devuelven un único valor lógico y no una matriz completa de resultados.

Cálculos estadísticos y comparaciones con fórmulas matriciales

Las fórmulas de matriz también resultan muy útiles para calcular medias o realizar comparaciones entre rangos completos. Un ejemplo clásico consiste en calcular la media de un conjunto de datos excluyendo los ceros. Si tu rango se llama Ventas, la fórmula =PROMEDIO(SI(Ventas<>0;Ventas)) crea una matriz con solo los valores distintos de cero y los pasa a PROMEDIO, omitiendo así los registros que no aportan información.

Otro caso interesante es comparar dos rangos con el mismo tamaño, por ejemplo MisDatos y TusDatos. Si quieres saber cuántas celdas difieren entre ambos, podrías usar =SUMA(SI(MisDatos=TusDatos;0;1)), que genera una matriz de 0 cuando los valores coinciden y 1 cuando son distintos, y luego suma el resultado. Si los dos rangos son idénticos, la fórmula devolverá 0.

Esta comparación se puede simplificar aún más con =SUMA(1*(MisDatos<>TusDatos)), haciendo que la comparación lógica (MisDatos<>TusDatos) genere VERDADERO o FALSO, que luego se transforman en 1 y 0 mediante la multiplicación por 1. De nuevo, la clave está en aprovechar el comportamiento de las expresiones booleanas dentro de las matrices.

También puedes usar las fórmulas matriciales para localizar el valor máximo de un rango y obtener su posición. Si dispones de un rango con nombre Datos, una opción es =MIN(SI(Datos=MAX(Datos);FILA(Datos);»»)). Esta fórmula crea una matriz en la que solo las celdas cuyo valor es igual al máximo contienen su número de fila; el resto se convierte en cadena vacía. Luego MIN devuelve el número de fila más pequeño de entre esos candidatos, es decir, la primera aparición del valor máximo.

Si en lugar de la fila quieres la referencia completa de la celda, puedes combinar DIRECCION con la lógica anterior usando algo como =DIRECCION(MIN(SI(Datos=MAX(Datos);FILA(Datos);»»));COLUMNA(Datos)), de manera que obtengas una referencia como “$B$7” que identifica exactamente dónde está el valor máximo en el rango.

Ejemplo práctico: ventas por producto con fórmulas matriciales

Para entender mejor el potencial de las fórmulas de matriz, imagina una pequeña tabla de ventas de vehículos con columnas para el vendedor, tipo de vehículo, unidades vendidas, precio unitario y ventas totales. Supón que las columnas C y D contienen, respectivamente, el número vendido y el precio unitario.

Si copias esta tabla en Excel, puedes seleccionar el rango E2:E11 e introducir una fórmula como =C2:C11*D2:D11. En las versiones antiguas, tendrías que confirmar la fórmula con Ctrl+Mayús+Entrar para que se convirtiera en una matriz clásica que devolviera, en bloque, la multiplicación fila a fila. Excel entonces calcularía las ventas totales para cada línea multiplicando unidades por precio unitario.

El detalle clave es que siempre debes seleccionar todas las celdas que van a contener los resultados antes de escribir la fórmula matricial. Si no lo haces, es posible que solo se calcule parte de los valores o que tengas que repetir el proceso varias veces, lo cual es poco eficiente.

En este contexto, también es frecuente utilizar una fórmula matricial de una sola celda para obtener el total general de todas las ventas. Por ejemplo, podrías situarte en la celda B13 e introducir =SUMA(C2:C11*D2:D11). Al confirmar la fórmula (en el modelo clásico con Ctrl+Mayús+Entrar), Excel multiplica cada par de valores en C y D y suma todos los productos para devolver un único valor agregado.

Aunque estos ejemplos se apoyan en el comportamiento heredado de las matrices CSE, las ideas de fondo son las mismas que se usan con las matrices dinámicas actuales: realizar operaciones sobre rangos completos a la vez y devolver resultados múltiples o agregados sin necesidad de fórmulas auxiliares intermedias en cada fila.

Hoy en día, muchas de estas tareas pueden resolverse combinando funciones dinámicas y desbordamiento, lo que hace que las hojas sean más limpias, más fáciles de leer y más sencillas de mantener, especialmente cuando el volumen de datos crece o cuando varios usuarios trabajan sobre el mismo libro.

Dominar el uso de las fórmulas matriciales dinámicas en Excel cambia por completo la manera de construir hojas de cálculo: al entender cómo funciona el desbordamiento, cómo se comportan los rangos de salida, en qué se diferencian de las antiguas fórmulas CSE y cómo aplicar ejemplos prácticos para ignorar errores, realizar sumas condicionales o comparar rangos, acabas trabajando con modelos mucho más flexibles, automatizados y fiables, ganando tiempo en cada actualización y reduciendo al mínimo los errores manuales.