Excel繪制指定區間的正態分布曲線下面積圖

正態分布曲線下面積是很有實際應用價值的。在工程能力指數的評估、產品質量分析和教育評估分析方面都發揮了很大作用。

在正態分布的密度函數中有上述兩個常數：算數平均數μ和標準差σ。正態分布的值有99.74%落在（μ-3σ，μ+3σ）區間內，也就是說落在以平均值為中心的左右各3個σ（共六個σ）的范圍內，所謂管理學中的“三西格瑪”或“六西格瑪”就源于此。Excel中可以使用正態分布的密度函數NORMDIST（x，μ，σ，邏輯值），來表達正態分布，其中：x —“值”，是要求分布的隨機變量數值；μ—“平均數”，是分布的算數平均數；σ—“標準差”，是分布的標準差；邏輯值—“積累與否”，是決定函數的邏輯值，其中取值為 “TRUE”（真），則返回累計分布函數；取“FALSE”（偽），則NORMDIST會返回正態分布函數的高度。如果為了繪制正態分布曲線，就要取“FALSE”。

Mly網友來信問起我在《Excel：正態分布函數曲線下的面積及其應用》中的圖-3. μ=60，σ=15，在（-∞，55）的正態分布曲線二維面積圖（藍斜紋）如下圖，是如何繪制的。

這篇《Excel：正態分布函數曲線下的面積及其應用》主要是談正態分布及其曲線下面積的應用，這張圖是運用的一個例子，至于圖表如何作出的具體步驟，文中就未作介紹。過后，正好有一位網友MZY來信問起了有關定積分的指定區間的曲線下面積的作圖，我又寫了一篇《答MZY：Excel指定區間的曲線下面積》，該文只是就Y=eX的曲線談了分幾個區間的曲線下面積的作圖，因為思路是一樣的，就未對分段的正態分布曲線作介紹。其實把這兩篇文章結合起來就可以解決類似于上圖的分區間的正態分布曲線下面積的繪制。

分區間段的面積圖有多種做法，一般使用覆蓋法，這樣對全程控制比較方便。但是要注意要先做全程的，然后從右到左，使后者逐次疊加覆蓋前者得一部分，每一系列的左端點應該是重合的，只是右端點不同，否則會出錯。上圖是μ=60，σ=15的正態分布曲線二維面積圖，看起來是對三個不同的、但相連接的區間（-∞，55]，（55，65]和（65，+∞）分別著色，其實是對（-∞，55]，（-∞，65]和（-∞，+∞）三個互有重疊的區間實施不同的顏色填充，由于數據系列的前后順序遮擋掩蓋，而在視覺上形成是對首尾互相連接的三個區間填充內部圖案的感覺。

首先，建立數據系列的表格，使用正態分布的密度函數NORMDIST（x，μ，σ，邏輯值），來表達區間（-∞，+∞）上的正態分布，其中：“平均數”μ=60；“標準差”σ=15；邏輯值“積累與否”取“FALSE”。A列設置隨機變量數值，在A2單元格設為0、A3單元格設1，等差值為1，選中A2：A3后，一直拖到A112得出數值為110為止。在B列設置正態分布的密度函數NORMDIST（x，μ，σ，邏輯值），使B2單元格的內容為“=NORMDIST(A2,60,15,FALSE)”，向下拖曳復制公式到B112，如表-1所示，B列的顯示的是公式：

表-1

如果使B列顯示的是數值，即如表-2所示：

表-2

先作系列1：

將區間（-∞，+∞）作為系列1，也就是全程，放在最下一層，

選中單元格區間A2：B112，插入二維面積圖，這就繪出μ=60，σ=15，在（-∞，+∞）的正態分布曲線圖，如圖-1所示?？梢娫撝虚g高兩頭低的鐘形圖形是以μ=60為對稱軸的軸對稱圖形，并以橫軸為漸近線：

圖-1

圖-1完成后，在2003版的“源數據”-“系列”中可以看到系列1的“值”和“分類（x）軸標志”設置，如圖-2所示：

圖-2

在2010版中點擊“圖表工具”-“數據”-“選擇數據”，如圖-2-2010-1所示：

圖-2-2010-1

在“選擇數據源”對話框中，可以看到系列1已設置，如圖-2-2010-2所示：

圖-2-2010-2

在“編輯數據系列”對話框中可以看到“系列值”中的設置，如圖-2-2010-3所示：

圖-2-2010-3

而“軸標簽”的設置，如圖-2-2010-4所示：

圖-2-2010-4

后續的數據系列必須在源數據中逐步添加。

添加系列2：

在“源數據”-“系列”-“系列”中添加系列2，將區間（-∞，65]作為系列2，“值”和“分類（x）軸標志”的設置如圖-3所示：

圖-3

圖-3中“值”的具體設置方法是：點擊“值”右側的拾取折疊按鈕，會彈出如圖-4所示的“源數據-數值”對話框，選取系列2數據所在的單元格B2：B67，“源數據-數值”對話框會出現設置如圖-4所示數據，再點擊“源數據-數值”對話框右側的拾取折疊按鈕，則折疊復位到圖-3。分類（x）軸標志”的設置方法也是利用右側的拾取折疊按鈕，選擇的范圍還是全程A2：A112.

圖-4

在2010版中，類似的設置如圖-4-2010-1與圖-4-2010-2所示：

圖-4-2010-1

圖-4-2010-2

確定后，圖表如圖-5所示：

圖-5

添加系列3：

在“源數據”-“系列”-“系列”中添加系列3，將區間（-∞，55]作為系列3，“值”和“分類（x）軸標志”的設置如圖-6所示：

圖-6

圖-6中“值”的具體設置方法是：點擊“值”右側的拾取折疊按鈕，在彈出的“源數據-數值”對話框，選取系列3數據所在的單元格B2：B57，再點擊“源數據-數值”對話框右側的拾取折疊按鈕，則折疊復位。分類（x）軸標志”選擇的范圍還是全程A2：A112。

在2010版中，類似的設置如圖-6-2010-1與圖-6-2010-2所示：

圖-6-2010-1

圖-6-2010-2

確定后，圖表如圖-7所示：

圖-7

為了說明三個系列前后遮擋掩蓋的效果，可以用三維面積圖來說明。

當只有系列1時的三維圖，如圖-8所示：

圖-8

當添加系列2后的三維圖，如圖-9所示：

圖-9

當添加系列3后的三維圖，如圖-10所示：

圖-10

將三個系列的三維圖的三維變換角度調為0，相當于從側面正視這個三維圖，三維的遮擋掩蓋效果就成了二維的分段切割效果，如圖-11所示：

圖-11

最后要說明的是，這種分段區間的曲線下面積圖的制作是對若干個互有重疊的區間實施不同的顏色填充，由于數據系列的前后順序遮擋掩蓋，而在視覺上形成是對首尾互相連接的三個區間填充內部圖案的感覺。因此在制作中如有覆蓋錯誤，很可能是數據系列的次序顛倒，可以在相關的數據系列次序調整的選項面板中進行調整。