【Excel】ピボットテーブルの「値フィールドの設定」で累計を表示!Excelの前年比・構成比の出し方

Excelのピボットテーブルで、前年比や構成比を算出したい場面があります。特に、売上データなどを分析する際に、前年との比較や全体の占める割合を知りたいことは多いでしょう。しかし、ピボットテーブルでこれらの計算を直接行う方法が分からないという方もいるかもしれません。この記事では、ピボットテーブルの「値フィールドの設定」機能を使って、累計、前年比、構成比を簡単に表示する方法を解説します。

ピボットテーブルの「値フィールドの設定」を使いこなせば、複雑な関数を使わずに、これらの重要な分析指標を瞬時に把握できるようになります。この記事を読むことで、Excelでのデータ分析の幅が大きく広がるはずです。

ピボットテーブルで「累計」を表示する仕組み

ピボットテーブルで累計を表示するには、「値フィールドの設定」ダイアログボックスの「データの個別の集計方法」という機能を利用します。この機能を使うと、元データの集計値だけでなく、前年比や構成比、さらには累計値などを計算して表示させることが可能です。累計とは、ある項目までの合計値を意味します。例えば、月別売上データがある場合、1月、2月、3月…と進むにつれて、その月までの累積売上を表示させることができます。これは、年初からの進捗状況などを把握するのに役立ちます。

この「データの個別の集計方法」は、Excel 2010以降のバージョンで利用できる機能です。それ以前のバージョンでは、手動で数式を作成する必要がありました。Excel 2019やMicrosoft 365では、この機能が標準で搭載されており、直感的に操作できます。

「値フィールドの設定」で累計を表示する手順

実際にピボットテーブルで累計を表示する手順を解説します。ここでは、例として月別売上データがあり、年初からの累計売上を表示するケースを想定します。

1. ピボットテーブルの準備
   分析したいデータ範囲を選択し、「挿入」タブから「ピボットテーブル」をクリックして作成します。行ラベルに「年月」などを配置し、値フィールドに「売上」などの集計したい数値を配置します。
2. 値フィールドの設定を開く
   「ピボットテーブルのフィールド」ウィンドウで、集計したい数値フィールド(例:「合計 / 売上」)を右クリックします。表示されるメニューから「値フィールドの設定」を選択します。
3. 「データの個別の集計方法」を選択
   「値フィールドの設定」ダイアログボックスが表示されます。「集計」タブの「値」欄で、現在選択されている集計方法(通常は「合計」)を確認します。その下にある「データの個別の集計方法」というドロップダウンリストをクリックします。
4. 「累計」を選択
   ドロップダウンリストの中から「累計」を選択します。
5. 基準フィールドを指定
   「基準フィールド」という項目が表示されるので、ここでは「年月」などの、累計を計算したい順番が決まっているフィールドを選択します。
6. 「OK」をクリック
   設定が完了したら、「OK」ボタンをクリックします。

これで、ピボットテーブルの売上欄に、年初からの累計売上が表示されるようになります。1月は1月の売上、2月は1月と2月の合計、3月は1月から3月までの合計…というように、自動的に計算されます。

前年比・前月比を計算する方法

次に、前年比や前月比を計算する方法を解説します。これは、売上の成長率や変動を把握するために非常に重要な分析です。

前年比を計算する手順

前年比を計算するには、「値フィールドの設定」で「差額」や「%差額」を利用します。ここでは、前年同月比を表示する手順を説明します。

1. 値フィールドの設定を開く
   累計の場合と同様に、集計したい数値フィールドを右クリックし、「値フィールドの設定」を選択します。
2. 「データの個別の集計方法」で「差額」を選択
   「値フィールドの設定」ダイアログボックスで、「データの個別の集計方法」ドロップダウンリストから「差額」を選択します。
3. 基準フィールドと基準項目を指定
   「基準フィールド」で、比較したい期間のフィールド(例:「年月」)を選択します。次に、「基準項目」で、比較対象となる項目を選択します。前年比を表示したい場合は、「(前年同期)」を選択します。
4. 「OK」をクリック
   設定が完了したら、「OK」ボタンをクリックします。

これにより、各月の売上が、前年の同じ月の売上からどれだけ増減したか(差額)が表示されます。さらに、この「差額」フィールドをもう一つピボットテーブルの値フィールドに追加し、その「値フィールドの設定」で「データの個別の集計方法」を「%差額」に設定すると、前年比のパーセンテージが表示できます。

前月比を計算する手順

前月比も同様の手順で計算できます。「値フィールドの設定」で「データの個別の集計方法」を「差額」にし、「基準フィールド」で「年月」などを選択します。「基準項目」で「(前の項目)」を選択すると、前月との差額が表示されます。これも同様に、「%差額」を設定することで前月比のパーセンテージを表示できます。

構成比(全体に対する割合)を計算する方法

構成比とは、全体の合計値に対して、各項目がどれくらいの割合を占めているかを示す指標です。例えば、商品カテゴリ別の売上構成比などを分析する際に役立ちます。

1. 値フィールドの設定を開く
   集計したい数値フィールドを右クリックし、「値フィールドの設定」を選択します。
2. 「データの個別の集計方法」で「%合計」を選択
   「値フィールドの設定」ダイアログボックスで、「データの個別の集計方法」ドロップダウンリストから「%合計」を選択します。
3. 基準フィールドを指定
   「基準フィールド」では、通常「総計」など、全体の合計を計算したいフィールドを選択します。行ラベルや列ラベルに配置したフィールド名が表示されるので、分析したい集計対象のフィールドを選択してください。
4. 「OK」をクリック
   設定が完了したら、「OK」ボタンをクリックします。

これにより、各項目(例:商品カテゴリ)が、全体の売上合計に対して何パーセントを占めているかが表示されます。この機能は、どの項目が全体の売上に大きく貢献しているかを把握するのに非常に有効です。

親カテゴリに対する構成比を表示する方法(%親合計)

ピボットテーブルで階層構造(例:大カテゴリ、小カテゴリ)になっている場合、各小カテゴリがその大カテゴリ内でどれくらいの割合を占めているかを知りたい場合があります。その際には、「%親合計」を使用します。

1. 値フィールドの設定を開く
   集計したい数値フィールドを右クリックし、「値フィールドの設定」を選択します。
2. 「データの個別の集計方法」で「%親合計」を選択
   「値フィールドの設定」ダイアログボックスで、「データの個別の集計方法」ドロップダウンリストから「%親合計」を選択します。
3. 基準フィールドを指定
   「基準フィールド」では、親となるカテゴリのフィールド(例:「大カテゴリ」)を選択します。
4. 「OK」をクリック
   設定が完了したら、「OK」ボタンをクリックします。

例えば、大カテゴリ「食品」、小カテゴリ「菓子」「飲料」があり、それぞれの売上構成比を計算したい場合、「基準フィールド」に「食品」を選択することで、「食品」カテゴリ全体に対する「菓子」の割合、「飲料」の割合が表示されます。これにより、各カテゴリ内での製品の強みや弱みを分析できます。

ピボットテーブルでの集計方法の比較

「値フィールドの設定」で利用できる集計方法には、基本的なものから応用的なものまで様々あります。それぞれの特徴を理解することで、より的確な分析が可能になります。

集計方法	機能概要	用途例
合計	単純な合計値を計算します。	総売上、総販売数など。
個数	データが存在するセルの数を数えます。	注文数、顧客数など。
平均	平均値を計算します。	平均単価、平均購入金額など。
最大	最大値を表示します。	最高売上、最高気温など。
最小	最小値を表示します。	最低売上、最低気温など。
累計	指定したフィールドの累計値を計算します。	年初からの売上進捗、累積件数など。
差額	指定した基準項目からの差額を計算します。	前年比の差額、前月比の差額など。
%差額	指定した基準項目からの差額の割合(%)を計算します。	前年比の成長率、前月比の成長率など。
%合計	全体の合計に対する割合(%)を計算します。	総売上に対する商品カテゴリ別売上構成比など。
%親合計	親カテゴリに対する割合(%)を計算します。	大カテゴリに対する小カテゴリの構成比など。
ランク	指定した基準項目での順位を表示します。	売上ランキング、顧客満足度ランキングなど。

ピボットテーブルで「値フィールドの設定」を使う際の注意点

ピボットテーブルの「値フィールドの設定」は非常に強力な機能ですが、いくつか注意すべき点があります。これらの点に留意することで、より正確な分析結果を得ることができます。

基準フィールド・基準項目の選択ミス

前年比や構成比を計算する際に、「基準フィールド」や「基準項目」の選択を間違えると、意図しない結果が表示されます。例えば、前年比を計算したいのに、基準項目で「(前の項目)」を選択してしまうと、前月比が表示されてしまいます。必ず、表示したい指標に対応した基準フィールドと基準項目を選択しているか確認してください。

「データの個別の集計方法」と「標準の集計方法」の混同

「値フィールドの設定」ダイアログボックスには、「標準の集計方法」と「データの個別の集計方法」の2つのタブがあります。通常、合計や個数などの基本的な集計は「標準の集計方法」で行い、累計、前年比、構成比などの応用的な計算は「データの個別の集計方法」で行います。両者の違いを理解せずに操作すると、混乱する可能性があります。

元データの並び順の影響

「累計」や「差額」などの計算は、ピボットテーブルの行ラベルや列ラベルに配置したフィールドの並び順に依存します。特に日付フィールドなどで、昇順・降順が意図した通りになっていないと、累計値などが正しく計算されないことがあります。必要に応じて、フィールドの並び順を確認・修正してください。

フィールド名の変更

「値フィールドの設定」で集計方法を変更すると、フィールド名が自動的に「合計 / 売上」から「差額 / 売上」や「%合計 / 売上」のように変わります。このままでは分かりにくい場合があるので、「値フィールドの設定」ダイアログボックスの「カスタム名」欄で、より分かりやすい名前に変更することをおすすめします。例えば、「前年比」「売上構成比」などに変更すると、レポートが見やすくなります。

Excelのバージョンによる違い

「データの個別の集計方法」機能は、Excel 2010以降で利用可能です。Excel 2007以前のバージョンでは、この機能は使えません。古いバージョンのExcelを使用している場合は、手動で計算フィールドを作成するか、VBAを使用する必要があります。Microsoft 365やExcel 2019では、この機能は標準で搭載されており、問題なく利用できます。

まとめ

Excelのピボットテーブルにおける「値フィールドの設定」は、累計、前年比、構成比といった高度な分析を簡単に行うための強力な機能です。これらの指標を正確に把握することで、データの傾向や課題をより深く理解できるようになります。今回解説した「データの個別の集計方法」を使いこなせば、複雑な関数を記述することなく、これらの分析を瞬時に実行できます。ぜひ、ご自身のデータ分析にこれらの機能を活用してみてください。さらに、ピボットテーブルの「スライサー」や「タイムライン」機能と組み合わせることで、よりインタラクティブなデータ分析が可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】