Excel應(yīng)用大全 | 離散趨勢

hercules028 2022-02-23

展開全文

集中趨勢只是數(shù)據(jù)組特征的一方面，而另一方面是離散趨勢，也就是圍繞中心點（平均值）的變動幅度。例如，圖 7-24 所示的這些學(xué)生的體重都是 45kg，均值是 45kg，每個人的體重都沒有變動趨勢，直方圖就是一個窄長條，如圖 7-25 所示。

圖7-24體重相同的學(xué)生

圖7-25體重相同的直方圖

如果這些學(xué)生的體重是圖 7-26 所示的情況，直方圖就會變成圖 7-27 所示的形態(tài)。

圖7-26體重差異大的學(xué)生

圖7-27體重差異大的直方圖

量化描述數(shù)據(jù)這種分散狀態(tài)的是方差和標(biāo)準(zhǔn)差。

假設(shè)有如圖 7-28 所示的 3 組數(shù)據(jù)，這 3 組數(shù)據(jù)的均值雖然都是 50，但是 B 組的變動幅度最大，A 組較小，C 組完全沒有變動幅度。

量化描述這種變動幅度的是極差。

圖7-28 3 組數(shù)據(jù)

本節(jié)重點討論的是離散趨勢的 3 個描述方法，分別是極差、方差和標(biāo)準(zhǔn)差。另外，四分位差也是描述數(shù)據(jù)離散趨勢的常用方法。

極差

極差是 3 個概念中最簡單、最籠統(tǒng)的指標(biāo)，即數(shù)據(jù)組中最大值和最小值之差，在圖 7-28中，A 組的極差是 4，B 組的極差是 40，C 組的極差是 0，計算方法如圖 7-29 所示。

圖7-29極差計算方法

極差可以直觀看到數(shù)據(jù)組的范圍，但是不夠精確。

方差和標(biāo)準(zhǔn)差

ENJOY THE SUMMER

1. 方差和標(biāo)準(zhǔn)差的實際意義

方差和標(biāo)準(zhǔn)差如何來度量分散程度呢，現(xiàn)有一組人群的體重數(shù)據(jù)如圖 7-30 所示。

圖7-30體重數(shù)據(jù)

這組體重數(shù)據(jù)的均值是 38.7，每一個數(shù)據(jù)相對平均值都有一定的變動幅度。

如圖 7-31 所示，圖中每個點代表一個人的體重，右邊的垂線是平均值所在的平均線，左邊的垂線是其中一個數(shù)據(jù)點所在直線，兩條垂線間的距離是數(shù)據(jù)點到平均線的距離，方差是所有數(shù)據(jù)點到平均線距離的平方的均值。

圖7-31體重分布圖

圖 7-32 展示了方差的計算過程：

圖 7-32　距離平方的計算步驟

借助這個表中得到的總和，可以求得距離的均值：1911/21=91，這個結(jié)果就是方差，將 91 開方后得到 9.5 就是標(biāo)準(zhǔn)差。

注意：由于統(tǒng)計總體和樣本的差異，計算均值時用的數(shù)值個數(shù)不是 22 個，而是22-1，即 n-1 個（請參閱 10.1.1 節(jié)內(nèi)容）

方差已經(jīng)足夠描述數(shù)值與均值的偏離程度，為什么又要計算標(biāo)準(zhǔn)差呢？雖然方差可以很好地描述數(shù)據(jù)與均值的變動幅度，但是方差與要描述的數(shù)據(jù)的單位是不一致的，這樣的處理結(jié)果并不符合人們的直觀思維習(xí)慣，而標(biāo)準(zhǔn)差與真實數(shù)據(jù)的單位是相同的。因此很多場合大家更傾向于使用標(biāo)準(zhǔn)差。

例如一個班級有 50 名學(xué)生，經(jīng)過統(tǒng)計數(shù)學(xué)平均分是 80 分，標(biāo)準(zhǔn)差是 10，其實際意義是每個學(xué)生的分?jǐn)?shù)與平均分的平均差距是 10 分；方差是 100，是標(biāo)準(zhǔn)差的平方，沒有單位，即每個學(xué)生的分?jǐn)?shù)與平均分的平均差距的平方是 100，這個解釋沒有任何實際意義，通常使用標(biāo)準(zhǔn)差進行描述更具有實際意義也更容易理解。從這一點來說，方差只是一個中間的計算過程，它的目的是平方運算后去掉負號，再開方得到原單位偏差值。當(dāng)然，方差的作用絕不僅限于此，在數(shù)據(jù)分析中它有很多重要的用途。

標(biāo)準(zhǔn)差的性質(zhì)：

（1）標(biāo)準(zhǔn)差是以均值為中心的變動幅度測量，如果是以中位數(shù)為中心，標(biāo)準(zhǔn)差是無效的。

（2）如果數(shù)據(jù)組的所有數(shù)據(jù)都相等，標(biāo)準(zhǔn)差為 0，否則必然是大于 0 的數(shù)值，而且數(shù)據(jù)越分散標(biāo)準(zhǔn)差越大，越集中標(biāo)準(zhǔn)差越小。

標(biāo)準(zhǔn)差在描述數(shù)據(jù)中有極其重要的作用，希望每一位讀者能深入體會其實際意義。

2. 在 Excel 中計算數(shù)據(jù)組的方差和標(biāo)準(zhǔn)差

某公司的銷售部門在月底統(tǒng)計每個銷售人員的業(yè)績，如圖 7-33 所示。

圖7-33某公司銷售人員業(yè)績

計算標(biāo)準(zhǔn)差和方差如圖 7-34 所示。

圖7-34計算銷售人員業(yè)績的標(biāo)準(zhǔn)差與方差

在 D2 單元格輸入以下公式，得到標(biāo)準(zhǔn)差為 10 185。

=STDEV.P(B2:B12)

在 E2 單元格輸入以下公式，得到方差為 103 740 510。

=POWER(D2,2)

或

=D2^2

注意，Excel 中的標(biāo)準(zhǔn)差函數(shù)有兩個，分別是 STDEV.P 和 STDEV.S。STDEV.P 計算的是總體標(biāo)準(zhǔn)差，STDEV.S 計算的是樣本標(biāo)準(zhǔn)差（請參閱 8.1.1 節(jié)）。計算方法的差別在于，總體標(biāo)準(zhǔn)差用的數(shù)值個數(shù)是 n，樣本標(biāo)準(zhǔn)差用的數(shù)值個數(shù)是 n-1。

本例中，全部銷售人員是一個總體，所以用STDEV.P計算標(biāo)準(zhǔn)差。從計算結(jié)果可以知道，每個銷售人員的業(yè)績與全體員工業(yè)績均值的平均差距是 10 185 元，均值是 21 692 元，標(biāo)準(zhǔn)差大約是均值的一半，說明整個銷售部門的員工銷售能力差距較大。

四分位間距

1. 四分位數(shù)

標(biāo)準(zhǔn)差是以均值為中心的變動幅度測量，如果以中位數(shù)的變動幅度測量需要借助四分位間距。

四分位間距也稱為四分位差，基本原理是把數(shù)據(jù)組內(nèi)的數(shù)值從小到大排序，按照數(shù)值個數(shù)等分成 4 組，然后再繼續(xù)觀察變動幅度，具體等分方法如下。

（1）圖 7-35 中是 23 名學(xué)生的語文成績，按從小到大排列。首先在中位數(shù)處將數(shù)據(jù)組分成高低分兩組，由于中位數(shù)恰好是其中的一個數(shù)值 80，而不是某個中間位置，目的是將整個數(shù)據(jù)組等分，無論將中位數(shù)劃分到哪個組都不再相等，所以劃分原則是將中位數(shù)既分配給高分組也分配給低分組，這樣所有學(xué)生的成績就分成了兩組，每組數(shù)值個數(shù)為 12。

（2）低分組的中位數(shù)是 72，稱作第一四分位數(shù)，也稱為下四分位數(shù)，通常記作 Q1。高分組的中位數(shù)是 83，稱作第三四分位數(shù)，也稱為上四分位數(shù)，通常記作 Q3。原數(shù)據(jù)組的中位數(shù) 80 也稱為第二四分位數(shù)，記作 Q2。

（3）Q3 與 Q1 距離稱為四分位間距，記作 IQR。在本例中，IQR= Q3-Q1=83-72=11。

圖7-35確定四分位數(shù)

IQR 展示的是中間一半數(shù)據(jù)的離散程度，數(shù)值越大越分散，反之?dāng)?shù)值越小越集中。同時IQR 處在數(shù)據(jù)中段且不受極端值的影響，能在一定程度上表現(xiàn)整體數(shù)據(jù)的離散程度。

2. 箱形圖

箱形圖是由著名統(tǒng)計學(xué)家 John W. Tukey 發(fā)明的，借助箱形圖可以更直觀地觀察四分位數(shù)。以圖 7-35 中的數(shù)據(jù)為例來說明。

找出 Q1、Q3 和中位數(shù)的位置，以 Q1 和 Q3 為兩邊畫一個矩形，以中位數(shù)的位置在矩形中畫一條直線，如圖 7-36 所示。

圖7-36箱形圖的主體

從矩形兩側(cè)分別延長出兩條線，延長線的長度為 1.5 倍的 IQR，如圖 7-37 所示。左端點和右端點分別稱作下限和上限，需要注意的是，此處上限和下限并不是數(shù)據(jù)組的最大值和最小值，而是人為規(guī)定的一個界限，在界限以內(nèi)的值是正常值，超過界限以外的是異常值。圖 7-37 中最左側(cè)的點是該組數(shù)據(jù)中唯一沒有落在上下限以內(nèi)的點，是異常值。

圖7-37箱形圖

箱形圖不受異常值影響，能夠準(zhǔn)確描述數(shù)據(jù)的離散程度，非常適合數(shù)據(jù)組之間的對比。而且箱形圖可以橫著畫，也可以豎著畫，只要保證對比的數(shù)據(jù)組用統(tǒng)一的刻度即可，如圖 7-38 所示。

圖7-38多組箱形圖的對比

3. 用 Excel 計算四分位數(shù)和制作箱形圖

示例 7-2 計算四分位數(shù)和制作箱形圖來檢驗超市 SKU 調(diào)整效果

有一家新開業(yè)的超市，由于老板經(jīng)驗不足，前3 個月對商品數(shù)量一直在做調(diào)整。現(xiàn)在老板希望對這一季度的商品數(shù)量進行分析，如圖 7-39 所示。

圖7-39某超市的商品數(shù)量

需要給每一個月數(shù)據(jù)計算四分位數(shù)并創(chuàng)建箱形圖，并且要求在同一個圖表中展示，以方便對比，步驟如下。

計算四分位數(shù)的方法如圖 7-40 所示。具體方法如下。

在 G2 和 G4 單元格分別輸入以下公式，并分別向右復(fù)制填充到 G2:I2 單元格區(qū)域和 G4:I4 單元格區(qū)域。

=QUARTILE.EXC(B3:B32,1)

=QUARTILE.EXC(B3:B32,3)