在業(yè)務(wù)分析中實現(xiàn)商業(yè)洞察 –Excel商業(yè)智能分析報表的玩法

方珺逸 2016-07-28

展開全文

一套完整的BI報表應(yīng)該至少具備以下四個條件：

條件一：能夠批量處理有一定規(guī)模的數(shù)據(jù)；

條件二：能夠保證數(shù)據(jù)的時效性及準(zhǔn)確性；

條件三：能夠?qū)嶋H業(yè)務(wù)中所涉及的所有相關(guān)數(shù)據(jù)整合到一起，搭建統(tǒng)一的多維數(shù)據(jù)分析環(huán)境；

條件四：能夠?qū)崿F(xiàn)交互式操作；

接下來的內(nèi)容將逐一為大家介紹在Excel上達(dá)成上述條件的方法。

想用Excel制作滿足所有條件的合格的BI報表我們需要掌握以下技能樹中的相關(guān)技能：

上述技能中的Power BI插件可以幫助我們達(dá)成在Excel中制作BI報表的前三項條件。Power BI是微軟為強化自身產(chǎn)品商業(yè)智能功能而開發(fā)的工具集。其中供Excel使用的主要插件工具包括Power Query、Power Pivot、Power View及Power Map這四款插件。這些插件工具均由微軟免費提供下載，適用于Excel 2010以上版本。根據(jù)Excel版本的不同，有些工具已經(jīng)預(yù)先安裝在Excel中，可以在加載項中直接激活使用。而有些則需要先通過網(wǎng)站下載后再激活使用。

這些BI插件大幅擴(kuò)展了Excel在數(shù)據(jù)處理、數(shù)據(jù)分析及結(jié)果展現(xiàn)方面的能力，使Excel從一個傳統(tǒng)的表格工具華麗變身為集表格與BI功能為一身的綜合數(shù)據(jù)分析、處理及展現(xiàn)平臺。

這里主要為大家介紹Power Query及Power Pivot這兩個最為重要的插件工具。省略Power View與Power Map的理由不是因為它們不重要，而是因為這兩個插件更像兩個獨立于Excel之外的工具，它們雖然需要通過Excel啟動，但啟動后它們會在Excel工作表中生成自己獨立的操作及展現(xiàn)界面，無法與Excel的其他功能結(jié)合使用，不適用于在Excel界面中創(chuàng)建完整的BI報表。

而Power Query與Power Pivot這兩款插件雖然操作界面獨立于Excel表格界面之外，但與表格界面共享同一數(shù)據(jù)源，展現(xiàn)界面也是Excel的表格界面，所以這兩款插件是最適合Excel BI報表的插件工具。

Power Query及Power Pivot聯(lián)手可以幫助Excel完成很多BI功能上的突破：

提取整合多數(shù)據(jù)源數(shù)據(jù)（如各種關(guān)系型數(shù)據(jù)庫、Excel文件、txt格式及csv格式等文本文件、Web頁面、Hadoop的HDFS等等）；

關(guān)聯(lián)多個數(shù)據(jù)源數(shù)據(jù)，建立統(tǒng)一的多維數(shù)據(jù)模型；

突破Excel表格的數(shù)據(jù)限制（它們可快速處理幾百萬甚至上千萬行的數(shù)據(jù)）；

可通過插件自帶的函數(shù)公式靈活創(chuàng)建自定義數(shù)據(jù)處理及計算規(guī)則

了解了Power BI是什么之后，我們再回到制作BI報表的四個條件上。

條件一：能夠批量處理有一定規(guī)模的數(shù)據(jù)

這需要Excel能夠擁有類似數(shù)據(jù)庫的處理“表”結(jié)構(gòu)數(shù)據(jù)的方法?！氨怼苯Y(jié)構(gòu)數(shù)據(jù)與Excel的“表格”數(shù)據(jù)最大的不同就是“表”結(jié)構(gòu)數(shù)據(jù)的最基本處理單位是“列”而不是“單元格”，“列”在“表”中又被稱為“字段”，對“表”中某個字段進(jìn)行計算后所有該字段行中的數(shù)值都將發(fā)生變化，只有具備了對“表”進(jìn)行操作的能力，才有可能快速批量處理大量數(shù)據(jù)以及在不同表間建立聯(lián)接關(guān)系，對“表”的操作是BI以及其他數(shù)據(jù)分析方法（預(yù)測分析、數(shù)據(jù)挖掘等）的基礎(chǔ)，在Excel中，Power Query以及Power Pivot正是以“表”結(jié)構(gòu)方式對數(shù)據(jù)進(jìn)行導(dǎo)入、存儲以及操作的。

條件二: “能夠保證數(shù)據(jù)的時效性及準(zhǔn)確性”

為了滿足此條件，Excel必需具備能夠?qū)氩煌瑪?shù)據(jù)源的外部數(shù)據(jù)并且能夠隨時與這些數(shù)據(jù)源進(jìn)行數(shù)據(jù)同步的能力，利用Power Query以及Power Pivot可以簡單快速地對多種數(shù)據(jù)源數(shù)據(jù)進(jìn)行導(dǎo)入及同步更新。

條件三: “能夠?qū)嶋H業(yè)務(wù)中所涉及的所有相關(guān)數(shù)據(jù)整合到一起，搭建統(tǒng)一的多維數(shù)據(jù)分析環(huán)境（多維數(shù)據(jù)集）”

多維數(shù)據(jù)集是相互間通過某種聯(lián)系被關(guān)聯(lián)在一起的不同類別的數(shù)據(jù)集合。多維數(shù)據(jù)集在咨詢公司以及BI工具廠商的介紹性資料中又被稱為“立方體（Cube）”或“多維數(shù)據(jù)模型”，在這些資料中常以一個立體正方形的形式出現(xiàn)。

多維數(shù)據(jù)集可以從多角度用數(shù)據(jù)全面映射某種業(yè)務(wù)的實際狀況。因為在企業(yè)運作中，任何業(yè)務(wù)都不是孤立存在的，只有多方考慮各種關(guān)聯(lián)因素才能掌握業(yè)務(wù)全貌，做出正確決策。比如當(dāng)出現(xiàn)上季度業(yè)績不佳的情況時，其原因可能來自于產(chǎn)品的渠道商不給力，或是產(chǎn)品競爭力下降，或是本公司銷售人員的能力所致，還有可能是這些原因共同作用的結(jié)果等等……市場業(yè)務(wù)人員只有將所有相關(guān)因素放在一起綜合考慮才有可能正確把握發(fā)生的情況，而多維數(shù)據(jù)集正是為滿足這樣的業(yè)務(wù)要求而產(chǎn)生的。所以創(chuàng)建全面的多維數(shù)據(jù)集是制作BI報表的關(guān)鍵，利用Power Pivot的關(guān)系圖視圖模式可以非常方便地快速搭建多維數(shù)據(jù)集。

條件四: “能夠?qū)崿F(xiàn)交互式操作”

交互式操作在BI報表中一般指動態(tài)圖表，動態(tài)圖表是能夠隨時響應(yīng)用戶操作指令改變展現(xiàn)結(jié)果的圖表。動態(tài)圖表是Excel中較為高級的圖表應(yīng)用形式，一旦圖表從靜態(tài)變?yōu)閯討B(tài)后，分析的深度及廣度都將得到質(zhì)的改變。一個專業(yè)的BI報表必然不能缺少優(yōu)秀的動態(tài)圖表元素。在不使用VBA的情況下，一般有兩種方法可以讓圖表動起來。

第一種方法比較簡單，就是用切片器直接控制數(shù)據(jù)透視圖表的方法，這種方法只適用于有切片器功能的Excel 2010以上版本。

第二種方法是使用控件、公式改變靜態(tài)圖表數(shù)據(jù)源的方法。這種方法是在切片器出現(xiàn)前就存在的傳統(tǒng)方法，缺點是制作起來比較麻煩，而且要保證公式引用區(qū)域及控件鏈接區(qū)域始終正確有效，限制條件較多。優(yōu)點是適用的圖表類型廣泛，不僅適用Excel自帶的傳統(tǒng)圖表，就連需要特別制作的自定義圖表（比如地圖）也同樣適用。

在滿足以上四個條件后我們基本上就可以在Excel中制作BI報表了，不過為了使制作的BI報表在展現(xiàn)形式上更為美觀，在使用感受上更為親切、方便，我們還需要學(xué)會專業(yè)商務(wù)圖表的制作技巧以及一些簡單VBA程序的編寫方法。

想要在Excel平臺上制作出“好看”的專業(yè)圖表，除了要熟練掌握Excel的基本制圖功能外，還要了解一些制作自定義圖表的重要技巧，這些技巧能夠幫助我們在圖表表現(xiàn)形式及圖表呈現(xiàn)方法上實現(xiàn)創(chuàng)新，只有擺脫Excel基本制圖功能的限制，不斷對圖表進(jìn)行改進(jìn)創(chuàng)新，才能制作出時尚美觀、能夠滿足實際分析需求的圖表。

比如嵌套多層餅圖及環(huán)形圖制作的半圓形儀表盤：

或者是用Excel公式及條件格式功能制作的MINI圖等。

如果把BI報表比作一盤菜，那么VBA程序就是菜中的調(diào)味料，有了“調(diào)味料”，“菜”才能更有味道。使用VBA程序不僅可以簡化報表的制作及操作過程，還能夠增強報表的互動性、自動化處理能力、界面及圖表的展現(xiàn)效果、數(shù)據(jù)加工處理能力及數(shù)據(jù)分析能力，令智能報表更加“智能”。

例如可以用VBA將環(huán)形圖自動填充至折線圖中的不同節(jié)點處，完成折線環(huán)形圖的快速嵌套制作：