.jpg)
立冬了，冷了！屋里沒(méi)暖氣？動(dòng)一動(dòng)，跺跺腳，搓搓手，人就暖和了。立冬了，又一年要完了！還沒(méi)漲薪？讓你的表動(dòng)一動(dòng)不同以往，同步更新，動(dòng)態(tài)展現(xiàn)，不但自己省事，老板也喜歡開“薪”……
第一動(dòng)：打動(dòng)，動(dòng)起來(lái)的圖表打動(dòng)老板
老板看慣了你一直上報(bào)的平淡表格（如下），現(xiàn)在你突然展現(xiàn)給他的是可以動(dòng)態(tài)查詢的圖表（如下），你說(shuō)能否擊中老板挑剔的心？能否讓老板驚訝激賞？
你現(xiàn)在的匯報(bào) ???????????????????? 你明天的匯報(bào)
??
怎么實(shí)現(xiàn)這種轉(zhuǎn)變？很簡(jiǎn)單，就是做動(dòng)態(tài)圖表。
動(dòng)態(tài)圖表就是老板選擇不同的區(qū)域，圖表就展示不同的數(shù)據(jù)。要實(shí)現(xiàn)就三步，一步做下拉菜單供老板選擇，一步做根據(jù)選擇動(dòng)態(tài)變化的數(shù)據(jù)區(qū)域，一步根據(jù)動(dòng)態(tài)數(shù)據(jù)區(qū)域插入圖表。
動(dòng)態(tài)數(shù)據(jù)區(qū)域常用VLOOKUP函數(shù)實(shí)現(xiàn)，但今天不走尋常路，我們利用OFFSET函數(shù)完成動(dòng)態(tài)數(shù)據(jù)區(qū)域。
第一步：做下拉選擇
1.選中J1單元格，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡下的“數(shù)據(jù)驗(yàn)證”。
2.在“數(shù)據(jù)驗(yàn)證”窗口下方的“設(shè)置”選項(xiàng)里，“允許”選擇“序列”，來(lái)源選擇五個(gè)銷售區(qū)域所在的單元格“=$A$2:$A$6”，點(diǎn)擊確定。
第二步：建立動(dòng)態(tài)更新的輔助數(shù)據(jù)區(qū)
3．接下來(lái)就是根據(jù)J1單元格的值來(lái)動(dòng)態(tài)更新圖表。J1選擇“北京區(qū)域”。在B7單元格輸入“=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)”。然后公式往右填充至G7單元格。這樣B7:G7單元格返回的就是北京區(qū)域1-6月的銷售額。
解析：
利用OFFSET以“B1”為參考系，偏移的行數(shù)為使用MATCH函數(shù)獲取$J$1在$A$2:$A$6的位置，偏移列數(shù)為0表示不偏移。如圖J1的值是“北京區(qū)域”，在$A$2:$A$6的位置為1，OFFSET返回的值為以“B1”為參考系，向下偏移一行的引用。這樣隨著選擇區(qū)域$J$1的不斷變化，B7:G7單元格就獲取到對(duì)應(yīng)區(qū)域的銷售數(shù)據(jù)。
4.然后設(shè)置平均線的數(shù)據(jù)，在B8單元格輸入“=AVERAGE($B$7:$G$7)”，獲取$B$7:$G$7的平均值。然后公式往右填充至G8單元格。如果選擇區(qū)域$J$1變化，則$B$7:$G$7變化，平均值也會(huì)隨之變化。
第三步：創(chuàng)建圖表
5.我們根據(jù)設(shè)置好的輔助行創(chuàng)建圖表。選擇標(biāo)題B1:G1和輔助行B7:G8區(qū)域點(diǎn)擊”插入”選項(xiàng)卡下的”圖表”組里的“二維柱形圖”。
6.在K1單元格輸入“=J1&”銷售數(shù)據(jù)””。
點(diǎn)擊圖表標(biāo)題框，在編輯欄輸入“=Sheet2!$K$1” ,這樣圖表標(biāo)題就和數(shù)據(jù)驗(yàn)證區(qū)域同步更新了。
7.點(diǎn)擊“圖表工具”下方“設(shè)計(jì)”選項(xiàng)卡下的“更改圖表類型”。
8.在“更改圖表類型”窗口，“所有圖表”選項(xiàng)下的“組合”，將平均值所在的系列修改成“折線圖”。
9.最后將圖表圖例刪除，把輔助數(shù)據(jù)B7:G8和K1單元格字體修改成白色不可見，就完成了。
第二動(dòng)：聯(lián)動(dòng)，讓透視表與數(shù)據(jù)源同步更新
??? 數(shù)據(jù)透視表要想實(shí)現(xiàn)和數(shù)據(jù)源同步的更新，之前給大家介紹過(guò)超級(jí)表可以實(shí)現(xiàn)。除此外，我們也可以用OFFSET實(shí)現(xiàn)。如圖，右側(cè)透視表是根據(jù)左側(cè)數(shù)據(jù)源插入的。
現(xiàn)在我們需要數(shù)據(jù)源更新后，透視表也能同步更新，如下：
第一步：定義名稱
1.點(diǎn)擊“公式”選項(xiàng)卡下的“定義的名稱”選項(xiàng)組里的“定義名稱”。
2.在“新建名稱”窗口，“名稱”欄輸入“數(shù)據(jù)”，“引用位置”輸入下列公式
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
解析:
“Sheet1”是數(shù)據(jù)所在的工作表。函數(shù)表示以“Sheet1!$A$1”為參考系，不偏移（偏移行和列為空），動(dòng)態(tài)返回整個(gè)表格數(shù)據(jù)。COUNTA(Sheet1!$A:$A)用于獲取表格數(shù)據(jù)的行數(shù)，COUNTA(Sheet1!$1:$1)用于獲取表格數(shù)據(jù)的列數(shù)。它們獲取的結(jié)果是動(dòng)態(tài)的，隨著表格行列數(shù)的增加或減少而變化。
第二步：更改數(shù)據(jù)源
3.單擊透視表上任意單元格，出現(xiàn)“數(shù)據(jù)透視表工具”。然后點(diǎn)擊“數(shù)據(jù)透視表工具”下方“分析”選項(xiàng)卡里的“更改數(shù)據(jù)源”。
4.在“更改數(shù)據(jù)透視表數(shù)據(jù)源”窗口，將“表/區(qū)域”修改成剛定義的名稱“數(shù)據(jù)”，點(diǎn)擊確定。
第三步：刷新同步
5.接下來(lái)在數(shù)據(jù)最后一行添加數(shù)據(jù)。
6.鼠標(biāo)右擊透視表，選擇“刷新”命令，數(shù)據(jù)透視表就完成更新啦。
利用Offset函數(shù)我們實(shí)現(xiàn)了兩“動(dòng)”，一動(dòng)，利用動(dòng)態(tài)圖表打動(dòng)老板，好開“薪”，二動(dòng)，透視表與數(shù)據(jù)源聯(lián)動(dòng)，自己更省事。Ok，快點(diǎn)讓你的表格動(dòng)起來(lái)吧！冬天來(lái)了，雖然避免不了冷手冷腳，但加加薪暖心就好！
相關(guān)學(xué)習(xí)推薦：excel教程
.png)
