Si usas Excel a diario, seguro que alguna vez te has peleado con fórmulas infinitas, referencias cruzadas y listas que se quedan cortas en cuanto añades una fila nueva. Con la llegada de las matrices dinámicas y las fórmulas de matriz moderna, todo ese lío se simplifica: una única fórmula puede generar muchos resultados, ajustarse sola al tamaño de los datos y recalcularse sin que tengas que tocar nada más.
Las fórmulas con matrices en Excel y las funciones dinámicas han cambiado por completo la manera de analizar datos: ahora puedes filtrar, ordenar, crear secuencias, detectar valores únicos o generar números aleatorios en bloques enteros de celdas, sin macros ni combinaciones raras de teclas. Vamos a ver con calma qué son, en qué se diferencian las matrices clásicas de las dinámicas, qué funciones nuevas tienes a tu disposición y cómo exprimirlas de verdad en tu día a día.
¿Qué es una fórmulas con matrices en Excel (modelo clásico)?
En su versión tradicional, una fórmula de matriz es una fórmula especial que trabaja con conjuntos de valores en vez de con una sola celda. En lugar de operar sobre A1 o B2 de forma aislada, puede procesar rangos completos y devolver un único resultado o varios, según cómo la hayas planteado.
Estas fórmulas se caracterizan porque operan con matrices de datos internas: toman múltiples valores de entrada, realizan cálculos en bloque (sumas, comparaciones, condiciones, etc.) y devuelven una salida que también puede ser un conjunto de valores. Tradicionalmente, Excel mostraba estas fórmulas con llaves { } en la barra de fórmulas, indicando que se trataba de una fórmula de matriz.
Para introducir una fórmula de este tipo en las versiones antiguas, no bastaba con pulsar Intro. Era necesario confirmar la fórmula con la combinación Ctrl + Mayús + Entrar. Al hacerlo, Excel entendía que debía tratar la expresión como una matriz y rodeaba la fórmula con llaves automáticamente. Si escribías tú mismo las llaves, no funcionaba, tenían que aparecer al usar esa combinación de teclas.
Las fórmulas de matriz clásicas han sido siempre muy útiles para cálculos complejos en múltiples celdas: operaciones matemáticas avanzadas, filtros condicionales sin funciones «especiales», comparaciones masivas entre rangos o resúmenes de información sin necesidad de columnas auxiliares. Eso sí, también tienen su cara B: son más difíciles de entender, de depurar y, si se abusa de ellas en libros grandes, pueden ralentizar el rendimiento de la hoja.
Un ejemplo típico de fórmula de matriz tradicional sería {=SUMA(A1:A5*B1:B5)}, donde se multiplican elemento a elemento los rangos A1:A5 y B1:B5, y luego se suman todos los productos. Otro ejemplo clásico sería {=MAX(A1:A5+B1:B5)}, en el que se suman las dos columnas y se toma el valor máximo del resultado.
¿Qué es una fórmulas con matrices dinámicas en Excel?
Con las versiones más recientes (Excel para Microsoft 365 y Excel 2021), Microsoft introdujo las fórmulas de matriz dinámica, que son la evolución natural de las matrices clásicas. La gran diferencia es que estas fórmulas admiten que el resultado sea un conjunto de celdas y, en lugar de forzarte a seleccionar el rango de salida y usar Ctrl + Mayús + Entrar, se desbordan automáticamente hacia las celdas contiguas.
Cuando hablamos de matriz dinámica nos referimos a cualquier fórmula que puede devolver un rango de tamaño variable y que Excel es capaz de volcar (o derramar) sobre la hoja sin que tengas que hacer nada más. Estas fórmulas se escriben en una sola celda y, según el cálculo, llenan todas las celdas necesarias con el resultado completo.
Las matrices dinámicas se apoyan en nuevas funciones diseñadas para trabajar con este comportamiento de desbordamiento: FILTRAR, ORDENAR, UNICOS, SECUENCIA, ORDENARPOR y MATRIZALEAT o RANDARRAY, entre otras. Todas ellas pueden devolver varios valores de una tacada y están pensadas justo para manejar matrices de forma natural, sin combinaciones de teclas especiales.
Una diferencia clave frente a las fórmulas de matriz de toda la vida es que ya no necesitas Ctrl + Mayús + Entrar. Escribes la fórmula normalmente, pulsas Intro y Excel se encarga de todo: calcula el tamaño de la salida y la derrama en tantas filas y columnas como hagan falta, marcando visualmente el rango de desbordamiento cuando seleccionas la celda original.
Otra ventaja importante es que estas fórmulas ajustan su tamaño automáticamente: si cambian los datos de origen (añades o quitas filas, modificas criterios, etc.), el rango derramado crece o se reduce solo, sin que tengas que arrastrar fórmulas o volver a definir rangos.
¿Qué significa el desbordamiento (spill) de una fórmula?
El desbordamiento es el comportamiento por el cual una fórmula genera varios resultados y Excel los coloca en celdas vecinas. Imagina que en la celda F2 escribes una fórmula como =ORDENAR(D2:D11,1,-1) para ordenar un listado de 10 elementos en orden descendente: el resultado no se queda solo en F2, sino que se derrama desde F2 hasta F11, ocupando todo el rango que necesita.
En este contexto, a las fórmulas que pueden devolver un número cambiante de elementos se las conoce como fórmulas de matriz dinámica. Y cuando el resultado ya se está expandiendo correctamente hacia varias celdas, solemos hablar de fórmula de matriz desbordada. La fórmula en sí solo existe en la celda superior izquierda del rango, pero su efecto se ve en todo el bloque de salida.
Cuando confirmas una fórmula de este tipo, Excel calcula el tamaño del rango de salida y reparte los resultados por todas las celdas que entren en ese área. Si seleccionas cualquier celda del rango de desbordamiento, la aplicación resalta todo el bloque con un borde especial, para que veas claramente que esos datos dependen de una única fórmula matriz. El borde desaparece al seleccionar otra celda ajena al rango.
Hay un detalle importante: solo puedes editar la primera celda del rango derramado. Si seleccionas otra celda dentro del desbordamiento, verás la fórmula en la barra de fórmulas, pero atenuada, como texto «fantasma», y no podrás modificarla desde ahí. Para cambiarla, tendrás que ir a la celda superior izquierda, editar la fórmula y pulsar Intro; Excel actualizará automáticamente todas las celdas del desbordamiento.
En cuanto a las tablas de Excel, debes tener en cuenta que no admiten fórmulas de matriz desbordadas dentro de la propia tabla. Las fórmulas dinámicas deben colocarse en la cuadrícula normal, fuera de las tablas estructuradas, ya que estas están pensadas para gestionar datos por filas y columnas, no para que una fórmula se expanda libremente.
Error #DESBORDAMIENTO! (#SPILL!) y problemas habituales
Una limitación clave de las matrices dinámicas es que necesitan espacio libre para volcar resultados. Si hay cualquier valor, fórmula o formato que interfiera en alguna de las celdas donde Excel debería escribir el rango derramado, la fórmula dará un error del tipo #DESBORDAMIENTO! (#SPILL! en inglés). Este error indica que existe un bloqueo en el rango de salida.
Visualmente, Excel suele mostrar el área donde debería expandirse la fórmula marcada con un borde de puntos sobre las celdas que están ocupadas. Es una forma rápida de ver exactamente qué valores están impidiendo el desbordamiento. Para solucionarlo, basta con borrar o mover los datos que estorban; en cuanto el rango queda libre, la fórmula se derrama correctamente.
Otro aspecto importante es que, si haces referencia desde otra fórmula a una matriz dinámica, conviene usar el operador de rango desbordado. Por ejemplo, si tu fórmula original está en A2 y se derrama hacia abajo, deberías escribir A2# al referenciarla, de forma que Excel entienda que debe tomar todo el rango generado, no solo la celda A2 aislada.
También es importante recordar que las matrices dinámicas entre libros tienen soporte limitado: Excel solo las maneja bien si ambos archivos están abiertos. Si el libro de origen se cierra, las fórmulas que dependen de esa matriz dinámica pueden devolver un error #REF! al recalcularse, ya que no tienen acceso al rango completo.
Diferencias entre fórmulas de matriz clásicas y dinámicas
Excel mantiene las fórmulas de matriz tradicionales (las de Ctrl + Mayús + Entrar) por motivos de compatibilidad con libros antiguos, pero la filosofía actual pasa por utilizar las nuevas matrices dinámicas siempre que sea posible. Las diferencias entre ambos modelos son bastante claras.
Por un lado, las fórmulas de matriz clásicas se introducen seleccionando de antemano el rango de salida y confirmando con Ctrl + Mayús + Entrar. No se desbordan de forma automática, no cambian el tamaño de salida por sí mismas y, en muchos casos, su resultado está limitado a un único valor, aunque internamente trabajen con varias celdas. Si la zona de salida es demasiado pequeña, pueden truncar resultados o devolver errores como #N/A.
En el otro extremo, las fórmulas de matriz dinámica se escriben en una sola celda, se confirman con Intro normal y Excel derrama la salida hacia tantas celdas como necesite. Si cambian los datos de entrada, el rango de salida se agranda o reduce sin que tengas que editar el tamaño. Además, cualquier fórmula nueva que devuelva varios resultados se desborda automáticamente, sin pasos extra.
Un problema histórico de las fórmulas CSE era la llamada «ruptura CSE»: cuando varias celdas de una misma matriz dependían entre sí, Excel intentaba calcularlas de forma individual, y en algunos escenarios podían generarse resultados inconsistentes. Las matrices dinámicas no se comportan así; cuando existe una referencia circular o un problema similar, Excel lo avisa como tal para que puedas corregirlo, en lugar de intentar «salvar» el cálculo.
Además, las nuevas fórmulas de matriz dinámica se modifican de manera más sencilla: solo tienes que tocar la celda de origen. En cambio, con las CSE clásicas, si querías cambiar algo en el rango de matriz, tenías que seleccionar todo el bloque y editar el conjunto. También se prohíbe insertar o eliminar filas y columnas que atraviesen un rango activo de fórmula CSE; primero hay que quitar esa fórmula para poder modificar la estructura.
Intersección implícita y operador @ en el nuevo Excel
Antes de que existieran las matrices dinámicas, cuando una fórmula podía devolver varias celdas pero se introducía en una única celda, Excel aplicaba silenciosamente una «intersección implícita». Es decir, reducía la matriz de salida a un solo valor, normalmente el que coincidía con la fila o columna de la celda donde estaba la fórmula, sin que el usuario fuera muy consciente de ello.
Con la llegada de las matrices dinámicas, Excel hace más explícito este comportamiento mediante el operador @. Cuando una función que en versiones anteriores devolvía una matriz se abre en un Excel moderno, puede aparecer automáticamente precedida de @ para indicar que se está tomando solo la intersección implícita y no toda la matriz. Esto ayuda a entender dónde podría producirse un desbordamiento si se eliminara ese operador.
Del mismo modo, si creas una fórmula con matrices dinámicas en la versión actual y luego la abres en un Excel antiguo, es posible que se represente como fórmula de matriz heredada si no se usa el operador @. La idea es que los libros sigan siendo compatibles entre versiones, pero que en la nueva experiencia quede claro qué fórmulas realmente trabajan con rangos completos y cuáles se han «reducido» a una intersección puntual.
Funciones clave de matrices dinámicas: FILTRAR, RANDARRAY, SECUENCIA, ORDENAR y ORDENARPOR
El auténtico salto de calidad viene de la mano de las funciones nuevas que trabajan con matrices dinámicas de forma nativa. Cada una resuelve problemas que antes exigían fórmulas muy enrevesadas o macros, y ahora se solucionan con una instrucción bastante legible.
FILTRAR: extraer solo lo que necesitas
La función FILTRAR (FILTER en inglés) permite obtener un subconjunto de datos que cumpla ciertos criterios sin necesidad de aplicar filtros manuales, usar tablas dinámicas o programar nada. Puedes indicarle que muestre solo los clientes con determinada puntuación, las ventas de un mes concreto o los empleados por encima de una edad, y el resultado se derrama en un rango que se actualiza solo al cambiar los datos originales.
Una forma típica de usarla sería algo como =FILTRAR(A2:C15,(A2:A15=F4)*(C2:C15=G4),»»), donde se combinan condiciones en varias columnas. El resultado es una matriz dinámica de filas que cumplen los criterios establecidos. Además, puedes combinarla con otras funciones, por ejemplo ORDENARPOR, para que el filtrado y el ordenado sucedan dentro de una única fórmula.
RANDARRAY / MATRIZALEAT: números aleatorios a granel
Con RANDARRAY (MATRIZALEAT en español), puedes generar de golpe una matriz de números aleatorios de tantas filas y columnas como necesites. Ya no hace falta copiar fórmulas con ALEATORIO() o ALEATORIO.ENTRE() por media hoja: una sola instrucción crea todo el bloque.
Esta función permite elegir si quieres números enteros o decimales, definir límites mínimo y máximo y especificar dimensiones exactas (número de filas y columnas). Es una herramienta muy útil para simulaciones, modelos financieros, ejercicios de muestreo o incluso para generar identificadores temporales y datos de prueba en cuestión de segundos.
SECUENCIA: listas y calendarios automáticos
La función SECUENCIA genera series de números (o fechas, si las combinas con otras funciones) que se expanden y se contraen de forma dinámica. Es ideal para numerar registros, construir calendarios, planificar semanas laborales o crear listas correlativas sin arrastrar el relleno manualmente.
Con SECUENCIA puedes indicar cuántas filas y columnas quieres, el valor inicial y el incremento. Por ejemplo, puedes crear una serie de fechas de lunes a viernes integrando SECUENCIA con funciones de fecha, de manera que la lista se genere de una sola vez y se adapte si añades o quitas filas asociadas a tus datos.
ORDENAR y ORDENARPOR: datos siempre en orden
La función ORDENAR (SORT) te permite reordenar un rango por una o varias columnas, en sentido ascendente o descendente, sin modificar la tabla original. El resultado se vuelca en un rango aparte que se actualizará en cuanto cambien los datos de origen, por lo que puedes tener siempre un listado ordenado «en espejo».
Por su parte, ORDENARPOR (SORTBY) va un paso más allá: permite ordenar un conjunto de datos según los valores de otra columna o de otro rango. Por ejemplo, podrías ordenar una lista de productos por las ventas registradas en otra tabla, o un listado de alumnos por la nota obtenida en un campo distinto, todo ello volcado en una matriz dinámica que se mantiene al día.
Combinando ORDENAR u ORDENARPOR con FILTRAR y SECUENCIA, puedes lograr efectos de nivel avanzado: tablas que se autoordenan y auto-filtran en función de criterios cambiantes, sin necesidad de macros ni de actualizar nada a mano.
Ejemplos prácticos de fórmulas de matriz avanzadas (modelo clásico)
Aunque las matrices dinámicas son la apuesta actual, muchas técnicas clásicas siguen siendo útiles, y se entienden mejor viendo ejemplos concretos de fórmulas de matriz avanzadas que se han usado durante años.
Un caso típico es sumar un rango que contiene errores. La función SUMA, por sí sola, falla si el rango incluye un valor como #N/A. Para evitarlo, se puede usar una fórmula del estilo =SUMA(SI(ESERROR(Datos),»»,Datos)), que crea internamente una matriz en la que se sustituyen los errores por cadenas vacías y se dejan intactos los valores válidos. SUMA trabaja entonces sobre esa matriz filtrada y obtiene el total sin tropezar con los errores.
Otro ejemplo práctico es contar cuántos errores hay en un rango. Una fórmula clásica sería =SUMA(SI(ESERROR(Datos),1,0)), que genera una matriz de 1 y 0 (uno por cada celda con error, cero por cada celda correcta) y luego suma esos valores. Se puede simplificar omitiendo el tercer argumento de SI: =SUMA(SI(ESERROR(Datos),1)), ya que cuando la condición es falsa, SI devuelve FALSO, que luego se puede evaluar como 0. Incluso existe una versión más compacta: =SUMA(SI(ESERROR(Datos)*1)), aprovechando que VERDADERO*1 es 1 y FALSO*1 es 0.
Las fórmulas de matriz también son muy potentes para sumar valores según condiciones. Por ejemplo, =SUMA(SI(Ventas>0;Ventas)) suma solo los valores positivos del rango Ventas, ignorando automáticamente los que no cumplen la condición. Para combinar varias condiciones del tipo «y», se puede usar algo como =SUMA((Ventas>0)*(Ventas<=5)*(Ventas)), que suma únicamente los valores mayores que 0 y menores o iguales que 5, siempre que la celda tenga datos numéricos.
Si lo que se necesita es una lógica de tipo «o», se puede recurrir a expresiones como =SUMA(SI((Ventas<5)+(Ventas>15);Ventas)), que suman los valores menores que 5 y mayores que 15 en un solo cálculo. Aunque Excel tiene funciones Y y O, en las fórmulas de matriz suelen ser más útiles estas combinaciones de multiplicaciones y sumas de condiciones, porque generan matrices de resultados en lugar de un único VERDADERO o FALSO.
Otro uso muy extendido es calcular una media excluyendo ceros. Con =PROMEDIO(SI(Ventas<>0;Ventas)) se crea una matriz solo con los valores distintos de 0 y se pasa a PROMEDIO, que hace el cálculo sin que las celdas con cero distorsionen el resultado.
Las fórmulas de matriz permiten también comparar rangos completos. Por ejemplo, contar cuántas diferencias hay entre dos áreas del mismo tamaño, MisDatos y TusDatos, con =SUMA(SI(MisDatos=TusDatos,0,1)). Esta fórmula genera una matriz de 0 y 1 (cero cuando coinciden, uno cuando hay diferencia) y devuelve la suma de esas diferencias. Se puede simplificar escribiendo =SUMA(1*(MisDatos<>TusDatos)), que se apoya de nuevo en la equivalencia VERDADERO*1=1 y FALSO*1=0.
También es posible localizar la posición del valor máximo en un rango. Una fórmula típica sería =MIN(SI(Datos=MAX(Datos),FILA(Datos),»»)), que construye una matriz con los números de fila donde se encuentra el valor máximo y cadenas vacías en el resto. MIN devuelve la fila más pequeña, es decir, la primera aparición del máximo. Si además quieres la dirección de la celda, puedes recurrir a =DIRECCION(MIN(SI(Datos=MAX(Datos),FILA(Datos),»»)),COLUMNA(Datos)), que combina esa fila con la columna del rango Datos.
Rango de desbordamiento: cómo se ve en la hoja
Cuando introduces una función que admite matrices dinámicas, como =MAYUSC(E7:E19), verás que el resultado se derrama automáticamente por el rango correspondiente. La fórmula solo se encuentra realmente en la primera celda donde la escribiste, pero todas las celdas del rango de salida muestran el resultado transformado (en este caso, el texto en mayúsculas).
Si seleccionas cualquier celda del rango de desbordamiento, comprobarás que la fórmula aparece atenuada en la barra de fórmulas, indicando que en esa celda no hay una fórmula propia, sino un resultado calculado por la matriz dinámica original. Para cambiar algo, tendrás que editar siempre la celda de origen; el resto del rango se actualizará de forma automática cuando confirmes el cambio.
Buenas prácticas y compatibilidad entre versiones
En los libros modernos, lo recomendable es priorizar siempre que puedas las fórmulas de matriz dinámica. Las fórmulas CSE heredadas siguen funcionando para no romper archivos antiguos, pero ya no se aconseja crearlas de cero, precisamente porque son más difíciles de mantener y tienen más restricciones a la hora de editar la estructura de la hoja.
Si quieres convertir una formula CSE antigua en una matriz dinámica, puedes localizar la primera celda del rango de matriz, copiar el texto de la fórmula, borrar todo el bloque completo y, a continuación, pegar la fórmula en la celda superior izquierda y confirmarla con Intro normal. Antes de hacerlo en libros críticos, conviene revisar las posibles diferencias de cálculo y asegurarse de que no dependes de comportamientos antiguos como la intersección implícita silenciosa.
Cuando intercambias archivos entre versiones, ten en cuenta que las fórmulas creadas en Excel con matrices dinámicas pueden aparecer como fórmulas de matriz heredadas si se abren en una edición anterior, salvo que se haya usado el operador @ para limitar la salida. Del mismo modo, muchas funciones antiguas que devolvían matrices ahora se muestran con el operador @ en las versiones nuevas, dejando claro que se está tomando solo una intersección, y no todo el rango derramado.
Por último, si trabajas con automatización externa, bibliotecas de terceros como Aspose.Cells permiten configurar y recalcular fórmulas de matriz dinámica por código, usando métodos específicos para establecer la fórmula de matriz en una celda y refrescar sus resultados antes de guardar el libro. Esto es relevante cuando generas informes de manera automática y quieres aprovechar el comportamiento de desbordamiento sin utilizar Excel de forma interactiva.
Dominar las fórmulas con matrices y, sobre todo, las funciones de matrices dinámicas de Excel convierte a la hoja de cálculo en un sistema de análisis mucho más inteligente: las fórmulas se adaptan al tamaño de tus datos, los resultados se ordenan y filtran solos, los errores se gestionan de forma elegante y tus informes dejan de ser estáticos para responder automáticamente a cada cambio que hagas en el origen. Comparte esta tutorial sobre fórmulas con matrices en Excel y más usuarios sabrán hacerlo.