LWP | スピル数式と名前定義でPower Queryにつなぐ
スピル数式と名前定義でPower Queryにつなぐ
扱いにくいExcelデータを、整形・受渡し・更新の順で考える
Copyright © 2026 LWP 山中 一弘
本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。
記事要約
スピル結果の増減をPower Queryへ渡すには、ブック範囲の名前に「スピル範囲全体」を指定します。Excelで整えた表を、毎回範囲指定を直さずに後工程へつなぐ方法です。
シャケもんとアユさんが、末尾の空白行を除く数式、名前定義の #、Power Queryでの見出し・型、再計算と更新の違いを順番に確認します。Power Queryだけで整形する方法もありますが、Excel側で前処理すると扱いやすい場面に向く構成です。
数式例はTRIMRANGEを利用できるExcel for Microsoft 365が前提です。動的配列はExcelテーブルの外へ展開し、読み込み先は整形用シートと分けます。長いMコードと入力データは記事付属のサンプル一式に収めています。
1 まず、渡せる表に整える
アドイン出力にはタイトルや条件行が付くことがあります。ここでは見出しが8行目にあり、4列の表と末尾の空白だけを扱う例に絞ります。
図1｜末尾の空白行を除き、受け渡す4列を維持する。灰色部分の点は空白行の続きの模式表現。
2 # が、表全体を指す
名前の参照先を =ABC!$A$1# とすると、A1から展開した現在の範囲を受け渡せます。ブック範囲でPQ_Sourceを作り、末尾の # を確かめます。
図2｜名前は範囲への入口。データを清浄化したり、クエリを更新したりする機能ではない。
3 見出しと型を、ここで決める
Power Queryの空のクエリから名前付き範囲を取得します。今回の範囲には見出しが含まれるため、先頭行を列名へ昇格してから型を指定します。
図3｜4列の最小コードは付属のPQ_Source.pqに収録。Excel.CurrentWorkbookはシート一覧を返す関数ではない。
4 再計算と更新は、別の段階
名前の参照範囲が広がっても、読み込み済みのクエリ出力は更新まで変わりません。次の図は、2列・2レコードから1件追加した検証記録です。
図4｜元ネタの2026年9月7日の実測記録。図1～3の4列・3レコードの制作例とは初期データが異なる。
まとめ
スピル数式はExcel側の整形、名前定義は可変範囲の受渡し、Power Queryは見出し・型・結合・集計と読み込みを担当します。名前の範囲を固定セルで止めず、スピル全体へ向けることが接続の要点です。
運用では、取得完了、再計算完了、スピルのエラー確認、クエリ更新完了、出力確認の順で確かめます。出力は別シートへ置き、入力と出力を循環させません。
TRIMRANGEだけで途中の小計や結合セルを直せるわけではありません。スピル先の既存値、対象範囲の上限、名前の範囲、見出し、型変換エラーも確認してください。必要な前処理を選び、整った部分からPower Queryへ渡します。
付属サンプル
元データ.tsv：4列・3レコードの入力例。
PQ_Source.pq：4列を取得し、見出しと型を確定するMコード。
再現手順.md：配置、数式、名前定義、読み込み、更新前後の確認方法。
出典メモ
元ネタ「スピル数式と名前定義でPower Queryにつなぐ」の本文と2026年9月7日追補をもとに構成しました。図1～3は制作例です。図4はExcel 16.0 Build 20326、Windowsの隔離ブックでの過去検証ログと照合しています。今回はその実験を再実行していません。過去検証はQueryTableによる読み込みであり、UIによるテーブル出力や全ブックの更新順序までを実測したものではありません。
参考リンク
公式資料確認日：2026年9月8日。画像はLWPの人物参照を使い、Codex内蔵画像生成で制作しました。