Matrices dinámicas en Excel: FILTRAR, ORDENAR, UNICOS y SECUENCIA

Las matrices dinámicas de Excel permiten que una sola fórmula devuelva muchos resultados que se "desbordan" automáticamente en las celdas vecinas. Con cuatro funciones—FILTRAR, ORDENAR, UNICOS y SECUENCIA—puedes crear listas, rankings y resúmenes que se actualizan solos cuando cambian tus datos, sin arrastrar fórmulas ni depender de una tabla dinámica.

Qué son las matrices dinámicas (y por qué cambiaron Excel)

Antes, una fórmula vivía en una celda y devolvía un solo valor. Si querías una lista de todos los clientes de la región Este, necesitabas columnas auxiliares, filtros avanzados o las viejas fórmulas matriciales con Ctrl+Shift+Enter. Hoy escribes una fórmula en una celda y Excel llena todas las filas y columnas que el resultado necesite. Esa zona de salida se llama rango de desbordamiento, y Excel la marca con un borde azul delgado cuando seleccionas cualquier celda dentro de ella.

Las matrices dinámicas están disponibles en Excel para Microsoft 365, Excel para la web y las versiones perpetuas recientes (Excel 2021 en adelante). Algunas funciones más nuevas como TOMAR (TAKE), EXCLUIR (DROP) o APILARV (VSTACK) llegaron después, así que si ves #¿NOMBRE?, probablemente tu versión todavía no la incluye. Revisa la página de soporte de Microsoft de cada función para confirmar.

Una nota práctica: en este artículo las fórmulas usan coma como separador de argumentos. Si tu Excel usa la coma como separador decimal (común en varios países), escribe punto y coma (;) en su lugar.

Tres ideas clave

• Rango de desbordamiento: el bloque de celdas que llena la fórmula. Solo la celda superior izquierda contiene la fórmula; las demás muestran valores "fantasma" que no puedes editar por separado.
• El operador #: para referirte a todo el rango desbordado, agrega # después de la celda ancla. =SUMA(F2#) suma todo lo que desborda desde F2, por más que crezca la lista.
• Error #¡DESBORDAMIENTO! (#SPILL!): aparece cuando algo bloquea la salida—normalmente un valor estorbando, celdas combinadas o una fórmula dentro de una tabla de Excel (las tablas no permiten desbordar).

Las cuatro funciones principales

Imagina una tabla de ventas en A1:E500 con las columnas Fecha, Vendedor, Región, Producto e Importe. Así funciona cada función con esos datos.

FILTRAR: extrae las filas que coinciden

Para listar todas las ventas de la región Este:

=FILTRAR(A2:E500, C2:C500="Este", "Sin ventas")

El tercer argumento es lo que Excel muestra si no hay coincidencias; sin él verías un error #¡CALC!. Para varias condiciones, multiplícalas para lógica Y, y súmalas para lógica O:

=FILTRAR(A2:E500, (C2:C500="Este") * (E2:E500>1000), "Nada")
=FILTRAR(A2:E500, (C2:C500="Este") + (C2:C500="Oeste"), "Nada")

Cada comparación devuelve VERDADERO/FALSO; al multiplicar se convierten en 1 y 0, así que una fila se queda solo cuando todas las condiciones valen 1.

ORDENAR y ORDENARPOR: arma rankings

ORDENAR acomoda un rango según una de sus propias columnas. Para ordenar toda la tabla por Importe (columna 5) de mayor a menor:

=ORDENAR(A2:E500, 5, -1)

ORDENARPOR es más flexible porque la columna de orden no tiene que salir en el resultado. Para mostrar solo los nombres de los vendedores, ordenados por importe:

=ORDENARPOR(B2:B500, E2:E500, -1)

UNICOS: listas limpias sin duplicados

=UNICOS(C2:C500) devuelve cada región una sola vez. Envuélvela en ORDENAR para tenerla en orden alfabético: =ORDENAR(UNICOS(C2:C500)). Si pones VERDADERO en el tercer argumento, devuelve solo los valores que aparecen exactamente una vez—muy útil para detectar clientes que compraron una sola vez.

SECUENCIA: genera números y fechas

=SECUENCIA(10) devuelve del 1 al 10 hacia abajo. =SECUENCIA(1, 12) devuelve del 1 al 12 en una fila, ideal para números de mes. Como Excel guarda las fechas como números, también puedes armar un calendario:

=SECUENCIA(30, 1, FECHA(2026,11,1), 1)

Dale formato de fecha al resultado y tendrás del 1 al 30 de noviembre.

Combinar funciones: aquí está el verdadero poder

Estas funciones aceptan matrices como entrada, así que puedes anidarlas libremente. Algunos patrones que los analistas usan todo el tiempo:

Lista ordenada de productos vendidos en una región

=ORDENAR(UNICOS(FILTRAR(D2:D500, C2:C500="Este")))

Una tabla resumen que se actualiza sola

Pon una lista única de vendedores en G2 y, al lado, el total de cada uno usando la referencia de desbordamiento:

G2:  =ORDENAR(UNICOS(B2:B500))
H2:  =SUMAR.SI.CONJUNTO(E2:E500, B2:B500, G2#)

Como G2# es una matriz, SUMAR.SI.CONJUNTO (SUMIFS) devuelve un total por vendedor y se desborda junto a los nombres. Si aparece un vendedor nuevo en los datos, ambas columnas crecen solas.

Las 5 ventas más grandes

=TOMAR(ORDENAR(A2:E500, 5, -1), 5)

TOMAR conserva las primeras cinco filas del resultado ordenado. Si tu Excel todavía no la tiene, usa =INDICE(ORDENAR(A2:E500,5,-1), SECUENCIA(5), SECUENCIA(1,5)).

Estos patrones son la base de los reportes armados con fórmulas. Si quieres convertirlos en una página de reporte completa, revisa nuestra guía para crear un dashboard interactivo en Excel.

Errores comunes y cómo resolverlos

1. #¡DESBORDAMIENTO! – Haz clic en el ícono del error y elige "Seleccionar celdas obstructoras". Borra esas celdas, descombina celdas o saca la fórmula de la tabla de Excel.
2. #¡CALC! – FILTRAR no encontró coincidencias y no pusiste el argumento si_vacío. Agrega algo como "Sin resultados".
3. #¡VALOR! – El rango incluir de FILTRAR tiene una cantidad de filas distinta a la matriz. Asegúrate de que ambos empiecen y terminen en las mismas filas.
4. #¿NOMBRE? – La función no existe en tu versión de Excel o hay un error de escritura.
5. Aparece el símbolo @ en fórmulas viejas – Excel lo agrega a fórmulas de archivos antiguos para conservar su comportamiento de un solo valor (intersección implícita). Normalmente puedes dejarlo así.

Cuándo usar matrices dinámicas y cuándo tablas dinámicas

Las matrices dinámicas y las tablas dinámicas se parecen en algunas cosas, pero no compiten. Las tablas dinámicas se crean más rápido, manejan bien muchos datos y permiten mover campos arrastrando. Las matrices dinámicas brillan cuando necesitas un resultado que se actualice sin presionar Actualizar, un diseño que una tabla dinámica no puede lograr o una lista que alimentará otra fórmula, un gráfico o una lista desplegable.

Los buenos libros de trabajo usan las dos. Si las tablas dinámicas son nuevas para ti, empieza con nuestra guía paso a paso de tablas dinámicas. Y cuando tus fórmulas empiecen a crecer demasiado, LET y LAMBDA te ayudarán a mantenerlas legibles.

Un ejercicio corto de práctica

La forma más rápida de dominar el desbordamiento es reconstruir un reporte que hoy haces a mano. Prueba esto con cualquier exportación de ventas:

1. Convierte los datos en una tabla llamada Ventas.
2. En una hoja vacía, crea en A2 la lista de regiones con =ORDENAR(UNICOS(Ventas[Región])).
3. En B2, calcula las ventas por región con =SUMAR.SI.CONJUNTO(Ventas[Importe], Ventas[Región], A2#).
4. Agrega una celda donde escribas el nombre de una región y, debajo, usa FILTRAR para mostrar sus transacciones.
5. Usa la lista desbordada de regiones como origen de una validación de datos escribiendo =$A$2# en el cuadro Origen.

Listo: tienes un pequeño reporte interactivo sin macros y sin copiar nada a mano.

Próximos pasos

Las matrices dinámicas son una de las mejoras más grandes que ha recibido Excel, y vuelven innecesarias muchas técnicas antiguas. Practica FILTRAR, ORDENAR, UNICOS y SECUENCIA con datos reales hasta que anidarlas te salga natural. Después combínalas con búsquedas: nuestra comparación de BUSCARX, BUSCARV e INDICE-COINCIDIR te muestra cuál función de búsqueda encaja mejor con los rangos desbordados.

Mira: lecciones en video

▶

▶

Preguntas frecuentes