ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2015-10-15 20:31 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 titi012 于 2015-10-29 21:25 编辑

我的答案:

公式一,F7输入,右拉上拉。173字,03公式。
  1. =INDEX($A:$A,RIGHT(MAX((TEXT($B$2:$B$5,"0;;"&89-SUM($B$2:$B$4))-COUNTIF($F$8:$P8,$A$2:$A$5&"")-COUNTIF($E7:E7,$A$2:$A$5&"")>0)*(RIGHT(RAND()*{2;3;4;5})+{2;3;4;5}*1%)))+0)&""
复制代码


公式二,F1:O7输入,多单元格公式,146字,07版公式
  1. =INDEX(A:A,SMALL(IF(B2:B4>=COLUMN(A:BY),{2;3;4},5),RIGHT(SMALL(RIGHT(RANDBETWEEN(9^8,9^9)*ROW(9:85),7)/1%+ROW(1:77),ROW()*11+6-COLUMN()),2)+0))&""
复制代码



公式三,  最终答案!
F7输入,右拉上拉. 03公式,152字符.
  1. =INDEX($A:$A,SMALL(IF($B$2:$B$4-COUNTIF($E7:E7,$A$2:$A$4)-COUNTIF($F$8:$P8,$A$2:$A$4)>=COLUMN($A:BY),{2;3;4},5),INT((ROW()*11+6-COLUMN())*RAND())+1))&""
复制代码

B2:B4公式,68字符
  1. =IF(ROW()=4,30-SUM(B$1:B1),ROUND(RAND()*(5+ROW()*5-SUM(B$1:B1)),)+5)
复制代码



再给一个取巧的公式,F1:O7多单元格公式,03版,利用了B2:B4的随机数,不作为正式答案。
  1. =INDEX(A:A,SMALL(IF(B2:B4>=COLUMN(A:BY),{2;3;4},5),RIGHT(SMALL(RIGHT(SQRTPI(B2*B3)*ROW(9:85),7)/1%+ROW(1:77),ROW()*11+6-COLUMN()),2)+0))&""
复制代码

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-10-16 12:32 | 显示全部楼层
本帖最后由 delete_007 于 2015-10-19 09:12 编辑

我的答案是:03版公式(139):
  1. =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&"")
复制代码
03以上版本公式(123):
  1. =INDEX(A:A,RIGHT(SMALL(RANDBETWEEN(1^C1:C77,9^9)+SMALL(3*(B2:B4<COLUMN(1:1))+{2;3;4},ROW(1:77))%,ROW()*11-COLUMN()+6)),)&""
复制代码

附加题(58):
  1. =INT(RAND()^(ROW()<4)*(6+ROW()*5-SUM(B$1:B1)))+5-(ROW()=4)
复制代码

评分

8

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-10-19 15:47 | 显示全部楼层
  1. =INDEX($A:$A,SUM(N((1+INT(RAND()*(89-ROW(A1)*11-COLUMN(A1))))>MMULT(N({1,2,3}<={1;2;3}),$B$2:$B$4-COUNTIF($E1:E1,$A$2:$A$4)-COUNTIF(OFFSET($E$1,,,MAX(ROW()-1,1),IF(ROW()=1,1,12)),$A$2:$A$4))))+2)&""
复制代码
  1. =RANDBETWEEN(IF(ROW()=4,25-SUM(B$1:B1),0),10-SUM(B$1:B1)+ROW(A1)*5)+5
复制代码

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-10-22 05:54 | 显示全部楼层
F7数组
=LOOKUP(RAND()*(6-COLUMN()+11*ROW()),MMULT(--(ROW($1:$4)>COLUMN($A:$C)),$B$2:$B$4-COUNTIF($E7:E7,$A$2:$A$4)-COUNTIF($F8:$P$8,$A$2:$A$4)),$A$2:$A$5)&""
右拉上拉

附加B2
=IF(ROW()=4,30-SUM(B$1:B1),RANDBETWEEN(5,15+ROW(A1)*5-SUM(B$1:B1)))
下拉

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-1 13:36 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 gdw831001 于 2015-11-20 23:15 编辑

我的答案是:在减一个字符
  1. =INDEX(A:A,SMALL(IF(B2:B4>=COLUMN(1:1),{2;3;4},99),MOD(SMALL(RANDBETWEEN(-T1:T77,77)/1%+ROW(1:77),ROW()*11+COLUMN()-16),100)))&""
复制代码


  1. =INDEX(A:A,SMALL(IF(B2:B4>=COLUMN(1:1),{2;3;4},99),MOD(SMALL(RANDBETWEEN(T1:T77+1,77)/1%+ROW(1:77),ROW()*11+COLUMN()-16),100)))&""
复制代码


  1. =INDEX(A:A,SMALL(IF(B2:B4>=COLUMN(1:1),{2;3;4},99),MOD(SMALL(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),(ROW()-1)*11+COLUMN()-5),100)))&""
复制代码



附加:
  1. =IF(A3<>"",RANDBETWEEN(5,25/2),30-SUM(B$1:B1))
复制代码







评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-2 21:23 | 显示全部楼层
本帖最后由 swyrlbq 于 2015-11-5 08:37 编辑

07版,
F1:P7
=INDEX(A:A,SMALL(IF(B2:B4<COLUMN(A:Z),5,{2;3;4}),MOD(SMALL(RANDBETWEEN(ROW(1:77)^0,77)/1%+ROW(1:77),ROW()*11+COLUMN()-16),100)))&""


03版,单元格数组公式(174字符):
F1=INDEX($A:$A,SMALL(IF($B$2:$B$4-COUNTIF(OFFSET($E$7,,,-7,COLUMN()-5),$A$2:$A$4)-COUNTIF(F2:F$8,$A$2:$A$4)<COLUMN($A:$Z),5,{2;3;4}),INT(RAND()*(77+ROW()-COLUMN(A:A)*7)+1)))&""



附加小题,
B2=IF(ROW()=4,30-SUM(B$1:B1),INT(RAND()*MIN(16,(21-SUM(B$1:B1)))+5))

评分

7

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-4 08:16 | 显示全部楼层
本帖最后由 WJX-online 于 2015-11-24 10:33 编辑

我的答案是:
  1. F1:P7=T(OFFSET(A1,RIGHT(SMALL(-RIGHT(SUM(LOGEST((B2:B4<COLUMN(A:Z))+1))*ROW(1:77)&TEXT(SMALL(IF(B2:B4<COLUMN(A:Z),99,ROW(1:3)),ROW(1:77)),"00"),9),COLUMN(A:K)*7+ROW(1:7)-7),2),)) 数组公式(包含等号173个字符)。可通过修改常数或常量来扩大种植种类。
  2. B2=IF(ROW(A1)>2,30-SUM(B$1:B1),INT(5+8*RAND())),下拉。包含等号45个字符.
复制代码

本帖子中包含更多资源

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

x

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-6 13:09 | 显示全部楼层
本帖最后由 jokie0913 于 2015-11-6 13:19 编辑

我的答案是:
=TEXT(INDIRECT("a"&SUM(FREQUENCY(RANDBETWEEN(1,COLUMN()+ROW()*11-16),SUMIF(OFFSET($B$1,,,ROW($2:$4)),"<>")-MMULT(--(ROW($1:$3)>=COLUMN($A1:$C1)),COUNTIF($F2:P8,$A$2:$A$4)+COUNTIF(G1:Q1,$A$2:$A$4)))*ROW($2:$5))),";;;@")
共计218字符,满足03要求。

稍微简化下,
=TEXT(INDIRECT("a"&SUM(FREQUENCY(RANDBETWEEN(1,COLUMN()+ROW()*11-16),SUMIF(OFFSET($B$1,,,{2;3;4}),"<>""")-MMULT({1,0,0;1,1,0;1,1,1},COUNTIF($F2:P8,$A$2:$A$4)+COUNTIF(G1:Q1,$A$2:$A$4)))*{2;3;4;5})),";;;@")
共计204字符。

如果没03限制的话能用index替代indirect略简化,能设置单元格格式的话能不用text来简化,不过算了,没新思路

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-10 14:58 | 显示全部楼层
本帖最后由 willin2000 于 2015-11-27 14:38 编辑

我的答案是:
  1. =INDEX(A:A,TEXT(--RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,999)*100+LARGE((COLUMN(A:AZ)<=B2:B4)*ROW(B2:B4),ROW(1:77)),COLUMN(A:K)+(ROW(1:7)-1)*11),2),"0;;!65536"))&""
复制代码
最终答案.
  1. =INDEX(A:A,RIGHT(LARGE(RANDBETWEEN(ROW(1:77)^0,99)*100+SMALL(IF(COLUMN(A:Z)<=B2:B4,ROW(B2:B4),5),ROW(1:77)),COLUMN(A:K)+(ROW(1:7)-1)*11),2))&""
复制代码


区域数组。

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2015-11-11 10:46 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 xzk235 于 2015-11-15 11:29 编辑

F1单单元格数组公式,增加行数需要修改MMULT的数组构造部分和后面的引用部分。
  1. =LOOKUP((ROW()*11+COLUMN()-16)*RAND(),MMULT(N(ROW($2:$5)>COLUMN($B:$D)),$B$2:$B$4-COUNTIF(G1:$Q1,$A$2:$A$4)-COUNTIF($F2:$P$8,$A$2:$A$4)),$A$2:$A$5)&""
复制代码

引用区域用到了公式区外的Q列和第8行,不知合不合题意。
本来想再整个多单元格数组公式出来,思路都想好了,用多个rand生成足够77位的随机数,用MID拆开*100+{1-77},排序取后两位,然后用lookup取结果,但做出来发现结果不对,找半天才发现是rand函数在每个单元格里结果不一样,纯函数想不出思路来怎么生成每个单元格相同的随机数。

评分

4

查看全部评分

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

本版积分规则

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

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

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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