ピボットテーブルを使って「光熱水費（経費）」を分析する

ピボットテーブルを使えば、「高度な分析作業」が簡単にできます

小規模な事業所において、「固定費」ともいえる「光熱水費」を分析することはとても重要です

　例えば、敷地内の排水管の破損すると水道料金が高額になってしまいます。

　通常の使用量からの増加が、一定の量を超えれば水道局から連絡がありますが、そうでない場合は担当者が気付くまで高額な水道料金を払い続けることになります。

　そんなときも水道料金の変化を毎月分析していれば即座に気付き、対応することができます。（※ 水道管の破損が原因の場合は、料金の減額措置があります）

　会計システムから出力される帳票だけでは情報不足で、細かな変化になかなか気づくことができません。

より詳しく分析するためには別資料を作成する必要があります。

 　分析するための資料は状況に応じてその都度作成するので、定型化が難しくその都度数式を使って作成することになりとても手間のかかる作業になってしまいます。

ピボットテーブルを使えば、単純な「リスト形式の表」が高度な分析資料になる

• 必要最小限の「情報」を単純に表にするだけ
• 元になる「表」はそのままなので、継続利用ができる
• 表を年や年度別に分ける必要がなく、連続して入力できる
• 「表」と同時に「グラフ」も作成できる
• 「表」や「グラフ」をリアルタイムに切り替えることができるので、プロジェクターを使ってのプレゼン時に資料を切り替えながら説明できる

条件に応じて集計し、必要に応じてフィールドを追加できます

　ピボットテーブルは元のデータ（集計表）から、数値を抽出して集計表（ピボットテーブル）を作成するので、元のデータ（集計表）に手を加える必要がありません。

　そのため集計方法を簡単に変更することができるので、色々な角度から分析することができます。

前年度との差異を「金額」で表す集計表

　年度で表を分けずに作成すれば、「年度を比較」した資料を作成することができます。

「年度」が混在した集計表

　　　

ピボットテーブルに「差額」のフィールド（項目）を追加する

　ピボットテーブルは元のデータ（集計表）には影響を与えずにフィールド（項目）を追加することができます。

　　　

前年度との差異を「比率」で表す集計表

　追加したフィールド（項目）の計算方法を簡単に変更することができます。　

　計算方法は「合計」以外にも「差額」や「比率」など様々な計算方法が用意されています。　　

　　　

ピボットテーブルに用意されている「計算方法」

　　　

ピボットテーブルと連動した「ピボットグラフ」を作成できます

　ピボットテーブルは、簡単な操作でピボットテーブル（集計表）と連動した、色々な種類のグラフが作成できます。

　複合集計やクロス集計など、設定が複雑になるグラフも作成できます。

前年度との月別の比較をしたグラフ

　複合集計で「前年同月との比較」や「年度別の推移」のグラフを作成できます。

　　　

科目別にすることもできます

　フィルターの機能を使って、必要な「科目」のみのグラフを作成できます。

　　　

　　　

リスト形式の単純な表から、様々な分析ができる

1. 元の表（データ）に手を加えて、集計用の「項目」を追加する必要がない
2. 「年」や「年度」を分割せずに一つの表にすることで、年（年度）での比較ができる
3. 「スライサー」や「タイムライン」の機能で必要な「情報」を絞り込んで表示することができる

１．元のデータ（表）に手を加えることなく、分析資料が作成できる

　ピボットテーブルは「元のデータ」から数値を抽出して集計表を作成するので、元のデータには影響を与えません。

　何度でもやり直しができるだけではなく、集計用のフィールド（列）を作成するなどの作業も必要ありません。

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

1. 表のどこか1カ所を選択します
2. 「挿入」タブを選択
3. 「ピボットテーブル」をクリック
4. テーブルまたは範囲を選択で「表全体」が選択されているのを確認
5. ピボットテーブルレポートを配置する･･･で、「新規ワークシート」にチェックを入れる

 　　　

新規ワークシートの「Sheet３」にピボットテーブルが作成される

1. 「日付」を行のボックスにドラッグ
2. 「科目」を列のボックスにドラッグ
3. 「金額」を∑値のボックスにドラッグ

　　　

各項目が集計され「表」が作成されました

　「日付」のフィールドを「行」のボックスにドラッグするだけで、「年」、「月」、「日付」にグループ化されそれを使って分析ができる。

　それらを複数組み合わせることで「複合集計」になります。　

　　　

※ ピボッテーブルの作成方法は、こちらも参考にしてください

k-ohmori9616.hatenablog.com

２．タイムラインの機能を使って、必要な年（年度）を選択できる

　タイムラインの「バー」を選択したりドラッグするだけで、「月」や「期間」を選択することができます。

タイムラインの作成方法

1. ピボットテーブル内のどこか１カ所を選択します
2. 「ピボットテーブル分析」タブを選択します
3. 「タイムラインの挿入」をクリックします
4. タイムラインの挿入ダイアログで、「日付」にチェックを入れます
5. 「OK」をクリックします

　　　

タイムラインを使って、集計する「期間」を選択します

　　　

k-ohmori9616.hatenablog.com

 ワンタッチでグラフが作成できます

　ピボットテーブルを作成するとそれに連動して「ピボットグラフ」が作成されます。

1. 表のどこか1カ所を選択
2. 「ピボットテーブルツール」が表示される
3. 「分析」タブを選択
4. 「ピボットグラフ」を選択
5. グラフの挿入で「集合縦棒」を選択
6. 「OK」をクリック

　　　

「日付」と「科目」の複合集計もワンタッチです

　「年」と「月」で複合集計されたグラフが作成されます。

　「年」別に「月」毎に集計したグラフで、「年別に月の推移」を分析することができます。

　　　

k-ohmori9616.hatenablog.com

ドラッグだけで集計方法を変えることができます

　ボックス内に配置するフィールド（項目）の上下の位置を変えるだけで、集計方法が変わります。

　「月」別に「年」毎に集計したグラフで、「去年の４月はどうだったか」を分析できます。

前年同月の分析

　「年」➡「月」の順番で複合集計をすることで、　月別に「前年度」との比較をする「前年度比較の集計表」を作成することができます。

　　　

複数年の「通年」の分析

　軸に「年」と「月」のフィールド（項目）を配置することで、複数年を通年表示にすることができます。

　複数年の表示にすることで「年間の推移」を分析することができます　　　

　　　

k-ohmori9616.hatenablog.com

３．スライサーの機能を使って、フィールド（項目）を選択できる

　「ラベル」や「フィルター」を使っても、フィールド（項目）を指定して表示させることはできますが、「スライサー」を使えば簡単に操作することができます。

1. 表のどこか1カ所を選択
2. 「ピボットテーブルツール」が表示される
3. 「分析」タブを選択
4. 「スライサーの挿入」をクリック
5. スライサーの挿入で「科目」にチェックを入れる
6. 「科目」のスライサーが作成される

　　　

スライサーで、集計したい「科目」を選択することができます

　「スライサー」をクリックするだけで、選択した科目が集計（表示）されます。

　　　

k-ohmori9616.hatenablog.com

フィールドを追加することで、高度な分析ができます

　ピボットテーブル（集計表）に、フィールド（項目）を追加することで詳細な分析資料を作ることができます。

1. 「金額」を「∑ 値」ボックスにドラッグします
2. 「合計／金額２」のラジオボタン「▼」をクリック
3. 「値フィールドの設定」をクリック
4. 値フィールドの設定で「計算の種類」を選択
5. 基準フィールドで「年」を選択
6. 基準アイテムで「（前の値）」を選択　　　

　　　

値フィールドの「名前」と「計算方法」を変更します

1. 名前の指定で、名称を「差額」に変更します
2. 計算の種類を「基準値との差分」にします　　　

　　　　

フィールドの「名前」と「計算方法」が変わりました

　　　

 計算の種類を「基準値に対する比率」に変更します　

　フィールドの計算方法を、基準となる数値との「数値の差額」から「比率」に変更することで、「どれくらい変化したのか」がより明確になります。

　　　　

　値フィールドの設定で、計算の種類を「基準値に対する比率」に変更します。

　　　　

ピボットテーブルにはその他の「計算方法」も用意されています

　　　　

k-ohmori9616.hatenablog.com

 前年同月から「どのくらい変化したか」を表す資料

　「基準値に対する比率」のフィールド（項目）を新しく追加することで、基準となる前年同月からどのくらい変化したかを「％（比率）」で表現する分析資料になりました。　　　

　　　

　このように必要最小限の「情報」のみを入力した、単純なリスト形式の「表」から様々な「分析資料」を作成することができます。

　「日付」や「科目」に分けて入力する必要はありません、１つの「表」に連続して入力していくだけで、必要に応じて「タイムライン」や「スライサー」の機能を使って、分析資料を作成することができます。

　通常であれば「SUMIFS関数」などを使って集計しなくてはならないような作業も、ドラッグ操作だけで簡単にできます。

k-ohmori9616.hatenablog.com