ExcelのPower Queryで複数セルの内容を引数として拾ってくる方法

*2019年9月 Toiさんのアドバイスで、引数をまとめたバージョンにしました。ありがとうございます。

ExcelのPower Queryとは

Excel 2013のプラグインとして提供され、Excel 2016から標準装備になったデータの整理機能です。

SQLデータベースや複数フォルダに格納されているデータファイルを必要なものだけ抽出してExcelへ出力することができます。

今のIoT時代で流行っているビッグデータ。
貯めただけだと腐るので、こういうツールできれいに整理する必要があるわけです。

Power Queryのクセが強い

このPower Query、Excelとはまた別の独特のインターフェイスを持っています。

簡単に使えると思って開くと下記のようなセルがいじれない謎の画面が出て面食らいますが、我慢して使いこなせるようになると非常に便利です。

試しに自販連の10月の新車販売台数ファイルを読み込んで、前年比などいらない列以外を削除(他の列を削除ってやつです)すると下記のような感じになります。

で、出来たらファイル→閉じて次に読み込む→接続のみで保存しましょう。

普通の使い方

使い方としては下記のような条件が考えられます。

• 月5000台以上販売した車だけ抽出する
• 月1位～10位までの車を抽出する
• 特定メーカーの車の販売台数だけ抽出する

この接続クエリからピボットテーブルを作成し、上記の条件を元にデータを絞ってグラフを作成することが出来ます。

• 50位まで全部表示した場合

• 10位まで表示した場合

タイムラインやスライサーなどで条件を絞って動的にグラフを作ることができるので便利です。
が、しかし！

ぶち当たる問題

その人が見たい情報を得るためには、ピボットテーブルやスライサーを使って色々とデータをいじる必要があるので、これで作って渡してもピボットテーブルを使いこなせない人相手だと往々にして放置されがちです。

ということで、シートの使い手にピボットテーブルを意識させず、条件を直接指定させたい場合があります。

普通のExcelの感覚から考えると、関数と同じようにExcelのセルに指定させたい条件を入れてそのセルをPower Query上で指定すれば……と思いますがそうは簡単に行かないのです。Power QueryとExcelのセルは直接リンクできないのです。

この場合どうするか、個人的に考えた方法を紹介します。

まずクエリを作る

ある台数の間の車だけ抽出する、というクエリの場合、もう最初にある台数で決め打ちして作成します。
3000～6000台/月で絞ります。

数字を変数化する

詳細エディターを開くと、こういうクエリ文が出てきます。先程指定した数字があるので変数に書き換えます。

こんな感じでdaisuu1、daisuu2を数字型で宣言します。そして、3000、6000の部分を変数で置き換えます。

しかし、これをやるとクエリが関数化されます。ビジュアルエディターで対応できないようで、クエリを右クリックで編集→詳細エディターでコードを直接編集するしかなくなります。

ソースコード

(daisuu1 as number, daisuu2 as number) =>

let
ソース = Excel.Workbook(File.Contents("E:\さびなーる\powerwuery\201810.xlsx*ここは変えてください"), null, true),
Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"順位", Int64.Type}, {"ブランド通称名", type text}, {"ブランド名", type text}, {"台数", Int64.Type}, {"前年比", type any}}),
削除された他の列 = Table.SelectColumns(変更された型,{"順位", "ブランド通称名", "ブランド名", "台数"}),
フィルターされた行 = Table.SelectRows(削除された他の列, each [台数] >= daisuu1 and [台数] <= daisuu2)
in
フィルターされた行

入力部を作る

下記の感じで、条件指定したいセルを作ります。

それを作業用別シート(を作ったほうがいい)でテーブルに変換します。

今回は引数が下限台数と上限台数なので、その2つを含んだ1つのテーブルを作ります。

テーブルを引数として読み込むクエリを作る

で、このテーブル・下限台数からまたクエリを作ります。この場合、テーブルまたは範囲からを選んでクエリを作成します。

ファイル→カスタム関数の呼び出しで”台数で切り分け”クエリを呼び出します。

まずdaisuu1の指定が出るので、テーブルの下限台数を指定します。

続いてdaisuu2の指定が出るので、テーブルの上限台数を指定します。

これで全ての引数を読み込むことができたので、テーブルを読み込むことができるようになります。

 

下記のように1000台～4000台の車が抜き出されます。

後はこれをグラフにします。

ソースコード

let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"下限台数", Int64.Type}}),
呼び出されたカスタム関数 = Table.AddColumn(変更された型, "台数で切り分け", each 台数で切り分け([下限台数], [上限台数])),
台数で切り分け1 = 呼び出されたカスタム関数{0}[台数で切り分け]
in
台数で切り分け1

使い方

台数下限、台数上限を指定して、更新ボタンを押せばグラフが変わるという例です。

更新ボタンには、台数を指定するごとにクエリの読み込み直しをするVBAコードを書き加えてあります。

11月、4000～1000台販売した車を降順で表示

11月、1500～500台販売した車を降順で表示

こういう風にしておけば、クエリやピボットに詳しくない人でも簡単に扱えるファイルが出来ます。

うまくデータが降順になってくれない時は

値データの台数を元に昇順/降順にしたいのに、行ラベルの車名でソートされてしまうときがよくあります。

そういう場合はピボットテーブルの行ラベルの右下矢印をクリック→その他のフィルタオプションをクリック。

値データにしている要素を元に並べ替えができます。

今回は車の月販台数でしたが、データベースに時々刻々とデータが自動格納される製品生産数、設備異常内容などをサマリーするときに便利だと思います。その場合は時間帯、異常件名などで区切ることになると思います。

ということで、突然のExcelのPower QueryのTIPSでした。

数少ないPower Queryの型・関数のリファレンスはこちら参照。

その他のOfficeスイートのTIPSはこちら

404 NOT FOUND | さびなーる

さびなーる　デジタルガジェット、クルマ、バイクが主なネタです

makotsu80.com

パソコン修理の【ハレルヤ】