目次
概要
請求書や申請書などで、複数セルを結合 したり Excel方眼紙 のように 1 桁ずつ金額を分けて入力しているワークシートがあります。通常のテーブル化では列がそろわず集計できませんが、Power Query の カスタム関数 を使えば、特定の行・列から値を抽出し、集約済みの整ったデータへ変換できます。
Mコード(「詳細エディター」に記述:例 fxReadGridSheet
)
// 連結セルや方眼紙シートを 1 行分読み取り、レコードで返す関数
(SheetTable as table) as record =>
let
// 例:2 行目の氏名(B 列相当)を取得
row2 = SheetTable{1},
employeeName = row2[Column2],
// 例:2 行目の F~J 列相当(桁分割された金額)を取得
amountParts = {
row2[Column6],
row2[Column7],
row2[Column8],
row2[Column9],
row2[Column10]
},
// null を "0" に置換して連結 → 数値へ変換
amountText = Text.Combine(
List.Transform(
amountParts,
each if _ = null then "0" else Number.ToText(_)
)
),
totalAmount = Number.FromText(amountText),
// レコードで返す
output = [Employee = employeeName, Amount = totalAmount]
in
output
- 列名が
Column2
などの連番 になっているのは、テーブル化していないワークシートをExcel.Workbook
で読み込んだ場合の仕様です。 SheetTable{1}
は「2 行目」を指します(0 始まり)。対象行が異なる場合はインデックスを変更してください。
応用
シナリオ | アプローチ |
---|---|
複数シートを一括処理 | Folder.Files でブックを列挙 → Excel.Workbook → fxReadGridSheet を Table.AddColumn で呼び出し、すべてのレコードを Table.Combine で統合します。 |
金額が右詰め・左詰めで長さが異なる | List.Reverse で並びを合わせたうえで Text.PadStart などを使い桁数を固定すると誤読を防げます。 |
途中に空白セルが含まれる | List.Transform で null を "0" へ置換し、桁位置のずれを回避します。 |
複数行(明細行)を取り込みたい | インデックスを List.Transform({1..n}, each SheetTable{_}) で回し、各行を同関数で処理してレコードのリストを作成します。 |
トラブルシューティング
- 「インデックスが範囲外」エラー
対象行が存在しない場合に発生します。Table.RowCount
で行数を確認し、安全にtry … otherwise null
で補完してください。 - 数値変換でエラー
数値セルにカンマや全角文字が混在している場合はText.Select
で数字だけ抜き出してからNumber.FromText
に渡します。 - 列ずれ、列数不足
列インデックスはワークシート構造に依存します。対象列が増減した際は、amountParts
のリストを見直してください。
まとめ
- カスタム関数 を利用すれば、テーブル化が難しい 連結セル や Excel方眼紙 形式でも Power Query で取り込み可能です。
- ポイントは「行・列をインデックスで直接指定し、リスト操作で正規化する」ことです。
- 複数ブック・複数シートにも同一関数を適用し、集計用のマスター表を自動生成できます。