為什麼你的excel還不夠excellent?

  職場上,人人都很忙。但只有少數人能忙得「有效率」。

「表哥」、「表姐」們總告訴我們,20%的Excel技能就可以解決80%的工作問題。

不懂Excel的操作方法,別人可以用半天搞定100張以上的數據報表,你得手動計算一整周。

今天要就和大家分享下Microsoft Excel中一些最強大、最實用的操作技能?

學會這些快準狠的技巧,你一定也能完勝「表哥」、「表姐」們。

數據透視表

權重:50%+

重要程度:★★★★★

學習難度:★★★★

數據透視表是數據分析的神器,我們日常工作中要統計的各種報表都可以通過這一功能來做到。

作為一種交互式的圖表,它允許用戶根據需要對各類數據維度進行劃分,通過快捷地拖動各類數據維度,將他們進行不同的重組,做到我們想要的結果。

技巧1:拖拖拽拽」,快速製作統計報表,完成數據統計

根據你需要統計的數據維度和表格結構,「拖拖拽拽」,快速製作出你需要的統計報表,完成相應的數據統計。

操作步驟:選中原始數據表中的任意單元格—【插入】—【數據透視表】—【數據透視表字段及區間】—根據報表行列呈現需要,在字段列表中選定該字段並按住滑鼠左鍵拖放到下方的矩陣窗口中,數據透視表布局即完成。

為什麼你的excel還不夠excellent?

圖1:1-數據透視表創建(GIF)

技巧2:多種數值統計方式,輕鬆完成

數據透視表提供了求和、計數、最值、平均值、標準差、百分比等多種數值統計方式,你想要的結果它都可以呈現

操作步驟:需要幾種統計方式就拖入幾次計算【值字段設置】—【值顯示方式】—【百分比】。

為什麼你的excel還不夠excellent?
  圖2:2-數據透視:多數值計算(GIF)

技巧3:根據時間變化創建組,報表多元顯示

不只是日期,數據按照月份、季度、年度或者它們的組合展示,統統都可以。

操作步驟:選中任一日期數據,右鍵創建組,選中月份,按住CTRL,再選中年,可以隨意組合的。這個也可以進行年齡分段統計等問題。

為什麼你的excel還不夠excellent?
  圖3:3-數據透視-創建組-時間(GIF)

技巧4:城市組合成區域,只要手動創建一下

北京、天津、瀋陽,這些城市如何組合成【華北區】?老板就要的大區級的數據統計,我該怎麼辦?不要擔心,手動創建一下,瞬間完成

操作步驟:選中要組合的標籤(CTRL進行多選)—右鍵創建組—修改數據標籤。

為什麼你的excel還不夠excellent?
  圖4:4-數據透視-創建組-區域組合(GIF)

技巧5:數據透視表下,數據排序依然有效

在數據透視表下,將數據升序、降序或者你自己定義的順序排序?

操作步驟:選中要排序的任一一數據—右鍵選擇排序—選擇升序或者降序。如果是自定義排序,先通過【選項】嵌入自定義排序,然後再選擇升序或者降序操作。

為什麼你的excel還不夠excellent?

圖5:5-數據透視表:排序(GIF)

技巧6:數據也可篩選,想要什麼找出什麼

找出銷售量TOP3的明星銷售員?篩選一下,就是這麼簡單

操作步驟:選中任一一數據標籤—右鍵篩選—【前10個】—修改為按照銷售額最大的3個。

為什麼你的excel還不夠excellent?

圖6:6-數據透視表:篩選(GIF)

技巧7:數據變化了,刷新一下,數據透視表隨之而動

根據統計的維度,我們就可以製作數據透視表模板了。數據一有變化,我們就更新一下,統計結果馬上出來,連「拖拖拽拽」的功夫都省了,這就是自動化!

操作步驟:選中數據透視表中任一數據—右鍵點擊刷新。這個刷新操作是無法自動完成,手動一下,手動一下就好。

為什麼你的excel還不夠excellent?

圖7:7-數據透視表:手動刷新(GIF)

技巧8:總表分多表,利用篩選器,告別復制黏貼

從系統內導出的總表數據,如何根據我們的需要,比如銷售城市、銷售部門等標籤分成多個分表呢?數據透視表中的篩選器瞬間做到

操作步驟:將分表的數據標籤拖入數據透視表中的篩選器—數據透視表選項—顯示報表篩選頁—確定。

雙擊各個報表的匯總值,符合要求的原始數據就顯現了,amazing!

為什麼你的excel還不夠excellent?

圖8:8-數據透視表-篩選器-分頁(GIF)

技巧9:數據按照時間軸滾動,日程表來了

讓重要數據按照時間軸展現?怎麼可能做到得了。插入一個日程表,就足夠了。

操作步驟:選中數據透視表任一單元格—插入日程表,拖拉一下日程表下方的滾動軸,想看哪個月就看哪個月,想看哪幾個月就看哪幾個月。

為什麼你的excel還不夠excellent?

圖9:9-數據透視表—插入日程表(GIF)

技巧10:數據的遙控器,切片器

數據演示的時候,老板突然說要看看某個類別的數據,匆匆忙忙趕緊找。唉,能不能給我個數據遙控器,想看什麼,點擊個菜單。哈哈哈,切片器就是來滿足你這個要求了。

為什麼你的excel還不夠excellent?

圖10:10-數據透視表—插入切片器(GIF)

技巧11:切片器多報表鏈接,按一鍵即可掌控

切片器不僅能構建多個,而且一個切片器可以鏈接多個報表。同一個遙控器,按一鍵,控制的可是多個報表,數據展現輕鬆暢快。

操作步驟:選中切片器—右鍵選擇報表鏈接—選中你需要的數據透視表即可。

為什麼你的excel還不夠excellent?

圖11:11-數據透視表—切片器—多表鏈接(GIF)

技巧12:不只有表,還有圖形展示:數據透視圖

完成的數據報表不只是可以通過表格做到,直接還可以生成圖表。如果再配合一個切片器,圖表竟然動了起來。

操作步驟:選中數據透視表任一單元格—數據透視圖—選中你需要的圖表類型—結合切片器,圖表就成為了動態圖表。

為什麼你的excel還不夠excellent?

圖12:12-數據透視表—插入數據透視圖(GIF)

數據透視表的功能是不是很強大,如果再讓你完成100張數據統計報表,是不是工作效率瞬間倍增。

但在這裡,還是要給大家一個小貼士:

數據透視表好用,但原始數據一定要規範:數據標籤行只有一行、數據完整、不要匯總統計、不要合併單元格、數據格式規範。千萬要記住!

為什麼你的excel還不夠excellent?

VLOOKUP函數

權重:25%+

重要程度:★★★★★

學習難度:★★★★

在我們的工作中,基本每天都會遇到這樣的場景。比如從總表中,根據姓名匹配身份證號信息,根據考核等級確定獎金比例。

這些工作本質上都是匹配調用:匹配同樣的數據,調用出我們需要的數據。

要解決這個問題,最常用到的就是VLOOKUP函數。VLOOKUP函數是Excel中的大眾情人。我們曾經做過「如果只能選擇學習Excel中的一項功能,你會選擇哪個」的調查,VLOOKUP函數竟然高居第二位。

那麼VLOOKUP函數究竟如何使用呢?

VLOOKUP函數語法結構:VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP(查找值,查找範圍,返回的數值所在的列數,精確匹配還是模糊匹配)。

技巧13:VLOOKUP函數精確匹配,返回你需要的唯一數據

比如根據姓名匹配身份證號信息,對於這種匹配調用唯一的數據,就要用到VLOOKUP函數的精確匹配了。

操作步驟:查找範圍為絕對引用,可按快捷鍵F4,精確匹配下參數為0或FALSE。

注意事項:查找範圍和要返回的數值所在的列數都是要從查找值所在的列開始計算。

為什麼你的excel還不夠excellent?

圖13:13-VLOOKUP查詢調用精確匹配(GIF)

技巧14:VLOOKUP函數模糊匹配,返回你需要的區間數據

比如根據考核等級確定獎金比例,對於這種在區間範圍內匹配調用數據,就要用到VLOOKUP函數的模糊匹配了,這個功能完全可以替代掉IF函數的多層嵌套,再也不用為寫錯順序發愁。

操作步驟:查找範圍依然為絕對引用,可按快捷鍵F4,模糊匹配下參數為1或TRUE。

注意事項:等級表的編制要從小到大

為什麼你的excel還不夠excellent?

圖14:14-VLOOKUP查詢調用模糊匹配(GIF)

為什麼你的excel還不夠excellent?

圖表製作與可視化

權重:20%+

重要程度:★★★★

學習難度:★★★★

俗話說:能用數據顯示的,絕不用文字說明;能用圖形顯示的,絕不用數據說明。

技巧15:柱線組合圖:數據差異再大,依然清晰展示

為什麼你的excel還不夠excellent?
  圖15:15-圖表效果(JPG)

這種工作場景你一定會經常碰到:老板看的數據,既要匯總值,還要變化率,一張圖表上如何體現這兩類數據差異如此之大的圖表呢。

第一步:插入圖表,創建組合圖

操作步驟:全選數據—插入圖表—二維柱形圖—選中圖表後在【格式】選項卡下選中「系列引文影響力」—【設計】選項卡下更改圖表類型—系列引文影響力變為折線圖,同時勾選次坐標軸。

注意:對於數據差異比較大的數據,次坐標軸一定要學會用

為什麼你的excel還不夠excellent?
  圖16:16-插入圖表-組合圖(GIF)

第二步:添加圖表元素,添加數據標籤

操作步驟:選中圖表—點擊圖表右側的+號—勾選數據標籤項—選擇添加位置。

為什麼你的excel還不夠excellent?

圖17:17-圖表元素添加-添加數據標籤(GIF)

第三步:坐標軸設置與隱藏,柱線分離,網格線刪除

當添加完數據標籤後,坐標軸存在的意義就不大了。

操作步驟:先雙擊主坐標軸(左邊)—【坐標軸選項】起點200,間隔400,柱形圖相應下降,標籤位置選擇無(隱藏)。

雙擊次坐標軸(右邊)—【坐標軸選項】起點-10,折線圖相應上升標籤位置選擇無(隱藏)

選中網格線,直接按Delete鍵或者取消網格線勾選

為什麼你的excel還不夠excellent?

圖18:18-柱線分離—隱藏坐標軸—刪除網格線(GIF)

第四步:基本美化設置

操作步驟:柱形圖間距比例調整為75%-100%,折線圖數據標籤選擇三角形等。

注意:標題可以改得更加醒目,給出直接結論更好;添加數據來源,顯得更加規範。圖表中的字體可以使用微軟雅黑或者Arial Unicode MS,配色可以去模仿商業雜誌的配色,圖表就會看起來非常專業。

為什麼你的excel還不夠excellent?

圖19:19-基本美化-間距設置(GIF)

讓圖表嵌入到單元格或以諸如條形圖等方式展示,也是除了圖表之外,讓數據可視化的常用手段。這裡給大家帶來三種。

技巧16:條件格式,讓你的數據會說話

我們經常會這麼做:對於增長的數據我們標註綠色的上升箭頭,不變的變為平行的黃色箭頭,減少的標註紅色的下降箭頭。

這就是條件格式,它提供條形圖、色階、圖標集等很多種顯示方式,功能強大。

操作步驟:選擇數據—條件格式—條件格式類型選擇。

對於已經設置好的條件格式,我們也可以通過編輯進行再次設置。

為什麼你的excel還不夠excellent?

圖20:20-條件格式(GIF)

技巧17:REPT,函數也可以製作條形圖

不只是通過插入圖表設置條形圖,函數也可以噠。=REPT(text,number_times)。即REPT(「符號」,次數)

操作步驟:「|」位置輸入鍵在ENTER鍵上方(\鍵),重復次數可根據情況使用原值或擴大或減小,比如重復次數*2。

最後設置字體為Playbill,更改字體顏色即更改條形圖顏色。

為什麼你的excel還不夠excellent?

圖21:21-REPT函數,條形圖製作(GIF)

技巧18:迷你圖,讓數據更直觀

將柱狀圖、折線圖、盈虧圖等嵌入單元格裡有沒有很酷炫的感覺?插入迷你圖,就可以做到了,效果絕對棒棒噠!

操作步驟:全選數據—插入迷你圖—選擇迷你圖類型—選擇迷你圖插入位置。

為什麼你的excel還不夠excellent?

圖22:22-迷你圖(GIF)

心動不如行動,馬上學習起來吧!

更多Office教程

請持續關注LinkedIn專欄【Office魔法學院】:

北大碩士給大腦植入Excel病毒,工作效率提升了好幾倍

真正的Excel,其實你只用過1%

如何使用Excel「拖拽」技巧,10倍提升工作效率?

為什麼你的excel還不夠excellent?

本周四,領英系列職人紀錄片《我的職業日記》即將放出第二集,本集的主人公是辯論女王、奇葩之王、媒體創業者馬薇薇。

接下來,我們會把錄影頭對準一個萬千少女羨慕嫉妒的職業——時尚電商的美妝產品採購,也就是美妝買手。

每天接觸最新最潮的彩妝,「買買買」、「花花花」是TA們的日常,只為了和更多人分享美麗。

不論你是女生還是男生,你有什麼問題想要問美妝買手嗎?關於這份工作和這個行業,有哪些方面是你希望了解的呢?

請在評論區留下你的問題~

為什麼你的excel還不夠excellent?

LinkedIn招聘啦!在微信後台回復關鍵詞「招聘」,即可了解我們最近的職位信息。

LinkedIn歡迎你的加入!~~

本文作者北大小笨,人力資源經理、高級培訓師、國家職業生涯規劃師、職場辦公專家,網易雲課堂授權講師。其開發的課程《Excel高效實戰:提高桌面生產力》已上線。個人微信號北大小小笨(id:swbeidaxiaoben)

本文圖片由作者提供,為非商業用途使用,如因版權等有疑問,請於本文刊發30日內聯繫LinkedIn。

LinkedIn歡迎各類廣告品牌合作,發郵件至[email protected]獲取更多信息。

?2017 領英 保留所有權利