【関数応用編】不規則なCOBOL帳票の撃破（不要行の除外・縦持ちデータの横展開）
エクセルでCOBOL帳票や固定長データを整形したい方向けに、Excel関数・Power Query・MIDBなどを使って列分割、不要行除去、帳票データ変換を進める実務手順を扱います。
この記事の内容
💡 関数だけで「悪路」を走破する（基礎ロジック編）
実務で頻繁に遭遇する「不要なヘッダー行などが混ざっているデータ」や「1レコードが2行に分かれている縦持ちデータ」。厄介に見えますが、実はこれらは数あるCOBOL帳票の中ではまだ「比較的シンプルな簡単な部類」です。
まずはこのページで、関数だけで「不要行を弾き飛ばし、1レコード1行の綺麗な状態を作る基本ロジックと流れ」を完璧に理解してください。
※「上部にある会社名をすべての明細行にコピーして持ってくる処理」や「明細行が2～3行どころではない、完全に不規則な帳票」など、実務特有のさらに凶悪なフォーマットについては、このロジックを応用した 【関数超絶技巧編】ヘッダー紐付けと不規則明細の完全攻略 の別リンクで解説しています。
【0】実務で絶対にやるべき「3層シート構成」と自動処理フロー
実は、不規則なCOBOL帳票を関数で相手にする際、同じシート内で抽出から切り出し（MIDB）まで全てを書こうとすると、数式がカオスになり一瞬で破綻します。実務では数式を組む前に、必ず以下の「3層構造（パイプライン）」になるようにExcelファイル（シート構成）を設計します。
1. 「貼付」シート
システムから落としたテキストデータを右側のB列にただ貼るだけの生データシート。
左側のA列（作業列）にはあらかじめ =IF(条件, ROW(), "") などの判定フラグ（ROW関数）を1万行下まで仕込んでおき、右にデータが貼られた瞬間に裏で自動計算させます。
2. 「抽出」シート
別シートから FILTER 関数や INDEX + SMALL などを使い、貼付シートのA列・B列を参照して「必要な明細行だけ」をワープ（抽出）させてきます。
さらに横の列で MIDB を使ってデータをスライスします。
3. 「出力」シート
抽出シートで作った「綺麗なデータベース」だけを参照した、最終的な提出・閲覧用シート（またはピボットテーブル）です。
余計な作業列は一切見せません。
この流れを一度構築してしまえば、翌月からは「1.貼付シート」のB列を新しいテキストデータで上書きペーストするだけで、連番付与→別シート抽出→切り出し→出力が全自動で走り、数秒で業務が完了します。以降の解説は、すべてこの「別シートから抽出する」前提での数式となります。
【試練1】「見出し」や「ページ数」等の不要行が混ざっている
これを関数で抽出するには、不要行を無視して「明細行」の条件を満たす行だけを上に詰めて取得する必要があります。
【攻略手順】全自動で明細行だけを抽出する2ステップ
ステップ1：貼付シートに「判定フラグ（連番）」を作る
抽出を始める前に、まずはベースとなる貼付シート側のA列（作業列）に、「右の行が明細行かどうか」を判定するフラグを仕込んでおきます。判定条件に合致した明細行だけに「その行の行番号」を付与する数式です。
貼付シート!A2セル（作業列）
貼付シート側のイメージ（常にA列が作業列で、B列にコピペする運用）
| 行 | A列（作業列） | B列（貼り付けたデータ） |
|---|---|---|
| 1 | === 2026/03/18 === 売上明細 === PAGE 1 === | |
| 2 | 2 | 1001-50 新宿本店 0050000 |
| 3 | ------------------------------------------ | |
| 4 | 4 | 1002-88 渋谷支店 0120000 |
MIDB 関数を使って「B列の5バイト目がハイフンか」を判定し、もしそうなら ROW() 関数で自分自身の行番号を出力します。
よくある「COUNTIF」や「MAX($A$1:A1)+1」を使った連番作成のほうが直感的で便利に見えますが、20万行の大容量データで使うとExcelがフリーズ・激重になる最大の原因となります。そのため、単独で計算が完結する ROW() をフラグに使うのが大容量データにおける最速の鉄則です。（なぜ急激に重くなるかの詳細な理由は、記事末尾の参考コラムをご覧ください）
ステップ2：抽出シートへ「明細行だけ」をワープさせる
フラグが完成したら、次に計算を行う抽出シート側のA列へ数式を入れ、先ほど作った連番を頼りに明細行だけを吸い上げます。Excelのバージョンに合わせて以下のどちらかの手法を使います。
解法A：FILTER関数で一発抽出（Excel 2021 / 2024 / Microsoft 365 など）
最新の環境なら、ステップ1の作業列すら見に行かず、この数式一つで条件判定から抽出まで（スピルで）完結します。
抽出シート!A1セルに入力
解法B：フラグ行番号 ＋ INDEX ＋ SMALL（全ver対応・20万行処理の最速解）
古いExcelを使わざるを得ない場合や、万単位の超大容量データがある場合は、抽出側の行番号を利用して、ステップ1で付けた貼付シートの「行番号フラグ（A列）」を SMALL 関数で小さい順から探しに行くのが最も安全で高速です。
抽出シート!A1セル（抽出先）
SMALL 関数が「貼付シートのA列の中で、一番小さい行番号、次に小さい行番号…」と順番に拾ってくるため、空白の行（ゴミ行）が見事にジャンプされ、上に詰まった状態で綺麗なデータとしてワープしてきます。重い累積計算を避けた、レガシー環境での究極の抽出術です。
【試練2】1つのデータが「2行」の縦持ちになっている
横幅（印字桁数）の制限により、COBOL帳票ではしょっちゅう発生する悲劇です。1件のデータが2行に分かれているため、1つの行に横持ちさせて（横列に広げて）からMIDBで切る必要があります。
魔法の解決策：WRAPROWS関数（Excel 2024 / Microsoft 365 などの対応環境）
縦にずらっと並んだ貼付シート側のデータを、指定した数（今回は「2列」）で強引に折り曲げて表にします。
抽出シート!A1セルに入力
これを打つだけで、スピルによって抽出シート側に「左の列に1行目の情報」「右の列に2行目の情報」が綺麗に横並びになります。COBOLの縦持ちデータという悪魔を一瞬で浄化する神関数です。
基本解法：フラグ行番号 ＋ INDEX ＋ SMALL で横持ちに変換（全バージョン対応）
WRAPROWSが無い環境（Excel 2021以前、または更新チャネル・買い切り版・職場PCの管理設定などで未対応の環境）では、試練1と全く同じ「フラグを作って SMALL で拾う」アプローチが最も確実で安全です。 今回は2行で1セットなので、A列（1行目データ用）にだけ奇数行を判別するフラグを作ります。
| A（1行目フラグ） | B（貼り付けたデータ） | |
|---|---|---|
| 1 | 1 | 店舗ID：1001 店舗名：新宿本店 売上：50,000 |
| 2 | （空白） | 住所：東京都新宿区〇〇 電話：03-xxxx-xxxx |
| 3 | 3 | 店舗ID：1002 店舗名：渋谷支店 売上：120,000 |
| 4 | （空白） | 住所：東京都渋谷区〇〇 電話：03-yyyy-yyyy |
| 5 | 5 | 店舗ID：1003 店舗名：池袋支店 売上：80,000 |
| 6 | （空白） | 住所：東京都豊島区〇〇 電話：03-zzzz-zzzz |
A列は奇数行（1行目データ）だけに
=IF(MOD(ROW(),2)=1, ROW(), "") でフラグを立てます。これが試練1と同じ「SMALL関数で拾うための目印」になります。
| A（連番） | B（1行目データ） | C（2行目データ） | |
|---|---|---|---|
| 1 | 1 | 店舗ID：1001 店舗名：新宿本店 売上：50,000 | 住所：東京都新宿区〇〇 電話：03-xxxx-xxxx |
| 2 | 2 | 店舗ID：1002 店舗名：渋谷支店 売上：120,000 | 住所：東京都渋谷区〇〇 電話：03-yyyy-yyyy |
| 3 | 3 | 店舗ID：1003 店舗名：池袋支店 売上：80,000 | 住所：東京都豊島区〇〇 電話：03-zzzz-zzzz |
抽出シート!B1セル（1行目データ：SMALLの「順位」にA列の連番を使う）
抽出シート!C1セル（2行目データ：取得した行番号に ＋1 する）
「A列のフラグから1行目データを拾う」、そして2行目のデータは「拾った行番号に
+ 1 した行を拾う」という仕組みです。あとはC列以降で
MIDB や TRIM を使って、
店舗ID・店舗名・売上・電話番号などを切り出せば、
COBOL帳票の縦持ちデータを通常のデータベース形式に変換できます。
上記のSMALL方式は「フラグ列を作って拾う」王道の手法ですが、WRAPROWSが無い機能限定環境で、作業列を増やしたくない場合は、「行番号（ROW）」を使った算数で、貼付シートのデータ列（B列）から自分の好きな行だけを規則的に狙い撃ちします。抽出シートのA列（1行目データ用）とB列（2行目データ用）にそれぞれ以下の数式を入れ、下にコピーします。
抽出シート!A1セル（1行目用）
抽出シート!B1セル（2行目用）
仕組みは単純な算数です。下にコピーして ROW(A2) になると (2-1)*2+1 = 3行目 が貼付シートから取得され、次は 5行目、その次は 7行目…と、奇数行・偶数行だけを正確に拾い集めてくれます。作業列が不要なのが利点ですが、「必ず2行1セットで途切れなく並んでいる」ことが前提条件です。
上記のアプローチで「きれいな横並び1レコード」のデータさえ作ってしまえば、あとは基本編に戻り、
MIDB や TRIM 関数を使って必要な部分をスライスしていくだけでデータベースの完成です。
【参考コラム】なぜ作業列で COUNTIF や MAX を使うとExcelがフリーズ（激重に）するのか？
作業列で連番を作る際、ネットの解説記事ではよく以下のような数式が「便利だから」「素直だから」という理由で紹介されます。しかし、これらの数式を数十万行のCOBOLデータに使ってしまうと、Excelが再計算地獄に陥り完全にフリーズ（あるいは激重・応答なし）してしまいます。
危険な累積範囲の数式例（大容量データの場合）
なぜ重くなるのか？（Nの2乗の罠）
原因は $A$1:A1 という「起点を固定して下に伸びていく範囲指定（累積計算）」にあります。
| 行位置 | 毎回振り返るセル数 |
|---|---|
| 1行目 | 1個のセルを振り返る |
| 1万行目 | 1万個のセルを毎回振り返る |
| 10万行目 | 10万個のセルを毎回振り返る |
| 20万行目 | 過去の20万個のセルを毎回すべて振り返って計算し直す |
このように行が下に行くほど、Excelが振り返って処理すべきセルの数が雪だるま式に倍増し、計算量が「Nの2乗」として莫大に膨れ上がります。これが大容量データでExcelが応答不能になる最大の理由です。
実務での正解：単独セルで完結させる
そのため何万行もあるCOBOL帳票の実務においては、「過去の膨大なセルを振り返る関数」を絶対に避けなければなりません。この記事のステップ1でご紹介した =ROW() などをフラグに使う手法は、他を振り返らず、自分自身の行番号という1つの情報だけを出力して計算が完結する（プログラムの世界で言う O(N) で終わる）ため、20万行あっても一瞬で計算が完了します。大容量データを関数で処理する本質的で必須のテクニックです。