數據可視化：如何用箭頭標註指標的同比增減情況？

前天《「偷懶」的技術》讀友群里有讀者提出下面的問題：

下表中用Excel的什麼功能能表示數據的同比增減情況？如果環比有所增加，用向上箭頭標註；若同比有所下降，用向下箭頭標註。

這是財務人員很常見的一個需求，很有代表性，就抽時間將原來簡短的回答擴充成本篇文章。希望對大家有幫助。

這個需求有二個方法可以實現：公式和條件格式（圖標集）。圖標集是2007版才增加的功能，所以Excel 2003版的用戶只能使用第一個方法。

問題看以簡單實際上還是有點小小的困難，難點在於：

公式法：如何智能化的判斷數據是增還是減，自動給箭頭添加不同的顏色，以示區分。

圖標集：如何批量給多個單元格加上相應的圖標？

下面分別介紹

一、 n使用公式

在D2單元格中輸入公式

=IF(C2>B2,"▲",IF(C2=B2,"","▼"))

然後將D列的列寬設置剛好一個字元的寬度，這樣上下三角箭頭就緊貼2016年的數據。效果如下圖：

這個需求有二個方法可以實現：公式和條件格式（圖標集）。圖標集是2007版才增加的功能，所以Excel 2003版的用戶只能使用第一個方法。

下面分別介紹

一、 n使用公式

在D2中輸入公式

=IF(C2>B2,"▲",IF(C2=B2,"","▼"))

然後將D列的列寬設置剛好一個字元的寬度，這樣上下三角箭頭就緊貼2016年的數據。效果如下圖：

儘管已經實現了所需的功能，但是不太完美，上下三角箭頭顏色一樣的，不好區分，不能一眼就看出哪個是增加，哪個是減少。所以應該用顏色區分：假設同比增長用紅色，同比下降用綠色。如果數據是固定不變，我們可以通過設置單元各的字體顏色來分別將其設為紅色或綠色。假如數字可能會發生變化，為了達到字元隨在數字大小相應變化，可以用自定義格式。

操作步驟：

1、 n將D2單元格公式改為=IF(C2>B2,1,IF(C2=B2,0,-1))，然後下拉填充至D8。此公式的計算結果是，當2016年金額顯示大於2015年時，返回1，小於2015時返回－1，二者相等時為空。

nnnn2、 選定D2:D8單元格區域，右鍵，設置單元格格式「[紅色]▲;[綠色]▼;」，如下圖

自定義格式解釋：

上圖的自定義格式用分號隔成三段，第一段用於正數的格式，第二段用於負數，第三段用於0。[紅色]表示將字體設置為紅色。翻譯一下就是：如果單元格的值是正數，就顯示為紅色上三角形（公式結果為1的顯示紅色上三角），如果是負數就顯示為綠色下三角形（公式結果為－1的顯示綠色下三角），如果是零，顯示為空。

詳細的解釋參見文後的自定義格式的相關知識點。

二、 n使用條件格式

在Excel 2007的版本中條件格式增加了圖標集的功能，我們可以使用這個功能進行標註。但是這個功能有一點小小的缺陷：不能使用相對引用，也就是不能將多單元格批量設置為符合要求的圖標。

下面我們來看如何設置以及如何規避這個缺陷。

步驟1：n

選中C2:C8單元格區域，點擊Excel的「開始」選項卡下的「條件格式」圖標，在彈出的下拉菜單選擇「新建規則」，在彈出的新建規則窗口中，規則類型選擇第一個「基於各自值設置所有單元格的格式」；格式樣式設置為「圖標集」，圖標樣式選擇上下箭頭（也可選擇上下三角符號）。然後按下圖進行設置：

設置後出現的結果如下：

顯然，C3和C4單元格圖標是錯誤的。為什麼出現這種結果呢？

因為條件格式設置的值是$B$2，為絕對引用，C2:C8單元格都是與B2單元格進行比較，而不是與同一行的單元格比較，所以會出現上面的錯誤。

那我們將$B$2改為B2，怎麼樣呢，會出現這樣的錯誤提示：

假如再將其改成$B$$2:$B$8單元格區域，又會彈出下面的錯誤提示：

那如何才能讓條件格式進行相對引用呢？我們可以這樣做：

選擇要設置條件格式的單元格時，僅選擇C2單元格，然後還是按照上面的步驟進行設置，將值「＝$B$2」改為

=OFFSET($B$2,ROW()-2,0)n

然後點擊「確定」退出。

公式中的ROW表示取當前行的行號，OFFSET函數為單元格引用函數，上面的公式表示以B2為基準，向下偏移到對應行的單元格。

通過OFFSET函數和ROW函數，解決了圖標集不能使用相對引用的問題。

我們在設置好B2單元格的條件格式後，再選擇B2單元格，然後雙擊格式刷，逐個點擊C3、C4……C8單元格，將B2單元格的條件格式應用到C3、C4……C8單元格。

通過這個方法可以解決不能將多個單元格批量設置條件格式（圖標集）的問題

設置好後，效果如下圖

要下載本篇文章示例文件的朋友，歡迎加入QQ群166053131下載，也大家在此探討交流用Excel偷懶的技術。

附「自定義格式中關於條件設置」的相關知識：

• 完整的條件格式共分為四段：

[條件1][顏色1]數字格式1;[條件2][顏色2]數字格式2;[顏色3]數字格式3;[顏色4]文本格式4n

• 四段不一定要分全，如果只寫一段，則為：

[條件][顏色]數字格式n

用於表示所有滿足條件的全部數字，

• 如果是二段：

[條件][顏色]數字格式;[顏色3]數字格式3n

第一段表示用於滿足條件的數字，第二段表示不滿足條件的格式

• 如果是三段：

[條件1][顏色1]數字格式1;[條件2][顏色2]數字格式2;[顏色3]數字格式3n

第一段用於滿足條件1的數字，第二段用於滿足條件2的數字，第三段表示不滿足條件1、2的格式

條件1和條件2都可以被省略。當未指定條件時，條件1默認為">0"，條件2默認為「<0"。即四個區段中，第一區段用於正數，第二區段用於負數，第三區段用於0，第四區段用於文本。即為下面的格式

關於自定義格式中各種符號的相關知識及應用，其內容較多，這裡就不一一介紹了，請參閱Excel的幫助文件或閱讀《「偷懶」的技術：打造財務Excel達人》的第三章。

推薦閱讀：