Valores unicos en columna filtrada con SUBTOTALES y DESREF

Surge una pregunta rapida pero muy practica en la comunidad: como obtener los valores unicos de una columna de tabla cuando los datos estan filtrados con segmentaciones. El uso directo de UNICOS no funciona porque esta funcion ignora los filtros aplicados a la tabla.

Un miembro sugiere combinar SUBTOTALES con un numero de funcion que cuente (para detectar filas visibles vs ocultas), lo cual permite discriminar que filas estan filtradas.

Miki propone el enfoque mas directo: envolver UNICOS con FILTRAR para preseleccionar las filas visibles. Sin embargo, el autor aclara que el filtro se genera dinamicamente segun la seleccion de segmentaciones, lo que complica usar FILTRAR con criterios fijos.

Finalmente, con ayuda de ChatGPT, llega a esta formula que combina ambos enfoques:

``
=FILAS(
UNICOS(
FILTRAR(
Tabla[Campo];
SUBTOTALES(103;
DESREF(Tabla[Campo];
FILA(Tabla[Campo]) - MIN(FILA(Tabla[Campo]));
0; 1; 1)
)
)
)
)
`

La clave esta en SUBTOTALES(103; ...) que devuelve 1 para las filas visibles y 0 para las ocultas por el filtro. DESREF genera referencias individuales a cada celda para que SUBTOTALES pueda evaluarlas fila por fila. Con eso, FILTRAR selecciona solo los valores visibles y UNICOS` elimina duplicados. Un patron muy util cuando se trabaja con segmentaciones de datos.

El problema: UNICOS no ve los filtros

Pregunta rápida en la comunidad, de esas que parecen de un minuto y no lo son: cómo sacar los valores únicos de una columna cuando la tabla está filtrada con segmentaciones.

Lo natural es escribir UNICOS(Tabla[Campo]) y esperar que respete lo que hay en pantalla. No lo hace. UNICOS trabaja sobre el rango completo y los filtros le dan igual: le da lo mismo que la fila esté oculta por una segmentación, por un autofiltro o porque alguien la escondió a mano. Devuelve los únicos de todo, no de lo visible.

Es la misma diferencia que hay entre SUMA y SUBTOTALES, y merece la pena tenerla clara: la mayoría de funciones de Excel operan sobre datos, no sobre lo que se ve.

Primer intento: envolver en FILTRAR

Miki propuso lo más directo: si el problema es que hay que preseleccionar las filas visibles, se envuelve UNICOS en un FILTRAR y listo.

El enfoque es correcto y en muchos casos es la respuesta buena. Pero aquí se topó con el detalle que hacía especial el caso: el filtro no es fijo, lo genera el usuario moviendo segmentaciones. Para escribir un FILTRAR necesitas un criterio, y aquí el criterio cambia con cada clic. No hay condición que escribir.

Así que la pregunta se reformula, y así es como se vuelve interesante: ¿cómo le preguntas a Excel qué filas están visibles ahora mismo?

La respuesta: SUBTOTALES(103; ...) fila a fila

SUBTOTALES es de las pocas funciones que sí saben qué está oculto. Con el código de función 103 (contar valores no vacíos, ignorando filas ocultas) devuelve 1 si la fila está visible y 0 si está filtrada. Justo el vector de unos y ceros que FILTRAR necesita como criterio.

El obstáculo es que SUBTOTALES está pensada para evaluar un rango entero y devolver un número, no para evaluar fila por fila. Ahí entra DESREF, que genera una referencia distinta para cada fila:

=FILAS(
  UNICOS(
    FILTRAR(
      Tabla[Campo];
      SUBTOTALES(103;
        DESREF(Tabla[Campo];
          FILA(Tabla[Campo]) - MIN(FILA(Tabla[Campo]));
          0; 1; 1)
      )
    )
  )
)

Leído de dentro hacia fuera:

  1. FILA(Tabla[Campo]) devuelve el número de fila de cada celda de la columna. Restándole MIN(FILA(...)) se convierte en un desplazamiento que empieza en 0: 0, 1, 2, 3...
  2. DESREF usa ese desplazamiento para generar una referencia de una fila y una columna por cada elemento. Ya no hay un rango, hay una matriz de referencias individuales.
  3. SUBTOTALES(103; ...) evalúa cada una de esas referencias por separado y devuelve el vector de unos y ceros.
  4. FILTRAR se queda con las filas visibles, UNICOS elimina los duplicados y FILAS cuenta el resultado. Si lo que quieres es la lista y no el recuento, basta con quitar ese FILAS de fuera.

El autor llegó a esta combinación con ayuda de ChatGPT, después de que la comunidad le hubiera puesto sobre la mesa las dos piezas: SUBTOTALES para detectar visibilidad y FILTRAR para preseleccionar. Vale la pena señalarlo porque es el reparto habitual: la comunidad aporta el camino, la herramienta ayuda a ensamblarlo.

Funciones clave

  • SUBTOTALES: la puerta de entrada a lo que está visible. Los códigos de 101 a 111 ignoran las filas ocultas; los de 1 a 11, no. El 103 cuenta valores no vacíos.
  • DESREF: genera referencias desplazadas. Aquí sirve para partir un rango en referencias de una sola celda.
  • FILTRAR: se queda con las filas cuyo criterio es verdadero. Un vector de unos y ceros le sirve perfectamente.
  • UNICOS: elimina duplicados. Sobre datos, nunca sobre lo visible.
  • FILA y MIN: la pareja que convierte números de fila absolutos en desplazamientos relativos que empiezan en cero.

Una advertencia sobre DESREF

DESREF es una función volátil: se recalcula con cualquier cambio del libro, no solo cuando cambian sus datos. En una tabla pequeña ni lo notas, pero sobre miles de filas y combinada con matrices dinámicas puede poner el libro lento. Si el rendimiento se resiente, merece la pena buscar una alternativa no volátil apoyada en INDICE.

Conclusión

El caso deja un patrón reutilizable: para hacer que cualquier función matricial respete los filtros, construye el vector de visibilidad con SUBTOTALES y úsalo como criterio de FILTRAR. Sirve igual para UNICOS, para ORDENAR, para AGRUPARPOR o para un CONTAR.SI. Y sale de una duda de dos líneas en el grupo de Influexcel, que es de donde salen casi siempre las cosas que luego usas cada semana.

Más casos con estas funciones

Más contenido de Excel en InflueXcel