2017/06/23

[Excel] Microsoft Flow で Excel を使う

Modern Excel

Power BI系のツールを調べていると Modern Excel (モダンエクセル)という言葉を見ることがあります。
これは Power Query や Power Pivot といった、Power なんとか系ツールを使ってお仕事を効率的にするための新しい Excel の使い方をさす「造語」です。

WHAT IS MODERN EXCEL? - ExcelTV(英語)
https://excel.tv/what-is-modern-excel/

Excel は製品の歴史が古いにもかかわらず、今でも多くのユーザーに使われ、それも業務の第一線で利用されている珍しいソフトウェアです。そのため、現場にこれまで蓄積されたテクニックや技術が数多くあり、鉄板ネタ、その世界では常識、といった手法や使い方が存在します。その反面、レガシーな方法でも多くのことができてしまうため、新しいツールや機能を積極的に取り込む「心の」ハードルが高くなっている、という状況も散見されます。

現在、モダン Excel の世界で Power Query や Power Pivot の緩やかな Excel 本体との統合が進んでいます。最新アップデートの Excel ではデータ タブの [データの取得と変換] は Power Query です。Power Pivot アドインを有効にしていなくてもデータ モデルやリレーションシップ、メジャーを Excel で使うことが可能です。

[Excel] データの取得と変換が標準になりました
https://road2cloudoffice.blogspot.jp/2017/06/excel.html

リレーションシップとデータ モデル
https://road2cloudoffice.blogspot.jp/2015/02/blog-post.html

[Excel BI] ピボット テーブルとメジャー
https://road2cloudoffice.blogspot.jp/2017/05/excel-bi.html

さらに、これから Excel との連携が進んでいくのは Power BI だけにとどまらず、 PowerApps と Microsoft Flow といったツールであるのは、ほぼ間違いないでしょう。OneDrive などのクラウド ストレージに保存されている Excel ブックをデータ ソースとして、PowerApps や Microsoft Flow で利用するパターンです。何やら難しい仕組みのようにも見えますが、これらのツールはタブレットやスマホなどの利用を前提とし、かつアプリ開発や設定もノンプログラミングでできることを目的にしているようです。

Microsoft Flow と Excel 

将来重要性が増すだろうツールの1つである Microsoft Flow (マイクロソフト フロー) という単語を見ると、なにかワークフローを扱って、難しそう、というイメージがあるかもしれません。

Microsoft Flow
https://flow.microsoft.com/ja-jp/


自動化するワークフロー、ビジネス プロセスの自動化、などを見ると難しそうですが、Excel ユーザーの観点から以下のようなことが Flow でできます。

  • Excel を開かずにワークシートのテーブルにデータを自動追加できる
Excel ブックのデータを読み込む、取り込む、といったことは Power Query で可能でした。Power BI系ツールでは難しく、VBA を使わなければならないのはブックへのデータの「保存」や「書き込み」の処理系でした。このエリアが Flow や PowerApps によって可能になりつつあります。

ただし、対象となる Excel ブックは OneDrive や SharePoint Online のフォルダーの中に作成することが必須です。クラウドが利用可能であれば、欲しいデータを欲しい形で「Excel のワークシート(のテーブル)」で扱うことができるので、いろいろとアイディアが膨らみますよね。Excel ブックとして扱うことはもちろん、レポートや分析は Power BI の得意の分野ですから、Excel ブックをデータ ソースとして利用することができます。

どのくらい Flow による Excel ブックへのデータの追加・保存が簡単なのか、順を追って見ていきましょう。

Flow で Excel を使うポイントは「テーブル」

Flow の設定は Web 上で行います。そのため、Microsoft Flow の Web サイトに「サインイン」する必要があります。Office 365 の組織アカウントか、Microsoft アカウントの利用が可能です。Power BI と違って、hotmail などのフリーのメールアドレスの Microsoft アカウントの利用が可能ですが、作成したフローを他の人と共有できません。他のユーザーとなんらかのコラボレーションをする場合は、Office 365 の組織アカウントをベースにする方向性なのでしょう。一人で試すだけであれば、いくつかの制限がありますが Microsoft アカウントでも利用可能です。
他のユーザーとフローを共有しようとすると表示されるアラート

Flow のサイトでは数多くのテンプレート(フローのサンプル)が用意されています。もちろんゼロから作ることも可能ですが、テンプレートを利用することで開発・設定の時間を大幅に削減することが可能です。

Excel ブックを保存先として使う場合のポイントは2つあります。
  • 事前に使用するブックを用意し、ワークシートに「テーブル」を作っておく
  • Excel Online でも、Flow のテスト時にはブックを必ず閉じる(ロックを避ける)
テーブルの列名はどんな名前でもいいです。フローの設定で、用意した列と保存するデータの突合せを行うことができるからです。とはいえ、どんなデータがフローのテンプレートで用意されているかを確認し、必要な列を準備することを考えれば、事前にテンプレートでデータの名前を確認して、それとわかる列名でテーブルを作成することになります。

なお、Excel の「テーブル」をあまり使ったことがない人も結構いるようです。モダン Excel では基本、テーブルの使用が前提となっていると考えて差し支えないでしょう。行・列からなるセル範囲とは全く別の新機能を提供してくれます。テーブルについては当ブログでも過去に紹介しているのでぜひ参照してください。

自動的に Excel にデータを書き込む

Office 365 のユーザーは Office 365 の組織アカウントを使って Flow を使うのが良いでしょう。他のユーザーとのフローの共有はもちろんですが、サンプルとして使う「一定間隔で何かする」という繰り返しの設定の上限がマイクロソフト アカウントを使った場合(無料プラン)と変わります。さらに Flow の Premium ラインセンスを購入することで利用できる機能が変わります。上限を超えた設定をするとアラートメッセージなどでも表示されますが、プランの種類と制限の確認は以下を参照すると良いでしょう。

マイクロソフト フロー プラン
https://flow.microsoft.com/ja-jp/pricing/

シンプルな Flow と Excel の連携のサンプルを紹介します。とてもシンプルですが、これを Excel のみで実現するには、、、と考えながら見ると面白いでしょう。
アカウントはあえて Microsoft アカウントの無料プランと OneDrive を使ってみますが、Office 365 組織アカウントを持っているならば組織アカウントで試すのがいいと思います。サンプルの処理は「15分おきに、特定の文字列と現在の時間を Excel のテーブルに追記する(=最後の行に追加していく)」です。よって、前提として

・マイクロソフト アカウント取得済み
・マイクロソフト アカウントで OneDrive が使える
・できれば Excel が PC にインストールされている(Excel Online のみで操作可能ですが、あったほうが吉)

です。
大まかな流れは、

・Microsoft Flow で新しくフローを登録する
・OneDrive で Excel ブックを新規作成し、データ保存のためのテーブルを準備する
・フローの Excel 操作のアクションで準備したテーブルを指定する
・フローを動かす
・Excel のテーブルにデータが追加されているのを確認する

です。

1) Microsoft Flow にサインイン


Office 365 の組織アカウントを使うのであれば、ポータルのランチャーから [Flow] を選びます。直接 Flow のページへは、https://flow.microsoft.com/ja-jp/ でいきます。

Microsoft Flow サービスのページからサインインします。サインイン後にトップバー(タブ)の [マイ フロー] をクリックして、マイ フローに移動します。


2) [+ 一から作成] をクリックして、フローを新規作成、名前を付ける


フロー名 無題 の無題の部分にフロー名を入力します。[コネクタ] から [スケジュール] をクリックしてフローに追加します。このスケジュールは、設定する間隔で「動き出す」部品です。フローの開発はこのように「部品」を選択して、組み合わせていきます。

3) スケジュール 繰り返しの設定


繰り返しのスケジュールトリガーはプランの最小値の 15 分にしておきます。
Microsoft アカウントを使った無料プランでは、この間隔の最小設定は 15 分です。Office 365/Dynamics365 のプラン は 5 分(ただし、5分未満もお試し版で試すことができます)、Flow プラン1 は 3分、プラン2は 1分(最小設定)です。


次に、Excel へデータを追加するアクションを追加します。上図のように [+ 新しいステップ] から [アクションの追加] をクリックします。

4) Excel 操作アクションの追加と設定

結論から言えば、ここではアクションのみ追加します。OneDrive の Excel ブックとテーブルの事前準備ができていないためです。
[コネクタ] の下にある [アクション] から [Excel 行の挿入] を選択します。


このアクションを選択したのち、OneDrive の Excel ブックを指定することになるので、ここで、OneDrive に Excel ブックを作成します。

今回の Excel への行の追加のサンプルの「フォーマット」は自由ですが、アクションによっては追加できるデータの種類と数が決まっています。事前にテーブルと列を用意するにも、どんなデータを取得し追加するかを調べる必要があるので、実際のところはテーブルを用意する前にここでチェックすることになります。

5) OneDrive 上に Excel ブックとテーブルを作成する

OneDrive上に Excel ブックを作成します。作成するフォルダーは自由です。デスクトップ アプリケーションの Excel でブックとテーブルを作成し、OneDrive にアップロードしたものも当然使えます。私のサンプルでは 1_WORKS という名前のフォルダーを作り、そこに ブック1.xlsx を作成しました。
OneDrive の Excel Online だけで操作する場合の注意点は、テーブル ツールが使えないことです。ちょっと「?」な仕様なのですが、Excel Online にてテーブルの作成は可能で、作成されるテーブルは日本語環境であれば「テーブル1」になります。しかし、テーブルの名前の確認や、名前の変更は Excel Online からできません。

以下のアニメーションGIFは、Excel Online だけでテーブルを作る手順です。 列名は 1, 2,3,4 で連番にしました。テーブル名の指定はできないので、この場合は「テーブル1」になります。[挿入] タブの [テーブル] で作成ができます。


なお、テーブルを作成し終えたら、Excel Online のブラウザは閉じてください。
コンシューマー OneDrive の Excel Online は、ブックを開くと「編集モード」で開いているため、ブックにロックかかり、Flow によるデータ追加が失敗するからです。(これは、業務運用上大きな考慮点になりえます)

6) アクションに Excel ブックを関連付ける

Excel ブック操作のアクションを選択すると、マイクロソフト アカウントを使用しているので OneDrive へサインインになりますが、接続先のクラウド サービスを変更することは可能です。たとえば、OneDrive for Business や SharePoint Online ドキュメント フォルダーにある Excel ブックも、適切なアクセス権が設定されているアカウントを使うことで利用可能です。

Excel 行の追加のアクションを追加して、OneDrive 上のブックを指定します。ブックを指定することで、ブックに含まれるテーブルがリスト表示されるので、それを選ぶと、テーブルに含まれる列すべてが表示され、列に追加するデータを指定することが可能になります。


7) 追加データを列に設定する(行の追加日時の設定方法)

業務利用の場合は、イベント間隔の部品 [スケジュー 繰り返し] と Excel ブックへのデータ追加の部品 [行の挿入] の間に「なんらかの処理をする部品」を配置して、その結果を Excel に取り込みますが、今回は直接 [行の挿入] で入力データを設定します。
文字列はそのまま同じ文字列挿入の繰り返しになりますが、ここに変数や関数があれば、イベント毎に処理をして Excel に書き込むことができます。
Excel ユーザーとして、だいたい一番最初にやりたくなるのは、行が追加された日時(タイムスタンプ)を入れることです。ワークシート関数に NOW() を入れたけど、ワークシート更新するたびに数値が変わる、どうすればいい?のアレです。

列1に文字列の「Excel-Flowテスト」
列2に Flow で日時を取得するワークフロー定義言語の関数 utcnow()
列3に 世界標準時刻 utc に 9 時間追加した日本標準時刻
列4に yyyy/MM/dd HH:mm:ss 表示の日付・時刻

上記データを追加するための列設定は以下です。

列1
Excel-Flowテスト

列2
"@{utcnow()}"

列3
"@{addhours(utcnow(),9)}"

列4
"@{formatDateTime(addhours(utcnow(),9),'yyyy/MM/dd HH:mm:ss')}"

特に列4の英文字の大文字・小文字に注意してください。大文字の MM は月を、小文字の mm は分を、HH は24時間表示など、それぞれ意味があります。
utcnow() や addhours()、formatDateTime() は「ワークフロー定義言語」の関数です。M言語や DAX、R言語も Power BI でやらなきゃなぁ、と思っていたところにワークフロー定義言語 (Workflow Definition Language)がやってきました!これも避けては通れない言語でしょうね。ただ、大きなトレンドは「如何にコードを書かせないか」なので、将来的に学習コストは最小限になることを期待しましょう。
また、関数の前後についているダブルコーテーションは必要です。


ワークフロー定義言語については、まずは以下を参照されるといいでしょう。

Azure Logic Apps のワークフロー定義言語
https://docs.microsoft.com/ja-jp/azure/logic-apps/logic-apps-workflow-definition-language

また、Microsoft Flow は Azure Logic Apps をベースに作成されています。上記のようにワークフロー定義言語などの資料は Azure Logic Apps のものが参照できたりします。
Microsoft Flow と Azure Logic Apps の関係は以下のページが参考になります。

Flow, Logic Apps, Functions, WebJobs の比較
https://docs.microsoft.com/ja-jp/azure/azure-functions/functions-compare-logic-apps-ms-flow-webjobs

8) フローを保存し、動かす

フローの作成をクリックすると、保存され、実行されます。


エラーがなければ、完了をクリックのメッセージが表示されます。[完了]5D; の 5D; はバグですね。


完了を押さずとも、初回のフローが実行されて、その結果が再描画され表示されます。


完了をクリックすると、編集モードが終了します。
Excel ブックを確認すると、以下のようにデータが追記されていることが確認できます。


ダブルコーテーションで囲まれている文字を挿入しているので、Excel としては "2017/06/22 16:20:05" は日付データのシリアル値ではなく、文字列です。列2から列4のワークフロー定義言語の書き方の検討余地はまだあると思いますが、たとえば "@{utcnow()}" の両端のダブルコーテーションを最初からとると駄目です。少し動きが怪しいのですが、ダブルコーテーションをつけることで確実に関数として動きますが、最初から外して関数を利用しようとすると「文字列」として処理されます。ただし、一度ダブルコーテーション付きで登録し動いているフローを、あらためてフローの編集で両サイドのダブルコーテーションをはずすと、ダブルコーテーションがない日付文字列が入ります。どうやらデータ挿入のためのワークフロー定義言語関数の処理と JSON の構成方法に課題がありそうです。まずはダブルコーテーション必須で覚えておいたほうがよさそうです。

ちなみに、これでタイプミスなどでエラーになった、もしくは上述のワークフロー定義言語の書き方を変える、などフローを更新した場合、間隔の15分待つことはありません。[再送信] や [今すぐ実行] をすることで、最小設定時間以内でもフローを動かすことができます。


Excel のテーブルと Flow

Flow で Excel を使う場合は、事前にExcelのテーブルを用意しますが、Flowにおけるテーブル利用時の特徴・注意点は「セルの数式は使用できない」です。数式によって値を算出するセルをテーブルの列として使うことができません。Flow によって新しく追加した行には、テーブルで事前に定義された数式はコピーされません。A列に1、B列に2だけをいれて、C列で =A+B で 3 にするようなパターンです。コピーされないというより、数式がクリアされる感じです。

文字列や数値はそれほど扱いに困りません。何も設定していないセルの書式「標準」で事足ります。一方で、シリアル値は、上記の 8) で紹介したように utcnow() で世界標準時をとり、addhours() で9時間足せば日本の時間になりますが、 2017-06-22T09:50:51.0064743Z のような記述になり、このままセルに入力しても日付データとして Excel は認識せず、文字列として扱います。また繰り返しになりますが、ダブルコーテーション問題もあります。

なんだ、結局使えないじゃないか、と考える前に、この「Excel を起動することなく、自動的に、ブックにデータを追加する」という仕組みは、これまでプログラム開発が必要になるものでした。それ、Excel の仕事じゃないですよね?と言われる範疇のものです。これをノン・コーディングで可能にするのが Flow です。

後処理を Excel 中心にすると、どうしてもビックテーブル(ビックデータじゃありませんよ)で考えがちです。ビックテーブルとは、すべてのデータを含んだ1枚の大きな表です。その Excel のビックテーブルと、数式が使えない、ブックを開いているとロックする、といった Flow の制限を組み合わせると活用が難しく感じられますが、Power BI を使ってデータ ソースとして Excel ブックを指定すると Excel でブックを開くことなくデータを取得することができ、Power Query のデータクレンジングとデータの型変換を持つ ETL 機能を使えば、ダブルコーテーション付きのテキスト文字列を日付のシリアル値に変えることはクリック操作のみで可能です。異なるブックのテーブル間のリレーションシップによるマージも可能です。

制限や考慮点がある、情報がまだまだ少ない、といった状況ですが、モダン Excel の重要な要素の1つであることは間違いないので、引き続きウォッチしていく価値はあると思います。

まとめ

Excel と Flow の関係を紹介してきましたが、Excel を扱うアクションは行の挿入だけではなく、行の取得もあります。取得するを開始する行番号をスキップ数で指定し、そこから指示した行数分のデータを全列分取得します。Flow の内部では、取得したデータは JSON形式で保持され、次のアクションで利用されます。

どんなことが Flow と Excel の組み合わせでできるかはテンプレートを参照するのが一番です。Excel を含むテンプレートの一覧は、Flow のトップページの検索ボックス [最初に使用するテンプレートまたはサービスを探す] で Excel を入力して検索すると表示されます。


以上、長くなりましたが、参考になれば幸いです。

2017/06/15

[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を組み立てる一連のステップを「関数化」して、ページ数やオフセットを引数として渡す、という方法を使います。

この件数の上限への対応は結構「頭の体操」的なアイディアが必要になります。場合によっては 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

2017/06/12

[Excel] データの取得と変換が標準になりました

Excel の [データ] タブの [外部データの取り込み] がリボンから消え、[データの取得と変換] が標準機能になることが発表されていました。Insider などの最新版を早く使う更新チャンネルでは3月で変更されていましたが、一般の人向けのチャネルでも更新が反映されたようです。

バージョン 1704 ビルド 8067.2157

データの取得と変換がリボンで標準機能になりました
この [データの取得と変換] は、Power Query と呼ばれていたアドイン機能が Excel の標準機能として取り込まれたものです。さまざまなデータを扱うことができ、これまで VBA を使わなければできなかったデータの操作・取り込みも可能になっています。

マイクロソフトによる記事
統合された取得と変換(support.office.com)

上記のマイクロソフトによる記事に書かれているように、この機能は「Office 365 サブスクリプションご利用の場合に限ります。」です。

過去にこのブログでも「Power Query」の機能として、いくつか紹介しています。

Excel ユーザーのための Power Query

[Power Query/取得と変換] ブックにある複数のワークシートをまとめる

[Power Query/取得と変換] ブックにある複数のワークシートをまとめる 2

CSVファイルの取り込みも、この新しい「データの取得と変換」をぜひ使ってみてください。代表的な新機能として、CSVデータ取り込み時に条件を指定して取り込み件数を絞り込むことができます。大量件数のCSVファイルの扱いで悩んでいる人にとっては、VBA を使わなくても、Excel に取り込むデータ件数を少なくできます。

いやいやいや、それでも前の機能を使わなければならない、いずれは移行するとしても、今は前の機能を使いたい、という方は、オプションで復活させることができます。

[ファイル] - [オプション] - [データ] の「レガシ データ インポート ウィザードの表示」が、これまでの[外部データの取り込み] です。このチェックボックスをオンにすることで、[データの取得] から展開する [従来のウィザード] が追加・表示、その中に復活させることができます。この変更はすぐに反映されるので、Excel を立ち上げなおす必要はありません。



オプションの [データ] でチェックをしたのにリボンに表示されない、と慌てないでください。[データの取得] の下に [従来のウィザード] が追加され、そこにチェックした復活させたい機能があります。
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を返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。