如何添加和使用 Excel 數據透視表計算字段

如何添加和使用 Excel 數據透視表計算字段

通常,一旦您創建了一個數據透視表,您就需要擴展您的分析並在其中包含更多數據/計算。

如果您需要一個可以使用數據透視表中現有數據點獲取的新數據點,則無需返回並將其添加到源數據中。相反,您可以使用數據透視表計算字段來執行此操作。

什麼是數據透視表計算字段?

讓我們從數據透視表的基本示例開始。

假設您有一個零售商數據集,並且您創建了一個數據透視表,如下所示:

Excel 數據透視表計算字段 - 數據

上面的數據透視表總結了零售商的銷售額和利潤值。

現在,如果您還想知道這些零售商的利潤率是多少(利潤率是“利潤”除以“銷售額”),該怎麼辦?

有幾種方法可以做到這一點:

  1. 返回原始數據集並添加這個新數據點。因此,您可以在源數據中插入一個新列併計算其中的利潤率。完成此操作後,您需要更新數據透視表的源數據以獲取此新列作為其中的一部分。
    • 雖然這種方法是可行的,但您需要手動返回數據集並進行計算。例如,您可能需要添加另一列來計算每單位的平均銷售額(銷售額/數量)。同樣,您必須將此列添加到源數據中,然後更新數據透視表。
    • 當您向其中添加新數據時,此方法還會使您的數據透視表膨脹。
  2. 在數據透視表之外添加計算。如果您的數據透視表結構不太可能更改,則可以選擇此選項。但是,如果您更改數據透視表,則計算可能不會相應更新,並且可能會給您錯誤的結果或錯誤。如下所示,我計算了當行中有零售商時的利潤率。但是當我將它從客戶更改為區域時,公式出錯了。數據透視表計算字段 - 錯誤
  3. 使用數據透視表計算字段。這是使用現有數據透視表數據和計算所需指標的最有效方式。將計算字段視為您使用數據透視表中的現有列添加的虛擬列。使用數據透視表計算字段有很多好處(稍後我們將看到):
    • 它不需要您處理公式或更新源數據。
    • 它是可擴展的,因為它會自動考慮您可能添加到數據透視表中的任何新數據。添加計算字段後,您可以像使用數據透視表中的任何其他字段一樣使用它。
    • 它易於更新和管理。例如,如果指標發生變化或您需要更改計算,您可以從數據透視表本身輕鬆完成。

將計算字段添加到數據透視表

讓我們看看如何在現有數據透視表中添加數據透視表計算字段。

假設您有一個如下所示的數據透視表,並且您想要計算每個零售商的利潤率:

數據透視表計算字段 - 數據

以下是添加數據透視表計算字段的步驟:

添加計算字段後,它將顯示為數據透視表字段列表中的字段之一。

數據透視表計算字段 - 字段列表

現在您可以將此計算字段用作任何其他數據透視表字段(請注意,您不能將數據透視表計算字段用作報表過濾器或切片器)。

正如我之前提到的,使用數據透視表計算字段的好處是您可以更改數據透視表的結構,它會自動調整。

例如,如果我將區域拖放到行區域中,您將得到如下所示的結果,其中報告了零售商和區域的利潤率值。

相關問題  如何在 Excel 中刪除單元格格式(從所有、空白、特定單元格)

 

在上面的示例中,我使用了一個簡單的公式 (=Profit/Sales) 來插入計算字段。但是,您也可以使用一些高級公式。

在我向您展示使用高級公式創建數據透視表計算字段的示例之前,您必須了解以下幾點:

  • 在創建數據透視表計算字段時,您不能使用引用或命名範圍。這將排除很多公式,例如 VLOOKUP、INDEX、OFFSET 等。但是,您可以使用無需引用即可工作的公式(例如 SUM、IF、COUNT 等..)。
  • 您可以在公式中使用常數。例如,如果您想知道預計增長 10% 的預測銷售額,您可以使用公式 =Sales*1.1(其中 1.1 是常數)。
  • 在生成計算字段的公式中遵循優先順序。作為最佳實踐,使用括號確保您不必記住優先順序。

現在,讓我們看一個使用高級公式創建計算字段的示例。

假設您有如下所示的數據集,並且您需要在數據透視表中顯示預測的銷售額。

對於預測值,您需要對大型零售商(銷售額超過 300 萬)使用 5% 的銷售額增長,對中小型零售商(銷售額低於 300 萬)使用 10% 的銷售額增長。

注意:此處的銷售數字是假的,已用於說明本教程中的示例。

以下是如何做到這一點:

這會在數據透視表中添加一個包含銷售預測值的新列。

數據透視表計算字段 - 高級公式結果

數據透視表計算字段的問題

計算字段是一項了不起的功能,它通過字段計算真正提高了數據透視表的價值,同時仍然保持一切可擴展和可管理。

但是,數據透視表計算字段存在一個問題,您必須在使用它之前了解它。

假設,我有一個如下所示的數據透視表,我使用計算字段來獲取預測銷售數字。

數據透視表計算字段 - 高級公式結果

請注意,小計和總計不正確。

雖然這些應該為每個零售商添加單獨的銷售預測值,但實際上,它遵循我們創建的相同計算字段公式。

因此,對於 South Total,雖然該值應為 22,824,000,但 South Total 錯誤地將其報告為 22,287,000。發生這種情況是因為它使用公式 21,225,800*1.05 來獲取值。

相關問題  從 Excel 中的字符串中提取數字(使用公式或 VBA)

數據透視表計算字段 - 錯誤值

不幸的是,您無法糾正此問題。

處理此問題的最佳方法是從數據透視表中刪除小計和總計。

您還可以通過 Debra 展示的一些創新解決方法來處理此問題。

如何修改或刪除數據透視表計算字段?

創建數據透視表計算字段後,您可以使用以下步驟修改公式或將其刪除:

如何獲取所有計算字段公式的列表?

如果您創建了很多數據透視表計算字段,則不必擔心跟踪每個字段中使用的公式。

Excel 允許您快速創建用於創建計算字段的所有公式的列表。

以下是快速獲取所有計算字段公式列表的步驟:

  • 選擇數據透視表中的任何單元格。
  • 轉到數據透視表工具 -> 分析 -> 字段、項和集 -> 列出公式。數據透視表計算字段 - 列出公式

一旦您單擊列表公式,Excel 將自動插入一個新工作表,其中包含您在數據透視表中使用的所有計算字段/項目的詳細信息。

如果您必須將工作發送給客戶或與您的團隊共享,這可能是一個非常有用的工具。

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment