Excel 피벗 테이블에서 전체 합계/소계 열의 백분율을 추가하려면 어떻게 해야 하나요?

각 항목이 전체 합계 또는 데이터 내 하위 그룹에 기여하는 백분율을 표시하려면, 백분율 계산 열을 추가하여 Excel 피벗 테이블을 강화할 수 있습니다. 이 접근 방식은 원시 데이터를 넘어선 데이터 비교를 수행하거나 요약 통계를 제시하고자 할 때 특히 유용합니다. 다음의 확장된 단계들은 이 기능을 설정하는 방법을 설명하며, 각 단계에서 실질적인 팁과 고려 사항도 제공합니다.

1. 피벗 테이블에서 분석하고자 하는 데이터 범위를 선택하세요. 그런 다음 Excel 리본 메뉴로 이동하여 삽입 > 피벗테이블을 클릭하세요. 이렇게 하면 분석을 위한 피벗 테이블 기반이 생성됩니다. 처음부터 정확한 소스 범위를 선택하면 계산이 정확해집니다. 선택 영역이 모든 관련 데이터를 포함하고 있는지 빈 행이나 열이 없는지 다시 한 번 확인하세요.

2. 나타나는 피벗테이블 만들기 대화상자에서 피벗 테이블을 새 워크시트에 배치할지 기존 워크시트에 배치할지 지정하세요. 새로운 워크시트를 선택하면 테이블이 보기 쉬워지고 원래 데이터는 유지됩니다. 선호도를 설정한 후 확인 버튼을 클릭하여 진행합니다.

3. 피벗테이블 필드 창에서 샵 필드와 아이템 필드를 행 영역으로 드래그하세요. 그런 다음 판매 필드를 값 영역으로 드래그하세요. 두 번이렇게 하면 결과 테이블에서 원래 판매 값과 백분율 계산을 나란히 표시할 수 있습니다. 백분율 열만 표시하려면 원래 값 필드를 나중에 제거하거나 숨길 수 있습니다.

4. 아래 값 영역에서 두 번째 Sales field (this will typically show as “Sum of Sales2” by default). Select Value Field Settings from the context menu. This step opens a dialog box where you can define how the field’s data is summarized and displayed in the table.

5. Within the Value Field Settings dialog box, go to the Show Values As tab. From the Show value as drop-down menu, pick % of Grand Total to calculate each value as a proportion of the grand total. Optionally, type a clear, descriptive name for your new column in the Custom Name field, such as "Percent of Total Sales," to make interpretation easier. Confirm your changes by clicking OK.

Note: If you'd like to show the percentage that each value represents of its parent subtotal (rather than the overall grand total), select % of Parent Row Total from the Show values as drop-down menu instead. This option is especially valuable when your dataset has grouped rows—such as categories under a shop—so you can analyze contribution to category-level totals.

Upon returning to the pivot table, you'll now see an added column displaying the “Percent of Grand Total” alongside the original values. This allows immediate comparison, making it much easier to interpret which items or categories are making the largest contribution to total results.

Note: When you select % of Parent Row Total in step 5, the percentage reflects each item's contribution to its respective subtotal (e.g., each product’s share within a shop), offering a more granular perspective on your data.

Adding percentage columns this way is useful for dashboards, quick performance analysis, and summarizing detailed data for presentations or management reports. However, if you need further customizations, such as conditional formatting or more advanced calculations, consider using calculated fields or supplementary Excel formulas for additional flexibility.

If you are interested in alternative approaches or need to achieve customized percentage calculations outside standard PivotTable options, try supplementing your report with Excel formulas or even automating your workflow with simple VBA. These methods offer more control, especially when built-in “Show Values As” settings don’t meet special requirements.

In some scenarios, you may want to display the percent of grand total directly next to your pivot table, or you require more formatting options than the built-in "Show Values As" feature offers. In this case, you can use Excel formulas outside the pivot table to perform the calculation.

1. Locate the numeric values column in your pivot table (for example, suppose your sales values are in cell range D5:D10). Next, identify the cell containing the grand total (such as D11). Alternatively, you can use the GETPIVOTDATA function to reference the total more reliably.

2. In the adjacent column (for instance, cell E5), enter the following formula to calculate the percent of grand total for each item:

=D5/$D$11

Or use this more robust version with GETPIVOTDATA (assuming the total value field is "Sales" and the pivot table starts in cell D4):

=D5/GETPIVOTDATA("Sales", $D$4)

These formulas divide each value by the grand total, ensuring a relative percentage is calculated for each row. Adjust the field name and cell references to match your actual pivot table layout.

3. Copy the formula down alongside the entire values range. For best results, format the new column as Percentage by selecting the range, right-clicking, choosing Format Cells, and picking Percentage.

Practical tip: This method offers flexibility for further customization (such as additional conditions or color-coding with conditional formatting). However, as your pivot table updates, double-check that formula references remain accurate—especially if items or rows change dynamically. Using GETPIVOTDATA helps prevent broken references in such cases.

For users who need to automate the process of adding a percent of grand total measure—especially when creating multiple pivot tables for reporting—VBA provides a customizable approach. This practical solution is ideal for recurring tasks or templates. Please follow these steps:

1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. In the VBA window, click Insert > Module, then copy and paste the following code into the module:

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. After inserting the code, click the “Run” button or press F5 to execute. The macro will automatically add a new field showing the percent of grand total in your existing pivot table on the current sheet.

Notes and Troubleshooting: This code assumes your PivotTable already contains at least one data field. If you want to target a specific PivotTable by name, you can replace ActiveSheet.PivotTables(1) with something like ActiveSheet.PivotTables("PivotTable1"). Always save your workbook before running new macros and ensure macros are enabled (check Trust Center settings if the code does not run).

How to sort by sum in Pivot Table in Excel?

How to automatically refresh a Pivot Table in Excel?