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