FILTRAR, ORDENAR y UNICOS: fórmulas que devuelven listas enteras
FILTRAR, ORDENAR y UNICOS son funciones de matriz dinámica: escribes la fórmula en una sola celda y el resultado se desborda hacia abajo y a la derecha. =FILTRAR(B2:C8;A2:A8="Norte") devuelve las filas de la región Norte, =ORDENAR(B2:C8;2;-1) las ordena por la segunda columna de mayor a menor y =UNICOS(B2:B8) da la lista sin repetidos. Requieren Microsoft 365 o Excel 2021 o posterior.
Te pasa cada lunes: copias las ventas de una región a otra hoja, las ordenas, quitas duplicados para sacar la lista de vendedores… y la semana siguiente vuelves a empezar porque han entrado datos nuevos. El filtro de la cinta sirve para mirar, pero no deja un informe que se actualice solo.
Las funciones FILTRAR, ORDENAR y UNICOS trabajan de otra forma: escribes la fórmula en una sola celda y Excel rellena todas las filas y columnas que haga falta. A esto se le llama desbordar: como cuando llenas un vaso y el agua ocupa el espacio que necesita. Si cambian los datos de origen, la lista resultante crece, encoge o se reordena sola.
En esta guía filtras las ventas por región, combinas condiciones, ordenas por importe, sacas listas sin repetidos y aprendes a resolver el error #¡DESBORDAMIENTO!, el único tropiezo habitual de estas fórmulas.
Qué vas a aprender
- Qué es una matriz dinámica y qué significa que una fórmula se desborde
- Cómo filtrar filas con una o varias condiciones usando FILTRAR
- Cómo ordenar el resultado con ORDENAR y anidar funciones
- Cómo sacar listas sin duplicados con UNICOS y usarlas en otras fórmulas
- Cómo resolver el error #¡DESBORDAMIENTO!
Paso a paso
Comprueba tu versión y prepara los datos
Estas funciones existen en Microsoft 365, Excel 2021, Excel 2024 y Excel para la web. En Excel 2019 o anterior verás #¿NOMBRE?. Para comprobarlo, escribe =UNICOS( en una celda: si Excel te la sugiere mientras escribes, la tienes.
Usaremos una lista de ventas con la Región en A2:A8, el Vendedor en B2:B8 y el Importe en C2:C8. Deja libres las columnas de la derecha: ahí se desbordarán los resultados.
¿Versión antigua? El filtro de la cinta (Datos › Ordenar y filtrar › Filtro) y Quitar duplicados hacen algo parecido, pero a mano y sin actualizarse solos.
Filtra las filas de una región con FILTRAR
Escribe los encabezados Vendedor e Importe en E1 y F1. Luego, en E2, escribe =FILTRAR(B2:C8;A2:A8="Norte") y pulsa Intro.
El primer argumento es qué columnas quieres ver (B2:C8); el segundo, la condición que debe cumplir cada fila (A2:A8="Norte"). Aunque solo escribiste en E2, el resultado ocupa E2:F4: Ana 1.200, Marta 2.300 y Luis 990. El rango desbordado se marca con un borde azul fino.
Devuelve vendedor e importe de las filas cuya región es Norte.
Escribe la región en una celda (por ejemplo H1) y usa =FILTRAR(B2:C8;A2:A8=H1): cambias la celda y cambia el informe.
Añade un mensaje para cuando no haya resultados
Si ninguna fila cumple la condición, FILTRAR devuelve el error #¡CALC!. Evítalo con el tercer argumento, que indica qué mostrar si el resultado está vacío:
=FILTRAR(B2:C8;A2:A8="Oeste";"Sin ventas")
Como no hay ventas en la región Oeste, E2 muestra Sin ventas. En cuanto alguien añada una venta del Oeste dentro del rango, aparecerá en la lista.
Si no hay filas del Oeste, muestra «Sin ventas».
Combina varias condiciones con * (Y) y + (O)
Para exigir dos condiciones a la vez, escribe cada una entre paréntesis y multiplícalas. Las ventas del Norte de más de 1.000:
=FILTRAR(B2:C8;(A2:A8="Norte")*(C2:C8>1000))
El resultado es Ana 1.200 y Marta 2.300 (Luis queda fuera con 990). Si quieres que se cumpla una u otra, suma en lugar de multiplicar: =FILTRAR(B2:C8;(A2:A8="Norte")+(A2:A8="Sur")) devuelve las ventas del Norte y del Sur.
Región Norte e importe mayor que 1000.
Región Norte o región Sur.
Dentro de FILTRAR no funcionan Y ni O: devuelven un único VERDADERO o FALSO para todo el rango. Usa * y + con cada condición entre paréntesis.
Ordena el resultado con ORDENAR
ORDENAR recibe un rango, la columna por la que ordenar y el sentido (1 ascendente, -1 descendente). Para ver todas las ventas de mayor a menor importe, escribe en E2: =ORDENAR(B2:C8;2;-1). El 2 es la segunda columna del rango (Importe), no la columna B de la hoja.
Lo potente es anidarla: =ORDENAR(FILTRAR(B2:C8;A2:A8="Norte");2;-1) devuelve las ventas del Norte ya ordenadas: Marta 2.300, Ana 1.200, Luis 990.
Todas las ventas, de mayor a menor importe.
Ventas del Norte ordenadas de mayor a menor.
Si quieres ordenar por una columna que no vas a mostrar, usa ORDENARPOR: =ORDENARPOR(B2:B8;C2:C8;-1) devuelve los vendedores ordenados por importe.
Saca una lista sin repetidos con UNICOS
UNICOS devuelve cada valor una sola vez, en el orden en que aparece. Escribe =ORDENAR(UNICOS(A2:A8)) en E2 y obtienes las regiones sin repetir y en orden alfabético: Este, Norte, Sur.
Ahora el truco: en F2 escribe =SUMAR.SI(A2:A8;E2#;C2:C8). El símbolo # detrás de E2 significa «todo el rango desbordado desde E2». La fórmula también se desborda y te da el total por región: 2.120, 4.490 y 2.600. Si aparece una región nueva, se añade sola a las dos columnas.
Regiones sin repetir, en orden alfabético.
Total de cada región de la lista desbordada en E2.
UNICOS tiene un tercer argumento: con =UNICOS(B2:B8;FALSO;VERDADERO) obtienes solo los valores que aparecen exactamente una vez (en el ejemplo, Pedro).
Resuelve el error #¡DESBORDAMIENTO!
Si alguna celda del rango donde debe desbordarse el resultado no está vacía, la fórmula no puede escribir encima y muestra #¡DESBORDAMIENTO!. Haz clic en la celda con el error: Excel dibuja con línea discontinua el área que necesita, y el icono de advertencia que aparece junto a la celda incluye una opción para seleccionar las celdas que obstruyen el paso.
Borra o mueve lo que estorba (en el ejemplo, una nota olvidada en E4) y el resultado aparece al instante. Otras causas: escribir la fórmula dentro de una tabla de Excel (allí no se desbordan) o que el resultado llegue al final de la hoja.
No escribas a mano dentro de un rango desbordado: cualquier valor que pongas ahí rompe la fórmula de la primera celda.
Trucos de experto
- Usa una tabla como origen. Si conviertes los datos en una tabla llamada
Ventas, escribe=FILTRAR(Ventas;Ventas[Región]="Norte")fuera de la tabla: cada fila nueva entra sola en el resultado. Ver tablas de Excel. - Cuenta lo que devuelve.
=FILAS(E2#)te dice cuántas filas tiene el resultado desbordado, y=CONTARA(UNICOS(B2:B8))cuántos vendedores distintos hay (4 en el ejemplo). - Haz informes dinámicos con listas desplegables. Pon una lista desplegable con las regiones en una celda y úsala como condición de
FILTRAR: tendrás un informe que cualquiera puede consultar sin tocar fórmulas. - Ponle nombre a las partes. Cuando una fórmula anida tres funciones,
LETte permite nombrar cada paso para que se lea mejor. Te lo contamos en nombres definidos y LET.
Errores comunes y cómo arreglarlos
La fórmula muestra #¿NOMBRE? nada más escribirla.
Tu versión de Excel no tiene matrices dinámicas (Excel 2019 o anterior) o el nombre está mal escrito. Comprueba que pones FILTRAR, ORDENAR o UNICOS en un Excel de Microsoft 365, 2021 o posterior.
FILTRAR devuelve #¡VALOR! al combinar condiciones.
Los rangos de la condición deben tener el mismo número de filas que el rango filtrado. Si filtras B2:C8, las condiciones deben ser A2:A8 y C2:C8, no A2:A10.
Las filas nuevas que añades debajo no aparecen en el resultado.
El rango de la fórmula termina en la fila 8. Amplíalo o, mejor, convierte los datos en una tabla de Excel y usa referencias como Ventas[Región], que crecen solas.
En Excel para Mac las funciones son las mismas y están disponibles en Microsoft 365 para Mac y en Excel 2021 para Mac o posterior. Las fórmulas se escriben exactamente igual.
Preguntas frecuentes
¿Qué diferencia hay entre FILTRAR y el filtro de la cinta?
El filtro de Datos › Ordenar y filtrar › Filtro oculta filas en los datos originales. FILTRAR deja los datos intactos y crea una copia filtrada en otro lugar, que se actualiza sola. Para el filtro clásico, mira ordenar y filtrar sin desordenar las filas.
¿Qué pasa si abro el archivo en un Excel antiguo?
Los resultados que ya estaban calculados suelen verse, pero las fórmulas no se pueden editar ni recalcular con normalidad. Si el archivo va a circular entre versiones antiguas, copia el resultado y pégalo como valores.
¿Puedo filtrar por texto que contenga una palabra?
Sí, combinando ESNUMERO y HALLAR: =FILTRAR(B2:C8;ESNUMERO(HALLAR("ar";B2:B8))) devuelve las filas cuyo vendedor contiene «ar» (Marta, dos veces).
Mi Excel da error al escribir la fórmula con punto y coma. ¿Qué hago?
En México y algunos otros países Excel usa la coma como separador. Cambia cada ; por ,; el botón de la página lo hace por ti en todas las fórmulas.
Fuentes oficiales
Documentación de Microsoft consultada para esta guía.
Se guarda solo en este navegador.
Sigue aprendiendo
Ordenar y filtrar datos en Excel sin desordenar las filas
Ordena por una o varias columnas, filtra por región, importe o fecha y suma solo lo visible, sin mezclar los datos de cada fila. Paso a paso, en 5 minutos.
Tablas de Excel: el formato que hace que filtros, fórmulas y gráficos funcionen solos
Convierte tus datos en una tabla de Excel: filtros, totales y fórmulas que se amplían solos cuando añades filas. Paso a paso y con ejemplos, en 6 minutos.
SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO: sumar y contar con condiciones
Aprende a sumar y contar solo las filas que cumplen varias condiciones (región, comercial, fechas, importes) con SUMAR.SI.CONJUNTO. Paso a paso, con ejemplos.
Nombres definidos y LET: fórmulas que se entienden al leerlas
Pon nombre a celdas y rangos y usa LET para que tus fórmulas se lean como una frase, sin $ ni cálculos repetidos. Paso a paso con un ejemplo de factura.
Errores en fórmulas (#N/D, #¡VALOR!, #¡REF!, #¡DIV/0!): qué significan y cómo arreglarlos
Qué significan los errores #N/D, #¡VALOR!, #¡REF!, #¡DIV/0! y #¿NOMBRE? en Excel, cómo encontrar la causa y cómo arreglarlos bien. Paso a paso con ejemplos.