「単純な集計表」をピボットテーブルの機能を活用して分析資料にする
その1に続いて、今回はピボットテーブルの具体的な操作方法を説明します。
- 「単純な集計表」をピボットテーブルの機能を活用して分析資料にする
「利用状況の集計表」から分析資料を作成する方法
ピボットテーブルを使えば介護保険の請求に使うための単純な「集計表」から、複雑な分析資料を作成する方法を説明します。
利用エリア別に、どの曜日の利用者が多いかの資料
曜日別に、どのエリアの利用者が多いのかの資料
月別の利用者数の推移の資料
集計表をピボットテーブルで使える「形式」にする
ピボットテーブルを使って分析資料を作成(可視化要約)するためには、何点かルールがあります。
- 「セルの連結」をしない
- 空白行(データの無い行)を作らない
- フィールド名(項目名)を複数行にしない
ピボットテーブル作成時の注意点
「ピボットテーブル」を作成する
ピボットテーブルの操作方法は簡単です、
- 「表」のどこか1カ所を「選択」(入力できる状態にする)
- リボンの「挿入」を選択
- リボンの左端の「ピボットテーブル」をクリック
- ピボットテーブルの作成ダイアログで「OK」をクリック
ピボットテーブルのフィールドで「行」、「列」、「値」に要素をドラッグ
- 「利用曜日」を行のボックスにドラッグ
- 「50音別」を列のボックスにドラッグ
- 「No.」を∑値のボックスにドラッグ
ピボットテーブルで集計表が作成されました
「利用曜日」と「50音別」のクロス集計で利用回数が集計されました。
集計表と連動した「グラフ」を作成
「ピボットグラフ」の機能で、集計表と連動したグラフを作成できます。
- 集計表のどこか1カ所を選択
- 「ピボットテーブル分析」タブを選択
- 「ピボットグラフ」を選択
- グラフの挿入ダイアログで「縦棒」を選択
- 縦棒グラフの種類で「積み上げ縦棒」を選択
- 「OK」をクリック
ピボットグラフが作成されました
「ピボットテーブル」の設定を変更する
集計方法の変更は「ピボットテーブルのフィールド」を操作するだけで簡単にできます。
「行」と「列」を入れ替る
ドラッグするだけで「行」と「列」を入れ替えて「軸」を「曜日」から「50音別」に変更することができます。
「行」と「列」が入れ替わり、グラフも自動的に書き換わります
軸が「利用曜日」から「50音別」に変更されました。
「タイムライン」や「スライサー」で分析したい要素を絞り込むことができる
「期間を操作できるタイムライン」や、「表示するフィールドを選択できるスライサー」を作成することができます。
タイムラインやスライサーをクリックするだけで分析(可視化)したい「要素」を切り変えることができます。
スライサーを作成する
「50音別」、「介護度」、「利用曜日」のスライサーを作成します。
- 集計表のどこか1カ所を選択します。
- 「ピボットテーブルツール」が表示されているのを確認
- 「分析」タブを選択
- 「スライサーの挿入」をクリック
- 「スライサーの挿入」ダイアログで、「50音別」、「介護度」、「利用曜日」にチェックを入れる
- 「OK」をクリック
スライサーを使えば、情報を絞り込むことができます
スライサーを使って、特定の「エリア」だけを表示する
「エリア」のスライサーを使えば、「皆実町」に絞り込むことができます。
タイムラインを作成する
データに「日付」形式のフィールド(項目)がある場合、「タイムライン」を作成することができます。
「利用日」のスライサーを作成
- 集計表のどこか1カ所を選択
- 「ピボットテーブルの分析」タブを選択
- 「タイムラインの挿入」をクリック
- タイムラインの挿入ダイアログで「利用日」を選択
※ 表示形式が「日付」のフィールドがない場合は、何も表示されません
タイムラインを使って、特定の「期間」だけを表示する
作成した「タイムライン」のバーをドラッグするだけで、集計対象期間を変更することができます。
タイムラインを使えば、利用状況を記録する台帳を「月別」に分ける必要がありません。
「日付」を気にすることなく入力し、年(年度)も別けずに入力しておけば、集計時に「前年同月」を分析する資料を作成することができます。
「前月比」や「前年比」をピボットテーブルで簡単に計算する(その1) - Excelの機能を活用して、事務作業の省力化や経営分析をする
「元のDATAになる表」の作り方に注意するだけです
分析の対象となる「表」の作り方さえ注意すれば、後の操作は簡単です。
「行と列の入れ替え」や、「分析対象の期間を変える」ことは、「VLOOKUP」や「COUNTIF」などの関数を使った方法では大変にな作業になってしまいます。
ピボットテーブルを使えば、目的別に「データ」を作成せず全てのフィールド(項目)が入力された「表」から簡単な操作で様々な集計方法の「分析資料」を作成することができます。
「タイムライン」の機能を使えば、「月」や「年(年度)」で分割されていない「表」から、任意の「期間」で集計することができます。
「月」や「年(年度)」でデータ(表)を分けずに入力しておけば、分析資料を作成する際に「前年同月比」や「時系列での推移」等の分析資料が簡単にできます。
大変な作業も、ピボットテーブルの機能を使えば「視覚化された操作」をするだけで簡単にできます。
「バザーの売り上げを集計する」や「町内会費の集金状況」なども、ピボットテーブルを使えば、簡単なリスト形式の「表」を作るだけで、そこから「可視化要約された立派な資料」を作ることができます。
ぜひ一度ピボットテーブルを使ってみてください。
- 「単純な集計表」をピボットテーブルの機能を活用して分析資料にする