2015/07/07

Power Query から https 経由で SharePoint の Excel ブックを開く

Power Query (2.23.4035.242 June 2015 Release) で https 経由で SharePoint のドキュメントライブラリに保存されている Excel ブック、および OneDrive for Business にある Excel ブックへのアクセスが可能になっていることを確認しました。(もしかしたら、May 2015 Release からかもしれません、、、、)

Power Query は頻繁に更新されています。数か月前は https 経由で SharePoint や OneDrive for Biz の Excel へのデータ接続はできませんでした。ローカル PC の同期フォルダーを使っているのであれば、同期フォルダー内の Excel ブックにデータ接続可能でした。

今後は同期フォルダーを構成していなくても、https 経由で Excel ブックに接続可能です。(ただし、コンシューマー版の OneDrive へのアクセスは成功していません。OneDrive for Business は Office 365 で提供されている OneDrive です。これは SharePoint をベースにしています)

取り込み手順

使い方はいたって簡単です。

[Power Query] タブの [外部データの取り込み] の [ファイルから] にある [Excel から] を選択します。

PQDCFILEEXCEL

[ファイル名] に SharePoint ドキュメント ライブラリーの URL を入れます。

SPDOCLIBURL

この URL は SharePoint のドキュメント ライブラリーであれば_layouts の前までになります。
OneDrive for Business も同様です。

doclibURL

[開く] ボタンを押すと、SharePoint サイトにある「すべてのサイト コンテンツ」のリストが表示されます。

今回は [ドキュメント] にあるブックを開くので、[ドキュメント] をダブルクリックします。(英語表記は Shared Documents)

allsitecontents

ドキュメント ライブラリにある Excel ブックを選択し、ダブルクリックするか、[開く] ボタンを押します。
すると、Web コンテンツへのアクセス ダイアログが表示されるので、[組織アカウント] を選んで、該当する URL をチェックします。
既定が [匿名] になっているので注意してください。

AccesstoWebContent

組織アカウント(Office 365 の ID)とパスワードでサインインします。

サインインが完了すると、以下のダイアログになるので [接続] をクリックします。

AfterSignIn

ナビゲーター ウィンドウが表示されるので、取り込みたいシートやテーブルを指定します。

PQNavigatorWindows

[編集] ボタンを押せば、クエリ エディタ ウィンドウが表示され、クエリの編集をすることができます。
クエリ エディタ ウィンドウではデータの絞り込みや並べ替え、列の削除や追加、データ型の変換を行い、欲しい形でデータを取り込むことができます。

QueryEditorWindow

[閉じて読み込む] を押せばワークシートにデータが展開されます。特に編集の必要がなければナビゲーター ウィンドウの [読み込む] ボタンでデータの読み込みが可能です。

ImportData

これまで、ファイルサーバー上の Excel ブックへのデータ接続は可能でしたが、https 経由でのデータ接続は少なくとも半年前はできませんでした。これにより、\\サーバー名 でのアクセスと同じように https:// で SharePoint 上にある Excel ブックへの接続が可能になりました。

何がうれしいの?

真っ先に思い浮かべたのが「Excel アンケート」です。

アンケートの共有をしている間は、ローカルの PC/Excel でブックを編集モードで開くことができません。

locked

アンケート集計が溜まってくると、ピボットテーブルを使って分析したくなるのですが、Excel Online ではピボットテーブルをゼロから作ることができません。
こんなときには Power Query を使って、SharePoint にある Excel ブックをクエリで読み込むことでピボットテーブルを作り、データ分析が可能になります。もちろん [データ更新] によって、最新のデータにすることも可能です。

次に使えるのは外部ブックのテーブル オブジェクトへのリンクです。これは SharePoint のドキュメント ライブラリーにある Excel ブックに限らず、ローカル PC やファイルサーバーでも同様です。

ここに、外部ブックの範囲に対して VLOOKUP を使ってデータを参照しているブックがあるとします。範囲の場合は再計算によって「その時の」外部ブックのデータを取り込む(=更新)することが可能です。

このブログで過去に紹介しているように、VLOOKUP や INDEX、MATCH などの「範囲指定」する関数にとって、範囲を「テーブル」にすることで、データの増減に自動的に対応可能になることは、テーブルを使う最大のメリットなのですが、テーブルの場合は外部リンクは使えないのです。このことはマイクロソフトの技術記事でも紹介されています。

Excel テーブル 数式で構造化参照を使う(support.office.com)

ここで「構造化参照を活用するヒント」に以下の記述があります。

“他のブックの Excel テーブルへの外部リンクを含むブックを使用する     ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合は、リンクを含む「リンク先」ブックの #REF! エラーを回避するため、そのリンクされた「リンク元」ブックを開いておく必要があります。リンク先ブックを最初に開くと、#REF! エラーが表示され、その後リンク元ブックを開くと、エラーは解決します。リンク元ブックを最初に開くと、エラー コードは表示されません。” (引用おわり)

つまり、リンク先ブックを開いておかなければ、別のブックのテーブルへリンクしている数式は #REF! エラーになります。

これを回避するために、リンクを使わず、Power Query を使うことで #REF! エラーを回避しつつ、データ更新によって最新データを参照することが可能になります。

この機能拡張によって、Office 365 の SharePoint や OneDrive for Business を Excel の保存先として、従来のファイルサーバーのように使うことが可能になりました。

 

毎月にようにリリースされる新しい Power Query については、英語ですが Office Blogs で新リリースごとに紹介されています。

https://blogs.office.com/

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018