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 の松本さんのブログがこちらになります。
