【Excel】公式生成不同出现概率的随机数 | 您所在的位置:网站首页 › excel中计算二项概率的函数是什么 › 【Excel】公式生成不同出现概率的随机数 |
工作需要,需要excel生成随机数,但随机数需要概率出现,比如:14-16的随机数,但需要满足14-14.5,14.5-15.5,15.5-16出现的概率为10%,80%,10%的要求。 先上结果: =IF(MATCH(RAND(),{0,0.1,0.9})=2,ROUND(RAND()+14.5,1),IF(RAND()>0.5,ROUND(RAND()*0.5+14,1),ROUND(RAND()*0.5+15.5,1)))这里有几个公式:
1、先说最核心的match() 用法:MATCH(lookup_value, lookup_array, [match_type])说明:在lookup_array 中搜索lookup_value ,并返回对应的位置。但这里重点在match_type,可以取三个值:-1,0,1。1为缺省值,MATCH 查找小于或等于 lookup_value 的最大值。 如:=MATCH(0.4,{0,0.1,0.9}),表示在{0,0.1,0.9}中查找小于或等于0.4的最大值,即0.1。0.1在数组中位于第2位,则这个公式的结果为2。 对lookup_value 不同取值,可以列出以下表格: ABC搜索 公式 结果 0=MATCH(A2,{0,0.1,0.9})10.1=MATCH(A3,{0,0.1,0.9})20.2=MATCH(A4,{0,0.1,0.9})20.3=MATCH(A5,{0,0.1,0.9})20.4=MATCH(A6,{0,0.1,0.9})20.5=MATCH(A7,{0,0.1,0.9})20.6=MATCH(A8,{0,0.1,0.9})20.7=MATCH(A9,{0,0.1,0.9})20.8=MATCH(A10,{0,0.1,0.9})20.9=MATCH(A11,{0,0.1,0.9})3
通过这个表可以看出,1有10%概率出现,2有80%概率出现,3有10%概率出现。 如果说要满足20%,60%,15%,5%的概率,那么可以构造数组为{0, 0.2, 0.8, 0.95},数组相邻元素的差值则为出现的概率。
2、公式rand() 用法:rand()说明:RAND 返回了一个大于等于 0 且小于 1 的平均分布的随机实数。 每次计算工作表时都会返回一个新的随机实数。 配合match(),=MATCH(RAND(),{0,0.1,0.9})公式的结果1,2,3出现的概率为10%,80%,10%。 rand()产生[0,1)的值,如果生成一个范围值,公式则为rand()*(max-min)+min,如生成一个14-16范围的随机数,公式为=rand()*2+14
3、公式round() 用法:ROUND(number, num_digits)说明:函数将数字四舍五入到指定的位数。 =round(rand()*2+14,1),则表示对14-16范围的随机数保留一位小数。
4、公式if() 用法:IF(logical_test, value_if_true, [value_if_false])说明:如果logical_test 为真,则返回value_if_true ,否则返回value_if_false
公式讲解完毕,那么现在一步一步写公式。从里往外写: =RAND(),表示产生[0,1)随机数。 =MATCH(RAND(),{0,0.1,0.9}),表示产生1,2,3三个数,出现概率为10%,80%,10% =IF(MATCH(RAND(),{0,0.1,0.9})=2,"22","11"),表示如果MATCH(RAND(),{0,0.1,0.9})等于2,则结果为22,否则为11。这里是有80%结果为22,20%为11。下面来把20%概率平分。 =IF(MATCH(RAND(),{0,0.1,0.9})=2,"22",IF(RAND()>0.5,"11","33")),这样,11,22,33三种结果的概率为10%,80%,10% 回到主题,产生14-14.5,14.5-15.5,15.5-16范围随机数并保留一位小数的公式分别为round(rand()*0.5+14,1),round(rand()*1+14.5,1),round(rand()*0.5+15.5,1),将其分别替换"11","22","33",则最终公式为: =IF(MATCH(RAND(),{0,0.1,0.9})=2,round(rand()*1+14.5,1),IF(RAND()>0.5,round(rand()*0.5+14,1),round(rand()*0.5+15.5,1)))-- bin.xu 2020.04.27 锦采 |
CopyRight 2018-2019 实验室设备网 版权所有 |