Skip to main content

在 Excel 中創建帶有平均值/目標線的圖表

Author: Xiaoyang Last Modified: 2025-05-12

在 Excel 中,向圖表插入水平或垂直線可能是一項常見任務,這些水平或垂直線可能代表某些目標或平均值。這種類型的線條使得比較實際值與目標值或平均值變得容易,並且借助這條線,您可以輕鬆地看到哪些值高於或低於這個參考值,如下方截圖所示。本文中,我將介紹如何在 Excel 中創建帶有平均值或目標線的柱狀圖或條形圖。

帶有水平線的柱狀圖 帶有垂直線的條形圖
Column chart with horizontal line Bar chart with vertical line

水平創建帶有平均值或目標線的柱狀圖

要水平創建帶有平均值或目標線的柱狀圖,請按照以下步驟操作:

1. 如果您想創建帶有平均值線的柱狀圖,首先應該計算數據列表的平均值,請在空白單元格中輸入以下公式,然後拖動自動填充柄將公式複製到其他單元格,參見截圖:

=AVERAGE($B$2:$B$7)
提示:如果您想創建帶有目標線的圖表,只需在單元格中輸入目標值即可。

steps on creating column chart with average or target line horizontally

2. 然後,選擇包含平均值的數據範圍,然後點擊 插入 > 插入柱狀圖或條形圖 > 簇狀柱狀圖,參見截圖:

steps on creating column chart with average or target line horizontally

3. 這樣就在工作表中插入了一個柱狀圖,您可以根據需要刪除不需要的元素,例如圖表標題、網格線等,參見截圖:

steps on creating column chart with average or target line horizontally steps on creating column chart with average or target line horizontally steps on creating column chart with average or target line horizontally

4. 接著,右鍵點擊平均數據系列(橙色條),並從上下文菜單中選擇 更改系列圖表類型,參見截圖:

steps on creating column chart with average or target line horizontally

5. 在 更改圖表類型 對話框中,在 選擇數據系列的圖表類型和軸 列表框下,從 圖表類型 下拉列表中為 平均 數據系列選擇 折線圖,參見截圖:

steps on creating column chart with average or target line horizontally

6. 然後,點擊 確定 按鈕,您將得到如下截圖所示的圖表:

steps on creating column chart with average or target line horizontally

7. 接下來,您可以為這條平均線添加數據標籤,雙擊選擇該線最右側的數據點,參見截圖:

steps on creating column chart with average or target line horizontally

8. 然後,點擊 圖表元素,勾選 數據標籤 框,這樣就為平均線插入了數據標籤,參見截圖:

steps on creating column chart with average or target line horizontally

提示:如果您想改變水平線的外觀,只需右鍵點擊該線,然後從 輪廓 下拉菜單中選擇所需的虛線類型、粗細或顏色即可。

在柱狀圖中添加具有不同值的目標線

有時候,您可能希望比較實際值與每行不同的目標值,在這種情況下,為了更清楚地可視化目標值,下面這種類型的圖表可能會對您有所幫助。

steps on adding a target line with different values in a column chart steps on creating column chart with average or target line horizontally steps on adding a target line with different values in a column chart

要創建這種類型的圖表,請按照以下步驟操作:

1. 首先,選擇數據範圍,然後點擊 插入 > 插入柱狀圖或條形圖 > 簇狀柱狀圖 以插入圖表。

steps on adding a target line with different values in a column chart

2. 然後,右鍵點擊目標數據系列(橙色條),並從上下文菜單中選擇 更改系列圖表類型,在 更改圖表類型 對話框中,從 圖表類型 下拉列表中為目標數據系列選擇 折線圖,參見截圖:

steps on adding a target line with different values in a column chart

3. 然後,點擊 確定 按鈕,然後雙擊目標線,並選擇 格式化數據系列,參見截圖:

steps on adding a target line with different values in a column chart

4. 在打開的 格式化數據系列 面板中,在 填充與線條 選項卡下,從 線條 部分選擇 無線條,參見截圖:

steps on adding a target line with different values in a column chart

5. 仍在 填充與線條 選項卡下,切換到 標記 部分,從 標記選項 部分選擇 內置,然後從 類型 下拉列表中選擇水平條,最後,在 大小 框中設置與您的條寬相對應的尺寸,參見截圖:

steps on adding a target line with different values in a column chart

6. 然後,從 填充 部分設置您喜歡的顏色給目標線,參見截圖:

steps on adding a target line with different values in a column chart

7. 最後,您可以根據需要刪除不需要的元素,例如圖表標題、網格線等。

steps on adding a target line with different values in a column chart


使用單選按鈕動態創建帶有平均值線的柱狀圖

如果您有一個包含多年產品銷售的大表格,現在您想在圖表中顯示每年的平均值線。為了處理這個任務,您可以通過點擊單選按鈕來使用帶有平均值線的圖表,如下方示例所示。當選擇相應的年份時,帶有平均值線的圖表將會動態顯示。

要創建這個動態圖表,首先,您應該插入幾個單選按鈕,然後創建一個輔助數據表,圖表基於此表,最後插入一個鏈接到單選按鈕的圖表。請按照以下步驟操作:

首先,插入幾個單選按鈕

1. 點擊 開發工具 > 插入 > 選項按鈕(窗體控件),然後畫出四個單選按鈕並根據需要編輯文本,參見截圖:

steps on creating column chart with average line dynamically by using radio buttons steps on creating column chart with average line dynamically by using radio buttons steps on creating column chart with average line dynamically by using radio buttons

2. 然後,右鍵點擊第一個單選按鈕,並從上下文菜單中選擇 格式控制,參見截圖:

steps on creating column chart with average line dynamically by using radio buttons

3. 在打開的 格式對象 對話框中,在 控制 選項卡下,從 單元格鏈接 文本框中選擇一個單元格來鏈接單選按鈕,然後點擊 確定 關閉它,參見截圖:

steps on creating column chart with average line dynamically by using radio buttons

其次,插入一個用於創建圖表的輔助數據表

4. 從原始表格中複製產品名稱到新的範圍,然後在新複製數據旁邊的空白單元格中應用以下公式,然後拖動填充柄獲取結果,參見截圖:

=VLOOKUP(G2,$A$2:$E$8,$O$1+1,0)
注意:在上述公式中,G2 是您粘貼的產品列表的單元格,A2:E8 是原始數據表的數據範圍,O1 是單選按鈕的鏈接單元格。這個公式用於根據選擇的單選按鈕返回數據列。

steps on creating column chart with average line dynamically by using radio buttons

5. 現在,當您點擊某個單選按鈕時,相應年份的數據將會自動顯示,如下方示例所示:

6. 然後,您可以使用以下公式計算平均值,參見截圖:

=AVERAGE($H$3:$H$8)

steps on creating column chart with average line dynamically by using radio buttons

第三,基於新的輔助數據表創建圖表

然後,選擇新創建的數據,然後請應用方法 1 的第 2 步至第 8 步來創建帶有平均值線的柱狀圖,您將獲得一個動態的帶有平均值線的圖表。參見下方示例:


垂直創建帶有平均值或目標線的條形圖

本節將介紹如何垂直創建帶有平均值或目標線的條形圖,請按照以下步驟操作:

1. 首先,創建一個輔助數據表,在兩個單元格中分別輸入 XY,然後請將平均值計算到單元格 D2 和 D3 中,參見截圖:

=AVERAGE($B$2:$B$7)
提示:如果您想創建帶有目標線的圖表,只需在單元格中輸入目標值即可。

steps on creating bar chart with average or target line vertically

2. 然後,在單元格 E2 和 E3 中分別輸入數字 01 作為 Y 值,參見截圖:

steps on creating bar chart with average or target line vertically

3. 然後,選擇原始數據範圍,然後點擊 插入 > 插入柱狀圖或條形圖 > 簇狀條形圖,參見截圖:

steps on creating bar chart with average or target line vertically

4. 這樣就在工作表中插入了一個條形圖,然後右鍵點擊條形,並從上下文菜單中選擇 選擇數據,參見截圖:

steps on creating bar chart with average or target line vertically

5. 在彈出的 選擇數據源 對話框中,點擊 添加 按鈕,參見截圖:

steps on creating bar chart with average or target line vertically

6. 然後,在 編輯系列 對話框中,在 系列名稱 文本框中輸入系列名稱,然後在 系列值 文本框中選擇包含 X 值的單元格(在此示例中為 D2:D3),參見截圖:

steps on creating bar chart with average or target line vertically

7. 點擊 確定 > 確定 關閉對話框,新的數據系列現在已添加到您的條形圖中(兩個橙色條),然後右鍵點擊橙色條並選擇 更改系列圖表類型,參見截圖:

steps on creating bar chart with average or target line vertically

8. 在彈出的 更改圖表類型 對話框中,從 圖表類型 下拉列表中為 平均 數據系列選擇 帶平滑線的散點圖,參見截圖:

steps on creating bar chart with average or target line vertically

9. 然後,點擊 確定 關閉對話框,新的數據系列轉換為沿主 Y 軸的一個數據點,然後右鍵點擊條形,然後從上下文菜單中選擇 選擇數據,參見截圖:

steps on creating bar chart with average or target line vertically

10. 在 選擇數據源 對話框中,在 圖例條目(系列) 列表框中,點擊 平均 選項,然後點擊 編輯 按鈕,參見截圖:

steps on creating bar chart with average or target line vertically

11. 然後,彈出一個 編輯系列 提示框,在 系列 X 值 框中選擇兩個 X 單元格(D2:D3),在 系列 Y 值 框中選擇兩個 Y 單元格(E2:E3),參見截圖:

steps on creating bar chart with average or target line vertically

12. 點擊 確定 > 確定 關閉對話框,您可以看到一條垂直的平均值線已經添加到圖表中,參見截圖:

steps on creating bar chart with average or target line vertically

13. 然後,右鍵點擊次級垂直軸(右側軸),並從上下文菜單中選擇 格式軸,參見截圖:

steps on creating bar chart with average or target line vertically

14. 在打開的 格式軸 面板中,在 軸選項 選項卡下的 軸選項 部分,於 最大值 框中輸入 1.0,您將得到如下截圖所示的圖表:

steps on creating bar chart with average or target line vertically

15. 最後,您可以根據需要刪除次級垂直軸和不需要的網格線,現在,帶有垂直平均值線的條形圖已完成。參見截圖:

steps on creating bar chart with average or target line vertically

提示:如果您想改變垂直線的外觀,只需右鍵點擊該線,然後從 輪廓 下拉菜單中選擇所需的虛線類型、粗細或顏色即可。

在條形圖中添加具有不同值的目標線

要在條形圖中添加具有不同值的目標線,如下方截圖所示,請按照以下步驟逐步操作:

steps on adding a target line with different values in a bar chart steps on adding a target line with different values in a bar chart steps on adding a target line with different values in a bar chart

1. 選擇數據範圍,然後點擊 插入 > 插入柱狀圖或條形圖 > 簇狀條形圖 插入一個條形圖,參見截圖:

steps on adding a target line with different values in a bar chart

2. 然後,右鍵點擊目標數據系列(橙色條),並從上下文菜單中選擇 格式化數據系列,參見截圖:

steps on adding a target line with different values in a bar chart

3. 在打開的 格式化數據系列 面板中,在 系列選項 選項卡下,將系列重疊更改為 100%,參見截圖:

steps on adding a target line with different values in a bar chart

4. 仍在 格式化數據系列 面板中,切換到 填充與線條 選項卡,從 填充 部分選擇 無填充,參見截圖:

steps on adding a target line with different values in a bar chart

5. 接著,進入 效果 選項卡,在 陰影 部分,請進行以下操作:

  • 顏色 下拉菜單中指定您需要的顏色;
  • 將模糊度設置為 0 pt
  • 然後,將角度調整為
  • 最後,根據需要將距離更改為 2pt

steps on adding a target line with different values in a bar chart

6. 現在,再次切換到 系列選項 選項卡,從 系列選項 部分選擇 次級軸,並根據需要將間隙寬度更改為 90%。參見截圖:

steps on adding a target line with different values in a bar chart

7. 最後,根據需要刪除不需要的元素,例如圖表標題、頂部 X 軸、網格線等,您將得到如下截圖所示的帶有不同目標線的條形圖:

steps on adding a target line with different values in a bar chart


使用便捷功能向柱狀圖或條形圖添加目標/平均值線

Kutools for Excel 提供了 50 多種 Excel 不具備的特殊類型圖表,例如 子彈圖目標與實際圖差值箭頭圖 等。憑藉其便捷的圖表工具- 插入直線到圖表,您只需在 Excel 中點擊一下即可向柱狀圖或條形圖添加目標或平均值線。 點擊免費試用 Kutools for Excel!


下載帶有平均值/目標線的圖表示例文件

sample of creating column chart


最佳的辦公生產力工具

Kutools for Excel - 幫助您脫穎而出

? Kutools AI 助手:基於智能執行生成代碼創建自訂公式分析數據並生成圖表調用 Kutools 函數…來徹底改變數據分析方式。
熱門功能查找、標記或識別重複值  |  刪除空行  |  合併列或單元格而不丟失數據  |  四捨五入無需公式 ...
超級 VLookup多條件  |  多值  |  跨多工作表  |  模糊查找...
高級下拉列表簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  比較列以選擇相同和不同的單元格 ...
特色功能網格聚焦  |  設計檢視  |  增強編輯欄  |  工作簿與工作表管理器 | 資源庫(自動文本)  |  日期提取器  |  合併資料  |  加密/解密儲存格  |  按清單發送電子郵件  |  超級篩選  |  特殊篩選(篩選粗體/斜體/刪除線...) ...
頂級 15 種工具集12 個文字工具添加文本刪除特定字符 ...)  |  50+ 圖表 類型甘特圖 ...)  |  40+ 實用 公式基於生日計算年齡 ...)  |  19 個插入工具插入QR碼從路徑插入圖片 ...)  |  12 個轉換工具金額轉大寫匯率轉換 ...)  |  7 個合併與分割工具高級合併行分割Excel單元格 ...)  |  ... 還有更多

Kutools for Excel 擁有超過 300 種功能,確保您需要的功能只需點擊一下即可實現...


Office Tab - 啟用 Microsoft Office(包括 Excel)中的分頁閱讀和編輯功能

  • 一秒鐘內在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標點擊,告別滑鼠手。
  • 當查看和編輯多個文檔時,您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的分頁功能,就像 Chrome、Edge 和 Firefox 一樣。