Power BI と Excel

この投稿は Office 365 Advent Calendar 2015 Dec 8th にもなります。

Power Query や Power Pivot といった Excel アドインが提供され、Power BI というのは Excel が中心になっているような気がしていましたが、最近、どうもその雲行きが怪しいな、と思い始めています。

Power BI のおさらいを含めながら、雲行きが怪しいところを今回はご紹介したいと思います。

Power BI Excel アドインと Power BI Desktop、Power BI サイト の関係

Power BI まわりの Power なんちゃら、、、については本ブログの以下の投稿でまとめています。

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

実際のところ、Power BI Desktop が目指しているところは、脱 Excel だと言われても仕方ないでしょうね。
Excel を使わないで、Power BI Excel アドインで実現していたことを Power BI Desktop のみでやろうとしています。

Power BI Excel アドイン	Power BI Desktop
Power Query	データ
Power Pivot	リレーションシップ
Power View	レポート

「慣れ」の問題から、私自身、どうしても Power BI Desktop でデータ分析するよりも、Excel でアドインや Pivot を使ってデータ分析するほうが楽なのですが、やはり「慣れ」の問題なので、慣れてしまえば、「やれることは一緒」となるでしょう。

また、Excel にデータを格納していく、追加していく、そして、その Excel ブックをデータソースとして使う、という状況であれば、Excel 2016 で追加された「発行」機能を使って、OneDrive にブックを保存して、Power BI で利用する、という使い方はあります。

 

SharePoint Online や SQL Azure にデータ接続をし、外部データ接続からデータ分析をする、そしてそれを共有・公開するのであれば、Excel を使う必要性はなく、Power BI Desktop のみで「レポート」まで作ってしまったほうが、分析作業の一貫性があります。

というのも、Power BI の「レポート」に対応する Power BI Excel アドインの「Power View」の扱いが、当初より明らかにトーンダウンしているように見え、改善されるように思えないからです。

・ Excel 2016 で Power View がリボンからはずされた

手作業で追加すれば、Power View レポート コマンドの追加は可能ですが、基本は「はずれた」ということです。
この件については、以下にてマイクロソフトから紹介されています。

Windows 版の Excel 2016 で Power View を有効にする　(support.office.com)

・ Power View レポートは Silverlight が標準

よって、Windows 10 で Power BI サイトを開くと、以下のメッセージとなります。残念ながら Edge での Silverlight のサポートはありません。

ということは、タブレットなどで Power BI の結果を表示して、、、といったワークスタイルの「本質」ではない、といえますよね。。。（Windows 10 でも IE + Silverlight であれば問題なく表示されます）

・ HTML版レポートはいまだまともに動いていない

Silverlight レポートの右下に以下のようなアイコンがあるのですが、いままでこれをクリックして同じようなレポート見たことはまだありません。

この状況から、どうも Excel アドインへの力の入れ具合は Power BI Desktop に比べると弱いな、、、と思わざるを得ません。

今後はどうなる？

分析は「慣れ」といいましたが、ピボットテーブルのようなツールの使い勝手はやはり Excel のほうが勝っています。

レポートとして Excel で Power View レポートを作成し、IE + Silverlight で Power BI サイトで「ダッシュボード」まで作ってしまえば、 Windows 10 Edge でも表示が可能です。
逆にこの回避策があるため、Power View レポートの HTML5化が進まないのかもしれません。

過渡期ということで、ちょっと中途半端な実装であることは否めません。ですが、スマートフォンの Power BI アプリでキーとなるデータをすぐに確認できたり、タブレットで対話的にレポートを確認できることは、自分たちのビジネスでも欲しい機能です。

細かい分析は今まで通り Excel で行い、共有するデータが決まれば、それを Power BI に発行して、IE+Silverlight でダッシュボードを作成して、グループ共有する、というのが流れかと思います。

あと、これは個人的な感想ですが、Power BI を使い、データモデルを Excel ブックに追加していくと、それほど「ビックデータ」でなくてもメモリ不足に陥ることがあるようです。この場合、32ビット版の Excel ではなく、64ビット版の Excel を使うことで回避できることが多いです。64ビット版の Excel の必要性はこれまであまり感じていませんでしたが、データモデルを使うパターンでは 64ビット版 Excel の存在価値が高くなりそうですね。

データモデルについては本ブログの以下の投稿を参照ください。

リレーションシップとデータモデル

Read More

Excel でゼロ パディングしたい – 0で埋める

一般にプログラミングの世界などで、足りない桁数分「0」を追加して長さを合わせることを「ゼロ パディング」と言います。
Excel では 0001 が 1 になる、といった「ゼロがなくなる」ことがよく話題になります。これをゼロ サプレスといいますが、あまりこちらの言葉は聞きませんね。
今回は Excel でのゼロ パディングの3つのポイントをご紹介します。

文字列設定で反映

0001 を 1 ではなく 0001 としてセルで扱うとなると、いくつかの方法があり、そのひとつは、入力されたデータを文字列として扱う方法です。
そのため、セルの書式を「文字列」に設定することが「第1のポイント」です。

• 設定したいセル・範囲を選択する
• Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「文字列」を選択して [OK]

これで、 0001 と入力しても、1 になることはありません。シングルコーテーションを 0001 の前に入力する方法も紹介されることが多いのですが、セル書式設定本来の機能からすると、入力するセルの位置・範囲が決まっているのであれば、セルの書式設定による文字列の設定が基本です。（シングルコーテーションによる文字列入力については Office TANAKA のこちらの記事を参照ください。)

表示形式で設定

ですが、「ゼロ パディング」という意味は、桁数が決まっていて、足りない分だけ 0 を入れて長さを合わせることです。ですから、例えば 6 桁でゼロ パディングしてほしい場合は、1 と入力したら 000001 であり、342 と入力したら 000342 になってほしいこともあるわけです。
この場合は数値を入力しても「見た目を変えてあげる」と考えます。表示形式で「円」や「人」といった単位を加えても数値として扱うことができますが、これと同じ考え方です。「ユーザー定義」の「種類(T)」で「000000」と設定します。すると、この表示形式が設定されたセルに数値を入力すると、指定した6桁に足りない分を 0 で埋めてくれます。この表示形式を使うのが「第2のポイント」です。

• 設定したいセル・範囲を選択する
• Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「ユーザー定義」を選択する
• 「種類」でゼロ パディングしたい桁数の 0 を入れる

文字列設定と違い、このセルに入力されているデータは「数値」となります。見た目は 0001 などですが、四則演算可能です。

TEXT関数でパディング

数値を数値として扱うのであれば、わざわざゼロ パディングする必要はないでしょう。たとえば社員番号のような E00001 のようなデータを作りたい場合、E と ゼロ パディングした数値をつなげるパターンになりますよね。
上述の表示形式は計算可能ですから、連番や何らかの演算で数値の計算が可能で、見た目はゼロ パディングすることが可能ですが、文字列 “E” などと組み合わせると、表示形式で設定されているゼロは消えてしまいます。

これを避けるためには TEXT関数を使ってゼロ パディングされた表示形式を使って数値を文字列にします。これが「第3のポイント」です。
=TEXT(セル、”00000”)

ちなみに、TEXT関数や表示形式で使った ”00000” の 0 は数字のゼロではなく、書式記号としての 0 です。
https://support.microsoft.com/ja-jp/kb/883199
なので “99999” としたところで 9 がパディングされることはありません。
また、小数点以下のゼロ パディングもあります。123.000 のようなものです。書式設定の [数値] で [小数点以下の桁数] を指定しますが、書式記号で書けば、ユーザー定義書式で小数点以下3桁は #.000 もしくは .000 となります。
ちなみに、#,##0 と #,### では同じ三桁カンマでも、0 の場合の表示が変わります。前者は 0 を表示、後者は何も表示しない、となります。

Format 関数を使ったゼロ パディング

VBA では TEXT関数同様に Format 関数を使ってゼロ パディングをすることができます。ゼロ パディングされた文字列をMsgBoxを使って表示可能です。
 
ゼロ パディングはできるのですが、結果的に、このゼロ パディングされた「文字列」の “0001” をワークシートのセルに入力した時点で、0001 –> 1 になります。
よって、事前にセルの書式設定をしておくか、VBA からセルの書式設定が必要になります。
セルを文字列の書式に設定してゼロ パディングするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “@”
Range(“A1”).Value = Format(buf,”00000”)
数値のまま、表示形式でゼロ パディングのユーザー定義書式にするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “00000”
Range(“A1”).Value = buf
になります。

Power Query でゼロ パディング

パディングをする関数は Text.PadStart です。
Text.PadStart
https://msdn.microsoft.com/en-us/library/mt260573.aspx
そのため、数値を文字列に変更します。Number.ToText を使って、数値を文字列にして、6桁、”0” から始まる、とします。
似たような関数として Text.PadEnd もあります。これは、足りない桁の後ろを指定した文字で埋めます。
Text.PadEnd
https://msdn.microsoft.com/en-us/library/mt260477.aspx

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

Read More

Excel 2016 と Power Query (取得と変換)

Excel 2016 の 「取得と変換」 は Power Query

このブログで紹介してきた Power BI Excel アドインの Power Query ですが、Excel 2016 の「データ」タブの「取得と変換」グループにあるリボンコマンドは、Excel 2013まで Power Query アドインとして提供されていたものが Excel 2016 の標準機能になったものです。

Power Query や Power Pivot などの機能は「Power BI」ブランドの仲間としてマイクロソフトから様々な形で提供されていますが、Excel 2016 のトピックとしては、これまでアドインだったり、Homeなどのエディションで利用不可能だった Power Query が標準機能の「取得と変換」となって Excel に組み込まれたことがあげられます。

このため、似たような外部からのデータの取り込み機能が「データ」タブのすぐ近くにあったり、「Power Pivot」の機能でもデータの取り込み機能があったり、ちょっと複雑な状況になりました。

これまでのマイクロソフトの Excel の機能拡張から考えると、どれかの機能がばっさり「切り捨てられる」ことはないと考えられますが、その中でも機能拡張の優先順位がつけられ、あるものは引き続き機能拡張され、あるものは機能拡張されずに機能として残りながらもそのうちリボンから消え、ユーザーの目の届かないところに位置づけられていく、ということが考えられます。

その意味で、外部とのデータ連携、外部からのデータ取得といったエリアでは [データ] タブにある [取得と変換] のリボンコマンドが今後の主流になる可能性が高いことが予想されます。多種多様なデータソースにつながることももちろん重要ですが、この「取得と変換」は他にはない特徴があります。それはクラウド (Office 365) を中心とした「データの取得方法(クエリ)の共有」と「取得したデータの公開」が可能なことです。

 

外部データの取り込みの置き換えではない「取得と変換」

そして、上述したクエリやデータの共有や公開という「拡張機能」も重要ですが、「基本機能」そのものも強化されています。共有などを考えずに、ただ単に Excel の外にあるデータをワークシートに取り込む、という機能を見ても「取得と変換」(Power Query)は強力です。

1) 閉じているブックからデータを取得したい

他のブックのデータを Excel で開かずに取り込むは「鉄板ネタ」でしょう。
これができるからといって、Excel 4.0 のマクロを今の時代に使うのは辞めにしませんか。またはそのような情報をありがたく(使ってもいないのに)紹介するのも、もうどうかと思います。

取得と変換 (Power Query)の強力な機能のひとつは「他で保管されている Excel ブックからデータを取得」することができる、です。

それも単純にすべてのデータをワークシートに取り込むのではなく、必要なデータを絞り込んで、変換（文字→数値、シリアル値・日付の表示形式の設定など）し、ワークシートに取り込むことができます。上級者であればワークシートにデータを取り込まず、データモデル・データ接続のみで外部の Excel ブックのデータをピボットテーブルのソースとして利用することもできます。

ネットワーク上にある Excel ブックを対象にできます。\\サーバー名\共有フォルダ\　で表現されるファイルサーバー上の Excel ブックはもちろん、Office 365 の OneDrive for Business や、SharePoint ドキュメント ライブラリー内の Excel ブックも利用可能です。

取得と変換(Power Query)によるデータ取得は「クエリ」をベースにしているため、「接続(クエリ)の更新」をすることで、最新のデータに更新されます。
そのため、一度取得する場所や条件、変換の設定をしてしまえば、あとは「更新」をするだけで最新のデータを利用することができます。

この例は以下ですでに紹介しています。

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


2) 指定したフォルダー内のすべてのファイルからデータを取り込みたい

「ファイルから」のサブメニューの「フォルダーから」は、フォルダにどんなファイルがあるか、を調べる機能だけではありません。

フォルダだけを指定した場合のクエリ結果

これも Excel VBA での「鉄板ネタ」になりますが、指定したフォルダー内にある CSV ファイルや Excel ブックを「すべて」読み込む機能をプログラミングなしに実現することができます。
この機能が強力なのは、一度設定してしまえば、ファイルが増えても OK、サブフォルダーが増えても OK という点です。もちろん、基本的にすべてのファイルを参照することができるので、そこから「ある名前の規則で絞り込む」ということもオートフィルターの設定同様容易に可能になります。

ポイントは Excel の中のデータを取り出す「列」を追加してやることです。その追加する列で使われる関数が Excel.Workbook 関数になります。

当たり前ですが、取り出す先の Excel ブックのワークシートは同じフォーマット、もしくはその構造をあらかじめ理解していて、そこから何を取り出したいかは決まっているものとします。
取り出し先のデータは「テーブル」形式でなくても大丈夫ですが、テーブルであれば「ヘッダー」と「データ」を自動識別してくれます。テーブルでない「範囲」の場合は明示的に指示する必要がでますが、難しい作業・指定ではありません。とはいえ、テーブル形式は今後は必須になっていくので、できればこのタイミングでテーブル形式を使うようにすれば幸せになれるでしょう。

ざっくりと手順を紹介します。

まず、対象となるブックが入っているフォルダーを指定します。

指定したフォルダーから該当するファイルを絞ります。この例では “Book” から始まっている名前の Excel ブック「だけ」に絞り込みます。
この絞り込みの設定手順も Excel のオートフィルターで慣れた手法になります。

絞り込んだファイルから Excel.Workbook 関数を使って、ブックの中のデータ情報を抜き出す「列」を追加します。
ブックそれぞれのデータ情報は [Content] のバイナリデータから Excel.Workbook 関数を使って参照することができます。
そのため、バイナリデータの [Content] 以外の列は不要なので、削除してから新規列を追加します。
ただし、この段階ではまだ「セル」や「テーブル」のデータそのものを参照しているわけではありません。

それぞれのブックにはテーブル形式でデータが入っていますが、 Power Query からは「テーブル形式」のデータと「シート」の範囲データの2つを認識しています。
今回使うのは「テーブル形式」のデータなので、「テーブル形式」のみのデータに絞り込み、不要な列を削除し、テーブルのデータを列として展開します。
テーブル形式のデータを利用するため、列ヘッダーとデータ部分を明示的に区別・指定する必要はありません。

このクエリの結果をワークシートに読み込みます。ワークシートに読み込んだときに「日付」がシリアル値のままで読み込まれるので、この表示形式の設定をクエリ エディタで行います。あわせて日付で昇順ソートの設定も行います。

ここで作成したクエリの情報は「ブック」に保存されています。このブックを開いて、「データ」タブの「接続」の「すべて更新」または「更新」や、「クエリ」ツールの「クエリ」タブの「読み込み」の「更新」、さらに結果テーブル上のコンテキストメニュー（右クリックメニュー）の「更新」によって、最新のデータ、最新のファイルをクエリで指定しているフォルダーから読み込んで、テーブルのデータを更新します。

Excel Online と Excel データの共有

Excel Services という機能が SharePoint Serer 2016 で無くなる、というリリースがありました。ですが、Office 365 / SharePoint Online 利用者にはほとんど影響ありません。
MOSS2007などの昔、Excel Services は SharePoint 上で Excel ブックを表示させる、という目的の機能でしたが、今の SharePoint ポータル上での Excel ブックの共有には Office Web Apps/Excel Online の Excel Web Access が使われ、Excel Services は使われていないからです。

Technet: SharePoint Server 2016 IT Preview で使用されなくなった機能と削除された機能
Technet: SharePoint Server 2016 IT Preview の既知の問題

そのため現状、Excel ブックの共有という意味では、SharePoint Onoine の例から見ても Excel Online が主流になってきているといえるでしょう。
さらに Power BI サービスと OneDrive との組み合わせにより、Excel Online を使った共有機能がさらに拡張されてきています。

つまり、田中メソッドでいうところの「入力・計算・出力」の「出力」の部分の大きな変革が始まっています。

いままでは「印刷」する、もしくはブックを「コピーして配布」するが主でしたが、今後は

• OneDrive にアップしたブックのリンクで Excel ブックを共有する
• SharePoint サイトのポータルページに埋め込み共有する
• Power BI サービスを使って、ダッシュボードとして共有する
• Power BI サービスを使って、最適化されたアプリでデータを閲覧する、させる

といった Excel Online をベースにした「出力」方法が主流になっていくと考えられます。

Power BI については、こちらのブログ記事も参照にしてもらえればと思います。

 

クエリの共有

実際に現場でデータを分析したい、データの傾向をみたい人が、必要なデータへのアクセス権限をすべて持っている、または、その権限を持っている人が現状を踏まえて分析できる、ということは稀でしょう。
規模が大きくなくても、パッケージやアプリケーションに組み込まれているデータは「正規化」され、複数のファイル、テーブルに分割されていることが多く、そのリレーションをすぐに把握できるものではないでしょう。

Power Query や Power Pivot を駆使して、正規化された複数のテーブルから必要なリレーションシップを基にして分析元のリストを作成することに労力をかけるよりも、必要であろうデータを収集できるクエリがすでにあり、そこから必要なデータを絞り込む、もしくは、いくつか用意されたクエリを結合して必要なデータを作り出し、傾向を把握する、分析することに時間をかけたほうがいいと考えるでしょう。

Power BI では「データ カタログ」としてクエリの共有機能を提供しています。データ カタログにクエリを登録、それを共有することによって、エンドユーザーがクエリをゼロから作らずに、データ管理をしている担当者がクエリを作成し、エンドユーザーに近い分析する部門がピボットテーブルを活用してデータの傾向を見る、といった活用が可能になります。

support.office.com Power BI データ カタログのクエリを共有する

 

パーソナルツールとしての Excel 機能拡張の限界

このようなことから、少なくとも今後の Excel の機能拡張の方向は Office 365 といったクラウドの進む方向と一緒だと考えるのが妥当でしょう。
そうすると、Excel を使いこなしたいユーザーにとって、以下を「知りません、得意じゃないんです」と言ってられなくなっています。

• Office 365、SharePoint、各種データソースへのアクセス権限についての理解
• 自分が共有したいブックの、他の人へのアクセス権設定

Excel ユーザーの方とお話をしていると「サーバー」や「アクセス権」、「認証」という言葉を聞いただけで拒否反応する人も少なくないのですが、それらの管理や設定を自分たちがすることがなくても、システム部門やデータ管理の担当者に適切な権限をください、もしくは組織内のデータを活用するために設定してほしい、というリクエストを出す、常々話をするくらいの考えは持ったほうがよいでしょうね。

また、管理者側からすれば、すべてをエンドユーザーに任せるのではなく、あらかじめ適切に権限設定された共有クエリを用意することで、必要なデータに対して必要な権限でアクセスさせることができます。そのような設定が管理者側で可能であることをエンドユーザーも知っておくべきでしょうね。

もう、一人で Excel ブックをいじってそれらのコピーを配布する時代の先行きは長くないようですから。

 

その他の参考情報

Power Query のクエリ編集についてはマイクロソフトからまとまった情報が出ているようには見えませんが、根気強く探すと出てきます。

キーワードは「M言語」ですが、検索するときは “M Language” で探しましょう。 “Power Query M Language” あたりのキーワードで msdn などの公式技術文書にいきつきます。

msdn: Power Query (informally known as “M”) Formula reference
https://msdn.microsoft.com/en-us/library/mt211003.aspx

そのほか、サイトに support.office.com を指定して Power Query のキーワード (& 1041 日本語指定）で検索するのも有効でしょう。

https://support.office.com/ja-JP/search/results?query=Power%20Query&lcid=1041

また、MSMVP の kagata さんが以下の方法による Power Query 関数のヘルプの確認方法を紹介しています。

http://mukkumuku.blogspot.jp/2015/10/power-bi-10-power-query-reference.html

最近では Power BI の新しい自習書が日本マイクロソフトさんから出ました。しかし、それは Excel の範疇では検索できません。
以下のリンク ページの「Power BI 自習書」の「Word版ダウンロード」や「PDF版ダウンロード」から自習書のデータをダウンロードしてください。

BI/Data Analytics – microsoft.com/ja-jp/ : Power BI 自習書

この自習書を作成した SQLQuality の松本さんのブログがこちらになります。

http://www.sqlquality.com/column/column_50.html

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

SharePoint ドキュメントライブラリーの Excel ブックが開けない

[ご注意] 結論はそれかよ！という内容なのであまり期待しないでください。とはいえ1日悩みました。。。

ある日突然 SharePoint Online のドキュメントライブラリーに保存している Excel ブックを開くことができなくなりました。

現象としては

• Excel Online では開ける、ただし、[ブックの編集] – [Excel で編集] を選択すると開けない。
• ブックのダウンロードはできる、ダウンロードしたブックを開くことはできる。
• [ファイルを開く] ダイアログからドキュメントライブラリーの URL を指定して、Excel ブックを選択しても開けない。（ライブラリーにブックがあることは確認できる）

開けないときのダイアログのメッセージは以下。

手がかりないですよね、このダイアログだと。。。

そもそも Excel Online で参照・編集できますし、ダウンロードも可能ということからアクセス権の問題ではなさそうです。というか、今まで使っていましたし、ブックの作成者は私です。
ダウンロードしたブックを開くことはできるので、ブックが壊れていることもなさそうです。

なんと、そのサイトのみならず、他のサイトの他の Excel ブックも Excel から直接開くことができなくなっていました。

さらに、いろいろ試してみると、追加の現象として以下であることがわかりました。

• 新規ブックを作成、ローカルに一度保存して、SharePoint のドキュメントライブラリーにアップロードは可能。ただし、アップロードしたブックを直接 Excel で開くことはできない
• 新規ブックを作成し、直接 SharePoint のドキュメントライブラリーに保存しようとすると、「ファイルを保存できませんでした。」とメッセージがでて保存できない。

つまり、Excel から直接 SharePoint 上にあるブックを使うことができないと想定されます。

使用している Excel のバージョンは以下。

Microsoft Office Professional Plus 2013
Microsoft Excel 2013 (15.0.4737.1000) MSO(15.0.4737,1001) 32 ビット

もし、これがバグとすれば大問題になっているような内容です。検索してみると以下がヒットしましたが、エラーの内容やビルド番号から今利用しているものは問題がなさそうです。

SharePoint Online または SharePoint Server で Excel ファイルを開こうとするとエラーが発生する(KB2919156)
https://support.microsoft.com/ja-jp/kb/2919156

ここ数日で、なんらかの Microsoft Update はあったかもしれませんが、Windows や Office のバージョンを変えるようなことはしていません。

こういうときは90年代から「再起動せよ」が鉄則です。（笑　そこですべてのアプリケーションを閉じ、Windows　の再起動をしたところ、問題なく Excel から SharePoint のブックを開くことができるようになりました。

未だにこの方法が有効だったとは。。。

使用している Windows は 8.1 の 64bit版、Office は 32bit版、PCのメモリーは 12GB です。
再起動、、、してみるのも手かもしれません。

Read More

シート別にデータを分ける – VBA/マクロとピボットテーブルの活用

実践ワークシート協会の通常業務では、なるべく「元データ」は Office 365 の SharePoint か、SharePoint Access アプリを経由して SQL Azure に入力して、クラウド上でまとめるようにしています。

生のデータがクラウド上にあれば、あとは Excel のデータ接続を使って生データをワークシートに取り込み、自分の欲しい形に加工すればよいわけです。おおよそオートフィルターかピボットテーブルで目的は達成でき、ちょっと複雑（複数テーブルを使う、列を入れ替える、削除するなど）になれば、Power Query や Power Pivot を使います。

(Office 365 SharePoint Online のリストアイテムの取り込みは OData フィードを使うと幸せになれることが多い。くわしくはこちらを参照)

定型の業務であれば、一度つくったピボットテーブルや表を Excel Services の Excel Web Access を使って SharePoint Online のポータルに貼り付けておけば、ローカル(PC)で Excel を開くことなく状況の確認が可能で、協会のメンバーと情報を共有できます。

Excel ユーザーのための Excel Services – Office 365

強いて言えば、協会のメンバーは全員が Excel を使いこなすことができるので、準備したデータを「あ～したい」、「こ～したい」、と思ったら自分で加工できるのが大きな強みでしょう。

このように協会ではピボットテーブルやオートフィルターを活用・多用するので、最新データは「データ更新」ボタンを押してクラウド上のデータを Excel で確認するか、条件が変われば自分でピボットテーブルやオートフィルターを変更するわけですが、一般的にはそうではなく、日次や月次などで「決まった形」でワークシートを用意する、印刷して提出しなければならないケースを聞くことが多いです。（繰り返しになりますが、協会内部の業務ではこのようなケースはありません。）

例えば、支店別の売り上げとか、製品別の売り上げとか、年月での売り上げでまとめたワークシート。[東京支店][大阪支店][名古屋支店] といったワークシートが並んでいたり、[2015年4月][2015年5月][2015年6月] というようなワークシートが並んでいるブックです。

比較・確認する数字などは定例の会議で確認する項目などはほとんど決まっているのでピボットテーブルを駆使した方が最新データへの更新も考えると楽なのに、、、と思うこともしばしばありますが、もうそうやって何年も十何年もその資料を使っていると、なかなか変更するのも大変です。（変更したほうがいいんですけどね）

だったら、その作り方で楽をしよう、というのが今回のテーマです。Excel VBA と ピボットテーブル の話になります。

グループ化と明細の表示を使う

Excel のピボットテーブルには「明細の表示」という機能があります。通常はピボットテーブルによる集計済みの項目の集計元データを、別のワークシートに作成し内容を確認するためのものです。

たとえば、日付項目を「年・月」でグループ化したピボットテーブルを作り、年月の集計値をダブルクリックすると、その年のその月のデータだけが「明細の表示」としてワークシートが追加されます。

クラウドやサーバーからデータを Excel に取り込み、年・月のシートに分割する、というような定型の業務がある場合は、オートフィルターを使ってコピーする方法より、このピボットテーブルと明細の表示を使うほうがあるかに手間がかかりません。そして、この手作業を VBA で自動化すれば、一瞬で年月ワークシートを作成することができます。元データは SharePoint や SQL Azure といったサーバーから取ってきますので、その時点での最新データから年月ワークシートを作成することが可能です。

グループ化は日付だけではなく、支店名や、製品グループなどの文字列データでも有効です。ピボットテーブルのグループ化の機能が強力なのは、あらかじめどんなユニークデータがあるかを知っている必要がないことです。この性質を利用して「重複データの削除」のような使い方も可能です。

重複したデータをチェックする - ピボットテーブルの応用

マクロ / VBA でピボットテーブルを作る

実践ワークシート協会代表理事/Office TANAKA の中の人 田中亨曰く、 マクロ記録で恐ろしい(難解な)結果が出る代表格の１つであるピボットテーブルの作成ですが、実は、非常にシンプルなコードでまとめることができます。このあたりは田中亨著の「Excel VBA 逆引き辞典」にシンプルなサンプルコードが紹介されています。

1回かぎりであれば手作業で十分ですが、毎回毎回同じことを繰り返す、作成するシートの数が多いようであれば、VBA を用意してそれを実行するだけで、ある条件でワークシートを作成することを自動化可能になります。

手順としては大きく4つです。

1. 元の表(テーブル)から条件を設定してピボットキャッシュ、ピボットテーブルを作成する
2. ピボットテーブルから詳細の表示を使って詳細ワークシートを作成
3. 作成したワークシートにあるテーブルを並べ替え
4. 作成したシートの名前を変更

それぞれの手順を見てみましょう。

例として以下のようなテーブルから年月別のワークシートを複数作成します。(サンプルは 10,000件）

1. 元のテーブルから条件を設定してピボットテーブルを作成する

Sub CreatePivotTable()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'Dからピボットキャッシュとテーブルの作成
   With nWS.PivotTables(1)  'ピボットテーブルのフィールドリスト操作を以下で行う
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。マクロ記録で調べるとよい。
End Sub

実行結果は以下のようになります。

ピボットテーブルから年月シートを VBA/マクロで作成する

ピボットテーブルができてしまえば、あとは [詳細の表示] に相当するコードでワークシートを追加していきます。大まかな手順は以下になります。

2. ピボットテーブルから[詳細の表示]を使って、各月のワークシートを作成する

3. 作成したワークシートを日付でソートする

4. シートの名前を年月にする

5. 最後にピボットテーブルのシートを消す

ここは一気にいきます。

上記ピボットテーブルを作成したソースに 2, 3, 4, 5 を実現するコードを加えたものが以下です。

Sub CreateMonthlySheets()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'ピボットキャッシュとnWSシートにピボットテーブルを作成
   With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定。各年月シート作成のためのフィールドで「金額」そのものには意味はない。
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。設定はマクロ記録で調べるとよい。
   Set D = nWS.PivotTables(1).DataBodyRange  'ピボットテーブルの値の範囲（金額集計）を D とする
   Dim Item As Range
   For Each Item In D  '金額集計の範囲の数だけ For Each を回す。個々のセル(Range)は Item に入る
       Item.ShowDetail = True　　'詳細の表示を行う。このとき追加されたシートがアクティブシートになる
       With ActiveSheet.Sort.SortFields　'追加されたシート(アクティブシート）で日付(A2)のソート条件を設定する
            .Clear  'おまじない
            'A2の日付の値で、昇順(xlAscending)、標準(xlSortNormal) でソートする条件を設定する
            .Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       End With
       With ActiveSheet.Sort　'アクティブシートの Sort オブジェクトでソートを実行する
            .SetRange Range("A1").CurrentRegion　'A1を含むデータのかたまり（範囲）を対象とし
            .Header = xlYes　'一番上の行はヘッダーとして
            .Orientation = xlTopToBottom　'上から下に向かって
            .Apply　'ソートを実行
       End With
       'セル A2 の日付から年月の文字列を作成し、シートの名前にする
       ActiveSheet.Name = Format(ActiveSheet.Range("A2"), "yyyy") & "年" & Format(ActiveSheet.Range("A2"), "m") & "月"
    Next Item
    Application.DisplayAlerts = False　'ピボットテーブルのシートを消す際のアラート表示をオフにする
    nWS.Delete　'ピボットテーブルのシートを消す
    Application.DisplayAlerts = True　'アラート表示を元に戻す
End Sub

10,000件程度であれば、数秒で年月別シートの作成が可能です。

最近は Power Query や Power Pivot といった機能の追加・拡張が Excel では多いですが、ワークシートを消す、新規に作る、追加するといった作業は VBA でしかできません。
そういった作業の自動化の要望がある限りは VBA がなくなることはないでしょうね。

ただ、ちょっとだけ「手間」を入れると Power Query の機能だけでも上記のことが可能になります。
機会があれば、Power Query で「年月別のシートを作る」手順をご紹介したいと思います。

Read More

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 へのアクセスは成功していません。([*1] 2022年追記 URLを編集して可能になっています）OneDrive for Business は Office 365 で提供されている OneDrive です。これは SharePoint をベースにしています）

取り込み手順

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

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

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

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

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

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

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

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

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

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

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

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

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

何がうれしいの？

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

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

アンケート集計が溜まってくると、ピボットテーブルを使って分析したくなるのですが、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/

[*1] OneDrive パーソナル (onederive.live.com) の Excel ファイルに接続する方法
https://road2cloudoffice.blogspot.com/2022/04/power-query-onedrive-onederivelivecom.html

Read More