Power Pivot 中的彙總 (機器翻譯)

[匯總] 是一種將資料折迭、匯總或分組的方式。 當您從資料表或其他資料來源開始原始資料時,資料通常是單層的,這表示有許多詳細資料,但尚未以任何方式組織或分組。 缺乏 [摘要] 或 [結構] 可能會使您難以發現資料中的模式。 資料模型的一個重要部分是定義匯總,以簡化、摘要或摘要模式,以回應特定的商業問題。

大多數常見的聚合(例如使用AVERAGECOUNTDISTINCTCOUNTMAXMIN或 SUM 的匯總),都可以使用 [自動加總] 來自動建立量值。 其他類型的聚合(例如AVERAGEXCOUNTXCOUNTROWSSUMX)會傳回資料表,且需要使用資料分析運算式(DAX)建立的公式。

瞭解 Power Pivot中的匯總

選擇群組以進行匯總

當您匯總資料時,您會依屬性(例如產品、價格、區域或日期)來分組資料,然後定義可在群組中的所有資料上運作的公式。 例如,當您建立一年的總計時,就表示您要建立匯總。 如果您在前一年建立此年的比率,並將它們顯示為百分比,就是不同的匯總類型。

如何將資料分組的決定是由業務問題所驅動。 例如,匯總可以回答下列問題:

算是   每個月有多少筆交易?

水準    銷售人員在本月的平均銷售額是什麼?

最小值與最大值    哪些銷售學區是售出單位的前五個?

若要建立回答這些問題的計算,您必須有包含要計算或加總之數位的詳細資料,而且該數位資料必須與您用來組織結果的群組有關聯的方式。

如果資料沒有包含您可以用來進行分組的值(例如產品類別或存放區所在地理區域的名稱),您可能會想要透過新增類別將群組引入您的資料。 當您在 Excel 中建立群組時,您必須手動輸入或選取您要在工作表中的欄之間使用的群組。 不過,在關聯式系統中,諸如產品類別之類的階層通常會儲存在不同的資料表中,而不是事實資料表或值表。 通常,類別資料表是依某種鍵與事實資料的連結。 例如,假設您發現您的資料包含產品識別碼,但不包含產品或其類別的名稱。 若要將類別新增至一般的 Excel 工作表,您必須在包含類別名稱的欄中複製。 您可以使用 Power Pivot將產品類別資料表匯入您的資料模型,在包含數位資料的資料表與產品類別清單之間建立關聯,然後使用類別將資料分組。 如需詳細資訊,請參閱建立資料表之間的關聯

選擇匯總函數

在您發現並新增要使用的群組之後,您必須決定要使用哪一種數學函數來進行匯總。 通常會將字集合用作匯總中所使用之數學或統計運算的同義字,例如總和、平均值、最小值或計數。 不過, Power Pivot 可讓您建立匯總的自訂公式,以及在 Power Pivot 和 Excel 中找到的標準匯總。

例如,在前面的範例中,您可以建立自訂匯總,以回答下列問題:

篩選的計數   每個月有多少筆交易,不包括月份結束維護視窗?

使用平均時間的比率    去年的銷售百分比增長或銷量下降了哪一年?

群組的最小值與最大值    針對每個產品類別,或針對每個銷售促銷,哪些銷售地區排名在最前面?

在公式和樞紐分析表中新增匯總

當您大致瞭解如何將資料組成群組,以及您想要使用的值時,您可以決定是否要建立樞紐分析表,或在表格中建立計算。 Power Pivot 擴大並改善 Excel 建立匯總(例如加總、計數或平均值)的原生能力。 您可以在 [ Power Pivot ] 視窗中,或在 Excel 樞紐分析表區域內,在 Power Pivot 中建立自訂匯總。

  • 計算結果欄中,您可以建立匯總,將目前的資料列內容納入來源,以從另一個資料表中取得相關資料列,然後在相關列中加總、計算或平均這些值。

  • 量值中,您可以建立動態匯總,使用公式中定義的篩選,以及樞紐分析表的設計所強加的篩選,以及交叉分析篩選器、欄標題及列標題的選取範圍。 您可以使用 [自動加總] 或建立公式,在 Power Pivot 中建立使用標準聚合的量值。 您也可以在 Excel 中使用樞紐分析表中的標準匯總來建立隱含的量值。

新增群組至樞紐分析表

當您設計樞紐分析表時,會將代表群組、類別或階層的欄位拖曳到樞紐分析表的 [欄和列] 區段,以將資料分組。 然後,將包含數值的欄位拖曳到 [值] 區域中,讓它們可以計數、平均值或求和。

如果您在樞紐分析表中新增類別,但類別資料與事實資料無關,您可能會收到錯誤或 peculiar 結果。 通常 Power Pivot 會自動偵測與建議關聯,以嘗試修正問題。 如需詳細資訊,請參閱使用樞紐分析表中的關聯

您也可以將欄位拖曳到交叉分析篩選器,以選取特定資料群組以供查看。 交叉分析篩選器可讓您以互動式方式分組、排序及篩選樞紐分析表中的結果。

在公式中使用分組

您也可以使用群組和類別來匯總儲存在表格中的資料,方法是建立資料表之間的關聯,然後建立可利用這些關聯來查詢相關值的公式。

換句話說,如果您想要建立根據類別來分組值的公式,您必須先使用關聯來連接包含詳細資料的資料表,以及包含該類別的資料表,然後建立公式。

如需有關如何建立使用查閱之公式的詳細資訊,請參閱Power Pivot 公式中的查閱

在聚合中使用篩選

Power Pivot 中的新功能就是將篩選套用至資料行和資料表的功能,不僅是在使用者介面和樞紐分析表或圖表中,也能在您用來計算匯總的公式中使用。 在計算結果欄和 s 中,您可以在公式中使用篩選。

例如,在新的 DAX 彙總函式中,您可以將整個資料表指定為引數,而不是指定要加總或計算的值。 如果您沒有將任何篩選套用到該資料表,匯總函數將會針對資料表中指定資料行的所有值運作。 不過,在 DAX 中,您可以在資料表上建立動態或靜態篩選,讓匯總根據篩選準則與目前的內容,針對不同的資料子集進行運算。

在公式中結合條件和篩選,您可以建立會根據公式中提供的值而變更的匯總,或根據樞紐分析表中的列標題和欄標題的選取範圍來變更。

如需詳細資訊,請參閱篩選公式中的資料

比較 Excel 匯總函數與 DAX 匯總函數

下表列出 Excel 提供的一些標準匯總函數,並提供 Power Pivot中這些函數實現的連結。 DAX 版本的這些函數的行為與 Excel 版本基本相同,在某些資料類型的語法和處理方面有一些細微的差異。

標準匯總函數

函數

用途

平均值

傳回資料行中所有數字的平均 (算術平均)。

AVERAGEA

傳回資料行中所有值的平均值(算術平均值)。 處理文字和非數值的值。

COUNT

計算資料行中數值的數目。

COUNTA

計算資料行中非空白值的數目。

MAX

傳回資料行中的最大數值。

MAXX

傳回一組運算式的最大值(透過資料表評估)。

最小

傳回資料行中的最小數值。

MINX

傳回一組依據資料表求得的運算式中的最小值。

將資料行中的所有數位相加。

DAX 匯總函數

DAX 包含可讓您指定要執行匯總之資料表的匯總函數。 因此,這些函數可讓您建立可動態定義要匯總之資料的運算式,而不只是將資料行中的值相加或平均加總。

下表列出 DAX 中可用的彙總函式。

函數

用途

AVERAGEX

計算在資料表上評估的一組運算式的平均值。

COUNTAX

計算在資料表上評估的一組運算式。

COUNTBLANK

計算欄中空白值的數目。

COUNTX

計算表格中的列總數。

COUNTROWS

計算嵌套資料表函數所傳回的列數,例如 filter 函數。

SUMX

傳回在資料表上評估的一組運算式的總和。

DAX 與 Excel 匯總函數的差異

雖然這些函數的名稱與 Excel 中的對應專案相同,但它們會利用 Power Pivot的記憶體中分析引擎,並重新編寫以搭配表格和欄使用。 您無法在 Excel 活頁簿中使用 DAX 公式,反之亦然。 它們只能在 [ Power Pivot ] 視窗和樞紐分析表中,以 Power Pivot 資料為基礎。 此外,雖然函數的名稱相同,但行為可能稍有不同。 如需詳細資訊,請參閱個別函數參考主題。

在匯總中評估資料行的方式,也與 Excel 處理聚合的方式不同。 範例可能會協助說明。

假設您想要取得表格銷售額中 [金額] 資料行的值總和,所以您要建立下列公式:

=SUM('Sales'[Amount])

在最簡單的情況下,函數會從單一未篩選的資料行中取得值,結果與在 Excel 中相同,只會在資料行中一直加總值。 不過,在 Power Pivot中,公式會轉譯為「取得 Sales 資料表每個資料列的金額值,然後再加總這些個別值。 Power Pivot 評估匯總所執行的每個資料列,並針對每個資料列計算單一標量值,然後依據這些值執行匯總。 因此,如果篩選已套用至資料表,或者根據可能篩選的其他匯總來計算值,則公式的結果可能會不同。 如需詳細資訊,請參閱DAX 公式中的內容

DAX 時間智慧函數

除了上一節中所述的資料表匯總函數之外,DAX 還包含可搭配您指定之日期和時間的匯總函數,以提供內建時間智慧。 這些函數會使用日期範圍來取得相關值並匯總值。 您也可以比較不同日期範圍內的值。

下表列出可用於匯總的時間智慧函數。

函數

用途

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

在指定期間的行事曆結尾計算值。

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

計算指定期間前期間行事曆結束的值。

TOTALMTD

TOTALYTD

TOTALQTD

在期間的第一天開始,到指定日期資料行中的最晚日期,計算值的間隔。

時間智慧函數區段中的其他函數(時間智慧函數)是可用於檢索要在匯總中使用之日期或自訂日期範圍的函數。 例如,您可以使用 DATESINPERIOD 函數傳回一個日期範圍,並將該日期集做為另一個函數的引數,以便只計算那些日期的自訂匯總。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×