跳至主要内容

在Excel中创建带有平均值/目标线的图表

Author: Xiaoyang Last Modified: 2025-05-07

在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 磅
  • 然后,将角度调整为
  • 最后,根据需要将距离更改为 2 磅

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个 插入 工具插入二维码从路径插入图片 ...)  |  12个 转换 工具小写金额转大写汇率转换 ...)  |  7个 合并与拆分 工具高级合并行分割Excel单元格 ...)  |  ... 还有更多

Kutools for Excel拥有超过300种功能,确保您需要的功能只需一键即可实现...


Office Tab - 在Microsoft Office(包括Excel)中启用标签式阅读和编辑

  • 一秒内切换数十个打开的文档!
  • 每天为您减少数百次鼠标点击,告别鼠标手。
  • 在查看和编辑多个文档时,您的工作效率将提高50%。
  • 为Office(包括Excel)带来高效的标签页,就像Chrome、Edge和Firefox一样。