Contar registros únicos en rangos grandes

En la nota anterior sobre registros únicos vimos cómo encarar el recuento usando tablas dinámicas. Esta vez Eduardo, el de la nota anterior, apareció en mi oficina y sin mucha ceremonia se sentó del otro lado del escritorio mirándome con el ceño fruncido.

- ¿Te acordás del asunto de contar registros únicos con tablas dinámicas?
- Si, seguro. Publiqué una nota en el blog.
- La leí, pero tengo otro problema.
- Si...
- Tengo una tabla con ventas a clientes. Cada cliente aparece muchas veces. Quiero poner en una celda cuántos clientes hay la lista, es decir, sin repeticiones.
- Ah! podés leer mi nota sobre contar valores únicos en Excel.
- La leí y apliqué la fórmula, pero lo lleva mucho tiempo calcular.
- ¿Cuántos registros hay en tu tabla?
- Más o menos, cuatrocientos mil.
- ¿¡¡Cua-tro-cientos-mil!!?, respondí pronunciando cada una de las sílabas por separado para enfatizar.
- Si, y cada mes agrego más.

Lo que Eduardo descubrió es que CONTAR.SI no es la más veloz de las funciones de Excel. Aplicar CONTAR.SI a un rango de 400 mil celdas es una de las mejores maneras de explicar el concepto de eternidad.

Como Eduardo insiste en no usar Filtro Avanzado o Quitar Duplicados y, además, está prohibido mencionar Access en su presencia, tuve que buscar alguna otra solución.

La fórmulas que intentaba usar Eduardo son:

=SUMA(1/CONTAR.SI(miRango,miRango)) en forma matricial
=SUMAPRODUCTO((miRango<>"")/CONTAR.SI(miRango,miRango))

Como puede observarse, ambas fórmulas utilizan la función CONTAR.SI, por lo que la solución que le propuse fue esta UDF (función definida por el usuario)

Function contar_unicos(rngSeleccion As Range)

    Dim collUnicos As New Collection
    Dim rngCell As Range


    On Error Resume Next
    For Each rngCell In rngSeleccion
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0

    contar_unicos = collUnicos.Count

End Function

Usando una macro que Charles Williams de Decisions Models tuvo la gentileza de colgar en el sitio de artículos técnicos del Office Dev Center, medí el tiempo de cálculo de las distintas fórmulas.

Para investigar el tiempo de cálculo de las distintas opciones usé un ejemplo con un rango de 20 mil celdas conteniendo números aleatorios. El examen del tiempo de cálculo de las fórmulas lo hice usando rangos de 5 mil, 10 mil y 20 mil registros

El valor en la celda G2 permite controlar el tamaño del rango; la celda G3 contiene la fórmula a examinar y la celda G4 recibe el valor del tiempo de cálculo hecho con la macro (apretando el botón "Tiempo de cálculo").

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), estos fueron los resultados en segundos:

Podemos ver que la UDF Contar_Unicos es mucho más rápida que las que usan CONTAR.SI. Además, la diferencia crece con la cantidad de celdas a procesar. Con 5 mil celdas, Contar_Unicos es casi 33 veces más rápida que las otras; con 10 mil celdas la diferencia llega a 78 veces y con 20 mil celdas 160 veces.

Otro detalle interesante es que el tiempo de cálculo de las fórmulas no es proporcional a la cantidad de registros. Al aumentar la cantidad de registros en un 100% (de 5000 a 10000), el tiempo de cálculo de las fórmulas con CONTAR.SI crece en un 300%; un aumento del 300% en los registros (de 5000 a 20000 celdas) resulta en un aumento del 1500% en el tiempo de cálculo.

El tiempo de cálculo de la UDF con 400 mil registros fue 3 segundos.