2018/06/13

[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 とピボットテーブルの機能をマスターすると、かなりデータ抽出、集計およびレポートの時短が可能になり、間違いも少なくなると思っています。

2018/03/04

[Excel 取得と変換] クロス集計表やピボットレポートをシンプルな表(テーブル)に変換する

何度かこのブログで紹介していますが、Excelの新機能(と、もう言えない?)の「取得と変換」は、これまでの「外部データの取り込み」を置き換えるだけではありません。

先日、ある方から相談を受けたのが、クロス集計表と呼ばれる表をデータ分析のためにクロス集計ではないシンプルな表形式データ(テーブル)に変換するものでした。その方は手作業でその変換をしていたのですが、テーブルのサイズが大きくなったり、繰り返しの作業になると手では無理、なんとか自動化できないか?というものでした。

これ、今は取得と変換のクエリエディターの「列ピボットの解除」を使えば、VBAを使わなくても変換ができます。

取得と変換のこの「クロス集計を解除する」言い換えれば「列ピボットを解除する」機能は、まだまだ認知が低いようです。Excelのテーブル機能と合わせて、取得と変換のクエリエディターは、業務でデータ分析をする人(もっと言えば、Excelを使ってレポートを作る人)にとって知っていて損はしない機能だと思います。また、PowerBIを使いこなすためのベースとなる知識のひとつです。

クロス集計表とは質問やデータのカテゴリを縦・横に「クロスさせて」数値を集計した表です。多くの人が意識しないでレポートの表を作ると、このクロス集計表になっていることが多く、Excel初級講座などでも、まずこのクロス集計表を作らせる演習が多いのも事実です。以下の表は、支店と製品のカテゴリを、月別の数値とクロスさせた表です。
クロス集計表の代表例 支店カテゴリ(縦)と月別の数値(横)をクロスさせた表
データ分析という観点からはツッコミどころが満載の表で、これを分析元のデータの表として作ってはいけないのですが、反面、数値を理解しやすい表なのです。この表はレポートとしての「最終形」と言えます。繰り返しになりますが、この表から別の分析をするといった作業には向かないのです。

分析のためのシンプルな表とは、縦・横のカテゴリでクロスしていない表で、以下のような表です。
シンプルな集計前の表形式のデータ
前置きはここまでにして、クロス集計表を「取得と変換」の「クエリエディター」の「列ピボットの解除」などの機能を使って、シンプルな表形式データに変換する手順をご紹介します。

以下は一連の作業を記録したアニメーションGIFです。
クロス集計表を取得と変換クエリエディターを使ってピボット解除する
以下、アニメーションGIF内の手順です。

1. クロス集計表をテーブルに変換する
リボン[挿入]のテーブルから変換できます。ショートカット Ctrl+T も使えます。
変換したいクロス集計表のセルを選択するだけで自動的に範囲が選択されます。
なお、集計行・集計列はいりません。アニメーションGIFでは、自動選択で集計行ははずれましたが、集計列を含んでテーブル変換したので、クエリエディターで集計列の削除をしています。

2. テーブルを指定してクエリエディターを立ち上げる
上記で作成したテーブルのいずれかのセルが選択されている状態(=アクティブセルをテーブル内のセルにする)にして、[データ]タブの「取得と変換」の [テーブルから] をクリックして、クエリエディターを立ち上げます。

クエリエディターはExcelとは別のウィンドウです。クエリエディターで指定した編集・操作の結果をExcelのワークシートに反映させることができます。元のデータを書き換えたりしないので安心してください。

3. 不要な列を削除する
集計列はいらないので「小計」の列を選択して、右クリックメニューの[削除]を使って列の削除をします。
このあたりの操作感はほぼExcelと一緒です。また、慣れてきたら要らない列を削除する方法から、必要な列を指定して残す方法も試してみて下さい。実際の業務では「必要な列だけを指定して残す」ほうが使い勝手が良いです。

4. 列ピボットを解除する
クロス集計表で慣れていると「何がダメなのかわからない」「どの列が列ピボットなのかわからない」と感じる人がいるようです。
仕方ありません。最初にExcelを習うときのサンプルの表はクロス集計表になりがちで、それは「罫線」のひき方、小計セルで使うSUM関数、さらには同じ値が連続したセルの結合方法(最悪・・・)を教えるには最適な表だからです。

Accessなどのデータベース製品を勉強した人にとっては、テーブルや表のことを言っているので理解しやすいと思いますが、Excelは上記のような教え方をされるため、ここの理解が第一関門かもしれません。このあたりは別途「テーブル」や「フィールド」、「レコード」といったキーワードで勉強してみてください。テーブルにおいては、1月、2月、3月、、、は月が「横に並ぶ」、東京、大阪、名古屋など地域や都市が「横に並ぶ」、製品A、製品B、製品Cなど同じ性質で種類の違うものが「横に並ぶ」ことはありません。それらは「月」や「地域」や「製品」の列にして扱います。

そうすると同じような行が増えるから「見づらい」という人もいますが、その感覚正しいです。そこから見やすく集計するから大丈夫です。また、何度も繰り返し同じ値のセルが続くからといって「結合セル」は使わないでください。(ワークシートでテーブルに変換済みだと結合セルは使えなくなりますが。)

サンプルのアニメーションGIFでは、「支店」と「製品」の列は列ピボットではなく、「1月」「2月」「3月」の列だけが列ピボットです。これらをまとめて「月」の列にしたいので、「1月」から「3月」の列を選択して、「列ピボットの解除」を行っています。解除した列は「属性」になっていますが、後で「月」などの名前(フィールド名)に変更可能です。

5. 空欄に値を埋める
列ピボット解除によって、元の行の直後に新しい行が追加されます。その時の空欄のセルに何を入れるかを指定します。アニメーションGIFの例では「支店」の列(フィールド)に空欄ができてしまいました。このような場合、「下方向にフィルする」を使って空欄に必要なデータを入れることができます。

6. ワークシートに結果を戻す
ここまでの操作は「クエリエディター」ウィンドウでの操作です。この結果をワークシートに戻すために、クエリエディターウィンドウの[ファイル]タブの[閉じて読み込む]や[閉じて次に読み込む]を使って、ワークシートに結果を戻します。

これでクロス集計表を表形式のテーブルに変換することができます。

このクエリエディターで行った操作は、ブックに保存されます。ブックに保存されているクエリは後から編集することも可能で、なおかつデータの「更新」によって最新の状態にすることができます。取得と変換の最大の強みは、元のデータを柔軟に指定できること(Excelは元より、他のcsvファイル、SharePointやFacebook、他のデータベースなどなど)、元データを変更せずにクエリエディター内で編集・加工すること、その手順をクエリとしてブック内に保存できること、そしてデータの更新を使ってクエリの結果を最新にできること、です。

この機能は覚えていて損はしません。この取得と変換のピボット解除の機能を知らないと、強引に手作業と機能で処理するか、この機能を知らないExcel上級者からVBAでやるしかないね、といったアドバイスを受けることになります。モダンエクセルで本当に知ってほしい機能のひとつです。(しつこいですが、あとはExcelのテーブル機能です。)
https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html

Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。