Contar valores únicos respetando filtros manuales: SUBTOTALES + MAP al rescate

La pregunta que parece fácil

Miki lanzó a la comunidad una duda que suena inocente: ¿cómo contar valores únicos en una columna cuando hay filtros manuales aplicados a la tabla?

El problema tiene dos capas que se dan de bruces la una con la otra:

• UNICOS y CONTAR.SI cuentan estupendamente valores distintos, pero ignoran por completo si una fila está oculta por un filtro.
• AGREGAR, que sí respeta los filtros, no tiene equivalente a "contar únicos" entre sus diecinueve funciones disponibles.

Es decir: la función que sabe contar únicos no ve los filtros, y la que ve los filtros no sabe contar únicos.

La clave: SUBTOTALES es la única que "ve" los filtros

Nacho apuntó la línea de ataque, y es el concepto central de todo el caso. SUBTOTALES es la única función nativa de Excel que sabe si una fila está oculta por un filtro.

El truco consiste en usarla no para su propósito habitual (calcular totales que respetan filtros), sino como detector de visibilidad celda a celda. Si consigues un vector de unos y ceros que diga qué filas se ven, el resto es filtrar y contar únicos con normalidad.

A partir de ahí, la comunidad desarrolló tres implementaciones.

Enfoque 1: la versión clásica con DESREF

Gerson propone la solución tradicional, la que funcionaba mucho antes de que existieran las matrices dinámicas:

=FILAS(
    UNICOS(
        SI(
            SUBTOTALES(3; DESREF(A1; FILA(A2:A20)-1; ));
            A2:A20
        )
    )
) - 1

La pieza ingeniosa es DESREF(A1; FILA(A2:A20)-1; ). En lugar de pasarle a SUBTOTALES un rango entero (que devolvería un único número), DESREF genera una referencia por cada fila, obligando a SUBTOTALES a evaluarlas de una en una.

SUBTOTALES con la función 3, que corresponde a CONTARA, devuelve 1 si la celda es visible y 0 si el filtro la oculta. El SI deja pasar solo las visibles, UNICOS quita repetidos y FILAS cuenta.

¿Y el menos uno del final? Las filas ocultas devuelven FALSO, que UNICOS considera un valor distinto más. Ese ajuste descuenta esa entrada fantasma.

Enfoque 2: la versión moderna con MAP

Leo propone el mismo concepto con herramientas actuales, y el resultado se lee mucho mejor:

=FILAS(
    UNICOS(
        FILTRAR(
            A2:A21;
            MAP(A2:A21; LAMBDA(x; SUBTOTALES(103; x)))
        )
    )
)

MAP hace lo que DESREF hacía a la fuerza: recorre el rango celda a celda y aplica SUBTOTALES a cada una por separado. El resultado es un vector limpio de unos y ceros.

Aquí se usa la función 103 en lugar de la 3. Ambas cuentan celdas no vacías, pero las de la serie 100 ignoran además las filas ocultas manualmente, no solo las filtradas. Es la opción más estricta y normalmente la que quieres.

Con el vector de visibilidad, FILTRAR se queda solo con las filas visibles, UNICOS elimina repetidos y FILAS cuenta. Sin ajuste final, porque FILTRAR descarta las filas ocultas en vez de convertirlas en FALSO.

Enfoque 3: MAP sin FILTRAR

Una tercera variante combina las dos ideas anteriores, usando MAP para la visibilidad pero SI en lugar de FILTRAR:

=FILAS(
    UNICOS(
        SI(
            MAP(A2:A21; LAMBDA(x; SUBTOTALES(3; x)));
            A2:A21
        )
    )
) - 1

Vuelve a aparecer el menos uno, por el mismo motivo que en la primera versión: SI sin rama falsa deja FALSO en las filas ocultas, y UNICOS lo cuenta como un valor más.

Sirve bien de puente didáctico entre las otras dos: moderniza el detector de visibilidad pero mantiene la estructura clásica.

Funciones clave

• SUBTOTALES — la única función nativa que distingue filas visibles de filas ocultas por filtro. Con función 3 o 103 sobre una sola celda, actúa como detector de visibilidad.
• DESREF — genera una referencia por fila para forzar la evaluación individual. El recurso clásico, antes de MAP.
• MAP — aplica una LAMBDA elemento a elemento. Sustituto natural y mucho más legible de DESREF para este patrón.
• FILTRAR — se queda con las filas visibles. Evita el ajuste del menos uno que exige el SI.
• UNICOS y FILAS — la pareja de siempre para contar valores distintos.

Conclusión

El aprendizaje que se lleva uno de este caso va más allá de contar únicos. Es este: SUBTOTALES aplicado celda a celda te devuelve un vector de visibilidad, y con ese vector puedes hacer que cualquier función dinámica respete los filtros manuales de la tabla.

Contar únicos es solo el primer ejemplo. La misma técnica sirve para promediar únicos, para concatenar los valores visibles o para alimentar un AGRUPARPOR que solo vea lo filtrado.

Casos así son los que mejor representan a la comunidad de InflueXcel: alguien pregunta por un recuento y acaba saliendo un patrón reutilizable en decenas de escenarios distintos.

Más casos con estas funciones

• LAMBDA
• FILTRAR
• UNICOS
• DESREF
• SUBTOTALES

Más contenido de Excel en InflueXcel

• Valores unicos en columna filtrada con SUBTOTALES y DESREF Caso
  Surge una pregunta rapida pero muy practica en la comunidad: como obtener los valores unicos de una columna de tabla cuando los datos estan
• Repetir un rango N veces según marca con MAP y CONTAR.SI Caso
  Un usuario necesita repetir un listado de locales (D2:D10) un número variable de veces para cada marca de la columna B. El número de repetic
• Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA Caso
  Hector comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
• Readmisión de pacientes en 48h: Power Query, LAMBDA/MAP y AGRUPARPOR Caso
  Andrés Rojas plantea un reto real de datos clínicos: a partir de una tabla con más de un millón de registros de urgencias (IdPaciente, Fecha
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• Reto de agrupación y pivotado: AGRUPARPOR vs ARCHIVOMAKEARRAY vs MAP Caso
  Leandro trae un reto de internet al grupo: a partir de una tabla con claves repetidas y valores, generar una tabla pivotada donde cada clave
• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Funciones personalizadas en Excel: LET, LAMBDA y recursividad Tutorial
  Cómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta pá
• Timeline chart dinámico en Excel con etiquetas que no se pisan Tutorial
  Un timeline en Excel se ve muy bien hasta que añades el cuarto evento y las etiquetas empiezan a solaparse. Colocarlas a mano funciona una v
• Buscar todos los valores coincidentes cuando BUSCARX solo devuelve uno Caso
  Juan se encuentra con un problema frecuente: tiene una tabla de búsqueda con claves duplicadas (A aparece varias veces con valores distintos