從 Excel 數據分析到 PowerBI 其實是自然之選

• 2019 年 10 月 6 日
• 筆記

微軟用幾年的彎路摸索出自助商務智慧的最終產品路線，PowerBI 自然而然地來了。另外，如果您正從零（或者具備一定Excel基礎）開始希望學習自助BI，也可以對照看目前所處的位置以更清晰學習上升的路線。

概述

不知從何時起，人們的辦公計算開始進入了數字化階段，大家開始使用Excel，通過Excel在工作表中使用各種公式來完成業務計算並成為了常態。然後發現很多業務數據會以某種規律化的方式反覆出現，便專門用表結構來組織這些數據，並強行要求這些數據必須滿足某些規則，否則會被視為是臟數據（不符合要求的業務數據），當然這種工作也可能由一個專門的部門IT部來完成。在觀察數據之間的關係和規律方面，有一種強大的工具叫做透視表（Pivot Table），歷史上，透視表最初是基於某個表的匯總分析表，透視表給了人們非常強大的洞察力。由於人們往往需要聯合不同的基礎表在一起以後再做透視表，這就要求需要一個主要的表作為基礎，把相關的數據補充進來，這個過程在Excel中常常由VLOOKUP函數完成。做大量業務分析的夥伴希望更好的提升效率，包括Excel的發明者微軟公司也面臨同樣問題，這就出現了數據模型的概念，期初數據模型由專業的IT人員設計，由終端用戶使用，而在現如今的Excel中，人們可以自行設計數據模型。由於有了數據模型，人們觀察和分析數據的基礎變得更加厚實，人們自然需要更快速靈活的探索和分析工具，數據可視化工具就成了重點。現在，人們可以通過數據可視化工具並憑藉自己的業務知識自行構建數據模型並展開分析，獲得更強大的生產力。 而集合上述所有功能為一體的工具中，PowerBI便是這樣進化而來。我們沿著歷史的發展來一起看看，現在您正在用怎樣的方法來處理和分析數據，以不斷理解我們可以進一步提升的方向。

Excel 平面數據計算

如果您正用著或者迷戀著Excel的函數，並直接展開某些計算，例如：

這裡即將寫出類似：SUM(A1:B5) 之類的公式來實現計算。 我們說這種方式叫：辦公計算 或者叫 平面數據計算。所謂辦公計算，就是它通常由個人基於某種臨時性的任務而計算完成，這個過程是不受控的，但很靈活。一些複雜的任務可能也會完成，但大多需要依賴大量技巧和潛在的非正規式的使用。 除非本人就對這種計算著迷，否則很難學會複雜的技巧用法。在大量數據面前，由於它並非為大數據量設計，顯得捉襟見肘。

Excel 工作表中的表

在辦公計算階段，一旦涉及的業務變得複雜，很難理解諸如 A5:D7 這種編碼形式的含義，而導致業務計算的邏輯在維護起來很複雜，也可能引發潛在的計算問題而不被發現。Excel中存在一種表結構，叫做Excel中的表。如下所示：

如果您認為只是表格帶了樣式就叫表格那就錯了，再看一個區別性的案例，請分辨左右兩邊的表結構，哪個是平面區域數據，哪個是Excel中的表：

理解Excel中的表的夥伴應該能一眼認出右邊才是Excel中的表，左邊僅僅是樣子和右邊一樣的平面區域數據而已。

思考：怎麼看出右邊是Excel中的表的？

Excel中的表的好處在於，在進行計算的時候，可以引用的是業務邏輯名稱，而不再是無意義的編碼區域了，例如：

這裡仍然是求和，但求和的對象已經使用了有意義的業務名稱了。可以看一個更加讓人一目了然的案例：

可以通過這種方式來進行計算，讓業務更加直接。

思考：在Excel中的快捷鍵：Ctrl + T 是幹嘛的？

Excel 透視表

之前我們已經鋪墊了什麼是透視表，或者說大多數人都在用透視表，但有誰真正停下來思考過，到底什麼是透視表呢。即使微軟也告訴了大家透視表能幹什麼以及用來幹什麼，但卻沒講過什麼是透視表。

我們可以觀察到一個現象：

插入透視表和表格是在同一個分組裡的。有很多人用了很多年透視表，但卻從沒用過表格。只能說微軟把透視表和表格編入一個功能分組，它們一定有非常緊密的關係，而如果你只用過透視表卻不知道表格，那說明什麼呢，透視表用得也一定不過關。

Excel 函數 VLOOKUP

現在讓我們來假設你會用表格和透視表，但很快你會遇到的問題就是：訂單表沒有產品資訊，需要根據產品資訊來分析訂單而建立透視表的時候，就需要在訂單表裡補充一個產品類別列，會這樣做：

人們開始思考如何做到更加的系統化處理業務，並去突破各種函數的限制。VLOOKUP很快就會被發現存在嚴重隱患：

正如上述細節描述，VLOOKUP存在兩個核心痛點：

• 要查找的值必須位於 table-array 中指定的單元格區域的第一列中。
• 列編號是一個固定的數。

這兩個問題將導致VLOOKUP將是一個巨大隱患。例如一旦你在產品表中插入了一列，那 VLOOKUP( [@產品ID], 產品, 3, FALSE ) 可能將導致計算到另外的列，而這種並非會報錯，有時這種錯誤很難發現，導致很嚴重的業務計算錯誤。

Excel 函數 INDEX + MATCH

當您開始思考如何真正地使用Excel函數來計算業務並確保其準確性，那就進入了一個新階段，這時候人們開始總結各種最佳實踐，也就是一條最好的路。對於VLOOKUP，可以用INDEX+MATCH來替代，如下：

INDEX + MATCH 的函數組合可以徹底解決 VLOOKUP 存在的風險，由此，您可以認為自己已經是Excel的函數高手了，並開始學習更多的Excel函數。

從技術的角度，我們確實理解了Excel中更多函數組合的秘密並能實現很棒的計算效果，但對於業務問題本身，痛點卻依然存在。

因為當你希望從時間，地理位置，產品，人員，客戶等多個角度的多個屬性去分析訂單時，需要寫大量的 INDEX + MATCH 來實現數據整合。當然，這已經比 VLOOKUP 好多了。

基於Excel 數據模型的透視表

其實，微軟的辦公人員一定是比我們最先遭受折磨的，因為微軟的辦公人員也在使用Excel來分析自己的業務，如果我們都無法忍受這種重複和低效，那麼微軟的人能比我們更能忍受嗎？

所以，我們一直有一個同理心邏輯，如果我們自己無法忍受某種低效，而微軟的人員也在這樣使用，請問他能夠忍受這種低效嗎？如果不能，那麼一定存在某種功能可能是我們不知道卻事實存在的。

我們仔細來看下構建透視表的過程細節：

創建數據透視表讓人們有三種選擇：

• 選擇一個表或區域
• 使用外部數據源
• 使用此工作簿的數據模型

大部分人只用過第一個方式的區域形式，其實透視表可以基於表來創建，正如這裡的訂單，其實是一個表。

但依然會遇到問題，就是無法從產品角度進行分析，但仔細觀察，會發現：

原來透視表知道我們可能找不到需要的欄位，因此給了一個機會讓此時可以從更多表格來重新選擇。如下：

這告訴我們，只要在分析中同時使用多個表，將自動使用數據模型來創建透視表。此時，Excel將可能用到的表都列了出來：

我們選擇產品的類別和訂單銷售額來進行分析，如下：

將欄位放置在合適的位置後，可以看到透視表的計算結果並不符合預期，原因是系統並為自動完成諸如 VLOOKUP 的功能，這需要建立表之間的關係，如下：

系統可以幫助用戶自動檢測到關係，並建立起來，以使計算正確。如下：

有了這個功能，在分析數據的時候有了很好的便利性，不再需要使用大量的VLOOKUP或INDEX+MATCH來整理相關數據。

Excel PowerPivot

直到此處，以上的內容在Excel2013及以後版本都已經比較成熟，而是基本都是自帶功能，並且都沒有提過Excel PowerPivot。如果您能用到這個階段，即使您不知道Excel PowerPivot，您也在沿著正統的路線使用透視表分析工具。我們可以在Excel的數據選項卡找到：

此時並沒有啟動PowerPivot，但卻可以定義關係，數據模型是Excel的內置機制，只有當我們需要更進一步地去管理數據模型時，才會繼續用到PowerPivot，而顯然我們也是需要這樣做的。在第一次點擊管理數據模型時，系統會提示我們：

這樣便徹底開啟了數據模型的高級能力：

我們可以在PowerPivot中對數據模型做更加複雜的定義以實現讓透視表可以完成非常強大的計算功能，甚至可以應對百萬乃至億級數據的處理。

理清思路

以上整個歷史的發展截止到2010年。也就是說，在2010年微軟就提供了上述所有功能。

這是當年 PowerPivot 的官方網站，但現在已經不存在了，微軟將 PowerPivot 作為了Excel的內置功能，並在專業增強版提供出來。

按照專家的說法：

這種大事，其實就是可以在一個工具中用一種簡單的方法來聯立分析數以百萬乃至億計的數據。

Power BI

只有一種核心能力，我們說那是一種發動機。有著強大發動機的車不一定就能滿足商業實際需求，在實際中，還要考慮更多：

經過微軟多年的實踐和總結，Power BI內置了這台發動機，並做到了更優化的調校，以滿足企業內對商務智慧的各種需求：

這也是我們說的：第三代BI產品。按照Gartner的描述：

商務智慧與分析平台經過多年發展演化，已經從IT導向的報表模式不可逆轉地轉向到了業務導向的自助分析模式，也就是說：拐點已過。

在微軟從2010年PowerPivot誕生到2017年正式發布Power BI，經過了大量實踐總結，這其中的經驗積累非常之巨大，而Cost顯然也很巨大。

Power BI 一經發布就提供了完全免費的Power BI Desktop，微軟將用Power BI來詮釋其使命：

那就是：賦能每個人和組織。

總結

我們總結了PowerBI入門學習的必經問題，那就是為什麼是PowerBI：

• 辦公自動化階段的平面計算階段歷史；
• 表結構計算階段歷史；
• 透視表階段歷史；
• 函數整合數據階段歷史；
• 優化函數整合數據階段歷史；
• 數據模型階段歷史；
• PowerPivot階段歷史；
• Power BI階段。

如果您剛剛接觸Power BI或正考慮分析企業業務數據，在問要用什麼，那回答必然是：Power BI。上述的一切都是歷史，但依然適用，只不過相對於全面啟動Power BI能力來說，這些都是基礎，並且有一個正確的學習路線。不得不強調：相對於努力來說，正確地學習也很重要。如果您仔細留意，這裡根本沒提VBA，因為 VBA 就不是為了分析數據而生的，它雖然強大，但它不是用來分析數據的。簡單地說，VBA 因自動化而生，但就分析而言，從 Excel 到 Power BI 是自然而然的。

我在 Excel120 等您加入，一起深入研究這些有意思的事。