如何使用Excel2007建立常態分布曲線圖表

如何使用Excel2007建立常態分布曲線圖表常態分布曲線(normal distribution)」是我們日常工作中很常使用到的一項工具,舉凡班級上的學科成績分佈,公司對員工的績效考核評等分佈,或是產品尺寸的量測分佈,這些都是常態分佈的表現之一。

姑且不論你到底對常態曲線懂得多少,如果你可以在日常的產品分析報告中把這個常態分佈給它用Excel給畫出,相信可以讓你的老闆對你刮目相看,當然如果可以的話,還是去瞭解一下何謂常態分佈。

相關閱讀:標準差與常態分佈的關係(six sigma)







下列的範例以Microsoft的原本說明當參考,因為我的Excel是英文版,所以部份解釋也是英文。範例基本上使用了Excel的 [Data Analysis(分析工具箱)] 中的「Random Number Generation(亂數產生器)」工具來產生資料,並使用「XY Scatter(色皆分佈圖)」來產生鐘型曲線,還可以再加上「Histogram(直方長條圖)」一起配合。

若要建立範例鐘型曲線,請依照下列步驟執行:

步驟1. 使用Excel建立鐘型曲線(常態分佈曲線)的準備工作

  1. 啟動 Excel。
  2. 在新的工作表中輸入下列資料行標題:
    A1:原始資料,B1:平均值,C1:Bin,D1:亂數,E1:直方圖,G1:直方圖

    如何使用Excel2007建立常態分布曲線圖表

  3. 在相同的工作表中輸入下列資料:
      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

    Excel常態分布02

    AVERAGE公式會產生的平均值。STDEV公式會產生標準差。

  4. 接著再輸入下列公式來產生直方圖或色階分佈圖的範圍:
       C2: =$B$2-3*$B4


    這個公式會產生區間範圍的下限。這個公式代表我們把下限設為三個標準差,所以用中心值減掉三個表準差。

    再輸入下面的公式:

       C3: =C2+$B$4

     

    記得要加上【$】號,否則下面的公式可能會跑掉。這個值是用來計算每個數值計算的刻度(間距),這裡我們把每個刻度用一個表準差來計算,所以每個儲存格都累加上一個表準差($B$4 )的值。

    選取儲存格 C3,抓取填滿控點,然後填入公式向下從儲存格 C3 的儲存格 C8。

    Excel常態分布03

步驟2. 產生鐘型曲線(常態分佈曲線)的亂數資料

接著要產生隨機數值,這是用來形成鐘型曲線(常態分佈曲線)的基礎,請依照下列步驟執行:

  1. 按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。

    Excel常態分布04 
    Excel 2013預設是沒有開啟[Data Analysis(資料分析)]功能的,你必須前往 [檔案] 》[選項] 》[增益集]。在 [管理] 方塊中,按一下 [COM 增益集] 》[執行]。檢查 [ Microsoft Office Microsoft Excel 2013 中的[Power Pivot] 方塊中,然後按一下[確定]。

  2. 在[Data Analysis(分析工具)] 方塊中,按一下 [Random Number Generation(亂數產生器)],然後按一下[OK(確定)]Excel常態分布05
  3. 在[Number of Variables(變數數字)] 中,鍵入1。
  4. [Number of Random Numbers(數字的隨機數字)] 方塊中,輸入2000。

    注意: 這個數字表示將產生的亂數數目,增加或減少這個數字將會影響鐘型曲線的正確性。

  5. 在[Distribution(發佈)] 方塊中,選取 [Normal(一般)]。
  6. 在 [Parameters(參數)] 窗格中的[Mean(平均值)],輸入儲存格 B2 中計算出的平均值 (在範例中為(29.25)] 。
  7. 在 [Standard deviation(標準差)] 方塊中輸入儲存格 B4 中計算出的數值 (14.8589)。
  8. [Random Seed)隨機種子]方塊保留空白。
  9. 在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並輸入 D2 或是 $D$2。如果點選後面的選取方塊從表格說選取就會出現$D$2絕對位址。

    Excel常態分布06

  10. 按一下[OK(確定)]後,會在D2:D2001的輸出範圍產生 2000 組常態分配的隨機數字(就是依照平均值與標準差所產生的亂數常態分佈,所以可以繪製出表較漂亮的常態鐘擺曲線)。

    Excel常態分布07

步驟3. 製作統計直方圖與鐘型曲線(常態分佈曲線)的亂數資料

接著建立直方圖的隨機資料,請依照下列步驟執行:

  1. 按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。
  2. 在[Data Analysis(分析工具)] 方塊中,按一下 [Histogram(直方圖)],然後按一下[OK(確定)]

    Excel常態分布08

  3. 在[Input Range(輸入範圍)]  空格中,輸入D2:D2001$D$2:$D$2001
  4. 在[Bin Range(區間範圍)] 方塊中,輸入C2:C8 或 $C$2:$C$8。
  5. 在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並在其空格中輸入E2$E$2

    Excel常態分布09 

  6. 按一下[OK(確定)]後,會在E2:F10的輸出範圍產生 7 組統計亂數後的間距與數量。

    如何使用Excel2007建立常態分布曲線圖表

步驟4. 建立原始數據的長條圖統計資料

若要建立原始資料長條圖,請依照下列步驟執行:

  1. 按一下水平功能表的 [Data(資料)],然後選取帶狀功能表上的[Data Analysis(資料分析)]。
  2. 在[Data Analysis(分析工具)] 方塊中,按一下 [Histogram(直方圖)],然後按一下[OK(確定)]
  3. 在[Input Range(輸入範圍)]  空格中,輸入A2:A9$A$2:$A$9
  4. 在[Bin Range(區間範圍)] 方塊中,輸入C2:C8 或 $C$2:$C$8。
  5. 在 [Output options(輸出選項)] 窗格中,按一下 [Output Range(輸出範圍)],並在其空格中輸入G2$G$2

    Excel常態分布11

  6. 按一下[OK(確定)]後,會另外在E2:F10的輸出範圍產生 7 組統計亂數後的間距與數量。

    Excel常態分布12

    建議可以把Bin的格式去掉小數點,這樣數字會比較漂亮。

步驟5. 繪製直方圖常態分佈

  1. 選取工作表上的 E2:F10 範圍的儲存格。
  2. 按一下水平選單 [Insert(插入)] ,然後選取帶狀功能表的[Column(直條圖)]的第一個 [2-D Column]。Excel常態分布13
  3. Excel會自動出現直方圖的結果如下圖。

    如何使用Excel2007建立常態分布曲線圖表

步驟6. 繪製XY(散佈)的常態分佈圖

  1. 在已經建立好的直方圖上點滑鼠右鍵,從快顯功能表中選取 [Select Data(選擇來源資料)]。

    如何使用Excel2007建立常態分布曲線圖表

  2. 出現[Select data Source]對話框,點選 [Add]按鈕新增一組資料。

    Excel常態分布16

  3. 在[Edit Series]對話框,[Series name]表留空白就可以了,[Series Values]的地方輸入 F3:F10,也可以點選空白框的後方選擇鈕從工作表上選擇,這時後會出現工作表的名稱前綴,因為我的範例在Sheet2,所以就出現在前綴,大部分的朋友工作表示都會是Sheet1才對。輸入完畢後按 [OK(確認)]

    Excel常態分布17

  4. 回到 [Select Data Source(選擇資料來源)] 對話框,直接點擊[OK(確定)]就可以了。

    Excel常態分布18

  5. 出現兩組長條圖了。用滑鼠左鍵選取心增加的紅色直條圖,然後按滑鼠右鍵,開啟快顯功能表,點選 [Change Series Chart Type(變更圖表類型)]。

    如何使用Excel2007建立常態分布曲線圖表

  6. 選擇[ XY (scatter) 散佈]的第三個圖形。如何使用Excel2007建立常態分布曲線圖表
  7. 常態分佈曲線外加直條圖就初步完成囉!

    如何使用Excel2007建立常態分布曲線圖表

  8. 剩下的可以自行調整長條形的間隙大小百分比,以及其它的設定,做出更漂亮的分佈曲線。

步驟7. 繪製原始資料的常態分佈與直條圖

  1. 接下來可以依照[步驟5]及[步驟6]的方法重新繪製新的直條圖與常態分佈圖。
  2. 這次的資料來源要選取工作表上的 G2:H10 範圍的儲存格。(原始資料的分佈)。
  3. 最終結果會如下圖。 如何使用Excel2007建立常態分布曲線圖表

這裡我把兩組圖表放在這裡當比較,有沒有發現使用【亂數】所做出來的圖表比較接近常態分佈,而使用原始資料作出來的圖表這呈現出雙峰的結果,這是因為原始資料的數量不夠多所至,只有八組資料,所以才會有失真的情形,一般建議統計的資料最少要有25或30組以上才能稍微表現出代表性。


Excel常態分布的注意事項:


延伸閱讀:

關於統計製程SPC:




訪客留言內容(Comments)

請問工作狂人文章中的圖片紅線是用哪套編輯軟體?

Eric;
PhotoCap

謝謝您!一直找不到該怎麼把常態分佈的曲線畫出來,看到您的文章才知道原來是這樣作!真的謝謝您!

版主您好,
想請問Office 2013 excel裡的date analysis放置在哪,我都找不到>”<

Emily;
Excel 2013預設是沒有開啟[data analysis]功能的,你必須前往 [檔案] > [選項] > [增益集]。在 [管理] 方塊中,按一下 [COM 增益集] > [執行]。檢查 [ Microsoft Office Microsoft Excel 2013 中的[Power Pivot] 方塊中,然後按一下[確定]。如果您有安裝Power Pivot增益集的其他版本,這些版本也會列在 [COM 增益集] 清單中。請務必選取Power Pivot增益集的 Excel。

請問Office 2007 excel裡的Data Analysis(資料分析)選項,不知道要到哪裡找出來,找很久都找不到??麻煩教我一下,謝謝….

版大您好,請問,此範例中,假設原始資料的8組數字是價格,那分布圖的橫軸、縱軸各代表甚麼?

Apollo,
老實說不瞭解你這樣的統計有何意義。
柏拉圖只是數量的統計,自己要瞭解為何要做這個柏拉圖,如果連自己都不瞭解,為何要做柏拉圖?

工作熊您好,
Excel 2007年版中,可經由以下方法成功新增 [資料分析] 功能到[資料]功能表中(即藉由載入 [分析工具箱] 的增益集程式達成。),敬請參考,謝謝!(方法來自 2007年版中的說明)
1.按一下 [Office 按鈕] ,然後按一下 [Excel 選項]。
2.按一下 [增益集],然後選取 [管理] 方塊中的 [Excel 增益集]。
3.按一下 [執行]。
4.在 [現有的增益集] 方塊中,選取 [分析工具箱] 核取方塊,然後按一下 [確定]。
PS.
1.如果 [分析工具箱] 未出現在 [現有的增益集] 方塊中,請按一下 [瀏覽] 找到工具的所在位置。
2.如果出現訊息,指出電腦上目前未安裝 [分析工具箱],請按一下 [是] 進行安裝。

謝謝!

Jerry


訪客留言注意事項:
1.首次留言須通過審核後內容才會出現在版面上,請不要重覆留言。
2.留言時請在相關主題文章下留言,與主題不相關的留言將會被視為垃圾留言,請善加利用【搜尋框】尋找相關文章,找不到主題時請在「水平選單」的「留言板」留言。
3. 留言前請先用【搜尋框】尋找相關文章,自己做一點功課後再留言。沒有前因後果的內容,工作熊不一定會瞭解你在說什麼,就更無法回答你的問題。
4. 工作熊並非某一方面的專家,所以回答的內容或許會有不正確的地方,服用前還請三思。如果您想詢問關於電路板方面的工程問題,前先參考這篇文章【詢問工程問題,請提供足夠的資訊以利有效回答】 把自己的問題想清楚了再來詢問,並且請提供足夠的資訊,這樣才能有效回答問題。
5. 工作熊每則留言都會看,但不會每則留言都回答,尤其是只有問候之類的內容。
6. 留言詢問時請注意您的態度,工作熊不是你的「細漢」,更沒有拿你的薪水,所以不接受吆喝工作熊的態度來回答你的問題。
7. 歡迎您訂閱本部落格的最新文章,當有新文章時會主動以電子郵件通知你。

您有話要說(Leave a comment)

(required)

(required)