Necesito generar 13 números a partir de 13 intervalos diferentes que sumarán 1360. En el cuadro a continuación, "índice" significa el índice de los 13 números diferentes. Media significa la media (promedio) de los intervalos. El rango será más o menos 15% de la media como se muestra a continuación. Prefiero que se generen los números aleatorios en función de la distribución normal con N (media, 7,5% de la media). lo recupero. Sin distribución normal. Utilice + - 15% como límites estrictos de los intervalos.

Sería genial si alguien pudiera descubrir cómo hacerlo en Excel. Los algoritmos también serán apreciados.

Index      mean    15%     low    high

  A        288     43      245    331
  B        50      8       43     58
  C        338     51      287    389
  D        50      8       43     58
  E        16      2       14     18
  F        66      10      56     76
  G        118     18      100    136
  H        17      3       14     20
  I        91      14      77     105
  J        26      4       22     30
  K        117     18      99     135
  L        165     25      140    190
  M        18      3       15     21
0
Viola 20 oct. 2017 a las 21:38

3 respuestas

La mejor respuesta

Ordenaría la tabla aumentando la media:

enter image description here

Y use una columna para un valor auxiliar (columna H arriba).

La idea es mantener, mientras se pasa a la siguiente fila, la desviación actual de un objetivo perfecto para el objetivo final. Perfecto significaría que cada valor aleatorio coincide con la media de esa fila. Si un valor es 2 menor que la media, ese 2 aparecerá en la columna H para la siguiente fila. El número aleatorio generado para la siguiente fila no apuntará a la media dada, sino a 2 menos que la media. El rango para el número aleatorio se reducirá adecuadamente para que los valores bajo / alto nunca se crucen.

Al ordenar primero las filas, podemos estar seguros de que esta media corregida siempre estará dentro del rango bajo / alto de la siguiente fila, por lo que siempre será posible generar un número aleatorio aceptable allí.

El valor final se calculará de manera diferente: será el resto que se necesita para alcanzar la suma objetivo. Por la misma razón que anteriormente, este valor está garantizado dentro del rango bajo / alto.

Las fórmulas utilizadas son las siguientes:

   |                       F                          |              H
 --+--------------------------------------------------+------------------------------
 2 | =RANDBETWEEN(D2, E2)                             |
 3 | =RANDBETWEEN(B3+H3-C3+ABS(H3), B3+H3+C3-ABS(H3)) | =SUM($B$2:$B2)-SUM($F$2:$F2)
 4 |      (copy above formula)                        | (copy above formula)
...|              ...                                 |         ...
13 |      (copy above formula)                        | (copy above formula)
14 | =SUM($B$2:$B14)-SUM($F$2:$F13)                   |

En teoría, las filas no necesitan ser ordenadas primero, pero luego las fórmulas no pueden copiarse como se indicó anteriormente, sino que deben hacer referencia a las filas correctas. Eso lo haría bastante complicado.

Si es absolutamente necesario que las filas se presenten en el orden de la columna Índice (A, B, C ...), utilice otra hoja para hacer lo anterior. Luego, en la hoja principal, lea el valor en la columna F con una BUSQUEDA de la otra hoja. Entonces en F2 tendrías:

=VLOOKUP(A2, OtherSheet!$A$2:$F$14, 6, 0)
0
trincot 20 oct. 2017 a las 20:30

Aquí está mi ejemplo para generar un número aleatorio basado en bajo y alto.

La fórmula en column F es solo un RANDBETWEEN:

=RANDBETWEEN($D2,$E2)

Entonces puede obtener el resultado siempre igual a 1360 con la siguiente fórmula para column G:

=F2/SUM($F$2:$F$14)*1360

Entonces cell G15 siempre será 1360, que es la suma de todos esos 13 intervalos.

0
ian0411 20 oct. 2017 a las 19:38

Obtén el número aleatorio como este

num = Int ((300 - 200 + 1) * Rnd + 200) //between 200 and 300

Haga clic aquí para más información

Y el número aleatorio debe ser la suma total menos la suma que ya obtuvo y el último será el que quede.

Por ejemplo: (si tenemos 4 números suman 100)

A is a random number between 0 to 100 //lets say 42
then B is a random number between 0 to (100-42) => 0 to 78 //lets say 18
then C is a random number between 0 to (100-42-18) => 0 to 40 //lets say 25
then, in the end D is 100-42-18-25 => D is 15

*100-42-18-25 is the same as 100-Sum(A,B,C) 
0
amitklein 20 oct. 2017 a las 18:54