Excel帳票をVBAで自動処理するとき、つい最初にコードを書き始めたくなります。
「このセルを読んで、あのセルへ書く」
「このシートから値を取得して、一覧表へ転記する」
単純な帳票なら、それでも動くものは作れます。
しかし建設実務の帳票では、処理対象が増えるにつれて、
「橋梁名はどのセルだったか」
「この自治体では路線名の位置が違う」
「同じ評価でも、ある帳票ではセル、別の帳票ではShapeに入っている」
「年度が変わったら列位置がずれた」
といった問題が出てきます。
この状態でセル番地をVBAへ直接書き続けると、コードがそのまま帳票仕様書になってしまいます。
すると仕様を確認するためにVBAを読まなければならず、帳票変更のたびにコード修正が必要になります。
そこで重要になるのが、
業務項目とExcel上の位置関係を、コードを書く前に「セルマッピング」として整理すること
です。
この記事では、Excel VBAによる帳票自動化でセルマッピングをどう設計するか、建設実務を想定して整理します。
セルマッピングとは何か
セルマッピングは、簡単に言えば、
「この業務項目は、Excelのどこにあるか」
を対応表にしたものです。
たとえば橋梁点検帳票なら、
| 業務項目 | シート | セル・位置 | データ型 | 備考 |
|---|---|---|---|---|
| 橋梁名 | 基本情報 | C5 | 文字列 | 必須 |
| 路線名 | 基本情報 | C7 | 文字列 | |
| 橋長 | 基本情報 | F10 | 数値 | m |
| 径間数 | 基本情報 | F12 | 数値 | |
| 健全性 | 点検結果 | H25 | 文字列 | 判定値 |
というように整理します。
VBA側では、
「C5を読む」
のではなく、
「橋梁名として定義された位置から値を取得する」
という考え方へ変えます。
この違いは小さく見えますが、対象帳票が増えるほど重要になります。
セル番地と業務上の意味を分ける
たとえばVBAの中に、
bridgeName = ws.Range("C5").Valueと書いてあったとします。
コードを書いた本人なら、C5が橋梁名だと分かります。
しかし半年後に見返したときや、別の担当者が修正するときには、
「なぜC5なのか」
を帳票と見比べなければなりません。
さらに自治体Bでは橋梁名がD6にある場合、
If municipality = "A" Then
bridgeName = ws.Range("C5").Value
Else
bridgeName = ws.Range("D6").Value
End Ifのような分岐が増えていきます。
この設計では、
業務項目の意味。
自治体ごとの差分。
Excel上の位置。
処理ロジック。
が一つのコードへ混ざります。
セルマッピングを独立させる目的は、この混在を減らすことです。
最初に「何を取り出したいか」を決める
セルマッピングを作るとき、いきなりExcelのセル番地を拾い始める必要はありません。
先に、
最終的に何のデータが必要なのか
を整理します。
たとえば統合マスタを作るなら、
- 橋梁ID
- 橋梁名
- 路線名
- 径間番号
- 部材
- 損傷種類
- 損傷程度
- 評価
- 写真番号
- 所見
など、必要な業務項目を先に決めます。
その後で、
「この項目は元帳票のどこにあるか」
を確認します。
先にセル位置から考えると、
「C5を取得する」
こと自体が目的になりがちです。
本来必要なのはC5ではなく、そこに入っている橋梁名です。
帳票変更に強くするためにも、
業務項目を主語にして、セル位置を従属情報として持つ
方が整理しやすくなります。
入力側と出力側を分ける
帳票自動化では、入力帳票からデータを読むだけでなく、別のExcelや統合マスタへ書き出すことがあります。
この場合は、
「入力元の位置」
と
「出力先の項目」
を分けて管理します。
たとえば、
| 項目ID | 項目名 | 入力シート | 入力位置 | 出力列 |
|---|---|---|---|---|
| BRIDGE_NAME | 橋梁名 | 基本情報 | C5 | bridge_name |
| ROUTE_NAME | 路線名 | 基本情報 | C7 | route_name |
| SPAN_NO | 径間番号 | 点検結果 | B12 | span_no |
のような形です。
こうしておくと、
入力帳票のレイアウトが変わっても、
出力側のデータ構造まで同時に変える必要はありません。
逆に統合マスタ側の列順を変更しても、入力帳票そのものを変更する必要はありません。
この分離ができると、
帳票とデータ構造の間に一枚の翻訳表を置く
ような設計になります。
項目IDを付ける
セルマッピングでは、業務項目に一定のIDを付けておくと扱いやすくなります。
たとえば、
BRIDGE_NAME
ROUTE_NAME
SPAN_NO
DAMAGE_TYPE
DAMAGE_LEVELのような形です。
日本語の「橋梁名」だけでも人間は理解できますが、VBAや他の処理から参照するときは、一定のキーを持っていた方が管理しやすくなります。
特に、
自治体AではC5。
自治体BではD6。
自治体Cでは別シート。
という場合でも、
業務上はすべて BRIDGE_NAME として扱えます。
セル位置が違っても、意味は同じ。
この構造を作ることがセルマッピングの大きな目的です。
固定セルと繰り返しデータを分ける
Excel帳票には、大きく分けて二種類の情報があります。
一つは固定セルです。
橋梁名、路線名、所在地など、
「この帳票では毎回ここに入る」
という情報です。
もう一つは繰り返しデータです。
部材一覧、損傷一覧、写真一覧など、
行数や件数が増減する情報です。
固定セルなら、
BRIDGE_NAME → C5というマッピングで表現できます。
しかし繰り返しデータでは、
開始行。
終了条件。
列位置。
1件を何行で表現するか。
といった情報も必要になります。
たとえば、
| 項目 | 開始行 | 列 | 終了条件 |
|---|---|---|---|
| 部材番号 | 15 | B | 空欄まで |
| 損傷種類 | 15 | F | 部材番号に連動 |
| 損傷程度 | 15 | H | 部材番号に連動 |
というように、固定セルとは別の構造で管理した方が分かりやすい場合があります。
何でも「セル番地一覧」に押し込むのではなく、帳票構造に応じてマッピング方法を分けます。
結合セルは見た目だけで判断しない
建設帳票では結合セルが使われていることがあります。
見た目では大きな一つの入力欄でも、Excel内部では左上セルにだけ値が入っていることがあります。
たとえばC5:E5が結合されていて、橋梁名が表示されている場合、
マッピングとしては、
C5:E5ではなく、
実際に値を持つセルを確認して登録する必要があります。
逆に帳票を出力する場合は、既存の結合状態を崩さないことも重要です。
見た目の帳票レイアウトと、データを取得する位置は同じとは限りません。
そのためセルマッピングを作る際は、
画面上でどこに見えるかではなく、Excel内部でどこに値が存在するか
を確認します。
Shapeやオートシェイプは別の取得方法になる
帳票によっては、重要な情報がセルではなくShapeやオートシェイプに入っていることがあります。
たとえば評価記号、吹き出し、図中の番号などです。
この場合、
Range("C5").Valueのような通常のセル取得では読めません。
Shape名。
座標。
テキスト。
位置関係。
など別の情報を使って判定する必要があります。
つまり、マッピング表には単なるセル番地だけでなく、
「取得方式」
を持たせる方法もあります。
たとえば、
| 項目ID | 取得方式 | 位置・条件 |
|---|---|---|
| BRIDGE_NAME | CELL | C5 |
| DAMAGE_LEVEL | SHAPE | 評価欄周辺Shape |
| PHOTO_NO | CELL | B20 |
という形です。
こうしておくと、
セルとして取得する項目と、Shape解析が必要な項目を処理前に区別できます。
「値」と「見た目」を分ける
Excel帳票では、同じセルでも、
値そのもの。
表示形式。
背景色。
文字色。
罫線。
コメント。
など複数の情報を持っています。
業務上必要なのが数値なら、値だけ取得すればよいかもしれません。
一方、
セルの色で判定区分を表している。
書式によって意味が変わる。
という帳票なら、Valueだけでは情報が不足します。
セルマッピングでは、
何を取得するのか
も明確にします。
値なのか。
文字列なのか。
色なのか。
数式結果なのか。
Shapeなのか。
この定義がないと、コードを書いた後で「欲しかった情報が違った」ということが起きます。
必須項目と任意項目を分ける
すべてのセルを同じ重要度で扱う必要はありません。
たとえば橋梁IDや橋梁名がなければ処理できない。
一方、備考欄は空欄でもよい。
ということがあります。
そこでマッピング表に、
必須
任意の区分を持たせます。
処理開始時に必須項目が取得できなければ止める。
任意項目が空欄ならそのまま進める。
といった制御ができます。
これにより、
「値が取れなかったが、そのまま処理が終わった」
という状態を減らせます。
帳票自動化では、エラーを出さず動くことより、
必要な情報が取れていないときに適切に止まること
が重要な場合があります。
セルマッピング表そのものを仕様書にする
セルマッピングは、VBAのためだけに作るものではありません。
実務上は、
「この帳票のどこから何を取得しているか」
を人間が確認するための仕様書にもなります。
たとえば自治体様式が更新されたとき、
旧帳票と新帳票を比較し、
C5からD5へ移動。
シート名変更。
新規項目追加。
削除項目あり。
といった差分をマッピング表上で確認できます。
コードだけで管理している場合は、変更箇所を探すためにVBAを読まなければなりません。
マッピング表があれば、
まず仕様差分を確認。
その後で必要なコード変更を判断。
という順番にできます。
これはAIへコード修正を依頼するときにも有効です。
AIへ巨大なExcelと「直して」と渡すより、
「BRIDGE_NAMEの取得位置がC5からD5へ変更」
と仕様として伝えた方が、変更内容を限定できます。
設定値とセルマッピングは役割が違う
前の記事では、自治体ごとのシート名や開始行などを設定値として管理する方法を扱いました。
セルマッピングとは似ていますが、役割は異なります。
設定値は、
「どの帳票を使うか」
「何行目から処理するか」
「どの自治体として動かすか」
といった処理条件です。
セルマッピングは、
「橋梁名はどこか」
「評価はどこか」
「写真番号はどこか」
という業務項目と帳票位置の対応関係です。
両方を分けて持つことで、
処理条件。
帳票構造。
VBAロジック。
をそれぞれ独立して管理しやすくなります。
コードを書く前にサンプル帳票で検証する
セルマッピング表を作ったら、いきなり本番処理を作るのではなく、まず数項目だけ読み取って確認します。
たとえば、
橋梁名。
路線名。
径間番号。
評価。
写真番号。
だけを取得し、想定した値が取れているか確認します。
この段階で、
結合セルだった。
数式だった。
Shapeだった。
位置が固定ではなかった。
と分かれば、設計を修正できます。
最初から数百項目を一気に実装すると、マッピングの誤りなのかコードの誤りなのか切り分けにくくなります。
小さくマッピングを確認してから処理範囲を広げる
方が、帳票自動化では安全です。
本番帳票へVBAを適用する前には、必ずコピーやテスト用ファイルで検証してください。
自治体帳票が変わったときにマッピング差分を見る
セルマッピングを独立して持つ大きなメリットは、帳票変更への対応です。
年度更新や自治体独自様式の改訂でセル位置が変わった場合、
まず旧マッピングと新帳票を比較します。
業務項目自体は変わらず、位置だけ変わったのであれば、マッピング変更で対応できる可能性があります。
一方、
項目構造そのものが変わった。
複数項目が一つになった。
評価方式が変わった。
セルからShapeへ変わった。
という場合は、ロジック変更が必要かもしれません。
このように、
レイアウト差分なのか、業務仕様差分なのか
を分けて考えられます。
SaaSと独自帳票をつなぐときにも使える
セルマッピングはExcel VBAだけの話ではありません。
建設DX製品やSaaSからCSVやExcelを出力し、それを自治体独自帳票へ変換する場合にも同じ考え方が使えます。
SaaS側では、
bridge_name
span_no
damage_typeのようなデータ項目を持っている。
自治体帳票側では、
橋梁名はC5。
径間番号はB12。
損傷種類はF15以降。
という構造になっている。
この二つの間へマッピングを置けば、
製品側のデータ構造と自治体帳票の構造を翻訳する層
として使えます。
製品本体へ自治体ごとの帳票差分をすべて持たせなくても、外側の変換処理で補完できる場合があります。
これは建設DXのラストワンマイルで残りやすい作業の一つです。
セルマッピングはコードより長く使えることがある
VBAコードは、処理方法が変われば書き換わります。
Excel VBAから別の処理方式へ変えることもあります。
しかし、
「橋梁名という項目がどこにあるか」
「この評価値がどの業務項目に相当するか」
という対応関係は、別の仕組みへ移行するときにも使えます。
つまりセルマッピングは、
単なるVBA用設定ではなく、
帳票をデータとして扱うための知識資産
になります。
ExcelからCSVへ変換する場合。
統合マスタへ取り込む場合。
SaaSと連携する場合。
AIに帳票構造を説明する場合。
どのケースでも、業務項目と帳票位置の対応関係が必要になります。
まとめ
Excel VBAで帳票を自動化するとき、セル番地をコードへ直接書くだけでも処理は作れます。
しかし対象帳票や自治体が増えるほど、
「何の項目がどこにあるのか」
をコードの外で管理する価値が大きくなります。
まず必要な業務項目を決める。
項目IDを付ける。
入力位置を対応させる。
固定セルと繰り返し領域を分ける。
セル、Shape、書式など取得方式を整理する。
必須項目を決める。
この情報をセルマッピングとして独立させます。
重要なのは、
コードを書く前に、帳票上の位置と業務上の意味を切り離して整理すること
です。
この一枚の対応表があるだけで、VBAの保守だけでなく、自治体様式変更、データ変換、SaaS連携、AIへの仕様説明まで扱いやすくなります。
建設帳票の自動化では、VBAそのものより先に、
「何を、どこから、どんな意味で読むのか」
を決めることが、後の実装を安定させる土台になります。
ダウンロード
維持DXでは、国交省様式を対象とした橋梁点検支援ツールを無料・機能制限版として公開しています。
実際の処理イメージを確認したい方は、以下のフォームからお申し込みください。
フォーム送信後に、ダウンロード案内をお送りします。
ご注意
ダウンロードしたExcelでマクロが実行できない場合は、
右クリック → プロパティ →「許可する」 をチェック後、再度開いてください。Windowsのセキュリティ機能により、初回実行時にマクロがブロックされる場合があります。
