Excel制作帕累托图教程

功能目标

使用Excel制作帕累托图，直观展示数据中“关键的少数”，快速识别影响最大的因素，辅助决策分析。帕累托图（Pareto图）结合柱形图和折线图，帮助用户聚焦核心问题，提升数据分析效率。

适用软件和版本

Microsoft Excel（桌面版，适用于Windows和macOS系统，Excel 2010及更高版本均支持组合图功能）。所有主流版本均可完成帕累托图制作，无需额外插件。

使用前准备

• 一份包含项目名称和对应数值的数据表格，例如产品名称和销售额。
• 数据已按数值从大到小排序（帕累托分析要求数据按降序排列，否则无法正确体现“二八原则”）。

功能入口

通过Excel的“插入”选项卡，在“图表”组中点击“组合图”或“所有图表”→“组合图”来完成。该功能是制作帕累托图的核心入口。

操作步骤

1. 整理数据并计算累计百分比

   • 在原有数据旁边新增两列：“占比”和“累计占比”。
   • 计算占比：每个项目的数值除以所有项目数值的总和，使用公式如 =B2/SUM(B:B)（假设B列为销售额），并设置为百分比格式。
   • 计算累计占比：从第一个项目开始，依次累加占比。例如，C2单元格输入 =D2，C3输入 =C2+D3，向下填充。累计占比是帕累托图的关键数据列。

2. 选择数据区域

   选中包含项目名称、原始数值、占比和累计占比的所有列（例如A列到D列）。确保数据区域完整，便于Excel识别。

   

3. 插入组合图

   • 点击顶部菜单栏的“插入”选项卡。
   • 在“图表”组中，点击“插入组合图”图标（或“所有图表”→“组合图”）。
   • 选择“簇状柱形图 - 折线图”预设，或手动设置。此操作直接生成帕累托图基础框架。

4. 设置图表类型

   • 在“组合图”设置窗口中，将“销售额”（或原始数值）系列设置为“簇状柱形图”。
   • 将“累计占比”系列设置为“折线图”，并勾选“次坐标轴”以将折线图显示在右侧。次坐标轴确保折线图数值范围在0%~100%。
   • 将“占比”系列（如果不需要）设置为“无”或直接删除。
   • 点击“确定”生成图表。此时帕累托图雏形已现。
   

5. 美化图表

   • 点击图表，使用“图表工具”选项卡下的“设计”和“格式”进行调整。
   • 修改折线颜色：右键点击折线→“设置数据系列格式”→“填充与线条”→选择醒目的颜色（如红色），突出累计占比曲线。
   • 调整柱形图颜色：右键点击柱形→“设置数据系列格式”→选择合适的填充色，如蓝色或绿色，使柱形图清晰易读。
   • 为图表添加标题：双击图表标题，输入“销售产品帕累托分析”。标题应明确反映分析对象。
   • 添加坐标轴标签：点击“图表元素”按钮（+），勾选“轴标题”，分别输入横轴和纵轴说明。例如横轴为“产品名称”，主纵轴为“销售额”，次纵轴为“累计占比”。
   • 调整坐标轴范围：右键点击纵轴→“设置坐标轴格式”，适当调整最小值、最大值，使数据展示清晰。主纵轴起点设为0，次纵轴范围设为0%至100%。
   

6. 突出关键数据

   • 在图表中标记80%累计占比线：添加一条水平参考线（例如通过添加辅助数据或使用“形状”工具手动绘制一条直线）。80%线是帕累托分析的核心参考。
   • 或者用不同颜色突出显示累计占比达到80%之前对应的柱形图（即前几个项目），让“关键的少数”一目了然。通常这些项目贡献了约80%的结果。

参数说明

• 占比：每个项目数值占总值的百分比，用于计算累计占比。占比是帕累托图的基础数据。
• 累计占比：从第一个项目开始累加占比，直到100%。通常关注累计占比达到80%左右对应的项目，视为关键因素，即“二八原则”中的少数。
• 组合图设置：将数值系列设为柱形图（主纵轴），累计占比系列设为折线图（次纵轴），便于对比。这是帕累托图的标准图形结构。
• 次坐标轴：累计占比的折线图使用次坐标轴，使其数值范围在0%～100%之间，与柱形图的数值范围分离，避免比例失真。

结果验证

• 图表中应显示柱形图（按降序排列）和折线图（累计占比从低到高上升）。
• 折线图在80%位置对应的横轴项目，即为贡献约80%结果的关键少数。验证此点可确认帕累托分析有效。
• 数据标签显示正确，无错位或重叠。若数据标签缺失，可手动添加。

常见问题

• 数据未排序导致图表不正确：帕累托分析要求数据按数值从大到小排序，否则图表无法体现“二八原则”。请务必在插入图表前完成降序排列。
• 累计占比折线图显示异常：检查累计占比公式是否正确，确保第一个累计占比等于第一个占比，后续依次累加。若出现负值或跳跃，需重新检查公式。
• 柱形图和折线图共用同一个纵轴：在设置组合图时，必须将累计占比勾选“次坐标轴”，否则折线图会被压缩在底部，无法正常显示。这是常见错误。
• 图表标题或轴标签未显示：点击图表右上角的“+”按钮，勾选“图表标题”和“轴标题”即可添加。也可在“图表工具-设计”中添加。

关闭或恢复方法

• 关闭图表：直接删除图表即可，不影响原始数据。如需重新制作，可重复上述步骤。
• 恢复默认设置：选中图表，在“图表工具”→“设计”→“重置以匹配样式”可恢复为Excel默认样式，保留数据但清除自定义格式。
• 保存工作簿：按 Ctrl+S 保存整个Excel文件，图表将随文件保存。建议定期保存以防数据丢失。