DISTINCTCOUNT con AGRUPARPOR: contar valores únicos como en DAX

El problema: AGRUPARPOR no sabe contar únicos

AGRUPARPOR trae de serie las agregaciones habituales: SUMA, CONTARA, PROMEDIO, MAX, MIN. Con eso cubres la mayoría de resúmenes. Pero falta una que en Power BI usas cada día: el conteo de valores únicos, el DISTINCTCOUNT de DAX.

Dos miembros de la comunidad plantearon el mismo reto en hilos separados, con una semana de diferencia:

• El primero necesitaba agrupar por sucursal mostrando cuántos clientes distintos hay y la facturación total. Ojo al matiz: no cuántas facturas, cuántos clientes. Un cliente con quince facturas cuenta una vez.
• El segundo quería agrupar por categoría con la suma de importes, el número de registros y el conteo de NIF únicos, además de cabeceras personalizadas.

Excel no trae una función nativa para esto dentro de AGRUPARPOR. Pero la función acepta agregaciones a medida, y ahí está la puerta de entrada.

Solución 1 (Nacho): una LAMBDA como función de agregación

El detalle que mucha gente desconoce: AGRUPARPOR acepta un LAMBDA en el argumento de agregación. No estás limitado a las funciones de la lista; puedes pasarle cualquier función que reciba un conjunto de valores y devuelva uno.

Contar únicos es entonces trivial: cuenta los elementos del resultado de UNICOS.

=AGRUPARPOR(F2:F80;
    APILARH(D2:D80; S2:S80);
    APILARV(
        APILARH(LAMBDA(X; CONTARA(UNICOS(X))); SUMA);
        APILARH("CLIENTES"; "FACTURACION")
    );;;; filtroImportesNoNulos)

Dos cosas ocurren aquí a la vez:

El LAMBDA personalizado. LAMBDA(X; CONTARA(UNICOS(X))) recibe los valores del grupo, quita duplicados con UNICOS y los cuenta con CONTARA. Eso es DISTINCTCOUNT, escrito en una línea.

Varias agregaciones y sus cabeceras, apiladas. APILARH combina la función personalizada con SUMA para pedir dos columnas de resultado. Y el APILARV de encima añade una segunda fila con los textos de cabecera. Este es el patrón para nombrar tus columnas: la primera fila del bloque son las funciones, la segunda los títulos.

El último argumento es un filtro que descarta los registros con importe nulo antes de agrupar.

Es la solución más legible de las dos, y la que recomendaría por defecto.

Solución 2 (Leo): numerar las combinaciones únicas

Leo atacó el segundo caso por otro camino, más indirecto pero muy instructivo:

=LET(
    a; T4:.T2000; b; AB4:.AB2000; c; AC4:.AC2000;
    AGRUPARPOR(
        APILARH(b; a);
        APILARH(c; c; COINCIDIRX(a & b; UNICOS(a & b)));
        APILARH(SUMA; CONTARA; SINGLE);; 0
    ))

El truco está en la tercera columna de valores: COINCIDIRX(a & b; UNICOS(a & b)).

Al concatenar las dos columnas se forma una clave compuesta. UNICOS saca la lista de combinaciones distintas, y COINCIDIRX devuelve la posición de cada fila dentro de esa lista. En la práctica es un número ordinal: todas las filas que comparten la misma combinación reciben el mismo número, y combinaciones distintas reciben números distintos.

Con eso, contar únicos se convierte en quedarse con un valor por grupo, que es lo que hace SINGLE (equivalente a tomar el mínimo). Es un DISTINCTCOUNT construido con álgebra de posiciones en lugar de con una función a medida.

Fíjate también en la notación de rango con el punto (T4:.T2000): fuerza a Excel a tratar el rango como fijo aunque esté dentro de una tabla que crece.

Solución 3: cabeceras personalizadas con EXPANDIR y SI.ND

La evolución que Leo compartió después resuelve la parte estética, que en un informe real no es menor:

=LET(
    a; T4:.T2000; b; AB4:.AB2000; c; AC4:.AC2000;
    g; AGRUPARPOR(APILARH(b; a);
        APILARH(c; c; COINCIDIRX(a & b; UNICOS(a & b)));
        APILARV(APILARH(SUMA; CONTARA; MIN); textosCabecera);; 0; 5);
    SI.ND(EXPANDIR(textosCabecera; 2; 3); g))

La idea final es ingeniosa: se expande el bloque de cabeceras hasta las mismas dimensiones que la tabla de resultados. Al expandir a un tamaño mayor del que tiene, las posiciones sobrantes se rellenan con el error de valor no disponible. Y entonces SI.ND sustituye cada uno de esos errores por el dato correspondiente de AGRUPARPOR.

El resultado: las cabeceras que tú escribiste arriba, los datos calculados debajo, todo en una sola matriz derramada. Los textos de cabecera se escriben como constante matricial entre llaves, con el separador de columnas que corresponda a tu configuración regional.

Funciones clave

• AGRUPARPOR — agrupa y agrega; acepta funciones personalizadas, varias agregaciones a la vez y un filtro final.
• LAMBDA como agregación — la vía directa para inventarte la función que Excel no trae.
• UNICOS con CONTARA — la pareja que implementa DISTINCTCOUNT.
• COINCIDIRX sobre UNICOS — asigna un ordinal a cada combinación distinta; útil cuando necesitas numerar grupos.
• APILARH y APILARV — combinan agregaciones y cabeceras en el mismo argumento.
• EXPANDIR con SI.ND — el patrón para superponer cabeceras propias sobre un resultado dinámico.

Conclusión

La conclusión práctica es corta: AGRUPARPOR no está limitada a las funciones de agregación que trae. En cuanto sabes que admite un LAMBDA, deja de haber agregaciones imposibles. Contar únicos, contar los que superan un umbral, calcular la mediana ponderada; todo cabe.

De las dos vías, la del LAMBDA personalizado es la que usarías el 90% de las veces. La de numerar combinaciones con COINCIDIRX merece conocerse porque resuelve casos donde el criterio de unicidad combina varias columnas y quieres tenerlo explícito.

Que dos personas plantearan el mismo problema con una semana de diferencia dice bastante de lo común que es. En la comunidad de InflueXcel estos hilos acaban siempre igual: con dos o tres soluciones sobre la mesa y la explicación de por qué cada una funciona.

Más casos con estas funciones

• LET
• LAMBDA
• APILARH
• APILARV
• AGRUPARPOR
• COINCIDIRX
• UNICOS

Más contenido de Excel en InflueXcel

• Optimización de REDUCE+APILARV con LAMBDA recursiva en bisección Caso
  Alejandro plantea un reto de rendimiento interesante: tiene una fórmula LET enorme que calcula la permanencia de carga en puerto por matrícu
• Error #CALC con MAP y AGRUPARPOR: solución con REDUCE+APILARV Caso
  Nuevo reto de Excel resuelto por la comunidad: un usuario necesita aplicar AGRUPARPOR de forma iterativa sobre un rango de códigos de cuenta
• 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á
• 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
• Generar la serie de Fibonacci con REDUCE, APILARV y LAMBDA Caso
  Interesante ejercicio compartido en la comunidad: generar los primeros N números de la serie de Fibonacci usando exclusivamente fórmulas de
• 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
• Agrupar y limpiar referencias con AGRUPARPOR, DIVIDIRTEXTO y APILARV Caso
  Interesante problema de un miembro que trabaja con una tabla de "Push Money cancelado" con más de 150 productos. Las referencias vienen dupl
• Formato dinámico AGRUPARPOR PIVOTARPOR Tutorial
  Aplicar formato condicional a las matrices calculadas es fundamental para conseguir que su legibilidad sea óptima
• Excel Inventario LIFO/FIFO Técnica Avanzada con LAMBDA Tutorial
  Cómo aplicar la función recursiva en Excel para transformar los tediosos procesos de valoración de inventario, mediante los métodos LIFO y F
• Identificar proveedor en asientos contables con BUSCARX y BYROW Caso
  Juan comparte en el grupo una fórmula de John Vergara que le ha "salvado la vida laboral" para un problema clásico de contabilidad: en un di