創建動態數據透視表的方法

  所謂動態數據透視表通常是指數據源可以自動變化的數據透視表。也就是說當數據源發生變化時,比如增加、刪除行或列,刷新數據透視表就可以自動擴展或縮小。下面我們來介紹一下常用的創建動態數據透視表的方法。

1、定義名稱

我們前面講過Offset函數的用法,可以指定返回區域的行高、列寬,我們把這兩個參數用函數來動態地賦值就可以得到一個動態的區域了。

在【公式】選項卡下面點擊「定義名稱」,在彈出的對話框中,「名稱」框中輸入Data(這個可以自己更改),「引用位置」框中輸入以下公式。

=OFFSET(數據源!$A$1,,,COUNTA(數據源!$A:$A),COUNTA(數據源!$1:$1))

公式中「數據源」是工作表名稱,COUNTA(數據源!$A:$A)用來獲取數據區域有多少行,COUNTA(數據源!$1:$1)用來獲取數據區域有多少列,當增加、刪除行或列的時候,這兩個函數返回更改後的數據區域的行數和列數,這樣就可以生成動態的區域了。

創建動態數據透視表的方法

接著在【插入】選項卡下面點擊「數據透視表」,在彈出的對話框中的「表/區域」框中直接輸入剛才定義的名稱Data,再點確定按鈕就創建了數據透視表,把字段加到數據透視表中就完成了。

創建動態數據透視表的方法

2、創建表格

選中數據區域中任意一個單元格,點擊【插入】選項卡下面的「表格」,Excel會自動識別最大的連續的數據區域。

創建動態數據透視表的方法

創建後的表格樣式如下。

創建動態數據透視表的方法

選中創建的表的任意一個單元格,在【插入】選項卡下面點擊「數據透視表」,在彈出的對話框中的「表/區域」框中會自動出現該「表」的名稱,這裡是「表1」,點擊「確定」就可以創建一個動態的數據透視表了。

這裡利用的是「表格」區域會自動擴展的原理。

創建動態數據透視表的方法

3、選取整列

如果不想麻煩,那麼你可以直接選取整列數據作為數據源,這樣當你增加行的時候,刷新數據透視表,也可以直接將數據包含進來。

但是要注意以下幾個問題:

不能自動擴展列,因為透視表要求每列必須有字段名稱,不能是空的;

日期時間類型的字段不能按照年、季度、月、日、小時、分、秒等自動組合;

數據透視表中會顯示一個空行。

4、使用SQL查詢

在【插入】選項卡下面點擊「數據透視表」,在彈出的對話框中選擇「使用外部數據源」。

創建動態數據透視表的方法

點擊「選擇鏈接」,在彈出的對話框中點擊左下角的「瀏覽更多」,選擇你的數據源文件,在彈出的對話框中選擇數據源中的工作表。

創建動態數據透視表的方法

這時就創建了一個數據透視表,但是數據源區域還不是動態的。

選中數據透視表,在【數據】選項卡下面點擊「屬性」按鈕。

創建動態數據透視表的方法

在彈出的對話框中點擊「定義」,在「命令文本」中輸入以下SQL語句。

select * from [SQL數據源$A1:I]

這裡「SQL數據源」是工作表名稱,A1時起始單元格,I是最後一列。I後面沒有數字表示不限定行數。當我們增加或減少行時都可以自動包含進來。

這樣就創建了一個動態的數據透視表。

不過這種方法最好能配合VBA去動態地更改數據源,否則將工作表移動到其他地方的話就失效了。這種方法通常可以用來只選擇需要的字段或創建高級的查詢時使用,比如合併多個工作表的數據,即使表格格式不完全一致也沒關係,我們只需要選擇需要的字段來創建合併查詢就可以了。這裡就不過多地展開了,僅當作拋磚引玉吧。

創建動態數據透視表的方法

–End–