高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

  作為職業表哥表姐,我們常常會遇到這樣的情景:快下班了,老板卻突然扔來一張excel表格(如圖1所示),讓你統計一下去年全年每個月每種產品的銷量以及每種產品在去年一年的銷量,以便供他做決策參考。

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖1

這類表格數據量一般都包含上千上萬的數據,思考一下,我們該如何做才能快速地得到統計結果並匯報給老板呢?有經驗的職場老鳥可能會想到使用排序+篩選的技巧。既先按日期排個升序,然後再篩選出每個月每種產品進行統計。如圖2所示:

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖2

顯然這是一種笨辦法,雖然可行,但是重復性勞力就是太多了。你想每種產品每個月你都要篩選統計一次,如果10種產品,一年有12個月,那麼最少要統計120次!說到這裡,可能有人會說,咱們就使用函數來統計好了,喂,你寫個公式給我瞧瞧呢?要一次性完成這樣的統計,寫函數一定不會簡單。就這個問題,我寫了個公式: =SUM((數據源!$A$2:$A$132&數據源!$B$2:$B$132=$A7&B$6)*數據源!$C$2:$C$132)。請問這個公式你看懂了嗎?因此這條路對於一般的用戶來說不是一個很好的選擇。

那麼難道這個問題就沒有更好的解決技巧嗎?答案當然是有,那就是數據透視表。使用這個工具,分分鐘搞定需要複雜函數才能搞定的統計問題。請來看看我的操作:

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]
  播放GIF

圖3

看看你的時間,我花了超沒有超過1分鐘解決問題?!數據透視表,這是一個多麼簡單,多麼強大,多麼高效的數據分析工具啊,怪不得在外企,張口閉口必談Pivot Table。這已經成了各類職業「表哥表姐」的最愛了。

你不會VBA和宏,沒關係;你不會圖表,沒關係;甚至你不會函數,也沒關係;但你絕對不能不會數據透視表!

要想不加班,就得靠它了。

30秒創建數據透視表的秘訣

只需30秒,輕輕鬆松創建一個數據透視表。我這樣說,得有兩個前提:一是你很清楚你要得到的結果是什麼;二是你的數據要符合規範,以防出錯。這兩點很有意思,如果你都不清楚你通過數據透視表得到什麼結果,那麼你做再多的操作都是無意義的;數據要符合規範:用來創建數據透視表的數據源一般都是一維的類似於數據庫一般的數據區域,列為字段,行為記錄。列標題為字段名(不能缺失),行的記錄最好不要有空格,也不要有重復記錄。因此呢,很多數據分析師在做數據分析之前,第一步要做就是規範數據,填充空行,刪除重復值等。好了,廢話不多說,大家看圖4所示的表格,我們需要統計一下每種產品每個月在不同倉庫的庫存量。

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖4

操作方法:

1.選中表格中的任意單元格(或者全選整個表格),一次單擊「插入」選項卡–數據透視表(最左邊),在彈出的「創建數據透視表」對話框中可以看到,「選擇一個表區域」中整個整個表格已經被完全選中,「選擇存放位置」處已經默認勾選了「新工作表」,我們也可以重新勾選下方的「現有工作表」然後單擊折疊按鈕為數據透視表指定存放位置,這裡就以默認的「新工作表」為例;

2.在「數據透視表字段」中選擇「月份」拖拽至下方的「行」,選擇「倉庫名稱」拖拽至「列」,拖拽「品名」至「列」,最後將「數量」拖拽至「值」。我們即可得到如圖5所示的效果。

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖5

1分鐘弄清楚數據透視表的基本布局

簡單學會了創建數據透視表,現在我們來對它的布局什麼做一個基本的了解(以圖6來做說明):

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖6

1.打開數據透視表的方法是什麼?

1)滑鼠單擊法:選中任意單元格,單擊「插入選項卡」–「數據透視表」,即可插入數據透視表;2)快捷鍵法:選中表格中任意單元格,按下快捷鍵Alt+N+V ,即可插入數據透視表,如果想通過數據透視表嚮導來創建數據透視表,則需要按下Alt+D+P。

2.數據透視表的基本組成部分和布局是什麼?

1)基本組成部分:如圖6所示,數據透視表一般由兩部分組成,左邊的數據透視表結果展示區以及右邊的數據透視表字段列表。在實際操作過程中,我們需要拖拽字段列表中的字段至其下方的行字段,列字段,值以及篩選字段等。2)數據透視表的布局:如圖7和圖8所示,一般為兩種布局,03版excel的布局我們稱為經典布局,07版以上的布局稱之為默認布局。如果excel文件為xls格式,無論我們用多高版本的excel軟體打開它,創建數據透視表時默認顯示的都是經典布局;如果excel文件格式不是xls, 那麼創建數據透視表時顯示的將是07版以上才有的默認布局。

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

經典布局高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

默認布局

可不可以將默認布局改為經典布局呢?答案當然是可以的,操作起來也非常簡單,將光標定位在數據透視表結果展示區,右鍵,數據透視表選項–顯示–勾選「經典數據透視表布局」–單擊確定即可。

3.數據透視表字段列表的布局

1)如何隱藏和顯示」數據透視表字段列表「?單擊數據透視表選項選項卡下的隱藏或者顯示;數據透視表中任意單元格單擊右鍵,然後再彈出的右鍵菜單中選擇最下方的」顯示和隱藏數據透視表「;選中透視表中的任意單元格,按下快捷鍵shift+F10+D。2)如何設置」數據透視表字段列表「的布局?方法非常簡單,直接單擊「字段列表」框右上角的下拉菜單選擇相應的布局即可。如下圖所示:

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]

圖9

3)如何操作「數據透視表字段列表」?拖拽,拖拽,拖拽,重要的事情說三遍。想怎麼拖就怎麼拖,想拖多少就拖多少,想拖多少次就可以拖多少次。

2個數據透視表經典應用技巧

1.如下圖的數據透視表,我們已經簡單知道每個月的各個品牌的銷售總數量,那麼如何設置才能得知,每個月的銷量占到全年的百分比呢?請看動畫演示:

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]
  播放GIF

單元格格式任意設置

2. 如何批量生成工作表?看看下面這個動畫你就再清楚不過了:

高手:這樣用EXCEL很傻啊!贈你3分鐘告別加班的利器 [一點資訊]
  播放GIF

批量生成工作表

今天寫的是Excel數據透視表的第一篇,就到這裡,陸陸續續會寫數據透視表的使用技巧,歡迎關注,也歡迎拍磚。

寫作不易,我的大部分文章均是熬夜的結果,如果能對你有所幫助,吾甚欣慰。期待得到你的喜歡,評論和打賞。你的支持是我創作的動力。如果需要轉載文章,請私信聯繫授權。