Excel 피벗 테이블에 계산 필드 추가하기
피벗 안을 클릭한 뒤 피벗 테이블 분석 > 필드, 항목 및 세트 > 계산 필드를 열고, 이름과 필드 이름을 쓴 수식(예: =Revenue*10%)을 입력하세요. 시트 셀 주소는 쓰지 마세요. Google Sheets에는 같은 대화 상자가 없으므로 원본에 도우미 열 =F2*0.1을 만든 뒤 값에 넣으세요.
목차
빠른 답변
피벗 안을 클릭한 뒤 피벗 테이블 분석 > 필드, 항목 및 세트 > 계산 필드를 열고, 이름과 필드 이름을 쓴 수식(예: =Revenue*10%)을 입력하세요. 시트 셀 주소는 쓰지 마세요. Google Sheets에는 같은 대화 상자가 없으므로 원본에 도우미 열 =F2*0.1을 만든 뒤 값에 넣으세요.
=24450*0.1
=9450*0.1
=19800*0.1확인 사항
2026년 11월 12일 테스트에서 매출 합계 24450의 10%는 2445, 동부 9450은 945, Widget 19800은 1980이었습니다. 피벗의 수수료가 이와 다르면 필드가 잘못되었거나 값이 여전히 개수일 수 있습니다. 계산 필드는 집계한 뒤에 계산하므로 비율 수식에는 맞지 않을 수 있고, 그때는 원본에서 행마다 계산하세요.
주의점 및 호환성
필드 이름에 공백이 있으면 ='Unit Price'*Quantity처럼 작은따옴표로 감쌉니다. 단추가 회색이면 피벗 바깥을 클릭했거나 원본이 데이터 모델인 경우이고, 그때는 측정값을 추가합니다. 총합 대비 비율은 표시 옵션이므로 계산 필드로 만들 필요가 없습니다. 원본을 바꿨으면 새로 고치세요.
영어 상세 예제, 수식 및 작성자 메모
Excel How-toTested in: The 10% commission figures below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Fields, Items, & Sets in Excel this session.
If you do not have a pivot yet, start with How to Create a Pivot Table in Excel. This page is only about a field you compute from other pivot fields.
Add the field in Excel
- Click any cell inside the pivot table.
- Open the PivotTable Analyze tab (it may be labeled Options or Analyze in older Excel).
- Choose Fields, Items, & Sets > Calculated Field.
- In Name, type something like
Commission. - In Formula, enter
=Revenue*10%(or=Revenue*0.1). You can double-click a field in the list instead of typing the name. - Click Add, then OK.
The field shows up in the Values well. If it arrives as Count of Commission, switch it to Sum, as in pivot table shows count instead of sum.
On the eight-sale sample used elsewhere on this site, 10% of Revenue should follow the same cuts as Revenue itself. In Google Sheets on 12 November 2026:
=24450*0.1
=9450*0.1
=19800*0.1Those returned 2445 (grand total), 945 (East), and 1980 (Widget). After you add the calculated field, the pivot’s Commission totals should match those figures. If they do not, the formula is using the wrong field, Values is still on Count, or a filter is hiding rows.
How the formula is written
Use field names, not cell addresses. =Revenue*0.1 is right; =F2*0.1 is not, because F2 is a worksheet cell, not a pivot field. Spaces in a field name need single quotes: ='Unit Price'*Quantity.
Excel calculates a calculated field on the aggregated totals, not on each source row and then summed. For =Revenue*0.1 that does not matter, because 10% of the sum is the sum of the 10%s. For a ratio such as =Profit/Revenue or =Revenue/Quantity, the field can be wrong: it will divide the summed profit by the summed revenue (which may be what you want) or, depending on the layout, not match a row-level margin you had in mind. When the math has to happen on every source row, add a helper column in the source (=G2/F2) and pivot that column.
You can also add a calculated field that subtracts two fields, for example =Revenue-Cost. Keep the result in Values as Sum, and check it against a sheet formula such as =SUM(F2:F9)-SUM(G2:G9).
Google Sheets, and when not to use a calculated field
Sheets pivot tables do not offer Calculated Field. Insert a column in the source:
=F2*0.1fill it down, then add that column to Values as SUM. The same 2445 / 945 / 1980 checks apply.
A calculated field is the wrong tool for “show Revenue as a percent of the grand total.” That is a display option, covered in pivot table percentage of total. It is also the wrong tool when the source is in the data model: add a DAX measure there, not a calculated field.
If you change the source after adding the field, refresh the pivot. To edit or delete the field, go back to Calculated Field, pick it from the name list, and change the formula or click Delete.