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

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

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

案例参考效果

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

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

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

操作步骤

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

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

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

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

单击选中系列"次数"，在［设计］选项卡［数据］功能组中单击［选择数据］按钮。在弹出的［选择数据源］对话框中，单击［图例项（系列）］下的［编辑］按钮，弹出［编辑数据系列］对话框。

在［编辑数据系列］对话框中，将［系列值］编辑框中默认的"=帕累托图!$B$3:$B$8"改为"=帕累托图!$B$4:$B$8"，排除B3单元格中的空值。单击［确定］按钮。

返回［选择数据源］对话框，单击［水平（分类）轴标签］下的［编辑］按钮，弹出［轴标签］对话框。在［轴标签］对话框中将［轴标签区域］编辑框中默认的"=帕累托图!$A$3:$A$8"改为"=帕累托图!$A$4:$A$8"，排除A3单元格中的空值。单击［确定］按钮。

双击图表中的系列"次数"，在工作表右侧弹出［设置数据系列格式］对话框，在［系列选项］下将［分类间距］设为"0"。此时，所有柱子紧靠在一起，且第一个柱子紧贴纵坐标轴。

５、设置次要横坐标轴。

鼠标单击图表，在图表右侧出现浮动按钮，点击最上面的"＋"按钮，在弹出的［图表元素］列表中勾选［坐标轴］选项，单击［坐标轴］右侧的三角形按钮，在级联菜单中勾选［次要横坐标轴］。此时，在图表标题下方出现了次要横坐标轴。

鼠标双击次要横坐标轴，在工作表右侧弹出的［设置坐标轴格式］对话框中，将［坐标轴选项］下的［坐标轴位置］设为"在刻度线上"。此时，可见"累计百分比"折线整体向左偏移了半个柱形的宽度，折线的第一个数据标记与系列"次数"第一个柱子左下角的端点重合，其余数据标记均与对应柱子的右边缘对齐。

选中次要横坐标轴，在［开始］选项卡［字体］功能组中将字体颜色设为背景色，可将次要横坐标轴进行隐藏，使图表更加美观。（次要横坐标轴不能直接删除，否则折线图又会回到偏移前的位置。）

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