在Excel中创建帕累托图
在Excel中,帕累托图由柱状图和折线图组成,其中柱形表示按降序排列的频率值,而折线则表示在次坐标轴上的累计总数。它用于分析产品缺陷中的重要领域,并确定能够提升公司价值的改进措施。要创建一个帕累托图以显示工作表中最常见的失败原因或产品缺陷,本文将为您提供帮助。
在Excel 2016及更高版本中创建简单的帕累托图
如果您使用的是Excel 2016及更高版本,内置了一个功能——帕累托图,可以帮助您快速轻松地插入帕累托图。请按照以下步骤操作:
1. 选择您希望基于其创建帕累托图的数据区域。
2. 然后,点击 插入 > 插入统计图表 > 帕累托图,参见截图:
3. 接着,立即生成了一个帕累托图,如下截图所示:
4. 然后,您可以根据需要编辑图表标题并添加数据标签,参见截图:
在Excel 2013及更早版本中创建简单的帕累托图
如果您使用的是Excel 2013及更早版本,请逐一应用以下步骤:
首先,准备用于创建帕累托图的数据:
1. 您应该按降序对数据进行排序,请选择单元格B2,然后点击 数据 > 降序(Z到A),参见截图:
2. 然后,数据区域已按降序排列,参见截图:
3. 接着,通过在此情况下在单元格C2中输入公式 =B2 来计算累积计数,并按 Enter 键。参见截图:
4. 然后,在单元格C3中输入公式 =C2+B3,并向下拖动填充柄至其他单元格,参见截图:
![]() | ![]() | ![]() |
5. 在获得累积计数之后,请继续计算累积百分比,输入公式:=C2/$C$11 到单元格D2,并向下拖动填充柄至所需单元格,参见截图:
6. 然后,您应将小数值转换为百分比值,选择公式单元格,然后点击 开始 > 百分比样式,参见截图:
第二步,基于数据创建图表
7. 准备好数据后,请按住 Ctrl 键选择列A、列B和列D中的数据,参见截图:
8. 然后,点击 插入 > 插入柱形图或条形图 > 簇状柱形图,参见截图:
9. 您将得到如下截图所示的图表:
10. 然后,右键单击一条红色柱形(累积百分比),并从上下文菜单中选择 更改系列图表类型,参见截图:
11. 在“更改图表类型”对话框中,在“所有图表”选项卡下,从左侧窗格中点击 组合 图表选项,在“为您的数据系列选择图表类型和坐标轴”列表框中,点击累积字段的下拉列表,选择 带标记的折线图 类型,并勾选复选框,参见截图:
12. 然后,点击 确定 按钮,您将得到如下截图所示的图表:
13. 然后,右键单击百分比坐标轴,然后从上下文菜单中选择 格式化坐标轴 选项,参见截图:
14. 在 格式化坐标轴 面板中,在 坐标轴选项 选项卡下,最大值旁边,将数字设置为 1.0 输入文本框中,帕累托图便成功创建了,参见截图:
15. 最后,您可以根据需要更改图表标题并添加数据标签。
在Excel中创建动态帕累托图
在本节中,我将讨论如何通过使用滚动条来创建动态帕累托图。当您通过点击或移动滚动条更改目标值时,图表将自动高亮显示问题所在的柱形,如下演示所示。
要创建这种类型的帕累托图,请按以下步骤逐步操作:
1. 首先,在列C中计算累积百分比,请在单元格C2中应用此公式:
2. 然后向下拖动填充柄至其他单元格,并将小数格式化为百分比值,参见截图:
3. 接下来,您需要三个辅助单元格,分别是目标值、累积值、滚动条链接值来进行一些计算,如下截图所示:
4. 创建这三个单元格后,点击 开发工具 > 插入 > 滚动条(窗体控件),然后绘制一个滚动条,如下截图所示:
![]() | ![]() | ![]() |
5. 然后,右键单击滚动条,并从上下文菜单中选择 格式化控件,在弹出的 格式化对象 对话框中,在 控制 选项卡下,设置相关值并指定链接单元格,如下截图所示:
6. 然后,点击 确定 按钮关闭对话框。现在,请分别在单元格B14和B15中输入以下两个公式,然后将它们格式化为百分比值:
B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)
7. 现在,您应在原始数据旁边创建两个辅助列,并在单元格D2和E2中输入以下两个公式:
E2: =IF($B$15
8. 然后将公式复制到其他单元格,如下截图所示:
9. 创建数据后,请按住 Ctrl 键选择列A、列C、列D、列E中的数据,然后点击 插入 > 插入柱形图或条形图 > 簇状柱形图,插入的图表如下截图所示:
10. 然后,选择图表,并点击 设计 > 更改图表类型,在 更改图表类型 对话框中,在 所有图表 选项卡下,从左侧窗格中点击 组合 图表,然后点击 自定义组合 图表,然后在 为您的数据系列选择图表类型和坐标轴 部分,请为每个数据系列指定以下操作:
- 累积%:折线图类型,并勾选 次坐标轴 复选框;
- 高亮柱形:簇状柱形图类型;
- 剩余柱形:簇状柱形图类型。
11. 然后,点击 确定 按钮关闭对话框,您将得到如下截图所示的图表:
12. 然后,右键单击百分比坐标轴,然后从上下文菜单中选择 格式化坐标轴 选项,在展开的 格式化坐标轴 面板中,在 坐标轴选项 选项卡下, 最大值旁边,将数字设置为 1.0 输入文本框中,参见截图:
13. 然后,继续右键单击图表中的任意柱形系列,然后选择 格式化数据系列,在 格式化数据系列 面板中,在 系列选项 选项卡下,在 系列重叠 文本框中输入 100%,参见截图:
14. 最后,您可以将滚动条移动到图表下方,并在滚动条下方的单元格中输入此公式:="目标 "&TEXT(B14,"0%") 以获取目标百分比值,参见截图:
15. 现在,动态帕累托图已经创建完成,您可以根据需要更改高亮柱形的填充颜色,并且当使用滚动条更改目标值时,帕累托图将自动更新,如下演示所示:
下载帕累托图示例文件
视频:在Excel中创建帕累托图
最佳的办公生产力工具
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一样。