教學課程:使用 Excel 2013 中的資料模型進行樞紐分析表資料分析

不出一個小時,您就能在 Excel 中建立一個合併多個表格資料的樞紐分析表。此教學課程的第一部分將引導您逐步執行資料的匯入與瀏覽。在第二部分,則會使用 Power Pivot 增益集來精簡報表背後的資料模型,學習如何新增 Power View 報表的計算、階層和最佳化。

現在就讓我們先匯入一些資料。

  1. 下載此教學課程的範例資料 (ContosoV2)。詳細資料請參閱取得 DAX 和資料模型教學課程的範例資料。請將資料檔案解壓縮並儲存在容易存取的位置,例如 [文件] 或 [我的文件]。

  2. 在 Excel 中開啟一個空白活頁簿。

  3. 按一下 [資料] > [取得外部資料] > [從 Access]

  4. 前往含有範例資料檔案的資料夾,然後選取 [ContosoSales]。

  5. 按一下 [開啟]。由於您會連線到含有多個表格的資料庫檔案,因此畫面會出現 [選取表格] 對話方塊,讓您選擇您要匯入哪些表格。

    [選取表格] 對話方塊

  6. 在 [選取表格] 中,核取 [啟用選取多個表格]

  7. 選擇所有表格,再按一下 [確定]

  8. 在 [匯入資料] 中,按一下 [樞紐分析表],再按一下 [確定]

    附註: 

    • 也許您沒有發現,但您已建立一個資料模型了。模型是一個資料整合層,會在您於同一個樞紐分析表中同時匯入或使用多個表格時自動建立。

    • 模型在 Excel 中多半是透明的,不過您可以直接使用 Power Pivot 增益集來檢視和修改。當您在 Excel 的 [樞紐分析表欄位] 清單中看到表格集合時,資料模型會很明顯。建立模型的方法有好幾種。詳細資料請參閱 在 Excel 中建立資料模型

使用樞紐分析表瀏覽資料

當您將欄位拖曳到樞紐分析表欄位清單的 [值][欄][列] 區域時,要瀏覽資料就很容易了。

  1. 在欄位清單中向下捲動,直到您找到 [FactSales] 表格。

  2. 按一下 [SalesAmount]。由於這項資料是數字,因此 Excel 會自動將 [SalesAmount] 置於 [值] 區域。

  3. 在 [DimDate] 中,將 [CalendarYear] 拖曳到 [欄]。

  4. 在 [DimProductSubcategory] 中,將 [ProductSubcategoryName] 拖曳到 [列]。

  5. 在 [DimProduct] 中,將 [BrandName] 拖曳到 [列],將其置於子類別下。

您的樞紐分析表外觀應如以下畫面所示。

顯示範例資料的樞紐分析表

現在,您輕輕鬆鬆就做出一個含有四個不同表格之欄位的基本樞紐分析表。這項工作之所以如此輕鬆,是因為表格之間預先存在關聯。由於來源已存在表格關聯,而且您在一次作業中即匯入所有表格,因此 Excel 可以在模型中重建那些關聯。

但萬一資料是來自不同來源,或是後來才匯入,這時候該怎麼辦呢? 通常您可以根據相符欄來建立關聯,藉此合併新資料。在接下來的步驟當中,您將匯入其他表格,並瞭解建立新關聯的需求和步驟。

新增更多表格

若要瞭解如何設定表格關聯,您必須有一些其他未連線的表格供您使用。在這個步驟當中,您將再匯入一個資料庫檔案,然後貼上其他兩個活頁簿的資料,以取得本教學課程中要用的其餘資料。

新增產品類別

  1. 在活頁簿中開啟新的工作表。這個工作表將用來儲存其他資料。

  2. 按一下 [資料] > [取得外部資料] > [從 Access]

  3. 前往含有範例資料檔案的資料夾,然後選取 [ProductCategories]。 按一下 [開啟]

  4. 在 [匯入資料] 中選取 [表格],再按一下 [確定]

新增地理資料

  1. 插入另一張工作表。

  2. 從範例資料檔案開啟 Geography.xlsx,將滑鼠指標放在 A1 中,然後按下 Ctrl-Shift-End,選取所有的資料。

  3. 將資料複製到剪貼簿。

  4. 將資料貼到您剛剛新增的空白工作表中。

  5. 按一下 [格式化為表格],選擇任何樣式。將資料格式化為表格是為了讓您為它命名,好讓您在稍後的步驟中定義關聯。

  6. 在 [格式化為表格] 中,確認已選取 [有標題的表格]。按一下 [確定]

  7. 將表格命名為 [Geography]。在 [表格工具] > [設計] 的 [表格名稱] 中,輸入 Geography。

  8. 關閉 Geography.xlsx,以從工作區清除該檔案。

新增商店資料

  • 對 Stores.xlsx 檔案重複上述步驟,將其內容貼到空白的工作表中。將表格命名為 Stores。

現在您應該有四份工作表。 Sheet1 包含樞紐分析表,Sheet2 包含 [ProductCategories],Sheet3 包含 [Geography],而 Sheet4 包含 [Stores]。由於您先前已經花時間為每一個表格命名,因此接下來建立關聯的步驟就簡單多了。

使用新匯入表格中的欄位

您現在就可以馬上使用您剛剛匯入之表格的欄位。如果 Excel 無法判斷如何將欄位合併到樞紐分析表,會要求您在新表格與模型原有表格之間建立表格關聯。

  1. 按一下 [樞紐分析表欄位] 頂端的 [全部],檢視可用表格的完整清單。

  2. 捲動到清單的底端。 您可以在這裡找到剛才新增的表格。

  3. 展開 [Stores]。

  4. 將 [StoreName] 拖曳到 [篩選] 區域。

  5. 請注意,Excel 會提示您建立關聯。通知會出現,是因為您使用的欄位來自於和模型沒有關聯的表格。

  6. 按一下 [建立],開啟 [建立關聯] 對話方塊。

  7. 在 [表格] 中選擇 [FactSales]。在您所用的範例資料中,[FactSales] 含有 Contoso 企業的詳細銷售與成本資訊,以及其他表格的索引鍵,包括也出現在您前一步驟匯入之 Stores.xlsx 檔案中的商店代碼。

  8. 在 [欄 (外部)] 中,選擇 [StoreKey]。

  9. 在 [關聯表格] 中,選擇 [Stores]。

  10. 在 [關聯欄 (主要)] 中,選擇 [StoreKey]。

  11. 按一下 [確定]

Excel 隨即會在幕後建立一個資料模型,該模型可用於整個活頁簿內任意數量的樞紐分析表、樞紐分析圖或 Power View 報表中。這個模型的基礎是表格關聯,表格關聯決定了樞紐分析表中所用的導覽與計算路徑。下一個工作就是手動建立關聯,以連接您剛剛匯入的資料。

新增關聯

您可以針對所有匯入的新表格,有系統地建立表格關聯。 如果您要和同事共用活頁簿,而且同事不像您一樣清楚這些資料時,最好能夠預先定義關聯。

當您手動建立關聯時,會同時使用兩個表格。您要為每一個表格選擇欄,讓 Excel 知道如何查閱另一個表格中的關聯列。

您的瀏覽器不支援視訊。 安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

將 [ProductSubcategory] 關聯到 [ProductCategory]

  1. 在 Excel 中,按一下 [資料] > [關聯圖] > [新增]

  2. 在 [表格] 中,選擇 [DimProductSubcategory]。

  3. 在 [欄 (外部)] 中,選擇 [ProductCategoryKey]。

  4. 在 [關聯表格] 中,選擇 [Table_ProductCategory.accdb]。

  5. 在 [關聯欄 (主要)] 中,選擇 [ProductCategoryKey]。

  6. 按一下 [確定]

  7. 關閉 [管理關聯] 對話方塊。

新增類別至樞紐分析表

雖然資料模型經過更新之後,已經含有其他表格與關聯,但是樞紐分析表尚未使用它們。接下來這個工作,您要將 ProductCategory 新增到 [樞紐分析表欄位] 清單中。

  1. 按一下 [樞紐分析表欄位] 中的 [全部],顯示資料模型中的表格。

  2. 捲動到清單底端。

  3. 在 [列] 區域中,移除 [BrandName]。

  4. 展開 [Table_DimProductCategories.accdb]。

  5. 將 [ProductCategoryName] 拖曳到 [列] 區域,置於 [ProductSubcategory] 上方。

  6. 按一下 [樞紐分析表欄位] 中的 [使用中],確認您剛剛所用的表格目前正用於樞紐分析表。

檢查點:複習剛剛所學的內容

現在,您的樞紐分析表已經含有來自多個表格的資料,其中有許多是在後續步驟匯入的。若要將這些資料放在一起,必須建立表格關聯,讓 Excel 用來關聯不同的列。現在您已經知道,要查閱關聯列,必須有欄提供相符的資料。在範例資料檔案中,所有表格都會有一欄作為這個用途。

雖然樞紐分析表是可運作的,但是您大概已經注意到,許多地方仍有改善的空間。[樞紐分析表欄位] 清單似乎還有其他表格 (DimEntity) 與欄 (ETLLoadID) 沒有關聯到 Contoso 企業。而且,我們也仍未整合 Geography 的資料。

接下來:以 Power Pivot 檢視和擴充模型

在下一系列的工作中,您將使用 Microsoft Office Microsoft Excel 2013 中的 Power Pivot 增益集來擴充模型。您會發現,使用增益集提供的 [圖表檢視],可以更輕鬆地建立關聯。您也將使用該增益集來建立計算及階層、隱藏不應該出現在欄位清單中的項目,以及最佳化資料以用於其他報表。

附註: Microsoft Excel 2013 中的 Power Pivot 增益集會於 Office 專業增強版中提供。相關詳細資訊,請參閱 Microsoft Excel 2013 中的 PowerPivot 增益集

啟用 增益集,將 Power Pivot 新增至 Excel 功能區。Power Pivot

  1. 前往 [檔案] > [選項] > [增益集]

  2. [管理] 方塊中,按一下 [COM 增益集] > [執行]

  1. 核取 [Microsoft Office Microsoft Excel 2013 中的 Power Pivot] 方塊,然後按一下 [確定]

功能區現在會有 Power Pivot 索引標籤。

使用 Power Pivot 的 [圖表檢視] 來新增關聯

  1. 在 Excel 中按一下 Sheet3,使其成為使用中的工作表。 Sheet3 含有您早先匯入的 [Geography] 表格。

  2. 在功能區上按一下 Power Pivot > [新增至資料模型]。這個步驟會將 [Geography] 表格新增至模型中。同時也會開啟 Power Pivot 增益集,以用於執行這項工作中的其餘步驟。

  3. 請注意,Power Pivot 視窗會顯示模型中的所有表格,包括 [Geography]。您可以按幾個表格看看。您可以在增益集檢視模型包含的所有資料。

  4. 在 Power Pivot 視窗的 [檢視] 區段中,按一下 [圖表檢視]

  5. 使用滑桿列調整圖表大小,好讓您看到圖表中的所有物件。請注意:[DimEntity] 和 [Geography] 這兩個表格與圖表其他部分並無關聯。

  6. 以滑鼠右鍵按一下 [DimEntity],再按一下 [刪除]。這個表格是原始資料庫的加工品,在模型中是不需要的。

  7. 放大 [Geography],好讓您檢視其所有欄位。您可以使用滑桿,將表格圖表調大。

  8. 請注意,[Geography] 含有 [GeographyKey]。此欄所含的值可以唯一識別 [Geography] 表格中的每一列。讓我們看看模型中是否有其他表格也使用這個索引鍵。如果有,則可以建立一個關聯,將 [Geography] 連接到模型中的其他表格。

  9. 按一下 [尋找]

  10. 在 [尋找中繼資料] 中,輸入 GeographyKey。

  11. 按一下 [找下一個] 幾次。您會注意到 [Geography] 表格和 [Stores] 表格都有出現 [GeographyKey]。

  12. 調整 [Geography] 表格的位置,使其位於 [Stores] 表格旁邊。

  13. 將 [Stores] 中的 [GeographyKey] 欄拖曳到 [Geography] 中的 [GeographyKey] 欄。Power Pivot 會在兩欄之間繪製一條線,以指出其關聯。

在這個工作中,您學會了新增表格和建立關聯的新技術。現在,您已有一個完全整合的模型,其中所有的表格已經全部連接,並且可供 Sheet1 的樞紐分析表使用。

提示:  [圖表檢視] 中有數個表格是完全展開的,顯示了 [ETLLoadID]、[LoadDate] 和 [UpdateDate] 等欄。這些特定欄位是原始 Contoso 資料倉儲的加工品,新增來支援資料擷取與載入作業。在您的模型中不需要這些項目。若要移除欄位,請將其醒目提示,然後按一下滑鼠右鍵,再按一下 [刪除]

建立計算結果欄

您可以在 Power Pivot 中,使用資料分析運算式 (DAX) 來新增計算。在此工作中,您將要計算總利潤,並且新增參照其他表格中的資料值的計算結果欄。然後,您將學習如何使用參照的欄來簡化模型。

  1. 在 Power Pivot 視窗中,切換回 [資料檢視]。

  2. 將 [Table_ProductCategories accdb] 表格重新命名為更好記的名稱。在接下來的步驟中,您會參照到這個表格,而較簡短的名稱會讓計算更易於閱讀。請以滑鼠右鍵按一下表格名稱,再按一下 [重新命名],然後輸入 ProductCategories,最後再按下 Enter。

  3. 選取 [FactSales] 表格。

  4. 按一下 [設計] > [欄] > [新增]

  5. 在表格上方的資料編輯列中,輸入以下公式。「自動完成」可協助您為欄和表格輸入完整名稱,並且列出可用的函數。您也可以直接按一下欄,Power Pivot 就會在公式中新增欄名。

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. 公式建立完畢後,按下 Enter 以接受公式。

    隨後就會在計算結果欄中輸入所有列的值。如果向下捲動表格,您會發現此欄的各列會根據列中的資料而有不同的值。

  7. 以滑鼠右鍵按一下 [CaculatedColumn1] 並選取 [重新命名欄],將該欄重新命名。輸入 Profit,然後按下 Enter。

  8. 現在,選取 [DimProduct] 表格。

  9. 按一下 [設計] > [欄] > [新增]

  10. 在表格上方的資料編輯列中,輸入以下公式。

    = RELATED(ProductCategories[ProductCategoryName])

    RELATED 函數會傳回相關表格中的值。在這個案例當中,[ProductCategories] 表格包含產品類別的名稱,您在建立內含類別資訊的階層時,在 [DimProduct] 表格中納入此資訊是很實用的。有關這個函數的詳細資訊,請參閱 RELATED 函數 (DAX)

  11. 公式建立完畢後,按下 Enter 以接受公式。

    隨後就會在計算結果欄中輸入所有列的值。如果往下捲動表格,會發現每一列現在都有產品類別名稱。

  12. 以滑鼠右鍵按一下 [CaculatedColumn1] 並選取 [重新命名欄],將該欄重新命名。然後輸入 ProductCategory,再按下 Enter。

  13. 按一下 [設計] > [欄] > [新增]

  14. 在表格上方的資料編輯列中,輸入以下公式,然後按下 Enter 接受公式。

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. 以滑鼠右鍵按一下 [CaculatedColumn1] 並選取 [重新命名欄],將該欄重新命名。輸入 ProductSubcategory,然後按下 Enter。

建立階層

大部分的模型都含有固有的階層資料。常見的範例包括行事曆資料、地理資料和產品類別。建立階層是很實用的,因為您可以將一個項目 (階層) 拖曳到報表,而不需要不斷組合與排列相同的欄位。

  1. 在 Power Pivot 中,切換至 [圖表檢視]。然後展開 [DimDate] 表格,好讓您更輕鬆地檢視所有欄位。

  2. 按住 Ctrl,然後按一下 [CalendarYear]、[CalendarQuarter] 和 [CalendarMonth] 欄 (您需要向下捲動表格)。

  3. 選取上述三欄後,以滑鼠右鍵按一下其中一欄,然後再按一下 [建立階層]。隨後會在表格底端建立一個父階層節點 [階層 1],而選取的欄則會複製到該階層下作為子節點。

  4. 輸入 [日期] 作為新階層的名稱。

  5. 新增 [FullDateLabel] 欄至該階層。先以滑鼠右鍵按一下 [FullDateLabel],選取 [新增至階層], 選擇 [日期]。[FullDateLabel] 包含完整日期,包括年月日。確認 [FullDateLabel] 有出現在階層最後面。現在,您有一個包括年、季、月和個別行事曆日的多層階層。

  6. 仍然在 [圖表檢視] 中,指向 [DimProduct] 表格,再按一下表格標題中的 [建立階層] 按鈕。隨後表格底端就會出現一個空白的階層父節點。

  7. 輸入 [產品類別] 作為新階層的名稱。

  8. 若要建立階層子節點,請將 [PoductCategory] 和 [ProductSubcategory] 拖曳到階層。

  9. 以滑鼠右鍵按一下 [ProductName],選取 [新增至階層]。選擇 [產品類別]。

現在您已經知道建立階層的幾個方法了,讓我們把其運用在樞紐分析表上。

  1. 回到 Excel。

  2. 在 Sheet1 (含有樞紐分析表的工作表) 中,移除 [列] 區域中的欄位。

  3. 以 [DimProduct] 中的新 [產品類別] 階層取代它們。

  4. 同樣的,將 [欄] 區域中的 [CalendarYear] 取代為 [DimDate] 中的 [日期] 階層。

現在您瀏覽資料時,就能很明顯感受到使用階層的好處。您可以個別展開並關閉樞紐分析表中的不同區域,更進一步地控制空間的使用。您甚至可以新增單一階層至 [列] 和 [欄],以獲得豐富即時的向下切入畫面,而不必為此堆疊多個欄位。

隱藏欄

現在,您已經建立 [產品類別] 階層,並將其置於 [DimProduct] 中,那麼,[樞紐分析表欄位] 清單已不再需要 [DimProductCategory] 或 [DimProductSubcategory]。在此工作中,您將學習如何隱藏會佔用 [樞紐分析表欄位] 清單空間的無關聯表格與欄。這些欄與表格隱藏起來之後,就可以改善報表體驗,而不會影響到提供資料關聯與計算的模型。

您的瀏覽器不支援視訊。 安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

您可以隱藏個別欄、一個範圍的欄,或是整個表格。表格名稱和欄名會呈現灰色,表示使用該模型的報表用戶端是看不到它們的。隱藏的欄在模型中會呈現灰色以指出其狀態,但是在 [資料檢視] 中仍然看得見,因此您可以繼續使用那些欄。

  1. 在 Power Pivot 中,確認已選取 [資料檢視]。

  2. 在底端的索引標籤中,以滑鼠右鍵按一下 [DimProductSubcategory],選取 [在用戶端工具中隱藏]

  3. 針對 [ProductCategories] 重複這些步驟。

  4. 開啟 [DimProduct]。

  5. 以滑鼠右鍵按一下以下各欄,再按一下 [在用戶端工具中隱藏]

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. 複選相鄰的欄。先從 [ClassID] 開始,然後繼續到結尾的 [ProductSubcategory]。以滑鼠右鍵按一下那些項目,將其隱藏起來。

  7. 對其他表格重複這些步驟、移除識別碼、索引鍵,或是報表中不會用到的其他詳細資料。

切換回 Excel 中含有 [樞紐分析表欄位] 清單的 Sheet 1,看看有什麼差異。您會發現表格數目減少了,[DimProduct] 只含有您在分析銷售時最常使用的那些項目。

建立 Power View 報表

樞紐分析表並非唯一受惠於資料模型的報表。您可以使用剛剛建立的同一模型來新增 Power View 工作表,試試其所提供的一些版面配置。

  1. 在 Excel 中,按一下 [插入] > [Power View]

    附註:  如果這是您第一次在這部電腦上使用 Power View,系統會提示您先啟用該增益集並安裝 Silverlight。

  2. 在 Power View 欄位中,按一下 [FactSales] 表格旁的箭號,再按一下 [SalesAmount]。

  3. 展開 [Geography] 表格,再按一下 [RegionCountryName]。

  4. 在功能區中按一下 [地圖]

  5. 隨後就會出現地圖報表。您可以拖曳邊角來調整其大小。地圖上各種大小的藍色圓圈代表不同國家或地區的銷售績效。

最佳化 Power View 報表

只要在模型做一點小小的變化,就可以在設計 Power View 報表時獲得更直覺式的回應。在此工作中,您將新增數個製造商的網站 URL,然後將資料分類為網址,讓 URL 位址顯示為連結。

第一步,將 URL 新增到活頁簿。

  1. 在 Excel 中開啟新的工作表,然後複製下列值:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

冒險工廠

http://www.fabrikam.com

皓國廣兌

  1. 將儲存格的格式設定為表格,然後將該表格命名為 URL。

  2. 在 [URL] 與內含製造商名稱的表格 (DimProduct) 之間建立關聯:

    1. 按一下 [資料] > [關聯圖]。隨即出現 [建立關聯] 對話方塊。

    2. 按一下 [新增]

    3. 在 [表格] 中選取 [DimProduct]。

    4. 在 [欄] 中選取 [Manufacturer]。

    5. 在 [關聯表格] 中選取 [URL]。

    6. 在 [關聯欄 (主要)] 中選取 [ManufacturerID]。

若要比較前後結果,請先建立一份新的 Power View 報表,然後新增 FactSales | SalesAmount、dimProduct | Manufacturer 和 URL | ManufacturerURL 至報表。請注意,URL 會顯示為靜態文字。

若要將 URL 轉譯為作用中的超連結,必須分類。若要將欄分類,必須使用 Power Pivot。

  1. 在 Power Pivot 中,開啟 [URL]。

  2. 選取 [ManufacturerURL]。

  3. 按一下 [進階] > [報表屬性] > [資料類別: 未分類]

  4. 按一下向下箭號。

  5. 選取 [網址]。

  6. 在 Excel 中,按一下 [插入] > [Power View]

  7. 在 Power View 欄位中,選取 FactSales | SalesAmount、dimProduct | Manufacturer 和 URL | ManufacturerURL。這次,URL 會顯示為實際的超連結。

其他 Power View 最佳化作業還包括為每個表格定義預設的欄位集,以及設定屬性來決定重複資料列要彙總列出或個別列出。詳細資訊請參閱設定 Power View 報表的預設欄位集設定 Power View 報表的資料表行為屬性

建立計算欄位

在第二個工作 (使用樞紐分析表瀏覽資料) 當中,您在 [樞紐分析表欄位] 清單中按了 [SalesAmount] 欄位。由於 [SalesAmount] 是一個數字欄,因此會自動置於樞紐分析表的 [值] 區域中。如此一來,無論套用哪一個篩選,[SalesAmount] 的總和都可以用來計算銷售額。在這個案例中,一開始並沒有任何篩選,但接著就有 [CalendarYear]、[ProductSubcategoryName] 和 [BrandName]。

您實際上做的是建立一個隱含的計算欄位,以方便您根據產品類別、地區和日期等其他欄位,分析 [FactSales] 表格的銷售額。當您將某個欄位拖曳到 [值] 區域,或是按一下某個數字欄位時 (就像您對 [SalesAmount] 執行的作業),Excel 就會建立一個隱含的計算欄位。隱含的計算欄位是使用 SUM、COUNT 和 AVERAGE 等標準彙總函數的公式,會自動建立供您使用。

另外,還有其他類型的計算欄位。您可以在 Power Pivot 中建立明確的計算欄位。隱含的計算欄位只能用於其建立所在的樞紐分析表,但明確計算欄位不同,可以用於活頁簿中的任何樞紐分析表,或是任何使用資料模型作為資料來源的報表。在 Power Pivot 中建立的明確計算欄位後,您就可以使用 [自動加總],以標準彙總自動建立計算欄位,或是使用以資料分析運算式 (DAX) 建立的公式,來建立自己的計算欄位。

您可以想見,建立計算欄位能協助您以絕佳且強大的方式分析資料,所以讓我們開始學習建立計算欄位吧。

使用 [自動加總] 時,可以輕鬆地在 中建立計算欄位。Power Pivot

  1. [FactSales] 表格中,按一下 [Profit] 欄。

  2. 按一下 [計算] > [自動加總]。請注意,在計算區域的 [Profit] 欄下的儲存格中,已經自動建立了一個名為 [Sum of Profit] 的新計算欄位。

  3. 在 Excel 的 Sheet1 中,在欄位清單的 [FactSales] 按一下 [Sum of Profit]

這樣就可以了!在 Power Pivot 使用標準彙總建立計算欄位就是這麼簡單。如您所見,不出幾分鐘的時間,就能建立一個 [SUM of Profit] 計算欄位,並且將其新增到樞紐分析表,以便您根據套用的篩選來分析利潤。在這個案例中,您會看到 [產品類別] 和 [日期] 階層所篩選的 [Sum of Profit]。

但是,萬一您需要做更詳細的分析,例如特定管道、產品或類別的銷售數量,這時候該怎麼辦? 這時候,您就必須建立另一個計算欄位,以根據您套用的篩選來計算列數,而且必須為 [FactSales] 表格中各銷售各建立一個。

  1. 在 [FactSales] 表格中,按一下 [SalesKey] 欄。

  2. [計算] 中,按一下 [自動加總] 的向下箭頭 > [計數]

  3. 以滑鼠右鍵按一下計算區域中的 [以下資料的項目個數: SalesKey],然後選取 [重新命名],以重新命名新的計算欄位。 輸入 Count,再按下 Enter。

  4. 在 Excel 的 Sheet1 中,在欄位清單的 [FactSales]按一下 [Count]

請注意,這時候樞紐分析表中就會新增一個新欄 [Count],根據您套用的篩選來顯示銷售數目。就像使用 [Sum of Profit] 計算欄位一樣,您會看到 [Count] 已使用 [產品類別] 和 [日期] 階層篩選。

讓我們再另外建立一個。這一次,您要建立一個計算欄位,計算在某個特定內容或篩選的情況下,佔總銷售額的百分比。不過,與您先前使用 [自動加總] 所建立的計算欄位不同,這一次您要手動輸入公式。

  1. 在 [FactSales] 表格的計算區域中,按一下空白儲存格。提示:左上角的儲存格是開始放置計算欄位的絕佳之處。這裡可讓您比較容易找到。您可以在計算區域移動任何計算欄位。

  2. 在資料編輯列中,輸入並使用 IntelliSense 建立以下公式: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. 按下 ENTER 接受公式。

  4. 在 Excel 的 Sheet1 中,在欄位清單的 [FactSales] 按一下 [所有產品百分比]

  5. 在樞紐分析表中,複選 [所有產品百分比] 欄。

  6. [常用] 索引標籤上,按一下 [數值] > [百分比]。請使用兩位小數位數格式化每一個新欄。

這個新的計算欄位會針對指定的篩選內容,計算佔總銷售額的百分比。在這個案例中,我們的篩選內容仍然是 [產品類別] 和 [日期] 階層。舉個例說,您可以看到產品總銷售額中,電腦所佔的百分比已經逐年增加。

如果您對於 Excel 公式的建立非常熟練,那麼建立計算結果欄和計算欄位的公式就難不倒您了。無論您熟不熟悉 Excel 公式,都可以逐篇閱讀快速入門:在 30 分鐘內瞭解 DAX 的基本概念 (英文) 中的課程,來瞭解 DAX 公式的基本概念。

儲存工作

您可以儲存活頁簿,以便與其他教學課程一起使用,或是日後進一步瀏覽。

後續步驟

雖然可以從 Excel 輕鬆匯入資料,但是使用 Power Pivot 增益集匯入通常更為快速,也更有效率。您可以篩選所匯入的資料,排除您不需要的欄。您也可以選擇要使用查詢建立器或查詢命令來擷取資料。下一步,就是要瞭解這些替代方式:在 Power Pivot 中從資料摘要取得資料,以及從 Analysis Services 或 Power Pivot 匯入資料。

Power View 報表主要搭配使用的資料模型是類似於您剛建立的資料模型。接下來請繼續閱讀,以進一步瞭解 Power View 為 Excel 提供的豐富的資料視覺效果:在 Excel 2013 啟動 Power View,以及 Power View:探索、視覺化,以及展示資料

您可以繼續閱讀下列文章,來強化您的資料模型,做出更好的 Power View 報表:教學課程:針對 Power View 報表最佳化資料模型

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×