【第4回】Excelでヒヤリハットを分析！ピボットテーブルとグラフで見える化する方法

第1回から第3回にかけて、フォームの作成からPower Query（パワークエリ）を使ったデータの整形まで、少しずつ準備を進めてきました。現場の職員が入力したデータが、集計しやすい形でExcelに取り込まれる仕組みは、すでに完成しています。

第4回では、いよいよ集計と見える化のステップに進みます。

今回は、第3回で作成したデータモデルをもとに、Excelの「ピボットテーブル」と「ピボットグラフ」を使って、ヒヤリハットの傾向をひと目で確認できるグラフを作成していきます。

• 「何月にヒヤリハットが多かったか」が分かる月別グラフ
• 「どの曜日にトラブルが起きやすいか」を確認できる曜日別グラフ
• 「他害・転倒・飛び出しなど、どの種類が多いか」を把握できるトラブル種類別グラフ

ここまでくれば、あとはExcelの得意な集計機能を組み合わせるだけです。
難しい関数やマクロ（VBA）は必要ありません。

まずは、ダッシュボードの土台となる集計表とグラフを作成していきましょう。

次回はいよいよ仕上げとして、スライサー（切り替えボタン）を使った絞り込み機能を追加し、実際の運用で使いやすいダッシュボードに完成させます。

全体の完成イメージを確認しよう

作業を始める前に、まずは最終的に完成する「ヒヤリハット自動集計ダッシュボード」のイメージを見てみましょう。

関数やマクロ（VBA）を使わなくても、Excelの標準機能だけでここまでの分析画面を作ることができます。

このダッシュボードは、大きく分けて次の2つの仕組みで構成されています。

傾向がひと目でわかる「3つのグラフ」

• 月別発生件数（縦棒グラフ）
  「夏休み期間に増えている」「新年度の4月に件数が多い」など、時期ごとの傾向を確認できます。
• 曜日別発生件数（縦棒グラフ）
  「特定の曜日だけ件数が多い」「職員配置や利用児童の組み合わせに原因があるかもしれない」といった、曜日ごとの傾向を把握できます。
• トラブル種類別件数（横棒グラフ）
  他害、転倒・衝突、飛び出し、器物破損など、どのトラブルが多いのかを把握できるため、優先して対策すべき内容を整理しやすくなります。

条件を切り替えられる「スライサー」

完成したダッシュボードでは、スライサーを使って「年度」「月」「発生場所」「発生場面」などの条件でデータを絞り込みながら分析できます。

例えば、「送迎車内」を選択すると、すべてのグラフが送迎車内のデータだけに切り替わります。

今回は、このダッシュボードの土台となる「3つの集計表」と「3つのグラフ」を作成していきます。
スライサーを使った絞り込み機能は、第5回で追加してダッシュボードを完成させます。

それでは、まずはダッシュボードの土台となる集計表とグラフを作っていきましょう。

ベースとなるピボットテーブルを作ろう

準備したデータ（クエリ）をもとに、集計の土台となる「ピボットテーブル」を作成していきましょう。

まずは1つ、基本となるピボットテーブルを作成します。あとはこれをコピーして使うことで、複数の集計表を効率よく作成できます。

クエリ（データモデル）からピボットテーブルを挿入する

前回の記事（第3回）では、Power Queryでデータを整えたあと、集計に使用する2つのクエリは「接続の作成のみ」でデータモデルへ読み込みました。

そのため、「ヒヤリハット一覧」シートはありますが、集計に使う元データのテーブルはシート上には表示されていません。

「本当にデータはあるの？」と不安になるかもしれませんが、大丈夫です。データはExcelの裏側にあるデータモデルへ保存されています。

ここからは、そのデータモデルを使って、集計用のピボットテーブルを作成していきましょう。

すると、画面右側に 「ピボットテーブルのフィールド」 パネルが表示されます。

画面右側に注目！3つのクエリが表示される

「ピボットテーブルのフィールド」パネルには、第3回で作成した「ヒヤリハット集計用」「トラブル種類集計用」「ヒヤリハット一覧」の3つのクエリが表示されます。

このうち、ピボットテーブルで使用するのは「ヒヤリハット集計用」と「トラブル種類集計用」の2つです。
「ヒヤリハット一覧」は、現場で詳細データを確認するための一覧表示用なので、今回の集計には使用しません。

通常のExcelでは、1つのピボットテーブルで扱えるのは1つのデータだけです。しかし、データモデルを利用することで、用途に応じて複数のクエリを使い分けながら集計できるようになります。

月別集計表を作ってみよう

画面の左側にはピボットテーブルの土台、右側には[ピボットテーブルのフィールド]という一覧が表示されているはずです。

難しそうに見えるかもしれませんが、項目を下のボックスへドラッグ＆ドロップするだけで集計できます。

それでは、まずは月別集計表を作ってみましょう。

これだけで、月ごとのヒヤリハット件数を自動で集計した表が完成します。

ピボットテーブルの便利なポイント
項目を配置するだけで集計表を作成できるのが、ピボットテーブルの大きな特徴です。
データが増えても[すべて更新]を実行するだけで最新の件数に集計し直せるため、毎月同じ集計をやり直す必要はありません。

集計用ピボットテーブルをコピーして増やそう

前のステップで、「月別」集計表が1つ完成しました。
ダッシュボードを作るために、さらに「曜日別」と「トラブル種類別」の集計表も作成していきます。

「もう一度、挿入タブから新しくピボットテーブルを作るのかな？」と思うかもしれませんが、実はその必要はありません。

ピボットテーブルは、完成したものをコピー＆ペーストすることで、同じ設定を引き継いだ新しい集計表を簡単に作成できます。

一から作り直すよりも手間が少なく、設定ミスも防ぎやすいため、この方法で効率よく集計表を増やしていきましょう。

曜日別集計表をコピペで作る

まずは、先ほど作成した「月別」の集計表をコピーして、「曜日別」の集計表に作り替えていきます。

これで、月別と曜日別のヒヤリハット件数を集計した2つ目の表が完成しました。

トラブル種類別集計表を「新規挿入」で作る

最後に、3つ目の「トラブル種類別（他害・転倒・飛び出しなど）」の集計表を作っていきます。

ここで1つ大切なポイントがあります。

第3回のパワークエリ編で、トラブルの種類を集計しやすくするために、「トラブル種類集計用」という専用のテーブルを作成しました。

トラブル種類別の集計だけは、この専用データを使う必要があります。そのため、先ほどのピボットテーブルをコピーするのではなく、新しくピボットテーブルを挿入して作成していきます。

これで、「月別」「曜日別」「トラブル種類別」の3つの集計表が完成しました。

運用のポイント
今後データ件数が増えても更新エラーが起きないよう、3つの集計表は縦ではなく横に並べて配置しています。
ピボットテーブルはデータ量に応じて下方向へ伸びるため、このレイアウトにしておくと長期間安心して運用できます。

見出しの文字を編集して分かりやすく整える

3つの集計表（ピボットテーブル）が画面に並びましたね！

ただ、このままでは見出しが「行ラベル」や「カウント／タイムスタンプ」といった、Excelが自動で付けた名前のままになっています。

集計内容がひと目で分かるように、最後に見出しを分かりやすい名前へ変更しておきましょう。

「カウント／タイムスタンプ」を「発生件数」に変更する

まずは、データの件数を表している「カウント／タイムスタンプ」、「カウント / トラブルの種類」という見出しを、「発生件数」に変更します。

1. 変更したいピボットテーブル内の 「カウント／タイムスタンプ」 をダブルクリックします。
2. 「値フィールドの設定」 画面が表示されたら、「名前の指定」 に 「発生件数」 と入力します。
3. ［OK］ をクリックします。

同じように、残りの2つの集計表も「発生件数」に変更します。

これで、集計表の内容がひと目で分かるようになります。

「行ラベル」を具体的な項目名に書き換える

続いて、その左隣にある「行ラベル」という文字も、それぞれの表の内容に合わせた名前へ書き換えていきます。

見出しを分かりやすくしておくことで、後から見返したときにも集計内容がひと目で分かるようになります。

1. 月別集計表の「行ラベル」を「月」に書き換える。
2. 曜日別集計表の「行ラベル」を「曜日」に書き換える。
3. トラブル種類別集計表の「行ラベル」を「トラブルの種類」に書き換える。

それぞれのセルをクリックし、そのまま文字を入力して上書きすれば完了です。

ピボットグラフで「見える化」しよう

せっかく3つの集計表が完成しても、数字だけでは傾向を把握しにくいことがあります。

そこでここからは、作成した集計表をもとに、ピボットグラフを使って見える化していきましょう。

ピボットグラフの便利なところは、元の集計表が更新されると、グラフも自動的に最新の状態へ更新される点です。
一度作成してしまえば、毎月グラフを作り直す必要はありません。

まずは、グラフを見やすく配置するための専用シートを作成しましょう。

グラフを配置する「ダッシュボード」シートを作成する

集計用のシートにそのままグラフを配置することもできますが、シートを「集計用」と「ダッシュボード」に分けておくと、画面が見やすくなり、今後データが増えても管理しやすくなります。

1.画面左下にある「＋」（新しいシート）をクリックします。
2.新しく作成されたシートのタブをダブルクリックし、シート名を「ダッシュボード」に変更します。
3.「ダッシュボード」シートのタブをドラッグして、一番左（先頭）へ移動します。

これで、グラフを配置するための「ダッシュボード」シートが準備できました。

月別グラフを作成する

まずは、一番上にある「月別集計表」をもとに、月ごとの発生件数がひと目で分かる縦棒グラフを作成します。

1. 一番上の「月別集計表」の中のセルを、どこでもよいので1か所クリックします。
2. 画面上の ［挿入］ タブ → ［ピボットグラフ］ をクリックします。
3. グラフの種類を選ぶ画面が表示されたら、左側の ［縦棒］ を選択し、［OK］ をクリックします。
4. そのままキーボードの [Ctrl] + [X] を押して、選択されているグラフを切り取ります（または右クリック ➔ ［切り取り］）。
   ※グラフが一時的に画面から消えますが、切り取られた状態になっているので問題ありません。
5. 画面左下の「ダッシュボード」シートに切り替えます。
6. グラフを配置したい位置（例：B2セル）をクリックします。
7. キーボードの [Ctrl] + [V] を押します（または右クリック ➔ ［貼り付け］）。

これで、月ごとのヒヤリハット発生件数を視覚的に確認できる縦棒グラフが完成します。

ピボットグラフの便利なポイント
ピボットグラフは、元になっているピボットテーブルと連動しています。
そのため、今後データが追加された場合も、「すべて更新」を実行するだけでグラフも自動的に最新の状態へ更新されます。

曜日別・トラブル種類別のグラフを作成する

2つ目、3つ目の集計表も、先ほどと同じ手順でグラフにできます。

① 曜日別集計表 → 縦棒グラフ

2つ目の「曜日別集計表」のセルをクリックし、［挿入］→［ピボットグラフ］ を選択します。

グラフの種類は、月別と同じ ［縦棒グラフ］ を選びましょう。

これで、「どの曜日にヒヤリハットが多いのか」をひと目で確認できるグラフが完成します。

② トラブル種類別集計表 → 横棒グラフ

3つ目の「トラブル種類別集計表」も同じようにクリックし、［挿入］→［ピボットグラフ］ を選択します。

こちらは ［横棒グラフ］ を選ぶのがおすすめです。

なぜトラブル種類は横棒グラフがおすすめ？
「飛び出し・離脱」や「自傷・パニック」など、項目名が長い場合、縦棒グラフでは文字が詰まったり斜め表示になったりして読みにくくなることがあります。
横棒グラフなら、項目名を左側に見やすく表示できるため、どのトラブルが多いのかを直感的に把握しやすくなります。

最後に、それぞれのグラフを月別グラフの下に並べるように配置しましょう。

グラフのフィールドボタンを非表示にしてタイトルを分かりやすく書き換える

3つのグラフが並びましたが、このままだとグラフの角にグレーの小さなボタンが表示されていたり、すべてのグラフが「集計」という同じタイトルになっていたりして、少し分かりにくい状態です。

最後に、見やすいダッシュボードになるよう整えていきましょう。

① グレーのボタン（フィールドボタン）を非表示にする

1. 作成したグラフをクリックして選択します。
2. 画面上部の ［ピボットグラフ分析］ タブを開きます。
3. 右側にある ［フィールド ボタン］ をクリックしてオフにします。

② グラフタイトルを書き換える

初期状態では「集計」というタイトルになっているため、内容が分かる名前へ変更しておきます。

1. グラフ内の「集計」という文字をダブルクリックします。
2. 編集できる状態になるので、分かりやすいタイトルへ変更します。

例）

• 月別グラフ：月別発生件数
• 曜日別グラフ：曜日別発生件数
• トラブル種類別グラフ：トラブル種類別件数

3. 右側に表示されている「■ 集計」という凡例は不要なので、クリックして ［Delete］キー で削除しておきましょう。

これで、3つのグラフが見やすく整理されたダッシュボードの完成形に近づきました。

まとめ

これで第4回の作業は完了です。

最初は「行ラベル」や「カウント／タイムスタンプ」といった分かりにくい見出しだった集計表も、見出しを整理してグラフを作成したことで、「いつ・どこで・どんなトラブルが多いのか」をひと目で把握できるダッシュボードの土台が完成しました。

ここまでできれば、自動分析システムの完成まであと一歩です。

次回の【第5回・最終回】では、いよいよ「スライサー（切り替えボタン）」を追加します。

スライサーを使えば、「年度」「月」「発生場所」「発生場面」などの条件でデータを絞り込みながら、3つの集計表とグラフを連動して分析できるようになります。

日々蓄積されるヒヤリハットデータを、より実践的に活用できるダッシュボードを一緒に完成させましょう。