Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

在樞紐分析表中,您可以在值欄位中使用彙總函數,從基本來源資料合併值。 如果彙總函數和自訂計算無法提供您想要的結果,您可以在導出欄位和計算項目中建立自己的公式。 例如,您可以使用銷售佣金的公式新增計算項目,該公式在每個地區會互不相同。 樞紐分析表則會在小計和總計中自動包含佣金。

另一種計算方法是使用 Power Pivot 中的量值,可使用 Data Analysis Expressions (DAX) 公式建立它。 如需詳細資訊,請參閱 建立 Power Pivot 中的量值

樞紐分析表提供多種計算資料的方式。 了解可用的計算方法、來源資料類型對計算有何影響,以及如何在樞紐分析表和樞紐分析圖中使用公式。

若要在樞紐分析表中計算值,您可以使用任何或所有下列類型的計算方法:

  • 在值欄位中的彙總函數    值區域中的資料會彙總樞紐分析表中的基本來源資料。 例如,下列來源資料:

    樞紐分析表來源資料的範例

  • 產生下列樞紐分析表和樞紐分析圖。 如果您從樞紐分析表中的資料建立樞紐分析圖,該樞紐分析圖中的值會反映相關的樞紐分析表中的計算。

    樞紐分析表範例

    樞紐分析圖範例

  • 在樞紐分析表中,[月] 欄的欄位提供 [三月] 和 [四月] 這些項目。 [地區] 列的欄位提供 [北區]、[南區]、[東區] 和 [西區] 這些欄位。 [四月] 欄和 [北區] 列交集處的值是總銷貨收入,而這是來自有 [四月] 的月值和 [北區] 的地區值之來源資料中的記錄。

  • 在樞紐分析圖中,[地區] 欄位可能是將 [北區]、[南區]、[東區] 和 [西區] 顯示為類別的類別欄位。 [月] 欄位可能是將 [三月]、[四月] 和 [五月] 這些項目顯示為圖例中表示之數列的數列欄位。 名為 [銷售總合] 的值欄位可能包含資料標記,表示每個月每個地區中的總收入。 例如,一個資料標記可能使用其垂直 (數值) 座標軸表示 [北區] 地區中的 [四月] 總銷售量。

  • 若要計算值欄位,您可以在所有來源資料類型中使用下列彙總函數,但線上分析處理 (OLAP) 來源資料除外。

    函數

    摘要說明

    Sum

    值的總和。 這是數值資料的預設函數。

    Count

    資料值的總數。 Count 彙總函數的運作方式與 COUNTA 函數相同。 Count 是數字以外資料的預設函數。

    Average

    數值的平均值。

    Max

    最大的數值。

    Min

    最小的數值。

    Product

    數值的乘積。

    Count Nums

    數字資料值的個數。 Count Nums 彙總函數的運作方式與 COUNT 函數相同。

    StDev

    母體標準差的估計值,其中的樣本是整個母體的子集合。

    StDevp

    母體的標準差,母體是要彙總的所有資料。

    Var

    母體變異數的估計值,其中的樣本是整個母體的子集合。

    Varp

    母體的變異數,母體是要彙總的所有資料。

  • 自訂計算    自訂計算 會根據資料區域中的其他項目或儲存格顯示值。 例如,您可以將 銷售量加總資料欄位中的值顯示為三月銷售量的百分比,或顯示為欄位中之項目的計算加總。

    您可以在值欄位中的自訂計算中使用下列函數。

    函數

    結果

    無計算

    顯示在欄位中輸入的值。

    總計百分比

    以報表中所有值或資料點之總計百分比的方式顯示值。

    欄總和百分比

    以佔欄或數列總計百分比的方式,顯示各欄或數列中的所有值。

    列總和百分比

    以佔列或類別總計百分比的方式,顯示各列或類別中的值。

    百分比

    以 [基本欄位] 中之 [基本項目] 值百分比的方式顯示值。

    父項列總和百分比

    計算值如下:

    (項目值) / (列中父項目值)

    父項欄總和百分比

    計算值如下:

    (項目值) / (欄中父項目值)

    父項總和百分比

    計算值如下:

    (項目值) / (所選 [基本欄位] 中父項目值)

    差異

    以與 [基本欄位] 中之 [基本項目] 值差異的方式顯示值。

    差異百分比

    以與 [基本欄位] 中之 [基本項目] 值百分比差異的方式顯示值。

    計算加總至

    將 [基本欄位] 中連續項目值做為計算加總的值。

    計算加總至百分比

    計算顯示為計算加總百分比的 [基本欄位] 中的連續項目值。

    最小到最大排列

    所選取的值在特定欄位中的順位,欄位中最小的項目列為 1,值愈大,所指派的順位值則愈後面。

    最大到最小排列

    所選取的值在特定欄位中的順位,欄位中最大的項目列為 1,值愈小,所指派的順位值則愈後面。

    索引

    計算值如下:

    ((儲存格內數值) x (總計的總計)) / ((列總計) x (欄總計))

  • 公式    如果彙總函數和自訂計算無法提供您想要的結果,您可以在導出欄位和計算項目中建立自己的公式。 例如,您可以使用銷售佣金的公式新增計算項目,該公式在每個地區會互不相同。 樞紐分析表則會在小計和總計中自動包含佣金。

樞紐分析表中可使用的計算和選項,取決於來源資料是否來自 OLAP 資料庫或非 OLAP 資料來源。

  • 基於 OLAP 來源資料計算​​    針對從 OLAP Cube 建立的樞紐分析表,在 Excel 顯示結果之前,會先在 OLAP 伺服器上預先計算摘要值。 您無法變更在樞紐分析表中計算這些預先計算值的方式。 例如,您無法變更用來計算資料欄位或小計的彙總函數,或新增導出欄位或計算項目。

    此外,如果 OLAP 伺服器提供導出欄位 (稱為導出成員),您則會在樞紐分析表欄位清單中看到這些欄位。 您也會看到以 Visual Basic for Applications (VBA) 撰寫之巨集建立並儲存在活頁簿中的任何導出欄位和計算項目,但您無法變更這些欄位或項目。 如果您需要其他計算類型,請連絡 OLAP 資料庫系統管理員。

    針對 OLAP 來源資料,您可以在計算小計和總計時包含或排除隱藏項目的值。

  • 基於非 OLAP 來源資料計算​​    在基於其他外部資料或工作表資料類型的樞紐分析表中,Excel 使用 Sum 彙總函數來計算包含數值資料的值欄位,以及使用 Count 彙總函數來計算包含文字的資料欄位。 您可以選擇不同的彙總函數 (例如 Average、Max 或 Min),進一步分析及自訂您的資料。 您也可以建立導出欄位或欄位中的計算項目,藉此自行建立使用樞紐分析表元素或其他工作表資料的公式。

您只能在基於非 OLAP 來源資料的樞紐分析表中建立公式。 您無法在基於 OLAP 資料庫的樞紐分析表中使用公式。 當您在樞紐分析表中使用公式時,您應了解下列公式語法規則和公式行為:

  • 樞紐分析表公式元素    在您為導出欄位和計算項目建立的公式中,您可以如同在其他工作表公式中一樣使用運算子和運算式。 您可以使用常數及參照樞紐分析表中的資料,但您不能使用儲存格參照或定義的名稱。 您不能使用要求儲存格參照或定義的名稱 (引數形式) 的工作表函數,而且您不能使用陣列函數。

  • 欄位和項目名稱    Excel 使用欄位和項目名稱以在公式中識別樞紐分析表的這些元素。 在下列範例中,C3:C9 範圍中的資料使用乳製品欄位名稱。 根據「乳製品」銷售量預估新產品銷售量的類型欄位中的計算項目,可能會使用這樣的公式:=Dairy * 115%

    樞紐分析表範例

    附註: 在樞紐分析圖中,欄位名稱會顯示在樞紐分析表欄位清單中,而且可以在每個欄位下拉式清單中看到項目名稱。 請勿將這些名稱與您在圖表提示中看到的名稱混淆,它們反映的是數列和資料點名稱。

  • 公式是在總和尚運算,而非個別的記錄    導出欄位的公式是在公式中任何欄位的基本資料加總上運算。 例如,=Sales * 1.2 導出欄位公式將每種類型和地區的銷售量加總乘以 1.2;它不會將每個個別銷售量乘以 1.2 然後再加總相乘的金額。

    計算項目的公式會在個別記錄上運算。 例如,=Dairy *115% 計算項目公式將每個個別「乳製品」銷售量乘以 115%,之後相乘的金額會在「值」區域中彙總在一起。

  • 名稱中的空格、數字和符號    在包含超過一個欄位的名稱中,欄位可以是任何順序。 在上述範例中, C6:D6 儲存格可以是 「四月北部」「北部四月」。 使用單引號來括住超過一個字的名稱或包含數字或符號的名稱。

  • 合計    公式無法參照合計 (例如範例中的三月合計四月合計總計)。

  • 項目參照中的欄位名稱    您可以在項目的參照中包含欄位名稱。 項目名稱必須使用方括號括住,例如地區 [北部]。 當樞紐分析表的兩個不同欄位中的兩個項目具有相同名稱, 請使用此格式防止 #NAME? 錯誤。 例如,如果樞紐分析表有一個在「類型」欄位中名為「肉類」的項目,以及另一個在「類別」欄位中名為「肉類」的項目, 您可使用 類型 [肉類]類別 [肉類] 的方式參照項目,以防止 #NAME? 錯誤。

  • 根據位置參照項目    您可以根據樞紐分析表中目前排序及顯示的項目位置來參照項目。 類型 [1]乳製品,而類型 [2]海鮮。 項目位置變更或顯示或隱藏不同的項目時,以此方式參照的項目可隨時隨之變更。 隱藏的項目不會計入此索引中。

    您可以使用相對位置來參照項目。 決定位置的方式是相對於包含公式的計算項目。 如果南部是目前的地區,地區[-1] 則是北部;如果北部是目前的地區,地區[+1] 則是南部。 例如,計算項目可以使用 =Region[-1] * 3% 公式。 如果您指定的位置在欄位中的第一個項目前面或最後一個項目後面,公式則會產生 #REF! 錯誤。

若要在樞紐分析圖中使用公式,您須在關聯的樞紐分析表中建立公式,您可在其中查看組成資料的個別值,然後可以在樞紐分析圖中以圖形的方式檢視結果。

例如,下列樞紐分析圖顯示每個地區的每個銷售人員的銷售量:

顯示每個地區各銷售員的銷售額樞紐分析圖

若要看看銷售量增加百分之 10 會呈現什麼樣子的話,您可以在使用 =Sales * 110% 此類公式的關聯樞紐分析表中建立導出欄位。

結果會立即顯示在樞紐分析圖中 (如下列圖表所示):

顯示每個地區銷售額增長百分之十後的樞紐分析圖

若要查看北部地區中銷售量與百分之 8 的交通成本相減的個別資料標記,您可以使用像 =North – (North * 8%) 這樣的公式在「地區」欄位中建立計算項目。

產生的圖表看起來像這樣:

包含計算項目的樞紐分析圖。

不過,銷售人員欄位中建立的計算項目會顯示為圖例中表示的數列,並且在每個類別中以資料點的形式顯示在圖表中。

重要: 您不能在連線到線上分析處理 (OLAP) 資料來源的樞紐分析表中建立公式。

開始之前,請決定您想要使用的是導出欄位,還是欄位中的計算項目。 當您想要在公式中使用來自另一個欄位的資料時,請使用導出欄位。 當您想要讓公式使用來自某個欄位中的一或多個特定項目的資料時,請使用計算項目。

針對計算項目,您可以在逐個儲存格中輸入不同的公式。 例如,如果名為橘郡的計算項目在所有月份中使用 =Oranges * .25 公式,您可以針對「六月」、「七月」和「八月」將公式變更為 =Oranges *.5

如果您有多個計算項目或公式,您可以調整計算順序。

新增導出欄位

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [導出欄位]。

    Excel 功能區影像

  3. 在 [名稱] 方塊中,輸入欄位的名稱。

  4. 在 [公式] 方塊中,輸入欄位的公式。

    若要在公式中使用來自另一個欄位的資料,請按一下 [欄位] 方塊中的欄位,然後按一下 [插入欄位]。 例如,若要在 [銷售量] 欄位的每個值上計算 15% 佣金,您可以輸入 = Sales * 15%

  5. 按一下 [新增]

將計算項目新增到欄位

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 如果欄位中的項目已組成群組,請在 [分析] 索引標籤上,按一下 [群組] 群組中的 [取消群組]

    Excel 功能區影像

  3. 按一下您要新增計算項目的欄位。

  4. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [計算項目]。

    Excel 功能區影像

  5. 在 [名稱] 方塊中,輸入該計算項目的名稱。

  6. 在 [公式] 方塊中,輸入項目的公式。

    若要在公式中使用來自項目的資料,請按一下 [項目] 清單中的項目,然後按一下 [插入項目] (項目必須來自與計算項目相同的欄位)。

  7. 按一下 [新增]。

為計算項目在逐個儲存格中輸入不同的公式

  1. 按一下您要變更公式的儲存格。

    若要變更多個儲存格的公式,請按住 CTRL,然後按一下其他儲存格。

  2. 在資料編輯列中,輸入公式的變更。

調整多個計算項目或公式的計算順序

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [求解順序]。

    Excel 功能區影像

  3. 按一下公式,然後按一下 [往上移] 或 [往下移]。

  4. 請繼續進行,直到公式變成您想要的計算順序為止。

您可以顯示目前樞紐分析表中使用的所有公式清單。

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [顯示公式]。

    Excel 功能區影像

編輯公式之前,請判斷公式是導出欄位還是計算項目。 如果公式是計算項目,也請判斷公式是否為計算項目的唯一項目。

對於計算項目,您可以編輯計算項目之特定儲存格的個別公式。 例如,如果名為 OrangeCalc 的計算項目在所有月份中使用 =Oranges * .25 公式,您可以針對「六月」、「七月」和「八月」將公式變更為 =Oranges *.5

判斷公式是導出欄位還是計算項目

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [顯示公式]。

    Excel 功能區影像

  3. 在公式清單中,尋找您要變更的公式 (列於 [導出欄位] 或 [計算項目] 底下)。

    當算項目有多個公式時,建立 項目 時所輸入的預設公式會在欄 B 中有計算項目名稱。對於計算項目的其他公式,欄 B 包含計算項目名稱和交集項目名稱。

    例如,您可能有一個用於計算項目的預設公式 (名為我的項目),以及另一個用於此項目的公式 (識別為我的項目一月銷售量)。 在樞紐分析表中,您會在「我的項目」列和「一月」欄的「銷售量」儲存格中發現此公式。

  4. 請使用下列其中一種編輯方法繼續進行。

編輯導出欄位的公式

  1. 按一下 [樞紐分析表]。

    隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [導出欄位]。

    Excel 功能區影像

  3. 在 [名稱] 方塊中,選取您要變更公式的導出欄位。

  4. 在 [公式] 方塊中,編輯公式。

  5. 按一下 [修改]。

編輯計算項目的單一公式

  1. 按一下包含計算項目的欄位。

  2. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [計算項目]。

    Excel 功能區影像

  3. 在 [名稱] 方塊中,選取計算項目。

  4. 在 [公式] 方塊中,編輯公式。

  5. 按一下 [修改]。

編輯計算項目之特定儲存格的個別公式

  1. 按一下您要變更公式的儲存格。

    若要變更多個儲存格的公式,請按住 CTRL,然後按一下其他儲存格。

  2. 在資料編輯列中,輸入公式的變更。

    提示: 如果您有多個計算項目或公式,您可以調整計算順序。 如需詳細資訊,請參閱調整多個計算項目或公式的計算順序

附註: 刪除樞紐分析表公式會將它永久移除。 如果您不想要永久移除公式,您可以改成將公式拖出樞紐分析表外,以隱藏欄位或項目。

  1. 判斷公式是導出欄位還是計算項目。

    導出欄位會顯示在樞紐分析表欄位清單中。 計算項目會以項目的形式顯示在其他欄位中。

  2. 執行下列其中一個動作:

    • 若要刪除導出欄位,請按一下樞紐分析表中的任何位置。

    • 若要刪除計算項目,請在樞紐分析表中按一下包含您要刪除之項目的欄位。

      隨後便會顯示 [樞紐分析表工具],另外還有 [分析] 和 [設計] 索引標籤。

  3. 在 [分析] 索引標籤上,按一下 [計算] 群組中的 [欄位、項目與集],然後按一下 [導出欄位] 或 [計算項目]。

    Excel 功能區影像

  4. 在 [名稱] 方塊中,選取您要刪除的欄位或項目。

  5. 按一下 [刪除]。

若要彙總 Excel 網頁版 內樞紐分析表中的數值,您可以使用 Sum、Count、Average 等彙總函數。 根據預設,Sum 函數會用於值欄位中的數值。 您可以根據 OLAP 資料來源檢視及編輯樞紐分析表,但無法在 Excel 網頁版中建立樞紐分析表。

以下說明如何選擇不同的彙總函數:

  1. 按一下樞紐分析表上的任意位置,然後選取 [樞紐分析表] > [欄位清單]。  也可以以滑鼠右鍵按一下樞紐分析表,然後選取 [顯示欄位清單]

  2. 在 [樞紐分析表欄位] 清單的 [值] 下方,按一下 [值] 欄位旁邊的箭號。

    [欄位清單] 中的 [值] 方塊

  3. 按一下 [值欄位設定]。

  4. 選取所需的彙總函數,然後按一下 [確定]

    附註: 樞紐分析表若是根據線上分析處理 (OLAP) 來源資料,則無法使用彙總函數。

    使用此彙總函數

    用於計算

    Sum

    數值的總和。 依預設用於含有數值的值欄位。

    Count

    非空值的個數。 Count 彙總函數的運作方式與 COUNTA 函數相同。 依預設,Count 用於含有非數值或空白的值欄位。

    Average

    數值的平均值。

    Max

    最大的數值。

    Min

    最小的數值。

    Product

    數值的乘積。

    Count Numbers

    含有數字的值之數量 (與 Count 不同,Count 包含非空白值)。

    StDev

    母體標準差的估計值,其中的樣本是整個母體的子集合。

    StDevp

    母體的標準差,母體是要彙總的所有資料。

    Var

    母體變異數的估計值,其中的樣本是整個母體的子集合。

    Varp

    母體的變異數,母體是要彙總的所有資料。

iPad 上的數據透視表適用於在 iPad 版本 2.82.205.0 及更新版本上執行 Excel 的客戶。 若要存取此功能,請確定您的應用程式已透過 App Store 更新至最新版本。

若要在 iPad 版 Excel 中摘要數據透視表中的值,您可以使用 [加總]、[計數] 和 [平均] 等匯總函數。 根據預設,Sum 函數會用於值欄位中的數值。 您可以根據 OLAP 數據源檢視及編輯數據透視表,但無法在 iPad 版 Excel 中建立數據透視表。 

以下說明如何選擇不同的彙總函數: 

1. 點選資料透視表中的任何位置以顯示到 [ 資料透視表 ] 索引標籤,向左撥動並選取 [ 字段清單 ] 以顯示欄位清單。 

[欄位清單] 按鈕

2. 在 [數據透視表欄位] 清單的 [] 底下,點選值欄位旁的省略號。 

iPad 上的數據透視表域值省略號

3. 點選 [欄位設定]

iPad 上的資料透視表值欄位。

4. 檢查您要的匯總函數。 

附註: 樞紐分析表若是根據線上分析處理 (OLAP) 來源資料,則無法使用彙總函數。

使用此彙總函數

用於計算

Sum

數值的總和。 依預設用於含有數值的值欄位。

Count

非空值的個數。 Count 彙總函數的運作方式與 COUNTA 函數相同。 依預設,Count 用於含有非數值或空白的值欄位。

Average

數值的平均值。

Max

最大的數值。

Min

最小的數值。

Product

數值的乘積。

Count Numbers

含有數字的值之數量 (與 Count 不同,Count 包含非空白值)。

StDev

母體標準差的估計值,其中的樣本是整個母體的子集合。

StDevp

母體的標準差,母體是要彙總的所有資料。

Var

母體變異數的估計值,其中的樣本是整個母體的子集合。

Varp

母體的變異數,母體是要彙總的所有資料。

需要更多協助嗎? 

您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。

Need more help?

Want more options?

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

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

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×