|
![]() |
如何使用Excel2007建立常態分佈曲線圖表
「常態分佈曲線(normal distribution)」又名「正態分佈(大陸用語)」也稱「高斯分佈(gaussian distribution)」是我們日常工作中很常使用到的一項工具,舉凡班級上的學科成績分佈,公司對員工的績效考核評等分佈,或是產品尺寸的量測分佈,這些都是常態分佈的表現之一。
姑且不論你到底對常態曲線懂得多少,如果你可以在日常的產品分析報告中把這個常態分佈給它用Excel給畫出,相信可以讓你的老闆對你刮目相看,當然如果可以的話,還是去瞭解一下何謂常態分佈。
YouTube影片:如何使用Excel畫出標準的常態分佈曲線(normal distribution)?
影片的內容比本文的說明有更多的資訊與內容更新,如果你是使用Office365的Excel建議觀看這支YouTube的影片以了解更多關於如何使用Excel繪製常態分佈圖的說明。
如何使用Excel2007建立常態分佈曲線圖表
下列的範例以Microsoft的原本說明當參考,因為工作熊的Excel是英文版,所以部份解釋也是英文。
範例基本上使用了Excel的 [Data Analysis(分析工具箱)] 中的「Random Number Generation(亂數產生器)」工具來產生資料,並使用「XY Scatter(色階分佈圖)」來產生鐘型曲線,還可以再加上「Histogram(直方長條圖)」一起配合。
請注意:本文中使用Excel畫出來的常態分佈曲線其實是利用我們原本的數值資料所計算出來的標準差與中心值,然後再用亂數所模擬產生出來的曲線,它有一個假設前提,就是數據的分佈必須為常態分佈,如果你無法確定資料為常態分佈,那畫出來的結果就可能失真,比如說生產出來的產品有先經過不良品挑選,那麼它的分佈就會是前後被截斷的常態分佈,而不是正常的常態分佈了,再比如說如果是單邊規格的產品,例如電池電壓,其分佈就可能為偏態分佈。
若要建立範例鐘型曲線,請依照下列步驟執行:
步驟1. 使用Excel建立鐘型曲線(常態分佈曲線)的準備工作
- 啟動 Excel。
- 在新的工作表中輸入下列資料行標題:
A1:原始資料,B1:平均值,C1:Bin,D1:亂數,E1:直方圖,G1:直方圖 - 在相同的工作表中輸入下列資料:
A1: 原始資料 B1: 平均值 A2: 22 B2: =AVERAGE(A2:A9) A3: 26 B3: 標準差 A4: 11 B4: =STDEV(A2:A9) A5: 25 A6: 28 A7: 58 A8: 44 A9: 20
AVERAGE公式會產生的平均值。STDEV公式會產生標準差。
- 接著再輸入下列公式來產生直方圖或色階分佈圖的範圍:
C2: =$B$2-3*$B4
這個公式會產生區間範圍的下限。這個公式代表我們把下限設為三個標準差,所以用中心值減掉三個表準差。
再輸入下面的公式:
C3: =C2+$B$4
記得要加上【$】號,否則下面的公式可能會跑掉。這個值是用來計算每個數值計算的刻度(間距),這裡我們把每個刻度用一個表準差來計算,所以每個儲存格都累加上一個表準差($B$4 )的值。
選取儲存格 C3,抓取填滿控點,然後填入公式向下從儲存格 C3 的儲存格 C8。
步驟2. 產生鐘型曲線(常態分佈曲線)的亂數資料
接著要產生隨機數值,這是用來形成鐘型曲線(常態分佈曲線)的基礎,請依照下列步驟執行:
- 按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。
Excel 2013預設是沒有開啟[Data Analysis(資料分析)]功能的,你必須前往 [檔案] 》[選項] 》[增益集]。在 [管理] 方塊中,按一下 [COM 增益集] 》[執行]。檢查 [ Microsoft Office Microsoft Excel 2013 中的[Power Pivot] 方塊中,然後按一下[確定]。 - 在[Data Analysis(分析工具)] 方塊中,按一下 [Random Number Generation(亂數產生器)],然後按一下[OK(確定)]。
-
在[Number of Variables(變數數字)] 中,鍵入1。
-
在 [Number of Random Numbers(數字的隨機數字)] 方塊中,輸入2000。
注意: 這個數字表示將產生的亂數數目,增加或減少這個數字將會影響鐘型曲線的正確性。
-
在[Distribution(發佈)] 方塊中,選取 [Normal(一般)]。
-
在 [Parameters(參數)] 窗格中的[Mean(平均值)],輸入儲存格 B2 中計算出的平均值 (在範例中為(29.25)] 。
-
在 [Standard deviation(標準差)] 方塊中輸入儲存格 B4 中計算出的數值 (14.8589)。
-
[Random Seed)隨機種子]方塊保留空白。
-
在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並輸入 D2 或是 $D$2。如果點選後面的選取方塊從表格說選取就會出現$D$2絕對位址。
-
按一下[OK(確定)]後,會在D2:D2001的輸出範圍產生 2000 組常態分配的隨機數字(就是依照平均值與標準差所產生的亂數常態分佈,所以可以繪製出表較漂亮的常態鐘擺曲線)。
步驟3. 製作統計直方圖與鐘型曲線(常態分佈曲線)的亂數資料
接著建立直方圖的隨機資料,請依照下列步驟執行:
-
按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。
-
在[Data Analysis(分析工具)] 方塊中,按一下 [Histogram(直方圖)],然後按一下[OK(確定)]。
-
在[Input Range(輸入範圍)] 空格中,輸入D2:D2001 或 $D$2:$D$2001。
-
在[Bin Range(區間範圍)] 方塊中,輸入C2:C8 或 $C$2:$C$8。
-
在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並在其空格中輸入E2 或 $E$2。
-
按一下[OK(確定)]後,會在E2:F10的輸出範圍產生 7 組統計亂數後的間距與數量。
步驟4. 建立原始數據的長條圖統計資料
若要建立原始資料長條圖,請依照下列步驟執行:
-
按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。
-
在[Data Analysis(分析工具)] 方塊中,按一下 [Histogram(直方圖)],然後按一下[OK(確定)]。
-
在[Input Range(輸入範圍)] 空格中,輸入A2:A9 或 $A$2:$A$9。
-
在[Bin Range(區間範圍)] 方塊中,輸入C2:C8 或 $C$2:$C$8。
-
在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並在其空格中輸入G2 或 $G$2。
-
按一下[OK(確定)]後,會另外在E2:F10的輸出範圍產生 7 組統計亂數後的間距與數量。
建議可以把Bin的格式去掉小數點,這樣數字會比較漂亮。
步驟5. 繪製直方圖常態分佈
-
選取工作表上的 E2:F10 範圍的儲存格。
-
按一下水平選單 [Insert(插入)] ,然後選取帶狀功能表的[Column(直條圖)]的第一個 [2-D Column]。
- Excel會自動出現直方圖的結果如下圖。
步驟6. 繪製XY(散佈)的常態分佈圖
-
在已經建立好的直方圖上點滑鼠右鍵,從快顯功能表中選取 [Select Data(選擇來源資料)]。
-
出現[Select data Source]對話框,點選 [Add]按鈕新增一組資料。
-
在[Edit Series]對話框,[Series name]表留空白就可以了,[Series Values]的地方輸入 F3:F10,也可以點選空白框的後方選擇鈕從工作表上選擇,這時後會出現工作表的名稱前綴,因為我的範例在Sheet2,所以就出現在前綴,大部分的朋友工作表示都會是Sheet1才對。輸入完畢後按 [OK(確認)] 。
-
回到 [Select Data Source(選擇資料來源)] 對話框,直接點擊[OK(確定)]就可以了。
-
出現兩組長條圖了。用滑鼠左鍵選取心增加的紅色直條圖,然後按滑鼠右鍵,開啟快顯功能表,點選 [Change Series Chart Type(變更圖表類型)]。
- 選擇[ XY (scatter) 散佈]的第三個圖形。
- 常態分佈曲線外加直條圖就初步完成囉!
-
剩下的可以自行調整長條形的間隙大小百分比,以及其它的設定,做出更漂亮的分佈曲線。
步驟7. 繪製原始資料的常態分佈與直條圖
-
接下來可以依照[步驟5]及[步驟6]的方法重新繪製新的直條圖與常態分佈圖。
-
這次的資料來源要選取工作表上的 G2:H10 範圍的儲存格。(原始資料的分佈)。
-
最終結果會如下圖。
這裡我把兩組圖表放在這裡當比較,有沒有發現使用【亂數】所做出來的圖表比較接近常態分佈,而使用原始資料作出來的圖表這呈現出雙峰的結果,這是因為原始資料的數量不夠多所至,只有八組資料,所以才會有失真的情形,一般建議統計的資料最少要有25或30組以上才能稍微表現出代表性。
Excel常態分佈的注意事項:
-
使用亂數產生2000筆常態分佈的目的是為了讓常態分佈曲線劃出來更漂亮,但是這樣子也可能扭曲了原來的數據,因為這裡我們假設數據一定是常態分佈,所以其結果就是常態分佈。
-
如果數據屬於單邊規格,個人不建議使用這個亂數來產生數據,因為其結果會強迫超出單邊規格,造成誤判。比如說鈕扣電池的電壓,下面左圖的曲線來自原始的數據(半邊的常態分佈曲線),右圖則來自亂數產生的曲線,一般來說鈕扣電池的規格上界只有3.0V,但使用了常態分佈亂數的結果就會有超出3.0V的情形出現,這樣就變成異常了。
-
如果數具已經超過了25個以上,個人強烈建議使用原來的數據來劃分佈圖就可以了,通常25個以上(有人建議30個)的樣品數據就可以初步代表母體。
延伸閱讀:
關於統計製程SPC:
|
訪客留言內容(Comments)
// Begin Comments & Trackbacks ?>工作熊您好,
Excel 2007年版中,可經由以下方法成功新增 [資料分析] 功能到[資料]功能表中(即藉由載入 [分析工具箱] 的增益集程式達成。),敬請參考,謝謝!(方法來自 2007年版中的說明)
1.按一下 [Office 按鈕] ,然後按一下 [Excel 選項]。
2.按一下 [增益集],然後選取 [管理] 方塊中的 [Excel 增益集]。
3.按一下 [執行]。
4.在 [現有的增益集] 方塊中,選取 [分析工具箱] 核取方塊,然後按一下 [確定]。
PS.
1.如果 [分析工具箱] 未出現在 [現有的增益集] 方塊中,請按一下 [瀏覽] 找到工具的所在位置。
2.如果出現訊息,指出電腦上目前未安裝 [分析工具箱],請按一下 [是] 進行安裝。
謝謝!
Jerry
你好 想請教一下
從資料分析-》直方圖 得出的「頻率」
這個頻率有沒有計算公式可得出數值呢?
因為資料分析得出的數值
要每次手動點選分析
我想做出根據填入資料自動變化的圖
組界的表格已經可以用公式得到
現在在想頻率要怎麼得到
感謝~
用google試算表也可生成類似的;不過因為沒有分析工具箱,有些步驟要修改
取亂數的地方要改成 =NORMINV(RAND(),B$2,B$4),貼在D2:D2001
因取完亂數後每次操作數值都會重新產生,所以得到亂數後要複製,貼上值,來變成固定的數值
找分布可以用=FREQUENCY(D:D,C2:C8)
訪客留言注意事項:
1.首次留言須通過審核後內容才會出現在版面上,請不要重覆留言。
2.留言時請在相關主題文章下留言,與主題不相關的留言將會被視為垃圾留言,請善加利用【搜尋框】尋找相關文章,找不到主題時請在「水平選單」的「留言板」留言。
3. 留言前請先用【搜尋框】尋找相關文章,自己做一點功課後再留言。沒有前因後果的內容,工作熊不一定會瞭解你在說什麼,就更無法回答你的問題。
4. 工作熊並非某一方面的專家,所以回答的內容或許會有不正確的地方,服用前還請三思。如果您想詢問關於電路板方面的工程問題,請前先參考這篇文章【詢問工程問題,請提供足夠的資訊以利有效回答】 把自己的問題想清楚了再來詢問,並且請提供足夠的資訊,這樣才能有效回答問題。
5. 工作熊每則留言都會看,但不會每則留言都回答,尤其是只有問候之類的內容。
6. 留言詢問時請注意您的態度,工作熊不是你的「細漢」,更沒有拿你的薪水,所以不接受吆喝工作熊的態度來回答你的問題。
7. 原則上工作熊不接受私下電子郵件、電話、私訊、微信或任何即時通聯絡。
8. 自2021年7月起Google將停止最新文章電子郵件通知,如果你想隨時接收部落格的最新文章可以參考這裡。
請問工作狂人文章中的圖片紅線是用哪套編輯軟體?