excel 制作平均值控制線圖案例解析

下圖左側(cè)A2:D10單元格區(qū)域?yàn)閿?shù)據(jù)源，C列和D列、第3行和第10行為輔助作圖區(qū)域。當(dāng)B列"分?jǐn)?shù)"數(shù)據(jù)發(fā)生變動(dòng)時(shí)，C列和D列中的數(shù)據(jù)也隨之發(fā)生變化。

右側(cè)為根據(jù)數(shù)據(jù)源制作的動(dòng)態(tài)圖表，系列"分?jǐn)?shù)"用藍(lán)色柱子展示，系列"高于平均值"用灰色柱子展示，系列"平均值"用紅色虛線展示。從圖表中可以很方便地看出哪些人的分?jǐn)?shù)高于平均值。

前期準(zhǔn)備

要讓C列"平均值"、D列"高于平均值"數(shù)據(jù)隨B列"分?jǐn)?shù)"中的數(shù)據(jù)同步變化，需要先在C列和D列設(shè)置函數(shù)公式。選中C3單元格，輸入公式：=AVERAGE($B$4:$B$9)，可返回$B$4:$B$9單元格區(qū)域所有數(shù)值的平均值84，將公式向下填充至C10單元格。（C3、C10單元格中的輔助數(shù)據(jù)是為了使圖表中系列"平均值"虛線左右兩端超出系列"分?jǐn)?shù)"和"高于平均值"柱子，起到美化圖表的作用。）

選中D4單元格，輸入公式：=IF(B4>C4,B4,0)，公式的含義是：如果B4單元格中的數(shù)值大于C4，則在D4單元格返回B4的數(shù)值，否則在D4單元格顯示"0"值。最后，將公式向下填充至D9單元格。

操作步驟

１、鼠標(biāo)選中A2:D2單元格區(qū)域，按住ctrl鍵加選A4:D9單元格區(qū)域，單擊［插入］選項(xiàng)卡［圖表］功能組右下角的對話框啟動(dòng)器按鈕，彈出［插入圖表］對話框。

2、在［插入圖表］對話框中，切換到［所有圖表］選項(xiàng)卡，單擊左側(cè)列表最下面的［組合］，在右側(cè)將系列"分?jǐn)?shù)"的圖表類型設(shè)為"簇狀柱形圖"；將系列"平均值"的圖表類型設(shè)為"折線圖"（同時(shí)勾選"次坐標(biāo)軸"選框）；將系列"高于平均值"的圖表類型設(shè)為"簇狀柱形圖"，最后單擊［確定］按鈕。

3、將系列"高于平均值"與系列"分?jǐn)?shù)"的柱子重疊。

雙擊系列"高于平均值"，在工作表右側(cè)彈出［設(shè)置數(shù)據(jù)系列格式］對話框，在［系列選項(xiàng)］下將［系列重疊］設(shè)為"100％"，可使系列"高于平均值"柱子覆蓋在"分?jǐn)?shù)"柱子之上。

4、統(tǒng)一主、次縱坐標(biāo)軸的最大最小值。

雙擊主要縱坐標(biāo)軸，在工作表右側(cè)彈出的［設(shè)置坐標(biāo)軸格式］對話框中，在［坐標(biāo)軸選項(xiàng)］下將［邊界］中的［最小值］設(shè)為"0"，［最大值］設(shè)為"100"。雙擊次要縱坐標(biāo)軸，進(jìn)行同樣的設(shè)置。

５、調(diào)整系列"平均值"的數(shù)據(jù)范圍。

• 選中圖表，在［設(shè)計(jì)］選項(xiàng)卡［數(shù)據(jù)］功能組中單擊［選擇數(shù)據(jù)］按鈕。

• 在彈出的［選擇數(shù)據(jù)源］對話框中，選中［圖例項(xiàng)（系列）］下的［平均值］，單擊［編輯］按鈕。在彈出的［編輯數(shù)據(jù)系列］對話框中，將［系列值］編輯框中默認(rèn)的"=動(dòng)態(tài)突顯高于平均值的數(shù)據(jù)!$C$4:$C$9"更改為"=動(dòng)態(tài)突顯高于平均值的數(shù)據(jù)!$C$3:$C$10"，將C3和C10兩個(gè)單元格內(nèi)容包含進(jìn)去，單擊［確定］按鈕。

?

"動(dòng)態(tài)突顯高于平均值的數(shù)據(jù)"為單元格所在工作表的名稱

• 返回［選擇數(shù)據(jù)源］對話框，單擊［水平（分類）軸標(biāo)簽］下的［編輯］按鈕，彈出［軸標(biāo)簽］對話框。在對話框中將［軸標(biāo)簽區(qū)域］編輯框中默認(rèn)的"=動(dòng)態(tài)突顯高于平均值的數(shù)據(jù)!$A$4:$A$9"更改為"=動(dòng)態(tài)突顯高于平均值的數(shù)據(jù)!$A$3:$A$10"，將A3和A10兩個(gè)單元格包含進(jìn)去。最后，單擊［確定］按鈕。

• 返回［選擇數(shù)據(jù)源］對話框，直接單擊［確定］按鈕。

６、對系列"平均值"折線進(jìn)行設(shè)置，使其顯示為紅色虛線。

雙擊系列"平均值"，在工作表右側(cè)彈出［設(shè)置數(shù)據(jù)系列格式］對話框，單擊［填充與線條］圖標(biāo)，將［線條］設(shè)為"實(shí)線"，［顏色］設(shè)為［標(biāo)準(zhǔn)紅色］，［短劃線類型］設(shè)為［短劃線］。

7、設(shè)置次要橫坐標(biāo)軸。

• 鼠標(biāo)單擊圖表，在圖表右側(cè)出現(xiàn)浮動(dòng)按鈕，點(diǎn)擊最上面的"＋"按鈕，在彈出的［圖表元素］列表中勾選［坐標(biāo)軸］選項(xiàng)，單擊［坐標(biāo)軸］右側(cè)的三角形按鈕，在級聯(lián)菜單中勾選［次要橫坐標(biāo)軸］。此時(shí)，在圖表標(biāo)題下方出現(xiàn)了次要橫坐標(biāo)軸。

• 鼠標(biāo)雙擊次要橫坐標(biāo)軸，在工作表右側(cè)彈出的［設(shè)置坐標(biāo)軸格式］對話框中，將［坐標(biāo)軸選項(xiàng)］下的［坐標(biāo)軸位置］設(shè)為"在刻度線上"。此時(shí)，系列"平均值"折線緊貼縱坐標(biāo)軸顯示。

?

?

• 選中次要橫坐標(biāo)軸，在［開始］選項(xiàng)卡［字體］功能組中將字體顏色設(shè)為背景色，可將次要橫坐標(biāo)軸進(jìn)行隱藏，使圖表更加美觀。（次要橫坐標(biāo)軸不能直接刪除，否則折線圖又會(huì)回到原來的位置。）

8、在"平均值"紅色虛線右側(cè)添加數(shù)據(jù)標(biāo)簽。

單擊選中系列"平均值"，再次單擊"平均值"虛線最右側(cè)的數(shù)據(jù)點(diǎn)將其單獨(dú)選中，為該數(shù)據(jù)點(diǎn)添加數(shù)據(jù)標(biāo)簽，標(biāo)簽位置設(shè)為"右"。此時(shí)，添加的數(shù)據(jù)標(biāo)簽與次要縱坐標(biāo)軸發(fā)生重疊，可單擊選中次要縱坐標(biāo)軸，按delete鍵將次要縱坐標(biāo)軸刪除。

9、最后，根據(jù)實(shí)際需要對圖表大小、位置、網(wǎng)格線、圖例等細(xì)節(jié)進(jìn)行調(diào)整，最終效果如下圖。

動(dòng)態(tài)效果測試

當(dāng)數(shù)據(jù)源中的數(shù)據(jù)發(fā)生變動(dòng)時(shí)，圖表中系列"平均值"虛線的位置會(huì)隨之發(fā)生變化。如當(dāng)把"虛竹"的"分?jǐn)?shù)"改為"100"時(shí)，圖表中"平均值"虛線的位置同時(shí)發(fā)生改變，上升到"85"的位置。