Excelの"取得と変換"(Power Query)を使ってみよう😎

冒頭から関連記事

neet-rookie.hatenablog.com

概要

人知れずExcel2010でPower Queryとして実装され、Excel2016で取得と変換に改名。

世界のExcelユーザの99.99%が利用していないと思われる"取得と変換"

私は4年前に仲良くなれました。

この機能は我々エンジニア(このブログの主な対象)、もしくは、一般企業のパワーユーザ向けです。

なぜならば、Excel上でデータベースのようにクエリ(ストアド)を発行する機能だからです。

エンドユーザにとってはピボットテーブル以上に敷居が高いです。

できること

何ができるかというと、例えば、

Excel上のマスタ(セル範囲)とデータテーブル(セル範囲)を更新し、それを保存されたクエリ(ストアド)で自動的にデータを結合・取得し、Excelシートに展開する。
様々な外部データソースからデータを取り込み、それをテーブルとして読み込むクエリ(ストアド)を作成し、フォーマットを自由に詳細に整形した上でExcelシートに展開する。クエリは保存されるため、取り込み方法を繰り返し調整でき、また、ほかのデータ(外部データソースやセル範囲)と結合して取得することもできる。

駄文

SQLになじみがある我々エンジニアは、データを見るとついSELECT FROM JOIN WHERE GROUP BYコンボをしたくなりますが、一般に知られたExcelの機能ではできませんでした。取得と変換ではSQLを書くのと同じぐらい簡単な操作で実現できます。

データを加工して新たなデータを作れるということは、それを元にピボットテーブルにすることもできます。

ピボットテーブルからグラフを起こせば一般ユーザも恩恵を受けることができるでしょう。

システム化されていない業務データ管理に活用することもでき、開発マシンがなくても事務用PCのExcelだけで、VBAも数式も使わずに高度な集計作業を行えます。

数千件程度で少ないカラムのデータの集計であれば、Excelシートへ展開まで行ってもパフォーマンスは問題ありません。数万件だと重いです。

Excelを使った小規模なデータ管理が仕事の人には最適です。取得と変換をマスター(使えるというレベルで十分)できれば業務効率は数倍にアップするはずです。

Excelファイルをデータソースに指定できるので、ファイルサーバにデータストアとして関連するデータごとにExcelファイルやCSVファイルを置いておき、普段はそこにデータを追加し、月末や年度末など集計が必要な時に、取得と変換を使ってクリック一つで集計できちゃいます。※多数の人間が触れるファイルは事故にあいやすいので、こまめにバックアップをとるかバージョン管理をしましょう。

駄文のまとめ

これまで、小規模システム(wshバッチなど)やVBA、あるいは複雑に絡み合った芸術的な数式(←皮肉)を使って実現していた作業が、Excelの取得と変換だけでできるのです。

スクショ

f:id:neet_rookie:20190628000251p:plain

f:id:neet_rookie:20190628000308p:plain

f:id:neet_rookie:20190628000319p:plain

f:id:neet_rookie:20190628000330p:plain

"取得と変換"におけるクエリとは

クエリ(ストアド)は手入力もできますが、癖が物凄く強いので、相当に慣れるまではAceessのようにマウスで作成する方がいいです。

というかAccessのイメージです。

でもAccessしか知らない人には難しいかも。

Accessのフロントエンドを完全に操作出来て、Access以外のRDBMSに対しSQLを書けるなら、問題なく取得と変換も使えます。

クエリの作成は、いくつかの加工・集計ステップに分けられています。

加工・集計ステップにはそれぞれ名前(和名で短文)が設定され、前の結果セットに対して何かをするという JavaのStringBuilder.append().append()のような RDBMSのViewを多段階に作るようなイメージで、少しずつデータが加工されていきます。

let
    ステップ名1 = データソース,
    ステップ名2 = 加工処理,
    ステップ名n・・・
in
    出力

let
    ソース = Excel.CurrentWorkbook(){[Name="M文具商会原価"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"JANコード", Int64.Type}, {"商品", type text}, {"金額", Int64.Type}})
in
    変更された型

let
    ソース = Excel.CurrentWorkbook(){[Name="T仕入れ"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type datetime}, {"JANコード", Int64.Type}, {"個数", Int64.Type}})
in
    変更された型

let
    ソース = T仕入れ,
    マージされたクエリ数 = Table.NestedJoin(ソース, {"JANコード"}, M文具商会原価, {"JANコード"}, "M文具商会原価", JoinKind.LeftOuter),
    #"展開された M文具商会原価" = Table.ExpandTableColumn(マージされたクエリ数, "M文具商会原価", {"金額"}, {"M文具商会原価.金額"}),
    挿入された乗算 = Table.AddColumn(#"展開された M文具商会原価", "合計額", each [M文具商会原価.金額] * [個数], Int64.Type)
in
    挿入された乗算