Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 2318|回复: 9

[已解决]能否控制每个随机数的范围?

[复制链接]
发表于 2017-3-19 21:18 | 显示全部楼层 |阅读模式
比如A1.B1.C1.D1.E1单元格中随机数是0--40的整数,且5个单元格数据总和为40,能否控制每个单元格的数字在0-5.0-10.0-15.5-20.20-40范围内?需产生这样的数据100行。
最佳答案
2017-3-20 12:54
本帖最后由 香川群子 于 2017-3-20 12:57 编辑

因为,如果总和不加限制,那么按照你的取值范围,5个数最小=0+0+0+5+20,最大=5+10+15+20+40=90,
5个数随机取值的平均值=(25+90)/2=57.5 远远大于总和40.

所以实际平均值需要缩小=40/57.5=0.696倍,这样才能使总和=40




excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2017-3-20 09:13 | 显示全部楼层
应该能做到,
但是实际数据范围会在:
0-3
0-6
0-10
5-12
20-22
这样较窄的范围内波动。
回复

使用道具 举报

 楼主| 发表于 2017-3-20 10:18 | 显示全部楼层
香川群子 发表于 2017-3-20 09:13
应该能做到,
但是实际数据范围会在:
0-3

为什么会这样?
回复

使用道具 举报

 楼主| 发表于 2017-3-20 10:19 | 显示全部楼层
能否做一个范例来学习一下,谢谢!
回复

使用道具 举报

发表于 2017-3-20 12:06 | 显示全部楼层
工作簿1.rar (5.86 KB, 下载次数: 10)
回复

使用道具 举报

发表于 2017-3-20 12:25 | 显示全部楼层
按指定范围、指定总和进行随机取值:

GetRnd.zip

10.7 KB, 下载次数: 20

回复

使用道具 举报

发表于 2017-3-20 12:41 | 显示全部楼层
5楼用随机函数直接计算:
首先计算第5个数E范围 20 - 35 (因为第1-4个至少是0+0+0+5=5)
接着计算第4个数D范围 5 - (40-E) (40-E小于20)
然后计算第3个数C范围 0 - (40-D-E) (40-D-E小于15)
然后计算第2个数B范围 0 - 10 或 (40-C-D-E) (当40-C-D-E小于10时)
最后得到第1个数A结果=(40-B-C-D-E)

……
这么做理论上没毛病,但随机结果则A、B、C出现=0的概率大大增加。


而我的随机算法,A、B、C出现=0的概率大部分被排除了。

楼主需要什么样的结果,自己决定吧。
回复

使用道具 举报

发表于 2017-3-20 12:54 | 显示全部楼层    本楼为最佳答案   
本帖最后由 香川群子 于 2017-3-20 12:57 编辑

因为,如果总和不加限制,那么按照你的取值范围,5个数最小=0+0+0+5+20,最大=5+10+15+20+40=90,
5个数随机取值的平均值=(25+90)/2=57.5 远远大于总和40.

所以实际平均值需要缩小=40/57.5=0.696倍,这样才能使总和=40




回复

使用道具 举报

 楼主| 发表于 2017-3-20 16:51 | 显示全部楼层
谢谢两位老师!
回复

使用道具 举报

发表于 2017-3-21 08:27 | 显示全部楼层
也可以一下子列出满足条件的所有组合,有3091个,从中随机提取一部分组合。

  1. Sub test()
  2.     Dim ar(), a&, b&, c&, d&, e&, k&, t&
  3.     ReDim ar(1 To 4000, 1 To 5)
  4.     For e = 20 To 35
  5.         For d = 5 To 20
  6.             For c = 0 To 40 - e - d
  7.                 t = 40 - e - d - c
  8.                 For b = 0 To IIf(t > 10, 10, t)
  9.                     a = t - b
  10.                     If a < 6 Then k = k + 1: ar(k, 1) = a: ar(k, 2) = b: ar(k, 3) = c: ar(k, 4) = d: ar(k, 5) = e
  11.                 Next
  12.             Next
  13.         Next
  14.     Next
  15.     MsgBox k
  16.     [a1].Resize(k, 5) = ar
  17. End Sub
复制代码


全部组合解中,各个数字的平均结果如下:
1.9         3.1         3.3         8.3         23.3

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-28 23:46 , Processed in 0.494160 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表