【複合関数編】複雑なCOBOL帳票の整理|ヘッダー紐付け・フィルダウンをExcel関数で自動化
ヘッダー行の情報(伝票番号・日付など)を下の明細行へ引き継ぐ「フィルダウン」を関数で行い、親子構造の帳票を1行1レコードの表に整える複合式を解説します。
💡 親情報を明細へ紐付ける設計
【応用編】(基礎ロジック編)では、「不要行の除外」と「2行固定の横展開」という基本の不規則帳票の倒し方を学びました。この応用編のテクニックに、今回解説する「上部にある会社名などの親データを、下にある明細行に引き継ぐ(紐付ける)」というフィルダウン処理を加えるだけで、実務で遭遇する大抵のCOBOL帳票は処理できるようになります。
※ちなみに、この「上の行を下にコピーして埋める」作業は、パワークエリを使える環境であれば「下方向へコピー」で定型処理にできます。処理時間はデータ量やPC環境で変わります。関数だけで同じ流れを自動化する場合は、作業列を含む設計が必要になります。
【試練3】上の親グループ情報(会社名・部門など)をすべての明細に紐付ける
データベースとして成立させるには、すべての明細行に対して「その親が誰なのか(どの会社・どの伝票の明細か)」というキーの列を持たせなければなりません。これを「紐付け」または「フィルダウン(空白埋め)」と呼びます。
【攻略法】貼付シートのA列で「自分自身の上のセル」を振り返って引き継ぐ
従来関数で行う空白埋め(Excel 2016以降対応)
応用編で学んだ「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列に保持した親情報を引き継ぐ」という仕組みです。抽出シート側で明細と一緒にこの作業列を参照すれば、各明細に親情報を紐付けられます。
Excel 2024 / Microsoft 365 など
SCAN 関数が使える環境では、上から下へ値を記憶しながらスピル展開させる次世代の力技も可能です。
貼付シート!A2(SCAN関数版・対応環境)
SCAN は入力と同じ大きさの配列を返すため、B:B のような列全体を渡すと104万行分の展開を試み、#SPILL! や長時間の応答停止につながります。必ず実データ範囲に絞ってください。50000行は例です。
【完成形】「親の紐付け」と「明細フラグ(ROW)」の合わせ技で1行のデータに仕上げる
ここまで学んだ「不要行の行取り(ROWフラグ)」と、今回の「親情報のフィルダウン(空白埋め)」を組み合わせ、複雑な帳票を1明細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(Excel 2016以降対応)
抽出!A2(明細)
抽出!B2(親情報)
50000行は例です。実データの最大行に合わせて調整するか、Excelテーブルの構造化参照を使ってください。
INDEX+SMALL方式では、数式を明細の件数よりも多くの行にコピーした場合、SMALLが参照できる値を失い
#NUM! エラーになります。IFERROR で囲んで空文字 "" を返すことで、データがない行を空白にして見た目を綺麗に保ちます。一方、FILTER関数はスピルで必要な行数だけ自動展開されるため、この処理は不要です。
【速度チューニング】INDEX+SMALLが遅い場合の奥の手 ― MATCH方式
上記の INDEX+SMALL 方式はシンプルで分かりやすいのですが、貼付シートの行数が数万行を超えてくると、目に見えて遅くなることがあります。SMALL関数の内部の処理はMicrosoftの資料に書かれていないため断定できませんが、下の実測でも、行数が増えるほど時間が大きく伸びました。
この速度問題を軽くする候補が、貼付シートA列のフラグをROW(行番号)ではなく「連番(1, 2, 3…)」にして、抽出シート側では SMALL の代わりに MATCH 関数で行位置を取得する方式です。
抽出方式の比較はこちら:【FILTER代替】4つの複数抽出方式を比較
ROW+SMALL、AGGREGATE、上行参照など、行数が増えたときの方式選択を汎用データで比較しています。
貼付シート!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 |
5行に1行を抽出する同じデータで、この記事の2つの式を1回計算した時間です。5,000行:SMALL 約0.14秒、MATCH 約0.02〜0.04秒。20,000行:SMALL 約2.3秒、MATCH 約0.02秒。60,000行:SMALL 約26秒、MATCH 約0.08秒(Excel 16.0 ビルド20527、2026-11-03)。時間はデータとPCで変わるので、実データでも比べてください。
上の
=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を使うのか?
目的は、文字列を切り出す対象を必要な明細行に絞り、数式の役割を分けることです。貼付シートがヘッダー行・罫線行・空白行・合計行を含む10万~30万行になる場合、全行・複数列にMIDBやTRIMを配置すると、不要行も計算対象になり、再計算が重くなる可能性があります。
本記事では貼付シートをIF・ROWによる仕分けに留め、抽出後の明細だけにMIDB・TRIMを適用します。例えば30万行のうち明細が5,000行なら、文字列切り出しの対象数は30万行から5,000行(60分の1)になります。ただし、実際の速度は数式・参照範囲・Excelのバージョン・PC環境でも変わるため、コピーしたブックで確認してください。
貼付シートでは行種を判定し、抽出シートでは必要な明細と親情報だけをMIDB・TRIMで整形します。全行へ切り出し式を置く案と、必要行へ絞る案を実測で比較してください。
再計算が重い場合、数式の書き方以外に揮発性関数とメモリ側の要因もあります。
揮発性関数とは?Excelが重くなるのを防ぐ
Excelが遅い原因=メモリスワップ
【さらに先へ】明細行数が不規則な帳票
伝票によって明細が1行、3行…と異なる帳票では、固定行数を前提としたWRAPROWS等の折り返しは使えません。行種の判定から正規化する設計は、別ページの 【不規則明細・完結編】完全不規則明細の正規化展開 で解説します。