2018/06/13

[Excel 取得と変換] カンマで区切られた複数回答結果を集計する

ネット上でアンケートを作成して、スマホやパソコンで答えてもらい、その結果をCSVなどで受け取るサービスってよくありますよね。身近な(でも、意外に使われていない)サービスとしては、Excel Onlineのアンケート機能(最近は Excel Survey という名称のようですが)、なんてものがあります。

ラジオボタンやドロップダウンリストで複数の選択肢から1個だけ選択させる、という設問は、結果が1つだけなので、1セルに1つの値が入りますが、複数回答可能な設問になると、1つのセルに複数の値が入ります。アンケートの結果として手に入れた表が以下のようになります。
セル内でカンマで区切られた複数回答の例
これも「集計・分析しづらい表」の代表例とも言えます。今回はこの複数回答結果のような、1セルにカンマなどの区切り文字で複数値が含まれているデータを簡単に Power Query (Excel であれば取得と変換)で集計する方法をご紹介します。ワークシート関数を駆使しなくても、VBAを使わなくても、Excelの新しい機能である「取得と変換」(Power Query)を知っていれば対応可能です。
上記の例の場合、ビール、ウィスキー、ワインが選ばれた数を知りたい、そこから、東京でビールを選んだ人はどのくらいいるのか、性別で見た時に、、、という分析をしたいわけですよね。こういう分析は、やはりピボットテーブルが使いやすいと思います。

セルの中の区切り文字でセルを分割する

Power Query - 取得と変換を使わなくても、[データ]タブの[データ ツール]グループにある「区切り位置」の機能を使えば、カンマ区切りの1セルのデータを複数セルに分割することができるのは確かです。

話は脱線しますが、これまでも同じ事ができてるんだから、あえて「取得と変換」 Power Query を使う必要はない、と考えてしまいがちですが、Power BI と Excel の製品動向から考えると、早めに同じ機能なのであれば「取得と変換」の使い方に慣れたほうが得策だと感じています。メリットやデメリットなどいろいろ〇×表で書けると思いますが、一番の違いは、元のデータが Excel のワークシート上に無くてもよい、という点、よって元のデータを直接加工しないで済む、という点だと思います。

実際の手順を追ってみましょう。

1) 元のデータをテーブルにする
モダンエクセルの基本は表形式のデータはテーブルにする、です。Ctrl+Tでテーブル形式に変換してしまいます。

2) テーブルからクエリ エディターを開く
変換したテーブルを元データとして、この元データを加工するためにクエリ エディターを開きます。アクティブセルをテーブル内において、[データ]タブの[取得と変換]グループにある[テーブルから]をクリックして、クエリ エディターを立ち上げます。

3) 列を指定して区切り文字による列分割を行う
サンプルデータであれば「好きな飲み物(複数回答可能)」の列を選択(ヘッダーをクリックでOK)し、区切り文字に「カンマ」を選択して列の分割を行います。手順は以下のアニメーションGIFを参考にしてください。
クエリ エディターの [変換] - [列の分割]機能を使って、カンマ区切り文字で列を分割する
集計・分析しやすいデータの持ち方に変換する

上記の列の分割までは「区切り位置」機能と大差ありませんが、取得と変換を使ってほしいのは、この後の「列ピボットの解除」の処理があるからです。
[追記] 列の分割機能の詳細設定オプションで「分割数」を「行」にすることで、以下の列ピボット解除を行わずに、一気に「好きな飲み物列」の各行にデータの展開が可能です。最後にその手順のアニメーションGIFを追加しました。ご指摘ありがとうございます!

データを区切ったまではいいのですが、まだこのデータは分析には向いていません。ビールやウィスキー、ワインといったデータは「好きな飲み物」の列にタテに入ることで、ピボットテーブルを使った分析が可能になります。

この横に並んでいるデータを縦にするのが「列のピボット解除」の機能です。すでにこの機能は以前の投稿で何度も紹介しています。クロス集計表・マトリクス表でデータが提供されてしまうことが多いExcel界隈では、本当にこの機能を知っている、この機能の使い方を熟知しているか、そうでないかで大きな違いがでると思います。

以下、その手順をおったアニメーションGIFです。
列ピボットの解除
ピボットテーブル レポート機能を使って集計する

ここまでデータの整形ができれば、あとはピボットテーブル レポートを使って集計が可能になります。

まずはビール、ウィスキー、ワインそれぞれがいくつ選ばれているかを集計します。もとのデータは1セルにカンマ区切りで入っていたデータを、列分割と列ピボット解除でテーブル形式にしたデータに整形し、ピボットテーブル レポートにします。

以下が、上記のアニメーションGIFの終わりの状態(クエリ エディターで列ピボット解除の状態)からピボットテーブル レポート作成までをアニメーションGIFにしたものです。
ピボットテーブル レポートの作成
あとは、ピボットテーブル レポートの機能を使ってクロス集計による分析が可能になります。地域毎や性別などで、どんな飲み物が選択されたか集計できます。アニメーションGIFの最後のほうではウィスキーを選んだデータのドリルダウンを行っています。ピボットテーブル レポートだからこそできる分析機能ですね。
ピボットテーブル レポートによる集計とドリルダウン
ここまで「一行も」関数を使った数式やVBAのコードを書いていません。

まとめると、データの取得と変換、Power Queryに代表される「ETL機能」を Excel で使うべき理由は、極端なことをいえば「ピボットテーブルで分析しやすいデータを作る」ことかもしれません。集計や分析でピボットテーブルを使いこなす人にとっては無くてはならないものです。

Power Query(取得と変換)とピボットテーブルはセットで覚えてしまうのをお勧めします。

皆さんの業務の参考になれば幸いです。
[追記] 列のピボット解除ではなく、列の分割の詳細オプションで行に展開した場合のアニメーションGIFが以下です。こちらのほうが簡単!
なお、このオプション名の「分割数」ですが、英語版は「Split into」で「分割先」です。このオプション、最初は分割先として列しかなく、最大数を指定していた記憶があり、途中で分割先として「行」が追加されたと思います。その名残りでしょう(要は更新し忘れているのでしょう)

列の分割の詳細オプションを使って、それぞれの行に展開する

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018