Excel のピボットテーブル値を合計に対する比率で表示する
Excel では「値フィールドの設定 > 計算の種類」で「総計に対する比率」、または列・行に対する比率を選びます。Google Sheets では値の Show as を % of grand total にします。数値自体は変わらず、表示だけが変わります。同じフィールドを値に2回入れ、一方を合計、もう一方を比率にできます。
目次
要点
Excel では「値フィールドの設定 > 計算の種類」で「総計に対する比率」、または列・行に対する比率を選びます。Google Sheets では値の Show as を % of grand total にします。数値自体は変わらず、表示だけが変わります。同じフィールドを値に2回入れ、一方を合計、もう一方を比率にできます。
=9450/24450
=19800/24450
=12600/24450確認のポイント
8行の売上では合計 24450、東 9450、Widget 19800、Maya 12600 です。2026年11月12日の確認では 9450/24450 は 0.38650306748466257、19800/24450 は 0.8098159509202454、12600/24450 は 0.5153374233128835 でした。ピボットの比率は、この丸め前の値と一致するはずです。
注意点と互換性
39% と表示されるのは丸めの結果で、丸めた比率を足すと 100% に足りないことがあります。フィルタは分母の合計を変えるので、西を外すと東は残りの 9450 の 100% になります。値が個数のままだと行数の比率になります。計算フィールドで =Revenue/GrandTotal と書かず、「計算の種類」を使います。
英語の詳細例・数式・著者メモ
Excel How-toTested in: The share figures below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click Show Values As in Excel this session.
This is a display option on a field you already have. It is not a calculated field. For the same math outside a pivot, use the percentage of total calculator.
Change Show Values As
In Excel 365
- Click a number in the Values area, or open the field’s menu in the Values well.
- Choose Value Field Settings.
- Open the Show Values As tab (in some builds this is a drop-down on the same dialog).
- Pick % of Grand Total, % of Column Total, or % of Row Total.
- Click OK. Format as a percentage if the cells do not already show a % sign.
To keep the original sums visible, drag Revenue into Values a second time and set only the copy to % of Grand Total. You will see “Sum of Revenue” and “Sum of Revenue2” (rename the second to “Share”).
In Google Sheets
- Click the pivot to open the editor.
- In Values, open the field.
- Set Show as to % of grand total, % of row, or % of column.
Check the shares against the sheet
On the eight-sale sample, Revenue sums to 24450, East is 9450, Widget is 19800, and Maya is 12600. In Google Sheets on 12 November 2026:
=9450/24450
=19800/24450
=12600/24450Those returned 0.38650306748466257, 0.8098159509202454, and 0.5153374233128835. A pivot that shows % of Grand Total for those items should match once you display enough decimals. West is 1 − East, or 0.6134969325153374.
% of Column Total is useful when Dates or Products run across the top and you want each column to add to 100%. % of Row Total does the same across a row. % of Parent Total (Excel) and % of parent (Sheets, when available) are for hierarchical layouts — for example, a product’s share of its region, not of the workbook grand total.
If Values is still on Count, you will be looking at a share of row counts, not of money. Switch the summary to Sum first; the percentage option does not fix a count.
Rounding, filters, and differences from a calculated field
A share displayed as 39% has been rounded; the unrounded East figure is 38.650…%. Adding rounded percentages can miss 100% by a point. That is a display issue, not a sign that the pivot is wrong.
A filter changes the grand total the percentage is based on. If you filter out West, East becomes 100% of the remaining 9450, not 38.65% of 24450. Decide whether you want the share of everything or the share of the visible slice before you apply the filter.
Do not build % of total as a calculated field like =Revenue/GrandTotal. There is no GrandTotal field to divide by, and a calculated field will not automatically use the pivot’s grand total the way Show Values As does. Leave percentages to Show Values As, and use a calculated field for a commission or a difference you can write with the field names you already have.