我是拉小登，一個愛梳頭的 Excel 高能老師。

在日常生活和工作中，我們經(jīng)常需要對比兩個事物，哪個是好的，哪個是不好的。

這種對比差異的分析，有一個更為流行的名稱：差異分析（variance analysis）。

無論何時何地，或者誰來做決策，都需要使用差異報告，來更好的理解當前的狀態(tài)，并決定采取哪些措施。

通常情況下，一個差異分析報告長成這個樣子：

我想，應(yīng)該沒多少人喜歡這樣的表格，對嗎？

面對現(xiàn)實吧！當你成為做決策的那個人的時候，肯定希望這些數(shù)據(jù)能夠用更直觀的形狀、圖形來呈現(xiàn)。

這樣你可以通過自己的眼睛，直觀的感受到差異，而不是費勁腦汁的再算一遍。

這就是為什么「顏色」「圖表」以及其他綜合的「可視化形式」，在差異分析報告中流行的原因了。

接下來，我會跟你分享 10 個差異分析可視化的技巧，讓你的報告不再只是枯燥的數(shù)字和百分比。

方法 1：用括號標記負數(shù)方法

2：自定義數(shù)字格式，設(shè)置數(shù)字顏色方法

?3：使用箭頭方法?

4：使用單元格顏色方法?

5：使用箭頭–條件格式方法?

6：使用數(shù)據(jù)條方法

7：「單元中」的圖表方法?

8：使用按比例縮小的圖表方法

?9：制作更好的差異分析圖方法?

10：在圖表中突出顯示實例方法?

11：漂亮的「實際與目標」差異對比圖方法?

12：帶有浮動條的「實際與目標」差異對比圖方法?

13：使用條形圖進行自上而下的垂直差異對比圖

用括號標記負數(shù)

這是最簡單，也是最正式的方法。

默認情況下，負數(shù)前面會有一個減號，但是這個標記不是很醒目。

把數(shù)字放在括號中，和負號比起來，更容易識別是正數(shù)還是負數(shù)。

具體做法如下：

? 選擇要設(shè)置格式的單元格；

??按下【Ctrl+1】，打開「單元格格式」對話框；

? 在對話框中選擇「數(shù)字」選項卡，選擇最下面的「最定義」；

? 設(shè)置單元格的格式代碼為：0;(0);0

最后點擊「確定」，負數(shù)就被放到括號里了。

如果數(shù)據(jù)是百分比，那么需要修改一下格式的代碼，加上%，具體如下：

0-00%;(0.00)%;0%

如果你是一個強迫癥患者，想要讓數(shù)字都對齊，不會受到括號的影響，讓報告更加美觀，可以把格式代碼改成下面的樣子：

0-00 _）％;（0.00）％; 0 _）％

負數(shù)會被放在括號中，數(shù)字會稍微的偏移一點點，視覺上會有沒對齊的感覺。通過添加「_)」，可以糾正這對齊的問題。

所以，我們在格式代碼中，只是對正數(shù)和 0 添加了這個符號，讓正數(shù)和 0 也稍微的偏移一點點，看起來就更舒服了。

自定義數(shù)字格式，設(shè)置數(shù)字顏色

除了括號之外，我們還可以給負數(shù)設(shè)置不同的顏色，讓數(shù)據(jù)差異更加明顯。

同樣的，還是要按下【Ctrl+1】打開「單元格格式」對話框，然后設(shè)置數(shù)字的格式如下：[Blue]0;[Red]0;0

這樣正數(shù)會顯示為藍色，負數(shù)會變成紅色，0 則保持默認的顏色。

另外，還可以把這個代碼和方法 1 里的代碼結(jié)合起來使用，如下：[Blue]0_);[Red](0);0_)

這樣正數(shù)會顯示藍色；負數(shù)會用括號包裹起來，而且顯示紅色；同時數(shù)字之間的對齊依舊完美。

這里，我們對正數(shù)和負數(shù)，分別設(shè)置了不同的顏色、或者括號，當然你也可以指定特定的顏色代碼，或者只對正數(shù)或負數(shù)單獨設(shè)置格式，這些都是可以的。

使用箭頭

使用上下箭頭，也可以直觀的呈現(xiàn)正數(shù)、負數(shù)的數(shù)值，或者好的、不好的數(shù)據(jù)。

??選擇數(shù)據(jù)，然后按下【Ctrl+1】打開「單元格格式」窗口。

??設(shè)置「自定義」格式代碼如下：0 ▲;0 ▼;0

需要優(yōu)化的一點是，讓箭頭靠右對齊，而數(shù)字靠左對齊，這樣會更加的美觀。

做法非常簡單，在空格前面加一個星號「*」就可以了，所以格式代碼修改為：

0* ▲;0* ▼;0

當然了，我們還可以在代碼中添加顏色的代碼，效果會更加的 wow！

使用單元格顏色

如果你不喜歡通過文本顏色來標注差異，也可以給正數(shù)、負數(shù)設(shè)置不同的單元格顏色。

??選擇要設(shè)置顏色的單元格。

??在「開始」選項卡中，點擊「條件格式」，點擊「新建規(guī)則」。

? 在對話框中，選擇「使用公式確定要設(shè)置格式的單元格」。

然后輸入下面的公式，給<0 的負數(shù)設(shè)置單元格顏色。

• =G5<0
  

點擊「格式」按鈕，設(shè)置一個喜歡的單元格填充顏色，然后點擊「確定」條件格式就設(shè)置好了。

使用箭頭–條件格式

在 03 中，我們使用了「自定義單元格格式」功能，給數(shù)字添加了上下箭頭，來表現(xiàn)數(shù)據(jù)的差異。

其實，使用條件格式也可以添加上下箭頭，而且更簡單、更好看。

? 在「開始」選項卡中點擊「條件格式」；

??點擊「新建規(guī)則」，選擇第 1 個規(guī)則類型；

? 設(shè)置「格式樣式」為「圖標集」；

??然后選擇我們需要的上下箭頭的圖表；

??把數(shù)值的類型改為「數(shù)字」，設(shè)置第 1 個條件是「>0」，第 2 個條件是「>=0」。

最后點擊「確定」，完成條件格式設(shè)置。

使用數(shù)據(jù)條

我最喜歡的方式，可以更直觀的呈現(xiàn)數(shù)據(jù)的差異，那就是「數(shù)據(jù)條」!

? 選擇數(shù)據(jù)單元格；

??在「開始」選項卡中，點擊「條件格式」；

? 選擇「數(shù)據(jù)條」，選擇你喜歡的樣式。

搞定！

「單元中」的圖表

到這里，我們使用了 Excel 內(nèi)置的功能，來實現(xiàn)了差異的各種可視化的形式。

比如數(shù)據(jù)條，條件格式、單元格格式等等。

另外一個方法，是使用 REPT 函數(shù)，結(jié)合特殊的字符，在單元格里做一個圖表。

這個技巧需要兩列的數(shù)據(jù)，分別用來繪制「正數(shù)」和「負數(shù)」的圖表。這里我們使用豎線「|」這個符號，因為可以直接從鍵盤中輸入進去。

案例中，數(shù)據(jù)在 G 列，然后我要用圖形表示數(shù)據(jù)的百分比，所以在單元格里設(shè)置下面兩個公式：

負數(shù)的公式：

• =IF(G5<0,REPT("|",ROUND(ABS(G5)*100,0)),"")
  

正數(shù)的公式：

• =IF(G5>0,REPT("|",ROUND(ABS(G5)*100,0)),"")
  

然后將公式填充到所有單元格。

記下來設(shè)置單元格的格式，把負數(shù)列設(shè)置為右對齊。

設(shè)置兩列「圖表」的字體為：Stencil，如果你的電腦上沒有這個字體，也可以使用「Britanica Bold」或者「Playbill」，效果是一樣的。

如果你覺得條形圖太長了，可以把單元格字號調(diào)整小一點，或者把公式里的 100 改為 50，或者更小的數(shù)字。這個數(shù)字可以理解成條形圖的寬度。

下面是另外一種表現(xiàn)形式，同樣是用的 REPT 函數(shù)方法。

使用按比例縮小的圖表

這是一個讓人無法拒絕的方法，這個方法最好的地方是，圖表可以隨著單元格大小，按比例縮放；圖表就像是「單元格中」的圖表一樣。

我真是愛死這個「按比例縮放」的圖表了，用在 dashboard 中非常的炫酷。

選擇圖表中要呈現(xiàn)的數(shù)據(jù)，然后在「插入」選項卡中，選擇「簇狀條形圖」。

圖表插入完成后，稍微設(shè)置一下圖表的格式。

比如：

? 刪除圖例、標題、橫坐標軸；

??使用單元格格式隱藏「數(shù)據(jù)標簽」。

下圖清晰地展示了整個操作的過程。

本例中，最后一個數(shù)據(jù)被放到了第 1 個位置，這是條形圖的默認設(shè)置，我們可以通過設(shè)置「逆序類別」調(diào)整順序。

這樣數(shù)據(jù)條的順序就正確了，同時正數(shù)顯示在坐標軸右側(cè)，負數(shù)在坐標軸左側(cè)。

使用相同的方法，我們還可以創(chuàng)建更加智能的圖表，負數(shù)顯示紅色，正數(shù)顯示藍色。

制作更好的差異分析圖

先看一下效果。

太美了，有沒有？

想知道是怎么做的嘛？

那就加入我們秋葉家的《秋葉 Excel 3 天集訓營》吧，僅需 1 元，你就能學到超多 Excel 小技巧，助你提高辦公效率！

秋葉 Excel 3 天集訓營

課程原價?99?元?

現(xiàn)在?僅需 1 元！

實操練習+名師帶學

3 天提升你的 Excel 能力！

趕緊掃碼搶課吧！

??????

關(guān)注我【秋葉 Excel】

還能免費領(lǐng)

40+實用圖表和300+函數(shù)公式學習

還有不定期小禮物小福利，歡迎持續(xù)關(guān)注呀~

??

*廣告