2020/05/01

[Excel データの取得と変換] Webページのテキストデータを取得する

Power Query / Power BI の[データの取得と変換]によりさまざまなデータソースから Excel や Power BI Desktop にデータを取り込むことができることは、このブログでもご紹介してきました。

特に「テーブル」を使った Web ページからのデータの取り込みは非常に簡単になったのも [データの取得と変換] を使う理由です。この素晴らしい機能が、いまだに Excel ユーザーの中で知名度が低いのは、本当に残念です。

[マイクロソフト] チュートリアル:Power BI Desktop を使用して Web ページのデータを分析する
https://docs.microsoft.com/ja-jp/power-bi/desktop-tutorial-importing-and-analyzing-data-from-a-web-page

今回は、HTMLのテーブルにもなっていないWebページのデータを取得する方法をご紹介します。俗に「ウェブ スクレイピング」と呼ばれるもので、そもそもデータの再利用などを考慮していないページから、最新のデータを取得する、昔はやった方法です。

Doorkeeperconnpassで勉強会開催情報を公開しているAWSのユーザーグループ「JAWS-UG」の両サービスの登録会員数の取得を例としてその方法をご紹介します。ちなみに両サイトはREST APIを公開しているので、勉強会開催情報の取得では本来スクレイピングする必要はないのですが、公開APIを調べる限りだと「登録メンバー数」を取得するAPIがありませんでした。しばらく「登録メンバー数」の取得は月1回なので目視&手作業で行っていましたが、外出自粛で時間ができたので「データ取得の自動化」をしようと思い立ちました。APIを使ったデータの取り込みは以下でご紹介しました。

[Excel] Excel で JSON データを読み込む
https://road2cloudoffice.blogspot.com/2017/06/excel-excel-json.html

Webスクレイピングのポイントは「テキスト ファイル」で扱う

操作の大まかな流れは以下になります。

  1. Webページのhtmlファイルを「テキスト ファイル」としてPower Queryに取り込む
  2. 該当する行を固有のキーワードでフィルターをかけて特定する
  3. 特定した行から、該当するデータを抽出する
  4. 抽出したテキスト データの属性を調整する(テキストから整数など)
それでは順を追ってみていきましょう。

1)テキストファイル形式で取り込む

Webページの構成上「テーブル」形式のものは取り込みやすいのですが、以下のような数値を取り込むのは、HTMLの構造上、テーブル内のデータとして認識されていません。


このようなWebページからデータを取り込むには、まずリボンの[データ]タブの[データの取得と変換]の[データの取得]にある、[その他のデータ ソースから]の[Webから]を使います。

(直接 [Webから] でも OK。ただしExcel2016以前の[Webクエリ]は別モノなので注意)

サンプルとして利用する、JAWS-UG全国のURL(https://jaws-ug.doorkeeper.jp/)を入れます。今回の場合は [基本] を選択したままで [OK] を押します。


今回 [Document] のテーブルビューは使えません。WebページをHTMLのテーブルビューではなく「テキスト」として扱います。そのため、URLのフォルダーアイコン上で右クリックメニューの [データの変換] を選びます。


Power Query エディター ウィンドウが開くので、[クエリの設定] の [適用したステップ] にある [ソース] の横にある歯車マーク(①)をクリックして、[形式を指定してファイルを開く] のドロップダウンリスト(②)を開き、[テキスト ファイル] (③)を選択します。


テキスト ファイル形式を指定することで、Power Query内にWebページのhtmlファイルは「テキスト」として読み込まれます。ここから該当する箇所を探します。

2)フィルターをかけて行を特定する

上からテキストを見て探してもよいですが、最終的には「フィルター」の機能を使って、該当するデータを含む行を絞り込みます。今回の場合は、メンバー数のデータがある部分を取り出したく、該当する記述は以下になります。

<a class="label label-full community-members-count" href="/members">9464人</a>
class名の"label label-full community-members-count"と href="/members" はユニークでフィルターのキーワードで使えそうです。以下のようにタグをコピーして行フィルターをかけます。

3)行からデータを抽出する

MID関数のような機能を使ってテキストの行からメンバー数に該当する数値を抜き出します。抜き出した結果は列を追加して新しく加えるので、[列の追加] タブにある [抽出] を使います。

4)データの属性を調整する

抽出されたデータは「テキスト」なので、これを整数に変換し、列名も変更します。
以下のように簡単に変更することが可能です。


上記では Doorkeeper のWebページからメンバー数を抽出しましたが、同じ手順で connpass からもメンバー数を抽出することが可能です。いくつかの支部のメンバー数を抽出した結果は以下のようになります。


一度シートを作成してしまえば、あとは [データ] タブの [クエリと接続] の [すべての更新] ボタンで、Webページを参照し、最新のデータに更新します。以下のアニメーションGIFでは、合計が 22,887人だったものが、東京支部が1名増えて、22,888人に更新されています。


なお、今回はスクレイピングの手法で、これからも再利用可能なWebページでしたが、Webページの作り方によっては再利用ができないものもあるので、すべてのケースに適用できるわけではないことをご留意ください。(毎回手作業が発生するものは自動化する意味がないからです。ここでいう「自動化」は [クエリと接続] と [すべて更新] ボタンを押すことで最新のデータになるものを指します)

次のステップ

この手の自動化ツールは汎用性を目指さなくてもいいので、該当する支部をすべて抜き出しシートにコピペしながら手作業で一度作成すれば、あとは [すべて更新] で瞬時に最新データに更新されるので、自動化・省力化の目的は達成です。

しかし、ちょっと冗長な感じがするので、時間があれば以下のようなことを検討できるかもしれません。

  • URLを変数としてもらい、メンバー数を返すユーザー関数にする
  • ユーザー関数を使って、計算対象支部の増減に柔軟に対応するシートにする
たぶん、できると思います。対象支部は50以上あるので、汎用化するか、手作業でやるかは微妙な数ですが、できれば汎用化して、またアウトプットしたいと思います。

もうひとつは「最終開催日」の取得です。これはREST APIの勉強会情報の開催日からも取れそうですし、ページを指定して上記の方法のようなスクレイピングでもとれそうです。

以下、ポエム

しばらくこのデータの取得と更新は月に1回、対象の支部が50程度なので、1時間くらいかけて行っていました。各支部の状況なども確認できるので、決して無駄な時間ではない、と考えていましたが、このデータのチェックを月1回から週1回に行う可能性が出てきました。

そうなると、週1回の1時間かかるデータ更新は外部の協力者に作業依頼する方法もありました。しかし、私たちはIT業界にいます。データを扱うITベンダーのマーケティング部門の人間であれば、ITを使って自動化・省力化を考えたほうが前向きだと思っています。ノンテク部門はお金で解決しがちですが、IT業界にいるのですから、やはりITで解決することにまずはチャレンジしたいです。

立場上、できれば、AWSのGlueやQuickSightを使ったほうがいいと思います。個人でデータを扱うのに適した Excel ではなく、クラウド上でデータ処理を行い、他の人たちと処理したデータを共有すべきタスクができたらチャレンジしたいと考えます。

以上、最後はポエムでまとめてしまいましたが、REST APIなどが提供されていない場合、昔ながらのスクレイピングによるデータ取得も、現在、Excel や Power BI Desktop の Power Query でコーディングレスで可能なことがおわかりいただけたかと思います。
何らかの参考になれば幸いです。

2019/09/30

[Excel BI]ピボットテーブルで予算と実績を管理する

先日、ある方がPower BIで予算額と実績明細の比較と分析を行うにはどうやればいいか難しい、という話題がありました。ワークシートとセルを扱って計算する Excel ユーザーが Power BIを使い始めるときによく直面する「Excel ならできるのに Power BIだとうまくいかないケース」の一つでもあります。

ひとつ、Power BI の前に慣れ親しんだ Excel の Pivot Table だけで予算と実績を比較するピボットテーブル レポートを作ってみましょう。ワークシート関数は使いません。ただし、ピボットテーブルで「メジャー」を使ってみます。
Excelでなんでそんなに面倒なことするの?と思うかもしれませんが、Power BIとExcelを双方使えるようになるための頭の体操みたいなものです。ある意味 Power BIの世界ではワークシートでセルを自由に編集(セルに数式を埋め込む)することができる「エクセル脳」は結構邪魔になります。とはいえ Excel は使うことが多いので、ちょっとだけデータ処理の考え方を柔軟にしてみてください。

最終的に以下のようなピボットテーブル レポートを作ることを目的とします。
最終的に作成する予算と実績のピボットテーブル
単純なサンプルから目的のピボットテーブル レポートを作ってみます。

予算と明細テーブル

おおよそ、ワークシートで予算と実績を管理する場合、割り当てられた予算額の表(テーブル)があると思います。
予算テーブルのサンプル
もっと細かくても構わないのですが、まずはシンプルなケースで考えましょう。上の予算テーブルからは、事業部Aの前期(2019/1/1)の予算は100、後期(2019/7/1)の予算は50、事業部Bは前期のみ50、事業部Cは前期に10、後期に20の予算が割り当てられています。

これが、縦に部門、横に月があるクロス集計表は分析に使えません。おおよそ人間向けの確認・印刷用にクロス集計表をワークシートに作っていることが多いのですが、クロス集計表はビジネス分析では「結果の表」であって、分析用のデータではないことを肝に銘じてください。(クロス集計表変換方法はこちらを参照してください)

実績明細テーブルは以下のようなものです。明細レコードが追加されていくようなものです。
実績明細テーブルのサンプル

複数のテーブルからピボットテーブル レポートを作成する

SUMIFSなどを使わない、セルに関数式を入力しないで、ピボットテーブル レポートのみで目標の予実対比表を作るには、Excel 2013以降に導入されたリレーションシップとデータモデルを使った複数テーブルからピボットテーブルを作成する機能を使います。ちなみに「範囲」は使いません。必ずテーブルに変換してください。(ここが?と思う方は、過去のブログ投稿などを参照ください)

まずピボットテーブルは「集計が簡単」というのがメリットです。実績明細テーブルからピボットテーブル レポートを作るとすぐに以下のような集計表が作れます。
ピボットテーブル機能で集計表を作成する
同様に予算テーブルも以下のような集計表を作成することができます。
ピボットテーブル レポートによる予算集計表
この2つのピボットテーブルから予算額と実績額を比較できる表の作成ができれば、目的となる表になります。残念ながらピボットテーブルを結合する機能は(私の知る範囲)ありません。予算テーブルと実績テーブルの2つのテーブルから、両方のデータを使ったピボットテーブル レポートを作ります。

まずは、予算合計に対して、実績合計の比較表を作成してみます。複数のテーブルからピボットテーブルを作成する方法は過去に手順をおったブログを投稿していますので、こちらも参照ください。
以下は、総額を比較する手順をアニメーションGIFでとったものです。
予算と実績の合計を比較したピポットテーブルを作る
上のアニメーションGIFの最後のフレームが短いのですが、最終的には以下のピボットテーブル レポートを作成しました。予算の合計は230、それに対して実績の合計は218という表です。
予算・実績の合計の比較表
ここからがちょっとエクセル脳から「考え方」を変える必要があります。
上の表は予算額の合計と、実績額の合計を並べている表ですが、次にこれを「部門別」に分ける、と考えます。事業部Aの予算と実績、事業部Bの予算と実績、事業部Cの予算と実績にわけます。ピボットテーブルの経験のある人は、行ラベルに「事業部」をもってくると出来る!と思うでしょう。このピボットはデータモデルに追加しているので、予算と実績の両方のテーブルが使えます。そのテーブルから「部門」を行に追加すればできそうですが、思った通りの結果にはなりません。
部門を行に追加するも思った結果にならない
実績テーブルの[部門]を選ぶと、実績額は部門別になりますが、予算は全合計のままです。言い換えると実績テーブルの部門を選んで、部門別のフィルターをかけて、実績金額を分けることができたのですが、予算額はフィルターがかかっていない全合計のまま、と言えます。その逆もしかり、です。
実績は部門でフィルタがかかるが、予算はフィルタがかからない
予算を部門でフィルタすると実績は全合計のまま

マスターテーブル/DIMENSIONテーブルの追加

この予算テーブルも明細テーブルも、どちらもFactテーブルです。予算をとった部門や実績をあげている部門は事業部A,B,Cですが、もしかすると事業部Dがあるかもしれません。実績明細で予算をもっている部門がまだ実績がなく明細データがないケースは容易に考えられます。注意しなければならないのは、予算テーブルと実績テーブルの部門をリレーションシップで関連付けるのはまったく意味のないことです。
そこで部門のDimensionテーブル(マスターテーブル)を追加・作成して、各FACTテーブルとリレーションシップを貼り、Dimensionテーブルの項目でフィルターをかけてみます。
追加したシンプルな部門マスター
この部門マスターの部門名と、予算、実績テーブルの部門名でリレーションシップを作成します。
部門マスターと予算・実績テーブルとの間でリレーションシップを作成する
このリレーションシップを作成することで、予算テーブルおよび実績テーブルの集計を、部門マスターの[部門名]でフィルターをかけることが可能になります。
部門マスターの部門名で合計金額をフィルタリングする
次にやりたいのは、月別にする、四半期別に集計する、か、予算と実績から達成率や消化率といった計算結果を出す、のどちらかでしょう。今回は実績/予算で消化・達成率を出す方法をみてみます。ちなみにこれがメジャーをExcelで使うことになります。

ピボットテーブルでメジャーを追加する

Excelのピボットテーブルに詳しい人であれば「ん?メジャー?そこは集計フィールドじゃない?」と思うでしょう。ただ、複数テーブルを処理するためにデータモデルを使うと集計フィールドが使えなくなるのです。(リボンでグレイアウトされます)そのかわりにメジャーを使うことになります。なお、セルに関数式を使って消化・達成率の計算はできますが、その誘惑に負けないようがんばりましょう。(これがセクセル脳の弊害のひとつです)

すでに以下のように部門別の予算・実績テーブルができています。ここに実績合計を予算合計で割った、消化・実績の割合を計算するフィールドを追加します。
ピボットテーブルでメジャーを追加する
メジャーの数式入力欄では "[" を入力すると、計算に利用できるフィールドのリストが表示されます。表示形式もセル範囲指定の書式変更でなく[値フィールドの設定]から書式設定を行ってください。メジャーによる消化・達成率を使って、以下のピボットテーブル レポートが作成できました。
消化・達成率のメジャーを追加した予実管理表
日にち・時間はExcelやPower BIでは重要でかつ難しいエリアです。特にExcelは内部で暗黙的にカレンダーをもっているので、通常ExcelユーザーはカレンダーをDimensionテーブルとして意識していません。
先の部門別のフィルターが予算合計と実績合計に適用するときのように、日付についてもFactテーブル(予算と実績のテーブル)にある日付を使うとうまくいきません。Dimensionテーブルとしてのカレンダーテーブルを作成し、そこからFactテーブルの予算、実績テーブルの日付と関連づけて、カレンダーテーブルのフィールドを使ってフィルターします。
最終的には以下のようなピボットテーブル レポートの作成が可能です。
四半期別にした予実管理ピボットテーブル レポート
ピボットテーブル レポートなので、行ラベルの入れ替えも瞬時に可能です。
行ラベルの順序の入れ替えも容易
#NUMが気に入らない、、、とはいえ分母(予算)が0なので、計算式的にはエラーですので、このあたりは「決め」の問題で対応を考えるしかありませんね。

いかがだったでしょうか。
唯一、計算式を記入したのはメジャーの数式の部分でした。Excelのピボットテーブル機能は、オリジナルのExcelと、将来メインになるだろうPower BIとちょうど中間にあるような感じだと思います。いきなり Power BI 脳になるのも難しく、かといって Excel 脳だとなかなか Power BI の世界が異質に見えるのも確かです。

これらの機能を使ったほうがいいのは、やはりデータが動いている状況でのレポートが欲しい時です。ピボットテーブル レポートなので、実績明細が常に追加される状況では「更新」を押すだけで最新データを使ったレポートに更新されるからです。日々レポートを更新しなければならない人にとっては、これほど素晴らしい機能はありません。
さらにセルに直接数式を入力していないため、実績明細のレコードが増えて予実比較表の行数が増えても、数式を入れ直すことなく表の更新が可能になります。

ここから他の人とレポートを共有しなければならない場合は、Excelではなく Power BI を使うことを検討したほうがいいでしょう。Enterprise BI は常に他の人とのデータおよびレポートの共有が必須ですから、エンタープライズでの BI は Power BI に向かうのは必然だと考えます。

これが少しでもみなさんの参考になれば幸いです。

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」で「分割先」です。このオプション、最初は分割先として列しかなく、最大数を指定していた記憶があり、途中で分割先として「行」が追加されたと思います。その名残りでしょう(要は更新し忘れているのでしょう)

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

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

2017/07/08

[Power BI / Excel] 複数にまたがる Web ページからデータを取得する

Excel や Power BI Desktop のデータ取得の機能である Power Query (データの取得と変換) の話題です。

地震データを取得する

最近、また地震が多いのが気になりますよね。地震データは気象庁のホームページで随時公開されています。しかしながら、この公開データは、良い意味でデータ クレンジングのサンプルになり、違う意味では「集計」や「レポート作成」などの後工程をあまり考慮していないデータかもしれない、と愕然とします。
今回のお題とは離れた話題なのですが、ちょっとだけご紹介します。

http://www.jma.go.jp/jp/quake/quake_local_index.html

すべての数字が「全角」です。大きい、小さいの判断をするためには数値にしないと比較できないので、まずはこれを半角に変換する必要があります。同じく日付に使われている数字も全角です。良いか悪か別にして、Excel は全角数字をセルに入力すると、自動的に全角数字を半角の数字に変換してくれます。また、関数も用意されていて、ASC関数が全角のアルファベット、数字を半角に変えてくれます。(反対は JSC関数)

残念ながら Power Query エディターには全角→半角にしてくれる関数はありません。自分でカスタム関数を作って対応します。やることはものすごくベタで、全角のAは半角の A に、全角の1は半角の 1 に、というようにすべてを羅列するようなものです。

一方、Yahoo さんの地震情報は、数字は半角になっています。元ネタは気象庁のデータを使っていると思うので、後工程・再利用を考えて変換されているのでしょう。

Yahoo! JAPAN 天気・災害 地震情報
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/


全角・半角変換の手間を考えると Yahooさんのページからデータを取得したいところですが、地震の回数が多くなる、ちょっと長い期間でみてみたい、となると最初の1ページのデータだけでは足りなくなります。

Yahooさんのこの情報ぺージは、1ページから300ページまであり、かなり前の情報も Web ページから取得することが可能です。


300ページ目を開くと、今だと 2004年の2月後半からデータを取得することができます。1ページあたりに100件のデータをテーブル内で表示してくれます。
URL は以下です。

https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101

?から後ろがページを指定するパラメーターですが、一番最後のパラメーターの b=101 が、このページで表示する「開始番号」の扱いです。なので

1ページ目 b=1
2ページ目 b=101
3ページ目 b=201
4ページ目 b=301
・・・・・
10ページ目 b=901

といったルールになっていることがわかります。

複数の Web ページからデータを取得する

Power Query で最新100件(=1ページ目)を超える過去のデータを取得したい場合は、2ページ目、3ページ目、、、と複数のページ上のデータを取得する必要があります。

毎回、たとえば3ページ分のデータを取得して分析しよう、とした場合、すぐに思いつくのは URL 固定で 3 個のクエリを作成し、そのクエリを Append (追加) する方法でしょうか。


https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=201

この3つのWebページのクエリ結果のテーブルを追加(Append)する方法です。

これでもいいんですが、URL固定、数固定にできない場合などを考慮すると、違う方法も覚えておくと便利です。それが「カスタム関数」を使う方法です。上記の URL であれば、b=1 や b=101 の部分の数値を変数、引数として扱い、結果をもらう、という方法です。

Excel / Power BI Desktop で「繰り返しの処理」のような方法でデータをとりたいな、と感じたときは、上述のように変数と関数を使うことを検討してみます。

データの読み込み処理を関数にする

Excel でも Power BI Desktop でも方法と手順は同じです。Excel の画面で手順を追ってみましょう。Excel 2016 のデータ タブにある「取得と変換」または「データの取得と変換」を使います。繰り返しになりますが、取得と変換はこれまでの外部データの取り込みに代わる新しい機能で Power Query と呼ばれていたものであり、Power BI Desktop のデータの取り込み・変換の機能(ETL機能)と同じものです。製品の更新タイミングによって若干の表現の差がありますが、ほぼ同じ位置や言い回しなので、脳内補完してください。あえて画面は少し前の Excel のスナップショットです。

データ タブの「(データの)取得と変換」と「新しいクエリ」や「データの取得」などのメニューから「Webから」を選びます。


データを取得する Web ページの URL を入れるダイアログが表示されます。
Yahoo!さんの地震情報ページの 1 行目からを表示する URL を入れて [OK] を押します。

https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1


今回は、Yahoo! さんの一般向けのページなので [匿名] のまま [接続] を押します。


指定したページにあるデータ情報からどのデータを取得するか指定する「ナビゲーターウィンドウ」が表示されます。テーブル形式でデータがまとまっている [Table 0] を選んで
[編集] を押します。[読み込み] を押すと、このまま取り込んでしまうので、いったん [編集] を押し、クエリ エディターにデータを読み込んで、変換・編集作業をします。


クエリ エディターが立ち上がります。取り込んだデータをもとにして、データの型が最初から列の属性として設定されています。気象庁のデータと違って、元データの数字が半角になっていること、マグニチュードの”M”が無いこと、などでマグニチュードは数値データとして、情報発表時刻は日付データとして認識されています。発生時刻が「ABC」の文字になっているのは 2017年7月8日 15時42分 ごろ という「ごろ」がデータに含まれているからです。最大震度が同じく「ABC」の文字になっているのは、半角数字の後に「弱」や「強」の文字がついているものがあったからです。


今回は発生時刻の「ごろ」や最大震度のお「強・弱」を扱う「データ クレンジング」処理は割愛しますが、このクエリ エディターでクレンジング処理をすると、クエリ エディターで「ステップ」として記録され、再利用することができます。

なお、マグニチュードでも文字列が入るデータがあるため、クレンジングの作業は必要です。

このクエリ エディタは基本 GUI で操作しますが、それらの操作手順は VBA のマクロ記録のように Power Query の M 言語で記録されます。M 言語で記述されたソースを編集するために詳細エディターを立ち上げます。[ホーム] タブの [クエリ] グループの [詳細エディター] をクリックしてください。詳細エディターを立ち上げるコマンドは他にも多く配置されています。


詳細エディターが立ち上がりました。詳細といっても、機能的には「簡易」です。
何度かお伝えしていますが、現段階で M 言語を深く習得し、詳細エディターでガリガリと操作を記述するような使い方をマイクロソフトは想定していないと思います。このエディターには本当に最低限の機能しかありません。そういうものだ、と思ってあまり深堀りしないほうがいいと感じています。

この簡易エディターで「引数」の指定をします。let の上に (page as number) => を挿入します。page という変数で、数値(number)型を引数として指定します、という意味です。


これで、page という変数(数値)が let 以下で使えるようになりました。これで 1 から始まる、101 から始まる、201 から始まる、という指定が可能になります。
URL の一番最後の値として置き換えたいのですが、注意点は page は「数値」であり、URL は「文字」であること。よって、数値を文字に変換してあげます。

Number.ToText(page)

この関数で変数 page の中の数値データが、文字のデータに代わります。URL の最後の b=1 の部分を以下のように書き換えます。

[変更前] /?sort=1&key=1&b=1")),
[変更後] /?sort=1&key=1&b="& Number.ToText(page))),


ウィンドウの右下の [完了] を押すと、エラーがなければ、パラメーターの入力を促す画面が表示されます。


画面左のナビゲーションの [クエリ] の上にある [>] 展開マークを押すと、このクエリ エディターで扱えるクエリが表示されます。(Excel の場合は、ブックに含まれるクエリであり、Power BI Desktop の場合は pbix ファイルに含まれるクエリを含みます)


引数(パラメーター)を準備する

ナビゲーションの Table 0 の前に [fx] アイコンからわかるように、この引数を設定する操作によって、Tablel 0 クエリは関数に代わりました。page パラメータに 1 を入れて [呼び出し] を押すと、1 からのデータの表を関数の「クエリの結果として」表示します。101 をいれると、101 からのデータの結果を表示してくれます。このクエリの結果はナビゲーションから削除することができます。削除するまでの操作手順のアニメーション GIF が以下です。


パラメータに、1、101、と入力し関数 Table 0 に渡し、その結果を表にすることができました。この 1、101、、、を手入力ではなくしたいのです。これが Power Query の繰り返しの処理です。

たとえば、常に10ページ分がほしいのであれば、1、101、201、、、901 までパラメータとして渡します。そのため、そのパラメータをリストとして用意します。

手順は、10個の連続した数値を用意して、1から901を作ります。いろいろなやり方があると思いますが、0 から 9 までの連続した数値から以下のように作ってみます。

0 -> 0*100+1 = 1
1 -> 1*100+1 = 101
2 -> 2*100+1 = 201
・・・
8 -> 8*100+1 = 801
9 -> 9*100+1 = 901

クエリ エディターで 0 から 9 までの連続した数値を作る方法は、数式で ={0..9} を使います。この数式の結果は {0,1,2,3,4,5,6,7,8,9} の連続した数値のリストを作成します。

クエリ エディターで、このリストをテーブルに変換して、新しい列を追加して、0*100+1 の数式を使って 1 から 901 までの数字を作ります。そこまでは以下のアニメーションGIFです。


パラメーターを使って関数を利用する

この操作で関数へ渡すパラメーターが用意できたので、このパラメーター列の横に新しく列を追加し、そこに作成した関数 Table 0 を指定して、1列目にある数値をパラメーターとして指定します。
関数 Table 0 はパラメーターで指定されたデータから始まる「表」をクエリの結果として返してくれます。個々の [Table] のリンクをクリックすると、1ページ100件分のデータのみですが、列名の横にある [展開] ボタンを押すと、すべての結果の表(テーブル)を結合して一気にデータ 10ページ 1000件を作成します。


0~9のパラメーターの最大値の 9 をさらにパラメーター化したり、多めの引数を用意して、取り込んだ後でエラー行の削除を行う、クレンジングをする、という方法を組み合わせることができます。

まとめ

今回は複数の Web ページのデータをまとめる、というお題でしたが、ポイントは「関数化」と「パラメーターの準備」による「繰り返し処理」と「クエリ結果の追加統合」の処理だと言えます。この方法を知っていると、Web ページだけではなく、他のところでも応用が利くテクニックです。

以上、少しでも参考になれば幸いです。

Powered by Blogger.

自己紹介

2014年頃よりPowerBI勉強会主催メンバーの一人として参画、MSMVP for PowerBI 2017受賞。 https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 2017-でAmazon Web Services Japanに入社し、Awardを返上、現在に至る。