Excel 數據分析中的抽樣設計之隨機數發生器 隨機抽樣 周期抽樣（excel表格）

隨機數發生器分析工具可用幾個分布之一產生的獨立隨機數來填充某個區域。可以通過概率分布來表示總體中的主體特征，抽樣分析工具以數據源區域為總體，從而為其創建一個樣本。當總體太大而不能進行處理或繪制時，可以選用具有代表性的樣本

一、隨機數發生器

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

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

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

隨機數發生器對話框

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

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

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

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

隨機數基數：在此輸入用來產生隨機數的可選數值。可在以后重新使用該數值來生成相同的隨機數。

輸出區域：在此輸入對輸出表左上角單元格的引用。如果輸出表將替換現有數據，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函數統計頻數，并求頻率（略）。

備注：

數據文件：鏈接: https://pan.baidu.com/s/1c15bjTQ 密碼: sdhe

二、抽樣

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

1.隨機抽樣

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

隨機抽樣

（2）抽樣對話框設置：

隨機抽樣對話框設置

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

2.周期抽樣

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

周期抽樣對話框設置

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

周期抽取的樣本

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

備注：

數據文件：鏈接: https://pan.baidu.com/s/1qYvz2qC 密碼: ugmb

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。