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

下圖左側A2:D10單元格區域為數據源，C列和D列、第3行和第10行為輔助作圖區域。當B列"分數"數據發生變動時，C列和D列中的數據也隨之發生變化。

右側為根據數據源制作的動態圖表，系列"分數"用藍色柱子展示，系列"高于平均值"用灰色柱子展示，系列"平均值"用紅色虛線展示。從圖表中可以很方便地看出哪些人的分數高于平均值。

前期準備

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

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

操作步驟

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

2、在［插入圖表］對話框中，切換到［所有圖表］選項卡，單擊左側列表最下面的［組合］，在右側將系列"分數"的圖表類型設為"簇狀柱形圖"；將系列"平均值"的圖表類型設為"折線圖"（同時勾選"次坐標軸"選框）；將系列"高于平均值"的圖表類型設為"簇狀柱形圖"，最后單擊［確定］按鈕。

3、將系列"高于平均值"與系列"分數"的柱子重疊。

雙擊系列"高于平均值"，在工作表右側彈出［設置數據系列格式］對話框，在［系列選項］下將［系列重疊］設為"100％"，可使系列"高于平均值"柱子覆蓋在"分數"柱子之上。

4、統一主、次縱坐標軸的最大最小值。

雙擊主要縱坐標軸，在工作表右側彈出的［設置坐標軸格式］對話框中，在［坐標軸選項］下將［邊界］中的［最小值］設為"0"，［最大值］設為"100"。雙擊次要縱坐標軸，進行同樣的設置。

５、調整系列"平均值"的數據范圍。

• 選中圖表，在［設計］選項卡［數據］功能組中單擊［選擇數據］按鈕。

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

?

"動態突顯高于平均值的數據"為單元格所在工作表的名稱

• 返回［選擇數據源］對話框，單擊［水平（分類）軸標簽］下的［編輯］按鈕，彈出［軸標簽］對話框。在對話框中將［軸標簽區域］編輯框中默認的"=動態突顯高于平均值的數據!$A$4:$A$9"更改為"=動態突顯高于平均值的數據!$A$3:$A$10"，將A3和A10兩個單元格包含進去。最后，單擊［確定］按鈕。

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

６、對系列"平均值"折線進行設置，使其顯示為紅色虛線。

雙擊系列"平均值"，在工作表右側彈出［設置數據系列格式］對話框，單擊［填充與線條］圖標，將［線條］設為"實線"，［顏色］設為［標準紅色］，［短劃線類型］設為［短劃線］。

7、設置次要橫坐標軸。

• 鼠標單擊圖表，在圖表右側出現浮動按鈕，點擊最上面的"＋"按鈕，在彈出的［圖表元素］列表中勾選［坐標軸］選項，單擊［坐標軸］右側的三角形按鈕，在級聯菜單中勾選［次要橫坐標軸］。此時，在圖表標題下方出現了次要橫坐標軸。

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

?

?

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

8、在"平均值"紅色虛線右側添加數據標簽。

單擊選中系列"平均值"，再次單擊"平均值"虛線最右側的數據點將其單獨選中，為該數據點添加數據標簽，標簽位置設為"右"。此時，添加的數據標簽與次要縱坐標軸發生重疊，可單擊選中次要縱坐標軸，按delete鍵將次要縱坐標軸刪除。

9、最后，根據實際需要對圖表大小、位置、網格線、圖例等細節進行調整，最終效果如下圖。

動態效果測試

當數據源中的數據發生變動時，圖表中系列"平均值"虛線的位置會隨之發生變化。如當把"虛竹"的"分數"改為"100"時，圖表中"平均值"虛線的位置同時發生改變，上升到"85"的位置。