スポンサーリンク/Sponsored Link

【Excel】複数のスピルが並んだ配列をクエリに取り込む

【Excel】複数のスピルが並んだ配列をクエリに取り込む
Excel(エクセル)
スポンサーリンク/Sponsored Link

Excelに新たに導入された「スピル」、一つの数式で多くのデータを一気に取り扱うことができ、大変便利です。

ひとつのFILTER関数で、7行2例の範囲にスピルしているようす

最近になり、「データのインポート」(Power Query)が、このスピルした配列に対応しました。

Excel データ→データの取得と変換→テーブルまたは範囲から
スポンサーリンク/Sponsored Link

スピルした範囲を動的にクエリに取り込めるようになった

「テーブルまたは範囲から」を選択すると、次の3つの方法で、ワークシート上のデータをクエリに取り込めます。

  1. ただデータが並んでいる範囲→自動的にテーブルに変換して取り込み
  2. テーブル→そのまま取り込み、テーブル名と同じ名前のクエリに
  3. 【NEW】スピルした範囲→自動的に名前付き範囲を定義して、スピル範囲を動的に取り込み
スピルした範囲をアクティブセルにし、データの取得と変換「テーブルまたは範囲から」をクリックすると、名前付き範囲「FromArray_1」が自動的に定義され、クエリに取り込まれる
スピルした範囲をアクティブセルにし、データの取得と変換「テーブルまたは範囲から」をクリックすると、名前付き範囲「FromArray_1」が自動的に定義され、クエリに取り込まれる

スピルする内容が変化し、行数や列数が変化しても、名前付き範囲が追従するため、大変使い勝手がよくなりました。

スピルの結果が変わり、行数が変わると、その後の更新でクエリも追従する
スピルの結果が変わり、行数が変わると、その後の更新でクエリも追従する

しかし、実務で使用するにあたり、ある重大な懸念があることに気づいたのです。

そこで、いろいろと試した結果、解決できましたのでまとめます。

スポンサーリンク/Sponsored Link

複数のスピルで「パッチワーク」になってもクエリに取り込めるのか?

実務では、データが単一の数式で表され、一つのスピル範囲でおさまることばかりではありません。

タイトル行とデータが、それぞれ1つのスピルで構成されるデータ
タイトル行とデータが別々のスピルで構成されるデータ

このように、2つ以上の数式が別々にスピルし、合わさって、ひとつの表を構成するすることがあります。

こうした場合、このまま「データの取得と変換」をすると、どちらか一方のスピル範囲しか取り込めません。

片方のスピル範囲をアクティブセルにして、デートの取得と変換をすると、タイトル行しか取り込めなかった

こうした時、どうすれば、全体を取り込めるでしょうか?

複数のスピルで構成され「パッチワーク」になった配列をクエリに取り込む方法

1. すべてのスピルを包含する名前付き範囲を定義する

まず、次のように手動で名前付き範囲を定義します。

G1#:G2#を名前として定義する

「G1#:G2#」なんて、こんな範囲指定、初めて使いましたけども、要するに、通常の範囲指定における「左上:右下」ルールを、数式単位で考え、「最も左上の数式:最も右下の数式」という形で範囲を定義します。

スピルしていますから、見た目上、この範囲は、必要なデータのすべてを包含していません。

しかし、「#」をつけて、スピルの全体を指し示すことによって、この表記で配列全体を示すことができるようです。

2. 「空のクエリ」を作成し、=Excel.CurrentWorkbook() をソースにする

次に、クエリの作成も、「テーブルまたは範囲」ではなく「空のクエリ」で作成します。

空のクエリを作成する操作

ソースに =Excel.CurrentWorkbook と手入力してEnterを押すと、ブック内のテーブルや名前付き範囲の一覧が出ますので、いま定義した名前付き範囲の左の「Table」をクリックして展開します。

すると、みごと、ほしい範囲の全体がクエリに取り込まれました。

=Excel.CurrentWorkBook() をソースとし、出てきたリストからいま定義した名前付き範囲のTableをクリックして展開
スポンサーリンク/Sponsored Link

スピルじゃないデータも混ざっていても、取り込めるのか?

最後に、もう少し複雑な例を試します。

手入力のセルと、スピルした範囲が混在したデータ
手入力のセルと、スピルした範囲が混在したデータ

このように、手入力した単なるデータと、スピルした配列が混在している場合、さきほどのような単純な名前付き範囲では取り込めません。

一覧に定義した名前付き範囲が出てこない
エラーになったと思われる

こんな場合でも、果たして取り込めるでしょうか?

少し工夫すれば、取り込めました。

単なるデータのセルを、わざと数式として書き直してから取り込む

次のように書き換えて、単なるデータが入っているセルをなくし、すべて数式で記述します。

数式でないデータが入っているセルを、="No."のように、数式に書き換える
数式でないデータが入っているセルを、=”No.”のように、数式に書き換える

このとき、「=”No.”」「=”グレード”」は、スピルしていませんが、それで構いません。

数式でさえあれば、さきほど使った形式の、「#」を付加した範囲指定が効きます。

定義した名前付き範囲が、一覧に出てきた

ぶじ、一覧に、目的の名前付き範囲が出て来ましたので「Table」をクリックします。

ぶじ、複雑なパッチワークになっている範囲の全体を、クエリに取り込むことができました。

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、オンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

キュリオステーション志木店運営をフォローする
スポンサーリンク/Sponsored Link

コメント

タイトルとURLをコピーしました