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所示。