Excel 怎么用帕累托图找出主要问题
问题一堆,全改不现实,先找出贡献最大的那几项。讲清帕累托图在 Excel 里怎么调出来、组合图怎么自己搭,以及排序和坐标轴最容易错的地方。
返工、投诉、故障原因列了一长串,真要改又没那么多人力,通常结论是「先抓两三项」。帕累托图就是把问题按数量从多到少排开,再叠一条累计占比曲线,一眼看出哪几项占了大头。Excel 里有现成的图表类型,旧版本也能用组合图自己搭。
入口在哪
较新的 Excel 在「插入」选项卡的图表区里有「统计图表」分组,下面带「排列图」,也叫帕累托图,选中两列数据点它就会自动排序并算累计占比。旧版本、网页版或者部分 WPS 不一定有这个分组,找不到就在「插入 > 图表」里逐个看一遍,或者在帮助里搜「排列图」。
没有这个图表类型也不影响,用组合图手工搭,效果一样,而且横轴顺序、坐标轴刻度都能自己控。下面两条路都写一遍,按手上的版本选。
具体步骤
- 准备两列:一列问题类别,一列出现次数。只放原始计数,百分比和累计值都交给图表或者公式算,别手工填。
- 走现成图表:选中这两列,插入统计图表里的排列图。它会自动降序排好,并把累计占比画成折线。
- 图出来以后右键数据系列,打开数据标签。柱子上标次数,折线上标百分比,汇报时才说得清。
- 走组合图:先按次数降序排序,再新增一列累计占比,公式写成逐行扩展的求和区域,除号后面固定总数区域。
- 选中类别、次数、累计占比三列,插入组合图:次数用簇状柱形,累计占比用折线并勾选次坐标轴。
- 把次坐标轴的最大值手动设成 100%,最小值设成 0,再加标题和备注,横轴类别多的时候把字旋转或者只留前几位。
几个坑
- 没排序就画。帕累托图的前提是降序,不排序柱子高低乱跳,累计曲线也失去意义。
- 类别太碎。几十个类别挤在一张图上没人看得懂,先把尾部合并成「其他」,或者只画前八到十项。
- 把占比当次数。累计占比必须走次坐标轴并固定 0 到 100%,和柱子共用一根轴,曲线会贴地看不见。
- 拿平均值来画。帕累托用的是计数、金额这类可加总的量,平均分、满意度这类指标不适用。
- 只画一次就下结论。样本小、时间跨度不一致时排序会变,按月或者按季度分开画再做对比更稳。
怎么验证
三个地方对得上,图就没画错:柱子加起来等于原始总数,最后一根柱子的累计占比正好是 100%,折线是单调不减的。
再挑排第一的类别,用筛选或者单独求和核一次次数,和柱子上的标签比对。把鼠标停在折线上看点提示,确认显示的是累计百分比而不是单点百分比,这一步最容易漏。
常见问题
版本里找不到排列图
老版本 Excel 和部分 WPS 确实没有这个图表类型,用组合图手搭。先降序排好数据,自己做一列累计占比,再用柱形加折线的组合图并勾上次坐标轴。
累计占比的公式怎么写
用逐行扩展的区域:起始单元格加绝对引用、结束单元格跟着下拉走,最后一行结果应该正好是 100%。区域写死的话,下拉之后就不对了。
类别里有重复项怎么办
先用数据透视表按类别汇总次数,再对汇总结果画图。类别没合并就画,同一个问题会被拆成好几根柱子,排序和累计都会失真。
一句话总结:先把问题按次数降序排好,再用柱形加累计占比折线的组合图,看累计曲线爬到八成左右的那一段,就知道该先改哪几项。