はじめに
※この記事は「LTSグループ Advent Calendar 2023」に参加しています
はじめまして！株式会社エル・ティー・エスのそねはらと申します。
私は非エンジニアながらデータを扱うことが多々あり、最近は Power Query にはまっています。
今回、LTSグループでアドベントカレンダーということで、 Power Query に関するTipsをご紹介できればと思います。
想定読者
- Microsoft Formsで複数回答した設問を、他の設問とクロス集計したい人
- 上記の内容を、 コーディングせずに 実現したい人
- 同じく、Excel以外を インストールせずに 実現したい人
やりたいこと
Microsoft Forms で複数回答をつかうと・・・
手軽に利用できるアンケートツールとして、Microsoft Formsを利用することが多いと思います。
例えば、Formsを利用すると、以下のようなアンケートが作れます。
設問1でサービスを友人や仕事仲間に進める可能性（いわゆるNPS）を10段階で確認し、設問2・3ではサービスの満足・不満足の理由を確認しています。設問2・3は、複数回答を選択できるようにしています。
Formsには簡易的に結果を分析する機能があり、下記のようなグラフを見ることができます。
しかし、上記の分析機能はあくまで簡易的なものなので、フィルタやクロス集計といった操作はすることができません。
一方で、今回のアンケートだと、 NPSが高いユーザーは、何に満足感を感じているかや、反対に NPSの低いユーザーが不満に感じているポイントなどのクロス集計をかけたいところです。
ゴールイメージ
改めて、今回の分析のゴールとしては NPSと満足点・不満足点でクロス集計 をおこなうこととします。
具体的には、下記のようなピボットテーブル・グラフを作ることを目指します。
これを実現するために、下記のように 「NPS」「満足点」「不満足点」 をそれぞれ管理する3つのテーブルを作り、IDをキーにしてリレーションシップを張ります。
Power Query を使ってみる
まずはExcelにダウンロード
Formsでは、回答結果をExcelにダウンロードできる機能があります。
クロス集計をおこなうために、Excelをダウンロードしてみました。
※一部列項目を非表示にしています。
ダウンロードしたデータを見ると、複数回答の項目は下記のように 「;（セミコロン）」 区切りで回答が記載されていました。
料金が安い;UIが使いやすい;機能が充実している;サポート体制が充実している;
Power Queryを立ち上げる
データの処理のために、 Power Query を立ち上げます。
回答データのテーブル内にあるセルにカーソルを合わせた状態で、 「データ」 > 「テーブルまたは範囲から」 を選択します。
すると、 Power Query エディタ がポップアップで立ち上がります。
ここから、クロス集計に必要なデータの処理をおこなっていきます。
テーブルの複製・名前の変更
Power Query エディタの左側にあるナビゲーションウィンドウにてテーブル名を右クリックすることで、 テーブルの複製・名前の変更 をすることができます。
今回は計3つのテーブルを作成したいので、Table1から2回複製をおこないます。
併せて、区別がしやすいように適当にテーブル名をつけます。（今回は、「NPS」「満足点」「不満足点」としました。）
「NPS」テーブルの処理
はじめに、「NPS」テーブルの処理をおこないます。
「NPS」テーブルでは、IDとNPSの値だけがあればよいので、他の列は 右クリック > 削除 します。
また、分かりやすいように 右クリック > 名前の変更 で列名を変えておきます。
「満足点」テーブルの処理
次は、「満足点」テーブルの処理をおこないます。
不要な列の削除と名前の変更
「満足点」テーブルでは、IDと、IDごとに選択された満足点が一覧化できればよいので、他の列は 右クリック > 削除 します。
分かりやすいように、 右クリック > 名前の変更 で列名も変えておきましょう。
列の分割
次に、 「満足点」 列に含まれている複数回答を、集計のために分割する処理をします。
「満足点」列を選択肢、 右クリック > 列の分割 > 区切り記号による分割 を選択します。
設定のポップアップが立ち上がるので、下記のように設定します。
| 項目 | 設定値 |
|---|---|
| 区切り記号 | セミコロン |
| 分割 | 区切り記号の出現ごと |
| 分割の方法 | 行 |
すると、1つのセルの中で「;（セミコロン）」により区切られていた複数回答が、列方向に分割されます。
空白行のフィルタ
最後に、 列の分割により作成した行には、 空白 や null といった集計に不要な値が含まれているため、フィルタにより除外します。
「満足点」 列のフィルタ設定を開き、 空白 や null のチャックを外します。
こうして、 IDごとに複数回答した満足点が、列方向に展開された テーブルが作成できます。
「不満足点」テーブルの処理
「不満足点」 テーブルにおいても、「満足点」テーブルと同様の処理により、 「不満足点」 列を列方向に分割します。
クエリの読み込み
ここまでで処理したテーブルをExcelで利用するために、クエリの読み込みをおこないます。
Power Query エディタ左上の、 閉じて読み込む をクリックします。
すると、作成した3つのテーブルが、それぞれ新しいシートにテーブルとして追加されます。
リレーションシップの作成
このままでは単に3つのテーブルが作成されただけなので、 リレーションシップ を作成して、3つのテーブルを関連付けながら集計できるようにします。
データ > リレーションシップ を選択します。
すると、リレーションシップ作成のポップアップが立ち上がるので、 新規作成 をクリックします。
「満足点」テーブルの「ID」列 と 「NPS」テーブルの「ID」列 とで、リレーションシップを作成します。
「満足点」テーブルの「ID」列 と 「NPS」テーブルの「ID」列 とでも、同様にリレーションシップを作成します。
ピポットテーブル・グラフの作成
挿入 > ピポットグラフ > ピポットグラフとピポットテーブル を選択します。
ピポットテーブル作成のポップアップが立ち上がるので、分析するデータに このブックのデータモデルを使用する を選択します。
新しいシートにピポットテーブル・ピポットグラフが作成されるので、 軸（分類項目）に「NPS」 、 凡例（系列）に「満足点」 を設定します。また、 値に「ID」 を設定し、値フィールドの設定から「重複しない値の数」を設定します。
こうして、「NPS」と「満足点」とでクロス集計をすることができます。
ピポットテーブル・ピポットグラフで作成しているので、後は行・列の入れ替えやフィルタ、グラフ種別の変更などを自由に設定することが可能です。
おわりに
書き終わってみるとなかなかニッチな内容になってしまいましたが、ここまでお読みいただきありがとうございます！
Power Queryは少し取っ付き難さはありますが、学んでいくとこれまでVBA等でのコーディングが必要だったことがノーコードで出来るとても便利なツールだと思います。
何より、Excelさえインストールされていれば、他ツールのインストールがいらないという、非エンジニアの会社員にとってはとてもありがたいツールでもあります。笑
また、データベースやデータ分析の基礎を触りながら学べるため、これからデータ分析を始めたいという人にもおすすめのツールになっています。
MicrosoftのBIツールであるPower BIでも、Excelと同様のPower Queryによりデータ処理をおこなうため、ガッツリ分析をしたければPower BIへ移行しやすいというメリットもあると思います。
この記事が、みなさまのお役に立てばうれしいです！