美觀又實用　打造有數據才顯示的圖表

比如下圖是某公司不同區域的銷售月報表，由于每個區域上報數據的時間并不同步，現在希望只顯示有數據的月份的條形圖，并在添加新的月份數據后自動增加對應的條形圖（圖1）。操作如下：

比如東南區目前只有1～5月份的銷售數據，那么就只顯示這5個月份的條形圖。

1. 制作銷售區域下拉選擇列表

定位到G1單元格，依次點擊“數據→數據驗證→設置→序列”，選擇B1：E1數據區域作為驗證列表，這樣在G1單元格中可以通過下拉列表選擇不同的銷售區域（圖2）。

2. 使用HLOOKUP函數引用對應的銷售數據

在G1單元格中選擇不同的銷售區域后，我們需要在G列引用對應的銷售數據。在G2單元格中輸入公式“=IF（HLOOKUP（$G$1，$B$1：$E$13，ROW（A2），0）=""，""，HLOOKUP（$G$1，$B$1：$E$13，ROW（A2），0））”，下拉填充到G13單元格（圖3）。

公式解釋：

先使用HLOOKUP函數引用數據，其中“$G$1”（絕對引用）作為要查找的值，查找的區域是“$B$1：$E$13”，查找范圍的行號是“ROW（A2）”（表示第2行，下拉公式后會依次變為第3行、第4行……），參數“0”表示精確匹配。然后將查找結果作為IF函數的判斷依據，如果未找到結果（即銷售數據為0）就顯示為空，反之則顯示找到的銷售數據。

3. 建立自定義公式

對于有銷售數據區域的引用需要借助自定義公式來完成。依次點擊“公式→定義名稱→定義名稱”，在打開的對話框中，在“名稱”處輸入“區域”、“引用位置”處輸入“=OFFSET（Sheet1！$G$2，0，0，COUNT（Sheet1！$G：$G））”，點擊“確定”按鈕即可完成公式的建立（圖4）。

公式解釋：

先使用COUNT函數對G列中有銷售數據的單元格計數，接著將結果作為OFFSET函數引用的行號，即G列中有幾個月份的銷售數據，那就引用幾行。

“Sheet1！$G$2”（即G2單元格）是OFFSE T函數的引用基準。“0，0”表示向下偏移0行，向右偏移0列，引用COUNT統計的行數。如果G列中沒有銷售數據，那么就不再引用，從而實現只引用有銷售數據的單元格。

操作同上，繼續新建一個名為“月份”的自定義公式，在“引用位置”處輸入“=OFFSET（Sheet1！$A$2，0，0，COUNT（Sheet1！$G：$G））”。公式的含義同上，即只在A列引用G列中有銷售數據的月份。

4. 插入圖表

按住C t r l 鍵并分別選中A1： A13、G1：G13數據區域，依次點擊“插入→ 圖表→ 條形圖”，按提示插入一個條形圖，可以看到，沒有銷售數據的月份會以空白的形式顯示在條形圖的上方（圖5）。

5. 應用自定義公式

在上述條形圖的任意空白處右擊并選擇“選擇數據”，在打開的窗口中點擊“圖例項（系列）”下的“編輯”按鈕，然后在“系列值”處輸入“=Sheet 1！ 區域”，這里的“區域”就是圖4中建立的自定義公式的名稱（圖6）。

繼續在圖6所示的窗口中點擊“水平（分類）軸標簽”下的“編輯”按鈕，然后在“軸標簽”處輸入“=Sheet 1！ 月份”。最后依次點擊“確定”按鈕保存退出，這樣有銷售數據的月份才會顯示條形圖。

6. 為圖表添加標題

定位到I1單元格并輸入公式“=G1&"區1-"&COUNT（Sheet1！ $G：$G）&"月銷售展示"”，接著點擊圖表的標題區域，在公式欄中輸入“=Sheet1！$I$1”，即引用I1單元格中的內容作為圖表的標題（圖7）。

7. 美化圖表

最后對圖表美化，如取消網格線的顯示、勾選坐標軸的“逆序類別”等即可。以后在原始數據區域中添加新的月份數據后，會自動增加新的條形圖，圖表的標題也會隨之同步發生變化（圖8）。