我是51CTO學(xué)院講師趙文超，在51CTO學(xué)院 “??4.20 IT充電節(jié)??”(4月19~20日) 到來之際，和大家分享一下Excel實(shí)戰(zhàn)經(jīng)驗(yàn)。正文來啦~~~

這是我和Excel的故事，說起來有些不可思議 – 非統(tǒng)計(jì)學(xué)科班出身的我，現(xiàn)在竟然從事著數(shù)據(jù)分析的工作，這在十幾年前我是萬萬想不到的。

初識(shí)Excel

讀書時(shí)，我學(xué)的是建筑設(shè)計(jì)專業(yè)。當(dāng)時(shí)的軟件課程是AutoCAD, Photoshop等設(shè)計(jì)類的，對(duì)于Office軟件我知之甚少，更不用說Excel了。記得***次看到Excel是一位會(huì)計(jì)朋友展示他做的表格給我看，當(dāng)看到Excel界面和密密麻麻的數(shù)字時(shí)，我就完全懵了，這是什么鬼?更厲害的是竟然還要寫函數(shù)，還能自動(dòng)進(jìn)行加減乘除計(jì)算!我的天吶！

?[[188830]]? 

驚嘆之余，我的感慨是隔行如隔山，我是不可能學(xué)會(huì)Excel的，好在我也不可能用到Excel。

Excel簡單操作

生活總是充滿了各種陰差陽錯(cuò)。后來，一次幫朋友做兼職的機(jī)會(huì)，我開始使用Excel了。當(dāng)時(shí)的兼職是數(shù)據(jù)處理，很簡單，從表格A里復(fù)制公司名的關(guān)鍵詞去表格B里查找該關(guān)鍵詞是否存在，也就是所謂的模糊匹配。簡單說，當(dāng)時(shí)的工作就是Ctrl+C、Alt+Tab、Ctrl+F、Ctrl+V… 然后不斷重復(fù)，重復(fù)，再重復(fù)。幾個(gè)月下來，***的進(jìn)步就是這幾個(gè)快捷鍵非常熟悉。同時(shí)也在兼職之余，看到了項(xiàng)目負(fù)責(zé)人是如何用Excel來統(tǒng)計(jì)匯報(bào)我們每天的工作進(jìn)度。這也算是見識(shí)了高手是如何利用Excel開展工作的。

Excel函數(shù)

借著兼職的機(jī)會(huì)，我跳槽到了某IT公司做運(yùn)營相關(guān)的工作(當(dāng)然是因?yàn)楣べY比原來高)。工作內(nèi)容也比較簡單，就是審核各代理商提交上來的申請(qǐng)是否符合要求。由于每周需要把處理的申請(qǐng)數(shù)量向老板匯報(bào)，我不得不開始學(xué)習(xí)如何用Excel 進(jìn)行統(tǒng)計(jì)。最開始我是用鼠標(biāo)數(shù)，后來慢慢學(xué)會(huì)了SUM、COUNT等簡單Excel函數(shù)。

?? 

VLOOKUP和數(shù)據(jù)透視表

大家肯定也遇到過，除了老板之外，工作中總有各種各樣的人向你要一些莫名其妙的報(bào)告。也許是出于工作需要，也許僅僅是為了滿足他們的好奇心或講故事需要(你懂的)。總之，產(chǎn)品組的人會(huì)要涉及到某個(gè)產(chǎn)品的報(bào)告，區(qū)域的人會(huì)要某個(gè)區(qū)域的報(bào)告…。而做為新人的我，當(dāng)時(shí)是不會(huì)也不敢說“不”的，只好硬著頭皮一個(gè)個(gè)去用篩選來數(shù)。那時(shí)只要有人要報(bào)告，我就需要加班。這種情況直到學(xué)會(huì)了VLOOKUP和數(shù)據(jù)透視表才得到緩解。VLOOKUP可以說是我學(xué)習(xí)Excel函數(shù)的一個(gè)里程碑，通過VLOOKUP我將各種各樣的信息V到一張大表里，然后再生成數(shù)據(jù)透視表報(bào)告。這是我工作前幾年一直用的方法。

?? 

圖表

會(huì)用數(shù)據(jù)透視表后，我制作報(bào)告的工作效率提高了很多，于是有時(shí)間考慮報(bào)告的美化工作。除了數(shù)據(jù)透視表，我開始嘗試學(xué)習(xí)在報(bào)告中加入一些圖表，比如折線圖，餅圖等。

?? 

再后來，這些Excel自帶的圖表已經(jīng)不能滿足我了，我開始在網(wǎng)上找資料，學(xué)習(xí)一些特殊圖表：

比如地圖： 

用氣泡圖加背景圖片制作的地圖效果

比如馬表，下面是我學(xué)習(xí)制作Excel馬表的6個(gè)版本 -從1.0到6.0。

?? 

還有現(xiàn)在看起來有點(diǎn)別扭的溫度計(jì)：

?? 

Excel儀表盤

有了前面的基礎(chǔ)，就可以組合簡單的Excel 儀表盤了，看起來還不錯(cuò)?

??

至此，我已經(jīng)可以應(yīng)對(duì)大部分制作報(bào)告的需要，但是工作還是比較重復(fù)、手工。有些問題依然解決不了：

不重復(fù)計(jì)數(shù)，我需要用透視表先透視，然后慢慢數(shù)。網(wǎng)上搜索了一下，數(shù)組可以解決，但我無論如何就是學(xué)不會(huì)數(shù)組，無奈只好繼續(xù)用透視表數(shù)數(shù)兒。

重復(fù)工作太多，想要自動(dòng)化一些手工操作，網(wǎng)上的方法是VBA或SQL…代碼盲的我果斷放棄。

報(bào)告更新時(shí)間太長，不光一大堆數(shù)據(jù)需要更新，VLOOKUP還要等很久。

這要怎么破?

Power Pivot – 數(shù)據(jù)建模分析

這是一個(gè)瓶頸期，由于沒有技術(shù)背景，學(xué)習(xí)成本太高，我一時(shí)感覺無法再進(jìn)一步提高。如果故事是這樣的話，也就不會(huì)有今天的這篇文章了。

轉(zhuǎn)機(jī)出現(xiàn)在2010年，微軟推出了Excel的插件 – Power Pivot，當(dāng)時(shí)還需要單獨(dú)下載安裝。我的老板，同時(shí)也是我的Excel 啟蒙老師看到了這個(gè)插件的潛力，要求我們學(xué)習(xí)并在工作中使用。

一開始我并沒有意識(shí)到Power Pivot有什么強(qiáng)大的地方，Power Pivot當(dāng)時(shí)宣傳是海量級(jí)數(shù)據(jù)處理，打破傳統(tǒng)Excel104萬行數(shù)據(jù)的限制，而我接觸到的數(shù)據(jù)遠(yuǎn)遠(yuǎn)沒有達(dá)到百萬，感覺沒有什么用。

直到有一天我發(fā)現(xiàn)Power Pivot連接幾個(gè)Excel數(shù)據(jù)源后，我只要進(jìn)行簡單地關(guān)系創(chuàng)建就能輸出透視表報(bào)告。***的亮點(diǎn)是這個(gè)模型可以重復(fù)使用，以后源數(shù)據(jù)更新后，我只要點(diǎn)擊“刷新”，所有的報(bào)告就都自動(dòng)更新了。這個(gè)太神奇了，這不就是原來我希望通過學(xué)習(xí)SQL實(shí)現(xiàn)的效果嗎?于是我開始上手使用Power Pivot，越學(xué)越發(fā)現(xiàn)他的強(qiáng)大：

1.在Excel里處理各種量級(jí)的數(shù)據(jù)，不再受Excel104萬行的限制。

2.獲取各種格式的數(shù)據(jù)源，包括Excel，CSV, 數(shù)據(jù)庫等。

3.將SQL里的模型概念引入Excel，只需拖拽建立關(guān)系即可，不再需要VLOOKUP。

4.Power Pivot內(nèi)嵌的DAX函數(shù) (數(shù)據(jù)分析表達(dá)式)類似Excel 函數(shù)，普通用戶上手快，而且遠(yuǎn)比Excel函數(shù)強(qiáng)大。可以輕松實(shí)現(xiàn)不重復(fù)計(jì)數(shù)、YTD 、YOY等計(jì)算。

5.最最重要的是，模型一次建立，***復(fù)用。也就是說我以后只需要點(diǎn)擊“刷新”，所有的報(bào)告都會(huì)更新。原來需要半天做好的報(bào)告，現(xiàn)在10分鐘就能搞定。

下面是Power Pivot生成的數(shù)據(jù)透視表報(bào)告，通過和切片器的配合，讓用戶和報(bào)告進(jìn)行交互，再也不需要做很多頁，很多列，自己維護(hù)起來都困難的報(bào)表了。

?? 

通過學(xué)習(xí)使用Power Pivot，為我打開了Excel進(jìn)階的一扇門。真正幫我把原來80%的整理數(shù)據(jù)的時(shí)間節(jié)省下來做更多有意義的工作。也正因?yàn)槿绱?，我也有幸從原來的部門換到了運(yùn)營分析部門，開始了專職數(shù)據(jù)分析之路。

Power Query – 數(shù)據(jù)獲取整理

到了2013年，微軟把Power Pivot直接嵌入到了Excel2013的加載項(xiàng)里，這充分說明了Power Pivot是非常被用戶認(rèn)可的。同時(shí)，Excel 2013 里還嵌入了其他3個(gè)插件：Power Query、Power View和Power Map。

Power Query是用來做數(shù)據(jù)獲取整理的。對(duì)于經(jīng)常需要手工對(duì)原始數(shù)據(jù)進(jìn)行合并、追加、分組等整理工作的Excel用戶而言，Power Query簡直就是數(shù)據(jù)整理神器。他通過圖形化界面就能完成大部分?jǐn)?shù)據(jù)整理工作。

?? 

來看幾個(gè)Power Query的數(shù)據(jù)處理：

追加查詢

?? 

透視/逆透視

?? 

除了圖形化操作，Power Query內(nèi)嵌的M語言，可以說是神器中的神器。用戶可以通過使用M語言進(jìn)行各種數(shù)據(jù)整理操作。比如文本移除(Text.Remove)，下圖是文本移除前和移除后的效果。

?? 

只需要使用M語言：Text.Remove([原始信息],{“”..”~”})即可實(shí)現(xiàn)上述效果。這些只是冰山一角，M語言的強(qiáng)大還需要用戶親自使用才能感受到。

***，也是最重要的一點(diǎn)，Power Query里的步驟記錄器可以記錄下所有數(shù)據(jù)整理過程。

?? 

這意味著我們可以隨時(shí)調(diào)整過去某一步的動(dòng)作。并且，今后我們的數(shù)據(jù)整理工作也會(huì)自動(dòng)化 – 用戶只要點(diǎn)擊“刷新”，所有的數(shù)據(jù)整理過程會(huì)自動(dòng)執(zhí)行。也就是說我們不再需要重復(fù)工作！

Power View – Excel交互儀表板

有了Power Pivot模型，我們還可以快速在Excel 的Power View里制作交互式儀表板。效果是這樣的： 

Power Map – Excel地圖可視化

Power Map是專業(yè)制作地圖可視化的組件，他的出現(xiàn)徹底改變了我們需要一點(diǎn)點(diǎn)在Excel里拼地圖的情況。通過使用Power Map，我們可以快速地進(jìn)行地圖可視化展現(xiàn)，他不僅支持多種展現(xiàn)形式，還通過引入圖層的概念支持多維度展現(xiàn)： 

還可以按時(shí)間軸播放，實(shí)現(xiàn)動(dòng)態(tài)地圖可視化展現(xiàn)效果。

?? 

還能把各個(gè)場景連接起來，制作炫酷的地圖可視化視頻：

此處請(qǐng)插入視頻??<https://v.qq.com/x/page/p03444l83tp.html>??

通用代碼：

<iframe frameborder="0" width="640" height="498" src="https://v.qq.com/iframe/player.html?vid=p03444l83tp&tiny=0&auto=0" allowfullscreen></iframe>

如技術(shù)有難度可以不插入。

自從學(xué)習(xí)并掌握這些插件后，我們的工作效率大大提高，報(bào)告制作周期大大縮短，做出的交互式報(bào)表也是深受公司高層的好評(píng)。

Power BI

故事到這應(yīng)該結(jié)束了，但真正的故事才剛剛開始。前面提到的Excel 數(shù)據(jù)分析神器其實(shí)只是微軟的Power BI在Excel里的4個(gè)插件，真正厲害的是我們做好的Excel報(bào)告可以直接發(fā)布到Power BI的在線服務(wù)上，制作更高大上的可視化儀表板和報(bào)表，并且這些報(bào)表還支持移動(dòng)端展現(xiàn)。

先看看Power BI的高顏值報(bào)表 – 可交互、下鉆 

除了常用的可視化圖表外，Power BI還提供了自定義可視化圖標(biāo)庫，截止目前，此自定義圖標(biāo)庫提供超過80種自定義圖表，且此數(shù)字還會(huì)一直上升。

?? 

問與答

更神奇的自然語言問與答功能，讓你的報(bào)告會(huì)說話。

?? 

除此之外，Power BI還有分享協(xié)作、定制刷新、權(quán)限管理、業(yè)務(wù)警報(bào)等非常多的功能。

篇幅原因，沒有辦法把這些神奇的功能一一列舉出來。去年夏天，我把這些經(jīng)驗(yàn)錄制成了視頻課程 – “從Excel到Power BI數(shù)據(jù)分析可視化”，分享到51CTO學(xué)院上，希望更多的人通過學(xué)習(xí)Power BI系列組件受益，實(shí)現(xiàn)“加薪不加班”的夢(mèng)想。

?? 

51CTO學(xué)院 4.20 IT充電節(jié)

(19-20號(hào)兩天，100門視頻課程免單搶，更有視頻課程會(huì)員享6折，非會(huì)員享7折，套餐折上8折，微職位立減2000元鉅惠)

活動(dòng)鏈接：??http://edu.51cto.com/activity/lists/id-47.html?wenzhang??

相關(guān)視頻教程：

從Excel到Power BI數(shù)據(jù)分析可視化

課程發(fā)布后，反響非常強(qiáng)烈，已有超過3000人報(bào)名學(xué)習(xí)。如果你正在用Excel進(jìn)行分析工作，如果你也和我一樣沒有技術(shù)背景，不會(huì)數(shù)組、VBA、SQL，現(xiàn)在就登陸51CTO，開始上手體驗(yàn)Power BI神器吧!

??http://edu.51cto.com/pack/view/id-641.html??