ピボットテーブル内で前日比などの「基準値に対する比率」を計算する方法 [ピボットテーブル基礎]

Aさん

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

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

森田

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

前日比の計算などは「値フィールドの設定」経由で設定できます。

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

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

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

はじめに

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

前提条件

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

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

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

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

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

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

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

ピボットテーブル内での前日比などの「基準値に対する比率」の計算は「値フィールドの設定」から行います。

では、ピボットテーブル内での「基準値に対する比率」の計算手順を確認していきましょう。

ピボットテーブル内での「基準値に対する比率」の計算手順

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

ピボットテーブル内での「基準値に対する比率」の計算は、以下の2ステップとなります。

今回の「基準値に対する比率」として、前日比（本日÷前日）の計算を例としていきますね。

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

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

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

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

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

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

【STEP2】「基準値に対する比率」を設定

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

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

続いて、「計算の種類」のプルダウンから「基準値に対する比率」を選択（④）し、「基準アイテム」のボックスから任意のアイテムを選択（⑤）してください。

今回の基準値が「前日」という日付によって変動するものにあたるため、手順⑤は「(前の値)」を選択しています。

最後に「OK」をクリック（⑥）すれば完了です。

手順④で選ぶ計算の種類によって「基準値に対する比率」以外をピボットテーブルレポート内で計算することが可能です。
たとえば、「前日差異」（本日-前日）などを計算したいなら「基準値との差分」を選択すれば良いです。

手順⑤は特定のアイテムを基準値として固定したい場合は、任意のアイテム名を選択してください。

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

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

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

また、9月1日のみ前日がないため、「100%」で表示されています。

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

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

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

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

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

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

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

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

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

さいごに

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

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

また、割合の中でも日や週などの時系列ごとでの比率計算は実務で行う機会は多いため、ぜひピボットテーブルレポート内の「基準値に対する比率」の設定方法を覚えておくと良いですね。

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

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

森田 貢士 (著)

Amazon

Kindle

楽天

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

森田

ピボットテーブルでは、四則演算をベースにさまざまな種類の計算が可能です。

ちょっと名称からどんな計算ができるのか推測するのがなかなか難しいですが、ピボットテーブルを使っていて計算が必要になった際、ピボットテーブル側でできないかを都度調べてみると良いと思います。