スピル範囲を、名前定義の操作なしでPower Queryへ

LWP | スピル範囲を、名前定義の操作なしでPower Queryへ

スピル範囲を、名前定義の操作なしでPower Queryへ

図解1|前編では、名前を付けて渡していた

シャケモンが記事T205の接続を振り返る。コード・担当者・商品・金額のスピル結果にPQ_Sourceを付け、Power Queryへ渡す

図1|前編では、スピル全体を指す名前を作り、Excel数式とPower Queryをつなぎました。

解説1|あの受け渡しを、別の道具でも使いたい

ストーリー

「前に作った、あのつなぎ方を使えそうだね」。シャケモンは、小さな集計用の道具を考えていました。Excel数式で必要な表を作り、その結果をPower Queryで加工する方法です。

前編の記事T205では、元の一覧を数式で整え、ABCシートのA1からスピルさせました。コード・担当者・商品・金額の4列を持つ表に、ブック範囲の名前「PQ_Source」を付けます。その参照先を =ABC!$A$1# とし、Power Queryから読み込む構成でした。

前編で分けた三つの仕事

Excel数式は表の前処理、名前はスピル全体の受け渡し、Power Queryは見出し・型・結合・集計などを受け持ちます。末尾の # は、A1から展開した現在のスピル範囲全体を指します。

シャケモンは、同じ方法を使うたびに、まず名前を定義していました。「今回もPQ_Sourceを作って、それから取り込みかな」。そこで、別の入口を知ります。

読むポイント

前編の接続の考え方を使い、今回は事前に名前を定義する操作を省く方法を見ていきます。前編は記事T205「スピル数式と名前定義でPower Queryにつなぐ」です。

図解2|名前を付ける前に、取り込めた!

スピル範囲のセルから「データ」→「テーブルまたは範囲から」を実行すると、FromArray_1という参照が用意される。シャケモンが新しい入口に驚く

図2|スピル範囲から取り込みを始めると、手動で名前を定義する前にクエリを作れます。

解説2|いつものボタンに、もう一つの入口があった

シャケモンの発見

スピル範囲のセルを選び、「データ」から「テーブルまたは範囲から」を実行します。Power Queryのソースには「FromArray_1」という名前が現れました。

「名前を先に付けてないのに、取り込めた!」。シャケモンが驚いたのは、自分で名前を用意する操作を、取り込み操作に任せられたことです。スピルで作った表を、すぐ次の加工へ持ち込めそうです。

操作が省ける場所

今回省けるのは、クエリを作る前に、ユーザーが名前と参照先を入力する段階です。取り込み操作が、動的配列を読むための参照先を用意します。数式を入力しただけで、すべてのスピル範囲にFromArrayの名前が付く、という流れではありません。

Microsoftの公式説明では、「テーブルまたは範囲から」は、現在のブックのテーブル・名前付き範囲・動的配列に対応しています。動的配列の取り込みにはMicrosoft 365サブスクリプションが必要です。

読むポイント

FromArray_1の末尾の数字は説明例です。実際に生成された名前を確認してください。これは、シャケモンが今回見つけた既存の機能です。Excelへ今回初めて追加された機能、という紹介ではありません。

図解3|自動でできた参照を、Mが読む

手動のPQ_Sourceと自動で用意されるFromArray_1が、どちらもExcel.CurrentWorkbookを通してスピル結果の値を渡す。ソース式と対象の関係を示す

図3|M式では、実際に用意された参照名を指定し、そのContentを取得します。

解説3|入口が変わっても、渡すのは計算結果

二つの名前と、同じ受け渡し

前編ではPQ_Source、今回はFromArray_1などが取り込み元になります。名前の用意の仕方が変わっても、Excel側で計算した結果をPower Queryへ渡す関係は同じです。

名前を # 付きの参照先へ結び付ける自動作成方式は、2021年のBeta版の実測報告にも見られます。現在の公式仕様でも、Excel.CurrentWorkbook()の取得対象には動的配列が含まれています。同関数は、ワークシートの一覧を返す関数ではありません。

ソース式の読み方

取り込み元の参照名がFromArray_1のとき、取得式は次の形になります。実際の名前が異なる場合は、その名前を確認します。

01

Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content]

Excel.CurrentWorkbook()で現在のブックの取得対象を一覧にし、NameがFromArray_1の行を選び、Contentにあるデータを取り出します。FromArrayという新しいM関数を呼んでいるわけではなく、Nameに入った文字列を使っています。

読むポイント

Power Queryへ渡るのは、スピル数式そのものをM言語へ翻訳したコードではなく、その時点の計算結果の値です。シャケモンは「Excelで作った表を渡す」という前編の考え方を、そのまま使えます。

図解4|取り込みから出力までを確かめる

スピル範囲からの取り込み、ソースと行列の確認、見出しと型、入力と重ならない出力先の四段階を示す

図4|名前を先に定義する手間を省いても、受け取った表の確認は残ります。

解説4|取り込んだ表は、いつもどおり確かめる

操作の流れ

対応するWindows版Excel for Microsoft 365で、正常に展開しているスピル範囲を使います。前編の構成なら、ABCシートのスピル元A1を選び、「データ」→「テーブルまたは範囲から」を実行します。

エディターが開いたら、ソースの参照名と、取得した行・列を確認します。次に、先頭行を見出しとして扱えているかを確認し、必要なら見出しへ昇格します。最後に列の型を整え、入力と重ならない場所へ読み込みます。

確認する表の意味

列

前編の表で確認すること

コード

A001などを識別子として保持する

担当者

田中・鈴木などの文字を読む

商品

商品A・商品Bなどの文字を読む

金額

10000などを数値として扱う

一行のデータは、一つの商品と担当者・金額の組合せです。先頭行の「コード」などがデータ行へ残っていないか、反対に最初のデータ行を見出しへ変えていないかも確認します。

読むポイント

通常の固定セル範囲を選んだ場合は、取り込み時にExcelテーブルへ変換されることがあります。選択したものがスピル範囲かどうかと、生成されたソースの両方を見てください。最初の動作確認には、手動の名前をまだ付けていない検証用のブックを使うと、どの段階で参照が用意されたかを追えます。

図解5|四つの名前を、役割で見分ける

FromArray_1は取り込み元、抽出結果はクエリ、出力はシート、tbl_抽出結果は出力テーブルという四つの名前を分ける

図5|クエリの名前を変える操作と、ソースが指す名前を変える操作を区別します。

解説5|クエリを改名して、使いやすくしておこう

名前の付いた対象を分ける

「クエリは『抽出結果』にしよう。読み元はそのままだね」。シャケモンは、処理が何をするのか分かる名前へ整えます。ここで見分けたいのは、次の四つです。

名前の種類

名前の例

指しているもの

取り込み元の参照名

FromArray_1

スピル結果を取得するための対象

クエリ名

抽出結果

Power Queryの処理

出力シート名

出力

結果を置くワークシート

出力テーブル名

tbl_抽出結果

読み込んだ結果のExcelテーブル

それぞれ別の対象の名前です。クエリやシート、出力テーブルの名前を整えると、どこに何があるか分かりやすくなります。ここに挙げた四つの名前は説明例です。実際に作られた名前を確認してください。

ソースの参照は、実在する名前へ

クエリ名を「抽出結果」にしたからといって、ソース式のName="FromArray_1"まで同じ文字列に置き換えません。そこは、取り込み元として実際に存在する名前を指します。

読むポイント

改名は、取り込みを成立させるための必須操作ではなく、管理しやすくする工夫です。取り込み元の名前自体も変える場合は、M式の参照先と一致させます。最初から意味の分かる名前を付ける前編の方法も、引き続き選べます。

図解6|スピルの再計算と、クエリ更新

元データ3件に1件を足すとスピルは見出し込み4行から5行へ増える。読み込み済みの出力は更新前に3件、クエリ更新後に4件となる説明例

図6|Excel側の結果を確かめ、クエリを更新し、その後に出力を確認します。

解説6|行が増えたら、出力まで確かめる

結果の変わる段階

前編と同じく、Excelの再計算とPower Queryの更新は別です。図は、データが3件ある表へ1件を追加する説明例です。見出しを含むスピル範囲は4行から5行へ増えます。

一方、前に読み込んだクエリの出力は、更新前にはデータ3件のままです。Excel側の計算結果を確認してからクエリを更新すると、新しい結果を取得します。更新後にデータ4件が出ているかを確かめます。

行数と列構成を分ける

行が増えた場合と、列名・列数を変えた場合では、確認する箇所が違います。列構成を変えると、型の指定や列の選択など、後続ステップの見直しが必要になることがあります。

スピルがエラーなら、まずExcel側の状態を調べます。スピル先を既存の値が塞いでいないか、数式が正常に計算されているかを確認し、クエリの出力先を入力範囲へ重ねないようにします。

読むポイント

FromArrayを使う場合も、「再計算を確認→クエリ更新→出力確認」の順で追います。図の件数は説明用のモデルです。更新タイミングや結果は、実際に使うブックで確認してください。

図解7|次の小さな道具で、試してみよう

セル条件でExcel数式の抽出結果を変え、スピルをPower Queryの結合や集計へ渡す道具の案。簡易な試作と継続運用で、名前の付け方を選ぶ

図7|前編の名前定義と、今回の取り込みから始める方法を、作るものに合わせて選びます。

解説7|使える入口が増えると、試したくなる

次の道具の案

「セルで条件を変えて、必要な行をスピルで出す。その先の結合や集計をMで作ったら、小さな確認用の道具になりそうだね」。シャケモンの中で、次に作りたいものが浮かびます。

今回の発見で、Excel数式の結果をPower Queryへ渡す前の一手間を省けます。まず動く形を試したいときには、自動で用意される参照を使う方法が候補になります。

まとめ

複数の取り込み元を使うとき、他の人へ引き継ぐとき、長く使うときには、PQ_Sourceのように意味の分かる名前を付けると関係を追いやすくなります。簡易な試作から始め、使い続ける見通しが立ったところで名前を整える進め方も考えられます。

今回の仕掛け

前編で覚えたのは、スピル全体を名前で受け渡す方法でした。今回は、取り込み操作へ参照の用意を任せられると分かりました。どちらも知っていれば、作りたいものに合わせて選べます。「これなら、小さな道具もすぐ試せそうだ!」。シャケモンの発見は、次に試す楽しさにつながっていきます。

出典メモ

Microsoftの取り込み手順、Excel.CurrentWorkbookの仕様、スピルの動作、2021年Beta版での観測報告を参照。公式資料確認日は2026年10月25日です。場面と表は説明用で、今回の操作を実測した報告ではありません。FromArray_1は参照名の例です。数字は実際の生成名に合わせます。

利用について

Copyright © 2026 LWP 山中 一弘。出典と著作権表示を保持して利用できます。