Excelに新たに導入された「スピル」、一つの数式で多くのデータを一気に取り扱うことができ、大変便利です。
最近になり、「データのインポート」(Power Query)が、このスピルした配列に対応しました。
スピルした範囲を動的にクエリに取り込めるようになった
「テーブルまたは範囲から」を選択すると、次の3つの方法で、ワークシート上のデータをクエリに取り込めます。
- ただデータが並んでいる範囲→自動的にテーブルに変換して取り込み
- テーブル→そのまま取り込み、テーブル名と同じ名前のクエリに
- 【NEW】スピルした範囲→自動的に名前付き範囲を定義して、スピル範囲を動的に取り込み
スピルする内容が変化し、行数や列数が変化しても、名前付き範囲が追従するため、大変使い勝手がよくなりました。
しかし、実務で使用するにあたり、ある重大な懸念があることに気づいたのです。
そこで、いろいろと試した結果、解決できましたのでまとめます。
複数のスピルで「パッチワーク」になってもクエリに取り込めるのか?
実務では、データが単一の数式で表され、一つのスピル範囲でおさまることばかりではありません。
このように、2つ以上の数式が別々にスピルし、合わさって、ひとつの表を構成するすることがあります。
こうした場合、このまま「データの取得と変換」をすると、どちらか一方のスピル範囲しか取り込めません。
こうした時、どうすれば、全体を取り込めるでしょうか?
複数のスピルで構成され「パッチワーク」になった配列をクエリに取り込む方法
1. すべてのスピルを包含する名前付き範囲を定義する
まず、次のように手動で名前付き範囲を定義します。
「G1#:G2#」なんて、こんな範囲指定、初めて使いましたけども、要するに、通常の範囲指定における「左上:右下」ルールを、数式単位で考え、「最も左上の数式:最も右下の数式」という形で範囲を定義します。
スピルしていますから、見た目上、この範囲は、必要なデータのすべてを包含していません。
しかし、「#」をつけて、スピルの全体を指し示すことによって、この表記で配列全体を示すことができるようです。
2. 「空のクエリ」を作成し、=Excel.CurrentWorkbook() をソースにする
次に、クエリの作成も、「テーブルまたは範囲」ではなく「空のクエリ」で作成します。
ソースに =Excel.CurrentWorkbook と手入力してEnterを押すと、ブック内のテーブルや名前付き範囲の一覧が出ますので、いま定義した名前付き範囲の左の「Table」をクリックして展開します。
すると、みごと、ほしい範囲の全体がクエリに取り込まれました。
スピルじゃないデータも混ざっていても、取り込めるのか?
最後に、もう少し複雑な例を試します。
このように、手入力した単なるデータと、スピルした配列が混在している場合、さきほどのような単純な名前付き範囲では取り込めません。
こんな場合でも、果たして取り込めるでしょうか?
少し工夫すれば、取り込めました。
単なるデータのセルを、わざと数式として書き直してから取り込む
次のように書き換えて、単なるデータが入っているセルをなくし、すべて数式で記述します。
このとき、「=”No.”」「=”グレード”」は、スピルしていませんが、それで構いません。
数式でさえあれば、さきほど使った形式の、「#」を付加した範囲指定が効きます。
ぶじ、一覧に、目的の名前付き範囲が出て来ましたので「Table」をクリックします。
ぶじ、複雑なパッチワークになっている範囲の全体を、クエリに取り込むことができました。
コメント