FILTRAR, ORDENAR, UNICOS y LET: funciones de matriz dinámica | Curso de Excel gratis: de básico a avanzado

Lo que lograrás en esta lección

Entender cómo funcionan las fórmulas que "se desbordan" a varias celdas.
Filtrar, ordenar y quitar duplicados con fórmulas que se actualizan solas.
Construir un top 5 automático y un buscador en tu hoja.
Escribir fórmulas largas de forma legible con LET.

Las funciones de matriz dinámica son el mayor cambio de Excel en los últimos 30 años. Una sola fórmula puede devolver una tabla entera que crece o se encoge sola cuando cambian los datos. Están disponibles en Microsoft 365, Excel 2021 y versiones posteriores, Excel para la web y Google Sheets (con nombres en inglés en algunos casos). Si tu Excel es 2019 o anterior, verás el error #¿NOMBRE?.

El desbordamiento

Escribe en una celda vacía =SECUENCIA(5). Aunque solo escribiste en una celda, aparecen los números del 1 al 5 hacia abajo. Eso es desbordar (en inglés, spill): la fórmula ocupa tantas celdas como resultados tiene.

Solo la primera celda contiene la fórmula; las demás muestran el resultado en gris en la barra de fórmulas.
Para referirte a todo el resultado desde otra fórmula, usa la primera celda con #: si la fórmula está en G2, todo el rango es G2#.
Si hay algo escrito en el camino, aparece el error #¡DESBORDAMIENTO!. Borra o mueve lo que estorba.

UNICOS: listas sin repetidos

=UNICOS(Ventas[Cliente])

Devuelve cada cliente una sola vez. Con un tercer argumento VERDADERO devuelve solo los que aparecen exactamente una vez: =UNICOS(Ventas[Cliente];;VERDADERO) (útil para detectar clientes de una única compra).

ORDENAR y ORDENARPOR

=ORDENAR(UNICOS(Ventas[Cliente]))              ← lista alfabética de clientes
=ORDENAR(Ventas; 4; -1)                        ← toda la tabla ordenada por la columna 4, de mayor a menor
=ORDENARPOR(Ventas[Vendedor]; Ventas[Importe]; -1)  ← vendedores ordenados por importe

El tercer argumento de ORDENAR es 1 (ascendente, por defecto) o -1 (descendente).

FILTRAR: el buscador que devuelve todas las coincidencias

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

A diferencia de BUSCARV, que devuelve solo la primera coincidencia, FILTRAR devuelve todas las filas que cumplen la condición:

=FILTRAR(Ventas; Ventas[Región]="Norte"; "Sin ventas")

Varias condiciones

Las condiciones se combinan con operaciones: multiplicar equivale a "Y", sumar equivale a "O". Cada condición va entre paréntesis.

Norte Y mayores de 1.000:
=FILTRAR(Ventas; (Ventas[Región]="Norte") * (Ventas[Importe]>1000))

Norte O Sur:
=FILTRAR(Ventas; (Ventas[Región]="Norte") + (Ventas[Región]="Sur"))

Ventas de un mes (fechas en H1 y H2):
=FILTRAR(Ventas; (Ventas[Fecha]>=H1) * (Ventas[Fecha]<=H2))

1. ¿Qué devuelve =FILTRAR(Ventas; Ventas[Vendedor]="Ana") si Ana tiene 12 ventas?

Solo la primera venta de Ana.
Las 12 filas de Ana, desbordadas hacia abajo.
El número 12.

FILTRAR devuelve todas las filas que cumplen la condición.

2. En FILTRAR, ¿cómo expresas "región Norte y estado Pagado"?

(Región="Norte") + (Estado="Pagado")
(Región="Norte") * (Estado="Pagado")
Y(Región="Norte"; Estado="Pagado")

Multiplicar condiciones equivale a "Y"; sumar, a "O". La función Y no sirve aquí porque devuelve un único valor, no uno por fila.

Un buscador en tu hoja

Combina FILTRAR con HALLAR para buscar texto parcial, como en un buscador web. Si el término de búsqueda está en B1:

=FILTRAR(Clientes; ESNUMERO(HALLAR(B1; Clientes[Nombre])); "No hay coincidencias")

Escribe "dist" en B1 y verás todos los clientes cuyo nombre contiene "dist", sin importar mayúsculas.

Top 5 automático

=TOMAR(ORDENAR(Ventas; 4; -1); 5)

TOMAR (de las versiones más recientes de Microsoft 365) se queda con las primeras filas del resultado. Si tu versión no la tiene, usa una tabla dinámica con el filtro Diez mejores (lección 9) o deja que la tabla ordenada se desborde y fíjate en las 5 primeras filas.

Otras funciones útiles de la misma familia: ELEGIRCOLS (quedarse con algunas columnas), APILARV (unir tablas una debajo de otra) y DIVIDIRTEXTO (separar un texto en columnas).

Resúmenes que se actualizan solos

Combina la lista desbordada con las funciones de la lección 7. Con los clientes únicos en G2#:

H2: =SUMAR.SI.CONJUNTO(Ventas[Importe]; Ventas[Cliente]; G2#)

Como G2# devuelve varios clientes, la fórmula de H2 también se desborda: un total por cliente, y si aparece un cliente nuevo, la tabla crece sola. Es un resumen tipo tabla dinámica que no necesita actualizarse.

3. La fórmula de G2 devuelve una lista desbordada. ¿Cómo te refieres a toda esa lista?

G2:G
G2#
G2*

El operador # de desbordamiento abarca todo el resultado, aunque cambie de tamaño.

LET: fórmulas largas, legibles

LET permite dar nombre a resultados intermedios dentro de una fórmula. Hace las fórmulas más fáciles de leer y más rápidas, porque cada cálculo se hace una sola vez:

=LET(
  ventasNorte; FILTRAR(Ventas[Importe]; Ventas[Región]="Norte");
  total; SUMA(ventasNorte);
  pedidos; CONTAR(ventasNorte);
  total / pedidos
)

Devuelve el ticket promedio del Norte. Puedes escribir la fórmula en varias líneas con Alt + Enter dentro de la barra de fórmulas.

Las matrices dinámicas en las tablas de Excel

Las fórmulas que se desbordan no pueden escribirse dentro de una tabla de Excel, pero sí pueden leer de ella. Lo habitual: los datos en una tabla, y los resúmenes con FILTRAR, ORDENAR y UNICOS en otra hoja.

4. Escribes =UNICOS(A2:A500) y aparece #¡DESBORDAMIENTO!. ¿Qué pasa?

Hay demasiados datos.
Alguna celda del camino donde debe aparecer el resultado no está vacía.
La función no existe en tu Excel.

El resultado necesita celdas libres. Si la función no existiera, verías #¿NOMBRE?.

Reto: un panel de búsqueda de ventas

Con una tabla Ventas (Fecha, Región, Vendedor, Producto, Importe) de al menos 50 filas:

En una hoja nueva, crea en B1 una lista desplegable de regiones (lección 8).
Debajo, muestra todas las ventas de la región elegida, ordenadas de mayor a menor importe.
Al lado, la lista de vendedores únicos de esa región con su total vendido.
Arriba, el ticket promedio de la región con LET.

Ver una solución

A4: =ORDENAR(FILTRAR(Ventas; Ventas[Región]=B1; "Sin ventas"); 5; -1)
H4: =ORDENAR(UNICOS(FILTRAR(Ventas[Vendedor]; Ventas[Región]=B1)))
I4: =SUMAR.SI.CONJUNTO(Ventas[Importe]; Ventas[Vendedor]; H4#; Ventas[Región]; B1)
D1: =LET(v; FILTRAR(Ventas[Importe]; Ventas[Región]=B1); SUMA(v)/CONTAR(v))

Resumen

Una fórmula puede desbordarse a muchas celdas; G2# hace referencia a todo el resultado.
UNICOS, ORDENAR y FILTRAR reemplazan muchas tareas manuales.
En FILTRAR, * = Y, + = O.
LET nombra pasos intermedios para escribir fórmulas legibles.

En la próxima lección aprenderás Power Query: importar y limpiar datos automáticamente, para que el informe que hoy te lleva una hora se actualice con un clic.