ピボットテーブル内で構成比を計算する方法（総計に対する比率） [ピボットテーブル基礎]

Aさん

ピボットテーブルの数値を元に、隣のセル上の数式で構成比を計算していますが、ピボットテーブルの条件を変えると数式の参照元がずれたり、数式が上書きされてしまいます。。

こうした場合、何か良い方法はありますか？

森田

その場合、ピボットテーブル内で構成比を計算した方が良いですね。

構成比の計算は「値フィールドの設定」経由で設定できます。

では、詳細を解説していきますね。

解説動画：【ピボットテーブル#7】集計表へ計算を追加する方法4選+α – レポートへ比率や差異の比較結果を加える

この記事の内容は下記の動画でも解説しています。
コメント欄の「総計に対する比率」の時間の部分をクリックすると該当の解説へジャンプできますよ！

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

ピボットテーブルレポートを元に数式で構成比を計算していると、参照元がずれることがある

ピボットテーブルレポート上で集計した数値を元に、数式で構成比などの計算をするのは手っ取り早いです。

しかし、ピボットテーブルの集計条件を変更に伴いレポートのサイズが変わってしまうと、数式で参照したい数値のセルがずれる可能性があります。

また、最悪の場合、ピボットテーブルレポートのサイズ拡張に伴い数式のセルが上書きされるリスクもあります。
次の警告メッセージ上の「OK」をクリックすると上書きされます。

よって、ピボットテーブルレポートで構成比を求めたい、かつレポートのサイズが変わる可能性がある場合は、ピボットテーブル側の機能で構成比を計算すると良いです。

すると、ピボットテーブルレポート内で構成比を計算することができ、レポートのサイズが変わっても構成比の計算が狂いません。

ピボットテーブル内での構成比の計算は「値フィールドの設定」から行います。

では、ピボットテーブル内での構成比の計算手順を確認していきましょう。

ピボットテーブル内での構成比の計算手順

今回の前提として、すでにピボットテーブルの集計条件がセットされた状態からスタートします。

ピボットテーブル内での構成比の計算は、以下の2ステップとなります。

【STEP1】「値フィールドの設定」ダイアログを起動

まず、「値」ボックス内の構成比を計算したいフィールド名の「▼」をクリック（①）します。

すると、メニューが表示されますので、その中から「値フィールドの設定」をクリック（②）してください。

レポート上に実数と構成比を並べて表示したい場合、同じフィールドを「値」ボックスへ設定しておく必要があります。（今回であれば「金額」フィールド）

【参考】手順①②は右クリックメニュー経由でもOK！

手順①②は以下のように右クリックメニュー経由（ピボットテーブルレポートの任意のフィールド上で右クリック→「値フィールドの設定」をクリック）でもOKです。

【STEP2】「総計に対する比率」を設定

「値フィールドの設定」ダイアログが起動します。

まず、「計算の種類」タブをクリック（③）してタブを切り替えましょう。

続いて、「計算の種類」のプルダウンから「総計に対する比率」を選択（④）し、「OK」をクリック（⑤）すれば完了です。

手順④で選ぶ計算の種類によって構成比以外をピボットテーブルレポート内で計算することが可能です。

設定した列が構成比で計算されていればOK！

ワークシート上を見ると、設定した「合計/金額2」が構成比で計算されていますね！

ちなみに、表示形式は自動的にパーセンテージ（％）で表示されます。

【注意】「値」ボックスからフィールドを外すと計算の種類の設定は解除される

なお、上記の「値フィールドの設定」ダイアログ経由で計算の種類を設定した場合、「値」ボックスから該当のフィールドを外すと計算の種類の設定は解除されます。

再度「値」ボックスに該当のフィールドを入れても当初の集計値に戻り、計算の種類も再設定が必要になるのでご注意ください。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ピボットテーブル_総計に対する比率.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）

ファイルを開いたら、次の手順を実施してください。（今までの解説のまとめです）

本記事の解説と同じ結果になればOKです！

さいごに

いかがでしたでしょうか？

ビジネスでは実数と割合を一緒に見せる機会は非常に多いです。

また、割合の中でも構成比は実務で頻出なため、ぜひピボットテーブルレポート内の「総計に対する比率」の設定方法を覚えておくと良いですね。

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ピボットテーブルも関数もぜんぶ使う！Excelでできるデータの集計・分析を極めるための本

created by Rinker

¥1,199 (2024/12/10 04:34:23時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！

ご参考になれば幸いですm(_ _)m

森田

私も最初はピボットテーブル内で計算できることを知らず、よくピボットテーブルの隣セルに数式で構成比を計算していました。

ただ、やっぱりピボットテーブルはいろいろ集計条件を変えて別確度から集計していきたいもの。

結果、「面倒だなー」と思いながらいちいち数式を書き替えていましたので、私と同じ思いをしないためにも、ぜひ本記事を参考にピボットテーブル内の計算テクニックを習得してください！