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.