【不規則明細・完結編】行数がバラバラなデータの「正規化展開」
1件あたりの明細行数がそろっていない帳票を、行の種類を判定しながら親情報と結び付け、正規化した表へ展開するシリーズ完結編です。
この記事の内容
💡 伝票ごとに明細行数が違う帳票の攻略
前回の複合関数編では、「ヘッダー行(親情報)を明細行に紐付けるフィルダウン」と「フラグ+抽出+MIDB分解」のパイプラインを学びました。
あの記事で紹介したデータは、各伝票の明細行数がたまたま揃っていました。本記事では、伝票ごとに明細件数が異なる場合でも、同じ行種判定を使えることをサンプルで確認します。
・同じ帳票仕様で明細件数だけが異なる場合、既存のパイプラインを適用できることを確認する。
・追加設計が必要な「マルチラインレコード」について、考え方を押さえる。
【2】今回の帳票データ
以下のようなCOBOL帳票を想定します。第一商事は3件、第二物産は2件、第三工業は4件と、伝票ごとに明細行の件数がバラバラです。複合関数編と同じフォーマット・同じバイト位置ですが、件数だけが異なります。
ヘッダー行は「■」で始まり、明細行は先頭付近に「A」があります。区切り線(---)やその他の行は不要行です。本サンプルでは前編と同じ判定条件を使います。
【3】既存の3ステップで正規化する
前編で構築したパイプラインを、本サンプルへ適用します。流れを確認しましょう。
Step 1 ― 貼付シート:フラグ列とフィルダウン列を作る
貼付シートのB列にデータを貼り付け、A列(フラグ列)とC列(フィルダウン列)に数式を入れます。
A列(フラグ列)の数式 ― A2に入れて下方コピー
明細行の判定位置(3バイト目)に「A」があればROW番号を返し、それ以外は空白にします。
C列(フィルダウン列)の数式 ― C2に入れて下方コピー
行の先頭が「■」(ヘッダー行)であればそのデータを取り込み、そうでなければ直上セルの値を引き継ぎます。
| A(フラグ) | B(元データ) | C(フィルダウン) | |
| 1 | フラグ | 元データ | 親情報 |
| 2 | ■伝票 9001 ㈱第一商事 | ■伝票 9001 ㈱第一商事 | |
| 3 | ------------------------------- | ■伝票 9001 ㈱第一商事 | |
| 4 | 4 | A 101 消しゴム 500 | ■伝票 9001 ㈱第一商事 |
| 5 | 5 | A 102 えんぴつ 300 | ■伝票 9001 ㈱第一商事 |
| 6 | 6 | A 103 ノート 800 | ■伝票 9001 ㈱第一商事 |
| 7 | ------------------------------- | ■伝票 9001 ㈱第一商事 | |
| 8 | ■伝票 9002 ㈱第二物産 | ■伝票 9002 ㈱第二物産 | |
| 9 | ------------------------------- | ■伝票 9002 ㈱第二物産 | |
| 10 | 10 | A 201 ボールペン 120 | ■伝票 9002 ㈱第二物産 |
| 11 | 11 | A 202 クリップ 60 | ■伝票 9002 ㈱第二物産 |
| 12 | ------------------------------- | ■伝票 9002 ㈱第二物産 | |
| 13 | ■伝票 9003 ㈱第三工業 | ■伝票 9003 ㈱第三工業 | |
| 14 | ------------------------------- | ■伝票 9003 ㈱第三工業 | |
| 15 | 15 | A 301 定規 150 | ■伝票 9003 ㈱第三工業 |
| 16 | 16 | A 302 ハサミ 400 | ■伝票 9003 ㈱第三工業 |
| 17 | 17 | A 303 テープ 90 | ■伝票 9003 ㈱第三工業 |
| 18 | 18 | A 304 のり 110 | ■伝票 9003 ㈱第三工業 |
フラグもフィルダウンも「今の行がヘッダーか明細か不要行か」を判定します。本サンプルでは明細件数を判定条件にしていないため、件数が異なっても同じ数式を使えます。
Step 2 ― 抽出シート:明細行だけを取り出す
抽出シートでは、フラグ列に値がある行(=明細行)だけを取り出します。
Excel 2021 / 2024 / Microsoft 365 などの場合(FILTER関数)
50000行は例です。実データの最大行に合わせて調整するか、Excelテーブルの構造化参照を使ってください。
Excel 2016以降対応(INDEX + SMALL + IFERROR)
抽出シート!A2(元データ)
抽出シート!B2(親情報)
| A(元データ) | B(親情報) | |
| 1 | 元データ | 親情報 |
| 2 | A 101 消しゴム 500 | ■伝票 9001 ㈱第一商事 |
| 3 | A 102 えんぴつ 300 | ■伝票 9001 ㈱第一商事 |
| 4 | A 103 ノート 800 | ■伝票 9001 ㈱第一商事 |
| 5 | A 201 ボールペン 120 | ■伝票 9002 ㈱第二物産 |
| 6 | A 202 クリップ 60 | ■伝票 9002 ㈱第二物産 |
| 7 | A 301 定規 150 | ■伝票 9003 ㈱第三工業 |
| 8 | A 302 ハサミ 400 | ■伝票 9003 ㈱第三工業 |
| 9 | A 303 テープ 90 | ■伝票 9003 ㈱第三工業 |
| 10 | A 304 のり 110 | ■伝票 9003 ㈱第三工業 |
第一商事は3件、第二物産は2件、第三工業は4件 ― 件数がバラバラでも、明細行だけが正しく親情報と一緒に抽出されています。
抽出方式の比較はこちら:【FILTER代替】4つの複数抽出方式を比較
ROW+SMALL、AGGREGATE、上行参照など、行数が増えたときの方式選択を汎用データで比較しています。
Step 3 ― MIDB分解:固定長データを列に分ける
抽出シートのデータをMIDBで切り出して、最終的な正規化テーブルを作ります。ここも複合関数編と同じ手法です。
C列:取引先
D列:商品コード
E列:商品名
F列:金額
最終的な正規化テーブル
| 取引先 | 商品コード | 商品名 | 金額 |
|---|---|---|---|
| ㈱第一商事 | 101 | 消しゴム | 500 |
| ㈱第一商事 | 102 | えんぴつ | 300 |
| ㈱第一商事 | 103 | ノート | 800 |
| ㈱第二物産 | 201 | ボールペン | 120 |
| ㈱第二物産 | 202 | クリップ | 60 |
| ㈱第三工業 | 301 | 定規 | 150 |
| ㈱第三工業 | 302 | ハサミ | 400 |
| ㈱第三工業 | 303 | テープ | 90 |
| ㈱第三工業 | 304 | のり | 110 |
各ステップは「今の行がヘッダーか明細か不要行か」という行種の判定を使います。本サンプルのように帳票仕様と判定条件が同じなら、伝票ごとの明細件数が異なっても適用できます。
【4】なぜ行数バラバラでも同じ数式で処理できるのか
この方式では、フラグ列が「3バイト目がAなら明細」、フィルダウン列が「先頭が■ならヘッダー」と各行を判定し、抽出シートはフラグが付いた行を拾います。明細件数ではなく行種を条件にするため、本サンプルの3件・2件・4件の各伝票を同じ流れで処理できます。
行種を安定して判定できる帳票では、明細件数に依存しない処理を組み立てられます。判定文字や行構造が異なる帳票では、条件の調整が必要です。
【5】速度面の注意
複合関数編で解説した速度面の注意はここでも同様に当てはまります。
COBOL帳票は貼付シートの行数が10万〜30万行になることがあります。全行にMIDB分解を配置すると計算対象が増えるため、貼付シートではフラグとフィルダウンを行い、MIDB分解は抽出シート(明細行だけに絞った後)で実行する構成を基本とします。
例えば30万行の帳票で明細行が5,000行なら、MIDB分解の対象が60分の1に削減されます。この設計思想がパイプライン方式の速度上の利点です。
MATCH方式による参照や、対応環境でのSCAN+FILTERによる構成例については複合関数編を参照してください。
【6】追加設計が必要な「1明細が複数行に跨るケース」
「1つの明細データ自体が2行以上に跨って記述されている」場合は、行種判定だけでなく、複数行を1件へまとめる前処理が必要です。
COBOL帳票は印刷帳票のイメージをそのまま出力しているため、1つの明細の情報が人間が見やすいように複数行に分けて記述される場合があります。例えば以下のような形です。
このように1つの明細が2行・3行に分かれている「マルチラインレコード」は、印刷レイアウトの都合でごく当たり前に発生します。各行のMIDB位置(何バイト目に何がある)は固定ですが、1行目にはコードと商品名、2行目には数量と単価、3行目には納期…というように情報が行をまたいで分散しているのが特徴です。
設計の考え方:貼付シートの作業列で「1行にまとめる」
マルチラインレコードに対処するための基本的な考え方は、貼付シートの作業列を増やして、各行からMIDBで必要な部分だけを切り出し、それらを1行に集約するというものです。
固定長の仕様が確認できるデータでは、「明細の1行目は何バイト目にコードがある」「2行目は何バイト目に数量がある」「3行目は何バイト目に納期がある」といった位置の規則を確認できます。まず実データと仕様書を照合して、規則を読み解くことが最初のステップです。
規則性を把握したら、貼付シートに作業列を追加して各行からMIDBでパーツを切り出します。そして明細の1行目の行に対して、直下の2行目・3行目の情報を作業列で横に集約すれば、「元は3行に分かれていたデータが、1行のレコードとして揃っている」状態を作ることができます。あとはその作業列に情報が揃った行だけをフラグで拾い、通常のパイプラインに乗せるだけです。
「明細が常に3行セット」なのか「2行目・3行目があったりなかったり」なのか、帳票によって構造が全く異なります。そのため本ページでは具体的な数式パターンを網羅することはせず、「まず規則性を目視で読み解き、作業列で1行にまとめてからパイプラインに乗せる」という考え方の原則だけをお伝えしています。
明細の1行目・2行目・3行目で「行の種類を区別する判定条件」を別々に用意した上で、それらを横に結合する仕組みが必要になります。フラグの種類が増え、抽出ロジックも複雑化するため、本シリーズで扱っている「1明細=1行」の帳票とは根本的に設計が変わります。
明細の「件数」がバラバラ(伝票ごとに明細行数が違う) → 行種判定を適用できるか確認
1明細が「複数行」に跨っている(マルチラインレコード) → 規則性の解析+作業列での結合が必要で、難易度が大きく上がる
📝 総まとめ
本サンプルでは、伝票ごとの明細件数が異なっても「フラグ+フィルダウン+抽出+MIDB分解」を適用できました。明細件数ではなく、各行の種類を判定しているためです。
一方、「1明細が複数行に跨るマルチラインレコード」では、帳票の規則を読み解き、貼付シートの作業列で1行にまとめる前処理が必要になります。
【基本編】MIDB切り出し・浄化・型変換 … 固定長データの基本的な切り出しと型変換のテンプレ。
【応用編】不要行の除外と横展開 … ROWフラグによる行フィルタリングと固定行数の横展開。
【複合関数編】ヘッダー紐付け・フィルダウン … 親情報のフィルダウンと抽出シートでのMIDB分解。速度チューニング。
【不規則明細・完結編】不規則明細の正規化(本ページ) … 行数バラバラでも同じパイプラインで正規化できることの確認。マルチラインレコードへの対処の考え方。
【補助解説】半角QRで別システムへ入力する実践編 … 整形済みのCOBOL帳票から対象者を抽出し、半角コード・数値だけをQR化して入力省力化につなげる橋渡し。