美觀又實(shí)用　打造有數(shù)據(jù)才顯示的圖表

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

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

1. 制作銷售區(qū)域下拉選擇列表

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

2. 使用HLOOKUP函數(shù)引用對應(yīng)的銷售數(shù)據(jù)

在G1單元格中選擇不同的銷售區(qū)域后，我們需要在G列引用對應(yīng)的銷售數(shù)據(jù)。在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函數(shù)引用數(shù)據(jù)，其中“$G$1”（絕對引用）作為要查找的值，查找的區(qū)域是“$B$1：$E$13”，查找范圍的行號是“ROW（A2）”（表示第2行，下拉公式后會依次變?yōu)榈?行、第4行……），參數(shù)“0”表示精確匹配。然后將查找結(jié)果作為IF函數(shù)的判斷依據(jù)，如果未找到結(jié)果（即銷售數(shù)據(jù)為0）就顯示為空，反之則顯示找到的銷售數(shù)據(jù)。

3. 建立自定義公式

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

公式解釋：

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

“Sheet1！$G$2”（即G2單元格）是OFFSE T函數(shù)的引用基準(zhǔn)?！?，0”表示向下偏移0行，向右偏移0列，引用COUNT統(tǒng)計的行數(shù)。如果G列中沒有銷售數(shù)據(jù)，那么就不再引用，從而實(shí)現(xiàn)只引用有銷售數(shù)據(jù)的單元格。

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

4. 插入圖表

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

5. 應(yīng)用自定義公式

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

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

6. 為圖表添加標(biāo)題

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

7. 美化圖表

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