【電腦】如何讓數據「造假」更快更真?






看了前些天老祝的一個專門「造假」的函數,你用過嗎?,覺得迭代計算用的比較少,開開關關有點麻煩,就想著怎麼用公式來解決,順便擴展了一下題目:「生成在指定範圍內,均值指定的n個隨機數」,比如生成n個70-130範圍內,均值為100的隨機數。

以7個數字為例,D1單元格為指定均值。

第一種思路是輔助法。

生成7個隨機數,求它們的平均值,然後按照各個隨機數與平均值的差等比擴大或縮小指定均值。操作步驟為:

1、首先添加輔助行,在A3單元格輸入公式向右填充,得到7個隨機數。

=RAND()

2、在A4單元格輸入公式後向右填充,即可得到答案。

=(1+A3-AVERAGE($A$3:$G$3))*$D$1

【電腦】如何讓數據「造假」更快更真?

這種方法有兩個瑕疵,第一個是需要添加輔助列,很多強迫症患者看起來很難受。第二個是最值的邊界難以確定,需要控制隨機數的平均值。這種思路我還想不出解決擴展問題的辦法。

第二種思路是均衡法。

【電腦】如何讓數據「造假」更快更真?

公式分為前、中、後三部分。

開始部分,在A5單元格輸入公式,生成一個隨機數:

=(B2-B3)*RAND()+B3

中部將已經生成的隨機數的均值(簡稱現有均值)與指定均值比較,若現有均值低於指定均值,生成一個大於指定均值、小於最大控制值的數;若高於指定均值,生成一個小於指定均值、大於最小控制值的數。

在B5輸入以下公式向右復制到F5單元格:

=IF(AVERAGE($A5:A5)<$B$1,($B$2-$B$1)*RAND()+$B1,($B1-$B3)*RAND()+$B3)

尾部(最後一個數)直接根據之前已生成的數據及指定均值計算所需要的數據。

G5單元格公式為:

=7*$B$1-SUM($A5:F5)

注意:這種方法對於生成偏離程度一致的隨機數有效,例如100±30。但是對於偏離程度不一致的就會出現超出範圍的情況。


圖文作者:鍋仔餅

原載:Excel之家ExcelHome