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

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

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

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

YouTube影片:如何使用Excel畫出標準的常態分佈曲線(normal distribution)?

影片的內容比本文的說明有更多的資訊與內容更新,如果你是使用Office365的Excel建議觀看這支YouTube的影片以了解更多關於如何使用Excel繪製常態分佈圖的說明。

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

下列的範例以Microsoft的原本說明當參考,因為工作熊的Excel是英文版,所以部份解釋也是英文。

範例基本上使用了Excel的 [Data Analysis(分析工具箱)] 中的「Random Number Generation(亂數產生器)」工具來產生資料,並使用「XY Scatter(色階分佈圖)」來產生鐘型曲線,還可以再加上「Histogram(直方長條圖)」一起配合。

請注意:本文中使用Excel畫出來的常態分佈曲線其實是利用我們原本的數值資料所計算出來的標準差與中心值,然後再用亂數所模擬產生出來的曲線,它有一個假設前提,就是數據的分佈必須為常態分佈,如果你無法確定資料為常態分佈,那畫出來的結果就可能失真,比如說生產出來的產品有先經過不良品挑選,那麼它的分佈就會是前後被截斷的常態分佈,而不是正常的常態分佈了,再比如說如果是單邊規格的產品,例如電池電壓,其分佈就可能為偏態分佈。

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

步驟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

你好 想請教一下
從資料分析-》直方圖 得出的「頻率」
這個頻率有沒有計算公式可得出數值呢?
因為資料分析得出的數值
要每次手動點選分析
我想做出根據填入資料自動變化的圖

組界的表格已經可以用公式得到
現在在想頻率要怎麼得到
感謝~

丹尼斯,
這個真的考倒我了~

您好,假設數值有分佈範圍有限,例0~100,請問若平均值加上三倍標準值差之後,所得值超上限100,如何處理。

常態分佈與數據是否超出規格上下界無關,也就是超出規格也可以劃出來。

直方圖的頻率是出現頻率
也就是在組距範圍之內的個數,沒有公式,因為就只是數數
只是從人工變成電腦
所以要自動變更的話要用count類的函數去寫

想請問 如果我的數據量有4465筆
那還需不需要亂數呢?
亂數取4464筆是對的嗎?

如果不需要亂數的話 要怎麼畫圖?

苦命研究生,
你可以試看看不取亂數後做出來的圖是否為常態分配,理論上4464筆的資料如果都是正確的,應該不需要取亂數,因為亂數是為了獲得一個常態分配表
如果做出來的圖不是常態分配,你可以要檢討資料是否有誤,還是取的間距有問題。

你好.我有118筆資料分析.但再步驟3完成後.頻率選項欄結果只有前七筆有大於0的數據.從第8筆到之後數值全是0.這樣是正確嗎?變成我輸出圖後.X軸圖片很難看.謝謝

成閎,
1.請先確認你量測出來的數據是對的,而且確認你量測的值屬於常態分佈。
2.無法從你的描述中了解你的問題。

在步驟1. 使用Excel建立鐘型曲線(常態分佈曲線)的準備工作,
第4項為什麼Bin那一欄的資料只有到C8, 為什麼不是C9?

阿東,
Bin的數據就只是為了標出:-1σ、-2σ、-3σ、中心值、1σ、2σ、3σ。

用google試算表也可生成類似的;不過因為沒有分析工具箱,有些步驟要修改

取亂數的地方要改成 =NORMINV(RAND(),B$2,B$4),貼在D2:D2001
因取完亂數後每次操作數值都會重新產生,所以得到亂數後要複製,貼上值,來變成固定的數值

找分布可以用=FREQUENCY(D:D,C2:C8)

果蠅,
有空找時間來試看看Google試算表

工作熊您好
突然被老闆交辦做圖
您的文章真是救了我一條命
謝謝您

版大好 點選橘色條狀更改類型(XY線型)後 僅剩下XY型圖原來的柱狀不見了 只剩下XY線型 請問該如何調整呢 感謝

RL,
文章已經更新增加Office365之Excel的操作步驟及圖示。

太感謝工作熊~
原本在YT看影片學,但後來沒成功,看了工作熊這篇文章,在1小時內就將圖畫出來,謝謝將每個步驟說明得很詳細

Q菜,
所以,我是不是也應該來做一段YT影片?

請教用亂數做出來的常態分佈,為何中間值不是當初設定的平均值?

阿華田,
這是因為我們採用的分組與間距是整數標準差所致。讓分組的邊界剛好在規格的中心~
如果把分組的開始變成2.5σ,這樣就會在中心值了。

請問如何在常態分佈圖上,再加入上、下限呢?

美祿,
網路上有人提供方法,可以自己搜尋參考,但都不是很好用。

請問如果我要加入上下界限,該如加入??

Steven,
手動畫上去吧!如果你有更好的方法,我也想知道。

假如是分數級距類型的該怎麼做出常態分佈圖呢,有標準差也有平均值,但是沒有每筆原始資料,只有每組個別人數

老胡,
本文中使用Excel畫出來的常態分佈曲線其實是就是利用原本數值資料所計算出來的標準差與中心值,然後再用亂數所模擬產生出來的曲線。建議你可以觀看文章中的影片,會有更多的解釋。


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

您有話要說(Leave a comment)

(required)

(required)