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

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

ラジオボタンやドロップダウンリストで複数の選択肢から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」で「分割先」です。このオプション、最初は分割先として列しかなく、最大数を指定していた記憶があり、途中で分割先として「行」が追加されたと思います。その名残りでしょう（要は更新し忘れているのでしょう）

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

[追記 2022/2] カンマで区切られた複数回答を複数行にして行を増やしたくない、以下のような結果がほしい、という参照記事を見かけました。上のやり方にもうちょっとだけ手を加えると自分の好きな集計結果にできるので追記します。

上記のサンプルを例にすると、以下のような表がほしいようです。

ビール　　　3

ウィスキー　4

ワイン　　　4

またはそこからビールを選んだ人だけの表

ビール　Aさん

ビール　Bさん

ビール　Cさん

これは「集計結果の表」で、考え方としては本ブログでそれそれの行に展開したデータ（テーブル）をデーターソースとして、関数やフィルター機能などを使って集計するのですが、ピボットテーブルさえ覚えてしまえば、このような集計はあっという間にできます。

PowerQuery（取得と変換）でデータソースを整理して、集計はピボットテーブルで処理するのがモダン Excel の呼吸 壱の型みたいな鉄板だと思います。

以下ではデータソースとなるテーブルから「ピボットテーブル」を追加し、飲み物でまとめたレポートを作成します。そしてビールの行の数字をダブルクリックして、ビールを選んだ人の表を作成しています。

Power Query を使ってデータベースにある大量のデータを処理してレポート作成をする業務になればなるほど、Excel のセル関数を使う場面は少なくなり、データソースであるテーブルにフィルターをかけて「生データを眺める」こともデータ量が多いので現実やらなくなります。逆に SUMIFS 関数など知らなくてもいいので、ピボットテーブルさえできれば、集計は可能ですし、教育コスト（習得まで時間がかかる）が高いVBAを学ばなくても、必要な表をピボットテーブル上でダブルクリック（ドリルダウンと呼びます）で一瞬で作成できます。

暴論ですが、(元データが比較的整い、綺麗なデータを扱うことができる)ビジネスパーソンは関数やVBAをやらなくてもいいので、Power Query とピボットテーブルの機能をマスターすると、かなりデータ抽出、集計およびレポートの時短が可能になり、間違いも少なくなると思っています。