|
excelperfect 引言：Excel提供了幾個(gè)工作表函數(shù)來處理正態(tài)分布或“鐘形曲線”，這里介紹Excel的正態(tài)分布函數(shù)為統(tǒng)計(jì)上的挑戰(zhàn)所提供的幫助。本文學(xué)習(xí)整理自exceluser.com，供有興趣的朋友參考。 關(guān)于正態(tài)曲線的一件有趣的事情是它經(jīng)常出現(xiàn)在許多不同的環(huán)境中：
等等。 最后一個(gè)例子，這里有一個(gè)令人驚訝的正態(tài)曲線：取任何人口，無論它是否呈正態(tài)分布，從該群體中隨機(jī)選擇至少30名成員，測(cè)量他們的某些特征，然后找到這些測(cè)量值的平均值，該平均值是一個(gè)數(shù)據(jù)點(diǎn)?，F(xiàn)在選擇相同數(shù)量的另一個(gè)隨機(jī)樣本，并找到它們的測(cè)量值的平均值。一次又一次地做同樣的事情，中心極限定理說這些平均值往往服從正態(tài)分布。 正態(tài)分布無處不在，讓我們盡可能輕松地使用Excel仔細(xì)看看如何使用它們。 簡(jiǎn)單的定義 我們需要了解一些簡(jiǎn)單的概念，以便可以開始使用Excel函數(shù)來描述數(shù)據(jù)。 從膽固醇到斑馬條紋，正態(tài)概率分布描述了具有特定屬性值范圍的總體比例。大多數(shù)成員的指標(biāo)接近平均水平；有些與平均值相差較遠(yuǎn)；有些與平均值相差更遠(yuǎn)。 例如，總體可能是世界上所有斑馬的所有條紋，正態(tài)曲線將顯示具有不同寬度的條紋的比例。 樣本的標(biāo)準(zhǔn)偏差是樣本與其平均值的分布的度量。（當(dāng)然，我們?cè)谝粋€(gè)“樣本”中取了很多項(xiàng)目，而不僅僅是一個(gè)項(xiàng)目。）在正態(tài)分布中，大約68%的樣本在均值的一個(gè)標(biāo)準(zhǔn)偏差內(nèi)，大約95%在兩個(gè)標(biāo)準(zhǔn)偏差內(nèi)，大約99.7%在三個(gè)標(biāo)準(zhǔn)偏差內(nèi)。圖1中的數(shù)字表示與平均值的標(biāo)準(zhǔn)偏差。 圖1 z值是一個(gè)值與以標(biāo)準(zhǔn)差表示的平均值之間的距離。在圖2中，每個(gè)數(shù)字都是一個(gè)z值。 圖2 計(jì)算或估計(jì)標(biāo)準(zhǔn)偏差 以下幾個(gè)函數(shù)需要標(biāo)準(zhǔn)偏差值，至少有兩種方法可以找到該值。 首先，如果有數(shù)據(jù)樣本，Excel原先提供有STDEV函數(shù)，但在Excel 2010中，被STDEV.S函數(shù)取代： =STDEV.S(range_of_values) 另一方面，如果使用的是整個(gè)總體，則可以使用STDEV.P函數(shù)計(jì)算標(biāo)準(zhǔn)偏差： =STDEV.P(range_of_values) 然而，如果要進(jìn)行粗略估計(jì)，則必須采用不同的方法，因?yàn)闆]有實(shí)際數(shù)據(jù)來支持你的估計(jì)。 在這種情況下，首先計(jì)算范圍，這是從最大可能值中減去最小可能值。很可能，讓我們假設(shè)所有可能的值在大約95%都在該范圍內(nèi)。 記住，大約95%的樣本在均值兩側(cè)的兩個(gè)標(biāo)準(zhǔn)偏差內(nèi)。（當(dāng)然，這是總共四個(gè)標(biāo)準(zhǔn)差。）因此，如果我們將范圍除以四，應(yīng)該得到近似的標(biāo)準(zhǔn)差。 僅僅將范圍除以四似乎是一種草率的方法，但要考慮這種計(jì)算經(jīng)常使用的方式。 假設(shè)要預(yù)測(cè)下一年的銷售額，你認(rèn)為銷售額將約為1000，但該數(shù)字可能高達(dá)1200，也可能低至800。有了這些信息，你可以在估計(jì)的銷售額周圍繪制一條正態(tài)曲線，并開始生成各種利潤和現(xiàn)金流預(yù)測(cè). 需要強(qiáng)調(diào)的是，這些數(shù)字只是你的最佳估計(jì)。因此，使用估計(jì)的標(biāo)準(zhǔn)偏差似乎并不像其他方式那樣草率。 根據(jù)這些估計(jì)，平均銷售額將約為1000，而標(biāo)準(zhǔn)差約為(1200 –800) / 4 = 100。有了這些信息，你可以使用以下函數(shù)來執(zhí)行需要的許多計(jì)算分析。 NORM.DIST(x, mean, standard_dev,cumulative) NORM.DIST函數(shù)給出一個(gè)數(shù)字落在或低于正態(tài)分布的給定值的概率，其中：
示例：美國18至24歲女性的身高分布近似正態(tài)分布，平均值為65.5英寸（166.37厘米），標(biāo)準(zhǔn)差為2.5 英寸（6.35 厘米），這些女性中有多少比例高于5英尺8英寸，即68英寸（172.72厘米）？ 身高小于或等于68英寸的女性百分比是： =NORM.DIST(68,65.5,2.5,TRUE)=84.13% 因此，身高超過68英寸的女性比例為1 –84.13%，即大約15.87%，該值由下圖3中的陰影區(qū)域表示。 圖3 NORM.S.DIST(z, cumulative) NORM.S.DIST函數(shù)將標(biāo)準(zhǔn)差(z)的數(shù)量轉(zhuǎn)換為累積概率，其中：
（概率質(zhì)量函數(shù)PMF提供離散（即非連續(xù)）隨機(jī)變量恰好等于某個(gè)值的概率。） 示例： =NORM.S.DIST(1,TRUE)=84.13% =NORM.S.DIST(-1,TRUE)=15.87% 因此，某個(gè)值在平均值的一個(gè)標(biāo)準(zhǔn)偏差內(nèi)的概率是這些值之間的差值，即68.27%，此范圍由如下圖4所示圖表的陰影區(qū)域表示。 圖4 NORM.INV(probability, mean, standard_dev) NORM.INV函數(shù)是NORM.DIST函數(shù)的反函數(shù)，它計(jì)算給定概率的x變量。 為了說明這一點(diǎn)，考慮上文中NORM.DIST函數(shù)的說明中使用的美國女性的身高，如果一個(gè)女人想成為75%最高的美國女性之一，她需要有多高？ 使用NORM.INV，她會(huì)知道她的身高至少需要63.81英寸，如以下公式所示： =NORM.INV(0.25,65.5,2.5)=63.81英寸 下圖5顯示了25%的美國女性比這個(gè)身高更矮所代表的區(qū)域。 圖5 NORM.S.INV(probability) NORM.S.INV函數(shù)是NORM.S.DIST函數(shù)的反函數(shù)，給定變量在均值一定距離內(nèi)的概率，它會(huì)找到z值。 為了說明這一點(diǎn)，假設(shè)你關(guān)心最接近均值的一半樣本。以下兩個(gè)公式提供了-.674和+.674的邊界， =NORM.S.INV(0.25) =NORM.S.INV(0.75) 如下圖6所示。 圖6 STANDARDIZE(x, mean, standard_dev) STANDARDIZE函數(shù)返回指定值、均值和標(biāo)準(zhǔn)差的z值。 為了說明這一點(diǎn)，在上文的NORM.INV示例中，我們發(fā)現(xiàn)女性至少需要63.81英寸高才能避開人口中最矮25%的身高（按身高計(jì)算）。STANDARDIZE函數(shù)告訴我們 63.81 英寸的z值是： =STANDARDIZE(63.81,65.5,2.5)=-0.676 可以使用NORM.S.DIST函數(shù)檢查這個(gè)數(shù)字： =NORM.S.DIST(-0.676,TRUE)=25% 也就是說，z值為-.6745的概率為25%。 如何從正態(tài)分布計(jì)算隨機(jī)數(shù) 記住，NORM.INV函數(shù)返回給定概率的值。在Excel2007及更高版本中，其語法是： NORM.INV(probability,mean,standard_dev) 此外，RAND函數(shù)返回一個(gè)介于0和1之間的隨機(jī)數(shù)。也就是說，RAND生成隨機(jī)概率。因此，可以使用NORM.INV函數(shù)從正態(tài)分布計(jì)算隨機(jī)數(shù)，在Excel2007及更高版本中使用此公式： =NORM.INV(RAND(),mean,standard_dev) |
|
|
