「前月比」や「前年比」をピボットテーブルで簡単に計算する（その１）

「ピボットテーブル」を使えば、「前月比」や「前年比」を数式を使うことなく簡単にできます　

　「ピボットテーブル」を使えば、単純なリスト形式の「集計データ」から様々な分析をすることをできます。

 「前月比」や「前年比」などの「２期比較」の資料も数式を使わずに、「ドラッグ」操作だけで作成することができます。　

集計表を作成すると、その集計表に連動したしたグラフも作成されます

　ピボットテーブルで「集計表」を作成すると、その表に連動した「グラフ」が自動的に作成されます。

2016年と2017年の同月を並べて表示し、さらに3種類の商品をクロス集計 

　　　

前月比を求め、商品分類ごとに成長率を調べたい

　商品分類別の「前月比」を集計し、さらにそれぞれの「成長率」を表す分析資料を作成します。

　　　

数式を使って「前月比」を計算する

　「前月比」は下の計算式で算出します。

　　 ( 当月の売上金額－前月の売上金額 ) ÷ 前月の売上金額 　

 　通常は数式を使って前月比を計算しますが、ピボットテーブルは数式を使わずに集計することができます。

「売り上げデータ」を用意します

　データの形式は、必要なフィールド（項目）をリスト形式で作成した単純な様式でかまいません。

　　　

 ピボットテーブルに変換します

　ピボットテーブルの作成は、ピボットテーブル化したい「表」のどこか1カ所を選択するだけで「範囲」を自動的に判断して作成されます。

　そのため「表」の作成時には、周囲に「空白」を設ける必要があります。

1. 「表」のどこか１カ所のセルを選択します
2. 「挿入タブ」を選択します
3. 「ピボットテーブル」をクリックします
4. 「ピボットテーブルの作成」ダイアログで「OK」をクリックします

　 ※ ピボットテーブルの作成ダイアログの内容は、基本的に初期状態で問題ありません。

　　　

フィールド（項目）を「列」､「行」､［∑値」のボックスにドラッグします

1. 「日付」を「列」のボックスにドラッグします
2. 「分類」を「行」のボックスにドラッグします
3. 「金額」を「∑ 値」のボックスにドラッグします

※ 「日付」をボックスにドラッグすると、自動的に「年」と「四半期」のフィールドが作成されます。

　　　

「四半期」のフィールド（項目）を削除します

　「日付」をボックスにドラッグすることにより自動作成された「四半期」は、今回は不要なので削除します。

1. 「四半期」のラジオボタン「▼」をクリック
2. 「フィールドの削除」をクリック

※ フィールドをボックスの外にドラッグするだけでも削除できます。　

　　　

「前月比」を表示する資料を作成します

「値」フィールドを「基準値との差分の比率」に変更します

1. 「合計／金額」のラジオボタン「▼」をクリック
2. 「値フィールドの設定」をクリックします
3. 「計算の種類」タブを選択
4. 計算の種類を「基準値との差分の比率」に設定
5. 基準フィールドのダイアログで「日付」を選択
6. 基準アイテムのダイアログで「（前の値）」をクリック

　　　

「列」フィールドに「日付（月）」を表示させます

　「列」の表示が、「年」になっているので「日付」の計算結果が表示できずエラーになっているので、「列」フィールドを展開して「日付（月）」を表示します。

　　　

 「前月比」が表示されました

　　2016年1月が、データの最初の月なので「前月比」は表示されません

　　　

「前年比」を表示する資料を作成します

「基準」フィールドを「年」に変更して「前年比」を表示します

1. 計算の種類を「基準値との差分の比率」にします
2. 基準フィールドのダイアログを「年」にします
3. 基準アイテムのダイアログを「前の値」にします

　　　

前月売り上げからの「増減額」の資料を作成する

　ダイアログで「基準値との差分」を選択するだけで、数式を使うことなく「前月との差額」を表示することができます。

前月との「差額」を表示して、「増減額」を表示します

1. 「計算の種類」タブを選択
2. 計算の種類を「基準値との差分」に設定
3. 基準フィールドを「日付」に設定
4. 基準アイテムを「前の値」に設定

　　　

項目数を増やして「当月の売上額」と「前月比」を同時に表示する

　基準値との「比率」や「差分」を表示するだけでは、資料として分かりにくいので、項目を増やして「基準値を表示」します。

1. フィールド「金額」を「合計／金額」の下にドラッグして、「合計／金額２」を作成します 

            

「合計／金額」のフィールドを「基準値」に戻します

　前の作業で計算方法を変更している「合計／金額」のフィールドの計算方法を「計算しない」にして「基準値」に戻します。

　　　

 追加作成した「合計／金額2」のフィールド名を変更します

　ピボットテーブルの「フィールド名」は、元の「表」のフィールド名が適用されていますが設定で自由に変更することができます。

1. 「合計／金額2」のラジオボタン「▼」をクリック（※ 画面は変更後）
2. 「値フィールドの設定」をクリック
3. 名前の設定ボックスで「前月比」と入力

　　　

「前月比」のフィールドの計算方法を「基準値との差分の比率」に変更します

1. 「前月比」のラジオボタン「▼」をクリック
2. 「値フィールド」をクリック
3. 基準フィールドのダイアログを「日付」に設定
4. 基準アイテムのダイアログを「前の値」に設定

　　　

数値に、千円単位の「位取り」を設定

　表を見やすくするために、数値に千円単位の位取りの「 , 」を設定します

　　　

「前月比」を表示する資料が完成しました

　　　

同じ操作で「前年比」の資料も簡単に作成できます

　　　

「前年同月」を比較する資料も簡単にできます

　「去年の同じ月はどうだったか？」を分析するための資料の作成も簡単です。

　　　

ピボットテーブルを使えば「グラフ」の作成も簡単です

　前年同月を比較するような「複合集計」のグラフの作成も簡単です。

　　　

　ピボットテーブルを使って「分析資料」を作成すれば、「元のデータに手を加えない」という特性を生かして、色々な集計方法を瞬時に切り替えることができます。

　ピボットテーブルを使って、グラフを作成する方法と、色々な分析資料を作る方法は「その２」で説明します。

k-ohmori9616.hatenablog.com