Excel图表学习64： 在Excel中仿制“关键影响因素图”

• 2019 年 12 月 27 日
• 筆記

前言：下面的内容是在chandoo.org上学到的制图技术。Chandoo.org是一个很好的网站，上面分享了很多让人耳目一新的Excel技术知识。

这是Microsoft Power BI引入的一种称为关键影响因素可视化的技术，是一个反映影响度量或者结果的关键因素的图表。例如，如果将客户满意度作为衡量标准，想知道数据的哪些方面对评级的影响最大，那么在创建关键影响因素可视化后，Power BI会找到所有排名靠前的影响因素，如下图1所示。

图1

其实，上图1是在Excel中仿制的“关键影响因素图”。我们没有讲Power BI，只是讲如何在Excel中实现这样的效果。

步骤1：整理数据

假设数据位于下图2所示的表中，表名为data，我们想调查“Salary（薪金）”列的主要影响因素。

图2

步骤2：计算并排序影响因素

首先，生成所有影响因素列表，如下图3所示。

图3

在上图3中，需要计算两种平均值：

1.“=条件“的每列的平均值

2.“<>条件“的每列的平均值

这可以使用AVERAGEIFS公式来实现，例如：

=AVERAGEIFS(data[Salary],data[Dept], “Accounting”)

计算“Accounting”的平均薪酬。

=AVERAGEIFS(data[Salary],data[Dept], “<>Accounting”)

计算除“Accounting”外的其他所有部门的平均薪酬。

现在有了两个平均值，再来计算它们的影响：

影响 = 满足条件的平均值/不满足条件的平均值–1

影响的顺序 = 所有影响中单个的影响等级

使用RANK.AVG()计算影响顺序。

如下图4所示为计算结果。

图4

步骤3：创建图表

选取上图4中的“More by”列和“Influence order”列，插入一个散点图，如下图5所示。

图5

选择垂直轴，按Ctrl+1组合键，在”设置坐标轴格式”中选取“逆序刻度值”前的复选框，如下图6所示。

图6

图表结果如下图7所示。

图7

现在的散点图显示了所有的影响因素，我们只需要限定前8个影响因素，因此将垂直轴的最大和最小值设置为8.5和0，结果如下图8所示。

图8

在工作表中绘制一个气泡形状。复制这个气泡形状，选择图表中的点，按Ctrl+v键粘贴，将图表中的点换成了气泡，如下图9所示。

图9

选择气泡并添加数据标签。标签显示X值或从单元格计算出的标签，将标签居中对齐并根据需要调整字体设置。此时的图表如下图10所示。

图10

添加虚拟序列，其值仅比影响列小1或2%。气泡已经准备好了，我们需要显示一个从0到影响量的箭头。为此，我们将使用误差线，特别是100%负x误差线。

在工作表的计算区域中添加一个新列，该列中的值为影响值-2%，如下图11所示。

图11

将该列添加到图表中，得到的图表如下图12所示。

图12

将100％的负x误差线添加到新添加的系列中并将其格式化：删除垂直误差线；选择水平X误差线并格式其方向为“负偏差”，误差量百分比为100%，末端样式更改为“无线端”，得到的图表如下图13所示。

图13

再设置误差线条颜色和箭头类型，得到的图表如下图14所示。

图14

在工作表计算区域中添加另一个值为-20％的虚拟系列，并创建所需的标签，如下图15所示。

图15

将上面的新系列添加到图表中，得到如下图16所示的结果。

图16

选取新添加的系列并添加数据标签为相邻单元格的值，结果如下图17所示。

图17

按你的想法清理并格式化图表，示例效果如下图18所示。

图18

最后，添加控件以查看正面和负面影响。为此，只需在上图4中的第5列乘以+1或-1。+1表示正面影响，-1表示负面影响。其他一切都按预期工作。可以将其链接到表单控件，以此获得一个动态影响因素图表，正如本文开始的图1所示。