ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [第92期]随机九宫格[已总结]

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-4-22 21:12 | 显示全部楼层
本帖最后由 swyrlbq 于 2013-4-22 21:57 编辑

用03版折腾了几天,不成功,RANDBETWEEN函数不支持数值,疑似03版不可。安装07版,可轻松搞定。不知可否。期待高手03版的公式。
07版数组公式216字符:
A1:I9=IF(MOD(COLUMN(),3)+MOD(ROW(),3)*3+1=MOD(SMALL(RANDBETWEEN(1,9+ROW()*0)*10+ROW(),INT((ROW()+2)/3)+INT((COLUMN()-1)/3)*3),10),MOD(SMALL(RANDBETWEEN(1,9+ROW()*0)*10+ROW(),INT((ROW()+2)/3)+INT((COLUMN()-1)/3)*3),10),"")

点评

我那是03班,只是位置是死的,你修改看看,能不能让位置活起来  发表于 2013-5-2 08:00

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-4-29 10:19 | 显示全部楼层


以上是附上的附件

本帖子中包含更多资源

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

x

点评

呵呵,技术加200. 可惜这个RANDBETWEEN函数了  发表于 2013-5-2 10:24
感谢匙总参与。  发表于 2013-5-2 07:22

TA的精华主题

TA的得分主题

发表于 2013-5-2 17:07 | 显示全部楼层
刚刚看到此帖子,突然发现Delete老师穿越了?哈哈,上图:

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2013-5-3 14:31 | 显示全部楼层
开赛前后并未公布评分标准,有失公允,如果是以公式长度为标准,我的答案也可以简化:
=IFERROR(VLOOKUP((ROUNDUP(COLUMN()/3,)+(ROUNDUP(ROW()/3,)-1)*3)*10+MOD(COLUMN()-1,3)+1+MOD(ROW()-1,3)*3,ROW()*{10,0}+RIGHT(SMALL(RANDBETWEEN(1,8+ROW()^0)/1%+ROW(),ROW())),2,),"")
参赛的家人都是抱着兴趣参加的,如果不能公开公平公正,大家的兴趣就会少很多的。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-5-3 14:53 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
羽希 发表于 2013-5-3 14:31
开赛前后并未公布评分标准,有失公允,如果是以公式长度为标准,我的答案也可以简化:
=IFERROR(VLOOKUP(( ...

这个公式不满足位置和数字绝对随机的要求。
一直以来,竞赛区都是以字符论英雄的,这次也不例外,评分标准是这样的:
1.错误答案不予评分;
2.低于我的预解字符的答案评2分;
3.高于预解字符的答案评1分;
4.根据公式具体字符长度,给予适当财富奖励。
5.本期我认为没有精彩答案,所以没有额外技术分奖励。

TA的精华主题

TA的得分主题

发表于 2013-5-3 15:31 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 羽希 于 2013-5-3 15:40 编辑
delete_007 发表于 2013-5-3 14:53
这个公式不满足位置和数字绝对随机的要求。
一直以来,竞赛区都是以字符论英雄的,这次也不例外,评分标 ...


怎说一直以来以字符论英雄?且不论事后给标准是否恰当 这个版块的主旨难道是让大家削尖脑袋减字符?看看apolloh老大定以的竞赛区宗旨吧

点评

平常心,平常心,虽然在实际解决问题时一味扣字符我也不是很推崇,不过在竞赛这个特殊的环境下确实有它评判方便的一面,此外如果确实能写出思路上特别舒畅的公式也一定能博得满堂彩。不管怎么说主要是享受那份快乐..  发表于 2013-7-26 12:07

TA的精华主题

TA的得分主题

发表于 2013-7-26 07:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 Bodhidharma 于 2013-7-26 14:45 编辑
delete_007 发表于 2013-5-2 07:16
==================答题截止==================
评分及总结见附件,如有意见或建议,敬请批评指正。

版主你好:
因為想要研究「不重複亂數」,經你的推薦,仔細研讀了這個帖子,但是讀完之後有個很大的疑惑:似乎所有的解法,都不符合「完全隨機」的要求。
在版主總結帖的4個思路中,全部都用到了類似的方式,取1~9的不重複亂數:
  1. =MOD(SMALL(RANDBETWEEN(ROW($1:$9)^0,9)/1%+ROW($1:$9),ROW($1:$9)),100)
复制代码

也就是在1~9中,隨機加上100~900,然後由小到大排列後,取其個位數
但是這樣子似乎不完全隨機:
比方說12隨機加100*(1~9),有1/9的機率會加到一樣的數
在這個情況下(加到一樣的數)1就會排在2的前面
也就是說,越小的數,排在越前面的機率越高,因此位置似乎並不是完全隨機
9個數字可能結果很多(9^9種情形),所以比較看不出來(不完全隨機),但是數字少的話就很就很清楚:

比方說取1~2的不重複亂數,公式會這樣寫:
  1. =MOD(SMALL(RANDBETWEEN(ROW($1:$2)^0,2)/1%+ROW($1:$2),ROW($1:$2)),100)
复制代码

兩個RANDBETWEEN產生的所有可能組合就是{101,102},{101,202},{201,102},{201,202}
在這個情況下,第一個位置是1的機率是3/4,是2的機率是1/4
如果是取1~3的不重複亂數,公式會這樣寫:
  1. =MOD(SMALL(RANDBETWEEN(ROW($1:$3)^0,3)/1%+ROW($1:$3),ROW($1:$3)),100)
复制代码

三個RANDBETWEEN產生的所有可能組合就是:
{101,102,103},{101,102,203},{101,102,303},{101,202,103},{101,202,203},{101,202,303},{101,302,103},{101,302,203},{101,302,303}
{201,102,103},{201,102,203},{201,102,303},{201,202,103},{201,202,203},{201,202,303},{201,302,103},{201,302,203},{201,302,303}
{301,102,103},{301,102,203},{301,102,303},{301,202,103},{301,202,203},{301,202,303},{301,302,103},{301,302,203},{301,302,303}
在這個情況下,第一個位置是1的機率是14/27,是2的機率是8/27,是3的機率是5/27
1~9的不重複亂數,顯然也會有相同的問題,也就是越小的數,排在越前面的機率越高!
當然把RANDBETWEEN的第二個參數改大,比方說改成
  1. =MOD(SMALL(RANDBETWEEN(ROW($1:$9)^0,999999)/1%+ROW($1:$9),ROW($1:$9)),100)
复制代码

可以大大增加隨機性,但是嚴格講起來,仍然不是完全隨機。而且本質上來說,這種在SMALL的第一個參數上做文章的方式,只要兩個RANDBETWEEN有可能出現一樣的東西,就一定無法達成完全隨機。
然後我仔細研究了總結帖的4個思路,發現4種方法都無法避開這個問題,使用這4種思路的結果,都會造成「Order_XOrder_Y中越小的數,返回比較小的數的機率」會比較大。
最左上的格子,返回1的機率會比所有其它格子都大,也會比理想中的1/81還要大
這個應該有辦法寫VBA驗證,但是我VBA還不熟,所以目前無法寫出來
麻煩版主看一下我這個想法有沒有問題,如果有人能幫忙寫VBA驗證我會很感謝
另外還是要回到我一開始的問題:是否有辦法用公式,寫一個1~n不重複亂數的內存數組?

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-7-26 12:37 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 胡剑0227 于 2013-7-29 09:02 编辑
Bodhidharma 发表于 2013-7-26 07:35
版主你好:因為想要研究「不覆複亂數」,經你的推薦,仔細研讀了這個帖子,但是 ...


回答最后一个问题:是否有办法用公式,写一个1-n不重复乱数的内存数组?

我曾经做过很多努力最后是没有成功,我想在现有的函数条件下可能真的无法实现,不是思路技巧受限,而是本质的计算机制上就行不通。
我以前为了产生随机性就会用上RAND,后来发现RAND是在太"活"了,比如直接写 =RAND()=RAND(),它也会直接返回FALSE对吧?所以公式中的不同RAND其实不是同一个随机数;另外在A1:A10中输入 =IF(ROW(1:10),RAND()),虽然用F9测试是相同的,但返回到A1:A10时却是不同的,也就是所在数组公式联合区域的每个单元格中它也会触发重算...所以这个太“活”了

我也想过用NOW函数,取它的小数部分,同样由于落入数组公式联合区域的每个单元格时都要重新计算,所以同一个NOW实际是不同的时间点,即也挺活,虽然比RAND好多了,但还是不能保证。

原因就是在录入的时候这些随机性还在触发,所以不能有效控制。还是在单元格区域中写一个不重复的,然后引用吧,呵呵。
或者就提供一个可以指定 起始值、终止值、的随机序列的预定义函数吧。
==================================================================================================================
哈,一个人确实容易沉浸在老的思路中,你上面提到的那个思路是很不错的,依稀感觉曾经看到过这个思路...发现自己又惯性到以往错误的坑了,汗颜

TA的精华主题

TA的得分主题

发表于 2013-7-26 12:46 | 显示全部楼层
本帖最后由 Bodhidharma 于 2013-7-26 12:48 编辑
胡剑0227 发表于 2013-7-26 12:37
回答最后一个问题:是否有办法用公式,写一个1-n不重复乱数的内存数组?

我曾经做过很多努力最后是没有 ...

感謝說明!
看來要完全隨機的話,還是得用(A2儲存格下拉)
  1. =SMALL(IF(COUNTIF(A$1:A1,ROW($1:$9))=0,ROW($1:$9)),RANDBETWEEN(1,10-ROW(A1)))
复制代码
之類的方式,然後再引用了
不過實務上,
  1. =MOD(SMALL(RANDBETWEEN(ROW($1:$9)^0,999999)/1%+ROW($1:$9),ROW($1:$9)),100)
复制代码
的方式,幾乎已經達到完全隨機了,應該也還是可以使用啦
另外,不知道我上篇其它部分的分析胡大怎麼看呢?

点评

恩,确实是新的思路和方法,我上面还沉浸在“死胡同”中,呵呵,学习了  发表于 2013-7-29 09:04

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-7-26 12:50 | 显示全部楼层
本帖最后由 delete_007 于 2013-7-26 13:22 编辑
Bodhidharma 发表于 2013-7-26 07:35
版主你好:因為想要研究「不覆複亂數」,經你的推薦,仔細研讀了這個帖子,但是 ...

问题确实存在,概率差异会随着随机取数区间的增大而减小。
随机区间为:[1,2] 那么第一位是1的概率是(1+2)/(2*2)=3/4
随机区间为:[1,100]那么第一位是1的概率是(1+100)/(2*100)=101/200
随机区间为:[1,10000]那么第一位是1的概率是(1+10000)/(2*10000)=10001/20000
对于三个数的随机排序,第一位是1的绝对随机概率应该是1/3
随机区间[1,3],第一位是1的概率:(3^2+2^2+1^2)/3^3=14/27
随机区间[1,100],第一位是1的概率:(100^2+99^2+……+1^2)/100^3=0.33835
随机区间[1,10000],第一位是1的概率:(10000^2+9999^2+……+1^2)/10000^3=0.333383335(已经非常接近1/3了,实际运用已足够。)
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 00:28 , Processed in 0.048607 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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