在本文中,您将学习如何创建动态帕累托图。

帕累托图以维尔夫雷多·帕累托(Vilfredo Pareto)命名,是一种包含条形图和折线图的图表类型,其中各个值由条形降序表示,累计总和由折线表示。

让我们以一个例子来理解:

  • 我们有学校投诉数据,并且需要创建帕累托图。

img1

我们需要C列中的辅助列“累积百分比”;单元格C2中的公式为= SUM($ B $ 2:B2)/ SUM($ B $ 2:$ B $ 11)*并将公式向下拖动

img2

创建动态帕累托;图表中,我们需要三个单元格,我们将在其中进行一些计算。首先,我们将创建滚动条并将其链接到单元格B16上,右键单击滚动条并选择“格式控制”,然后输入如下快照所示的值

img3

在单元格B14中,公式为= B16 / 100以计算* Target

在单元格B15中;对于*累积百分比

,我们有以下公式

* = INDEX($ C $ 2:$ C $ 11,IFERROR(MATCH($ B $ 14,$ C $ 2:$ C $ 11,1),0)+1)

img4

  • 现在我们需要再创建2个帮助器列,即突出显示的%和剩余的%

在单元格D2中,公式为* = IF($ B $ 15> = C2,B2,NA())

img5

在单元格E2中,公式为* = IF($ B $ 15 <C2,B2,NA())

img6

  • 最后,所有设置均已创建帕累托图;我们需要选择范围A1:A11和C1:E11

img7

在“设计”选项卡上,单击“图表类型”,然后将“折线图类型”应用于“累计百分比”,然后单击“辅助轴”

img8

img9

|。我们需要将累积百分比设置为100%,以显示120%,右键单击辅助轴并选择“格式化轴” *选择“最大值”为1而不是1.2

img10

现在我们都可以查看动态帕累托图;唯一的问题是,当您单击滚动条时,您会发现条形将从其原始位置移动,因为有两个系列。要解决此问题,我们将单击“ Broken Benchs(Highlighted%)”,然后右键单击它,选择“格式化数据系列”。在系列重叠中,输入100%,请参考以下快照

img11

  • 查看选择的目标百分比;在单元格J24中,将公式输入为

* =“目标”&TEXT(B14,“ 0%”)

img12

这样,我们可以制作动态帕累托图,其中随着您增加或减少滚动条值,它将更新目标。