如何在 Excel 的樞紐分析表中新增總計/小計欄位的百分比?
當你在 Excel 中處理大型數據集並通過樞紐分析表進行分析時,該工具通常會自動生成包含數值數據匯總的總計欄或行。然而,在許多實際情況下,例如績效評估或銷售比較,你不僅需要查看總數,還需要了解每個項目相對於總計或其子組小計所佔的比例(百分比)。通過直接在數值旁邊顯示這些百分比,你可以快速識別關鍵貢獻者、發現趨勢並更有效地傳達見解。本指南將逐步演示如何在樞紐分析表中新增一個額外的欄位,計算每個值占整體總計或子組小計的百分比,從而簡化 Excel 中的數據分析和報告任務。
在 Excel 樞紐分析表中新增總計/小計欄位的百分比
若要顯示每個項目對整體總計或資料內子組的貢獻百分比,可以透過在 Excel 樞紐分析表中新增一個百分比計算欄位來增強功能。這種方法特別適用於希望執行數據比較或呈現超越原始數字的統計摘要時。以下擴展步驟概述了如何設置此功能,並且在每個階段提供實用提示與注意事項。
1. 首先選擇要在樞紐分析表中分析的數據範圍。然後前往 Excel 功能區並點擊 插入 > 樞紐分析表。這為你的分析創建了一個樞紐分析表基礎。一開始選擇正確的來源範圍可確保計算準確;請仔細檢查選取範圍是否涵蓋所有相關數據且不含空白行或列。
2。在 建立樞紐分析表 對話框中,指定是要將樞紐分析表放置在新工作表還是現有工作表中。選擇新工作表通常使表格更容易查看並保持原始數據的完整性。設定好偏好後,點擊 確定 按鈕繼續。
3。在 樞紐分析表欄位 窗格中,拖動 商店 欄位及 項目 欄位至 列 區域。接著,將 銷售 欄位拖動至 值 區域 兩次。這樣做允許你在結果表中並排顯示原始銷售值和百分比計算。如果你只想顯示百分比欄位,稍後可以移除或隱藏原始值欄位。
4。在 值 區域下方,點擊第二個 銷售 欄位旁的下拉箭頭(預設通常顯示為“銷售總和2”)。從上下文菜單中選擇 值欄位設定 。此步驟打開一個對話框,你可以在其中定義欄位數據如何在表中進行匯總和顯示。
5。在 值欄位設定 對話框中,前往 顯示值為 標籤。從 顯示值為 下拉菜單中選擇 總計的百分比 以計算每個值占總計的比例。或者,在 自訂名稱 欄位中輸入清晰且描述性的名稱,例如「總銷售百分比」,以便於解釋。點擊 確定.
注意:如果你想顯示每個值對其父級小計(而非整體總計)的百分比,請從「顯示值為」下拉菜單中選擇 % of Parent Row Total。這個選項在你的數據集有分組行時(例如商店下的類別)特別有用,因此你可以分析對類別層級總計的貢獻。
返回樞紐分析表後,你現在會看到新增的一個柱,顯示「總計的百分比」與原始值並列。這使得立即比較變得容易,讓你更容易理解哪些項目或類別對總結果貢獻最大。
注意:當你在第 5 步選擇 % of Parent Row Total 時,百分比反映的是每個項目對其各自小計的貢獻(例如,每個產品在商店中的份額),從而提供更細緻的數據視角。
💡 提示與注意事項:
- 如果原始數據包含過濾器或空白,請在設置百分比後再次檢查樞紐分析表的準確性。
- 格式可能默認顯示為小數;右鍵點擊百分比欄位,選擇 數字格式,然後選擇 百分比 格式。
- 在某些版本的 Excel 中,欄位名稱或界面可能略有不同——如果屏幕不完全匹配,請專注於一般步驟。
- 如果「顯示值為」選項被灰化,請確保數值欄位在 值 區域中,並且已選擇樞紐分析表。
以這種方式添加百分比欄位對於儀表板、快速性能分析以及為簡報或管理報告總結詳細數據非常有用。但是,如果需要進一步定制(如條件格式或更高級的計算),請考慮使用計算欄位或輔助 Excel 公式以獲得更多靈活性。
如果你對替代方法感興趣或需要在標準樞紐分析表選項之外實現定制的百分比計算,可以嘗試使用 Excel 公式補充報告,甚至使用簡單的 VBA 自動化工作流程。這些方法提供了更多控制,特別是當內置「顯示值為」設置無法滿足特殊要求時。
使用 Excel 公式在樞紐分析表外部計算總計的百分比
在某些情況下,你可能希望直接在樞紐分析表旁邊顯示總計百分比,或者你需要比內置「顯示值為」功能提供的更多格式選項。在這種情況下,可以在樞紐分析表外部使用 Excel 公式來進行計算。
1. 找到樞紐分析表中的數值欄位(例如,假設你的銷售值在 D5:D10 範圍內)。接下來,找出包含總計的單元格(例如 D11)。或者,你可以使用 GETPIVOTDATA 函數來更可靠地引用總計。
2. 在相鄰的欄位(例如 E5 單元格)中,輸入以下公式來計算每個項目對總計的百分比:
=D5/$D$11
或者使用這個更穩健的版本搭配 GETPIVOTDATA(假設總值欄位為「銷售」並且樞紐分析表從 D4 開始):
=D5/GETPIVOTDATA("Sales", $D$4)
這些公式將每個值除以總計,確保為每一行計算出相對百分比。調整欄位名稱和單元格引用以匹配你的實際樞紐分析表佈局。
3. 將公式複製到整個數值範圍旁邊。為了達到最佳效果,選擇範圍後右鍵點擊,選擇 設定儲存格格式,然後選擇 百分比 格式來格式化新欄位。
實用提示:此方法提供了進一步定制的靈活性(例如附加條件或使用條件格式進行顏色編碼)。然而,當樞紐分析表更新時,請仔細檢查公式引用是否仍然準確——特別是當項目或行動態變化時。在這些情況下,使用 GETPIVOTDATA 可幫助防止引用中斷。
使用 VBA 程式碼將總計百分比新增到樞紐分析表
對於需要自動化新增總計百分比指標的用戶,特別是在為報告創建多個樞紐分析表時,VBA 提供了一種可定制的方法。這個實用解決方案非常適合重複任務或模板。請按照以下步驟操作:
1. 點擊 開發工具 > Visual Basic 以打開 Microsoft Visual Basic for Applications 窗口。在 VBA 窗口中,點擊 插入 > 模塊,然後將以下程式碼複製並粘貼到模塊中:
Sub AddPercentOfGrandTotal()
Dim pt As PivotTable
Dim pf As PivotField
Dim pfNew As PivotField
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
If ActiveSheet.PivotTables.Count = 0 Then
MsgBox "No PivotTable found on this sheet.", vbExclamation, xTitleId
Exit Sub
End If
Set pt = ActiveSheet.PivotTables(1)
If pt.DataFields.Count = 0 Then
MsgBox "No data field found in the PivotTable.", vbExclamation, xTitleId
Exit Sub
End If
Set pf = pt.DataFields(1)
' Check if the field already exists
Dim fldName As String
fldName = "Percent of Grand Total"
On Error Resume Next
Set pfNew = pt.PivotFields(fldName)
On Error GoTo 0
If Not pfNew Is Nothing Then
MsgBox "Field '" & fldName & "' already exists.", vbInformation, xTitleId
Exit Sub
End If
' Add new field and apply percentage calculation
Set pfNew = pt.AddDataField(pt.PivotFields(pf.SourceName), fldName, xlSum)
With pfNew
.Calculation = xlPercentOfTotal
.NumberFormat = "0.00%"
End With
End Sub
2。插入程式碼後,點擊 “運行”按鈕或按下 F5 以執行。宏將自動在當前工作表上的現有樞紐分析表中新增一個顯示總計百分比的新欄位。
注意與故障排除:此程式碼假設你的樞紐分析表已經至少包含一個數據欄位。如果你想按名稱定位特定的樞紐分析表,可以將 ActiveSheet.PivotTables(1)
替換為 ActiveSheet.PivotTables("PivotTable1")
。在運行新宏之前務必保存工作簿,並確保啟用了宏(如果程式碼未運行,請檢查信任中心設置)。
相關文章:
最佳 Office 生產力工具
🤖 | Kutools AI 助手:以智能執行為基礎,革新數據分析 |生成程式碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 增強函數… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不遺失數據 | 四捨五入(免公式)... | |
高級 LOOKUP:多條件 VLookup|多值 VLookup|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 |依賴型下拉列表 | 多選下拉列表... | |
列管理器:添加指定數量的列 | 移動列 | 切換隱藏列的顯示狀態 | 比較區域及列... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫(快捷文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線...)... | |
15 大工具集:12 項文本工具(添加文本、刪除特定字符…)|50+ 儀表 類型(甘特圖等)|40+ 實用 公式(基於生日計算年齡等)|19 項插入工具(插入QR码、根據路徑插入圖片等)|12 項轉換工具(金額轉大寫、匯率轉換等)|7 項合併與分割工具(高級合併行、分割儲存格等)|...及更多 |
運用 Kutools for Excel,全面提升您的 Excel 技能,體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能,讓您提升工作效率、節省時間。 點此尋找您最需要的功能...
Office Tab 為 Office 帶來分頁介面,讓您的工作更加輕鬆簡單
- 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀。
- 在同一視窗的新分頁中打開與創建多份文件,而非開啟新視窗。
- 提升您的生產力50%,每日可幫您減少數百次鼠標點擊!
所有 Kutools 外掛,一次安裝
Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛,外加 Office Tab Pro,非常適合需要跨 Office 應用程式協同作業的團隊。





- 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
- 一鍵安裝,一份授權 — 幾分鐘完成設置(支援 MSI)
- 協同運作更順暢 — Office 應用間無縫提升生產力
- 30 天全功能試用 — 無需註冊、無需信用卡
- 最超值 — 一次購買,節省單獨外掛費用