Excel圖表學習之不拘一格OFFSET動態表

立冬了，冷了！屋裡沒暖氣？動一動，跺跺腳，搓搓手，人就暖和了。立冬了，又一年要結束了！還沒漲薪？讓你的表動一動不同以往，同步更新，動態展現，不但自己省事，老闆也喜歡開「薪」…

##第一個動：打動，動起來的圖表打動老闆

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

你現在的報告                  ‧很簡單，就是做動態圖表。

動態圖表就是老闆選擇不同的區域，圖表就顯示不同的數據。要實現就三步，一步做下拉式選單供老闆選擇，一步做根據選擇動態變化的資料區域，一步根據動態資料區域插入圖表。

動態資料區常用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變化，平均值也會隨之變化。

第三個步驟：建立圖表

#5.我們根據設定好的輔助行建立圖表。選擇標題B1:G1和輔助行B7:G8區域點選」插入」標籤下的」圖表」群組裡的「二維長條圖」。

6.在K1儲存格輸入「=J1&"銷售資料"」。

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

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」為參考系，不偏移（偏移行和列為空），動態傳回整個表格資料。 COUNTA(Sheet1!$A:$A)用於取得表格資料的行數，COUNTA(Sheet1!$1:$1)用於取得表格資料的列數。它們所獲得的結果是動態的，隨著表格行列數的增加或減少而變化。

第二步：變更資料來源

3.點選透視表上任一儲存格，出現「資料透視表工具」。然後點選「資料透視表工具」下方「分析」選項卡里的「更改資料來源」。

4.在“更改資料透視表資料來源”窗口，將“表格/區域”修改成剛定義的名稱“資料”，點選確定。

第三步：刷新同步

#5.接下來在資料最後一行新增資料。

6.滑鼠右鍵透視表，選擇「刷新」指令，資料透視表就完成更新啦。

利用Offset函數我們實現了兩“動”，一動，利用動態圖表打動老闆，好開“薪”，二動，透視表與數據源聯動，自己更省事。 Ok，快點讓你的表格動起來吧！冬天來了，雖然避免不了冷手冷腳，但加加薪暖心就好！

相關學習推薦：

excel教學

以上是Excel圖表學習之不拘一格OFFSET動態表的詳細內容。更多資訊請關注PHP中文網其他相關文章！