しかし、マイクロソフトの SQL Server を導入していなければそれほど気にする Excel ユーザーはいないと思われる。使うとしても Access データベースが多かったのではないだろうか。(Web クエリも使えそうに思うのだが、それほど活用できるシーンは多くないのが実状だろう）
クラウド サービスが台頭するに従い、参照するデータが社内のサーバー（これを最近はクラウドと対比して オンプレミス と呼ぶ）だけではなくなり、また、そのデータ接続の形態も多様になってきた。
そのような流れのなかで Power Query と呼ばれる Excel アドオンがマイクロソフトのダウンロードセンターから無料で利用可能になった。注意点としては、システム要件で Excel のエディションは Professional Plus, 365 Pro Plus の企業向けエディションに限られ、Home and Business / Solo などの個人向け Excel にはインストールすることはできない。また、インストールできたとしても、Professional Plus, Pro Plus 以外ではネット上に公開されているデータのみ参照といった制限が加えられる。
この Power Query は PowerPivot を含む Power BI と呼ばれるサービスブランドの主力製品の1つであり、これまでリボンの [データ] タブにあった [外部データの取り込み] を完全に置き換えるものである。
ところが、このブログの Office 365 SharePoint リストとの連携などで、Power Query を一度も使っていない。 Power Query を使わなくても SharePoint リストをテーブルとして Excel に取り込むことはできる。現在のところ、このあたりの機能は「移行期」であり、同様の結果をもたらす機能がいくつかあるが、明らかにマイクロソフトは Power BI を軸にして、Excel をデータ分析のツールとし、クラウドでも重要なポジションとなるように拡張している。
では、クラウド以外では Power Query は使えないのか？というとそうではない。
あえて今回の投稿のタイトルを「Excel ユーザーのための」としたのは、クラウドを使っていない環境でも Power Query の活用シーンが多いからである。
CSV データの取り込み
従来の 「テキスト ファイル ウィザード」（データ タブのテキスト ファイル）を使って CSV データを Excel に取り込んでいるユーザーも多いだろう。CSV データ ファイルはマイクロソフトが Excel に関連付けしているためダブルクリックでも取り込むことができるが、鉄板ネタの「001 が 1 になる」「1-1 が1月1日になる」といったユーザーが期待しない変換をする。このため、このウィザードは必須であった。
Power Query の CSV 取り込みでは、上記のウィザードでできることはもちろん、様々な強化がされている。
Power Query は単純にデータを取り込むだけでない。取り込む「列」の指定や列の順番の変更を[ホーム] タブの [列の管理] で指定できる。取り込む行数の削減や並べ替え、さらには列内の文字を置換したり、区切り文字を指定して列の分割も可能だ。また、[カスタム列の追加] も可能で、その列には数式により、他の列を参照したり、Power Query 関数を使って計算が可能だ。
Excel データの取り込み
さらに、ローカル PC やファイルサーバー上にある Excel ブックからデータを取り出すこともできる。それも、そのブックを Excel で開くことなしに取り込むことができるのだ。
また、ブックに複数のワークシート、そしてテーブルがある場合は、それらを判断してナビゲーション ウィンドウで選択することができる。
取り込みたいデータを選択すれば、上述の CSV で行った加工が同様に可能である。
しかし、これだけだと Power Query を使って Excel のデータを取り込むメリットが実務面ではわからないだろう。
Power Query は「クエリ」という単位で取り込む条件を記憶する。データ取り込み先の情報にはじまり、クエリ エディタで設定した条件などを保持することができる。
そのクエリを使って、さらにデータの加工が可能なのだが、特筆すべきはクエリによる結果データ（テーブル）の「追加」と「マージ」ができることだろう。
クエリによるデータ（テーブル）の追加 （Append)
実務では同じ列の表（データ）を追加するという業務も少なくない。
そのような場合、手作業で範囲をコピーして、コピー先にペーストするか、自動化するために VBA を使うなどが考えらえる。
このデータ追加の作業も Power Query を使うことで可能だ。
蓄積している表（またはテーブル）を取り込むクエリと、追加するデータを含む表（またはテーブル）を取り込むクエリを作成し、双方のクエリを「追加」結合させることで、双方のテーブルを結合した新しいテーブルを作ることができる。データベースの世界ではユニオンと呼ばれる機能である。
たとえば、過去のデータは Excel のブックのワークシートに保存されており、今期のデータは CSV や Excel ワークシートとして更新されているようなケースでは、過去のデータのクエリと、現在進行中のデータのクエリを「追加」結合させることができる。もし、列順が違う、今期から列名に修正が入った、、、などの事態が発生した場合は、クエリ エディターを使って追加結合できるように修正すればよい。
そして、この方法の最大のメリットは、一度作ってしまえば、そのクエリは再利用が可能なことである。もちろん、実務では参照先のファイル名が変わるケースもあるだろうが、もし、上書きでファイルが更新されるような場合は「データ更新」するだけで最新のデータを参照して結合することができるのだ。
結合できるクエリの数には上限はない。（扱うデータによってメモリの上限はあるはずだが。）
2つのクエリを結合するクエリは既定で「Append1」のような名前のクエリになる。さらに結合させたいクエリがあれば、この Append1 にそのクエリを使って追加結合させる。これを繰り返すことで3つ以上のクエリを結合させることができる。
クエリによるデータのマージ
かたや、マージは Excel 2013 以降に追加されたリレーションシップ、もしくは PowerPivot のリレーションと同じことができる。
さらに、PowerPivot で行った RELATED 関数を使わずに参照先の欲しい列を挿入することができる。
一般的には一度ワークシートに取り込んでから加工をするのがわかりやすいが、件数が多いときなどは、上記の追加結合やマージ結合を含むデータの加工処理のためのクエリ作成で、ワークシートに参照先の全データを取り込む必要がない。
表・テーブルの読み込み先の指定の「データを表示する方法」で「接続の作成のみ」を選択して、クエリを作ることで、全データを読み込むこと無しに、そのクエリを使って加工、追加結合、マージ結合が可能になり、そのクエリ結果だけをワークシートに書き出すことが可能だ。
そして、この操作をする上で PowerPivot のように「データモデル」に追加する必要がないのも、Excel のピボットテーブル ユーザーにとってはうれしいことだろう。
「機能」への拡張
Excel は「機能」、「関数」、「VBA」の3要素から成り立っている、というのは実践ワークシート協会 代表理事 田中 亨の主張であるが、その中でもとりわけ最近のマイクロソフトは「機能」面における拡張を推し進めている。確かに、新しいバージョンの Excel でできることは昔の Excel とそれほど差がないかもしれない。上述のようなデータの操作も、もし、VBA に精通しているユーザーが多くいれば、わざわざ新機能を使って同じことをする必要はない。
しかし、現実は、そのように VBA を使いこなせるユーザーが多いわけではない。ワークシート関数はブックのメンテナンスという観点からみると VBA 以上にやっかいなものであることを多くのユーザーが気づいていない。複雑なワークシート関数を使ったブックにはコード（数式）の一覧性がなく、そのワークシートを作った本人ですらメンテナンスができるかどうかあやしいのだ。
その状況を顧みれば、いままで VBA でやっていたこと、複雑なワークシート関数を駆使して実施していたことを「機能」だけで実現できるのは「組織にとって」大きなメリットであり、マイクロソフトはそちらのほうに向かってユーザーの利便性を高めようとしている。
そう理解して新機能、新バージョンと接することが、本当の意味で業務の効率化や、効果的な Office の活用ができると考えている。
OneDrive/SharePoint ドキュメント ライブラリーは使えない
[追記：2015年6月版 2.23.4035.242 で HTTPS 経由でのアクセスが可能になりました]
と、いつものパターンの落とし穴だが、最後にできないことを紹介しておく。
Power Query でローカルPCや LAN 上のファイルサーバーにある Excel ブックからデータを取りだすことを紹介したが、残念ながら現在の Power Query のバージョン(2014年11月版 2.17.3850.242)では https 経由で OneDrive、OneDrive for Business、さらに SharePoint のドキュメント ライブラリーにある Excel ブックからデータを取り出すことができない。
ただ、同期ツールを使ってローカル PC のエクスプローラーにある OneDrive / OneDrive for Business フォルダーから Excel ブックを利用することは可能だ。現在、OneDrive は無制限になっているのでオフライン同期するフォルダー選択には注意が必要だ。
Power Query そのものは2～3か月に1度に更新されるような頻度であり、どんどん機能拡張・追加されてきた。OneDrive や SharePoint ドキュメント ライブラリー内の Excel ブックにアクセスできる機能拡張の早急な実装を期待したいところだ。これが可能になると Excel 中心で実務を動かすユーザーにとってはさらに Office 365 の利用シーンが広がるだろう。
[参照]
(英語) Power Query formula categories (Power Query で利用できる関数一覧）
https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819
Office Online - Microsoft Power Query の概要
https://support.office.com/ja-jp/article/Microsoft-Power-Query-for-Excel-%E3%81%AE%E6%A6%82%E8%A6%81-6e92e2f4-2079-4e1f-bad5-89f6269cd605
0 コメント:
コメントを投稿