ピボットテーブル内で任意のフィールドを対象に計算する方法(集計フィールド) [ピボットテーブル基礎]
AさんAさん

ピボットテーブルで集計した結果に対し、消費税を加えたり、「予算」と「実績」の差分の計算などの簡単な計算をしたいです。

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

森田森田

その場合、ピボットテーブルの「集計フィールド」を使えば良いです!

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

はじめに

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

  • Excelでデータの集計・分析作業を行うことが多い方
  • ピボットテーブルを実務で使う機会がある方
  • ピボットテーブルの数値を元に計算したい方

前提条件

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

ピボットテーブルレポート側だけで計算させたい場合に「集計フィールド」が有効

消費税の計算や、「予算」と「実績」などの別フィールド間の計算などは、通常はデータソース側であらかじめ計算させておき、それをピボットテーブルレポート上で集計することがセオリーです。

ただ、ちょっとした計算のためにデータソースのフィールドを増やしていくと、元々のレコード数によってはデータ量が大きくなりすぎてExcelブックが重くなる原因になるケースもあります。

そうした場合、「集計フィールド」を使うと良いです。

集計フィールドは、任意のフィールドに対しての計算や、複数のフィールド間の差分や比率の計算などをピボットテーブルレポート内に追加することが可能です。

では、「集計フィールド」の設定手順を確認していきましょう。

集計フィールドの設定手順

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

集計フィールドの設定は、以下の2ステップとなります。

今回の「金額」フィールドの集計結果に対し、消費税10%を加えていきます。

STEP1】「集計フィールドの挿入」ダイアログを起動

まず、ピボットテーブルレポート上のいずれかのセルを選択(①)した状態で、リボン「ピボットテーブル分析」タブをクリック(②)します。

続いて、「フィールド/アイテム/セット」をクリック(③)し、その中にある「集計フィールド」をクリック(④)してください。

STEP2】任意の計算を行うための数式を設定

「集計フィールドの挿入」ダイアログが起動します。

まず、集計フィールドの名前を入力(⑤)してください。(デフォルトの名前は「フィールド1」)

今回は「金額(税込)」という名前にしました。

続いて、任意の数式を入力(⑥)し、OK」をクリック(⑦)すれば完了です。

今回は、「= 金額*1.1」という数式を入力しています。

意味的には「金額」フィールドの集計結果に対し、1.1(消費税10%を加えた数)を掛けるということですね。

このように、数式上のフィールド名は特段記号で囲う必要はないです。

その他、四則演算のルールは通常のセルと同じですね。(+-*/

手順⑥でフィールド名を手入力する必要はないです。
左下のボックスで任意のフィールド名を選択したら「フィールドの挿入」をクリックすると、「数式」ボックス上へフィールド名が挿入されますので、活用しましょう。
ちなみに、四則演算の記号や「1.1」の係数は手入力が必要です。

集計フィールドが挿入され、数式通りに計算されていればOK

ワークシート上を見ると、設定した「合計 / 金額(税込)」が追加され、元値+税込の110%で計算されていますね!

【参考】集計フィールドは「値」ボックスに出し入れが可能

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

今回の集計フィールドについては、「ピボットテーブルのフィールド」ウィンドウ上に新たなフィールドとして生成されるため、こちらは「値」ボックスから外しても再度設定し直すことが可能です。

なお、集計フィールドは「値」ボックスにしか設定できない仕様です。

【補足】集計フィールドの変更・削除の方法

集計フィールドの名前や数式を修正したい場合、再度「集計フィールドの挿入」ダイアログを起動(上記設定手順①~④)します。

そして、「名前」ボックスのプルダウンから任意の集計フィールドを選択します。

あとは、それぞれ以下の操作を行えばOKです。

  • 変更の場合:名前や数式を修正の上「変更」をクリック
  • 削除の場合:「削除」をクリック

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

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

サンプルファイル_ピボットテーブル_集計フィールド.xlsx

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

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

  1. ピボットテーブルレポート上のいずれかのセルを選択
  2. リボン「ピボットテーブル分析」タブをクリック
  3. 「フィールド/アイテム/セット」をクリック
  4. 「集計フィールド」をクリック
  5. 集計フィールドの名前を入力
    ※今回は「金額(税込)」
  6. 任意の数式を入力
    ※今回は「= 金額*1.1
  7. OK」をクリック

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

さいごに

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

フィールドに対する計算やフィールド間の計算は案外多いものです。

いちいちデータソースを直すまでもない場合やデータ量を増やしたくない場合に集計フィールドは活躍しますので、覚えておくことをおすすめします。

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

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

森田森田

私もピボットテーブルを使い始めのうちは、いちいちデータソース側へ最初に計算させてからピボットテーブルで集計していました。

通常はこれで良いですが、本当にちょっとした計算を加えたいだけの場合など、正直めんどくさいかったので、集計フィールドを覚えてからは助かりましたね。

ぜひ、知らなかった方は覚えてみてください!