關於數據透視表你不得不知道的那些事兒

  數據透視表是十分強大的數據匯總工具之一,匯總數據方便快捷,用滑鼠簡單幾步操作就可以完成數據匯總。今天我們來看看數據透視表有哪些方便實用的設置。

我們以下面的數據源來做數據透視並講解常用的設置。

關於數據透視表你不得不知道的那些事兒

1、以表格形式顯示

創建完數據透視表後,默認是以壓縮形式顯示的,多個字段顯示在一列。我們可以改成以表格形式顯示,使每個字段占據一列。

在【設計】選項卡的「報表布局」下拉列表中點擊「以表格形式顯示」。

關於數據透視表你不得不知道的那些事兒

2、重復所有項目標籤

剛創建的數據透視表中重復的項目顯示空白,我們可以把所有空白都填充上數據。在【設計】選項卡的「報表布局」下拉列表中點擊「重復所有項目標籤」。

3、刪除「總計」、「分類匯總」

在【設計】選項卡中點擊「分類匯總」下拉列表中的「不顯示分類匯總」,也可以在透視表的該字段上點擊右鍵,在彈出菜單中取消勾選「分類匯總***」。

關於數據透視表你不得不知道的那些事兒
  關於數據透視表你不得不知道的那些事兒

在【設計】選項卡中點擊「總計」下拉列表中的「對行和列禁用」,或者滑鼠放到最後一行的「總計」單元格,點擊右鍵,在彈出菜單中點擊「刪除總計」。

關於數據透視表你不得不知道的那些事兒
  關於數據透視表你不得不知道的那些事兒

4、取消「生成GetPivotData」

在啟用「生成GetPivotData」時,我們在其他單元格編輯公式時使用滑鼠點選透視表的單元格,返回的是用GetPivotData函數生成的結果,而不是單元格地址。

關於數據透視表你不得不知道的那些事兒

我們在復制拖動公式時,公式中的引用不能像普通單元格的地址那樣變化。我們取消勾選「生成GetPivotData」,就可以像普通單元格那樣,在公式中點選透視表的單元格時就返回單元格地址了。

在【分析】選項卡下面,取消勾選數據透視表「選項」中的「生成GetPivotData」。

關於數據透視表你不得不知道的那些事兒

5、替換匯總字段中的「求和項:」、「計數項:」等

對於匯總字段中自動添加的「求和項:」、「計數項:」等前綴字符,我們可以用普通的查找、替換的方式來替換,不過,這些前綴不能替換成空字符,但是可以替換成一個空格,這樣可以節省列字段的寬度,外觀上幾乎看不出來,而且可以消除原始的、雜亂無章的感覺。

6、取消「更新時自動調整列寬」

默認情況下,刷新數據透視表時,它會自動調整列寬。有時候我們自己調整了透視表的列寬,並且希望始終保持這個列寬,我們可以讓透視表不自動調整。

在透視表上點擊右鍵,在彈出菜單中點擊「數據透視表」選項,在彈出的對話框中的「布局和格式」選項卡中取消勾選「更新時自動調整列寬」。

關於數據透視表你不得不知道的那些事兒

7、空單元格顯示0,錯誤值顯示0

還是上面的數據透視表選項中,勾選「對於錯誤值,顯示」,在後面的框中輸入0;勾選「對於空單元格,顯示」,在後面的框中輸入0。

8、篩選字段橫向顯示

在數據透視表選項中,將「在報表篩選區域顯示篩選字段」改成「水平並排」就可以了。

關於數據透視表你不得不知道的那些事兒

9、從字段值中清除已經刪除的項

在數據透視表選項的「數據」選項卡中將「每個字段保留的項數」改成「無」,然後再刷新數據透視表,這樣我們從數據源中刪除的項目就不會再出現在這個字段中了,我們在透視表中點擊下拉篩選按鈕可以看到,刪除的項已經不見了。

關於數據透視表你不得不知道的那些事兒

10、打開文件時刷新數據

看上圖,在數據透視表選項的「數據」選項卡中勾選「打開文件時刷新數據」。

11、使用經典數據透視表布局

在數據透視表選項的「顯示」選項卡中勾選「經典數據透視表布局」,我們就可以將字段直接拖放到透視表中相應的區域上了。

關於數據透視表你不得不知道的那些事兒

啟用了「經典數據透視表布局」後,透視表的每個區域都顯示藍色的粗線框。

關於數據透視表你不得不知道的那些事兒

12、啟用選定內容

在【分析】選項卡中的「操作」選項中點擊「選擇」下拉菜單中的「啟用選定內容」。

在啟用狀態下,我們把滑鼠放到透視表的某一行或某一列上,滑鼠指針變成向右或向下的箭頭,這時候點擊滑鼠就可以選中所有的相同項了。

關於數據透視表你不得不知道的那些事兒

如下所示,我們把滑鼠放到任意一個「大米」上,點擊滑鼠,則所有「大米」就被選中了,這時就可以快速地設置格式或者做其他操作了。

關於數據透視表你不得不知道的那些事兒

13、手動組合

選中要組合的項目,點擊右鍵菜單中的「創建組」就可以手動組合了。

如果是日期字段,點擊「創建組」會出現以下對話框,我們可以設置起始日期和終止日期,可以按照各種方式來組合日期字段。

如果是日期字段,但是沒有出現以下對話框,那麼你需要檢查日期字段是否有以下問題:

是否有空白單元格

是否有文本或其他非日期類型的內容

是否有看起來像日期,其實是文本格式的內容

是否是整列引用了數據源來生成數據透視表

數據源區域是否已被刪除

關於數據透視表你不得不知道的那些事兒

14、設置數據透視表樣式

在【設計】選項卡下面的「數據透視表樣式」中選擇一種即可快速應用樣式,也可以自定義樣式,方便下次使用。

15、快速展開、折疊整個字段

在需要展開或折疊的字段上點右鍵,在彈出的菜單中點擊「展開/折疊」下的「展開整個字段」、「折疊整個字段」。

關於數據透視表你不得不知道的那些事兒

16、篩選前幾項數據

在相應的字段上點右鍵,在彈出菜單中點擊’篩選’下面的「前10個」.

關於數據透視表你不得不知道的那些事兒

我們可以自己設定最大或最小的前幾項、篩選的數目、按照項或百分比篩選、設定篩選的依據等。

關於數據透視表你不得不知道的那些事兒

也可以按照「標籤篩選」或「值篩選」,大家可以嘗試一下。

— End —