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

[注意] 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ページのデータを取得する方法をご紹介します。俗に「ウェブ スクレイピング」と呼ばれるもので、そもそもデータの再利用などを考慮していないページから、最新のデータを取得する、昔はやった方法です。

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

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

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

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

1. Webページのhtmlファイルを「テキスト ファイル」としてPower Queryに取り込む
2. 該当する行を固有のキーワードでフィルターをかけて特定する
3. 特定した行から、該当するデータを抽出する
4. 抽出したテキスト データの属性を調整する（テキストから整数など）

それでは順を追ってみていきましょう。

１）テキストファイル形式で取り込む

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人だったものが、東京支部が１名増えて、22,888人に更新されています。

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

次のステップ

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

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

• URLを変数としてもらい、メンバー数を返すユーザー関数にする
• ユーザー関数を使って、計算対象支部の増減に柔軟に対応するシートにする

たぶん、できると思います。対象支部は５０以上あるので、汎用化するか、手作業でやるかは微妙な数ですが、できれば汎用化して、またアウトプットしたいと思います。

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

以下、ポエム

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

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

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

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

Read More

[Excel] Excel で JSON データを読み込む

この前の投稿でご紹介したように、Power Query が「データの取得と変換」となって Excel の標準機能となり、様々なデータの取り扱いが可能になりました。(2017年6月現在、Office 365 サブスクリプションの 最新の Excel が機能拡張の対象となります）

データの取得と変換である Power Query は、アドイン単体としての機能追加、さらに Power BI Desktop の登場によって、Power BI Desktop の ETL 機能 (Extract, Transform, Load)として拡張が行われてきました。Power Query は、Excel そして Power BI Desktop のデータの取り込み、変換・加工、ロードを受けもつ ETL 機能として今も進化し続けています。

JSON 形式のデータをプログラミングなしで取り込む

この進化し続ける「データの取得と変換」機能で、すぐにでも使ってほしいのが CSV データの取り込み機能ですが、人によっては JSON 形式データの取り込みのほうを重宝するかもしれません。

というのも、JSON 形式のデータ取り込みは、以前からも Power Query を使ってできていたのですが、現在は空のクエリから詳細エディターを開いて Power Query 関数を手で記述することなく、クエリ エディターのクリック操作のみで取り込みが可能になったからです。

また、JSON形式のデータを表形式に変換してワークシート上に読み込むためには、VBAを使う方法がこれまで多く紹介されていましたが、VBAでプログラムすることなく、JSON形式のデータをワークシートに展開することができるようになりました。

Web から JSON でデータを取り込む

実際のところ、JSON形式のデータによるテキスト ファイルをフォルダーから読み込むよりも、Web 上での検索条件設定の結果で、JSON形式のデータが表示されることが多いと思います。

サンプルとして、IT勉強会の告知・募集でお世話になることが多い connpass さんの API を使ってみたいと思います。

https://connpass.com/

connpass さんは、イベントサーチ API を提供していて、検索クエリの条件に応じた一覧を JSON 形式のデータとして取得することができます。

connpass API リファレンス

Web API や REST API でデータ提供サービスをしています、という場合、上記のような API リファレンスのページが必ずありますので、探してみてください。

たとえば "BI" というキーワードでイベントを検索するための URL は以下のようになります。

https://connpass.com/api/v1/event/?keyword=bi

この URL で、以下のような JSON 形式のデータによるレスポンスがブラウザに表示されます。

このデータを、Excel のワークシートに展開できるように2次元の表形式に「変換」して、私たちが読めるようにデータを加工することが、データの取得と変換のクエリ エディターだけでできます。上述のように VBA などでプログラミングをすること無しに可能です。

シンプルなデータの取り込み手順

JSON データの取り込みには2つの方法があります。ファイルから取り込む方法と、Web から取り込む方法です。ファイルから取り込む方法を使っても URL を指定することで Web からの取り込みが可能ですが、今回は「Web から」を使ってみます。

「Web から」のデータ取得は、これまでの Web クエリよりも高機能になっています。HTML ページの table だけではなく、今回のように JSON にも対応しているのです。

connpass の Web API 利用はサインインする必要がありません。以下のダイアログで "PowerBI" キーワードを含むイベントを検索する URL を入力し、OKをクリックします。(上記 JSON サンプルのキーワード "bi" の検索結果の数が多かったので、キーワードを PowerBI に変更しています。注意してください。）

接続が完了するとクエリ エディター ウィンドウが立ち上がり、以下の画面が表示されます。

注意点は、ここでファイルのアイコンの [connpass.com] をダブルクリックしてはいけません。アイコン上でマウスオーバーすると「開くにはダブルクリックします」というツールチップが表示されますが、ダブルクリックすると、現時点ではテキストファイルとして処理されてしまいます。リボンの [変換] タブの [形式を指定して開く] から [JSON] を選んでください。

Json 形式で開くと以下のデータが表示されます。

results_returned、events、results_start、results_available の意味は、上述の API リファレンスに解説がありますので、詳細については後で参照してほしいのですが、情報から「指定した（PowerBI）キーワードの検索結果の総数は 19 件で、このファイル（データ）に含まれるのは 10 件、検索開始位置は 1 件目からですよ」という意味です。

あらかじめだいたいの件数がわかっている、もしくは取得する件数に上限を付けることができるのであれば、取得件数を 20 件に指定して全件を一気に取得することができます。PowerBI のサンプルは件数が少ないので、以下の検索条件にしてみます。

条件を変更（URLの変更）のため、クエリの設定の [適用したステップ] の [ソース] の横にある歯車マークをクリックします。

ダイアログの URL を変更します。取り込む件数の上限を 20 とするパラメーターの &count=20 を追記した URL です。

[OK] を押すと、results_returned が 19 に更新されます。

今回は例をシンプルにするために、検索結果で全件を取り込むパラメータを追加しました。また、匿名アクセスで利用が可能なので、認証に関する設定はありません。これらへの設定・対応はもちろん可能です。あらためて別の機会にご紹介したいと思います。

今、この状態は、検索したいキーワードを設定した URL をサーバーに送り、その結果を JSON 形式のデータとして Web 経由で 19 件取得しています。
この表示されているリストの events の List のリンクに、JSON 形式で 19件の検索結果の格納されています。
List のリンクをクリックすると、以下のように List リンク内のリストが 19件のデータとして展開されます。

Record のリンクをクリックすると、クリックした1件分だけの内容を確認することができますが、今回はすべての Record（勉強会）の詳細を一気に展開したいので、ここでリボンにある [テーブルへの変換] をクリックして、19件のレコードを含むリストを、テーブル（表）形式に変換します。すでに1件1レコードとして認識されているので、テーブルへの変換のダイアログのオプションはそのままで、[OK] ボタンをクリックします。

リストだったデータがテーブル形式になりました。

ここで Record リンクをクリックすると1件分のみの展開をしますが、テーブル形式に変換したことによる「列名」の Column1 の横にある [展開] ボタンを押すと、Record に含まれるデータをテーブル形式1件分のデータのみなした場合の列名の一覧が表示されます。ここで取り出す列を絞り込むこともできます。今回はすべての列を選択し、かつ、列名が長くなるので、[元の列名をプレフィックスとして使用します] のチェックをはずして、[OK] ボタンをクリックします。

JSON形式の元データを、21列19行のテーブル形式のデータに変換することができました。クエリ エディターの操作のみで1行もコードを書かずにここまでできました。

テーブル形式になったデータを Excel 向けに加工する

今回は Excel の「データの取得と変換」を使って connpass から検索結果を JSON 形式のデータで取得しました。その後、ここまでの処理・操作で、データはテーブル形式になりました。それぞれの列名がどのような意味なのか、connpass の API リファレンスのレスポンス フィールドで確認することが可能です。

ここで、必要な列や行のみを残す、といった加工が可能です。ここからの処理は、JSON だから特別、というものはなく、普通のテーブル形式のデータのフィルター オプションを操作する感覚でできるのは、クエリ エディターを使ったことがあれば理解できるでしょう。まだ慣れていない方は Power Query によるデータの加工や絞り込みについて、もう少しだけ情報収集するといいでしょう。

この connpass のデータや、特にサーバーからデータを取得した際に、Excel ユーザーが一瞬「おや？」と思うのは、日付データの扱いです。この日付データのトピックだけで結構長いお話になってしまうので、ここで詳細は割愛しますが、1つだけ意識してほしいのは、サーバーの日付形式のデータは、そのまま Excel で扱うことができない場合がある、ということです。

上記はデータ変換をせずにテーブル形式に変換したJSONデータをワークシートに読み込み、列名 started_at の1行目のセルの書式を表示したものです。2017-05-20T13:00:00+09:00 は勉強会・イベントの開始日のデータですが、Excel は単なる文字列として認識し、日付として扱っていません。
多くの場合、Excel は日付「らしい」文字列のセルへの入力があると、日付データの「シリアル値」に変換し、表示形式によって人間が日付と認識できるデータに見た目上変換します。残念ながら、2017-05-20T13:00:00+09:00 という ISO-8061 形式の文字列は Excel によって日付データと認識されなかった、となります。このままではシリアル値として扱っていないため、日付関連の関数の利用や演算ができません。

そのため、クエリ エディターで Excel が日付として認識できるように加工します。
データをワークシートに読み込む前の、クエリ エディター上で、対象となる日付のデータの started_at の列データを変換します。
started_at の列名をクリックし列を選択した状態で [変換] タブの [データ型の検出] を使ってもいいですし、この日付データ型は [日付/時刻/タイムゾーン] と呼ばれるものなので、列名横のデータ型のボタンを押して、明示的に選択することで変換可能です。[ABC 123] のアイコンが地球儀と時計のアイコンに変わります。

この変換ステップを行うことで、[日付/時刻] に変換できるようになります。[日付/時刻/タイムゾーン] に一度変換しないで、いきなり [日付/時刻] を選択すると Error になるので注意してください。データ型ボタンで [日付/時刻] を選ぶと、列タイプの変更 ダイアログが表示されるので、[新規手順の追加] を選んでください。[現在のものを置換] はタイムゾーン付きのデータに変換したステップを置き換えてしまうのでエラーになります。

このように日付データを変換して Excel のワークシートに読み込むことで、シリアル値として扱うことができます。

日本語データも特に問題なく扱うことができています。イベント(勉強会)の概要データの description は HTMLタグを含むテキストデータとして取り込まれています。ここからまた何らかの判断をしたい場合は、クエリ エディターの詳細エディター上で M 言語を使ってやってもいいですし、ワークシートに取り込んだ後で、関数や VBA を使ってもいいでしょう。

実際は考慮すべき点がいっぱい

今回は JSON 形式のデータでも、取得と変換のクエリ エディターを使うことで、プログラミングすること無しに Excel ワークシートに取り込むことができることを紹介するのが目的でした。しかし、それ以外のところで考慮すべきことが出てくるのが実際でしょう。

たとえば、取得する件数。サービスによっては上限が決まっていて、それ以上のデータの取得は、今回のような件数の指定のほかに、ページ数指定や、オフセット指定を使うことが推奨されます。
Power Query / 取得と変換のクエリ エディターで、詳細エディターを使ってこれらへの対応が可能です。ページ数やオフセットの「繰り返し」の処理は、URLを組み立てる一連のステップを「関数化」して、ページ数やオフセットを引数として渡す、という方法を使います。

[追記] 参考 https://road2cloudoffice.blogspot.com/2017/07/power-bi-excel-web.html

この件数の上限への対応は結構「頭の体操」的なアイディアが必要になります。場合によっては CData さんの ODBC ドライバーを使うと幸せになれることがあります。
サイボウズの kintone の API も1回のリクエストあたりのデータ取得件数の上限がありますが、CData さんのドライバーを使うことで、その上限を気にせずにデータの取得が可能になります。

CData ODBC ドライバー一覧
https://www.cdata.com/jp/download/?f=odbc

Power Query/Excel 取得と変換/Power BI Desktop も標準機能としてさまざまなデータソースに対応していますが、CDataさんのような専業メーカーさんの ODBC ドライバーを試してみると、意外な発見や、解決策を見つけることができるかもしれません。

また、匿名アクセスではなく、ユーザー名＋パスワード、アプリケーション登録による Web キーの利用など、サービスによって認証の方法はさまざまです。
connpass と同じくらい IT 勉強会の告知・募集・管理ツールで人気がある Doorkeeper の場合は、検索の URL の送信と一緒に Header データに認証情報を入れる必要があります。connpass は「Web から」の「基本」を使いましたが、Doorkeeper は「詳細設定」を使って、Header に認証情報をセットして、検索リクエストを送信する必要があります。Doorkeeper の API の解説には、Power BI Desktop や Excel クエリ エディターの具体的な設定方法や手順はないので、苦労するポイントでしょう。

登録して、Public API Access Token を取得

HTTP要求ヘッダーに Bearer を使ってトークンを追加

正直いうと、Doorkeeper API で Authorization Bearer に行きつくまで結構な時間がかかりました。

長くなりましたが、Excel のデータの取得と変換という新しい外部データ取り込みの仕組みを使うことで様々なデータソースへ接続し、様々なデータ形式のデータを扱うことができます。まだまだ進化中ですが、その方向はなるべくコーディングさせない方向で、JSON形式のデータであっても、コーディングなしでテーブル形式に展開して、ワークシートに取り込むことが可能です。

食わず嫌いせずに、この新しい機能をぜひ使ってみてください。

[追記] 最後まで読んでいただいてありがとうございます。取得と変換の機能の一つの「ピボットの解除」もJSON処理同様これまでVBAでなければできないと言われていた処理です。こちらもおすすめ機能なので是非使ってみてください。
https://road2cloudoffice.blogspot.jp/2018/03/blog-post.html

[追記] REST APIで取れないデータをWebページスクレイピングでとる方法を追加しました。
https://road2cloudoffice.blogspot.com/2020/05/excel-web.html

Read More

[Excel] テーブルと Excel VBA

ワークシート関数を使う時、これまでの「範囲」の変化に対応することができるテーブル機能は Excel 2007 以降の最大の機能拡張、、、なんてことも言われています。

過去にこのブログでもいくつかテーブル機能について紹介しました。

テーブルのすすめシリーズ
http://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

機能、関数で多くのメリットを提供するテーブルですが、Excel VBA の世界ではどうでしょう？

まず、表としての範囲が、テーブルというオブジェクト（ListObject オブジェクト）になるため、最終行＋空白問題（上から End モードで下に行ったとき、空白があったら止まる）、もしくは最終行＋ごみ問題（行の一番下から上に上がっても、途中にごみがあればそこで止まる）は基本的になくなります。

というのも、テーブルではテーブルの行数は End モードを使わずとも、テーブルのプロパティで確認することができるからです。テーブルの行数は ListRows.Count でわかります。

ここで標準的なテーブルを実践ワークシート協会の田中亨が推奨する「黄金テンプレート」で処理するコードを見てみると以下のようになります。

サンプルテーブル

サンプルテーブル

テーブルでも表の処理同様の VBA コードを書くことができます。
名前が田中だったら、個数X単価を合計に代入する、といった処理だと、田中亨の黄金テンプレートで考えると、

範囲はどこか？　

行は 3 から 12。もちろん、最終行の 12 が可変だとしても、End モードで特定します。

条件はなにか？

条件は、名前のセルが "田中" だったら、ですね。

処理は何か？

合計セルに個数X単価を代入する、です。

そうすると VBA のコードは以下のようになりますね。

Sub sample1()
    Dim i As Long
    For i = 3 To Range("B2").End(xlDown).Row
        If Cells(i, 2) = "田中" Then
            Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
        End If
    Next i
End Sub

これをテーブルのオブジェクトを使って、テーブルらしく書いてみると、上記の表形式とは大きく違う点があります。それは「列番号」であり、「行番号」の考え方になります。

テーブル オブジェクトの世界では、ワークシートの「A列」や「D列」は意味がなく、あくまでテーブル内での列順序の番号です。上の例でいえば、名前の列は、B列で列番号2ではなく、あくまで名前フィールドであり、フィールドの番号はテーブル内で「1」になります。

さらに、テーブルというオブジェクトは「ListRowsコレクション」からデータ部分が構成されています。テーブルの行の各1行は ListRow オブジェクトとして扱うことができます。このことより、「何行あるか、わからないけど、For Each を使えば、そのテーブルのすべての行（ListRowsコレクション）を扱うことができる」として処理可能になります。

そのサンプル コードが以下です。

Sub sample2()
    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("TestTable")  ''テーブル名がTestTableだった場合
    Dim R As ListRow
    For Each R In tb.ListRows
        If R.Range(1) = "田中" Then
            R.Range(4) = R.Range(2) * R.Range(3)
        End If
    Next R
End Sub

たぶん、R.Range(1) という書き方ってあまり使っていなかったと思います。

この R.Range(1) という書き方は、以下の書き方と同じものです。

R.Range.Item(1)

R.Range.Cells(1)

おもしろいですね。

VBAセミナースタンダードで田中亨がよく「上級者は2次元配列は使いません。だって、Excel VBA にはワークシートがあるんですから！」と言いますが、上記の表記はその流れです。（正確にはこれは一次元配列ですけど）

For Each でとってくる R は一次元配列（的なもの)です。（要素の型が違うので配列とは言えませんが、便宜上お許しを）この1次元配列は1列または一行で複数セルを選択した「セル範囲」のイメージです。今はテーブルなので、以下のような横方向の1行のセル範囲です。

[[名前] [個数] [単価] [合計] ]

この順序です。ですから、名前は (1) で、個数が (2)、、、となるわけです。

その表記方法が R.Range(1) だったり、R.Range.Item(1) だったり、R.Range.Cells(1) になるわけです。セル範囲の Range オブジェクトを配列のイメージとして扱っているんですね。

繰り返しますが、ポイントは A列、B列といったワークシート上の絶対的な番地では無い！ということです。

上記の例はサンプルなので処理や条件がシンプルなものですが、これが複雑になってくると、Cells(行, 列) でセルを指定するやり方と、R.Range(要素) で処理すべきセルを指定する方法のどちらがいいかはだんだん変わってきます。

その意味から、変数を使って、テーブルの列順序を定義すると、さらに可読性が高まります。（IMEオン・オフの操作があるので日本語変数がいいわけではありませんが、サンプルとして定義します）

Sub sample3()

    Dim tb As ListObject

    Set tb = ActiveSheet.ListObjects("TestTable")

    Dim 名前 As Long, 個数 As Long, 単価 As Long, 合計 As Long

    名前 = tb.ListColumns("名前").Index

    個数 = tb.ListColumns("個数").Index

    単価 = tb.ListColumns("単価").Index

    合計 = tb.ListColumns("合計").Index

    

    Dim R As ListRow

    For Each R In tb.ListRows

        If R.Range(名前) = "田中" Then

            R.Range(合計) = R.Range(個数) * R.Range(単価)

        End If

    Next R

End Sub

たった5～6行の処理のための変数宣言と変数設定で7行は、、、と思いますが、通常はもっと条件や処理が複雑になるので、これだけで「行数が多い、無駄だ」と判断するのは早計だと思います。 For Each から Next R までの処理の「可読性」を見てください。実にわかりやすい表記になっています。

たぶん、テーブル ListObject オブジェクトを VBA で扱うのは、このオブジェクト変数を使ってテーブルを設定し、テーブル内のフィールドの順序を変数として設定していくのが一番良い方法だと思います。

ここでテーブル機能で重要な「構造化参照」が出てきてませんよね？

いろいろ試した結果、この「構造化参照」は VBA を編集するツールである VBE で実装されていないため、インテリセンスのような形で、文字通りの「構造化参照」という機能は実装されていないようです。テーブル名を指定すれば、そのテーブルにあるフィールドが表示される、、、ような機能は VBE には無い、ということです。

では、VBE に期待しないで表記したらどうなるでしょう？

ここでも Excel の Range オブジェクトの実装、、、「すげー」となるわけです（笑

VBE では構造化参照のメリット享受できないので使う理由がないのですが、無理やりやると以下のようなコードを書くことができます。

Sub sample4()

    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序

    Dim R As Range

    For Each R In Range("TestTable").Rows

        If R.Cells(1) = "田中" Then

            R.Cells(4) = R.Cells(2) * R.Cells(3)

        End If

    Next R

End Sub

テーブルを ListObject として宣言しない分だけ行数が短いですが、R.Cells(1) などを番号ではなくフィールド名で、、、となると、宣言や設定のためそれなりに行数が増えます。さらに、構造化参照が VBE でサポートされていないために、複雑、長くなればなるほどめんどうなことが発生してきます。(*1 最後に追記してます）

以上のことより、通常、テーブルを VBA で扱うのであれば、テーブルは ListRow のかたまりであり、テーブルを ListRows コレクションとして処理するのが王道でしょう。

そのときの For Each として１つ１つ処理するのは ListRow オブジェクトであり、その ListRow オブジェクトの扱いは「一次元j配列」的になる、と言えるでしょうね。

今後、データ接続でテーブル形式でデータを取り込むことが多くなります。そのときは、上記の考え方でテーブルをマクロで扱うことも多くなると思います。

(*1) 構造化参照が少なすぎるとおもったので追記。
構造化参照方式で以下のような表記が可能です。

Range("テーブル1[名前]")

上記でテーブル１の名前フィールドのデータ部分の範囲を指し示すことができます。
しかし、この Range(構造化参照) は、あくまで Range による範囲（つまり、named range) のため、ListObject との関連がありません。

そのため、Range("テーブル[名前]").Column は、ワークシート上の列番号を返します。


[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。

Read More

Power BI Desktop と Excel の Power BI アドイン

ここにきて Power BI Desktop (旧 Power BI Designer）と、これまでこのブログでご紹介してきた Excel の Power BI アドイン（Power Query や Power Pivot）ってどう違うの？という話題が私の周りで聞くようになりました。また、Power BI と Power BI Desktop と Power BI for Office 365 など “Power BI” という単語が付いている製品・サービスの違いについても、新しい機能メインの単独紹介記事ばかりが出てくるため、関連や違いがわからない、という話も私の周りで聞きます。

これ、わかりづらいですよね。

まず、ちょっと交通整理するために、以下の単語を使って区分けしてみます。

1. Power BI Excel アドイン
2. Power BI Desktop  
3. Power BI
4. Power BI for Office 365

1. Power BI Excel アドイン

これまでこのブログで紹介してきた、単に Power Pivot などと言っていたものですが、現状では例えば Power Pivot は「the Power Pivot in Microsoft Excel add-ins」（長い。。。）と呼ばれるようになっています。

https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8?ui=en-US&rs=en-US&ad=US

これまで Power BI Excel アドインとして以下をこのブログでも扱ってきましたし、私自身も Power Query や Power Pivot は業務でよく使っています。

• Power Query
• Power Pivot
• Power View
• Power Map

Power Query は Excel 2013 まではダウンロードセンターからダウンロードするアドインでしたが、Excel 2016 からは内部に組み込まれ、あらゆるエディションで利用可能となります。（一部制限あり）
このあたりのお話は以下のブログでも紹介しています。

Power Query が Excel 全エディションで利用可能になりました
Excel ユーザーのための Power BI 系インストールのお話

現実の話としては全エディションで利用可能になった Power Query 以外は、Professional Plus か Office 365 ProPlus 前提となるため、Excel アドインといえども対象となるユーザーは限定されます。

Power Query や Power Pivot は Excel をより使いやすく、リッチな機能にするアドイン、という認識ですが、以降の Power BI を見ると、実は本当に重要なアドインは Power View です。 Power Query や Power Pivot は Power View (または Power Map) による分析レポートを作成するための補助ツール、という捉え方です。

ただ通常は、Excel のワークシートでレポートは完結してしまう場合が多いので、Power View を活用している人はあまり多くないと思います。私自身も Power View までを使って分析レポートを作ることはほぼありませんでした。

しかし、Power View が目指すところは単なる綺麗な「レポート」ではないようです。レポートの共有を印刷による「紙」ではなく、たとえば、タブレットやスマホを使って、いつでも、どこでも共有可能にする、という使い方です。分析結果やキーとなる数字の共有、それもある程度定期的・自動的に更新されるデータの共有をする、それが以降の Power BI が目指すもののようです。

なお、Excel 2016 Preview (16.0.4229.1011) で、この Power View は [挿入] タブにありません。

Excel 2013 のリボン コマンド インターフェース にある パワー ビュー

パワー ビュー のリボン コマンドがない Excel 2016 (16.0.4229.1011) 2015年8月

[リボンのユーザー設定] をみると タブ レベルで [Power View] があるのですが、そのタブ自体が表示されていません。Excel のデータ分析のオプションをいぢっても変わらずです。
どうやら [挿入] タブから独立して [Power View] タブにしたものの、肝心の [Power View] タブが表示されていない、という現象のようです。

調べてみると、マイクロソフトではこれを Known Issues のバグとして認識しており、そのうち直す、としています。

なお、Excel 2016 Preview で Power View を手動で [リボンのユーザー設定] で追加することは可能です。コマンドの選択で [リボンにないコマンド] で、[Power View レポートの挿入] をユーザー設定グループに追加してください。

2. Power BI Desktop (旧 Power BI Designer)

GA(一般利用）にともない、Power BI Designer は Power BI Desktop に名前が変更されました。
Power BI Desktop の特徴は「Excel を必要としない Power View シート(的なもの)作成ツール」の1点につきます。なおかつ、Power BI Excel アドインを使っていたユーザーであれば、ほぼ同じ操作、考え方で Excel の Power View シートに相当する「レポート」を作成することが可能です。そして、この「レポート」を他のユーザーと共有するためのクラウドサービスが「Power BI」(以下の 3. Power BI)になります。

Power BI Excel アドインで慣れている人であれば、ほぼ、取説なしで利用可能なほど、操作性は同じです。
(以下は Power BI に Office 365 の組織アカウントを使って無料トライアルをサインイン済み、という状況を前提としています。）

[データを取得]は Power Query に相当します。

データ取得先が SharePoint Online であれば、 OData フィードを使います。 URL の指定の仕方などは Power Query と全く一緒です。

組織アカウントを使って、SharePoint Online サイトにサインインします。

ナビゲーターも Power Query のものと一緒です。

[編集] ボタンで Power Query と同じ クエリ エディター が立ち上がります。

[閉じて読み込む] で、ワークシートにデータを展開するのではなく、データ エリアにレコードを読み込みます。

左側にある [リレーション] タブは、Power Pivot のダイアグラムビューに相当します。

左側の [レポート] タブが Power View シートに相当します。ここで、キーとなる数値の表示や、データの推移や割合を表すグラフを使い、レポートを作成します。

これらは pbix 拡張子のファイルとしてローカルに保存できます。
Power View もそうですし、この Power BI Desktop による「レポート」も、対話型でデータの確認・分析ができることがメリットと言われています。

Power BI Desktop のもうひとつ重要な機能は、この pbix ファイルをサーバーにアップロードして、他のメンバーと共有できる点です。

あらかじめ Power BI の無料トライアルにサインアップしていれば、[発行] を押すと Power BI サイトへのサインインが求められます。

発行が成功すると、発行したレポートへのリンクが表示されます。

リンクをクリックすると、app.powerbi.com の自分の Web サイトでアップロードしたレポートを確認することができます。

3. Power BI

きっと、この Power BI というサービスが、これまで Excel と Office 365/SharePoint Online を使っていたユーザーにとって一番わかりづらいと思います。

イメージ的には、Office 365 とは違う、別のサーバー(クラウド サービス)が立っています。ただし、Azure AD でアカウントの管理を行っているので、Office 365 の組織アカウントでサインアップすると、Office 365 の一部のような形でアプリランチャーに登録されます。

ちゃっかり Office 365 管理センターの [ライセンス] にも登録されています。

実践ワークシート協会では「Power BI for Office 365」を購入していたのですが、アプリランチャーのアイコンは Power BI に置き換わりました。
なお、Power BI サイトから、従来の Power BI for Office 365 にいくには、歯車アイコンから Power BI for Office 365 を選択することが可能です。

無料のサービスでは 1GB の powerbi.com のストレージが利用可能です。このストレージに対して、Power BI Desktop で作成した pbix ファイルをアップロードするのはもちろんですが、Excel の Power View で作成したレポートを含む Excel ブックもアップロードすることが可能です。Power BI サイトの大まかな使い方は以下と言えるでしょう。

レポート共有のためのアップロード先

• Power BI Desktop で各サーバー、サービスのデータを取り込み、レポートを作成し、pbix ファイルを powerbi.com にアップロードする
• Excel で各サーバー、サービスのデータを Power Query のモデル OLE DB接続で取り込み、Power View シートを作成、xlsx ファイルを powerbi.com にアップロードする

レポート共有のためのダッシュボードの作成

• アップロードした pbix や xlsx のレポートから必要なデータを「ダッシュボード」に貼り付け、ダッシュボードの共有先を指定する（招待する）

レポートが使用しているデータソース（データセット）のアクセスと更新頻度の管理

• データ接続の即時更新
• 更新頻度の設定
• データソースへの認証情報の保存

他のサービスや共有可能なコンテンツの管理と参照

• ダッシュボード、レポート、データセットの組み合わせでコンテンツ パックを作成し、組織内で共有をする
• 同じ組織の他の人が公開しているコンテンツ パックの参照
• Salesforce や Google Analytics など他のサービスのコンテンツ パックの参照と共有（Power BI Desktop や Excel を使わない）

ダッシュボード共有先の管理

• 共有したダッシュボードが誰と共有されているかを管理する
• ダッシュボードを共有するための「グループ」を参照、編集、新規作成できる
• この「グループ」は、Outlook で作成し、ファイルやスレッド(会話)を指定したメンバーと共有する、あの「グループ」機能と同じ
• ダッシュボードに登録することで、iPhone などの Power BI アプリからダッシュボードを参照できるようになります

iPhone の Power BI アプリでダッシュボードを表示

なお、無償サービスはストレージが 1GB まで、といった制限がありますが、10GB までに拡張した Power BI Pro という有償サービスのトライアルもあります。

実はこれがやっかいというか、意識せずして Power BI Pro のトライアルに移行してしまっている場合があります。私もそれになってました。。。
Power BI Pro トライアルへの申込みページはありません。無償のサービスを使っていて、有償の機能を使った時点で自動的に有償サービスの Power BI Pro のトライアルとなるようです。（私の記憶では、有償のみの設定をして、Pro のトライアルを使うかどうかの確認をされた記憶はないのですが。。。）
自分が Power BI Pro のトライアルになっているかどうかは、Power BI サイトの歯車アイコンのストレージをみるといいでしょう。パーソナル ストレージの管理で 10GB になっていれば、 Power BI Pro のトライアルになっています。

でも、10GB なんて使わないのに、どうして Power BI Pro のトライアルに移行するのか、、、謎でした。

たぶん、多くの人が「やってしまう」Power BI Pro の機能はデータセットの「更新スケジュールの設定」でしょう。

この設定は1日に1回までであれば無償サービスの範囲ですが、1日に2回以上の更新を設定すれば、それは Power BI Pro でなければならないために Power BI Pro トライアルに移行しているようです。
[追記] 知らず知らずにトライアルに移行することはないようです。現在検証中です。私の場合状況から Power BI for Office 365 のライセンス割り当てがすでにされていることが関係しそうです。

Power BI 無償と有償の違い
https://powerbi.microsoft.com/pricing

Power BI Pro の Free Trial について
https://support.powerbi.com/knowledgebase/articles/664495

Free Trial は 60日間のようですが、60日たってどうなるか、、、までは上記のページから探すことはできませんでした。

4. Power BI for Office 365

Power BI for Office 365 も、URL から判断すると sites.powerbi.com で提供しているサービスで、このサービスを適用した SharePoint Online サイトのドキュメント ライブラリーにある Excel ブックを更新、Excel Online で表示したり、Power View を Online で操作することを目的としたものです。他に Data Management Gateway 機能で　SharePoint の BCS (Business Connectivity Services) のように Office 365 以外のオンプレミスの SQL Server のデータなどを Office 365 経由で扱えるようにしたり、個人で作成した Power Query のクエリを他のメンバーと共有することができるようになります。

ただ、Power BI for Office 365 は、正直なところ実践ワークシート協会の Office 365 / SharePoint Online の使い方から活用しきれませんでした。

もっとも大きな理由は、SharePoint Online のリスト アイテムと Excel ブックのデータ接続の更新ができないことでした。

https://social.technet.microsoft.com/Forums/en-US/332b421c-63aa-4040-a856-db65d543c2b7/power-bi-refresh-from-sharepoint-online-list-data-source?forum=powerbiforoffice365

本来であれば、Excel で SharePoint Online のリストからデータを取得し、キーとなる数値データを Power View シートで表示し、そのブックを SharePoint Online のドキュメント ライブラリーに保存、 Power BI for Office 365 の BI サイトでブックの定期更新をして、Excel Online で表示したかったのです。ところが、データ接続を使った SharePoint Online のリスト データ更新ができないため、一旦 PC 側の Excel でブックを開き、更新後にアップロードという処理になり、これであれば、すでに Office データ接続と Excel Web Access/Excel Services で実現している方法よりも「使い勝手が悪い」となりました。（念のため、これは SharePoint Online のリストをデータソースにしているためで、SQL Server やオンプレミスの SharePoint Server であれば問題ないようです）

Power BI for Office 365 そのものは、Data Management Gateway による Office 365 とオンプレミスの SharePoint Server や SQL Server の統合機能もあり、また、Power Query のクエリの共有機能もあるため、使える機能も多くあると思います。しかし、Office 365 のクラウド セントリックな業務形態の実践ワークシート協会では、その良さを活かす場面がなかなかなく、逆にデータ接続における SharePoint Online との親和性の未熟さがネックとなってしまった、ということでしょう。

あと、現状 Power BI for Office 365 の Power BI サイトを開くと以下のようになります。

画面上部の「最新版の Power BI はこちらです。今すぐ試す」の表示から考えると、Power BI for Office 365 よりも、Power BI に集約させたい、というメッセージかもしれませんね。

Power BI にサインアップすることで、Office 365 管理センターから Power BI がなくなりました。しかし、https://admin.powerbi.com にアクセスすることで、従来の Power BI 管理センターにアクセスができました。または、SharePoint サイトに作った Power BI Site トップで歯車アイコンから Power BI Admin Center を選んで Power BI 管理センターに移動できます。

Power BI for Office 365 を使いたかった理由はブックの自動更新 (scheduled data refresh) でした。現状、3. Power BI でそれが可能です。それも SharePoint Online のリストがデータ ソースであってもです。1日に1回の更新であれば、無償で十分であり、そもそも Power BI for Office 365 の管理センターでは1日に1回の更新しか指定できませんでした。このようなブックの自動更新の目的であれば、無償の Power BI で十分だと思います。

Power BI for Office 365 でブック毎に設定できる更新スケジュールの構成。頻度は1日1回か、週に1回となる。

 

今回、Power BI と Power BI Desktop を使い込むにあたり、これまで実現できていなかった機能を確認することができました。
Excel ブックに含まれているデータ接続の Excel Online での更新については、すでに Office データ接続を使って、SharePoint サイトにアプリ権限付与をすることで可能でしたが、Power BI を使うことで Power Query による SharePoint Online リストデータ接続を含む Excel ブックを Excel Online で更新可能に設定することができます。

次回はその使い勝手と設定をご紹介したいと思います。

Read More