受夠加班煎熬，我整理出10條職場人士最常用的透視表技巧?。ㄏ缕?/h1>
?
作者：壹仟伍佰萬來源：部落窩教育發(fā)布時間：2019-11-29 17:06:31點擊：3508
版權說明： 原創(chuàng)作品，禁止轉載。
編按：
哈嘍，大家好！在上篇文章中，我們?yōu)榇蠹曳窒砹送敢暠淼那?條妙用，分別是合并同類項、按條件匯總數(shù)據(jù)、統(tǒng)計非重復數(shù)據(jù)、排名、批量創(chuàng)建表格，不知道大家都還記得嗎？那么今天我們書接上回，繼續(xù)為大家分享關于透視表的后5條妙用，趕緊來看看吧！（由于篇幅原因，文章分為上下兩篇，本篇為下篇。）
六、切片器
說到數(shù)據(jù)透視表，那就不得不提到它的另一個功能——切片器。它的主要作用就是實現(xiàn)動態(tài)篩選數(shù)據(jù)。生成透視表后，在“數(shù)據(jù)透視表工具”欄下的“分析”選項卡下，點擊“插入切片器”，接著設置要篩選的字段，即可生成切片器。那它和普通的篩選有啥區(qū)別呢？看下方動圖：
Gif1（普通篩選）
Gif2（切片器篩選）
是不是感覺這個篩選速度簡直不能比！
其實關于切片器的知識，我們在以往的文章中也介紹過，具體可以參考《光漲肉價，不漲工資？用excel做張老板最愛的自動化表格，讓你的工資翻一番！》。
七、總表拆分為分表
看到這個標題，小伙伴們有沒有覺得很熟悉呢？沒錯在前不久的文章《別再為拆分、合并工作表鬧心啦！最實用的7種方法，分分鐘搞定它?。ú鸱制?/span>》中，就為大家介紹過這一神奇的操作，還不清楚的小伙伴，趕緊戳鏈接看看吧~
八、多表操作
數(shù)據(jù)源如下：
圖3
1.提取出多表中所有商品種類
其實這就是一個提取不重復值的問題，而提取不重復值是數(shù)據(jù)透視表自帶的功能，只是在多表中提取不重復值應該怎么操作呢？很簡單!
步驟：
選中數(shù)據(jù)區(qū)域中的任意單元格,按快捷鍵ALT+D，釋放按鍵后再按P鍵啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導”對話框，選擇“多重合并計算數(shù)據(jù)區(qū)域”，并點擊“下一步”。
圖4
接著點擊“創(chuàng)建單頁字段”，并單擊“下一步”
圖5
在“選定區(qū)域”中，依次添加4個區(qū)域的數(shù)據(jù)，并點擊下一步，將數(shù)據(jù)透視表的顯示位置設置為“新工作表”，點擊“完成”。此時，所有商品的種類就一目了然了。
Gif6
2.分別統(tǒng)計多表中各類產品的總銷售額
根據(jù)上面的操作，在建立好的數(shù)據(jù)透視表中，可以發(fā)現(xiàn)，此時的透視表是以計數(shù)的方式，顯示的各項數(shù)據(jù)。
圖7
我們點擊“計數(shù)項：值”的下拉按鈕-“值字段設置”，將“值匯總方式”設置為“求和”，點擊“確定”。這樣一來，各個產品的總銷售額就計算出來了。
圖8
同樣，我們還可以通過設置不同的“值匯總方式”統(tǒng)計出多表中各類產品銷售額的平均值、最大、最小值等數(shù)據(jù)，這里就不一一介紹了，小伙伴們可以自己下來研究一下。
圖9
九、GETPIVOTDATA函數(shù)
看到這里，有的小伙伴可能會問小編了，“不是在說數(shù)據(jù)透視表嗎？怎么又扯到函數(shù)那旮沓去了。”其實，這個函數(shù)是透視表才有的函數(shù)，主要功能是返回透視表中的可見數(shù)據(jù)。需要在“數(shù)據(jù)透視表工具”欄下的“分析”選項卡下，點擊“選項”，勾選“生成GetPivotData”才能使用GETPIVOTDATA函數(shù)。
圖10
GETPIVOTDATA函數(shù)結構為：=GETPIVOTDATA("透視表的值字段名稱",數(shù)據(jù)透視表中任意單元格,"透視表的字段名稱1",條件1,"透視表的字段名稱2",條件2)。
（注意：除日期、數(shù)字和引用單元格外，參數(shù)都必須加上英文雙引號）
說了這么多，可能小伙伴們還不太明白，舉個例子，如下圖所示，我們需要計算出表中各地區(qū)對應商品的銷售額。
圖11
這道題的解法很多，可以挨個復制粘貼，也可以使用查找函數(shù)，但今天我們主要來說說使用GETPIVOTDATA函數(shù)如何解題。
首先在現(xiàn)有工作表中，插入數(shù)據(jù)透視表，將“銷售地區(qū)”和“商品”拖放入“行”字段下，將“銷售額”拖放入“值”字段下，此時數(shù)據(jù)基本上已經一目了然了，只需考慮如何將數(shù)據(jù)填入M4:M6區(qū)域中。
圖12
我們在M4單元格中輸入“=”，然后點擊它對應的值“I4”單元格，就可以自動生成公式：=GETPIVOTDATA("銷售額",$G$3,"銷售地區(qū)","北京","商品","吹風機")。
Gif13
但是下拉填充公式卻發(fā)現(xiàn)，公式的值并沒有變動，這是什么原因呢？其實這與該函數(shù)的特質有關，由于篇幅有限，本篇就不展開討論了，如果小伙伴們對這個函數(shù)感興趣，歡迎在評論區(qū)留言，我們將針對此函數(shù)單獨寫一篇教程。
這里我們需要稍稍改動一下公式，將第四參數(shù)"北京"替換為$K4，將第六參數(shù)"吹風機"替換為$L4，然后再下拉填充，就可以得到正確的數(shù)值啦~
圖14
十、快速刷新透視表
說了這么多，最后再為大家介紹一個刷新透視表的方法。有的小伙伴可能會問了，“直接點刷新不就好了嗎？這有啥可說的?！?span>nonono！直接點刷新只能刷新出在原本數(shù)據(jù)區(qū)域中修改后的數(shù)據(jù)。但如果在原本的數(shù)據(jù)區(qū)域中增加了新的行或列，就需要重新修改透視表的數(shù)據(jù)源，十分麻煩。
Gif15
有沒有什么好的解決方法呢？當然是有的，只需要把原本的表格設置為“超級表”，這樣一來，往后源數(shù)據(jù)新增的行或列只需要在透視表中，點擊刷新，就能自動出現(xiàn)，是不是很方便呢？
Gif16
補充：如果工作簿中有很多數(shù)據(jù)透視表，都需要刷新數(shù)據(jù)，此時可以添加“全部刷新”按鈕，批量刷新，這樣就不用挨個點擊“刷新”了。
步驟：
點擊“文件”-“選項”，在“快速訪問工具欄”的左側命令中，將“全部刷新”添加到右側工具欄中。
圖17
然后點擊表格左上角的“全部刷新”命令，工作簿中所有的透視表就全部被刷新了。
Gif18
說到這里，本系列教程就算告一段落了。當然，這10條技巧可能還沒有充分展示出數(shù)據(jù)透視表強大的功能，如果大家對這個系列感興趣，可以在評論區(qū)留言，我們會根據(jù)大家的需求，繼續(xù)推出這個系列教程。
本文配套的練習課件請加入QQ群：747953401下載。
做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！
掃下方二維碼關注公眾號，可隨時隨地學習Excel：
相關推薦：
透視表的10條妙用（上）《受夠加班煎熬，我整理出10條職場人士最常用的透視表技巧！（上）》
透視表刷新數(shù)據(jù)的方法《excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)的三種方法：VBA自動刷新透視表、超級表、現(xiàn)有連接》
用透視表制作賬齡分析表《別給姐說公式，用透視表一樣做好賬齡分析表?。ㄉ希?/span>》
編按：
哈嘍，大家好！在上篇文章中，我們?yōu)榇蠹曳窒砹送敢暠淼那?條妙用，分別是合并同類項、按條件匯總數(shù)據(jù)、統(tǒng)計非重復數(shù)據(jù)、排名、批量創(chuàng)建表格，不知道大家都還記得嗎？那么今天我們書接上回，繼續(xù)為大家分享關于透視表的后5條妙用，趕緊來看看吧！（由于篇幅原因，文章分為上下兩篇，本篇為下篇。）
六、切片器
說到數(shù)據(jù)透視表，那就不得不提到它的另一個功能——切片器。它的主要作用就是實現(xiàn)動態(tài)篩選數(shù)據(jù)。生成透視表后，在“數(shù)據(jù)透視表工具”欄下的“分析”選項卡下，點擊“插入切片器”，接著設置要篩選的字段，即可生成切片器。那它和普通的篩選有啥區(qū)別呢？看下方動圖：
Gif1（普通篩選）
Gif2（切片器篩選）
是不是感覺這個篩選速度簡直不能比！
其實關于切片器的知識，我們在以往的文章中也介紹過，具體可以參考《光漲肉價，不漲工資？用excel做張老板最愛的自動化表格，讓你的工資翻一番！》。
七、總表拆分為分表
看到這個標題，小伙伴們有沒有覺得很熟悉呢？沒錯在前不久的文章《別再為拆分、合并工作表鬧心啦！最實用的7種方法，分分鐘搞定它?。ú鸱制?/span>》中，就為大家介紹過這一神奇的操作，還不清楚的小伙伴，趕緊戳鏈接看看吧~
八、多表操作
數(shù)據(jù)源如下：
圖3
1.提取出多表中所有商品種類
其實這就是一個提取不重復值的問題，而提取不重復值是數(shù)據(jù)透視表自帶的功能，只是在多表中提取不重復值應該怎么操作呢？很簡單!
步驟：
選中數(shù)據(jù)區(qū)域中的任意單元格,按快捷鍵ALT+D，釋放按鍵后再按P鍵啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導”對話框，選擇“多重合并計算數(shù)據(jù)區(qū)域”，并點擊“下一步”。
圖4
接著點擊“創(chuàng)建單頁字段”，并單擊“下一步”
圖5
在“選定區(qū)域”中，依次添加4個區(qū)域的數(shù)據(jù)，并點擊下一步，將數(shù)據(jù)透視表的顯示位置設置為“新工作表”，點擊“完成”。此時，所有商品的種類就一目了然了。
Gif6
2.分別統(tǒng)計多表中各類產品的總銷售額
根據(jù)上面的操作，在建立好的數(shù)據(jù)透視表中，可以發(fā)現(xiàn)，此時的透視表是以計數(shù)的方式，顯示的各項數(shù)據(jù)。
圖7
我們點擊“計數(shù)項：值”的下拉按鈕-“值字段設置”，將“值匯總方式”設置為“求和”，點擊“確定”。這樣一來，各個產品的總銷售額就計算出來了。
圖8
同樣，我們還可以通過設置不同的“值匯總方式”統(tǒng)計出多表中各類產品銷售額的平均值、最大、最小值等數(shù)據(jù)，這里就不一一介紹了，小伙伴們可以自己下來研究一下。
圖9
九、GETPIVOTDATA函數(shù)
看到這里，有的小伙伴可能會問小編了，“不是在說數(shù)據(jù)透視表嗎？怎么又扯到函數(shù)那旮沓去了。”其實，這個函數(shù)是透視表才有的函數(shù)，主要功能是返回透視表中的可見數(shù)據(jù)。需要在“數(shù)據(jù)透視表工具”欄下的“分析”選項卡下，點擊“選項”，勾選“生成GetPivotData”才能使用GETPIVOTDATA函數(shù)。
圖10
GETPIVOTDATA函數(shù)結構為：=GETPIVOTDATA("透視表的值字段名稱",數(shù)據(jù)透視表中任意單元格,"透視表的字段名稱1",條件1,"透視表的字段名稱2",條件2)。
（注意：除日期、數(shù)字和引用單元格外，參數(shù)都必須加上英文雙引號）
說了這么多，可能小伙伴們還不太明白，舉個例子，如下圖所示，我們需要計算出表中各地區(qū)對應商品的銷售額。
圖11
這道題的解法很多，可以挨個復制粘貼，也可以使用查找函數(shù)，但今天我們主要來說說使用GETPIVOTDATA函數(shù)如何解題。
首先在現(xiàn)有工作表中，插入數(shù)據(jù)透視表，將“銷售地區(qū)”和“商品”拖放入“行”字段下，將“銷售額”拖放入“值”字段下，此時數(shù)據(jù)基本上已經一目了然了，只需考慮如何將數(shù)據(jù)填入M4:M6區(qū)域中。
圖12
我們在M4單元格中輸入“=”，然后點擊它對應的值“I4”單元格，就可以自動生成公式：=GETPIVOTDATA("銷售額",$G$3,"銷售地區(qū)","北京","商品","吹風機")。
Gif13
但是下拉填充公式卻發(fā)現(xiàn)，公式的值并沒有變動，這是什么原因呢？其實這與該函數(shù)的特質有關，由于篇幅有限，本篇就不展開討論了，如果小伙伴們對這個函數(shù)感興趣，歡迎在評論區(qū)留言，我們將針對此函數(shù)單獨寫一篇教程。
這里我們需要稍稍改動一下公式，將第四參數(shù)"北京"替換為$K4，將第六參數(shù)"吹風機"替換為$L4，然后再下拉填充，就可以得到正確的數(shù)值啦~
圖14
十、快速刷新透視表
說了這么多，最后再為大家介紹一個刷新透視表的方法。有的小伙伴可能會問了，“直接點刷新不就好了嗎？這有啥可說的?！?span>nonono！直接點刷新只能刷新出在原本數(shù)據(jù)區(qū)域中修改后的數(shù)據(jù)。但如果在原本的數(shù)據(jù)區(qū)域中增加了新的行或列，就需要重新修改透視表的數(shù)據(jù)源，十分麻煩。
Gif15
有沒有什么好的解決方法呢？當然是有的，只需要把原本的表格設置為“超級表”，這樣一來，往后源數(shù)據(jù)新增的行或列只需要在透視表中，點擊刷新，就能自動出現(xiàn)，是不是很方便呢？
Gif16
補充：如果工作簿中有很多數(shù)據(jù)透視表，都需要刷新數(shù)據(jù)，此時可以添加“全部刷新”按鈕，批量刷新，這樣就不用挨個點擊“刷新”了。
步驟：
點擊“文件”-“選項”，在“快速訪問工具欄”的左側命令中，將“全部刷新”添加到右側工具欄中。
圖17
然后點擊表格左上角的“全部刷新”命令，工作簿中所有的透視表就全部被刷新了。
Gif18
說到這里，本系列教程就算告一段落了。當然，這10條技巧可能還沒有充分展示出數(shù)據(jù)透視表強大的功能，如果大家對這個系列感興趣，可以在評論區(qū)留言，我們會根據(jù)大家的需求，繼續(xù)推出這個系列教程。
本文配套的練習課件請加入QQ群：747953401下載。
做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！
掃下方二維碼關注公眾號，可隨時隨地學習Excel：
相關推薦：
透視表的10條妙用（上）《受夠加班煎熬，我整理出10條職場人士最常用的透視表技巧！（上）》
透視表刷新數(shù)據(jù)的方法《excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)的三種方法：VBA自動刷新透視表、超級表、現(xiàn)有連接》
用透視表制作賬齡分析表《別給姐說公式，用透視表一樣做好賬齡分析表?。ㄉ希?/span>》