當前位置:首頁 > 科技 > 正文

為啥透視表老有問題?看完這篇終于找到答案了!

每一樣東西在使用的過程中都會出現各種各樣的問題,出現問題不可怕,隻要找到病根,就能解決掉問題。

對于大多數人而言,隻要看鍊接的這篇文章就能解決掉一半問題:90%的女神用了透視表,都會出現這些問題!

剩下的一半問題,看完這篇也幾乎能解決。

1.兼容模式闖的禍

都是用Excel2013以上版本創建的數據透視表,怎麼兩個數據透視表的求和項位置怎麼不一樣?

左邊這種情況是在兼容模式下創建的數據透視表,可以看到兼容模式4個字。

單擊“文件”選項卡,可以看到信息這裡提示兼容模式,單擊“轉換”,在彈出的警告對話框,單擊“确定”按鈕。轉換成功後再重新創建數據透視表就能得到右邊那種效果。

兼容模式下會顯示經典數據透視表布局,也就是可以直接将字段拖過去,而正常模式直接是拉到“數據透視表字段”對話框操作。

除此之外,屬于高版本的功能:插入切片器、插入日程表等功能卻顯示灰色,無法使用,怎麼回事?

這種問題同樣是工作簿是兼容模式情況下的弊端,在兼容模式下很多功能都會被限制使用。

這裡除了可以使用“轉換”這個功能,其實還可以通過将工作簿另存為高版本的。

單擊“文件”選項卡,再單擊“另存為”,選擇儲存位置,保存類型選擇:Excel 工作簿(*.xlsx)。

還有一點需要說明,就是用高版本的新功能制作數據透視表,在低版本是沒法看到的,低版本不支持。

2.标題缺失與雙行标題都不行

在創建數據透視表時,彈出“數據透視表字段名無效”的警告對話框,這是什麼原因?

數據透視表字段名無效,也就是數據源的标題缺失,現在單位沒寫,隻要将單位這個标題寫上就行。

就是原始的數據源,裡面出現了合并單元格,導緻了部分内容沒有标題。在需要進行彙總的表格,最好不用使用合并單元格,這樣會造成一系列麻煩。

在第3行插入一行,将所有标題都寫上。

在創建數據透視表時,區域選擇從第3行開始以後的區域。

在完成了統計以後,可以将插入的這一行隐藏起來,這樣看起來就跟以前的表格一樣,但依然可以繼續統計而不影響。

3.真假财務部,看似相同實則不同

創建透視表後,居然出現了兩個财務部,而沒有合并起來統計,怎麼回事呢?

出現這種情況,最大的可能就是在錄入數據的時候不小心錄入空格,這是很常有的事兒。

返回到數據源工作表,Ctrl+H調出“查找和替換”對話框,在查找内容輸入一個空格,單擊“全部替換”按鈕,這時可以看到一個對話框,提示完成2處替換,再單擊“确定”按鈕。也就說數據源存在了2個空格,符合我們的猜測。

再重新回到數據透視表工作表,右擊選擇“刷新”,财務部就已經進行了合并彙總了。

類似還有不同格式的數據,有的是文本格式,有的是常規格式,這樣數據透視表統計的時候就會出現2個44030。

針對這種情況,不能直接采用将單元格設置為文本這種方法,可以采用分列功能進行統一格式。

選擇區域,切換到“數據”選項卡,單擊“分列”,在彈出的“文本分列向導”對話框,保持默認不變,連續單擊兩次“下一步”按鈕。

選擇“文本”格式,單擊“完成”按鈕。

對數據透視表進行刷新,就恢複了正常。

4.數據透視表選項中那些好用卻被忽視的功能

01取消更新時自動調整列寬

設置好的表格,一旦數據源更新,刷新數據透視表,列寬就會自動改變,這樣一來前面的列寬設置就沒用了,又得重新設置。如何才能讓列寬始終保持不變?

右擊選擇“數據透視表選項”,在彈出來的對話框單擊“布局和格式”,取消勾選“更新時自動調整列寬”,再單擊“确定”按鈕。這樣以後不管怎麼刷新,數據透視表的列寬都不會再改變。

02對于錯誤值或對于空單元格的顯示

有的時候數據透視表會出現一些錯誤值,為了美觀,可以在“布局和格式”,勾選“對于錯誤值,顯示”,後面的内容什麼都不寫,就代表顯示空白。

03設置打印标題

我們還經常需要打印标題,這時可以在“打印”勾選“打印标題”。

5.一個職業一個表格

是某培訓班的學員資料,現在要根據職業,把每個職業的人員信息分成多個表格,一個職業一個表格,該如何處理?

像這種分成多個表格的,水平高的都會采用VBA代碼,但對于普通人而言,借助數據透視表,也能輕松實現。

STEP 01 創建數據透視表,将職業拖到篩選器,其他字段依次勾選。

STEP 02 單擊“設計”選項卡,選擇“報表布局”,單擊“以表格格式顯示”。

STEP 03 選擇“分類彙總”,單擊“不顯示分類彙總”。

STEP 04 單擊“分析”選項卡,選擇“選項”,單擊“顯示報表篩選頁”,在彈出的“顯示報表篩選頁”對話框,單擊“确定”按鈕。

通過上面4步,生成4個表格,每個職業一個明細表。

6.在每個項目後面插入分頁符

一個職業一個表格,如果隻是為實現打印方便,其實還有另外一種做法,就是在每個項目後面插入分頁符。

STEP 01 先将數據透視表的布局略作改變。

STEP 02 單擊職業這個單元格,右擊選擇“字段設置”。

STEP 03 單擊“布局和打印”,勾選“每項後面插入分頁符”,單擊“确定”按鈕。

STEP 04 單擊職業這個單元格,右擊選擇“數據透視表選項”。

STEP 05 單擊“打印”選項卡,勾選“設置打印标題”,單擊“确定”按鈕。

STEP 06 借助快捷鍵Ctrl+P調出打印預覽,對打印的頁面進行調整,最終分成4頁,每一頁都包含标題。

7.将數據源中沒有的項目顯示出來

用日期組合的時候,隻顯示2個月份,如何在數據透視表顯示所有月份?

右擊選擇“字段設置”,單擊“布局和打印”,勾選“顯示無數據的項目”,單擊“确定”按鈕。

這樣所有月份就都顯示出來,不過多了2個不是月份的,有點礙眼,直接篩選的時候,取消勾選這2個就行。

8.數據透視表之再透視

這是用數據透視表統計出來的結果,現在想統計100、200、300這些數量出現的次數,該如何處理?

我們都知道,數據透視表可以對數據源進行彙總,其實數據透視表還可以對另外的數據透視表進行彙總,也就是數據再透視。

STEP 01 創建數據透視表,選擇原始數據透視表不包含總計的區域,将數據透視表的位置放在D3,單擊“确定”按鈕。

STEP 02 将求和項:數量拖到行字段,行标簽拖到值字段,也就是說将原始數據透視的字段位置轉換過來。

再對字段進行重命名,效果會更好。

數據再透視,是轉換一個思維處理事情,正難則反。

推薦:知道這個透視表不聯動功能的人,都已經老了。。。

上文:用了LOOKUP(1,0/,.....)的這個套路,表格好卡好卡,怎麼辦?

以上那些問題你曾經遇到過哪些,還遇到過哪些新問題?

作者:盧子,清華暢銷書作者,《Excel效率手冊 早做完,不加班》系列叢書創始人,個人公衆号:Excel不加班(ID:Excelbujiaban)

你可能想看:

有話要說...

取消
掃碼支持 支付碼