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

Durante años, sacar de una tabla solo las ventas de una zona, ordenarlas o conseguir una lista sin repetidos obligaba a usar filtros manuales, tablas dinámicas o fórmulas larguísimas que había que copiar fila a fila. Con las matrices dinámicas, una sola fórmula escrita en una celda devuelve un rango completo de resultados que se actualiza cuando cambian los datos.

Aquí verás cómo funcionan FILTRAR, ORDENAR y UNICOS, cómo combinarlas entre sí y qué hacer cuando aparece un error de desbordamiento. Todos los ejemplos usan la misma tabla de muestra, así que puedes reproducirlos en un par de minutos.

Qué son las matrices dinámicas y qué versión de Excel necesitas

Una matriz dinámica es el resultado de una fórmula que ocupa varias celdas. Escribes la fórmula en la primera celda y Excel «desborda» los resultados hacia abajo y hacia la derecha, tantas filas y columnas como haga falta. Si los datos de origen cambian, el rango de resultados se ajusta solo.

Estas funciones están disponibles en Excel para Microsoft 365, Excel 2021 y versiones posteriores, y en Excel para la web. En Excel 2019 o anteriores no existen: si abres allí un archivo que las usa, verás el error #¿NOMBRE? o valores fijos que ya no se recalculan.

Antes de empezar, conviene tener claras tres reglas:

• Solo se escribe la fórmula en la primera celda. El resto del resultado aparece rodeado de un borde azul cuando lo seleccionas.
• No puedes editar ni borrar una celda suelta del rango desbordado. Si quieres cambiar algo, modifica la fórmula de origen.
• Para referirte al resultado completo desde otra fórmula se usa el operador # detrás de la primera celda, por ejemplo F2#.

La tabla de ejemplo

Copia estos datos en una hoja nueva, empezando en la celda A1. Las fórmulas de los apartados siguientes trabajan sobre el rango A2:D9.

FILTRAR: extraer solo las filas que cumplen una condición

La sintaxis es FILTRAR(matriz; incluir; [si_vacío]). El primer argumento es el rango que quieres devolver, el segundo es una condición que se evalúa fila a fila y el tercero, opcional, indica qué mostrar si ninguna fila cumple la condición.

Para obtener todas las ventas de la zona Norte:

=FILTRAR(A2:D9;C2:C9="Norte";"Sin resultados")

El resultado son cuatro filas completas (Cliente A, Cliente C, Cliente D y de nuevo Cliente C) con sus cuatro columnas.

Varias condiciones a la vez

Dentro del argumento incluir no sirven Y ni O, porque esas funciones devuelven un único valor y no uno por fila. En su lugar, cada condición se escribe entre paréntesis y se combinan con operaciones:

• Para que se cumplan todas (lógica Y), multiplica: (C2:C9="Norte")*(D2:D9>500).
• Para que se cumpla al menos una (lógica O), suma: (C2:C9="Norte")+(C2:C9="Sur").

Ventas de la zona Norte de más de 500:

=FILTRAR(A2:D9;(C2:C9="Norte")*(D2:D9>500);"Sin resultados")

Devuelve dos filas: Cliente D con 1250 y Cliente C con 870.

ORDENAR: ordenar resultados sin tocar los datos originales

La sintaxis es ORDENAR(matriz; [índice_orden]; [orden]; [por_columna]). El índice indica por qué columna de la matriz se ordena (1 es la primera) y el orden vale 1 para ascendente, que es el valor por defecto, o -1 para descendente.

=ORDENAR(A2:D9;4;-1)

Devuelve la tabla completa ordenada por importe de mayor a menor: primero Cliente D (1250), luego Cliente A (910), Cliente B (890) y así hasta Cliente C con 45. La tabla original no cambia de orden.

Ordenar el resultado de un filtro

Como FILTRAR devuelve una matriz, puedes pasársela directamente a ORDENAR. Así obtienes las ventas del Norte de mayor a menor importe:

=ORDENAR(FILTRAR(A2:D9;C2:C9="Norte");4;-1)

UNICOS: listas sin repetidos con una sola fórmula

La sintaxis es UNICOS(matriz; [por_columna]; [exactamente_una_vez]). En su forma más simple devuelve cada valor distinto una sola vez, en el orden en que aparece por primera vez:

=UNICOS(B2:B9)

El resultado es Teclado, Monitor, Ratón y Portátil.

Valores que aparecen exactamente una vez

El tercer argumento cambia el comportamiento: con VERDADERO solo devuelve los valores que no se repiten ninguna vez. Fíjate en el doble punto y coma, que deja vacío el segundo argumento:

=UNICOS(A2:A9;;VERDADERO)

Devuelve Cliente D y Cliente E, es decir, los clientes con una sola compra.

Contar valores distintos

Si envuelves UNICOS en CONTARA, obtienes cuántos elementos diferentes hay. Esta fórmula devuelve 3, porque hay tres zonas distintas:

=CONTARA(UNICOS(C2:C9))

Combinar las tres funciones: un resumen por zona

La potencia real aparece al anidarlas. Por ejemplo, para ver qué productos se han vendido en la zona Sur, sin repetir y en orden alfabético:

=ORDENAR(UNICOS(FILTRAR(B2:B9;C2:C9="Sur")))

El resultado es Monitor y Teclado. Con la misma lógica puedes montar un pequeño resumen de ventas por zona que crece solo:

1. En F1 escribe el encabezado Zona y en G1 el encabezado Total.
2. En F2 escribe =ORDENAR(UNICOS(C2:C9)). Aparecerán Este, Norte y Sur.
3. En G2 escribe =SUMAR.SI(C2:C9;F2#;D2:D9). El operador # hace que la suma se calcule para cada zona de la lista desbordada.
4. Comprueba los totales: Este 970, Norte 2485 y Sur 1200.

Si aparece una zona nueva en los datos, la lista de F2 se alarga y la columna G la acompaña sin copiar ninguna fórmula.

Errores habituales y cómo resolverlos

• #¡DESBORDAMIENTO!: hay algo en el rango donde deben aparecer los resultados (un valor, una fórmula o incluso un espacio) o hay celdas combinadas. Al seleccionar la fórmula, un borde discontinuo marca la zona que necesita. Vacíala o mueve la fórmula.
• #¡CALC!: FILTRAR no ha encontrado ninguna fila y no has indicado el tercer argumento. Añade un texto como "Sin resultados" o unas comillas vacías.
• #¡VALOR!: la matriz y la condición tienen distinto número de filas, por ejemplo A2:D9 frente a C2:C10.
• Fórmula dentro de una tabla de Excel: las fórmulas que desbordan no funcionan dentro de una tabla creada con Ctrl+T. Colócalas fuera, aunque sí pueden leer los datos de la tabla.

Cuando necesitas limpiar el archivo, no solo verlo

UNICOS es perfecto para consultar valores distintos, pero no elimina las filas repetidas del archivo original: la tabla de origen sigue igual. Si lo que necesitas es entregar un archivo limpio, o trabajas con una versión de Excel sin matrices dinámicas, puedes usar nuestra herramienta gratuita para quitar duplicados: subes el archivo xlsx, xls, csv u ods y descargas una copia sin filas repetidas. Y si quieres revisar qué filas han desaparecido, puedes comparar el archivo original con el limpio.

Conclusión

FILTRAR, ORDENAR y UNICOS sustituyen muchas tareas que antes exigían filtros manuales o fórmulas copiadas fila a fila. Escribe una fórmula, deja espacio libre para el resultado y aprovecha el operador # para encadenar cálculos. Empieza con los ejemplos de esta guía sobre tus propios datos y, en pocos días, montarás informes que se actualizan solos.

Preguntas frecuentes