首页 > Excel专区 > Excel技巧 >

excel 如何制作帕累托图案例解析

Excel技巧 2021-11-25 11:43:35

帕累托图由意大利经济学家帕累托发明,由柱形图与折线图组合而成,柱形图中的数据点根据数值大小从高到低排列,折线图反映相应数据点的累计百分比。

案例参考效果

下图左侧A2:C8单元格区域为数据源,A3:C3单元格为辅助行。右侧为根据数据源制作的帕累托图,系列"次数"用柱形图展示,系列"累计百分比"用带数据标记的折线图展示。折线中的每个数据标记均与柱形的垂直边缘线对齐,且前两个数据标记"0%"和"38%"位置分别与第一个柱子左下角和右上角的端点重合。

Excel办公技巧:帕累托图制作案例解析 

在帕累托图中,客诉问题按次数由多到少排列。

操作步骤

1、选中A2:C8单元格区域,单击[插入]选项卡[图表]功能组右下角的对话框启动器按钮,弹出[插入图表]对话框。

Excel办公技巧:帕累托图制作案例解析

2、在[插入图表]对话框中,切换到[所有图表]选项卡,在左侧列表中选择最下方的[组合],在右侧将系列"次数"的图表类型设为"簇状柱形图";将系列"累计百分比"的图表类型设为"带数据标记的折线图"(同时勾选"次坐标轴"),单击[确定]按钮。

Excel办公技巧:帕累托图制作案例解析

3、调整纵坐标轴最大值。

  • 双击主要纵坐标轴,在工作表右侧弹出的[设置坐标轴格式]对话框中,在[坐标轴选项]下将[边界]中的[最大值]设为"40"(系列"次数"所有数据点数值之和)。
Excel办公技巧:帕累托图制作案例解析
  • 双击次要纵坐标轴,在工作表右侧弹出的[设置坐标轴格式]对话框中,在[坐标轴选项]下将[边界]中的[最大值]设为"1"。
Excel办公技巧:帕累托图制作案例解析Excel办公技巧:帕累托图制作案例解析

4、调整系列"次数"的数据范围和分类间距。

  • 单击选中系列"次数",在[设计]选项卡[数据]功能组中单击[选择数据]按钮。在弹出的[选择数据源]对话框中,单击[图例项(系列)]下的[编辑]按钮,弹出[编辑数据系列]对话框。
Excel办公技巧:帕累托图制作案例解析Excel办公技巧:帕累托图制作案例解析
  • 在[编辑数据系列]对话框中,将[系列值]编辑框中默认的"=帕累托图!$B$3:$B$8"改为"=帕累托图!$B$4:$B$8",排除B3单元格中的空值。单击[确定]按钮。
Excel办公技巧:帕累托图制作案例解析
  • 返回[选择数据源]对话框,单击[水平(分类)轴标签]下的[编辑]按钮,弹出[轴标签]对话框。在[轴标签]对话框中将[轴标签区域]编辑框中默认的"=帕累托图!$A$3:$A$8"改为"=帕累托图!$A$4:$A$8",排除A3单元格中的空值。单击[确定]按钮。
Excel办公技巧:帕累托图制作案例解析Excel办公技巧:帕累托图制作案例解析
  • 返回[选择数据源对话框],直接单击[确定]按钮。此时,"尺码不符"前面多余的空柱子已经消失。
Excel办公技巧:帕累托图制作案例解析Excel办公技巧:帕累托图制作案例解析
  • 双击图表中的系列"次数",在工作表右侧弹出[设置数据系列格式]对话框,在[系列选项]下将[分类间距]设为"0"。此时,所有柱子紧靠在一起,且第一个柱子紧贴纵坐标轴。
Excel办公技巧:帕累托图制作案例解析

5、设置次要横坐标轴。

  • 鼠标单击图表,在图表右侧出现浮动按钮,点击最上面的"+"按钮,在弹出的[图表元素]列表中勾选[坐标轴]选项,单击[坐标轴]右侧的三角形按钮,在级联菜单中勾选[次要横坐标轴]。此时,在图表标题下方出现了次要横坐标轴。
Excel办公技巧:帕累托图制作案例解析
  • 鼠标双击次要横坐标轴,在工作表右侧弹出的[设置坐标轴格式]对话框中,将[坐标轴选项]下的[坐标轴位置]设为"在刻度线上"。此时,可见"累计百分比"折线整体向左偏移了半个柱形的宽度,折线的第一个数据标记与系列"次数"第一个柱子左下角的端点重合,其余数据标记均与对应柱子的右边缘对齐。
Excel办公技巧:帕累托图制作案例解析
  • 选中次要横坐标轴,在[开始]选项卡[字体]功能组中将字体颜色设为背景色,可将次要横坐标轴进行隐藏,使图表更加美观。(次要横坐标轴不能直接删除,否则折线图又会回到偏移前的位置。)
Excel办公技巧:帕累托图制作案例解析

6、最后,根据实际需要对图表大小、网格线、数据标签等细节进行调整,最终效果如下图。

Excel办公技巧:帕累托图制作案例解析

标签: excel制作帕累托图案帕累托图案

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.