Excel數據分析:抽樣設計

一、隨機數發生器

1. 隨機數發生器主要功能

“隨機數發生器”分析工具可用幾個分布之一產生的獨立隨機數來填充某個區域?？梢酝ㄟ^概率分布來表示總體中的主體特征。例如，可以使用正態分布來表示人體身高的總體特征，或者使用雙值輸出的伯努利分布來表示擲幣實驗結果的總體特征。

2. 隨機數發生器對話框簡介

隨機數發生器對話框

該對話框中的參數隨分布的選擇而有所不同，其余均相同。

變量個數：在此輸入輸出表中數值列的個數。

隨機數個數：在此輸入要查看的數據點個數。每一個數據點出現在輸出表的一行中。

分布：在此單擊用于創建隨機數的分布方法。包括以下幾種：均勻分布、正態分布、伯努利分布、二項式、泊松、模式、離散。

隨機數基數：在此輸入用來產生隨機數的可選數值?？稍谝院笾匦率褂迷摂抵祦砩上嗤碾S機數。

輸出區域：在此輸入對輸出表左上角單元格的引用。如果輸出表將替換現有數據，Excel 會自動確定輸出區域的大小并顯示一條消息。

新工作表：單擊此選項可在當前工作簿中插入新工作表，并從新工作表的 A1 單元格開始粘貼計算結果。若要為新工作表命名，請在框中鍵入名稱。

新工作簿：單擊此選項可創建新工作簿并將結果添加到其中的新工作表中。

3. 隨機數發生器應用舉例

3.1 均勻隨機數的產生

均勻：以下限和上限來表征。其變量是通過對區域中的所有數值進行等概率抽取而得到的。普通的應用使用范圍 0 到 1 之間的均勻分布。相當于工作表函數：“= a+RAND()*(b-a)”，與RANDBETWEEN (a,b)”的區別是，RANDBETWEEN產生的是離散型隨機數，而隨機數發生器產生的是連續型隨機數。

離散型函數產生可重復隨機數，若想產生無重復隨機數，應使用連續型，再從中利用RANK函數產生整型。通常在進行抽樣設計時要產生無重復的整型均勻隨機數。

例：在編號為1至20之間隨機抽取10個無重復的均勻隨機數。

均勻隨機數對話框

單擊“確定”生成連續型隨機數（如圖）。

產生隨機數

由圖可見，所產生的是連續型隨機數，若四舍五入取整，在B1單元格輸入公式“=ROUND(A1,0)”，并復制到B1:B10，得到整型隨機數。

由圖可見，數字13出現了兩次，為可重復隨機數。在統計調查時，不能對同一調查對象調查兩次，應產生無重復隨機數。處理的辦法如下：

在A列對總體進行編號；在B2輸入公式“=RAND()”，生產0至1之間的均勻隨機數，并復制到B3:B21；C列顯示樣本序號；選擇D2:D11單元格區域，在D2單元格輸入公式“=RANK(B2:B21,B2:B21)”，按住Ctrl+Shift不放再按回車鍵，生成隨機數。該隨機數是無重復的。當然也可由VLOOKUP函數實現，所處從略。

無重復隨機數的產生

3.2 正態隨機數的產生

正態分布描述：

正態分布描述

正態：以平均值和標準偏差來表征，相當于工作表函數“=NORMINV(rand(),mu,sigma)”

例：產生10行8列來自均值為100、標準差為10的總體隨機數。

隨機數發生器選擇“分布”為“正態”，設置對話框如下：

隨機數發生器對話框的正態分布設置

單擊“確定”生成隨機數如下：

產生的正態分布隨機數

3.3 產生0-1分布隨機數

伯努利：以給定的試驗中成功的概率（p 值）來表征。伯努利隨機變量的值為 0 或 1。等價于函數：“=IF(RAND())”.

例：產生5列10行的成功概率為0.5的0-1隨機數。驗證概率的頻率法定義。

隨機數發生器“分布”選擇柏努利，設置對話框如下：

0-1隨機數對話框

單擊“確定”生成隨機數。

在G列輸入累積的試驗次數；H2輸入公式，統計正態朝上的次數（1的個數）；I2求得頻率(=H2/G2)；將H2:I2復制到H3:I21單元格區域。

以H列為橫坐標，I列為縱坐標，繪制不帶標志點的折線型散點圖。由圖可見，隨機試驗次數的增加，頻率逐步趨于0.5

頻率法概率定義的驗證

3.4 產生二項分布隨機數

二項式：以一系列試驗中成功的概率（p 值）來表征。例如，可以按照試驗次數生成一系列伯努利隨機變量，這些變量之和為一個二項式隨機變量。

二項分布描述：

二項分布描述

例：某射手中靶的概率為0.8，每次射擊10發子彈，射擊10次，模擬每次中靶的次數。

隨機數發生器選擇“分布”為“二項”，設置對話框如下：

隨機數發生器對話框的二項分布設置

單擊“確定”生成隨機數如下：

產生的二項分布隨機數

3.5 產生泊松分布隨機數

泊松：以值 λ 來表征，λ 等于平均值的倒數。泊松分布經常用于表示單位時間內事件發生的次數，例如，汽車到達收費停車場的平均速率。其描述如下：

泊松分布描述

例：某加油站，平均每小時前來加油的車輛為10輛，試進行100次模擬，并求其分布情況。

隨機數發生器選擇“分布”為“泊松”，設置對話框如下：

隨機數發生器對話框的泊松分布設置

單擊“確定”生成隨機數如下：

產生的泊松分布隨機數

求得最大值，最小值，確定組限，利用frequency函數統計頻數，并求頻率如下圖。選擇P2:P10單元格區域，在P2單元格輸入公式“=FREQUENCY(A1:J10,O2:O10)”,同時按ctrl+shift+enter：

頻數統計

3.6 產生重復序列

模式：以下界和上界、步幅、數值的重復率和序列的重復率來表征。在生物遺傳學中常用到重復序列。EXCEL的“模式”所產生的重復序列是按相同步長產生的重復序列。

如：下列對話框設置：

重復序列對話框

可產生的重復序列為：112233112233112233

3.7 產生離散隨機數

離散：以數值及相應的概率區域來表征。該區域必須包含兩列，左邊一列包含數值，右邊一列為與該行中的數值相對應的發生概率。所有概率的和必須為 1。

例如：某商品銷售情況根據某段時期統計如下（經驗分布）：

試進行80次模擬。

（1）在A列和B列輸入參數（經驗分布）

（2）隨機數發生器選擇“離散”，設置如下：

離散分布對話框

（3）單擊確定，在C1:M8產生80個隨機數。

（4）對產生的隨機數利用frequency函數統計頻數，并求頻率（略）。

二、抽樣

“抽樣”分析工具以數據源區域為總體，從而為其創建一個樣本。當總體太大而不能進行處理或繪制時，可以選用具有代表性的樣本。如果確認數據源區域中的數據是周期性的，還可以僅對一個周期中特定時間段中的數值進行采樣。例如，如果數據源區域包含季度銷售量數據，則以四為周期進行采樣，將在輸出區域中生成與數據源區域中相同季度的數值。

1.隨機抽樣

（1）打開一張工作表，輸入總體編號或總體標志值（本例A2:J11單元格區域，使用“填充”-“序列”可以快速生成該區域）。

隨機抽樣

（2）抽樣對話框設置：

隨機抽樣對話框設置

單擊“確定”生成隨機樣本。注意，該樣本是可重復抽樣，重復率與總體單位數成反比，與樣本量成正比。

2.周期抽樣

例：從1至10編號按固定周期間隔分別為2、3、4、5抽樣。

周期抽樣對話框設置

單擊“確定”抽得樣本（D列），取間隔依次取3、4、5，輸出區域依次改為E2、F2、G2，得隨機數如圖。

周期抽取的樣本

該種抽樣類似等距抽樣，但不同的是統計學中的等距抽樣是在第1組進行簡單隨機抽樣，以后的樣本等于首樣本位置依次加組距的k倍。