在樞紐分析表中計算值

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

可用的計算方法

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

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

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

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

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

• 若要計算值欄位，您可以在所有來源資料類型中使用下列彙總函數，但線上分析處理 (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%) 這樣的公式在「地區」欄位中建立計算項目。

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

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