EXCEL: Cuente números largos sin CONTAR.SI

Prefacio

Esta es una introducción molestamente larga, pero el contexto es importante, lo siento.

Si intenta contar números muy largos (más de 16 dígitos) en un rango con CONTAR.SI, puede ver resultados incorrectos, debido a un error en cómo ciertas funciones manejan números largos, incluso cuando esos números se almacenan como texto. Considere la pantalla a continuación. Todos los recuentos en la columna D son incorrectos; aunque cada número en la columna B es único, el recuento devuelto por CONTAR. Sugiero que estos números son duplicados.

 =CONTAR.SI(data,B5)

Este problema está relacionado con cómo Excel maneja los números. Excel solo puede manejar 15 dígitos significativos, y si ingresa un número con más de 15 dígitos en Excel, verá los dígitos finales silenciosamente convertidos a cero. El problema de conteo mencionado anteriormente surge de este límite.

Normalmente, puede evitar este límite ingresando números largos como texto, ya sea comenzando el número con una comilla simple ('999999999999999999) o formateando las celdas como Texto antes de ingresar. Mientras no necesite realizar operaciones matemáticas en un número, esta es una buena solución, y le permite ingresar números extra largos para cosas como números de tarjetas de crédito y números de serie sin perder ningún número.

Sin embargo, si intenta usar CONTAR.SI para contar un número con más de 15 dígitos (incluso cuando se almacena como texto), puede ver resultados poco confiables. Esto sucede porque CONTAR.SI convierte internamente el valor largo de nuevo en un número en algún momento durante el procesamiento, lo que activa el límite de 15 dígitos descrito anteriormente. Sin todos los dígitos presentes, algunos números pueden contarse como duplicados cuando se cuentan con CONTAR.SI.

Solución

Una solución es reemplazar la fórmula CONTAR.SI con una fórmula que use SUMA o SUMAPRODUCT. En el ejemplo que se muestra, la fórmula en E5 se ve así:

=SUMAPRODUCTO(--(data=B5))

La fórmula utiliza los "datos" de rango con nombre (B5: B9) y genera el recuento correcto para cada número con SUMAPRODUCTO.

Cómo funciona esta fórmula

Primero, la expresión dentro de SUMAPRODUCTO compara todos los valores en el rango "datos" con el valor de la columna B en la fila actual. Esto da como resultado una serie de resultados VERDADEROS / FALSOS.

=SUMAPRODUCTO(--(data=B5))
=SUMAPRODUCTO(--({VERDADERO;FALSO;FALSO;FALSO;FALSO}))

A continuación, el doble negativo obliga a los valores VERDADERO / FALSO a valores 1/0.

=SUMAPRODUCTO({1;0;0;0;0})

Finalmente, SUMAPRODUCTO simplemente suma los elementos de la matriz y devuelve el resultado.

Variante de fórmula de matriz

También puede usar la función SUMA en lugar de SUMAPRODUCT, pero esta es una fórmula de matriz y debe ingresarse con control + shift + enter:

{=SUMA(--(B:B=B5))}

Otras funciones con este problema

No lo he verificado yo mismo, pero parece que varias funciones tienen el mismo problema, incluidas SUMAR.SI, SUMAR.SIS, CONTAR.SI, CONTAR.SIS, PROMEDIO.SI y PROMEDIO.SIS.