Excel 2016以降パワークエリ M言語 COBOL/マニアック更新日: 2026-04-23

【参考資料】M言語でMIDB相当の自作関数を作る
― パワークエリ最大の壁を越えるための完全テクニック集 ―

エクセルでCOBOL帳票や固定長データを整形したい方向けに、Excel関数・Power Query・MIDBなどを使って列分割、不要行除去、帳票データ変換を進める実務手順を扱います。

💡 この資料の位置づけ

パワークエリ編で説明した「MIDBの壁」を実際に越えるための技術的な手順をまとめた参考資料です。M言語にはバイト単位で文字列を切り出す標準関数が存在しないため、Text.ToBinary と Binary.Range を組み合わせてExcelのMIDB・LEFTB・RIGHTB・LENBに相当するカスタム関数を自作します。

💬 前提知識：
・パワークエリの基本操作（エディタの起動、ステップの追加）がわかっていること。
・M言語の基本文法（let...in 構文、関数の書き方）をざっくり理解していること。
・COBOL帳票の固定長データが「半角＝1バイト、全角＝2バイト」であることを把握していること。

M言語を全く触ったことがない方は、先にパワークエリのGUI操作で自動生成されるMコードを読む練習をしてから本資料に進むことをお勧めします。

核心技術：Text.ToBinary + Binary.Range + Text.FromBinary

M言語でバイト単位の操作を実現するカギは、文字列をバイナリに変換し、バイナリレベルで切り出してから文字列に戻すという3段階の処理です。この3つの関数がすべての基礎になります。

文字列（text） → Text.ToBinary
Shift_JIS バイナリに変換 → Binary.Range
バイト位置で切り出し → Text.FromBinary
文字列に復元

ポイントはエンコーディングにShift_JIS（コードページ932）を指定することです。Shift_JISでは半角文字が1バイト、全角文字が2バイトとなり、これはCOBOL帳票の固定長ルールと完全に一致します。M言語の Text.ToBinary と Text.FromBinary はコードページ番号を受け取れるため、932 を渡すだけでShift_JISでの変換が可能です。

使用する主要関数（Microsoft公式リファレンス）：
Text.ToBinary(text, 932) ― 文字列をShift_JISエンコーディングでバイナリに変換
Binary.Length(binary) ― バイナリのバイト数を返す
Binary.Range(binary, offset, count) ― バイナリの指定位置から指定バイト数を切り出す（0始まり）
Text.FromBinary(binary, 932) ― バイナリをShift_JISとして文字列に復元

⚠ 重要：Binary.Range のオフセットは0始まり
ExcelのMIDB関数は開始位置が1始まりですが、M言語の Binary.Range は0始まりです。自作関数の中でこの差を吸収するか、使う側で意識する必要があります。本資料の自作関数ではExcelのMIDBと同じ1始まりに統一しています。

1 LENB相当：fx_LENB ― バイト数を数える

まず最もシンプルな関数から始めます。ExcelのLENB関数に相当する、文字列のバイト数を返す関数です。これ自体がMIDB自作関数の土台になるだけでなく、COBOL帳票の行長チェックなどにも単体で活躍します。

fx_LENB のコード

パワークエリエディタで [ホーム] → [詳細エディター] を開き、新しい空のクエリに以下を貼り付けます。クエリ名を fx_LENB に変更してください。

// fx_LENB ― ExcelのLENB相当（Shift_JIS基準）
(targetText as text) as number =>
let
// 文字列をShift_JISバイナリに変換
    bin = Text.ToBinary(targetText, 932),
    // バイナリの長さ ＝ バイト数
    length = Binary.Length(bin)
in
    length

検証例：
fx_LENB("ABC") → 3（半角3文字 = 3バイト）
fx_LENB("新宿本店") → 8（全角4文字 = 8バイト）
fx_LENB("AB新宿") → 6（半角2 + 全角2 = 2+4 = 6バイト）

💡 なぜ932を指定するのか：
932はWindowsにおけるShift_JISのコードページ番号です。COBOL帳票がShift_JISで出力されている場合（日本の業務システムでは大多数）、この値を使います。帳票がUTF-8の場合は全角が3バイトになるため、932ではなく65001を指定し、切り出しバイト数もそれに合わせて調整する必要があります。ただし、COBOL帳票のほとんどはShift_JIS前提で設計されているため、通常は932で問題ありません。

2 MIDB相当：fx_MIDB ― バイト位置で切り出す

これが本丸です。ExcelのMIDB関数と同じように、「開始バイト位置」と「バイト数」を指定して文字列の一部を切り出します。COBOL帳票の列分解で最も多用する関数です。

fx_MIDB のコード

// fx_MIDB ― ExcelのMIDB相当（Shift_JIS基準・1始まり）
(targetText as text, startByte as number, numBytes as number) as text =>
let
// 文字列をShift_JISバイナリに変換
    bin = Text.ToBinary(targetText, 932),
    // Binary.Rangeは0始まり → ExcelのMIDB（1始まり）に合わせて -1 する
    sliced = Binary.Range(bin, startByte - 1, numBytes),
    // バイナリをShift_JISとして文字列に復元
    result = Text.FromBinary(sliced, 932)
in
    result

検証例（帳票行: " 1001 新宿本店 0050000 山田"）：
fx_MIDB(row, 2, 4) → "1001"（店舗ID）
fx_MIDB(row, 9, 14) → "新宿本店 "（店舗名 ※末尾スペース含む）
fx_MIDB(row, 25, 7) → "0050000"（売上金額）

💬 引数の対応関係（ExcelのMIDBと完全に同じ）：
fx_MIDB(文字列, 開始バイト位置, バイト数)
・開始バイト位置：1始まり（先頭は1）。ExcelのMIDBと同じ。
・バイト数：切り出すバイト数。半角1バイト、全角2バイト。

COBOL帳票のPICTURE句やレイアウト定義書のバイト位置をそのまま使えるため、Excel関数版からの移行もスムーズです。

3 LEFTB・RIGHTB相当：fx_LEFTB / fx_RIGHTB

左端・右端からバイト数で切り出す関数も、同じ仕組みで簡単に作れます。COBOL帳票では使用頻度はMIDBほど高くありませんが、レコード種別の先頭判定や末尾のチェックディジット取得などに便利です。

fx_LEFTB のコード

// fx_LEFTB ― ExcelのLEFTB相当
(targetText as text, numBytes as number) as text =>
let
    bin = Text.ToBinary(targetText, 932),
    sliced = Binary.Range(bin, 0, numBytes),
    result = Text.FromBinary(sliced, 932)
in
    result

fx_RIGHTB のコード

// fx_RIGHTB ― ExcelのRIGHTB相当
(targetText as text, numBytes as number) as text =>
let
    bin = Text.ToBinary(targetText, 932),
    totalLen = Binary.Length(bin),
    sliced = Binary.Range(bin, totalLen - numBytes, numBytes),
    result = Text.FromBinary(sliced, 932)
in
    result

検証例：
fx_LEFTB("AB新宿本店", 6) → "AB新宿"（2 + 4 = 6バイト）
fx_RIGHTB("AB新宿本店", 4) → "本店"（全角2文字 = 4バイト）

4 カスタム関数の登録手順

自作関数をパワークエリに登録する手順を示します。一度登録すれば、同じブック内のどのクエリからでも呼び出せます。

手順① 空のクエリを作成

[データ] → [データの取得] → [その他のデータソースから] → [空のクエリ] を選択します。パワークエリエディタが開きます。

手順② 詳細エディターにコードを貼り付け

[ホーム] → [詳細エディター] をクリックし、表示されたエディタの内容をすべて削除してから、上記の関数コード（例：fx_MIDBのコード全体）を貼り付けます。[完了] をクリックします。

手順③ クエリ名を変更

画面右側の「クエリの設定」ペインで、名前を fx_MIDB などわかりやすい名前に変更します。関数として正しく認識されると、エディタ上部に引数の入力欄が表示されます。

手順④ 動作確認

引数入力欄にテスト値を入れて [呼び出し] ボタンを押し、期待どおりの結果が返ることを確認します。確認後、自動生成された呼び出しクエリは削除して構いません。

📌 4つの関数すべてを登録する場合：
fx_LENB、fx_MIDB、fx_LEFTB、fx_RIGHTB の4つをそれぞれ空のクエリとして登録します。クエリ一覧の左側ペインに4つの関数アイコン（fxマーク）が並びます。

5 実践：COBOL帳票の列分解に使う

登録した自作関数を使って、COBOL帳票を列分解する具体的な手順を示します。

想定する帳票レイアウト

位置    バイト数   項目名
─────────────────────────
     4       店舗ID（半角数字）
     1       区切りスペース
    14       店舗名（全角/半角混在）
     1       区切りスペース
     7       売上金額（半角数字）
     1       区切りスペース
     8       担当者名（全角）

カスタム列の追加でfx_MIDBを呼び出す

帳票データが1列（例：Column1）に取り込まれている状態で、[列の追加] → [カスタム列] を選択し、以下のような式を入力します。

// 店舗ID列（バイト位置1～4）
fx_MIDB([Column1], 1, 4)

// 店舗名列（バイト位置6～19）
Text.Trim( fx_MIDB([Column1], 6, 14) )

// 売上金額列（バイト位置21～27）
Number.FromText( fx_MIDB([Column1], 21, 7) )

// 担当者名列（バイト位置29～36）
Text.Trim( fx_MIDB([Column1], 29, 8) )

💡 Text.Trim と Number.FromText を組み合わせる：
COBOL帳票のフィールドはスペース埋めされているため、文字列項目は Text.Trim() で前後のスペースを除去します。数値項目は Number.FromText() で数値型に変換すれば、先頭のゼロ埋め（例："0050000" → 50000）も自動的に処理されます。これはExcel関数版で TRIM() や VALUE() を使っていたのと同じ発想です。

Excel関数 ↔ M言語自作関数対応表

Excel関数パイプラインからパワークエリに移行する際の対応関係をまとめます。

処理内容	Excel関数	M言語（自作関数）
バイト数を返す	`LENB(A1)`	`fx_LENB([Column1])`
バイト位置で切り出し	`MIDB(A1, 6, 14)`	`fx_MIDB([Column1], 6, 14)`
左端からバイト数で取得	`LEFTB(A1, 4)`	`fx_LEFTB([Column1], 4)`
右端からバイト数で取得	`RIGHTB(A1, 8)`	`fx_RIGHTB([Column1], 8)`
前後スペース除去	`TRIM(...)`	`Text.Trim(...)`　※標準関数
数値変換	`VALUE(...)`	`Number.FromText(...)`　※標準関数
行フラグ判定	`IF(LEFTB(A1,3)="===", ...)`	フィルター操作で除外、または `Text.StartsWith([Column1], "===")`
フィルダウン	`IF(条件, 新値, 直上セル参照)`	[変換] → [フィル] → [下方向]　※標準機能

⚠ エラーハンドリング：境界ケースへの対処

自作関数を実際の帳票データに使うと、空行やバイト位置が範囲外のケースでエラーが発生することがあります。安全に使うためのエラー処理パターンを示します。

fx_MIDB 安全版（エラー処理付き）

// fx_MIDB_Safe ― 範囲外や空文字列でもエラーにならない安全版
(targetText as nullable text, startByte as number, numBytes as number) as nullable text =>
let
// null や空文字列のガード
    safeText = if targetText = null or targetText = "" then null
else
let
            bin = Text.ToBinary(targetText, 932),
            totalLen = Binary.Length(bin),
            // 開始位置がバイト長を超えている場合は空文字
            adjStart = startByte - 1,
            // 切り出し範囲が末尾を超えないよう調整
            adjCount = Number.Min({numBytes, totalLen - adjStart}),
            result = if adjStart >= totalLen or adjCount <= 0 then ""
else Text.FromBinary(Binary.Range(bin, adjStart, adjCount), 932)
        in
            result
in
    safeText

💬 安全版を使うべきケース：
・帳票にヘッダー行やフッター行が含まれており、バイト長が明細行と異なる場合。
・フィルターで不要行を除去する前にMIDB分解を行う場合。
・null値が混在するデータを処理する場合。

不要行をフィルターで除去した後にMIDB分解を行う（＝全行が同一レイアウト）なら、シンプル版で十分です。処理の順序に応じて使い分けてください。

⚡ パフォーマンスに関する注意

自作関数は便利ですが、パワークエリのカスタム関数は標準関数と比べて処理速度が遅くなる傾向があります。特にMIDB分解では行ごとにバイナリ変換を行うため、データ量が多い場合はパフォーマンスへの影響に注意が必要です。

パフォーマンスを改善するテクニック

テクニック①：先にフィルターで行数を減らす

MIDB分解（重い処理）の前に不要行をフィルターで除去します。これにより、バイナリ変換の回数が大幅に減ります。1万行のデータから5,000行が不要行なら、処理時間は半分になります。

テクニック②：一括バイナリ変換パターン

各列をカスタム列として個別に追加すると、行ごとに何度もText.ToBinaryが呼ばれます。以下のように1回のバイナリ変換から複数の列を切り出すほうが効率的です。

// 1行分のバイナリ変換を1回だけ行い、複数項目を切り出す
// カスタム列でレコードを返し、展開する方式
let
    bin = Text.ToBinary([Column1], 932),
    店舗ID   = Text.FromBinary(Binary.Range(bin, 0, 4), 932),
    店舗名   = Text.Trim(Text.FromBinary(Binary.Range(bin, 5, 14), 932)),
    売上金額 = Number.FromText(Text.FromBinary(Binary.Range(bin, 20, 7), 932)),
    担当者名 = Text.Trim(Text.FromBinary(Binary.Range(bin, 28, 8), 932))
in
    [店舗ID=店舗ID, 店舗名=店舗名, 売上金額=売上金額, 担当者名=担当者名]

このパターンでは、Text.ToBinary が行あたり1回しか実行されません。4列を個別に追加すると4回実行されるところが、4分の1に減ります。カスタム列でレコード型を返し、そのレコードを展開して各列にすることで実現できます。

テクニック③：Table.Buffer で中間結果をキャッシュ

複雑なクエリでは、MIDBの結果テーブルを Table.Buffer() でメモリにキャッシュすることで、後続のステップでの再計算を防げます。

📌 パフォーマンスの目安：
数千行程度 … パフォーマンスを意識する必要はほぼない。
1万～5万行 … テクニック①（先にフィルター）を適用すれば問題ない。
5万行以上 … テクニック②（一括変換）を検討。それでも遅い場合はテクニック③も併用。
数十万行以上 … パワークエリ単体では限界がある場合も。Power BIやデータベース経由の処理も視野に入れる。

📋 エンコーディングとコードページ一覧

本資料ではShift_JIS（コードページ932）を前提としていますが、帳票のエンコーディングが異なる場合は、Text.ToBinary と Text.FromBinary に渡すコードページ番号を変更します。

エンコーディング	コードページ	全角1文字のバイト数	備考
Shift_JIS	`932`	2バイト	日本のCOBOL帳票の大多数。ExcelのMIDBと同じバイト計算。
UTF-8	`65001`	3バイト	最近のシステムで増加中。全角が3バイトのため、COBOL帳票のレイアウト定義と合わないことが多い。
EUC-JP	`20932`	2バイト	UNIX系システムのCOBOL帳票で稀に使用。
IBM CP930 (EBCDIC日本語)	`―`	2バイト	メインフレームからの直接出力。パワークエリ（Windows）では直接対応しないため、事前にShift_JISへ変換が必要。

⚠ エンコーディングの確認方法：
帳票ファイルが何のエンコーディングで出力されているかわからない場合は、テキストエディタ（サクラエディタ、VSCodeなど）でファイルを開き、ステータスバーに表示されるエンコーディングを確認してください。Shift_JIS、CP932、SJIS、MS932 などはすべてコードページ932で処理できます。

🔧 トラブルシューティング

自作関数を使っていて遭遇しやすい問題と対処法をまとめます。

症状：切り出し結果が文字化けする

原因： 全角文字の途中（2バイトの1バイト目だけ、または2バイト目だけ）を切り出している。開始位置やバイト数が1バイトずれると、全角文字の境界をまたいでしまいます。

対処： COBOL帳票のレイアウト定義書を再確認し、全角項目の開始バイト位置と長さが偶数バイトで揃っているか確認してください。全角項目は必ず2の倍数のバイト位置から、2の倍数のバイト数で切り出す必要があります。

症状：Expression.Error - 引数が範囲外

原因： Binary.Range のオフセットやカウントがバイナリの長さを超えている。ヘッダー行やフッター行など、明細行よりバイト長が短い行が混在している可能性があります。

対処： MIDB分解の前にフィルターで不要行を除去するか、安全版の fx_MIDB_Safe を使用してください。

症状：半角カタカナの切り出し結果がおかしい

原因： Shift_JISでは半角カタカナは1バイトですが、UTF-8では3バイトです。帳票のエンコーディングとコードページの指定が合っていない可能性があります。

対処： 帳票のエンコーディングを確認し、コードページ番号が正しいか検証してください。COBOL帳票がShift_JISなら932、UTF-8なら65001を指定します。

症状：関数呼び出しで「クエリが見つかりません」エラー

原因： カスタム関数のクエリ名が正しく設定されていない、またはクエリが「接続のみ」になっていない。

対処： カスタム関数のクエリが [閉じて読み込む] → [接続の作成のみ] で保存されているか確認してください。また、関数を呼び出すクエリとカスタム関数のクエリが同じブック内にあることを確認してください。

🎯 まとめ：この資料の関数一覧

クエリ名	対応するExcel関数	用途
`fx_LENB`	LENB	文字列のバイト数を返す。行長チェック・条件分岐に使用。
`fx_MIDB`	MIDB	バイト位置指定で切り出す。COBOL帳票の列分解の中核。
`fx_LEFTB`	LEFTB	左端からバイト数で切り出す。レコード種別判定に使用。
`fx_RIGHTB`	RIGHTB	右端からバイト数で切り出す。チェックディジット取得等。
`fx_MIDB_Safe`	MIDB（エラー耐性版）	null・範囲外を自動処理。不要行混在時に使用。

💡 この資料の関数を使えば、パワークエリ編で解説した以下のパイプラインがすべて実現できます：

フォルダから一括取込 → フィルターで不要行除去 → フィル下方向（フィルダウン） → fx_MIDBで列分解 → 型変換・整形

最初の壁を越えた後は、パワークエリの標準機能（フィルター、フィルダウン、フォルダ結合）と組み合わせることで、関数パイプラインよりはるかに簡潔なCOBOL帳票処理が可能になります。

📌 シリーズリンク：
【完全版】MIDB切り出し・浄化・型変換 … 固定長データの基本的な切り出しと型変換。
【応用編】不要行の除外と横展開 … ROWフラグによる行フィルタリング。
【超絶技巧編】ヘッダー紐付け・フィルダウン … 親情報のフィルダウンとMIDB分解パイプライン。
【不規則明細・完結編】行数バラバラの正規化 … 不規則明細とマルチラインレコード。
【パワークエリ編】不要行除去と一括結合 … MIDBの壁と越えた後の優位性。関数との使い分け。
【参考資料】M言語でMIDB自作関数を作る（本ページ） … 壁を越える具体的テクニック集。

【参考資料】M言語でMIDB相当の自作関数を作る― パワークエリ最大の壁を越えるための完全テクニック集 ―