Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

匯總是一種折疊、匯總或將數據分組的方式。 當您從數據表或其他數據源的原始數據開始時,數據通常會是平坦的,表示有許多詳細數據,但並未以任何方式加以組織或分組。 缺少摘要或結構可能會讓您難以探索數據中的模式。 數據模型的一個重要部分是定義匯總,以簡化、抽象或摘要模式,以回答特定的商務問題。

大多數的常見匯總,例如使用 AVERAGECOUNTDISTINCTCOUNTMAXMINSUM 的匯總,都可以使用 [自動求和] 以 量值 自動建立。 其他類型的匯總,例如 AVERAGEXCOUNTXCOUNTROWSSUMX會傳回數據表,並且需要使用 DAX) (數據分析運算式建立的公式。

瞭解 Power Pivot中的匯總

選擇 [匯總] 的 群組

匯總數據時,您可以依產品、價格、地區或日期等屬性將數據分組,然後定義適用於群組中所有數據的公式。 例如,當您建立一年的總計時,您正在建立匯總。 如果您接著建立與去年相較的今年比例,並將這些比例呈現為百分比,則會是不同的匯總類型。

決定數據群組的方式是由商務問題所驅動。 例如,匯總可以回答下列問題:

計數   一個月內有多少筆交易?

平均    銷售人員這個月的銷售額代表什麼?

最小值和最大值    以銷售量而言,前五名是哪個銷售區?

若要建立解答這些問題的計算,您必須擁有包含要計算或加總之數位的詳細數據,而且該數值數據必須與您用來整理結果的群組相關聯。

如果數據尚未包含可用於群組的值,例如產品類別或存放區所在地理區域的名稱,建議您新增類別以將群組導入您的數據。 當您在 Excel 中建立群組時,您必須從工作表中的欄中手動輸入或選取您要使用的群組。 不過,在關係型系統中,產品類別等階層通常會儲存在與事實或值數據表不同的數據表中。 通常類別數據表會以某種索引鍵連結至事實數據。 例如,假設您發現您的數據包含產品標識碼,但不包含產品名稱或其類別。 若要將類別新增至平面 Excel 工作表,您必須複製包含類別名稱的欄。 透過 Power Pivot,您可以將產品類別數據表匯入至數據模型、使用數位數據與產品類別清單建立數據表之間的關聯,然後使用類別來群組數據。 如需詳細資訊,請參閱 Create 數據表之間的關聯

選擇匯總的函數

在您識別並新增群組以供使用之後,您必須決定要用於匯總的數學函數。 彙總一詞通常會做為用於匯總之數學或統計運算的同義字,例如加總、平均值、最小值或計數。 不過, Power Pivot 可讓您建立匯總的自定義公式,以及在 Power Pivot 和 Excel 中找到的標準匯總。

例如,假設前述範例中使用的一組值和群組相同,您可以建立可回答下列問題的自定義匯總:

篩選的計數   除了月底維護視窗之外,一個月內有多少筆交易?

使用一段時間平均值的比例    與去年同一個期間相比,銷售額的百分比成長或下滑是什麼?

將最小值和最大值分組    哪些銷售區在每個產品類別或每個促銷促銷中排在最前面?

在公式和數據透視表中新增匯總

當您對於數據應如何分組為有意義的一般概念,以及您想要使用的值時,您可以決定是否要建立數據透視表或在數據表中建立計算。 Power Pivot 擴充和改善 Excel 建立加總、計數或平均等匯總的原生能力。 您可以在 Power Pivot 視窗內或 Excel 數據透視表區域內,在 Power Pivot 中建立自定義匯總。

  • 計算結果列中,您可以建立匯總以考慮目前列上下文,以從另一個數據表擷取相關數據列,然後加總、計算或平均相關數據列中的這些值。

  • 量值中,您可以建立動態匯總,使用公式中定義的兩個篩選,以及由數據透視表的設計所套用的篩選,以及交叉分析篩選器、欄標題和列名的選取範圍。 您可以使用 [自動求和] 或建立公式,在 Power Pivot 中建立使用標準匯總的量值。 您也可以使用 Excel 數據透視表中的標準匯總來建立隱含量值。

將群組新增至數據透視表

當您設計數據透視表時,將代表群組、類別或階層的欄位拖曳到數據透視表的欄和列區段,以將數據分組。 然後,將包含數值的字段拖曳到值區域,以便進行計算、平均或加總。

如果您新增類別至數據透視表,但類別數據與事實數據無關,您可能會收到錯誤或修正結果。 通常 Power Pivot 會嘗試透過自動偵測並建議關聯來修正問題。 如需詳細資訊,請參閱 在數據透視表中使用關聯。

您也可以將欄位拖曳到交叉分析篩選器,以選取特定數據群組以供檢視。 交叉分析篩選器可讓您在數據透視表中以互動式方式分組、排序及篩選結果。

在公式中使用群組

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

換句話說,如果您想要建立一個公式,將值依類別分組,首先請使用關聯來連接包含詳細數據的數據表和包含類別的數據表,然後建立公式。

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

在匯總中使用篩選

Power Pivot 中的新功能是將篩選套用至數據域和表格,不僅可在使用者介面和數據透視表或圖表中,而且在您用來計算匯總的公式中。 您可以在計算結果列和 s 的公式中使用篩選。

例如,在新的 DAX 匯總函數中,您可以指定整個數據表做為自變數,而不是指定要加總或計算的值。 如果您沒有將任何篩選套用到該數據表,匯總函數會對應數據表指定欄中的所有值。 不過,在 DAX 中,您可以在數據表上建立動態或靜態篩選,讓匯總根據篩選條件和目前上下文而對不同的數據子集運作。

藉由結合公式中的條件和篩選,您可以建立匯總,這些匯總會根據公式中提供的值而有所變更,或是根據數據透視表中列名和欄名的選取範圍而有所變更。

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

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

下表列出 Excel 提供的一些標準匯總函數,並提供在 Power Pivot中實作這些函數的連結。 這些函數的 DAX 版本運作方式與 Excel 版本大致相同,在語法及特定數據類型的處理方式上有些微差異。

標準匯總函數

功能

用途

平均

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

AVERAGEA

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

計數

計算欄中的數值個數。

COUNTA

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

麥克斯

傳回數據行中的最大數值。

MAXX

傳回一組透過數據表評估之表達式的最大值。

MIN

傳回數據行中最小的數值。

MINX

從一組透過數據表評估的表達式傳回最小值。

將欄中的所有數位相加。

DAX 匯總函數

DAX 包含匯總函數,可讓您指定要執行匯總的數據表。 因此,這些函數可讓您建立表達式,以動態定義要匯總的數據,而不只是新增或平均數據行中的值。

下表列出 DAX 中可用的匯總函數。

功能

用途

AVERAGEX

平均顯示一組透過數據表評估的表達式。

COUNTAX

計算一組透過數據表評估的表達式。

COUNTBLANK

計算欄中的空白值數目。

COUNTX

計算表格中的列總數。

COUNTROWS

計算巢狀表格函數傳回的列數,例如篩選函數。

SUMX

傳回一組透過數據表評估的表達式加總。

DAX 和 Excel 匯總函數之間的差異

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

在匯總中評估欄的方式,也與 Excel 處理匯總的方式不同。 範例或許可以協助說明。

假設您想要取得數據表 [銷售額] 中 [金額] 欄中的值總和,因此您建立了下列公式:

=SUM('Sales'[Amount])

在最簡單的案例中,函數會從單一未篩選欄取得值,而結果與 Excel 相同,Excel 一律只會加總數據行中的值,金額。 不過,在 Power Pivot中,公式會解譯為「在 Sales 表格的每一列取得金額中的值,然後加總這些個別的值。 Power Pivot 會評估執行匯總的每一列,併為每一列計算單一刻度值,然後對這些值執行匯總。 因此,如果已將篩選套用至表格,或是根據可能篩選的其他匯總來計算值,公式的結果可能會不同。 如需詳細資訊,請參閱 DAX 公式中的上下文

DAX 時間智慧函數

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

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

功能

用途

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

計算指定期間之行事歷結尾的值。

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

計算指定期間之前該期間之行事歷結尾的值。

TOTALMTD

TOTALYTD

TOTALQTD

計算期間第一天開始並在指定日期欄中最晚日期結束之間隔的值。

時間智慧函數區段中的其他函數 (時間智慧函 數) 是可用來擷取日期或自定義日期範圍以用於匯總的函數。 例如,您可以使用 DATESINPERIOD 函數傳回某個日期範圍,並使用該組日期做為另一個函數的自變數,來計算這些日期的自定義匯總。

Need more help?

Want more options?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。