Power Queryで「連結セル」「Excel方眼紙」形式のデータを読み取る方法

目次

概要

請求書や申請書などで、複数セルを結合 したり 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.WorkbookfxReadGridSheetTable.AddColumn で呼び出し、すべてのレコードを Table.Combine で統合します。
金額が右詰め・左詰めで長さが異なるList.Reverse で並びを合わせたうえで Text.PadStart などを使い桁数を固定すると誤読を防げます。
途中に空白セルが含まれるList.Transformnull"0" へ置換し、桁位置のずれを回避します。
複数行(明細行)を取り込みたいインデックスを List.Transform({1..n}, each SheetTable{_}) で回し、各行を同関数で処理してレコードのリストを作成します。

トラブルシューティング

  • 「インデックスが範囲外」エラー
    対象行が存在しない場合に発生します。Table.RowCount で行数を確認し、安全に try … otherwise null で補完してください。
  • 数値変換でエラー
    数値セルにカンマや全角文字が混在している場合は Text.Select で数字だけ抜き出してから Number.FromText に渡します。
  • 列ずれ、列数不足
    列インデックスはワークシート構造に依存します。対象列が増減した際は、amountParts のリストを見直してください。

まとめ

  • カスタム関数 を利用すれば、テーブル化が難しい 連結セルExcel方眼紙 形式でも Power Query で取り込み可能です。
  • ポイントは「行・列をインデックスで直接指定し、リスト操作で正規化する」ことです。
  • 複数ブック・複数シートにも同一関数を適用し、集計用のマスター表を自動生成できます。
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

私が勉強したこと、実践したこと、してることを書いているブログです。
主に資産運用について書いていたのですが、
最近はプログラミングに興味があるので、今はそればっかりです。

目次