LWP | 不定形データをPower Queryへ安定して渡す

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

ツール出力やコピー＆ペーストで受け取る一覧は、行数や列数が毎回変わります。この不定形な入力をPower Queryへ安定して渡すには、Excel側で有効データだけの動的配列へ整え、意味のある名前を取込口として公開する設計が有効です。

この記事では、TRIMRANGEなどで整形した結果をH2#としてスピルさせ、ブック範囲の名前「取込データ」を付け、Excel.CurrentWorkbook()から取得します。動的範囲そのものはH2#で完成しており、名前定義はその範囲をPower Queryへ安定して公開するインターフェースとして使います。

同じブックに貼り付けたデータなら、ファイルパスを組み立てずにExcel.CurrentWorkbook()で取得できます。ただし、ワークシート全体を取込口にするのではなく、Power Queryから識別できる安定した入口を用意するのがこの記事の設計です。

図1｜不定形な入力を、そのままPower Query側へ持ち込まない

図中の「一覧に出てこない」は、元原稿の実機検証で裸のスピル結果が利用者の付けた名前として列挙されなかった状態を指します。Microsoft公式ではExcel.CurrentWorkbook()はテーブル、名前付き範囲、動的配列を返すと説明されているため、すべてのExcelビルドで動的配列が列挙されないという意味ではありません。

入力専用シートの外周に残る空白行・空白列を除き、Power Queryへ渡したい長方形の配列を作ります。たとえば別の入力シートのA列からF列を対象にするなら、整形シートのH2へ=TRIMRANGE(入力!A:F)を置きます。

図2｜TRIMRANGEは目的ではなく、Power Queryへ渡す形を作る整形工程

列全体を参照する場合は、遠い行にメモや検証値を残さない入力専用シートとして運用します。最大件数が決まっているなら、=TRIMRANGE(入力!A1:F50000)のように探索範囲を限定する設計も選べます。

H2#は、H2を起点に現在スピルしている範囲全体を表します。Excel数式だけで利用するなら、ROWS(H2#)やINDEX(H2#,2,1)のように直接参照でき、動的範囲を作るための名前定義は不要です。

図3｜名前は範囲を動的にするためではなく、Power Queryへ意味のある入口として公開するために付ける

今回の方法では、H2#をそのまま使えるExcel数式と、Excel.CurrentWorkbook()から安定した名前で取りたいPower Queryの役割を分けます。

Excelの「数式」タブから「名前の管理」を開き、ブック範囲の名前「取込データ」を作ります。参照範囲には、現在の長方形を固定アドレスで登録せず、スピル範囲演算子#を含む参照を指定します。

参照範囲は=動的名前範囲!$H$2#です。

図4｜入力の増減に追従するH2#を、ブック範囲の名前で公開する

=$H$2:$M$7のような固定範囲を登録すると、行や列が増えても名前の参照先は広がりません。名前の管理で、参照範囲がH2#になっていることを確認します。

Power Queryの空のクエリでExcel.CurrentWorkbook()を評価し、名前「取込データ」のContentを取得します。対象名を直接指定するなら、Mコードは次の形です。

取得式はExcel.CurrentWorkbook(){[Name="取込データ"]}[Content]です。必要な変換は、この結果をSourceとして後続ステップへつなぎます。

図5｜Power Queryはセル番地ではなく、公開された名前を取込口として使う

図5の一覧は、テーブルと名前定義の種類を説明する模式図です。実際の取得コードはNameとContentを使い、図中のKindやHidden列を前提にしません。

Power Queryへ渡す前に、名前「取込データ」が現在のスピル範囲全体を指しているかをExcel側で確認します。

• 行数: =ROWS(取込データ)

• 列数: =COLUMNS(取込データ)

• 先頭明細: =INDEX(取込データ,2,1)

図6｜行数・列数の増減、スピル先の空き、起点セルと名前定義をセットで確認する

スピル先に値、数式、結合セルがあると展開できません。起点セルをH2からJ2へ移した場合は、名前の参照先もJ2#へ変更します。入力列の上限を広く取る場合は、対象列の遠い位置に不要な値が残っていないことも確認します。

この方法はTRIMRANGEの小技ではなく、入力、整形、公開、Power Queryを分離する設計パターンです。Excel側で不定形さを吸収し、Power Queryには「取込データ」という安定した入口だけを見せます。

図7｜入力の変動はExcel側で処理し、Power Queryは公開された名前から取得する

TRIMRANGEに対応するExcel環境であれば、外周空白の除去を簡潔に書けます。対応していない環境では別の整形方法が必要ですが、整形済みの動的範囲に名前を付け、CurrentWorkbookから取得するという4層の考え方は共通です。

不定形な入力データは、Power Queryへ直接探させるのではなく、Excel側で有効データだけのスピル範囲へ整えます。H2#を参照する名前「取込データ」をブック範囲で定義すれば、入力件数が変わってもPower Queryは同じ名前から取得できます。

動的範囲を作る役割はTRIMRANGEとH2#、Power Queryへ公開する役割は名前定義、取得と変換の役割はExcel.CurrentWorkbook()に分けます。この役割分担が、保守しやすい取込口を作る要点です。

• 元原稿・図1〜図7: C:\Users\hoehoe\Downloads\02_記事掛け合い\記事作成済み\記事202_不定形データをPowerQueryへ渡す_CurrentWorkbook動的名前範囲_記事元ネタ改訂版260827.md および同フォルダーの採用済み掛け合いインフォグラフィック（2026-08-27確認）

• 参考画像: C:\Users\hoehoe\Downloads\02_記事掛け合い\記事の参考画像・動的名前範囲.png は内容確認だけに使用し、記事本文・公開画像・完了移動対象には含めていない

• Microsoft Learn: Excel.CurrentWorkbook — 現在のブックからテーブル、名前付き範囲、動的配列を返し、シートは返さない

• Microsoft Support: Import data from data sources (Power Query) — 現在のブック内のテーブル、名前付き範囲、動的配列からクエリを作成できる

• Microsoft Support: TRIMRANGE function — 範囲または配列の外周にある空白行・空白列を除外する関数

Copyright © LWP 山中 一弘 |