Matrices Dinámicas en Excel: FILTRAR, ORDENAR, UNICOS y la Fórmula Que Rellena Su Propio Rango

Durante treinta años, una fórmula en Excel significaba una fórmula en una celda. Una celda, una respuesta. Si querías veinte respuestas, escribías la fórmula una vez y la arrastrabas veinte filas hacia abajo, y desde ese momento la hoja tenía una forma que había que mantener a mano. Llegan filas, arrastras más. Se van filas, borras lo que sobra. Alguien se olvida y el informe deja de cubrir, en silencio, los últimos cuatro registros.

Las matrices dinámicas acabaron con eso. Una sola fórmula puede ahora devolver un bloque entero de valores y desbordarlos hacia las celdas de debajo y de al lado, ajustándose al tamaño de la respuesta en vez de al del último arrastre que hiciste. La lista de regiones crece a cuatro; la fórmula devuelve cuatro. Se borra una operación; la tabla filtrada se encoge en el mismo segundo.

Esta guía cubre las cinco funciones que lo hacen posible — UNICOS, ORDENAR, ORDENARPOR, FILTRAR y SECUENCIA — el operador # que te permite apuntar a un rango cuyo tamaño no conoces, y los dos códigos de error que solo existen porque existe el desbordamiento.

Consejo: Todos los ejemplos de abajo funcionan sobre la tabla que aparece tras la sección 1. Cópiala en una hoja en blanco empezando en A1 y las referencias de celda coincidirán exactamente. Necesitas Excel 365 o Excel 2021 (o Excel para la web) — la sección 9 cubre qué pasa si un compañero abre el archivo en algo más antiguo.

1) Una Fórmula, Muchas Celdas

Escribe esto en H2 de una hoja que contenga la tabla de abajo y pulsa Intro una sola vez:

=UNICOS(B2:B11)

Aparecen tres valores — H2, H3, H4 — de una sola fórmula. Haz clic en H3 y la barra de fórmulas muestra =UNICOS(B2:B11) en gris; haz clic en H2 y muestra la misma fórmula en negro, editable. Ese es todo el modelo:

De ahí se derivan dos consecuencias inmediatas, y a ambas cuesta acostumbrarse. No puedes borrar ni editar una celda suelta de un rango desbordado — Excel responde "No se puede cambiar parte de una matriz" y se niega. Y ya no controlas cuántas filas ocupa el resultado; lo controlan los datos. Eso segundo es justamente el objetivo, pero significa que una fórmula puede crecer hasta invadir celdas que usabas para otra cosa, que es de donde sale el #¡DESBORDAMIENTO! de la sección 8.

2) UNICOS: Una Lista de Valores Distintos Que Se Mantiene Sola

🎯 Escenario: Necesitas la lista de regiones para un bloque de resumen, y necesitas que siga siendo correcta cuando alguien abra una operación en una región que no existía el trimestre pasado.

=UNICOS(matriz, [por_columna], [exactamente_una_vez])

=UNICOS(B2:B11)

Resultado: EMEA, LATAM, AMER — tres celdas, en orden de primera aparición, no alfabético.

Ese orden pilla a mucha gente. UNICOS conserva el orden en el que encontró los valores; si los quieres alfabéticos tienes que pedirlo, y de eso se encarga la sección 3.

El tercer argumento es el que casi nadie conoce:

=UNICOS(A2:A11, FALSO, VERDADERO)

Resultado: Sofia Nyberg

Pon exactamente_una_vez a VERDADERO y obtienes los valores que aparecen una sola vez — no una copia de cada valor, sino los valores que no tienen ningún duplicado. Priya aparece tres veces, Tom dos, Marco dos, Dana dos; solo Sofia está sola. Es la auditoría más rápida de Excel: comerciales con una única operación, números de factura que nunca se emparejaron con un abono, identificadores que no llegaron a coincidir.

UNICOS también funciona sobre filas enteras. =UNICOS(B2:C11) devuelve las combinaciones distintas de región + producto, comparando cada fila como una unidad en vez de cada celda:

Resultado: siete filas — EMEA/Analytics, EMEA/Storage, LATAM/Analytics, AMER/Storage, EMEA/Compute, LATAM/Compute, AMER/Analytics. Cuéntalas en los datos antes de fiarte de la cuenta de la hoja; ese hábito vale más que la fórmula.

Trampa: UNICOS considera un espacio final como una diferencia. "EMEA " y "EMEA" son dos regiones distintas para él, y en pantalla son idénticas. Si una lista de distintos sale más larga de lo que esperabas, envuelve el origen en ESPACIOS — =UNICOS(ESPACIOS(B2:B11)) — y mira si baja la cuenta.

3) ORDENAR y ORDENARPOR: Orden Sin Tocar el Origen

🎯 Escenario: Una clasificación que se reordena sola cuando cambia un número, sin que nadie tenga que volver a ejecutar Datos ▸ Ordenar.

=ORDENAR(matriz, [índice_ordenación], [orden], [por_columna])

=ORDENAR(UNICOS(A2:A11))

Resultado: Dana Whitlock, Marco Silva, Priya Raman, Sofia Nyberg, Tom Beckett

El índice_ordenación es la columna dentro de la matriz que le pasaste, no la columna de la hoja. Si le das a ORDENAR la tabla de seis columnas y le pides la columna 5, ordena por el importe de la operación:

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

Resultado: la tabla entera, con la operación mayor arriba — los 31.200 de Dana en primer lugar y los 4.900 de Sofia al final. -1 es descendente; 1, el valor por defecto, es ascendente.

ORDENARPOR es la versión para cuando lo que quieres ordenar y aquello por lo que quieres ordenar son rangos diferentes:

=ORDENARPOR(A2:A11, E2:E11, -1)

Resultado: solo los nombres de los comerciales, ordenados por importe descendente — Dana Whitlock (31.200), Marco Silva (27.300), Priya Raman (24.500), Priya Raman (20.100), Tom Beckett (18.200), … Los nombres se repiten porque las operaciones se repiten; la lista tiene diez filas, igual que el origen.

La diferencia importa más de lo que parece. ORDENAR solo puede ordenar por una columna que esté dentro de la matriz que devuelve, así que una lista de solo nombres no se puede ordenar por importe con ORDENAR. ORDENARPOR recibe las claves de ordenación por separado, lo que además te permite ordenar por algo que nunca muestras — una prioridad, una puntuación oculta, una fecha que el lector no necesita ver. Con varias claves simplemente se sigue: =ORDENARPOR(A2:A11, B2:B11, 1, E2:E11, -1) ordena por región ascendente y luego por importe descendente dentro de cada región.

Trampa: la matriz de claves de ORDENARPOR debe tener exactamente la misma altura que la matriz que se ordena. Una fila de más en cualquiera de las dos y obtienes #¡VALOR! sin ninguna pista de qué lado está mal.

4) FILTRAR: La Consulta Que Antes Escribías a Mano

FILTRAR es la función que cambia cómo se construye la hoja. Todo lo anterior — columnas auxiliares de SI, un autofiltro que alguien tiene que acordarse de volver a aplicar, una tabla dinámica actualizada a mano — era un apaño por no tenerla.

=FILTRAR(matriz, incluir, [si_vacío])

🎯 Escenario: Una lista en vivo de operaciones ganadas que alimenta un resumen de comisiones y que no puede quedarse obsoleta en silencio.

=FILTRAR(A2:F11, D2:D11="Won")

Resultado: cuatro filas — Priya/EMEA/Analytics/24.500, Dana/AMER/Storage/31.200, Tom/EMEA/Compute/12.750, Priya/EMEA/Compute/20.100.

El segundo argumento, incluir, no es una condición en el sentido habitual — es una matriz de VERDADEROs y FALSOs exactamente igual de alta que los datos. D2:D11="Won" se evalúa como diez booleanos, y FILTRAR conserva las filas donde encontró VERDADERO. En cuanto lo ves así, combinar condiciones deja de ser una cuestión de sintaxis y pasa a ser aritmética:

=FILTRAR(A2:F11, (B2:B11="EMEA")*(D2:D11="Won"))

Resultado: tres filas, que suman 57.350 — solo las operaciones ganadas de EMEA. Fíjate en los paréntesis alrededor de cada condición: no son opcionales, porque * tiene más prioridad que = y sin ellos Excel intenta multiplicar texto por texto.

Ahora el caso O, que es donde suelen romperse los filtros escritos a mano:

=FILTRAR(A2:F11, (E2:E11>20000)+(F2:F11>60))

Resultado: seis filas — las cuatro operaciones por encima de 20.000 más las dos que llevan abiertas más de sesenta días. Una operación que cumple ambas condiciones sigue apareciendo una sola vez; a FILTRAR solo le importa que el número no sea cero, no cuánto vale.

🎯 Escenario: El mismo filtro, conectado a una celda en la que alguien puede escribir.

=FILTRAR(A2:F11, B2:B11=$H$1, "Sin operaciones en esta región")

Pon LATAM en H1 y aparecen dos filas. Pon APAC en H1 y obtienes el mensaje en vez de un error — para eso está si_vacío. Si lo omites, un resultado vacío devuelve #¡CALC!, y un informe que muestra #¡CALC! cada vez que un filtro no encuentra nada parece roto aunque funcione perfectamente. Rellena el tercer argumento siempre.

FILTRAR se combina con los agregados que ya usas, y ahí está la mayor parte de su valor diario:

=SUMA(FILTRAR(E2:E11, D2:D11="Won"))

Resultado: 88550

Eso es un SUMAR.SI.CONJUNTO que podrías haber escrito de otra forma. Pero =PROMEDIO(FILTRAR(F2:F11, D2:D11="Open")) → 25,25 es el cálculo que resuelve PROMEDIO.SI.CONJUNTO, y =MEDIANA(FILTRAR(E2:E11, B2:B11="EMEA")) es uno para el que no existe ningún MEDIANA.SI.CONJUNTO. Cualquier función que acepte un rango acepta una matriz filtrada, lo que te da sin ruido una versión condicional de todos los estadísticos de Excel.

5) SECUENCIA: Números y Fechas Salidos de la Nada

🎯 Escenario: Una columna de posiciones junto a una lista cuya longitud cambia, o doce cierres de mes para una previsión que antes se escribía a mano.

=SECUENCIA(filas, [columnas], [inicio], [paso])

=SECUENCIA(5)            → 1, 2, 3, 4, 5 en cinco filas
=SECUENCIA(1, 5)         → 1, 2, 3, 4, 5 en cinco columnas
=SECUENCIA(5, 1, 10, 5)  → 10, 15, 20, 25, 30

Por sí solo parece un truco de salón. Se gana su sitio cuando la cuenta sale de los datos y no de ti:

=SECUENCIA(FILAS(H2#))

Eso numera una lista desbordada — de la longitud que tenga ahora mismo — y la renumera en cuanto la lista cambia de tamaño. Las fechas son el otro uso cotidiano, ya que las fechas de Excel no son más que números:

=FECHA.MES(FECHA(2026,1,1), SECUENCIA(12, 1, 0, 1))

Resultado: el día uno de cada mes de todo 2026, una vez das formato de fecha a las celdas. SECUENCIA(12,1,0,1) produce del 0 al 11, y FECHA.MES suma esos meses a la fecha inicial — que es la razón por la que esto trata bien los meses cortos, cosa que +30 nunca haría.

6) El Operador #: Apuntar a un Rango Que No Dimensionaste

Aquí está el problema que crea el desbordamiento. =UNICOS(B2:B11) en H2 devuelve hoy tres regiones. Si escribes =SUMAR.SI(B2:B11, H2:H4, E2:E11) al lado y el mes que viene aparece una cuarta región, tu resumen cubre tres de cuatro regiones y no da ninguna señal de que ya está mal.

El operador de rango desbordado lo arregla. H2# significa todo el rango en el que H2 desborda ahora mismo, sea cual sea:

=SUMAR.SI($B$2:$B$11, H2#, $E$2:$E$11)

Resultado: 85150, 42700, 42900 — un total por región, desbordado para encajar. Añade una región a los datos y ambas columnas crecen juntas, porque el argumento de criterios es a su vez una matriz desbordada y SUMAR.SI devuelve un resultado por criterio.

Esa # final es la pieza que convierte las matrices dinámicas de una forma más cómoda de escribir fórmulas en una forma distinta de construir una hoja. En cualquier sitio donde antes habrías fijado un rango a mano y cruzado los dedos:

=CONTARA(H2#)                    → cuántas filas tiene la lista ahora
=INDICE(H2#, 1)                  → el primer elemento, sea cual sea
=UNIRCADENAS(", ", VERDADERO, H2#) → la lista entera como una sola frase

Dos sitios que merece la pena conocer en concreto:

• Validación de datos. En el cuadro Origen, escribe =$H$2# y la lista desplegable crece y se encoge con la lista. Es la mejor razón que hay para construir desplegables con ORDENAR(UNICOS(...)) en vez de con una lista escrita a mano.
• Gráficos. El cuadro de diálogo de series no acepta H2#. La solución es un nombre definido — Fórmulas ▸ Asignar nombre, que hace referencia a =Hoja1!$H$2# — y luego apuntar la serie del gráfico a ese nombre. El gráfico sigue entonces al desbordamiento.

Trampa: # se refiere al desbordamiento actual. Si borras la fórmula ancla, todas las referencias con # que apuntaban a ella se convierten en #¡REF! — igual que cualquier otra referencia rota, solo que se lleva por delante un bloque dependiente entero de una vez, no una celda.

7) Combinarlas: El Informe Que Se Reconstruye Solo

Las cinco funciones se diseñaron para anidarse, y las fórmulas útiles son casi siempre dos o tres de ellas apiladas. Léelas de dentro hacia fuera.

🎯 Escenario: Un bloque en una hoja de resumen que muestra las operaciones ganadas de EMEA, solo comercial e importe, de mayor a menor — reconstruido automáticamente cada vez que cambia la lista de operaciones.

=ORDENAR(FILTRAR(APILARH(A2:A11, E2:E11), (B2:B11="EMEA")*(D2:D11="Won")), 2, -1)

Resultado:

APILARH pega las dos columnas no contiguas en una única matriz de dos columnas; FILTRAR se queda con las victorias de EMEA; ORDENAR coloca las supervivientes por la segunda columna, descendente. Sin columnas auxiliares, sin rango intermedio, sin nada que mantener. (APILARH requiere Excel 365 o Excel 2024 — en Excel 2021, =FILTRAR(ELEGIR({1\2}, A2:A11, E2:E11), …) hace el mismo trabajo.)

🎯 Escenario: Las tres operaciones mayores, sean las que sean esta mañana.

=TOMAR(ORDENAR(A2:F11, 5, -1), 3)

Resultado: los 31.200 de Dana, los 27.300 de Marco y los 24.500 de Priya.

🎯 Escenario: Una lista ordenada y sin repetidos de los comerciales que tienen algo abierto ahora mismo — exactamente el origen que quieres detrás de un desplegable.

=ORDENAR(UNICOS(FILTRAR(A2:A11, D2:D11="Open")))

Resultado: Dana Whitlock, Marco Silva, Priya Raman, Tom Beckett

Apunta el Origen de la validación de datos a =$H$2# y el desplegable se mantiene solo durante el resto de la vida del libro. Esa combinación — ORDENAR(UNICOS(FILTRAR(…))) detrás de una celda validada, alimentando un FILTRAR que lee esa celda — es la mayor parte de lo que la gente quiere decir cuando dice que una hoja de cálculo parece una aplicación y no un documento.

8) #¡DESBORDAMIENTO! y #¡CALC!: Los Dos Errores Nuevos

Ninguno de los dos existía antes de las matrices dinámicas, y cada uno significa algo concreto.

#¡DESBORDAMIENTO! — el resultado no tiene dónde ir. La fórmula está bien; la hoja estorba. Haz clic en la celda y el triángulo de aviso te dice el motivo. Los cinco que te encontrarás de verdad:

El de la columna entera es el habitual, y merece la pena ser tajante: A:A son 1.048.576 filas. =FILTRAR(A:F, D:D="Won") le pide a Excel que evalúe un millón de filas en cada recálculo. Apunta las matrices dinámicas a una Tabla en su lugar — =FILTRAR(tblOperaciones, tblOperaciones[Etapa]="Won") cubre exactamente las filas que existen, crece cuando se añaden filas y no hace trabajo de más.

#¡CALC! — el resultado es una matriz vacía. En la práctica esto significa una cosa: un FILTRAR no encontró nada y te dejaste si_vacío. Añade el tercer argumento. La otra causa es una matriz anidada (una matriz de matrices), que hay que esforzarse bastante para producir sin querer.

9) El Signo @ y Qué Pasa en un Excel Antiguo

Abre un libro antiguo en un Excel actual y puede que encuentres símbolos @ en fórmulas que tú no pusiste: =@BUSCARV(...), =@A1:A10. No hay nada roto. Es el operador de intersección implícita, y es una marca de compatibilidad.

Antes de las matrices dinámicas, una fórmula a la que le dabas un rango donde esperaba un solo valor cogía en silencio el valor de la misma fila que la fórmula — intersección implícita. El Excel moderno no hace eso; desborda. Así que cuando abre un archivo heredado, inserta @ allí donde el comportamiento antiguo estaba en juego, con el significado de sigue cogiendo un solo valor aquí. Borrar la @ hace que la fórmula desborde, lo cual a veces es justo lo que quieres y a veces reescribe la hoja que has heredado. Bórrala a propósito, no por ordenar.

En el sentido contrario hay menos margen:

El caso #¿NOMBRE? conviene entenderlo más que temerlo, porque no es una degradación elegante: los valores no sobreviven, y guardar el archivo en la versión antigua puede eliminar la fórmula por completo. Si un libro tiene que abrirse en Excel 2019, las matrices dinámicas no son una opción en él — sin apaños, sin sustitutos. Esa es una decisión que se toma al crear el archivo, no cuando alguien escribe para decir que todas las celdas ponen #¿NOMBRE?.

10) Cuándo No Desbordar

Las matrices dinámicas no son gratis, y hay tres situaciones que siguen resolviéndose mejor a la antigua.

Dentro de Tablas. Una columna de Tabla quiere una fórmula por fila, y la rellena hacia abajo automáticamente según se añaden filas — que ya es el comportamiento sin mantenimiento que buscabas. Una fórmula que desborda dentro de una Tabla solo devuelve #¡DESBORDAMIENTO!. Usa fórmulas por fila dentro de las Tablas y matrices dinámicas fuera, leyendo de ellas.

Donde la maqueta es fija y se imprime. Un desbordamiento que crece no apartará nada — fallará con #¡DESBORDAMIENTO! en su lugar. En una plantilla donde un total tiene que caer en una celda concreta de una página impresa concreta, un número impredecible de filas es un riesgo, no una ventaja.

Conjuntos filtrados muy grandes que se recalculan constantemente. Un FILTRAR sobre 200.000 filas se reevalúa cada vez que cambia algo en la hoja. Cuando eso se vuelve lento, la respuesta suele ser una tabla dinámica o Power Query — herramientas que conservan un resultado hasta que les dices que se actualicen, en vez de recalcularlo sin parar.

En todo lo demás — resúmenes, desplegables, dashboards, cualquier lista cuya longitud sea una propiedad de los datos y no una decisión tuya — la versión desbordada es más pequeña, más honesta y no obliga a volver a arrastrarla a quien herede el archivo.

Lista de Comprobación Rápida (Antes de Fiarte del Desbordamiento)

• Todo FILTRAR tiene su tercer argumento si_vacío, para que un resultado sin coincidencias se lea como texto y no como #¡CALC!
• Cada condición dentro de un FILTRAR de varios criterios va entre sus propios paréntesis
• * para Y, + para O — y has comprobado cuál querías de verdad
• Los rangos son Tablas o rangos acotados, nunca A:A
• Todo lo que dependa de un desbordamiento lo referencia con #, no con un rango fijado a mano
• Las celdas de debajo y a la derecha de cada ancla están realmente vacías — sin espacios sueltos
• El índice_ordenación de ORDENAR cuenta columnas dentro de la matriz, no columnas de la hoja
• Las matrices de claves de ORDENARPOR tienen exactamente la misma altura que la matriz ordenada
• Sabes qué versión de Excel usa la última persona que abrirá este archivo

Resumen de Errores Comunes

1. Omitir si_vacío: un filtro que no encuentra nada muestra #¡CALC! y parece un informe roto.
2. Faltan paréntesis en filtros de varios criterios: B2:B11="EMEA"*D2:D11="Won" es un #¡VALOR!, y la solución son cuatro paréntesis.
3. + donde querías *: un filtro O que devuelve casi todas las filas suele ser esto, y produce un resultado plausible en vez de un error.
4. Referenciar un desbordamiento por su dirección actual: H2:H4 es correcto hasta que crecen los datos. H2# sigue siéndolo después.
5. Rangos de columna entera: =FILTRAR(A:F, D:D="Won") funciona y luego vuelve lento el libro. Usa una Tabla.
6. Suponer que UNICOS ordena: devuelve el orden de primera aparición. Envuélvelo en ORDENAR si lo necesitas alfabético.
7. Espacios finales en el origen: UNICOS cuenta "EMEA " y "EMEA" por separado, y en pantalla son idénticos.
8. Intentar editar una celda fantasma: solo el ancla es editable — "No se puede cambiar parte de una matriz" no es un fallo.
9. Construir un desbordamiento dentro de una Tabla: siempre será #¡DESBORDAMIENTO!. Sácalo fuera.
10. Enviar un libro con matrices dinámicas a Excel 2019: las fórmulas pasan a #¿NOMBRE?, y guardarlo allí puede destruirlas.

Conclusión

Lo que merece la pena llevarse de todo esto no es la lista de funciones — es que el tamaño de un resultado es ahora asunto de los datos y no tuyo. Una fórmula arrastrada codifica una suposición sobre cuántas filas habrá, y todas esas suposiciones acaban quedándose obsoletas de una forma que nadie nota. Una fórmula desbordada no supone nada.

Si después de leer esto escribes una cosa de forma distinta, que sea el desplegable: =ORDENAR(UNICOS(FILTRAR(...))) en una columna libre y =$H$2# en el Origen de la validación. Cuesta un minuto y elimina una tarea de mantenimiento que, si no, vuelve cada trimestre mientras el libro exista.

Y luego añade el argumento si_vacío a cada FILTRAR que escribas. Cuesta tres palabras y es la diferencia entre un informe que dice Sin operaciones en esta región y otro que pone #¡CALC! delante de tu jefe.

¿Quieres practicar? Los ejercicios de búsqueda y filtrado de la app funcionan con estos mismos patrones — filtrar por dos condiciones, ordenar por una columna que no muestras, y aquellos en los que la fórmula obvia desborda sobre algo que ya estaba ahí.