自動調整數據源的數據透視表，你會做嗎？

Excel基礎學習園地

公眾號「Excel基礎學習園地」是一個免費發布Excel基礎知識、函數應用、操作技巧、學習方法等資訊的公眾號，請點擊上方「Excel基礎學習園地」添加關注，方便我們每天向您推送精彩資訊。

大家都知道數據透視表是個強大的工具，尤其在我們進行數據分析、統計的時候，非常方便。但是用久了也會覺得有點遺憾，每次增加數據的時候，數據源不能同步更新，就像下面這個動畫演示中的情況，在原有數據源進行修改後，透視表上點一下刷新，匯總結果會發生變化，但是在原有數據源後面增加一行後，刷新也沒有什麼作用。

數據源肯定是經常增加的，每次都要手動修改數據源還真的是件麻煩事，今天分享兩個方法，可以讓數據源自動更新，想學的趕緊往下看……

方法一：利用表格工具構造動態數據源

1：插入【表】實現數據源自動更新

先看一下操作演示：

在這個操作中，主要涉及到兩個重要的步驟：

步驟1：將數據源轉化為【表格】

點擊【表格】按鈕後出現如下提示：

可以看到數據源區域自動被選中，點擊確定完成。

成功創建表格後，在區域的右下角會有一個三角的標記，當我們在下一行錄入數據的時候，這個標記會自動擴展：

【表格】這個功能是Excel裡非常有用並且智能的一個工具，有想了解詳細內容的話可以看下之前發過的教程。

步驟2：使用表格作為透視表的數據源

當我們成功轉換表格後，需要使用表格作為透視表的數據源，可以刪掉已有的透視表重新添加，也可以通過更改數據源進行修改：

點擊更改數據源後，界面如下圖：

此時數據源是以區域的形式存在，重新選擇區域後，這裡會自動變成表名：

直接點擊確定，今後再添加數據源後就可以直接刷新了。

以上是通過【表格】這個功能實現的自動更新數據源，下面我們介紹使用

「名稱」這個功能來實現自動更新數據源的方法。

方法二：利用公式和名稱構造動態數據源

先看一下效果演示：

法2：利用「名稱」實現數據源自動更新

在這個操作中，我們沒有使用【表格】，透視表也可以自動調整數據源，其中的奧秘何在？

我們打開數據透視表的數據源設置，可以看到此時既不是區域，也不是表名，就是三個字-數據源。

這個數據源又是個什麼東東呢？

其實這就是一個「名稱」。

在公式選項組中，有個定義名稱的功能，也可以使用名稱管理器查看我們已經定義好的名稱。

在定義名稱的時候，需要填入兩個信息：名稱和引用位置。

名稱就是給這個引用位置起的名字，例如這裡可以叫數據源，也可以起成其他的名字，當然在透視表的數據源中也要對應修改才行。

下面引用位置中用到的公式才是關鍵部分：

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

通常構造動態區域會用到offset或者indirect這兩個函數，今天我們用offset作介紹。

在之前的文章中多次介紹過offset這個函數，一共有五個參數構成，具體含義是：

=OFFSET(起始位置，行偏移量，列偏移量，區域高度，區域寬度)

在本例中，我們的數據源是從A1開始，因此第一參數為Sheet1!$A$1，這裡是加了工作表的名稱和絕對引用；因為A1這個單元格就是要包含在數據區域中的，因此不需要偏移量，第二參數和第三參數都是0；

數據源只有3列，所以區域寬度是3，因此最後一個參數就是3；

關鍵在第四個參數的設定，也就是區域高度，為了可以自動確定區域的高度，我們用到了一個計數函數counta，這個函數的功能很簡單，就是統計給定區域中有數據的單元格個數。在這個公式裡，區域給的是A列，當我們在A列增加數據時，counta就會自動確定出有多少行數據，因此offset得到區域就會自動調整了。

小結：對於新手來說，使用方法1可能比較容易掌握，但是方法2才是重點，這個思路經常會用到一些動態圖表，動態數據源的有效性設置等等地方，建議感興趣的朋友要把offset這個函數好好研究一下了。

在Excel中，有很多功能也許你還未曾接觸過，例如今天用到的表格和名稱，也可能有很多好用的函數你還不知道怎麼用。

這些實用的技能，在老菜鳥的班都能學到，只要你肯學，我們就能承諾包教包會。