[第92期]随机九宫格[已总结]

swyrlbq

用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),"")

little-key

以上是附上的附件

cleverzhzhf

刚刚看到此帖子，突然发现Delete老师穿越了？哈哈，上图：

羽希

开赛前后并未公布评分标准，有失公允，如果是以公式长度为标准，我的答案也可以简化：
=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,),"")
参赛的家人都是抱着兴趣参加的，如果不能公开公平公正，大家的兴趣就会少很多的。

delete_007

羽希 发表于 2013-5-3 14:31
开赛前后并未公布评分标准，有失公允，如果是以公式长度为标准，我的答案也可以简化：
=IFERROR(VLOOKUP(( ...

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

羽希

delete_007 发表于 2013-5-3 14:53
这个公式不满足位置和数字绝对随机的要求。
一直以来，竞赛区都是以字符论英雄的，这次也不例外，评分标 ...

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

Bodhidharma

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，然後由小到大排列後，取其個位數

但是這樣子似乎不完全隨機：

比方說1和2隨機加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_X和Order_Y中越小的數，返回比較小的數的機率」會比較大。

最左上的格子，返回1的機率會比所有其它格子都大，也會比理想中的1/81還要大

這個應該有辦法寫VBA驗證，但是我VBA還不熟，所以目前無法寫出來

麻煩版主看一下我這個想法有沒有問題，如果有人能幫忙寫VBA驗證我會很感謝

另外還是要回到我一開始的問題：是否有辦法用公式，寫一個1~n不重複亂數的內存數組？

胡剑0227

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好多了，但还是不能保证。

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

Bodhidharma

胡剑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)

复制代码

的方式，幾乎已經達到完全隨機了，應該也還是可以使用啦
另外，不知道我上篇其它部分的分析胡大怎麼看呢？

delete_007

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了，实际运用已足够。）