【関数超絶技巧編】凶悪COBOL帳票の撃破｜ヘッダー紐付け・フィルダウンをExcel関数で自動化
エクセルでCOBOL帳票や固定長データを整形したい方向けに、Excel関数・Power Query・MIDBなどを使って列分割、不要行除去、帳票データ変換を進める実務手順を扱います。
この記事の内容
💡 Excel関数の限界に挑む「ラスボス」前哨戦
【応用編】（基礎ロジック編）では、「不要行の除外」と「2行固定の横展開」という基本の不規則帳票の倒し方を学びました。この応用編のテクニックに、今回解説する「上部にある会社名などの親データを、下にある明細行に引き継ぐ（紐付ける）」というフィルダウン処理を加えるだけで、実務で遭遇する大抵のCOBOL帳票は処理できるようになります。
※ちなみに、この「上の行を下にコピーして埋める」作業は、パワークエリを使える環境であれば『下方向へコピー』という機能で一瞬で終わるものです。しかし、関数しか使えない環境でこれを完全自動化するには、独特な思考回路（超絶技巧）が必要になります。
【試練3】上の親グループ情報（会社名・部門など）をすべての明細に紐付ける
データベースとして成立させるには、すべての明細行に対して「その親が誰なのか（どの会社・どの伝票の明細か）」というキーの列を持たせなければなりません。これを「紐付け」または「フィルダウン（空白埋め）」と呼びます。
【攻略法】貼付シートのA列で「自分自身の上のセル」を振り返って引き継ぐ
伝統的で最強の空白埋め数式（全バージョン対応）
応用編で学んだ「3層シート構成」のうち、貼付シート側の作業列（A列など）に、これ専用の数式を仕込みます。ポイントは、「もし今の行がヘッダー行ならその文字を出し、そうでない（明細行やゴミ行の）場合は、一つ上の自分の作業列（A列）の値をそのまま参照して引き継ぐ」という、数式によるバケツリレーです。
貼付シート!A2セル（会社名引継ぎ列）
| A | B | |
| 1 | 会社名の引継ぎ列 | 貼り付けたデータ |
| 2 | =IF(MIDB(B2,1,2)="■", B2, A1) 結果: ■...第一商事 |
■ 伝票番号：9001 取引先：㈱第一商事 |
| 3 | =IF(MIDB(B3,1,2)="■", B3, A2) 結果: ■...第一商事 (A2を参照) |
------------------------------------------ |
| 4 | =IF(MIDB(B4,1,2)="■", B4, A3) 結果: ■...第一商事 (A3を参照) |
A 101 消しゴム 100 |
| 5 | =IF(MIDB(B5,1,2)="■", B5, A4) 結果: ■...第一商事 (A4を参照) |
A 102 えんぴつ 150 |
| 6 | =IF(MIDB(B6,1,2)="■", B6, A5) 結果: ■...第二物産 (新データに切替) |
■ 伝票番号：9002 取引先：第二物産 |
| 7 | =IF(MIDB(B7,1,2)="■", B7, A6) 結果: ■...第二物産 (A6を参照) |
A 990 ノート 200 |
関数が A1、A2… と計算していく過程において、「もし親行ならB列から取得し直す、親でなければ一つ上のA列（＝自分がさっき保持した親情報）をそのまま下に流す」という仕組みです。これこそが実務で脈々と使われてきた最強の「関数フィルダウン」の手法です。あとは抽出シート側で、明細をワープさせた隣の列でこのA列の作業列を拾ってくれば、全てのデータに共通の会社名が紐付きます。
Excel 2024 / Microsoft 365 など
SCAN 関数が使える環境では、上から下へ値を記憶しながらスピル展開させる次世代の力技も可能です。
SCAN関数版（対応環境）
【完成形】「親の紐付け」と「明細フラグ（ROW）」の合わせ技で1行のデータに仕上げる
ここまで学んだ「不要行の行取り（ROWフラグ）」と、今回の「親情報のフィルダウン（空白埋め）」を組み合わせることで、複雑な帳票が完璧な1行のデータベースとして一発展開されます。
貼付シートに「フラグ」と「フィルダウン」の2つの作業列を持つ
実務では、貼付シート側に作業列を2つ（A列とB列など）設けて、それぞれの役割を分担させます。判定フラグとなる列を一番左（A列）に置くのが、視認性も高く、抽出関数でキーとする際にも一番自然な配置です。
貼付シート!A列（明細行フラグ）
貼付シート!B列（親情報引継ぎ）
| A | B | C | |
| 1 | 明細フラグ(ROW) | 親情報引継ぎ | 貼り付けたデータ |
| 2 | ■...第一商事 | ■ 伝票番号：9001 取引先：㈱第一商事 | |
| 3 | ■...第一商事 | ------------------------------------------ | |
| 4 | 4 | ■...第一商事 | A 101 消しゴム 100 |
| 5 | 5 | ■...第一商事 | A 102 えんぴつ 150 |
| 6 | ■...第二物産 | ■ 伝票番号：9002 取引先：第二物産 | |
| 7 | 7 | ■...第二物産 | A 990 ノート 200 |
このように、「明細行にだけ行番号が入った列（A列）」と「親情報がすべての行に浸透した列（B列）」の2つの武器が揃いました。あとはこのフラグを元に、次のように別シート（抽出シート）で仕上げを行います。
抽出シートへ「明細生データ」と「親情報」をワープさせ、D列以降のMIDBで切る
抽出シートのA列に「明細の生データ（貼付シートのC列）」を、B列に「親情報（貼付シートのB列）」を抽出します。ここで初めて FILTER 関数や INDEX + SMALL 関数を使って、「貼付シートA列のフラグ」をキーに明細行だけを吸い上げます。
解法1：FILTER関数（Excel 2021 / 2024 / Microsoft 365 など）
抽出!A2（明細）
抽出!B2（親情報）
解法2：INDEX+SMALL（全バージョン対応）
抽出!A2（明細）
抽出!B2（親情報）
INDEX+SMALL方式では、数式を明細の件数よりも多くの行にコピーした場合、SMALLが参照できる値を失い
#NUM! エラーになります。IFERROR で囲んで空文字 "" を返すことで、データがない行を空白にして見た目を綺麗に保ちます。一方、FILTER関数はスピルで必要な行数だけ自動展開されるため、この処理は不要です。
【速度チューニング】INDEX+SMALLが遅い場合の奥の手 ― MATCH方式
上記の INDEX+SMALL 方式はシンプルで分かりやすいのですが、実はSMALL関数は内部的にデータ全体をソート（並べ替え）してからk番目を探すという処理を行っています。そのため、貼付シートの行数が数万行を超えてくると、抽出シートの各行で毎回ソートが走り、体感的に目に見えて遅くなることがあります。
この速度問題を根本から解決するのが、貼付シートA列のフラグをROW（行番号）ではなく「連番（1, 2, 3…）」にして、抽出シート側では SMALL の代わりに MATCH 関数で行位置を取得する方式です。
貼付シート!A列（連番フラグに変更）
抽出シート!A2（MATCH方式で抽出）
抽出シート!B2（親情報をMATCH方式で抽出）
| A | B | C | |
| 1 | 連番フラグ | 親情報引継ぎ | 貼り付けたデータ |
| 2 | 0 | ■...第一商事 | ■ 伝票番号：9001 取引先：㈱第一商事 |
| 3 | 0 | ■...第一商事 | ------------------------------------------ |
| 4 | 1 | ■...第一商事 | A 101 消しゴム 100 |
| 5 | 2 | ■...第一商事 | A 102 えんぴつ 150 |
| 6 | 2 | ■...第二物産 | ■ 伝票番号：9002 取引先：第二物産 |
| 7 | 3 | ■...第二物産 | A 990 ノート 200 |
SMALL関数は呼ばれるたびにA列全体の中からk番目に小さい値を探す（＝毎回ソートに近い処理を行う）ため、行数が増えるほど急激に遅くなります。一方、MATCH関数は「指定した値がA列の何行目にあるか？」を探すだけの単純な検索処理です。連番（1, 2, 3…）であれば各値がA列に1つしか存在しないため、見つかった瞬間に処理が完了します。この差がデータ量が大きくなるほど如実に表れ、数万行規模のデータでは体感で数倍～数十倍の速度差になることがあります。
上の
=IF(MIDB(C2,5,1)="-", A1+1, A1) のような「条件を満たしたら+1、そうでなければ維持」という数式は、慣れないとイメージしにくいかもしれません。まずは分かりやすい ROW + SMALL 方式で組んでみて、速度的に問題が出たときの切り札としてこのMATCH方式を覚えておくのがおすすめです。
| A | B | C | D | E | F | |
| 1 | 明細データ (←貼付C列) | 親情報 (←貼付B列) | No. | 会社名 (MIDB) | 商品名 (MIDB) | 金額 (MIDB) |
| 2 | A 101 消しゴム 100 | ■...㈱第一商事 | 1 | =TRIM(MIDB(B2,21,20)) 結果: ㈱第一商事 |
=TRIM(MIDB(A2,11,8)) 結果: 消しゴム |
=MIDB(A2,20,5)*1 結果: 100 |
| 3 | A 102 えんぴつ 150 | ■...㈱第一商事 | 2 | =TRIM(MIDB(B3,21,20)) 結果: ㈱第一商事 |
=TRIM(MIDB(A3,11,8)) 結果: えんぴつ |
=MIDB(A3,20,5)*1 結果: 150 |
| 4 | A 990 ノート 200 | ■...第二物産 | 3 | =TRIM(MIDB(B4,21,20)) 結果: 第二物産 |
=TRIM(MIDB(A4,11,8)) 結果: ノート |
=MIDB(A4,20,5)*1 結果: 200 |
A列 … 貼付シートのC列（明細の生データ）をそのまま抽出。E列・F列のMIDBの参照元。
B列 … 貼付シートのB列（親情報引継ぎ列）をそのまま抽出。D列のMIDBの参照元。
C列 … 連番（No.）。「今何件目の明細か」を目視で確認するための目印。
=ROW()-1 等で入れる。D列 … B列（親情報）からMIDBで会社名だけを切り出す。
E列 … A列（明細データ）からMIDBで商品名を切り出す。
F列 … A列（明細データ）からMIDBで金額を切り出し、
*1 で数値化する。
なぜ「貼付シート」ではなく「抽出シート」でMIDBを使うのか？
この構成にしている理由は、速度・効率・シンプルさの3点に集約されます。
実務で扱うCOBOL帳票の貼付シートは、ヘッダー行・罫線行・空白行・合計行などのゴミ行を含めると10万行～30万行に膨れ上がることが珍しくありません。この貼付シートにMIDBやTRIMなどの文字列操作関数を直接埋め込むと、最終的に不要となるゴミ行に対しても毎回すべての関数が再計算されます。これが数十万行×複数列に及ぶと、再計算のたびにExcelの動作が極端に重くなり、最悪の場合フリーズや応答なしに陥ります。
一方、本記事の方式では、貼付シート側の作業列はIF関数とROW関数という極めて軽い数式だけに留めておき、重い文字列操作（MIDB・TRIM等）は抽出シート側で「必要な明細行だけ」に対して実行します。
例えば、貼付シートが30万行あってもそのうち有効な明細行が5,000行であれば、MIDBが計算されるのはたった5,000回です。30万回と5,000回では、計算負荷に60倍もの差が生まれます。
貼付シートのC列の隣（D列・E列…）に直接
=TRIM(MIDB(C2,11,8)) のような数式を30万行分入れること。ゴミ行も含めてすべて計算されるため、動作が致命的に重くなります。
① 貼付シートの作業列は IF + ROW だけなので数十万行あっても軽い
② 抽出シートのA列・B列で必要な明細行だけを綺麗に吸い上げる
③ 吸い上げた後の必要な行だけにMIDB・TRIMで整形するので計算回数が最小限
④ 「抽出→整形」の流れが一方向なので、数式の構成がシンプルで読みやすい
つまり、「貼付シートでは仕分けだけ。重い整形処理は抽出シートで、必要な行だけに行う」という役割分担こそが、大量データでもExcelをフリーズさせずに正確なデータベースを完成させる最大のポイントです。
【さらに先へ】明細行の数が「完全にバラバラ」の真のラスボス
ここまでマスターすれば大抵の帳票は怖くありませんが、世の中には「ある伝票は明細が1行、別の伝票は3行…」という、1レコードの行数が完全に不規則な真のラスボス帳票が存在します。このような「もう算数（WRAPROWS等）での折り返しすら通用しない」限界の帳票を、関数だけで正規化してデータベース化する究極のパイプライン思想については、別ページの 【関数限界突破編】完全不規則明細の正規化展開 で解説します。