PowerPivot 中的彙總

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

「彙總」(Aggregation) 是一種摺疊、摘要或群組資料的方式。當您從資料表或其他資料來源的原始資料開始時,通常都是一般資料,也就是內容雖極為詳盡,卻未曾以任何方式整理或分組。這種欠缺摘要或結構的情況會使資料中的模式難以發現。資料模型化的一個重要部分就是定義簡化、擷取或摘要模式的彙總,以回答特定的商務問題。

最常見的彙總,例如使用AVERAGECOUNTDISTINCTCOUNT最大值最小值加總可以建立在量值會自動使用自動加總。其他類型的彙總,例如AVERAGEXCOUNTXCOUNTROWSSUMX傳回資料表,並要求建立使用資料分析運算式 (DAX)公式。

瞭解 Power Pivot 中的彙總

選擇彙總的群組

彙總資料時,可依產品、價格、地區或日期等屬性進行資料分組,然後定義適用於群組中所有資料的公式。例如,建立一年的總計時,就是建立彙總。如果之後又建立這一年與前一年的比率,然後以百分比呈現,這就是不同類型的彙總。

如何群組資料的決定是受商務問題左右。例如,彙總可以回答下列問題:

計數   :一個月之中有多少次交易?

平均值   :本月份每個銷售人員的平均銷售量是多少?

最小值與最大值   :以銷售單位數來說,哪些銷售區域是前五大區域?

若要建立回答上述問題的計算,必須有包含要計算或加總之數字的詳細資料,而且該項數值資料必須與您要用來組織結果的群組有某種關聯。

如果資料已經沒有值,可用來組成群組,例如產品類別或儲存所在的地理區域的名稱,您可能想要向您的資料群組介紹藉由新增類別。當您在 Excel 中的群組時,您必須手動輸入或選取您想要使用從您的工作表中的資料行中的群組。不過,在關聯式系統中,例如產品類別的階層通常會儲存在不同於交易的資料表或值] 表格。通常 category 資料表已連結至交易資料以某些鍵。例如,假設您找到您的資料包含產品識別碼,但不是產品或其類別的名稱。若要新增類別一般的 Excel 工作表,您必須複製中所包含的類別名稱的資料行。與Power Pivot,您可以匯入至您的資料模型的 product category 資料表、 建立數字資料的資料表和 [產品類別] 清單之間的關聯,然後使用 [類別] 群組中的資料。如需詳細資訊,請參閱建立資料表之間的關聯

選擇彙總的函數

在您識別及新增要使用的群組後,必須決定要使用哪個數學函數來進行彙總。彙總這個詞常作為加總、平均、最小值或計數等數學或統計運算之彙總的同義詞。但是,除了 Power Pivot 和 Excel 中包含的標準彙總外,Power Pivot 還可讓您針對彙總建立自訂公式。

例如,以上一個範例中所使用的同一組值和群組,您可以建立自訂彙總來回答下列問題:

篩選的計數   :除了月底維護期間以外,一個月中有多少次交易?

使用一段時間的平均值算出比例   :與去年同期銷售量相比,成長或衰退百分比是多少?

群組的最小值和最大值   :以每個產品類別或每項銷售促銷來說,哪個銷售區域排行最高?

將彙總加入至公式和樞紐分析表

當您大概知道要如何分組資料才是有意義的,以及您要使用的值時,即可決定要建立樞紐分析表或在資料表中建立計算。Excel 原本便可建立彙總 (例如加總、計數或平均),但 Power Pivot 能進一步擴充及改進這項能力。您可以在 Power Pivot 中的 Power Pivot 視窗或 Excel 樞紐分析表區域中建立自訂彙總。

  • 計算結果欄中,您可以建立將目前資料列內容列入考量的彙總,以便從另一個資料表中擷取相關的資料列,然後加總、計數或平均相關資料列中的這些值。

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

將群組加入至樞紐分析表

當您設計樞紐分析表時,可以將代表群組、類別目錄或階層的欄位拖曳到樞紐分析表的資料行和資料列區段來進行資料分組。然後您可以將包含數值的欄位拖曳到值區域之中,以便進行計數、平均或加總。

如果您將類別新增至樞紐分析表,但是類別資料與事實資料不相關,可能就會計算出錯誤或怪異的結果。Power Pivot 通常都會自動偵測並建議關聯性,以嘗試修正問題。如需詳細資訊,請參閱使用樞紐分析表中的關聯性

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

在公式中使用群組

您也可以透過建立資料表之間的關聯性,然後建立利用這些關聯性來查閱相關值的公式,藉此使用群組和類別來彙總儲存在資料表中的資料。

換句話說,如果您想要建立的公式是依類別目錄將值分組,就要先使用關聯性連接包含詳細資料的資料表與包含類別目錄的資料表,然後再建置公式。

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

在彙總中使用篩選

Power Pivot中的新功能是將篩選套用到資料行和資料表的資料,而不只是在使用者介面和樞紐分析表或圖表中非常您用來計算彙總的公式中的功能。篩選可以用於公式計算結果欄中,s 中的資料。

例如,在新的 DAX 彙總函式中,不是指定要進行加總或計數的值做為引數,而可以指定整個資料表做為引數。如果沒有套用任何篩選至該資料表,彙總函式會根據資料表中所指定資料行中的所有值進行計算。但是在 DAX 中,您可以在資料表上建立動態或靜態的篩選,以便讓彙總依篩選條件和目前內容而定,根據不同的資料子集進行運算。

您可以在公式中結合條件和篩選,建立依公式中所提供值變更的彙總,或者建立依樞紐分析表中資料列標題與資料行標題選擇而變更的彙總。

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

Excel 彙總函式和 DAX 彙總函式的比較

下表列出 Excel 提供的一些標準彙總函數,並提供在 Power Pivot 中實作這些函數的連結。這些函數的 DAX 版本與 Excel 版本的行為大部分相同,但在某些資料類型的語法和處理上有些微差異。

標準彙總函式

函數

使用

AVERAGE

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

AVERAGEA

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

COUNT

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

COUNTA

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

MAX

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

MAXX

傳回對資料表進行評估的一組運算式的最大值。

MIN

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

MINX

傳回對資料表進行評估的一組運算式的最小值。

SUM

將資料行的所有數字相加。

DAX 彙總函式

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

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

函數

使用

AVERAGEX

計算透過資料表評估之一組運算式的平均值。

COUNTAX

計算對資料表進行評估的一組運算式的數目。

COUNTBLANK

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

COUNTX

計算資料表中資料列的總數。

COUNTROWS

計算從巢狀資料表函數 (例如 filter 函數) 傳回的資料列數目。

SUMX

傳回對資料表進行評估的一組運算式的加總。

DAX 和 Excel 彙總函式之間的差異

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

資料行在彙總時進行評估的方式也和 Excel 處理彙總的方式不同。舉例說明應能更有助於理解。

假設您想要加總 [銷售] 資料表中 [金額] 欄的值,就要建立下列公式:

=SUM('Sales'[Amount])

在最簡單的情況下,函數會從單一未篩選的欄取得值,其結果與在 Excel 中相同,也就是一律只加總 [金額] 欄中的值。不過,在 Power Pivot 中,此公式會解譯為「取得 [銷售] 資料表中每一列之 [金額] 中的值,然後加總這些個別的值」。Power Pivot 會評估用來執行彙總的每一列,然後計算每一列的單一純量值,再以這些值執行彙總。因此,如果資料表已經套用篩選,或是值計算所依據的其他彙總可能經過篩選,公式的結果便不盡然相同。如需詳細資訊,請參閱DAX 公式中的內容

DAX 時間智慧函數

除了上一節中說明的資料表彙總函式以外,DAX 也有可搭配您所指定之日期和時間使用的彙總函式,以提供內建「時間智慧」(Time Intelligence)。這些函數使用日期範圍,取得相關的值並彙總這些值。您也可以比較跨日期範圍的值。

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

函數

使用

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

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

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

在特定期間之前的期間行事曆結尾計算值。

TOTALMTD

TOTALYTD

TOTALQTD

針對始於期間的第一天且結束於指定之日期資料行中的最後日期,計算落在該間隔內的值。

您還可以使用<時間智慧函數>一節 (時間智慧函數) 中的其他函數,擷取日期或自訂日期範圍以便用於彙總之中。例如,您可以使用 DATESINPERIOD 函數傳回特定的日期範圍,然後將該組日期當做其他函數的引數使用,從而建立只針對這些日期執行的自訂彙總。

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×