再不讓你的表動一動,年終加“薪”又是夢:
不拘一格OFFSET動態(tài)表
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-11-07 17:10:18點擊:3945
立冬了,冷了!屋里沒暖氣?動一動,跺跺腳,搓搓手,人就暖和了。立冬了,又一年要完了!還沒漲薪?讓你的表動一動不同以往,同步更新,動態(tài)展現,不但自己省事,老板也喜歡開“薪”……
第一動:打動,動起來的圖表打動老板
老板看慣了你一直上報的平淡表格(如下),現在你突然展現給他的是可以動態(tài)查詢的圖表(如下),你說能否擊中老板挑剔的心?能否讓老板驚訝激賞?
你現在的匯報 你明天的匯報
怎么實現這種轉變?很簡單,就是做動態(tài)圖表。
動態(tài)圖表就是老板選擇不同的區(qū)域,圖表就展示不同的數據。要實現就三步,一步做下拉菜單供老板選擇,一步做根據選擇動態(tài)變化的數據區(qū)域,一步根據動態(tài)數據區(qū)域插入圖表。
動態(tài)數據區(qū)域常用VLOOKUP函數實現,但今天不走尋常路,我們利用OFFSET函數完成動態(tài)數據區(qū)域。
第一步:做下拉選擇
1.選中J1單元格,點擊“數據”選項卡下的“數據驗證”。
2.在“數據驗證”窗口下方的“設置”選項里,“允許”選擇“序列”,來源選擇五個銷售區(qū)域所在的單元格“=$A$2:$A$6”,點擊確定。
第二步:建立動態(tài)更新的輔助數據區(qū)
3.接下來就是根據J1單元格的值來動態(tài)更新圖表。J1選擇“北京區(qū)域”。在B7單元格輸入“=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)”。然后公式往右填充至G7單元格。這樣B7:G7單元格返回的就是北京區(qū)域1-6月的銷售額。
解析:
利用OFFSET以“B1”為參考系,偏移的行數為使用MATCH函數獲取$J$1在$A$2:$A$6的位置,偏移列數為0表示不偏移。如圖J1的值是“北京區(qū)域”,在$A$2:$A$6的位置為1,OFFSET返回的值為以“B1”為參考系,向下偏移一行的引用。這樣隨著選擇區(qū)域$J$1的不斷變化,B7:G7單元格就獲取到對應區(qū)域的銷售數據。
4.然后設置平均線的數據,在B8單元格輸入“=AVERAGE($B$7:$G$7)”,獲取$B$7:$G$7的平均值。然后公式往右填充至G8單元格。如果選擇區(qū)域$J$1變化,則$B$7:$G$7變化,平均值也會隨之變化。
第三步:創(chuàng)建圖表
5.我們根據設置好的輔助行創(chuàng)建圖表。選擇標題B1:G1和輔助行B7:G8區(qū)域點擊”插入”選項卡下的”圖表”組里的“二維柱形圖”。
6.在K1單元格輸入“=J1&"銷售數據"”。
點擊圖表標題框,在編輯欄輸入“=Sheet2!$K$1” ,這樣圖表標題就和數據驗證區(qū)域同步更新了。
7.點擊“圖表工具”下方“設計”選項卡下的“更改圖表類型”。
8.在“更改圖表類型”窗口,“所有圖表”選項下的“組合”,將平均值所在的系列修改成“折線圖”。
9.最后將圖表圖例刪除,把輔助數據B7:G8和K1單元格字體修改成白色不可見,就完成了。
第二動:聯動,讓透視表與數據源同步更新
數據透視表要想實現和數據源同步的更新,之前給大家介紹過超級表可以實現。除此外,我們也可以用OFFSET實現。如圖,右側透視表是根據左側數據源插入的。
現在我們需要數據源更新后,透視表也能同步更新,如下:
第一步:定義名稱
1.點擊“公式”選項卡下的“定義的名稱”選項組里的“定義名稱”。
2.在“新建名稱”窗口,“名稱”欄輸入“數據”,“引用位置”輸入下列公式
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
解析:
“Sheet1”是數據所在的工作表。函數表示以“Sheet1!$A$1”為參考系,不偏移(偏移行和列為空),動態(tài)返回整個表格數據。COUNTA(Sheet1!$A:$A)用于獲取表格數據的行數,COUNTA(Sheet1!$1:$1)用于獲取表格數據的列數。它們獲取的結果是動態(tài)的,隨著表格行列數的增加或減少而變化。
第二步:更改數據源
3.單擊透視表上任意單元格,出現“數據透視表工具”。然后點擊“數據透視表工具”下方“分析”選項卡里的“更改數據源”。
4.在“更改數據透視表數據源”窗口,將“表/區(qū)域”修改成剛定義的名稱“數據”,點擊確定。
第三步:刷新同步
5.接下來在數據最后一行添加數據。
6.鼠標右擊透視表,選擇“刷新”命令,數據透視表就完成更新啦。
利用Offset函數我們實現了兩“動”,一動,利用動態(tài)圖表打動老板,好開“薪”,二動,透視表與數據源聯動,自己更省事。Ok,快點讓你的表格動起來吧!冬天來了,雖然避免不了冷手冷腳,但加加薪暖心就好!
素材下載請加QQ群:264539405。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
動態(tài)圖表入門《一看就會的excel動態(tài)圖表入門篇》
數據同步更新的其他做法《excel數據透視表動態(tài)刷新數據的三種方法》
帶控件的動態(tài)圖表《控件制作Excel條形圖》
動態(tài)甘特圖制作《Excel制作動態(tài)甘特圖大揭秘!》
圖片說明:
首圖運用了《老板求加薪》圖書封面素材。