兩個實用案例,帶你認識大佬超愛的OFFSET!

excel教程2019-07-02 11:26:34

關注回覆[目錄]學習所有教程

全套Excel視頻教程,掃碼觀看


動態圖表

老闆看慣了你一直上報的平淡表格(如下),現在你突然展現給他的是可以動態查詢的圖表(如下),你説能否擊中老闆挑剔的心?能否讓老闆驚訝激賞?

你之前的彙報

你現在的彙報 

怎麼實現這種轉變?很簡單,就是做動態圖表。

動態圖表就是老闆選擇不同的區域,圖表就展示不同的數據。要實現需要三步。第一步做下拉菜單供老闆選擇;第二步做一個根據選擇,動態變化的數據區域;第三步根據動態數據區域插入圖表。

動態數據區域常用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實現。(不熟悉超級表的小夥伴可以查看往期教程:Excel教程:還在苦逼的調格式?有它,一鍵美化美化表格不是夢!)如圖,右側透視表是根據左側數據源插入的。 

現在我們需要實現在數據源更新後,透視表也能同步更新,如下: 


第一步:定義名稱


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函數我們實現了動態圖表,以及透視表與數據源聯動。趕緊操作操作吧!

掃一掃添加老師微信


在線諮詢Excel課程


Excel教程相關推薦



不是超級表做的下拉菜單,都是在浪費時間!
Excel教程:這個動態圖表這麼牛,老闆看了都忍不住點贊!
Excel教程:還在苦逼的調格式?有它,一鍵美化美化表格不是夢!

想要跟隨滴答老師全面系統學習Excel,不妨關注《一週Excel直通車》視頻課或者《Excel極速貫通班》。


《一週Excel直通車》視頻課

包含Excel技巧、函數公式、

數據透視表、圖表。

一次購買,永久學習。


最實用接地氣的Excel視頻課

《一週Excel直通車》

風趣易懂,快速高效,帶您7天學會Excel

38 節視頻大課

(已更新完畢,可永久學習)

理論+實操一應俱全


主講老師: 滴答


 

Excel技術大神,資深培訓師;

課程粉絲100萬+;

開發有《Excel小白脱白系列課》

        《Excel極速貫通班》。


原價299元

限時特價 99 元,隨時漲價

少喝兩杯咖啡,少吃兩袋零食

就能習得受用一生的Excel職場技能!


  長按下面二維碼立即購買學習


購課後,加客服微信:blwjymx3領取練習課件


讓工作提速百倍的「Excel極速貫通班」

↓ 點擊閲讀原文,可直接購買。

https://hk.wxwenku.com/d/201042693