在樞紐分析表中計算值

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

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

可用的計算方法

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

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

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

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

    樞紐分析表範例

    樞紐分析圖範例

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

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

  • 若要計算值欄位,您可以在所有來源資料類型中使用下列彙總函數,但線上分析處理 (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. 按一下 [刪除]。

頁面頂端

檢視樞紐分析表中使用的所有公式

若要顯示目前樞紐分析表中使用的所有公式清單,請執行下列操作:

  1. 按一下樞紐分析表。

  2. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [顯示公式]。

編輯樞紐分析表公式

  1. 判斷公式是導出欄位還是計算項目。如果公式是計算項目,請執行下列操作以判斷公式是否為計算項目的唯一項目:

    1. 按一下樞紐分析表。

    2. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [顯示公式]。

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

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

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

  2. 執行下列其中一項操作:

    編輯導出欄位的公式    

    1. 按一下樞紐分析表。

    2. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [計算欄位]。

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

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

    5. 按一下 [修改]。

      編輯計算項目的單一公式    

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

    7. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [計算項目]。

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

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

    10. 按一下 [修改]。

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

      例如,如果名為 OrangeCalc 的計算項目在所有月份中使用 =Oranges * .25 公式,您可以針對「六月」、「七月」和「八月」將公式變更為 =Oranges *.5

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

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

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

  3. 如果您有多個計算項目或公式,請執行下列操作以調整計算順序:

    1. 按一下樞紐分析表。

    2. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [求解順序]。

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

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

刪除樞紐分析表公式

提示: 如果您不想要永久刪除公式,您可以隱藏欄位或項目。若要隱藏欄位,請將欄位拖出樞紐分析表外。

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

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

  2. 執行下列其中一項操作:

    刪除導出欄位    

    1. 按一下樞紐分析表。

    2. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [計算欄位]。

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

    4. 按一下 [刪除]。

      刪除計算項目    

    5. 按一下包含您要刪除之項目的欄位。

    6. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [公式],然後按一下 [計算項目]。

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

    8. 按一下 [刪除]。

頁面頂端

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×