帕累托图表由一个柱形图和一个折线图组成,其中柱形用来表示降序的频率值,而折线则表示升序的累积百分比。它常用于问题分析,对原因进行分类排序,识别出重要影响因素,以便针对性的制定改进措施。比如,生产管理中对影响进度的原因分析。

本文将使用滚动条创建动态帕累托图表,通过单击或移动滚动条来更改目标值时,图表将自动突出显示问题类别。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

1、设置辅助区域

在原数据区域设置三个辅助单元格,分别为“目标”、“累计”、“滚动条链接”,用于将“滚动条”的结果进行计算。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

2、创建滚动条

单击菜单栏[开发工具]=>[插入]=>[滚动条(窗体控件)]

在Excel中创建动态帕累托图表的方法-趣帮office教程网

右键单击[滚动条],选择[设置控件格式],在弹出的[设置控件格式]对话框=>[控制]标签,[单元格链接]设置为对应的单元格。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

在Excel中创建动态帕累托图表的方法-趣帮office教程网

3、完善辅助区域公式

分别在单元格C15和C16中输入以下两个公式,然后将它们格式化为百分比值。

C15: =C17/100

C16: =IFERROR(INDEX($D$3:$D$9,IFERROR(MATCH($C$15,$D$3:$D$9,1),0) 1,1),1)

在Excel中创建动态帕累托图表的方法-趣帮office教程网

4、原数据区域增加辅助列

计算累计百分比。输入公式:=SUM($C$3:C3)/SUM($C$3:$C$9),并将公式复制到D3:D9数据区域。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

在原数据旁边创建两个辅助列,并在单元格E3和F3中输入以下两个公式:

E3: =IF(D3<=$C$15,C3,NA()) F3: =IF(D3>$C$15,C3,NA())

然后将公式复制到其他单元格。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

5、插入并设置组合图表

按住Ctrl键,分别选中B/D/E/F列的数据,单击[插入]=>[图表]=>[簇状柱形图]

在Excel中创建动态帕累托图表的方法-趣帮office教程网

在Excel中创建动态帕累托图表的方法-趣帮office教程网

6、调整图表格式

选中图表,右键单击,选择[更改图表类型]

在Excel中创建动态帕累托图表的方法-趣帮office教程网

在弹出的[更改图表类型]对话框,选择[组合图]。并将[累计百分比]系列改为[折线图],勾选[次坐标轴]

在Excel中创建动态帕累托图表的方法-趣帮office教程网

将次坐标轴的最大值改为1。右键单击次坐标轴,选择[设置坐标轴格式],将[坐标轴选项]=>[边界]=>[最大值]改为1

在Excel中创建动态帕累托图表的方法-趣帮office教程网

右键单击柱形图,选择[设置数据系列格式],将[系列选项]=>[系列重叠]设置为100%

在Excel中创建动态帕累托图表的方法-趣帮office教程网

7、完善图表

将滚动条移动到图表下方,然后在下方的单元格输入以下公式: ="目标 "&TEXT(C15,"0%"),以便获取目标百分比值。

在Excel中创建动态帕累托图表的方法-趣帮office教程网

这样就做好了一张动态的帕累托图,在使用滚动条更改目标值时,将自动更新图表。

在Excel中创建动态帕累托图表的方法-趣帮office教程网