數(shù)據(jù)透視表
權(quán)重：50%+
重要程度：★★★★★
學(xué)習(xí)難度：★★★★
數(shù)據(jù)透視表是數(shù)據(jù)分析的神器，我們?nèi)粘９ぷ髦幸y(tǒng)計(jì)的各種報(bào)表都可以通過這一功能來實(shí)現(xiàn)。
作為一種交互式的圖表，它允許用戶根據(jù)需要對各類數(shù)據(jù)維度進(jìn)行劃分，通過快捷地拖動各類數(shù)據(jù)維度，將他們進(jìn)行不同的重組，實(shí)現(xiàn)我們想要的結(jié)果。
技巧1:拖拖拽拽”，快速制作統(tǒng)計(jì)報(bào)表，完成數(shù)據(jù)統(tǒng)計(jì)
根據(jù)你需要統(tǒng)計(jì)的數(shù)據(jù)維度和表格結(jié)構(gòu)，“拖拖拽拽”，快速制作出你需要的統(tǒng)計(jì)報(bào)表，完成相應(yīng)的數(shù)據(jù)統(tǒng)計(jì)。
操作步驟：選中原始數(shù)據(jù)表中的任意單元格—【插入】—【數(shù)據(jù)透視表】—【數(shù)據(jù)透視表字段及區(qū)間】—根據(jù)報(bào)表行列呈現(xiàn)需要，在字段列表中選定該字段并按住鼠標(biāo)左鍵拖放到下方的矩陣窗口中，數(shù)據(jù)透視表布局即完成。
圖1:1-數(shù)據(jù)透視表創(chuàng)建（GIF）
技巧2:多種數(shù)值統(tǒng)計(jì)方式，輕松完成
數(shù)據(jù)透視表提供了求和、計(jì)數(shù)、最值、平均值、標(biāo)準(zhǔn)差、百分比等多種數(shù)值統(tǒng)計(jì)方式，你想要的結(jié)果它都可以呈現(xiàn)
操作步驟：需要幾種統(tǒng)計(jì)方式就拖入幾次計(jì)算【值字段設(shè)置】—【值顯示方式】—【百分比】。
圖2:2-數(shù)據(jù)透視：多數(shù)值計(jì)算（GIF）
技巧3:根據(jù)時(shí)間變化創(chuàng)建組，報(bào)表多元顯示
不只是日期，數(shù)據(jù)按照月份、季度、年度或者它們的組合展示，統(tǒng)統(tǒng)都可以。
操作步驟：選中任一日期數(shù)據(jù)，右鍵創(chuàng)建組，選中月份，按住CTRL,再選中年，可以隨意組合的。這個(gè)也可以進(jìn)行年齡分段統(tǒng)計(jì)等問題。
圖3:3-數(shù)據(jù)透視-創(chuàng)建組-時(shí)間（GIF）
技巧4：城市組合成區(qū)域，只要手動創(chuàng)建一下
北京、天津、沈陽，這些城市如何組合成【華北區(qū)】？老板就要的大區(qū)級的數(shù)據(jù)統(tǒng)計(jì)，我該怎么辦？不要擔(dān)心，手動創(chuàng)建一下，瞬間完成
操作步驟：選中要組合的標(biāo)簽（CTRL進(jìn)行多選）—右鍵創(chuàng)建組—修改數(shù)據(jù)標(biāo)簽。
圖4:4-數(shù)據(jù)透視-創(chuàng)建組-區(qū)域組合（GIF）
技巧5:數(shù)據(jù)透視表下，數(shù)據(jù)排序依然有效
在數(shù)據(jù)透視表下，將數(shù)據(jù)升序、降序或者你自己定義的順序排序？
操作步驟：選中要排序的任一一數(shù)據(jù)—右鍵選擇排序—選擇升序或者降序。如果是自定義排序，先通過【選項(xiàng)】嵌入自定義排序，然后再選擇升序或者降序操作。
圖5:5-數(shù)據(jù)透視表：排序（GIF）
技巧6:數(shù)據(jù)也可篩選，想要什么找出什么
找出銷售量TOP3的明星銷售員？篩選一下，就是這么簡單
操作步驟：選中任一一數(shù)據(jù)標(biāo)簽—右鍵篩選—【前10個(gè)】—修改為按照銷售額最大的3個(gè)。
圖6:6-數(shù)據(jù)透視表：篩選（GIF）
技巧7:數(shù)據(jù)變化了，刷新一下，數(shù)據(jù)透視表隨之而動
根據(jù)統(tǒng)計(jì)的維度，我們就可以制作數(shù)據(jù)透視表模板了。數(shù)據(jù)一有變化，我們就更新一下，統(tǒng)計(jì)結(jié)果馬上出來，連“拖拖拽拽”的功夫都省了，這就是自動化！
操作步驟：選中數(shù)據(jù)透視表中任一數(shù)據(jù)—右鍵點(diǎn)擊刷新。這個(gè)刷新操作是無法自動完成，手動一下，手動一下就好。
圖7:7-數(shù)據(jù)透視表：手動刷新（GIF）
技巧8:總表分多表，利用篩選器，告別復(fù)制粘貼
從系統(tǒng)內(nèi)導(dǎo)出的總表數(shù)據(jù)，如何根據(jù)我們的需要，比如銷售城市、銷售部門等標(biāo)簽分成多個(gè)分表呢？數(shù)據(jù)透視表中的篩選器瞬間實(shí)現(xiàn)
操作步驟：將分表的數(shù)據(jù)標(biāo)簽拖入數(shù)據(jù)透視表中的篩選器—數(shù)據(jù)透視表選項(xiàng)—顯示報(bào)表篩選頁—確定。
雙擊各個(gè)報(bào)表的匯總值，符合要求的原始數(shù)據(jù)就顯現(xiàn)了，amazing！
圖8:8-數(shù)據(jù)透視表-篩選器-分頁（GIF）
技巧9：數(shù)據(jù)按照時(shí)間軸滾動，日程表來了
讓重要數(shù)據(jù)按照時(shí)間軸展現(xiàn)？怎么可能實(shí)現(xiàn)得了。插入一個(gè)日程表，就足夠了。
操作步驟：選中數(shù)據(jù)透視表任一單元格—插入日程表，拖拉一下日程表下方的滾動軸，想看哪個(gè)月就看哪個(gè)月，想看哪幾個(gè)月就看哪幾個(gè)月。
圖9:9-數(shù)據(jù)透視表—插入日程表（GIF）
技巧10:數(shù)據(jù)的遙控器，切片器
數(shù)據(jù)演示的時(shí)候，老板突然說要看看某個(gè)類別的數(shù)據(jù)，匆匆忙忙趕緊找。唉，能不能給我個(gè)數(shù)據(jù)遙控器，想看什么，點(diǎn)擊個(gè)菜單。哈哈哈，切片器就是來滿足你這個(gè)要求了。
圖10:10-數(shù)據(jù)透視表—插入切片器（GIF）
技巧11:切片器多報(bào)表鏈接，按一鍵即可掌控
切片器不僅能構(gòu)建多個(gè)，而且一個(gè)切片器可以鏈接多個(gè)報(bào)表。同一個(gè)遙控器，按一鍵，控制的可是多個(gè)報(bào)表，數(shù)據(jù)展現(xiàn)輕松暢快。
操作步驟：選中切片器—右鍵選擇報(bào)表鏈接—選中你需要的數(shù)據(jù)透視表即可。
圖11:11-數(shù)據(jù)透視表—切片器—多表鏈接（GIF）
技巧12:不只有表，還有圖形展示：數(shù)據(jù)透視圖
完成的數(shù)據(jù)報(bào)表不只是可以通過表格實(shí)現(xiàn)，直接還可以生成圖表。如果再配合一個(gè)切片器，圖表竟然動了起來。
操作步驟：選中數(shù)據(jù)透視表任一單元格—數(shù)據(jù)透視圖—選中你需要的圖表類型—結(jié)合切片器，圖表就成為了動態(tài)圖表。
圖12:12-數(shù)據(jù)透視表—插入數(shù)據(jù)透視圖（GIF）
數(shù)據(jù)透視表的功能是不是很強(qiáng)大，如果再讓你完成100張數(shù)據(jù)統(tǒng)計(jì)報(bào)表，是不是工作效率瞬間倍增。
但在這里，還是要給大家一個(gè)小貼士：
數(shù)據(jù)透視表好用，但原始數(shù)據(jù)一定要規(guī)范：數(shù)據(jù)標(biāo)簽行只有一行、數(shù)據(jù)完整、不要匯總統(tǒng)計(jì)、不要合并單元格、數(shù)據(jù)格式規(guī)范。千萬要記住！
VLOOKUP函數(shù)
權(quán)重：25%+
重要程度：★★★★★
學(xué)習(xí)難度：★★★★
在我們的工作中，基本每天都會遇到這樣的場景。比如從總表中，根據(jù)姓名匹配身份證號信息，根據(jù)考核等級確定獎(jiǎng)金比例。
這些工作本質(zhì)上都是匹配調(diào)用：匹配同樣的數(shù)據(jù)，調(diào)用出我們需要的數(shù)據(jù)。
要解決這個(gè)問題，最常用到的就是VLOOKUP函數(shù)。VLOOKUP函數(shù)是Excel中的大眾情人。我們曾經(jīng)做過“如果只能選擇學(xué)習(xí)Excel中的一項(xiàng)功能，你會選擇哪個(gè)”的調(diào)查，VLOOKUP函數(shù)竟然高居第二位。
那么VLOOKUP函數(shù)究竟如何使用呢？
VLOOKUP函數(shù)語法結(jié)構(gòu)：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找范圍，返回的數(shù)值所在的列數(shù)，精確匹配還是模糊匹配）。
技巧13:VLOOKUP函數(shù)精確匹配，返回你需要的唯一數(shù)據(jù)
比如根據(jù)姓名匹配身份證號信息，對于這種匹配調(diào)用唯一的數(shù)據(jù)，就要用到VLOOKUP函數(shù)的精確匹配了。
操作步驟：查找范圍為絕對引用，可按快捷鍵F4，精確匹配下參數(shù)為0或FALSE。
注意事項(xiàng)：查找范圍和要返回的數(shù)值所在的列數(shù)都是要從查找值所在的列開始計(jì)算。
圖13:13-VLOOKUP查詢調(diào)用精確匹配（GIF）
技巧14:VLOOKUP函數(shù)模糊匹配，返回你需要的區(qū)間數(shù)據(jù)
比如根據(jù)考核等級確定獎(jiǎng)金比例，對于這種在區(qū)間范圍內(nèi)匹配調(diào)用數(shù)據(jù)，就要用到VLOOKUP函數(shù)的模糊匹配了，這個(gè)功能完全可以替代掉IF函數(shù)的多層嵌套，再也不用為寫錯(cuò)順序發(fā)愁。
操作步驟：查找范圍依然為絕對引用，可按快捷鍵F4，模糊匹配下參數(shù)為1或TRUE。
注意事項(xiàng)：等級表的編制要從小到大
圖14:14-VLOOKUP查詢調(diào)用模糊匹配（GIF）
圖表制作與可視化
權(quán)重：20%+
重要程度：★★★★
學(xué)習(xí)難度：★★★★
俗話說：能用數(shù)據(jù)顯示的，絕不用文字說明；能用圖形顯示的，絕不用數(shù)據(jù)說明。
技巧15:柱線組合圖：數(shù)據(jù)差異再大，依然清晰展示
圖15:15-圖表效果（JPG）
這種工作場景你一定會經(jīng)常碰到：老板看的數(shù)據(jù)，既要匯總值，還要變化率，一張圖表上如何體現(xiàn)這兩類數(shù)據(jù)差異如此之大的圖表呢。
第一步：插入圖表，創(chuàng)建組合圖
操作步驟：全選數(shù)據(jù)—插入圖表—二維柱形圖—選中圖表后在【格式】選項(xiàng)卡下選中“系列引文影響力”—【設(shè)計(jì)】選項(xiàng)卡下更改圖表類型—系列引文影響力變?yōu)檎劬€圖，同時(shí)勾選次坐標(biāo)軸。
注意：對于數(shù)據(jù)差異比較大的數(shù)據(jù)，次坐標(biāo)軸一定要學(xué)會用
動圖圖16:16-插入圖表-組合圖（GIF）
第二步：添加圖表元素，添加數(shù)據(jù)標(biāo)簽
操作步驟：選中圖表—點(diǎn)擊圖表右側(cè)的+號—勾選數(shù)據(jù)標(biāo)簽項(xiàng)—選擇添加位置。
圖17:17-圖表元素添加-添加數(shù)據(jù)標(biāo)簽（GIF）
第三步：坐標(biāo)軸設(shè)置與隱藏，柱線分離，網(wǎng)格線刪除
當(dāng)添加完數(shù)據(jù)標(biāo)簽后，坐標(biāo)軸存在的意義就不大了。
操作步驟：先雙擊主坐標(biāo)軸（左邊）—【坐標(biāo)軸選項(xiàng)】起點(diǎn)200，間隔400，柱形圖相應(yīng)下降，標(biāo)簽位置選擇無（隱藏）。
雙擊次坐標(biāo)軸（右邊）—【坐標(biāo)軸選項(xiàng)】起點(diǎn)-10，折線圖相應(yīng)上升標(biāo)簽位置選擇無（隱藏）
選中網(wǎng)格線，直接按Delete鍵或者取消網(wǎng)格線勾選
圖18:18-柱線分離—隱藏坐標(biāo)軸—?jiǎng)h除網(wǎng)格線（GIF）
第四步：基本美化設(shè)置
操作步驟：柱形圖間距比例調(diào)整為75%-100%，折線圖數(shù)據(jù)標(biāo)簽選擇三角形等。
注意：標(biāo)題可以改得更加醒目，給出直接結(jié)論更好；添加數(shù)據(jù)來源，顯得更加規(guī)范。圖表中的字體可以使用微軟雅黑或者Arial Unicode MS，配色可以去模仿商業(yè)雜志的配色，圖表就會看起來非常專業(yè)。
圖19:19-基本美化-間距設(shè)置（GIF）
讓圖表嵌入到單元格或以諸如條形圖等方式展示，也是除了圖表之外，讓數(shù)據(jù)可視化的常用手段。這里給大家?guī)砣N。
技巧16:條件格式，讓你的數(shù)據(jù)會說話
我們經(jīng)常會這么做：對于增長的數(shù)據(jù)我們標(biāo)注綠色的上升箭頭，不變的變?yōu)槠叫械狞S色箭頭，減少的標(biāo)注紅色的下降箭頭。
這就是條件格式，它提供條形圖、色階、圖標(biāo)集等很多種顯示方式，功能強(qiáng)大。
操作步驟：選擇數(shù)據(jù)—條件格式—條件格式類型選擇。
對于已經(jīng)設(shè)置好的條件格式，我們也可以通過編輯進(jìn)行再次設(shè)置。
圖20:20-條件格式（GIF）
技巧17:REPT，函數(shù)也可以制作條形圖
不只是通過插入圖表設(shè)置條形圖，函數(shù)也可以噠。=REPT（text，number_times)。即REPT（“符號”，次數(shù)）
操作步驟：“|”位置輸入鍵在ENTER鍵上方（/鍵），重復(fù)次數(shù)可根據(jù)情況使用原值或擴(kuò)大或減小，比如重復(fù)次數(shù)*2。
最后設(shè)置字體為Playbill，更改字體顏色即更改條形圖顏色。
圖21:21-REPT函數(shù)，條形圖制作（GIF）
技巧18:迷你圖，讓數(shù)據(jù)更直觀
將柱狀圖、折線圖、盈虧圖等嵌入單元格里有沒有很酷炫的感覺？插入迷你圖，就可以實(shí)現(xiàn)了，效果絕對棒棒噠！
操作步驟：全選數(shù)據(jù)—插入迷你圖—選擇迷你圖類型—選擇迷你圖插入位置。
圖22:22-迷你圖（GIF）
心動不如行動，馬上學(xué)習(xí)起來吧！
本文來源于公眾號LinkedIn，轉(zhuǎn)載請聯(lián)系作者并注明來源。【轉(zhuǎn)載說明】 若上述素材出現(xiàn)侵權(quán)，請及時(shí)聯(lián)系我們刪除及進(jìn)行處理：[email protected]
