ピボットテーブルで「前年同月比」の集計資料を作成する

「前年同月比」の資料作成は、複合検索となるため数式が複雑になってしまいます

　ピボットテーブルを使えば、複合検索の「前年同月比」の集計資料の作成が簡単にできます。

2016年に対する2017年の売上比率の集計表

　前年同月比に「2017年は2016年に対して何％だったか」を表示。

　　　

商品の売上高を「前年同月」で比較したグラフ

　※ グラフの場合は、視覚情報で「比率」が表現できるので「売上高」で集計する。

　　　

「前年同月比」の集計方法は、前年度からの売り上げの変化を把握する方法

　前年同月と比較する方法として「差分」と「比率」があります。

　「差分」での集計では、変化の絶対値の把握はできますが、変化の重要度が分かりにくい欠点があります。

　「比率」での集計では、売上額が大きな商品の状況の変化は把握できますが、売上額が小さな商品の状況の変化が分かりにくくなります。

　「差分」で集計して「1万円の減少」は、１カ月の売上が100万円の商品では大きな問題ではありませんが、1カ月の売り上げが10万円の商品の「1万円」の減少は大きな問題です。

　「1万円の減少」を「比率」で集計すれば、「99％」と「90％」になり重要度が一目瞭然です。

「差分」で集計

• 売上額 100万円   ➡  99万円　　  差額 1万円
• 売上額   10万円   ➡    9万円　　  差額 1万円

「比率」で集計

• 売上額 100万円   ➡  99万円　　  比率 99％
• 売上額   10万円   ➡    9万円　　  比率 90％

　ピボットテーブルを使っての集計方法は、単純な「合計」、「累計」、「平均」だけではなく、「比率」、「差分」、「差分の比率」など複雑な集計（計算）方法が用意されています。

前年同月比をピボットテーブルで作成

　集計方法の「前年同月比」は分析の方法として有効な方法です。

　　　

「基準値のフィールド」を作成する

　「前年同月」を比較するためには「年」と「月」のフィールドが必要です。

　　　

「日付」のフィールド（項目）がシリアル値の場合は数値に変更する

「日付」のフィールドはシリアル値を「表示形式」で、「〇〇年〇〇月〇〇日」のように変更している場合が多く、シリアル値のままでは集計の「基準値」には使えません。

※ シリアル値は1900年1月1日を「1」とした連番で、2019年5月31日のシリアル値は「43616」になります。

　　　

基準値のフィールドを追加する 

　基準値を設定する方法は、ピボットテーブルの「グループ化」を使う方法もありますが、今回はフィールドを追加する方法を説明します。

元のデータに「年」と「月」のフィールドを作成する

　YEAR関数とMONTH関数を使って「年」と「月」のフィールドを作成します。

　　　

YEAR関数を使って「年」のフィールドを作成

　　　数式「 = YEAR (C2)  」

　　　

MONTH関数を使って「月」のフィールドを作成

　　　数式「 =MONTH (E2)  」

　　　

「年」と「月」が文字列として抽出されました

　　　

ピボットテーブルを作成します

　「表」がルール通りに作成されていれば、表の１カ所を選択すれば「表全体」がピボットテーブルでの集計対象になります。

1. 集計するリスト「表」のどこか１カ所を選択します
2. 「挿入」タブを選択
3. 「ピボットテーブル」をクリック
4. ピボットテーブルの作成で、範囲が正しく選択されていることを確認して「OK」をクリック

　　　

ピボットテーブルのフィールドダイアログで、フィールド（項目）をドラッグします

　集計したいフィールド（項目）をドラッグするだけで自動的に集計されます。

1. 「年」を「行のボックス」にドラッグする
2. 「月」を「行のボックス」の「月」の上にドラッグ（※ 「上・下」の位置で集計方法が変わります）
3. 「分類」を「列のボックス」にドラッグ
4. 「金額」を「∑値のボックス」にドラッグ
5. ２つ目の「金額」を「∑値のボックス」にドラッグ（※ 後で「前年同月比」に名称と計算内容を変更します）

　　　

 「合計／金額２」の設定を変更します

　「∑値ボックス」にドラッグした、２つ目の「金額」のフィールド「合計／金額２」の名称と計算方法を変更します。

　計算方法を「基準値に対する比率」とし、基準値を「2016」にします。

1. 「合計／金額２」のラジオボタン「▼」をクリック
2. 「値フィールドの設定」をクリック
3. 値フィールドの設定で名前の指定を「前年同月比」と入力
4. 「計算の種類」タブを選択
5. 計算の種類を「基準値に対する比率」を選択
6. 基準フィールドを「年」に設定
7. 基準アイテムを「2016」に設定　

　　　

前年同月比の「フィールド」が作成されました

　　　

条件付き書式を使って、資料を見やすくする

　ピボットテーブルを使えば、グラフを作成して視覚化した資料を作ることができますが、集計方法が複雑なので、グラフ化は難しいので、条件付き書式を使って資料を視覚化すれば分かり易い資料になります。

1. 前年同月比が「110％以上」を「緑」で表示
2. 前年同月比が「100％以下、８０％以上」を「黄」で表示
3. 前年同月比が「80％以下」を「赤」で表示

　　　

条件付き書式の設定方法

　条件付き書式を使えば、「集計表」の内容を変更せずに情報を視覚化することができます。

1. 前年同月比のフィールド全体を選択します
2. 「ホーム」タブを選択
3. 「条件付き書式」をクリック
4. 「セルの強調表示ルール」をクリック
5. 「指定の範囲内」をクリック
6. 条件を入力します

　　　

条件付き書式の内容

　「条件付き書式ルールの管理」のダイアログで、「新規ルール」を使えば同じ範囲（表）に複数の条件付き書式を設定することができます。

　複数の条件を設定する場合は、「上から順番に条件が適用される」ので上下の並びには注意する。

1. 「80～90％」を「黄色」で塗りつぶす
2. 「60～80％」を「赤」で塗りつぶす
3. 「110～150％」を「緑」で塗りつぶす

　※「条件付き書式を使って目で見て情報が伝わる･･･」も参考にしてください。

　　　

ピボットテーブルには、色々な集計方法が用意されています

集計方法

　「値フィールド」のダイアログで、「集計方法」を選択することができます。　

　　　

「計算の種類」はいろいろな方法が用意されています

　「比率」だけではなく、「差分」の集計もできます。

　　　

　ピボットテーブルを使えば、数式（関数）を使った方法では複雑になってしまう「前年同月比」などの集計も簡単な操作で作成することができます。

　分析資料を作成する際に、グラフを使って視覚化することも簡単です。

　条件付き書式と組み合わせることもできますので、うまく使いこなして資料作成に役立ててください。

k-ohmori9616.hatenablog.com