随机数概率设置_记⽤Excel模拟⾮等概率随机分布
前⾔
由于经常需要⽤到Excel，让我对Excel有了更深的认识，以⾄于不得不感叹Excel实在是强⼤，我知道现在还有更⾼级的数据处理⼯具，如Python、R，但对于⽣活中的⼤多数计算问题，Excel完全胜任并能处理得很好(不过我还没接触过R，也说不定哪天就真⾹了)，哪怕仅仅是⽤于加减乘除运算，也⽐计算器要直观⽅便些，配合各种函数可以达到编程的效果，我觉得Excel的优势之⼀就是计算过程⾮常直观。再加上VBA(当然现在还没有深⼊到这个地步，⼤学⾥教的落伍的VB语⾔没想到能在这⾥派上⽤场)，就完全可以⽤于编程了。
在⼀个⽉前，我碰到了⼀个需求，这个需求由于我的脑洞变得更加具体：我想知道Excel能不能随机模拟。⽐如我现在有⼀个刻了ABCDEFGH⼋种字母的骰⼦，投掷100次，并记下每次出现的字母，由此获得⼀串由⼋个字母组成的随机排列，要求每种字母出现的频率保持⼀定规律，即每种字母在投掷时具有特定的概率。当投掷的次数趋于⽆穷时，字母出现的频率就⽆限接近于概率。
在⼀般情况下，骰⼦每个字母出现的概率相同，即1/8，这种模拟⾮常简单，只需要使⽤ =RANDBETWEEN(1,6)函数重复100次即可。
但 =RANDBETWEEN(A,B)函数只能表⽰在[A,B]区间内的随机整数，另⼀种随机函数 =RAND()表⽰⼤于或等于0且⼩于1的随机数字。我们现在要求每种字母出现的概率不同，如何实现？
可以借⽤⼏何的思想。假设有⼀条长度10的线段， 取a的长度的2，b的长度为3，c的长度为4，d的长度为5，如下图：
由此获得五个分段点，在后⽂中我称为阈值：
我们随机在线段上取⼀个点E，取点的过程完全随机，即在任意位置的概率都相同，当E的值在[0,2)区间时，表⽰选中a，当E的值在[2,5)区间时，表⽰选中b，当E的值在[5,9)区间时，表⽰选中c，当E的值在[9,10)区间时，表⽰选中d。
且得到E落在各区间段上的概率：
E的取值完全随机，由此即可⽤⼀个[0,10)之间的随机(整)数实现了a, b, c, d 的⾮等概率随机过程。
同样的思路，即可⽤在上述需求中。
Excel实现
在了解这种⽅法后，我们开始在Excel上动⼿实现。
⾸先请容许我给⼤家看⼀下最终的实现效果：
更进⼀步⽤区域编号说明：
A区表⽰随机变量分布的模拟，B区⽤于设定变量及其概率，C区⽤于设定随机数的区间，D区⽤于统计⽣成的随机变量分布，E区⽤图表表⽰随机变量分布的实际频率与变量设定出现概率之间的差异。
开始制作吧
C区
为模拟上述线段的效果，我们需要先设计⼀个区间，如C区表⽰，由于隐藏了不必要显⽰的单元格，我们先取消隐藏，看看实际的C区样⼦。
C区的随机区间(单元格G13)设定会对全局产⽣影响，包括A区⽣成随机数的范围和B区的阈值。
为⽅便的⽤⼀个单元格实现对所有随机数范围和阈值的设置，可以将单元格的值带⼊随机函数中，单元格G12的“0-1000”表⽰在
[0,1000)内⽣成随机整数(以 RANDBETWEEN(0,1000)函数)或[0,1000)内⽣成随机有理数(以 RAND()*1000函数时)。“0-1000”是⼀段⽂本，0表⽰下限，1000表⽰上限，Excel⽆法直接处理这段⽂本，因此就有了函数组合来处理。
excel的随机数函数在Excel中常⽤的⽂本截取函数(以字符截取)有三种：
=LEFT(text, [num_chars])=RIGHT(text, [num_chars])=MID(text, start_num, num_chars)
想要精确的截取字符串“0-1000”中的0和1000，就需要知道各⾃字符的长度和位置，在该例中，可以直接看出0和1000各⾃的长度和位置，⽤ =LEFT(G13,1)截取0，⽤ =RIGHT(G13,4)截取1000可太容易了，但如果⽂本换成其他长度的呢？⽐如改成“0-
10000”或“250-500000”，就需要重新调整函数条件，有些繁琐了。⼀般情况下，⽤正则表达式可以很⽅便地匹配字符串，但Excel 并不⽀持正则表达式(不过可以使⽤插件或⾃⼰在VBA⾥写个函数⽀持实现)，我们可以另辟蹊径，实现同样的效果。
Excel中有两个函数 SEARCH()和 FIND()可以⽤于返回字符串中特定字符的位置，在这个例⼦中两个函数功能相同。从⽂本格式可以看到通过字符“-”可以定位0和1000的位置。单元格H12表⽰的即为字符“-”在⽂本中的位置，具体写法为：
H12 =SEARCH("-",G13)
知道了“-”的位置就知道了下限的长度，即 =H12-1，因此H13表⽰的就是下限的长度，即可截取出下限数值“1”来，为避免出现问题，在结果外⾯套上VALUE()函数将⽂本转为数字，写法如下：
H13 =VALUE(LEFT(G13,H12-1))
想要获得上限的长度稍显⿇烦些，因为我们本⾝是⽆法直接通过字符“-”的位置判断上限长度的。我们
需要⽤到字符长度计算函数
LEN()得出完整⽂本的字符长度，减去字符“-”的位置即可得到上限字符长度 =LEN(G13)-H12，⽤单元格H14表⽰：
H14 =VALUE(RIGHT(G13,LEN(G13)-H12))
因此，单元格H13和H14分别表⽰随机区间下限和上限的数值，使⽤
=RANDBETWEEN($H$13,$H$14-1)
或者
=RAND()*$H$14
即可⽣成我们需要的区间内随机数，公式中的 $表⽰绝对位置，这在⼤量单元格填充时可以避免引⽤数据发⽣位移。
B区
接下来使⽤B区设定⾮等概率随机产⽣的变量和各⾃概率，以及⽣成阈值，由于隐藏了不必要显⽰的单
元格，因此也先取消隐藏⼀下。
这是⼀个概率表，让我具体说明各⾃意义。
NAME
NAME⼀栏，⽤于设定表⽰的随机变量，在这⾥设定的随机变量对全局产⽣影响，你也可以写成苹果、桃⼦、李⼦、西⽠、葡萄、⾹蕉、荔枝、猕猴桃、blablabla。
ID
ID⼀栏表⽰随机变量在概率表中的⾏位置，⽐如A是第⼀个，就是1，C是第三个，就是3，可以按标序号的⽅式⾃动填充，也可以闲着没事⽤公式 =MATCH(G2,$G$2:$G$9)算出来，往下⾃动填充就是了。需要注意ID值必须以升序排列。ID起到很重要的作⽤，之后会讲到。
概率
概率⼀栏是我们⾃⼰设定的概率，⼤于0⼩于1内可以随便设置，所有变量概率之和为100%。
叠加
叠加⼀栏是为了⽅便⽽做的⼀个中间步骤，⽤于⽣成阈值，使⽤时可以隐藏这列，不太懂这⼀步可以往前看⼀下阈值的说明。写法如下：第⼀⾏：K2=J2第⼆⾏及以后：K3=(往下⾃动填充)
阈值
阈值⼀栏⽤于确定随机变量的边界，求值⽅式为叠加值乘以随机区间宽度并加下限值。Excel计算时将⽣成的随机数与阈值⽐较，并判断出具体表⽰为哪⼀个随机变量。接下来马上详细说明，写法为：
L2=K2*($H$14-$H$13)+$(往下填充)
A区
由于A区往下的⾏内容⼏乎都是重复填充，因此只以第⼀⾏数据说明
“随机1”采⽤的 =RANDBETWEEN()函数⽣成随机整数，区间引⽤之前的H13和H14，写法为：
=RANDBETWEEN($H$13,$H$14-1)
“随机2”采⽤的 =RAND()函数⽣成随机有理数，区间引⽤之前的H14，写法为：
=RAND()*$H$14
若⽤随机1的⽅法⽣成随机变量，随机区间的范围越⼤，表⽰的变量概率精度越⾼，若⽤随机2的⽅法⽣成随机变量，⼀般情况下只要设定的区间⼤于1，对表⽰的变量概率精度⼏乎没有影响。
ID值⼀栏⽤于计算出⽣成的随机数所⽐较的阈值位置，即随机变量在B区概率表中的位置，也即ID值，该列的单元格公式是整个⽅法的核⼼，写法为：
=IF(ISNA(MATCH(C2,L$2:L$9,1)),0,MATCH(C2,L$2:L$9,1))+1
请容我慢慢解释，⾸先选择合适的 MATCH()函数，该函数在官⽹中的解释为：
使⽤ MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。例如，如果
A1:A3 区域中包含值 5、25和 38，那么公式 =MATCH(25,A1:A3,0) 返回数字 2，因为 25 是该区域中的第⼆项。
具体语法说明我直接复制官⽹内容：
MATCH(lookupvalue, lookuparray, [match_type])
MATCH 函数语法具有下列参数：
lookupvalue 必需。要在 lookuparray 中匹配的值。例如，如果要在电话簿中查某⼈的电话号码，则应该将姓名作为查值，但实际上需要的是电话号码。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。