ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
123
返回列表 发新帖
楼主: willin2000

[Excel 函数与公式] [第113期]满园春色关得住:花草按指定数量随机分布.[已结]

[复制链接]

TA的精华主题

TA的得分主题

发表于 2015-11-14 17:01 | 显示全部楼层
139字符
  1. =INDEX(A2:A5,SMALL(IF(B2:B4>=COLUMN(A:Z),{1;2;3},4),MOD(SMALL(RANDBETWEEN(ROW(1:77)^0,9)/1%+ROW(1:77),COLUMN(A:K)+ROW(1:7)*11-11),100)))&""
复制代码


附加题
----B2下拉至B4
  1. =IF(ROW()=4,30-SUM(B$1:B1),RANDBETWEEN(5,-SUM(B$1:B1)+ROW(A4)*5))
复制代码

or
  1. =RANDBETWEEN(ROW(A1)*5+(ROW()=4)*(15-SUM(E$1:E1)),-SUM(E$1:E1)+ROW(A3)*5)
复制代码



评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-15 11:25 | 显示全部楼层
本帖最后由 fugb-2010 于 2015-11-21 11:38 编辑

我的答案是(03版)
1、F7=OFFSET($A$1,MATCH(INT(RAND()*(ROW()*11-COLUMN()+6))+1,MMULT(N({1;2;3;4}>{1,2,3,4}),$B$2:$B$5+1%-COUNTIF($F8:$P$8,$A$2:$A$5)-COUNTIF($E7:E7,$A$2:$A$5))),)&""
    数组公式,上拖、右拖
2、B2=INT(IF(ROW(1:1)=3,1,RAND())*(ROW(3:3)*5-SUM(B$1:B1))+5)下拖

解题思路
1、数据①【=COUNTIF($F8:$P$8,$A$2:$A$5)+COUNTIF($E7:E7,$A$2:$A$5)】
    统计在$F8:$P$8、$E7:E7两个区域中$A$2:$A$5分别的个数,上拖、右拖区域变化
2、数据②【=MMULT(N(ROW($1:$4)>COLUMN($A:$D)),$B$2:$B$5+1%-数据①)】
    统计$A$2:$A$5分别的个数并向下累加,以+1%修正以便最先行第1个查找到
3、数据③【=INT(RAND()*(ROW(7:7)*11-COLUMN(A:A)+1))+1】
    产生剩余的个数随机抽取,剩余的个数F7为77个、…、P7为67个、…、F1为11个、…、P1为1个,剩余的个数随机抽取均等
4、数据④【=MATCH(数据③,数据②)】
    产生剩余的个数随机抽取数据③在数据②中查找所在行
5、结果【=OFFSET($A$1,数据④,)&""】
F7=OFFSET($A$1,MATCH(INT(RAND()*(ROW(7:7)*11-COLUMN(A:A)+1))+1,MMULT(N(ROW($1:$4)>COLUMN($A:$D)),$B$2:$B$5+1%-COUNTIF($F8:$P$8,$A$2:$A$5)-COUNTIF($E7:E7,$A$2:$A$5))),)&""
    简化=OFFSET($A$1,MATCH(INT(RAND()*(ROW()*11-COLUMN()+6))+1,MMULT(N({1;2;3;4}>{1,2,3,4}),$B$2:$B$5+1%-COUNTIF($F8:$P$8,$A$2:$A$5)-COUNTIF($E7:E7,$A$2:$A$5))),)&""
6、B2=INT(IF(ROW(1:1)=3,30-SUM(B$1:B1),RAND()*(10+ROW(1:1)*5-SUM(B$1:B1))+5))
   简化B2=INT(IF(ROW(1:1)=3,30-SUM(B$1:B1),RAND()*(ROW(3:3)*5-SUM(B$1:B1))+5))
   简化B2=INT(IF(ROW(1:1)=3,1,RAND())*(ROW(3:3)*5-SUM(B$1:B1))+5)

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-18 16:14 | 显示全部楼层
{:soso_e141:}电脑里没装07,那就整个03版的公式吧:
首先附加题B2=IF(A3=0,30-B$2-B$3,5+ROUND((10+5*ROW(A1)-N(B1))*RAND(),))   下拉
正题F1=LOOKUP(RAND()*(94-ROW()*11-COLUMN()),SUBTOTAL(9,OFFSET($B$1,,,{1,2,3,4}))-MMULT({1,1,1,1},(IF(ROW()>1,COUNTIF(OFFSET($F$1:$P$1,,,ROW()-1),$A$1:$A$4))+COUNTIF($E1:E1,$A$1:$A$4))*({1,2,3,4}>={1;2;3;4})),$A$2:$A$5)&"" 右拉下拽

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-20 12:24 | 显示全部楼层
本帖最后由 kuangben8 于 2015-11-27 16:31 编辑

我是来请教的,下面的公式为什么不可以!!!!!!!
  1. =LOOKUP(MOD(SMALL(RANDBETWEEN(1^ROW(1:77),99)/1%%+ROW(1:77),ROW(1:7)*11+COLUMN(A:K)-11),1000)-1,SUBTOTAL(9,OFFSET($B$1,,,ROW($1:$4),)),$A$2:$A$5)&""
复制代码

郁闷啊。。。。。

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-23 11:36 | 显示全部楼层
本帖最后由 sayhi95 于 2015-11-29 21:27 编辑
  1. =MID(REPT(A2,B2)&REPT(A3,B3)&REPT(A4,B4),INDEX(MOD(LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77)),100),11*(ROW(1:7)-1)+COLUMN(A:K)),1)
复制代码
区域数组公式,不满足要求4,应该还是不对。

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-28 21:25 | 显示全部楼层
本帖最后由 gxl19870625 于 2015-11-28 21:27 编辑

我的答案是:
=LOOKUP(INDEX(--RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77)),2),COLUMN()+11*ROW()-16),MMULT(N(ROW(1:4)>=COLUMN(A:D)),IF(B1:B4="量",1,B1:B4)),A2:A5)字符数165
公式为区域数组公式,输入方法如下:选中区域F1:P7,输入上述公式,再同时按ctrl+shift+enter;
公式解读:
公式整体为lookup,其3个参数分别为:
①INDEX(--RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77)),2),COLUMN()+11*ROW()-16)
②MMULT(N(ROW(1:4)>=COLUMN(A:D)),IF(B1:B4="数量",1,B1:B4))
③A2:A5
对于①,利用RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77)生成随机等概率分布的77个自然数,再利用LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77))对这77个数进行从大到小的排序,再利用--RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77)),2)截取最右两位并转为数值即可以得到由1-77组成的随机等概率分布的77个自然数序列,最后利用INDEX(--RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW(1:77)),2),COLUMN()+11*ROW()-16)取出每个数值;
对于②是形成由1;B2;B3;B4组成的累加数组,即1;1+B2;1+B2+B3;1+B2+B3+B4;
对于③就是①的某一个数值根据②的累加数组去匹配,返回结果为A2:A5中对应的结果,数值在[1,B2]中的结果为为A2,数值在[1+B2,B2+B3]中的结果为A3,数值在[1+B2+B3,B2+B3+B4]中的结果为A4,数值在[1+B2+B3+B4,77]中的结果为A5,A5为0,为了显示的效果自定义公式区域F1:P70数据格式为0;0;

附加题:
在单元格B2输入下列公式:
=CHOOSE(ROW()-1,RANDBETWEEN(5,20),RANDBETWEEN(5,RANDBETWEEN(5,25-B$2)),30-B$2-B$3)
下拉至B4即可, 字符数81.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

4

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-11-30 18:57 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 willin2000 于 2015-11-30 19:02 编辑

本题总体上,有2种方案,07函数RANDBETWEENT,03函数RAND.

03公式,Delete_007:
=LOOKUP(RAND()*(ROW()*11+COLUMN()-16),MMULT(N({1;2;3;4}>{1,2,3}),$B$2:$B$4-COUNTIF(G1:P1,$A$2:$A$4)-COUNTIF($F2:P7,$A$2:$A$4)),$A$2:$A5&"")

07公式,笨笨四:
=INDEX(A:A,RIGHT(SMALL(RANDBETWEEN(ROW(1:77)^0,77)+SMALL(5^(B2:B4<COLUMN(1:1))+{1;2;3},ROW(1:77))%,ROW()+7*COLUMN()-42)))&""

附加题通用典型算法,即能适应更多个数,内存结果,有:
wcymiss,MMULT计算分布个数:
=MMULT(N(RANDBETWEEN(COLUMN(A:O)^0,3)={1;2;3}),ROW(1:15)^0)+5
象山海鲜,Frequency统计分布个数:
=FREQUENCY(RANDBETWEEN(0,14+ROW(1:15)%),{4,9})+5
Horselyq,插板法:
=MMULT(IFERROR(SMALL(RANDBETWEEN(0^ROW(1:2),15),ROW(1:3)-{1,0}),{0,15}),{-1;1})+5备注:数组的"和"固定的上述内存数组三种方法,其实并不真正随机.

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-12-6 11:56 | 显示全部楼层
本帖最后由 fugb-2010 于 2015-12-6 12:00 编辑

        评分汇总

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2023-5-17 22:06 | 显示全部楼层
  1. =LET(_a,RIGHT(SORT(RANDBETWEEN(ROW($1:$77)^0,99)/1%%+ROW($1:$77)),2),_b,MID(TEXTJOIN("",,REPT(A2,B2),REPT(A3,B3),REPT(A4,B4),REPT("a",77-SUM(B2:B4))),SEQUENCE(77),1),WRAPCOLS(SUBSTITUTE(INDEX(_b,_a),"a",""),7))
复制代码

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

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

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-7-13 20:46 , Processed in 0.044782 second(s), 20 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

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