Anomalías en Datos con la Ley de Benford y Excel

Anomalías en Datos con la Ley de Benford y Excel

La Ley de Benford es una herramienta poderosa utilizada por auditores y analistas de datos para detectar fraudes, errores y patrones inusuales en grandes volúmenes de datos. En este tutorial, cubriremos cómo verificar la autenticidad de los datos y cómo detectar errores utilizando técnicas avanzadas en Excel

La Ley de Benford es una ley matemática que afirma que, dado un conjunto de números lo suficientemente grande, la cantidad de valores que empiezan por 1 es muy superior al resto. De hecho, existe toda una escala: los que empiezan por 1 representan aproximadamente el 30%, los que empiezan por 2 rondan el 18%, y así sucesivamente hasta el 9. Aunque pueda parecer una mera curiosidad matemática, esta ley tiene aplicaciones muy reales, sobre todo en el ámbito de la auditoría de datos. En este tutorial veremos cómo aplicarla en Excel para detectar anomalías.

Para qué sirve la Ley de Benford

Uno de los casos de uso más habituales está en contabilidad. Se toman todos los importes (por ejemplo, las compras de una empresa) y se pasan por el filtro de la Ley de Benford para comprobar si su composición la cumple. Si no la cumple, hay algo raro:

  • No siempre se trata de un fraude, que es la primera idea que viene a la cabeza.
  • Puede deberse a algún tipo de práctica u operativa que influye en cómo se generan los números.
  • También puede señalar errores, una de las aplicaciones más importantes para asegurar que la calidad de los datos es la correcta.

Calcular la distribución teórica de Benford

Para el ejercicio se parte de valores de contabilidad reales con cliente, importe (compras de distintas empresas) y año. El objetivo es comprobar si esos importes cumplen la ley.

La probabilidad teórica de Benford se calcula con un logaritmo de base 10 aplicado a 1 + 1/n, donde n es la cifra (del 1 al 9) cuya probabilidad queremos conocer. En Excel:

  1. Se genera una secuencia del 1 al 9 con la función SECUENCIA.
  2. Se aplica sobre esa secuencia la fórmula del logaritmo para obtener la tabla de Benford de referencia.

Esta tabla también puede consultarse directamente en internet, ya que muchos sitios la tienen calculada; sirve como referencia con la que comparar nuestros números.

Extraer la primera cifra de cada importe

El siguiente paso es obtener el dígito que aparece a la izquierda de cada número. Para ello se trata el importe como si fuera texto y se extrae el primer carácter por la izquierda con la función IZQUIERDA, indicando que coja 1 carácter.

Al estar los datos en una tabla, al pasar toda la columna como argumento (en lugar de una sola celda), la función se aplica automáticamente a todas las filas del rango, devolviendo la primera cifra de cada importe.

Contar y calcular porcentajes

Ahora hay que saber cuántos números empiezan por cada cifra. Se usa CONTAR.SI:

  • Rango: la columna de primeras cifras. Se marca la celda y se añade el símbolo # para que lo tome como rango derramado completo.
  • Criterio: la secuencia del 1 al 9, también con # para que evalúe cada dígito.

Esto devuelve cuántos importes empiezan por cada número. Para obtener el porcentaje en lugar del valor absoluto, se divide cada conteo (con #) entre la suma del mismo rango:

  • Al usar funciones que agregan, como CONTAR o SUMAR, aunque se les pase un rango calculan el total, no fila por fila.
  • Se multiplica por 100 para que los porcentajes sean comparables con la tabla de Benford.

Comparar las dos distribuciones

Con los porcentajes reales y los teóricos se hacen dos comprobaciones.

Comparación visual

Se monta un gráfico con ambas columnas. Para mejorarlo se convierte en un gráfico combinado: la distribución teórica de Benford queda como una máscara alrededor de la columna que de verdad interesa.

  • Se ajusta el ancho del rango de las barras: cuanto menor sea el ancho del rango, más anchas son las columnas (por ejemplo, un 25%).
  • A la serie de referencia se le da un relleno más grisáceo o se muestra como línea punteada, para que no destaque tanto.

Comparación numérica

Se utiliza la función PRUEBA.CHICU (prueba estadística de chi cuadrado), que mide la bondad de ajuste entre dos distribuciones. Recibe el rango real y el rango esperado y devuelve un porcentaje. En el conjunto completo da casi un 100%, lo que indica que estos datos de contabilidad cumplen bien la Ley de Benford: a nivel de auditoría, la calidad de los datos sería buena.

Filtrar por cliente para encontrar anomalías

Para afinar, se filtran los datos por cada cliente con la función FILTRAR, manteniendo la columna de valores y añadiendo un filtro donde la columna cliente sea igual a cada cliente. Así se recalcula todo para cada uno:

  • Los clientes 1 y 2 dan resultados cercanos al 100%, con distribución pareja.
  • El cliente 3 baja a un valor más bajo y aparece una especie de rotura de la distribución esperada: hay más números empezando por 3 o 4 de lo previsto.

Llegados a este punto, no hay que alarmarse: no implica fraude ni mala intención. Pueden ser mil razones, desde errores hasta operativas que condicionan la generación de números. Pero si fueras auditor, en lugar de revisar toda la contabilidad de golpe, irías directamente a esos clientes donde la diferencia respecto a Benford es mayor.

Conclusión

La Ley de Benford combina funciones nativas de Excel (SECUENCIA, IZQUIERDA, CONTAR.SI, FILTRAR y PRUEBA.CHICU) para crear una herramienta sencilla pero potente de control de calidad. Tiene un carácter más estadístico de lo habitual, pero permite detectar de un vistazo dónde merece la pena profundizar cuando auditamos la fiabilidad de un conjunto de datos.

Más contenido de Excel en InflueXcel