【第3回】Excelでヒヤリハットを分析！Power Queryで集計データを整える方法

前回の記事では、Googleフォームに集まったヒヤリハットデータをExcelで活用できるように、GoogleスプレッドシートとExcelを連携する方法を紹介しました。

しかし、

「集まったデータを毎月集計するのが大変…」
「どの場所や時間帯でヒヤリハットが多いのか把握したい…」
「グラフや集計表を作るたびに同じ作業を繰り返している…」

と感じている方もいるのではないでしょうか。

そこで今回は、前回接続した Power Query を使って、集計や分析に使いやすい形にデータを整えていきます。

一度仕組みを作ってしまえば、翌月以降は「すべて更新」をクリックするだけで、最新のデータを取り込み、同じ流れを自動で実行できるようになります。

また、「年度」「月」「曜日名」といった集計に必要な項目を追加したり、複数選択されたトラブルの種類を正しく分析できるようにしたりすることで、次回のピボットテーブルによる集計やグラフ作成がスムーズになります。

それでは、Googleフォームで集めたデータを、分析しやすい形に整えていきましょう。

パワークエリ① 年度・月・曜日・時間帯を追加して集計用データを作る

第2回の最後で「データの変換」をクリックして表示された画面が、Power Query（パワークエリ）エディターです。

現在は、Googleフォームから取得したデータが表示されている状態です。ここでは、後から集計や分析をしやすくするために、データを整えていきます。

まずは1つ目のPower Queryとして、分析に使う基本データを作成します。

Excelへデータを読み込む前に、「年度」「月」「曜日名」「時間帯」の3つの列を追加しておきましょう。

これらの項目を作成しておくことで、後からピボットテーブルで年度別・月別・曜日別・時間帯別の傾向を確認しやすくなります。Power Queryを使えば、発生日や発生時刻から必要な情報を簡単に取り出せます。

それでは、実際に設定していきましょう。

集計用の列「月・曜日名」を追加する

ここでは、発生日から「月」「曜日名」の列を作成します。

1. 「発生日」の列ヘッダーをクリックして選択します。
2. 画面上部の「列の追加」タブをクリックし、「日付」ボタンを押します。

3. 「月」→「月」をクリックします。
   　→ 「月」の列が追加されます。
4. もう一度「列の追加」タブの「日付」ボタンを押します。
5. 「日」→「曜日名」をクリックします。
   　→ 「曜日名」の列が追加されます。

これで、「月」「曜日名」の3つの列を追加できました。

集計用の列「発生時間帯」の列も追加する

「ヒヤリハットが何時頃に起きやすいか」を分析したい場合、元データのままだと「14:35」のように分単位まで記録されているため、そのままでは集計しづらいことがあります。

そこで、Power Queryの機能を使って「何時台か」だけを取り出した列を追加しておきましょう。こちらは難しい数式は不要で、マウス操作だけで簡単に作成できます。

発生時間帯（時）の追加手順

1. Power Query画面で、「発生時刻」の列ヘッダーをクリックして選択します。
2. 画面上の ［列の追加］ タブ → ［時刻］ → ［時］ → ［時］ の順にクリックします。
3. 表の一番右側に、「時」という新しい列が追加されます。
4. 追加された「時」という列の見出し（ヘッダー）をダブルクリックします。
5. 名前を編集できる状態になるので、「時間帯」と入力して ［Enter］ キーで確定します。

集計用の列「年度」を追加する

福祉の現場や行政の書類では、カレンダー通りの西暦（1月〜12月）ではなく、4月始まりの「〇〇年度」で管理されることが多いですよね。

今後データを継続的に蓄積していくために、元データの段階で「年度」を自動計算する列を追加しておきましょう。

この列を作成しておくだけで、後から「令和8年度のデータだけを表示する」といった集計や分析がしやすくなります。

年度列を追加する手順

1. 画面上の ［列の追加］ タブ → ［カスタム列］ をクリックします。
2. 表示された設定画面で、以下のように入力します。

• 新しい列名：年度
• カスタム列の数式：（以下のコードをそのまま貼り付けます）

"令和" & Text.From((if Date.Month([発生日]) <= 3 then Date.Year([発生日]) - 1 else Date.Year([発生日])) - 2018) & "年度"

3. ［OK］ をクリックします。

これで「令和7年度」「令和8年度」のような「年度」の列が自動で追加されます。

今回は分かりやすさを優先して「令和○年度」を直接表示する数式を使っています。
より汎用的に作りたい場合は、Excelの和暦表示形式（ggge）を利用する方法もあります。

これで、「時間帯（時）」「年度」との列が追加できました。
これらの列を作成しておくことで、後からピボットテーブルやスライサーを使って、

• 年度ごとの傾向を比較する
• 何時台にヒヤリハットが発生しやすいかを確認する

といった分析がしやすくなります。

管理番号を追加する

データを1件ずつ区別しやすくするため、自動で連番の管理番号を追加しておきましょう。

1. 画面上部の ［列の追加］ タブを開きます。
2. ［インデックス列］ の▼をクリックし、［1 から］ を選択します。
3. 追加された「インデックス」列の見出しをダブルクリックし、「管理番号」 に変更します。

発生時刻から日付を取り除き、時刻だけを表示する

Googleフォームから「時刻」を取り込むと、「1899/12/31 14:30:00」のように日付が付いて表示されることがあります。

これは、Excelが時刻データを日付とセットで管理しているためです。

このままでも利用できますが、後で時間帯ごとの集計や分析を行いやすくするため、時刻だけの形式に変更しておきましょう。

1. 「発生時刻」の列ヘッダーをクリックして選択します。
2. 列ヘッダー左側に表示されているデータ型のアイコン（カレンダーや時計のマーク）をクリックします。
3. 表示されたメニューから「時刻」を選択します。
4. 確認メッセージが表示されたら、［現在のものを置換］をクリックします。

すると、「1899/12/31 14:30:00」と表示されていたデータが、「14:30:00」のように時刻だけの表示になります。

クエリに分かりやすい名前を付ける

画面左側にあるクエリの名前は、初期設定では「フォームの回答 1」になっていると思います。

このままでも問題ありませんが、後から見返したときに分かりやすいよう、名前を変更しておきましょう。

1. 左側のクエリ一覧にある「フォームの回答 1」を右クリックします。
2. メニューから「名前の変更」をクリックします（名前をダブルクリックして変更しても構いません）。
3. 「ヒヤリハット集計用」と入力して、[Enter]キーを押します。

これで、月別や曜日別などの集計に使うクエリの準備ができました。

パワークエリ② 複数選択の項目を分析用データに変換する

ここからは、「トラブルの種類」などの複数選択の項目を正しく集計できるようにしていきます。

Googleフォームのチェックボックス（複数選択）で入力されたデータは、Excel上では
「他害, 器物破損」
のように、1つのセルにまとめて取り込まれます。

このままでは、「他害」と「器物破損」を個別に集計できないため、Power Queryを使って1件ずつ分けて扱えるようにします。

ただし、複数選択を分解すると、1つの報告が複数の行に分かれるため、そのデータで全体の発生件数を集計すると件数が実際より多くなってしまいます。

そこで、先ほど作成した「月別・曜日別の集計用クエリ」はそのまま残し、新しく「トラブルの種類を分析するためのクエリ」を別に作成します。

このように用途ごとにクエリを分けておくことで、全体件数とトラブルの種類別集計の両方を正しく分析できるようになります。

それでは、2つ目のクエリを作成していきましょう。

クエリを複製する

まずは、先ほど作成したクエリをコピーします。

1. パワークエリ画面の左側にある「クエリ」の一覧から、「ヒヤリハット集計用」を右クリックします。
2. メニューから「複製」をクリックします。

3. 新しく作成されたクエリの名前を、分かりやすく「トラブル種類集計用」に変更しておきます。

これで、元の集計用クエリを残したまま、トラブルの種類を分析するためのクエリを作成できました。

カンマで列を分割する

Googleフォームで「トラブルの種類」などをチェックボックス（複数選択）にしている場合、Excelには
「他害, 器物破損」のように、1つのセルの中に複数の内容がまとめて取り込まれます。

このままでは、「他害」と「器物破損」が1つのデータとして扱われてしまい、種類ごとの件数を正しく集計できません。

そこで、Power Queryを使って複数の回答を1件ずつ分けて扱えるようにしておきます。

まずは、1つのセルにまとめられている複数の回答を列ごとに分けていきます。

1. 「トラブルの種類」の列ヘッダーをクリックして選択します。
2. 画面上部の「ホーム」タブ → 「列の分割」 → 「区切り記号による分割」をクリックします。
3. 区切り記号は「コンマ」を選択し、「OK」をクリックします。

選択肢には「コンマ」と表示されていますが、「カンマ（,）」を意味しています。
そのまま「コンマ」を選択して進めてください。

すると、「トラブルの種類.1」「トラブルの種類.2」…というように、複数選択された項目が別々の列に分かれて表示されます。

文字の前後にある余分な空白を削除する

ここで、後の集計で同じ項目が別々に扱われないよう、文字の余分な空白を削除しておきます。

Googleフォームから取り込んだデータでは、カンマで区切られた2つ目以降の項目の先頭に、目に見えない半角スペースが入ることがあります。

このまま集計すると、Excelが「他害」と「 他害（先頭にスペースあり）」を別の項目として扱ってしまい、正しく集計できない場合があります。

1. 新しく分割された列（トラブルの種類.1、.2、.3など）を、Shiftキーを押しながらすべて選択します。
2. 画面上部の「変換」タブをクリックします。
3. 「書式」→「トリミング」の順にクリックします。

これで、文字の前後にある余分な空白が削除されます。

画面上では変化が分かりにくいですが、後の集計で同じ項目が別々に扱われるのを防ぐことができます。

選択した列のみをピボット解除する

最後に、横に分かれている複数の列を、集計しやすい縦方向のデータに変換します。

1. 先ほどトリムした列（「トラブルの種類.1」など）がすべて選択された状態のまま、列ヘッダーの上で右クリックします。
2. メニューから「選択した列のみをピボット解除」をクリックします。

すると、横に並んでいた複数の列が縦方向に並び替えられ、1件ずつ集計しやすい形に変換されます。

列名を整理してExcelへ読み込む

ピボット解除を行うと、新しく「属性」と「値」の2つの列が作成されます。

• 属性：元の列名（トラブルの種類.1、.2、.3 など）
• 値：実際のデータ（他害、器物破損、自傷・パニックなど）

「属性」という列が突然現れるため、失敗したのではないかと思うかもしれませんが、これは Power Query の仕様なので問題ありません。

1. 「属性」列は不要なので、列ヘッダーを右クリックして「削除」をクリックします。
2. 「値」列の名前をダブルクリックし、「トラブルの種類」に変更します。

これで、トラブルの種類ごとに集計できるデータが完成しました。

最後に、このデータをExcelへ読み込みます。今回は後からピボットテーブルで集計できるようにするため、通常とは少し異なる方法で読み込むのがポイントです。

1. パワークエリ画面の左上にある ［閉じて読み込む］ の下にある ［▼］ をクリックします。
2. 表示されたメニューから、［閉じて次に読み込む…］ を選択します。

注意！
上側の ［閉じて読み込む］ ボタンをそのままクリックすると、設定画面を開かずにデータが読み込まれてしまいます。必ず ［▼］ から ［閉じて次に読み込む…］ を選択してください。

3. 「データのインポート」画面が表示されます。
4. データの表示方法は、「テーブル」 や 「接続の作成のみ」 など、目的に応じて選択します。
5. 画面下部にある ［☑ このデータをデータモデルに追加する］ に必ずチェックを入れます。
6. 最後に ［OK］ をクリックすれば完了です。

一度作ればOK！「すべて更新」で最新データを取り込む

次回以降、Googleフォームに新しいヒヤリハットの回答が追加されても、もう一度同じ設定をやり直す必要はありません。

Excelを開いて、次の操作を行うだけで最新のデータを取り込めます。

まとめ

今回は、Power Queryを使って、Googleフォームから届いたデータをExcelで集計しやすい形に整える方法を紹介しました。

ポイントを振り返ると、

1つ目のデータ（ヒヤリハット集計用）

• データの行数を増やさず、月別や曜日別などの発生件数を集計するための基本データ

2つ目のデータ（トラブル種類集計用）

• クエリを複製し、複数選択された項目を1件ずつ集計できるようにしたデータ

という2つのデータを用意しました。

このように用途ごとにデータを分けておくことで、全体の発生件数とトラブルの種類別集計を、それぞれ正しく行えるようになります。

一度設定してしまえば、次回以降は「すべて更新」をクリックするだけで、Power Queryが同じ処理を自動で実行してくれます。

次回はいよいよ、今回作成した2つのデータを使って、ピボットテーブルで月別・曜日別・トラブル種類別の集計を行っていきます。