如何在Excel中创建动态帕累托图
在本文中,您将学习如何创建动态帕累托图。
帕累托图以维尔夫雷多·帕累托(Vilfredo Pareto)命名,是一种包含条形图和折线图的图表类型,其中各个值由条形降序表示,累计总和由折线表示。
让我们以一个例子来理解:
-
我们有学校投诉数据,并且需要创建帕累托图。
我们需要C列中的辅助列“累积百分比”;单元格C2中的公式为= SUM($ B $ 2:B2)/ SUM($ B $ 2:$ B $ 11)*并将公式向下拖动
创建动态帕累托;图表中,我们需要三个单元格,我们将在其中进行一些计算。首先,我们将创建滚动条并将其链接到单元格B16上,右键单击滚动条并选择“格式控制”,然后输入如下快照所示的值
在单元格B14中,公式为= B16 / 100以计算* Target
在单元格B15中;对于*累积百分比
,我们有以下公式
* = INDEX($ C $ 2:$ C $ 11,IFERROR(MATCH($ B $ 14,$ C $ 2:$ C $ 11,1),0)+1)
-
现在我们需要再创建2个帮助器列,即突出显示的%和剩余的%
在单元格D2中,公式为* = IF($ B $ 15> = C2,B2,NA())
在单元格E2中,公式为* = IF($ B $ 15 <C2,B2,NA())
-
最后,所有设置均已创建帕累托图;我们需要选择范围A1:A11和C1:E11
在“设计”选项卡上,单击“图表类型”,然后将“折线图类型”应用于“累计百分比”,然后单击“辅助轴”
|。我们需要将累积百分比设置为100%,以显示120%,右键单击辅助轴并选择“格式化轴” *选择“最大值”为1而不是1.2
现在我们都可以查看动态帕累托图;唯一的问题是,当您单击滚动条时,您会发现条形将从其原始位置移动,因为有两个系列。要解决此问题,我们将单击“ Broken Benchs(Highlighted%)”,然后右键单击它,选择“格式化数据系列”。在系列重叠中,输入100%,请参考以下快照
-
查看选择的目标百分比;在单元格J24中,将公式输入为
* =“目标”&TEXT(B14,“ 0%”)
这样,我们可以制作动态帕累托图,其中随着您增加或减少滚动条值,它将更新目标。