Excel繪制指定區(qū)間的正態(tài)分布曲線下面積圖

正態(tài)分布曲線下面積是很有實(shí)際應(yīng)用價(jià)值的。在工程能力指數(shù)的評(píng)估、產(chǎn)品質(zhì)量分析和教育評(píng)估分析方面都發(fā)揮了很大作用。

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

Mly網(wǎng)友來信問起我在《Excel：正態(tài)分布函數(shù)曲線下的面積及其應(yīng)用》中的圖-3. μ=60，σ=15，在（-∞，55）的正態(tài)分布曲線二維面積圖（藍(lán)斜紋）如下圖，是如何繪制的。

這篇《Excel：正態(tài)分布函數(shù)曲線下的面積及其應(yīng)用》主要是談正態(tài)分布及其曲線下面積的應(yīng)用，這張圖是運(yùn)用的一個(gè)例子，至于圖表如何作出的具體步驟，文中就未作介紹。過后，正好有一位網(wǎng)友MZY來信問起了有關(guān)定積分的指定區(qū)間的曲線下面積的作圖，我又寫了一篇《答MZY：Excel指定區(qū)間的曲線下面積》，該文只是就Y=eX的曲線談了分幾個(gè)區(qū)間的曲線下面積的作圖，因?yàn)樗悸肥且粯拥模臀磳?duì)分段的正態(tài)分布曲線作介紹。其實(shí)把這兩篇文章結(jié)合起來就可以解決類似于上圖的分區(qū)間的正態(tài)分布曲線下面積的繪制。

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

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

表-1

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

表-2

先作系列1：

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

選中單元格區(qū)間A2：B112，插入二維面積圖，這就繪出μ=60，σ=15，在（-∞，+∞）的正態(tài)分布曲線圖，如圖-1所示。可見該中間高兩頭低的鐘形圖形是以μ=60為對(duì)稱軸的軸對(duì)稱圖形，并以橫軸為漸近線：

圖-1

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

圖-2

在2010版中點(diǎn)擊“圖表工具”-“數(shù)據(jù)”-“選擇數(shù)據(jù)”，如圖-2-2010-1所示：

圖-2-2010-1

在“選擇數(shù)據(jù)源”對(duì)話框中，可以看到系列1已設(shè)置，如圖-2-2010-2所示：

圖-2-2010-2

在“編輯數(shù)據(jù)系列”對(duì)話框中可以看到“系列值”中的設(shè)置，如圖-2-2010-3所示：

圖-2-2010-3

而“軸標(biāo)簽”的設(shè)置，如圖-2-2010-4所示：

圖-2-2010-4

后續(xù)的數(shù)據(jù)系列必須在源數(shù)據(jù)中逐步添加。

添加系列2：

在“源數(shù)據(jù)”-“系列”-“系列”中添加系列2，將區(qū)間（-∞，65]作為系列2，“值”和“分類（x）軸標(biāo)志”的設(shè)置如圖-3所示：

圖-3

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

圖-4

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

圖-4-2010-1

圖-4-2010-2

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

圖-5

添加系列3：

在“源數(shù)據(jù)”-“系列”-“系列”中添加系列3，將區(qū)間（-∞，55]作為系列3，“值”和“分類（x）軸標(biāo)志”的設(shè)置如圖-6所示：

圖-6

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

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

圖-6-2010-1

圖-6-2010-2

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

圖-7

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

當(dāng)只有系列1時(shí)的三維圖，如圖-8所示：

圖-8

當(dāng)添加系列2后的三維圖，如圖-9所示：

圖-9

當(dāng)添加系列3后的三維圖，如圖-10所示：

圖-10

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

圖-11

最后要說明的是，這種分段區(qū)間的曲線下面積圖的制作是對(duì)若干個(gè)互有重疊的區(qū)間實(shí)施不同的顏色填充，由于數(shù)據(jù)系列的前后順序遮擋掩蓋，而在視覺上形成是對(duì)首尾互相連接的三個(gè)區(qū)間填充內(nèi)部圖案的感覺。因此在制作中如有覆蓋錯(cuò)誤，很可能是數(shù)據(jù)系列的次序顛倒，可以在相關(guān)的數(shù)據(jù)系列次序調(diào)整的選項(xiàng)面板中進(jìn)行調(diào)整。