Generación de números aleatorios que satisfagan la ley de Benford
La ley de Benford, también llamada ley del primer dígito (aunque se puede aplicar más allá de ese primer dígito), se utiliza a veces para intentar detectar fraudes, algo que a mí me ha parecido siempre un poco absurdo porque no resulta demasiado complicado generar números que la satisfagan. En este trabajo veremos cómo conseguirlo en Excel utilizando tanto funciones de hoja de cálculo como una función VBA.
Lógicamente, lo primero que necesitamos es saber qué porcentaje de apariciones predice la ley de Benford para cada uno de los números 0 a 9 dependiendo de su posición en el número. Esta tabla muestra esos porcentajes:
| Probabilidades según la ley de Benford | |||||
| Número | 1er. dígito | 2º dígito | 3er. dígito | 4º dígito | 5º dígito |
| 0 | 11,9679269% | 10,1784365% | 10,0176147% | 10,0017592% | |
| 1 | 30,1029996% | 11,3890103% | 10,1375977% | 10,0136888% | 10,0013681% |
| 2 | 17,6091259% | 10,8821499% | 10,0972198% | 10,0097673% | 10,0009771% |
| 3 | 12,4938737% | 10,4329560% | 10,0572932% | 10,0058500% | 10,0005862% |
| 4 | 9,6910013% | 10,0308202% | 10,0178088% | 10,0019371% | 10,0001953% |
| 5 | 7,9181246% | 9,6677236% | 9,9787576% | 9,9980285% | 9,9998044% |
| 6 | 6,6946790% | 9,3374736% | 9,9401310% | 9,9941242% | 9,9994136% |
| 7 | 5,7991947% | 9,0351989% | 9,9019207% | 9,9902241% | 9,9990228% |
| 8 | 5,1152522% | 8,7570054% | 9,8641184% | 9,9863284% | 9,9986321% |
| 9 | 4,5757491% | 8,4997352% | 9,8267164% | 9,9824369% | 9,9982414% |
Las fórmulas para calcular estos porcentajes en Excel son:
-
Para el 1er. dígito, en B4: =LOG(1+1/(FILA()-3)) ->copiar y pegar hasta B12
-
Para el 2º dígito, en C3: {=SUMA(LOG((FILA(INDIRECTO("1:9"))*10+FILA()-2)/(FILA(INDIRECTO("1:9"))*10+FILA()-3)))} -> copiar y pegar hasta C13
-
Para el 3er. dígito, en D3: {=SUMA(LOG((FILA(INDIRECTO("10:99"))*10+FILA()-2)/(FILA(INDIRECTO("10:99"))*10+FILA()-3)))} -> copiar y pegar hasta D13
-
Para el 4º dígito, en E3: {=SUMA(LOG((FILA(INDIRECTO("100:999"))*10+FILA()-2)/(FILA(INDIRECTO("100:999"))*10+FILA()-3)))} -> copiar y pegar hasta E13
-
Para el 5º dígito, en F3:{=SUMA(LOG((FILA(INDIRECTO("1000:9999"))*10+FILA()-2)/(FILA(INDIRECTO("1000:9999"))*10+FILA()-3)))} -> copiar y pegar hasta F13
(nota: las llaves indican que la fórmula es matricial, por lo que hay que introducirla pulsando mayúsculas control entrada la mismo tiempo; pero las llaves no hay que incluirlas, deberían aparecer automáticamente al introducir la fórmula usando la combinación de teclas indicada).
A partir del 5º o 6º dígito (incluso del 3er. o el 4º en situaciones normales) las probabilidades son prácticamente las mismas para todos los números, por lo que la ley de Benford no tiene demasiado sentido para ellos, salvo que se trate de muestras enormes de números; pero, en cualquier caso, siguiendo la progresión 10:99, 100:999, 1000:9999, etc. de las fórmulas, sería posible, si fuese realmente necesario, calcular qué porcentajes son los previstos por la ley para dígitos más allá del 5º. Hay que tener en cuenta, no obstante, que en Excel 2003 no sería posible calcular a partir del 6º dígito, puesto que el número 99.999 supera las 65.536 filas que tiene esta versión. En las versiones posteriores de Excel, no sería posible calcular a partir del 8º dígito, porque 9.999.999 excede las 1.048.576 filas disponibles.
No tiene demasiado sentido que Excel calcule estos datos cada vez que ejecute un recálculo, puesto que al tratarse de valores constantes serán siempre los mismos, como es obvio. Por lo tanto, una vez obtenidos lo mejor es copiarlos y hacer un pegado especial de sus valores en las mismas celdas donde se encuentren (en este ejemplo, el rango sería B3:F12), con lo que ganaremos algo de rendimiento cuando Excel recalcule.
Puesto que las fórmulas utilizarán la función COINCIDIR(), necesitaremos una tabla cuya primera fila sean ceros y las demás los acumulados para cada uno de los dígitos, hasta llegar al 100%:
| Acumulados (los usan las fórmulas) | ||||
| 1er. dígito | 2º dígito | 3er. dígito | 4º dígito | 5º dígito |
| 0,0000000% | 0,0000000% | 0,0000000% | 0,0000000% | |
| 0,0000000% | 11,9679269% | 10,1784365% | 10,0176147% | 10,0017592% |
| 30,1029996% | 23,3569372% | 20,3160342% | 20,0313035% | 20,0031273% |
| 47,7121255% | 34,2390871% | 30,4132540% | 30,0410708% | 30,0041044% |
| 60,2059991% | 44,6720431% | 40,4705472% | 40,0469208% | 40,0046906% |
| 69,8970004% | 54,7028634% | 50,4883560% | 50,0488579% | 50,0048858% |
| 77,8151250% | 64,3705869% | 60,4671136% | 60,0468864% | 60,0046902% |
| 84,5098040% | 73,7080605% | 70,4072446% | 70,0410106% | 70,0041038% |
| 90,3089987% | 82,7432594% | 80,3091652% | 80,0312347% | 80,0031266% |
| 95,4242509% | 91,5002648% | 90,1732836% | 90,0175631% | 90,0017586% |
| 100,0000000% | 100,0000000% | 100,0000000% | 100,0000000% | 100,0000000% |
En este ejemplo, esta tabla iría en H1:L13.
Dado que ALEATORIO() devuelve un número >0 y <1, para obtener números aleatorios que satisfagan la ley de Benford buscaremos en esta tabla el primer valor (de entre los que correspondan al dígito que deseemos obtener) que sea mayor que el número devuelto por ALEATORIO(), y ese será el número elegido en cada caso.
En cualquier fila vacía (en el ejemplo va en N3) ponemos la fórmula:
=COINCIDIR(ALEATORIO();H$4:H$13)
En la celda situada a su derecha (en este ejemplo O3):
=COINCIDIR(ALEATORIO();I$3:I$13)-1
En la celda a su derecha (P3):
=COINCIDIR(ALEATORIO();J$3:J$13)-1
En la celda a su derecha (Q3):
=COINCIDIR(ALEATORIO();K$3:K$13)-1
Y en la celda a su derecha (R3):
=COINCIDIR(ALEATORIO();L$3:L$13)-1
Ahora copiamos estas celdas N3:R3 y las pegamos hacia abajo, tantas filas como números aleatorios deseemos. Con esto obtendremos números de 5 dígitos que, siempre que la serie sea lo suficientemente grande, tenderán a satisfacer la ley de Benford.
Si se necesitan números más largos, para el 6º dígito y siguientes se puede usar directamente la fórmula:
=ALEATORIO.ENTRE(0;9)
puesto que a partir de ese 6º dígito la posibilidad de aparición de los números 0 a 9 es prácticamente la misma para todos ellos. De hecho, como ya comenté antes, excepto para tamaños enormes de la muestra las posibilidades de aparición de cada número a partir del 3er. o 4º dígito son también casi iguales, como se puede comprobar en la primera tabla.
En el libro de ejemplo (Excel 2010) para estas fórmulas las he utilizado en en rango N3:R10002 para obtener 10.000 números (de 5 cifras) que, como puede comprobarse en la tabla que está en el rango V1:AA13, satisfacen con bastante exactitud la ley de Benford, a pesar de ser números "inventados". Por ejemplo, al guardar el libro la mayor divergencia entre los datos esperados y los reales es de sólo el 0,67%, aunque como es lógico esto cambiará con bastante probabilidad al abrir el libro, puesto que las funciones ALEATORIO() que tiene se recalcularán y los números obtenidos serán otros.
Como ya he dicho, todos los números "inventados" son de 5 cifras. Además, no tienen decimales. Pero es obvio que:
-
Si se desean números de más longitud, como ya dije antes bastaría con añadir cifras por la derecha usando directamente =ALEATORIO.ENTRE(0;9)
-
Si se desean números de menos longitud, bastaría con tomar por la izquierda las cifras que se desease. Incluso esto se podría aleatorizar, utilizando =IZQUIERDA(T3;ALEATORIO.ENTRE(1;5)). Copiando esta fórmula y pegándola hacia abajo hasta donde se desease se obtendrían números de entre una y cinco cifras, y esas longitudes serían aleatorias.
-
Si se desean números con decimales, basta con dividir entre 10número de decimales. Por ejemplo, para obtener 2 decimales la fórmula sería =T3/10^2 (esto es, =T3/100), fórmula que de nuevo podría ser copiada y pegada hacia abajo hasta donde se quisiese.
-
Por ejemplo, la siguiente fórmula devolverá un número de longitud aleatoria entre 2 y 9 dígitos y con hasta dos decimales, y que si se repite el suficiente número de veces satisfará la ley de Benford hasta el 5º dígito: =IZQUIERDA(T3&ALEATORIO.ENTRE(0;9999);ALEATORIO.ENTRE(2;9))/100
Nota: para el primer dígito, es posible utilizar una fórmula matricial, según explico en este enlace
El código de la función VBA es el siguiente:
Private dDígito1 As Variant, dDígito2 As Variant, dDígito3 As Variant, dDígito4 As Variant, dDígito5 As Variant
Public Function GenerarNúmero(Optional iDígitos As Integer) As Long
'Sintaxis: =GenerarNúmero() -> generará números aleatorios de 3 cifras que, repetidos el suficiente & _
número de veces, deberían satisfacer la ley de Benford & _
=GenerarNúmero(n) -> generará números aleatorios de n cifras (n>0 y n<6) que, repetidos el & _
suficiente número de veces, deberían satisfacer la ley de Benford
'Variable en la que se irán almacenando los dígitos
Dim sProceso As String
'Si no están inicializadas las matrices, llamar al sub que las inicializa
If VarType(dDígito1) = vbEmpty Then LlenarMatrices
'Si no se especifica el número de dígitos al llamar a la función, se ponen 3 por defecto
If iDígitos = 0 Then iDígitos = 3
'Creación de los números
sProceso = auxiliar(dDígito1, True)
If iDígitos > 1 Then sProceso = sProceso & auxiliar(dDígito2, False)
If iDígitos > 2 Then sProceso = sProceso & auxiliar(dDígito3, False)
If iDígitos > 3 Then sProceso = sProceso & auxiliar(dDígito4, False)
If iDígitos > 4 Then sProceso = sProceso & auxiliar(dDígito5, False)
GenerarNúmero = CLng(sProceso)
End Function
Private Function auxiliar(vMatriz As Variant, blnPrimerDígito As Boolean) As String
Dim n As Byte, dAleatorio As Double
Randomize
dAleatorio = Rnd
For n = LBound(vMatriz) To UBound(vMatriz)
If dAleatorio < vMatriz(n) Then
auxiliar = IIf(blnPrimerDígito, CStr(n + 1), CStr(n))
Exit Function
End If
Next n
End Function
Private Sub LlenarMatrices()
'Puesto que en condiciones normales la función GenerarNúmero deberá ser utilizada miles de veces, & _
se utiliza este sub para llenar las matrices de datos una sola vez, con lo que se ganará rendimiento & _
al no tener que ser declaradas y llenadas cada vez que se ejecuta la función.
dDígito1 = Array(0.301029995663981, 0.477121254719662, 0.602059991327962, 0.698970004336019, 0.778151250383644, 0.845098040014257, 0.903089986991944, 0.954242509439325, 1)
dDígito2 = Array(0.119679268596881, 0.233569372004437, 0.342390871009945, 0.446720431240905, 0.547028633508484, 0.643705869310807, 0.737080605093843, 0.827432594363446, 0.915002647942307, 1)
dDígito3 = Array(0.101784364644217, 0.203160342092018, 0.30413254022906, 0.404705472339986, 0.504883559967933, 0.604671135660111, 0.704072445605073, 0.803091652166969, 0.901732836321746, 1)
dDígito4 = Array(0.100176146939935, 0.200313035057513, 0.300410707652128, 0.400469207935615, 0.50048857903252, 0.60046886398036, 0.700410105729886, 0.800312347145336, 0.900175631004708, 1)
dDígito5 = Array(0.100017591505929, 0.200031272641375, 0.300041043836599, 0.400046905521762, 0.50004885812695, 0.600046902082151, 0.700041037817276, 0.800031265762147, 0.900017586346502, 1)
End Sub
La sintaxis es:
=GenerarNúmero(Longitud_deseada)
donde Longitud_deseada debe estar comprendido entre 1 y 5, aunque es un argumento que puede omitirse, en cuyo caso la función devolverá un número de 3 dígitos (aunque esto, obviamente, se puede modificar cambiando el 3 de la instrucción
If iDígitos = 0 Then iDígitos = 3
por la longitud que se desee).
El código no tiene demasiado misterio. Lo único que quizás merezca una pequeña explicación es el sub LlenarMatrices que usa la función principal: dado que, como ya vimos antes, los porcentajes previstos para cada dígito por la ley de Benford son valores constantes, no tendría ningún sentido que la función GenerarNúmero tuviera que rellenar las matrices que necesita cada vez que se ejecuta. Por lo tanto, lo que hace la función es verificar si la primera de las matrices (dDígito1) está vacía; si no lo está, sigue con la ejecución de su código (de forma que se evita tener que llenar de nuevo las matrices), y si lo está llama antes de continuar a la función encargada de llenarlas. Al tratarse de variables declaradas en el nivel de módulo, lo más normal será que se reinicialicen muy raramente.
La función principal hace uso de una función llamada auxiliar, que es la que realmente se encarga de ir construyendo los números, con la longitud deseada.
En el libro de ejemplo de este código VBA he usado esta función para obtener 10.000 números aleatorios que satisfacen con bastante exactitud la ley de Benford, tal como demuestra la tabla situada en J1:O12 (la divergencia más grande obtenida raramente supera el +-0,6%)