受夠加班煎熬,我整理出10條職場人士最常用的透視表技巧?。ㄏ缕?/h1>
?
作者:壹仟伍佰萬來源:部落窩教育發(fā)布時間:2019-11-29 17:06:31點(diǎn)擊:3904
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。
編按:
哈嘍,大家好!在上篇文章中,我們?yōu)榇蠹曳窒砹送敢暠淼那?條妙用,分別是合并同類項(xiàng)、按條件匯總數(shù)據(jù)、統(tǒng)計(jì)非重復(fù)數(shù)據(jù)、排名、批量創(chuàng)建表格,不知道大家都還記得嗎?那么今天我們書接上回,繼續(xù)為大家分享關(guān)于透視表的后5條妙用,趕緊來看看吧!(由于篇幅原因,文章分為上下兩篇,本篇為下篇。)
六、切片器
說到數(shù)據(jù)透視表,那就不得不提到它的另一個功能——切片器。它的主要作用就是實(shí)現(xiàn)動態(tài)篩選數(shù)據(jù)。生成透視表后,在“數(shù)據(jù)透視表工具”欄下的“分析”選項(xiàng)卡下,點(diǎn)擊“插入切片器”,接著設(shè)置要篩選的字段,即可生成切片器。那它和普通的篩選有啥區(qū)別呢?看下方動圖:

Gif1(普通篩選)

Gif2(切片器篩選)
是不是感覺這個篩選速度簡直不能比!
其實(shí)關(guān)于切片器的知識,我們在以往的文章中也介紹過,具體可以參考《光漲肉價,不漲工資?用excel做張老板最愛的自動化表格,讓你的工資翻一番!》。
七、總表拆分為分表
看到這個標(biāo)題,小伙伴們有沒有覺得很熟悉呢?沒錯在前不久的文章《別再為拆分、合并工作表鬧心啦!最實(shí)用的7種方法,分分鐘搞定它!(拆分篇)》中,就為大家介紹過這一神奇的操作,還不清楚的小伙伴,趕緊戳鏈接看看吧~
八、多表操作
數(shù)據(jù)源如下:

圖3
1.提取出多表中所有商品種類
其實(shí)這就是一個提取不重復(fù)值的問題,而提取不重復(fù)值是數(shù)據(jù)透視表自帶的功能,只是在多表中提取不重復(fù)值應(yīng)該怎么操作呢?很簡單!
步驟:
選中數(shù)據(jù)區(qū)域中的任意單元格,按快捷鍵ALT+D,釋放按鍵后再按P鍵啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂υ捒颍x擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”,并點(diǎn)擊“下一步”。

圖4
接著點(diǎn)擊“創(chuàng)建單頁字段”,并單擊“下一步”

圖5
在“選定區(qū)域”中,依次添加4個區(qū)域的數(shù)據(jù),并點(diǎn)擊下一步,將數(shù)據(jù)透視表的顯示位置設(shè)置為“新工作表”,點(diǎn)擊“完成”。此時,所有商品的種類就一目了然了。

Gif6
2.分別統(tǒng)計(jì)多表中各類產(chǎn)品的總銷售額
根據(jù)上面的操作,在建立好的數(shù)據(jù)透視表中,可以發(fā)現(xiàn),此時的透視表是以計(jì)數(shù)的方式,顯示的各項(xiàng)數(shù)據(jù)。

圖7
我們點(diǎn)擊“計(jì)數(shù)項(xiàng):值”的下拉按鈕-“值字段設(shè)置”,將“值匯總方式”設(shè)置為“求和”,點(diǎn)擊“確定”。這樣一來,各個產(chǎn)品的總銷售額就計(jì)算出來了。

圖8
同樣,我們還可以通過設(shè)置不同的“值匯總方式”統(tǒng)計(jì)出多表中各類產(chǎn)品銷售額的平均值、最大、最小值等數(shù)據(jù),這里就不一一介紹了,小伙伴們可以自己下來研究一下。

圖9
九、GETPIVOTDATA函數(shù)
看到這里,有的小伙伴可能會問小編了,“不是在說數(shù)據(jù)透視表嗎?怎么又扯到函數(shù)那旮沓去了。”其實(shí),這個函數(shù)是透視表才有的函數(shù),主要功能是返回透視表中的可見數(shù)據(jù)。需要在“數(shù)據(jù)透視表工具”欄下的“分析”選項(xiàng)卡下,點(diǎn)擊“選項(xiàng)”,勾選“生成GetPivotData”才能使用GETPIVOTDATA函數(shù)。

圖10
GETPIVOTDATA函數(shù)結(jié)構(gòu)為:=GETPIVOTDATA("透視表的值字段名稱",數(shù)據(jù)透視表中任意單元格,"透視表的字段名稱1",條件1,"透視表的字段名稱2",條件2)。
(注意:除日期、數(shù)字和引用單元格外,參數(shù)都必須加上英文雙引號)
說了這么多,可能小伙伴們還不太明白,舉個例子,如下圖所示,我們需要計(jì)算出表中各地區(qū)對應(yīng)商品的銷售額。

圖11
這道題的解法很多,可以挨個復(fù)制粘貼,也可以使用查找函數(shù),但今天我們主要來說說使用GETPIVOTDATA函數(shù)如何解題。
首先在現(xiàn)有工作表中,插入數(shù)據(jù)透視表,將“銷售地區(qū)”和“商品”拖放入“行”字段下,將“銷售額”拖放入“值”字段下,此時數(shù)據(jù)基本上已經(jīng)一目了然了,只需考慮如何將數(shù)據(jù)填入M4:M6區(qū)域中。

圖12
我們在M4單元格中輸入“=”,然后點(diǎn)擊它對應(yīng)的值“I4”單元格,就可以自動生成公式:=GETPIVOTDATA("銷售額",$G$3,"銷售地區(qū)","北京","商品","吹風(fēng)機(jī)")。

Gif13
但是下拉填充公式卻發(fā)現(xiàn),公式的值并沒有變動,這是什么原因呢?其實(shí)這與該函數(shù)的特質(zhì)有關(guān),由于篇幅有限,本篇就不展開討論了,如果小伙伴們對這個函數(shù)感興趣,歡迎在評論區(qū)留言,我們將針對此函數(shù)單獨(dú)寫一篇教程。
這里我們需要稍稍改動一下公式,將第四參數(shù)"北京"替換為$K4,將第六參數(shù)"吹風(fēng)機(jī)"替換為$L4,然后再下拉填充,就可以得到正確的數(shù)值啦~

圖14
十、快速刷新透視表
說了這么多,最后再為大家介紹一個刷新透視表的方法。有的小伙伴可能會問了,“直接點(diǎn)刷新不就好了嗎?這有啥可說的。”nonono!直接點(diǎn)刷新只能刷新出在原本數(shù)據(jù)區(qū)域中修改后的數(shù)據(jù)。但如果在原本的數(shù)據(jù)區(qū)域中增加了新的行或列,就需要重新修改透視表的數(shù)據(jù)源,十分麻煩。

Gif15
有沒有什么好的解決方法呢?當(dāng)然是有的,只需要把原本的表格設(shè)置為“超級表”,這樣一來,往后源數(shù)據(jù)新增的行或列只需要在透視表中,點(diǎn)擊刷新,就能自動出現(xiàn),是不是很方便呢?

Gif16
補(bǔ)充:如果工作簿中有很多數(shù)據(jù)透視表,都需要刷新數(shù)據(jù),此時可以添加“全部刷新”按鈕,批量刷新,這樣就不用挨個點(diǎn)擊“刷新”了。
步驟:
點(diǎn)擊“文件”-“選項(xiàng)”,在“快速訪問工具欄”的左側(cè)命令中,將“全部刷新”添加到右側(cè)工具欄中。

圖17
然后點(diǎn)擊表格左上角的“全部刷新”命令,工作簿中所有的透視表就全部被刷新了。

Gif18
說到這里,本系列教程就算告一段落了。當(dāng)然,這10條技巧可能還沒有充分展示出數(shù)據(jù)透視表強(qiáng)大的功能,如果大家對這個系列感興趣,可以在評論區(qū)留言,我們會根據(jù)大家的需求,繼續(xù)推出這個系列教程。
本文配套的練習(xí)課件請加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:

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