Excel-箱線圖（數(shù)據(jù)分布）分析

箱線圖（Boxplot）也稱(chēng)箱須圖（Box-whisker Plot），它是用一組數(shù)據(jù)中的最小值、第一四分位數(shù)、中位數(shù)、第三四分位數(shù)和最大值來(lái)反映數(shù)據(jù)分布的中心位置和散布范圍，可以粗略地看出數(shù)據(jù)是否具有對(duì)稱(chēng)性。通過(guò)將多組數(shù)據(jù)的箱線圖畫(huà)在同一坐標(biāo)上，則可以清晰地顯示各組數(shù)據(jù)的分布差異，為發(fā)現(xiàn)問(wèn)題、改進(jìn)流程提供線索。

1．什么是四分位數(shù)

箱線圖需要用到統(tǒng)計(jì)學(xué)的四分位數(shù)（Quartile）的概念，所謂四分位數(shù)，就是把組中所有數(shù)據(jù)由小到大排列并分成四等份，處于三個(gè)分割點(diǎn)位置的數(shù)字就是四分位數(shù)。

第一四分位數(shù)（Q1），又稱(chēng)“較小四分位數(shù)”或“下四分位數(shù)”，等于該樣本中所有數(shù)值由小到大排列后第25%的數(shù)字。

第二四分位數(shù)（Q2），又稱(chēng)“中位數(shù)”，等于該樣本中所有數(shù)值由小到大排列后第50%的數(shù)字。

第三四分位數(shù)（Q3），又稱(chēng)“較大四分位數(shù)”或“上四分位數(shù)”，等于該樣本中所有數(shù)值由小到大排列后第75%的數(shù)字。

第三四分位數(shù)與第一四分位數(shù)的差距又稱(chēng)四分位間距（InterQuartile Range，IQR）。

計(jì)算四分位數(shù)首先要確定Q1、Q2、Q3的位置（n表示數(shù)字的總個(gè)數(shù)）：

Q1的位置=（n+1）/4

Q2的位置=（n+1）/2

Q3的位置=3（n+1）/4

對(duì)于數(shù)字個(gè)數(shù)為奇數(shù)的，其四分位數(shù)比較容易確定。例如，數(shù)字“5、47、48、15、42、41、7、39、45、40、35”共有11項(xiàng)，由小到大排列的結(jié)果為“5、7、15、35、39、40、41、42、45、47、48”，計(jì)算結(jié)果如下：

Q1的位置=（11+1）/4=3，該位置的數(shù)字是15。

Q2的位置=（11+1）/2=6，該位置的數(shù)字是40。

Q3的位置=3（11+1）/4=9，該位置的數(shù)字是45。

而對(duì)于數(shù)字個(gè)數(shù)為偶數(shù)的，其四分位數(shù)確定起來(lái)稍微繁瑣一點(diǎn)。例如，數(shù)字“8、17、38、39、42、44”共有6項(xiàng)，位置計(jì)算結(jié)果如下：

Q1的位置=（6+1）/4=1.75

Q2的位置=（6+1）/2=3.5

Q3的位置=3（6+1）/4=5.25

這時(shí)的數(shù)字以數(shù)據(jù)連續(xù)為前提，由所確定位置的前后兩個(gè)數(shù)字共同確定。例如，Q2的位置為3.5，則由第3個(gè)數(shù)字38和第4個(gè)數(shù)字39共同確定，計(jì)算方法是：38+（39-38）×3.5的小數(shù)部分，即38+1×0.5=38.5。該結(jié)果實(shí)際上是38和39的平均數(shù)。

同理，Q1、Q3的計(jì)算結(jié)果如下：

Q1 = 8+（17-8）×0.75=14.75

Q3 = 42+（44-42）×0.25=42.5

Excel為計(jì)算四分位數(shù)提供了QUARTILE(array,quart)函數(shù)，其中array參數(shù)用于指定要計(jì)算四分位數(shù)值的數(shù)組或數(shù)值型單元格區(qū)域，quart指定返回哪一個(gè)四分位值，可用值如下：

0，返回最小值；

1，返回第一個(gè)四分位數(shù)；

2，返回第二個(gè)四分位數(shù)，即中位數(shù)；

3，返回第三個(gè)四分位數(shù)；

4，返回最大值。

2．箱線圖的結(jié)構(gòu)

箱線圖包括一個(gè)矩形箱體和上下兩條豎線，箱體表示數(shù)據(jù)的集中范圍，上下兩條豎線分別表示數(shù)據(jù)向上和向下的延伸范圍，結(jié)構(gòu)如圖9-51所示。

圖9-51箱線圖的結(jié)構(gòu)

四分位間距框的頂部線條是第三四分位數(shù)的位置，即Q3，表示有75%的數(shù)據(jù)小于等于此值。底部線條是第一四分位數(shù)的位置，即Q1，表示有25%的數(shù)據(jù)小于此值。則整個(gè)四分位間距框所代表的是數(shù)據(jù)集中50%（即75%-25%）的數(shù)據(jù)，四分位間距框的高度就是這些數(shù)據(jù)涉及的范圍，能夠表現(xiàn)出數(shù)據(jù)的集中程度。Q2是數(shù)據(jù)中位數(shù)的位置。

Whisker上限是延伸至距框頂部1.5倍框高范圍內(nèi)的最大數(shù)據(jù)點(diǎn)，Whisker下限是延伸至距框底部1.5倍框高范圍內(nèi)的最小數(shù)據(jù)點(diǎn)，超出Whisker上限或下限的數(shù)值將使用星號(hào)“*”表示。但是，在Excel中繪制箱線圖需要借助股價(jià)圖來(lái)實(shí)現(xiàn)，因此無(wú)法展現(xiàn)異常值，Whisker上限將延伸至數(shù)據(jù)最大值的位置，Whisker下限將延伸至數(shù)據(jù)最小值的位置。

3．繪制箱線圖

圖9-52中的A2:F8區(qū)域和H2:M8區(qū)域分別是華北和華南是某段時(shí)間客戶訂單收貨天數(shù)的統(tǒng)計(jì)結(jié)果，C11:C15和J11:J15是利用QUARTILE函數(shù)計(jì)算的華北、華南收貨天數(shù)的四分位數(shù)結(jié)果。

圖9-52收貨天數(shù)的四分位數(shù)計(jì)算結(jié)果

在Excel中繪制箱線圖需要借助股價(jià)圖的“開(kāi)盤(pán)-盤(pán)高-盤(pán)底-收盤(pán)”圖來(lái)實(shí)現(xiàn)。根據(jù)Excel繪圖時(shí)放置數(shù)據(jù)系列的位置，開(kāi)盤(pán)、盤(pán)高、盤(pán)底、收盤(pán)應(yīng)分別對(duì)應(yīng)Q1、Q0、Q2、Q4。下面是繪圖步驟：

準(zhǔn)備圖表數(shù)據(jù)。根據(jù)對(duì)應(yīng)關(guān)系，在表格的B18:E18區(qū)域分別輸入華北客戶的Q1、Q0、Q2、Q4統(tǒng)計(jì)數(shù)字，將Q3輸入到最后的F18單元格中，在A18中輸入一個(gè)日期型數(shù)據(jù)（注意，必須為日期型），如“2013/1/1”。然后在第19行中輸入華南客戶的數(shù)據(jù)，A19中的日期遞增1天，最終結(jié)果如圖9-53所示。

圖9-53準(zhǔn)備圖表數(shù)據(jù)

插入圖表。選定A18:E19區(qū)域，在“插入”功能區(qū)的“圖表”模塊中單擊“其他圖表”，選擇股價(jià)圖部分的“開(kāi)盤(pán)-盤(pán)高-盤(pán)底-收盤(pán)圖”按鈕，即可看到繪制的股價(jià)圖，如圖9-54所示。

圖9-54插入股價(jià)圖

添加Q3數(shù)據(jù)系列。由圖9-54可以看出，四分位間距框的頂部線條使用的是Q4（最大值）位置，而是不是箱線圖要求的Q3位置。右擊繪圖區(qū)，在彈出的快捷菜單中選擇“選擇數(shù)據(jù)”命令，打開(kāi)“選擇數(shù)據(jù)源”對(duì)話框。單擊“添加”按鈕打開(kāi)“編輯數(shù)據(jù)系列”對(duì)話框，在“系列名稱(chēng)”折疊框中輸入“Q3”，在系列值折疊框中選擇F18:F19區(qū)域，單擊“確定”按鈕即可看到股價(jià)圖變成了箱線圖，如圖9-55所示。四分位間距框的高度小了很多，單擊頂部線條與Whisker上限交匯處，可以看到使用的是Q3數(shù)據(jù)。

圖9-55添加Q3數(shù)據(jù)系列

顯示中位數(shù)線。至此，四分位間距框雖然已經(jīng)繪制正確了，但是還缺少中位數(shù)線，即Q2。選擇圖例中的“系列3”標(biāo)簽，然后單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置數(shù)據(jù)系列格式”命令，打開(kāi)“設(shè)置數(shù)據(jù)系列格式”對(duì)話框。在“數(shù)據(jù)標(biāo)記選項(xiàng)”中將標(biāo)記類(lèi)型設(shè)置為內(nèi)置的“-”形狀，單擊“關(guān)閉”按鈕即可看到中位線顯示了出來(lái)，如圖9-56所示。

圖9-56顯示中位數(shù)線

美化圖表。首先要修改分類(lèi)軸（橫軸）標(biāo)簽，由于插入股價(jià)圖時(shí)的限制在A18和A19單元格中輸入了日期型數(shù)據(jù)，但是在圖表插入后，可以將其修改為其他數(shù)據(jù)類(lèi)型的值，因此在A18和A19單元格分別輸入“華北”、“華南”。其次是刪除圖例欄，對(duì)于箱線圖而言這并不需要。最后，可以為圖表添加一個(gè)標(biāo)題。最終美化后結(jié)果如圖9-57所示。

圖9-57美化后的圖表

由圖9-57可以看出，華北和華南客戶的中位數(shù)位置、四分位間距框的位置與高度基本相同，說(shuō)明兩區(qū)域的客戶收貨天數(shù)基本相同。但是，從Whisker上限和Whisker下限看，華南客戶的收貨天數(shù)范圍小于華北客戶，說(shuō)明流程更加穩(wěn)定。