在業務分析中實現商業洞察 – Excel商業智能分析報表「玩」法介紹

隨著大數據時代的到來，企業管理者對數據價值的重視度越來越高，他們渴望從企業內外部數據中獲得更多的信息財富，並以此為依據，幫助自己做出正確的戰略決策。在此種大環境下，缺乏洞察力的傳統業務報表已經開始無法滿足複雜市場環境中的企業決策需求，在很多企業中，「能否基於業務分析提供更具商業洞察力的數據信息」正在逐步取代「能否準確、及時地提供業務報表」成為考核業務人員能力的重要參考指標。為了能夠提供更具洞察力的信息，需要業務人員強化以下兩類能力：

1. 強化所從事業務工作中的相關知識以及與該業務知識相關的其他擴展知識

2. 強化對工作中使用工具的駕馭能力：考慮到大部分業務人員在業務分析中所使用的工具都是Excel，此項要求簡單來說就是，不只要會用Excel，還要能把Excel「玩」出水平

本文將為大家介紹的主要內容就是關於以上第二項能力，把Excel「玩」出水平的方法，此類方法稱之為「Excel商業智能分析報表」的製作方法。

在數據分析領域中，自古以來，「商業智能」就是提高企業商業洞察力的重要方法，英文縮寫為「BI」，我們先來通過下邊兩個例子快速了解一下「商業智能報表（以下簡稱BI報表）」與傳統業務報表的區別：

案例1： 某份傳統財務報表是這樣的：

而對其稍作加工後，變為足具洞察力的杜邦分析儀（BI報表）：

我們可以通過杜邦分析儀快速了解各財務指標間的構成及佔比關係，並從中快速發現造成關鍵指標同比上升或下降的原因出在哪些相關指標上。

案例2：一份傳統的銷售管理報表可能是這樣的：

而稍作加工後就會變為會講故事的BI報表，像這樣：

銷售經理可以從上邊BI報表中快速了解到銷售商機中的風險點在哪個銷售角色中的哪些銷售階段上，從而做出快速、準確的銷售決策，以確保銷售周期結束前可以成功達成銷售目標。

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

條件一：能夠批量處理有一定規模的數據

條件二：能夠保證數據的時效性及準確性

條件三：能夠將實際業務中所涉及的所有相關數據整合到一起，搭建統一的多維數據分析環境（多維數據集）

條件四：能夠實現互動式操作

下邊內容將逐一為大家介紹在Excel上達成上述條件的方法。

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

上述技能中的Power BI插件可以幫助我們達成在Excel中製作BI報表的前三項條件。PowerBI是微軟為強化自身產品商業智能功能而開發的工具集。其中供Excel使用的主要插件工具包括Power Query、Power Pivot、PowerView及Power Map這四款插件。這些插件工具均由微軟免費提供下載，適用於Excel 2010以上版本。根據Excel版本的不同，有些工具已經預先安裝在Excel中，可以在載入項中直接激活使用。而有些則需要先通過網站下載後再激活使用。

這些BI插件大幅擴展了Excel在數據處理、數據分析及結果展現方面的能力，使Excel從一個傳統的表格工具華麗變身為集表格與BI功能為一身的綜合數據分析、處理及展現平台。

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

而Power Query與PowerPivot這兩款插件雖然操作界面獨立於Excel表格界面之外，但與表格界面共享同一數據源，展現界面也是Excel的表格界面，所以這兩款插件是最適合Excel BI報表的插件工具。

PowerQuery及Power Pivot聯手可以幫助Excel完成很多BI功能上的突破：

1. 提取整合多數據源數據（如各種關係型資料庫、Excel文件、txt格式及csv格式等文本文件、Web頁面、Hadoop的HDFS等等）

2. 關聯多個數據源數據，建立統一的多維數據模型

3. 突破Excel表格的數據限制（它們可快速處理幾百萬甚至上千萬行的數據）

4. 可通過插件自帶的函數公式靈活創建自定義數據處理及計算規則

了解了Power BI是什麼之後，我們再回到製作BI報表的四個條件上，為了滿足「條件一：能夠批量處理有一定規模的數據」，就需要Excel能夠擁有類似資料庫的處理「表」結構數據的方法。「表」結構數據與Excel的「表格」數據最大的不同就是「表」結構數據的最基本處理單位是「列」而不是「單元格」，「列」在「表」中又被稱為「欄位」，對「表」中某個欄位進行計算後所有該欄位行中的數值都將發生變化，只有具備了對「表」進行操作的能力，才有可能快速批量處理大量數據以及在不同表間建立聯接關係，對「表」的操作是BI以及其他數據分析方法（預測分析、數據挖掘等）的基礎，在Excel中，Power Query以及PowerPivot正是以「表」結構方式對數據進行導入、存儲以及操作的。

條件二是「能夠保證數據的時效性及準確性」，為了滿足此條件，Excel必需具備能夠導入不同數據源的外部數據並且能夠隨時與這些數據源進行數據同步的能力，利用PowerQuery以及Power Pivot可以簡單快速地對多種數據源數據進行導入及同步更新。

條件三是「能夠將實際業務中所涉及的所有相關數據整合到一起，搭建統一的多維數據分析環境（多維數據集）」，多維數據集是相互間通過某種聯繫被關聯在一起的不同類別的數據集合。多維數據集在諮詢公司以及BI工具廠商的介紹性資料中又被稱為「立方體（Cube）」或「多維數據模型」，在這些資料中常以一個立體正方形的形式出現。

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

條件四是「能夠實現互動式操作」，互動式操作在BI報表中一般指動態圖表，動態圖表是能夠隨時響應用戶操作指令改變展現結果的圖表。動態圖表是Excel中較為高級的圖表應用形式，一旦圖表從靜態變為動態後，分析的深度及廣度都將得到質的改變。一個專業的BI報表必然不能缺少優秀的動態圖表元素。在不使用VBA的情況下，一般有兩種方法可以讓圖表動起來。第一種方法比較簡單，就是用切片器直接控制數據透視圖表的方法，這種方法只適用於有切片器功能的Excel2010以上版本。

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

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

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

比如嵌套多層餅圖及環形圖製作的半圓形儀錶盤：

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

如果把BI報表比作一盤菜，那麼VBA程序就是菜中的調味料，有了「調味料」，「菜」才能更有味道。使用VBA程序不僅可以簡化報表的製作及操作過程，還能夠增強報表的互動性、自動化處理能力、界面及圖表的展現效果、數據加工處理能力及數據分析能力，令智能報表更加「智能」。

例如可以用VBA將環形圖自動填充至折線圖中的不同節點處，完成折線環形圖的快速嵌套製作：

還可以利用VBA寫一段Funcation函數用以返回切片器篩選值，令閱讀者一目了然掌握當前篩選項狀態：

在掌握了以上Excel應用方法後，再結合自身的業務需求便可以簡單地製作出令領導滿意的具有商業洞察力的商業智能報表了。

歡迎大家關注我的個人微信公眾號「秒懂Club」 我的所有文章都會在此公眾號中首發。

新浪微博「ExcelBI-李奇 」

http://weixin.qq.com/r/MzmXj2bEszvMrSLs92wh (二維碼自動識別)