如果老板看慣了你一直上報的平淡表格（如下），現在你突然展現給他的是可以動態查詢的圖表（如下），你說能否擊中老板挑剔的心？能否讓老板驚訝激賞？

?

?

怎么實現這種轉變？很簡單，就是做動態圖表。動態圖表就是老板選擇不同的區域，圖表就展示不同的數據。要實現需要三步。第一步做下拉菜單供老板選擇；第二步做一個根據選擇，動態變化的數據區域；第三步根據動態數據區域插入圖表。動態數據區域常用VLOOKUP函數實現，但今天不走尋常路，我們利用OFFSET函數完成動態數據區域。

第一步：做下拉選擇

1.選中J1單元格，點擊“數據”選項卡下的“數據驗證”。

?

2.在“數據驗證”窗口下方的“設置”選項里，“允許”選擇“序列”，來源選擇五個銷售區域所在的單元格“=$A$2:$A$6”，點擊確定。

?

第二步：建立動態更新的輔助數據區域

3.接下來需要設置根據J1單元格的值來動態更新圖表。J1選擇“北京區域”。在B7單元格輸入“=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)”。然后公式往右填充至G7單元格。這樣B7:G7單元格返回的就是北京區域1-6月的銷售額。

?

解析

利用OFFSET以“B1”為參考系，偏移的行數是使用MATCH函數獲取的$J$1在$A$2:$A$6的位置，偏移列數為0表示不偏移。如圖J1的值是“北京區域”，在$A$2:$A$6的位置為1，OFFSET返回的值是以“B1”為參考系，向下偏移一行的引用。這樣隨著選擇區域$J$1的不斷變化，B7:G7單元格就能獲取到對應區域的銷售數據。

4.然后設置平均線的數據，在B8單元格輸入“=AVERAGE($B$7:$G$7)”，獲取$B$7:$G$7的平均值。然后公式往右填充至G8單元格。如果選擇區域$J$1變化，則$B$7:$G$7變化，平均值區域$B$8:$G$8也會隨之變化。

?

第三步：創建圖表

5.我們根據設置好的輔助行創建圖表。選擇標題B1:G1和輔助行B7:G8區域，點擊”插入”選項卡下的”圖表”組里的“二維柱形圖”。

?

6.在K1單元格輸入“=J1&"銷售數據"”。

?

點擊圖表標題框，在編輯欄輸入“=Sheet2!$K$1” ,這樣圖表標題就和數據驗證區域同步更新了。

?

7.點擊“圖表工具”下方“設計”選項卡下的“更改圖表類型”。

?

8.在“更改圖表類型”窗口，點擊“所有圖表”選項下的“組合”，將平均值所在的系列2修改成“折線圖”。

?

9.最后將圖表圖例刪除，把輔助數據B7:G8和K1單元格字體修改成白色不可見，就完成了。

?

實現透視表與數據源同步更新

數據透視表要想實現和數據源同步的更新，除了之前給大家介紹過超級表可以實現外，我們也可以用OFFSET實現。如圖，右側透視表是根據左側數據源插入的。

?

現在我們需要實現在數據源更新后，透視表也能同步更新，如下：

?

第一步：定義名稱

1.點擊“公式”選項卡下的“定義的名稱”選項組里的“定義名稱”。

?

2.在“新建名稱”窗口，“名稱”欄輸入“數據”，在“引用位置”處輸入下列公式

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

?

解析

“Sheet1”是數據所在的工作表。公式的意思就是以“Sheet1!$A$1”為參考系，不偏移（偏移行和列為空），動態返回整個表格數據。COUNTA(Sheet1!$A:$A)用于獲取表格數據的行數，COUNTA(Sheet1!$1:$1)用于獲取表格數據的列數。它們獲取的結果是動態的，隨著表格行列數的增加或減少而變化。

?

第二步：更改數據源

3.單擊透視表上任意單元格，出現“數據透視表工具”。然后點擊“數據透視表工具”下方“分析”選項卡里的“更改數據源”。

?

4.在“更改數據透視表數據源”窗口，將“表/區域”修改成剛定義的名稱“數據”，點擊確定。

?

第三步：刷新同步

5.接下來在數據最后一行添加數據。

?

6.鼠標右擊透視表，選擇“刷新”命令，數據透視表就完成更新啦。

?

利用OFFSET函數我們實現了動態圖表，以及透視表與數據源聯動。