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

Windows

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の部分を変数で置き換えます。

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

入力部を作る

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

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

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

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

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

ファイル→カスタム関数の呼び出しで”台数で切り分け”クエリを呼び出すと、daisuu1の指定が出るので、テーブルの下限台数を指定します。

その後出てくる台数で切り分け列のfunctionを押すとdaisuu2の指定が出てきますが、もう指定できないのでこのまま閉じます。

続いて、上限台数のテーブルを指定し、カスタム関数の呼び出しで先程daisuu1を読み込んだクエリを指定します。

そうすると、今度はうまいことdaisuu2の指定が出てくるので、このテーブルの数字である上限台数を読み込みます。

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

これで、下記のように1000台~4000台の車が抜き出されます。

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

使い方

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

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

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

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

こういう風にしておけば、クエリやピボットに詳しくない人でも簡単に扱えるファイルが出来ます。今回は車の月販台数でしたが、データベースに時々刻々とデータが自動格納される製品生産数、設備異常内容などをサマリーするときに便利だと思います。

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

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

コメント