こんにちは。土木設計歴30年、AI土木研究室です。
数量計算書の作成は、設計業務のなかでも「単純だが量が多く、しかも間違えられない」典型的な作業です。工種ごとにシートを作り、同じ書式を何枚もコピーし、最後に総括表へ手作業で転記する。この繰り返しに一日を溶かした経験は、多くの方にあるはずです。
この手の作業は、Excel VBA（マクロ）を使うと驚くほど短くできます。前回ご紹介したAutoLISPによる図面側の自動化と対になる、計算書側の自動化です。今回はプログラミング未経験の方でもそのままコピーして使えるマクロを4例、実務で使える形でご紹介します。あわせて、私が実際に踏んだ失敗と、そこから決めた運用ルールもお伝えします。
なぜ数量計算書はExcel VBAと相性がよいのか
数量計算書の作業には、自動化に向いた特徴がはっきりあります。
- 形が決まっている：様式・列構成・単位の並びが工種を越えてほぼ共通
- 同じ操作の繰り返し：シートの複製、名前の変更、総括表への転記が延々と続く
- 判断が要らない：どの値をどこへ持っていくかが、あらかじめルールとして決まっている
- 手作業のミスが致命的：転記ミス1件が、そのまま積算の誤りとして下流に流れる
逆に言えば、設計者の判断が入る部分――どの工種を計上するか、どの標準図を採用するか――は自動化の対象外です。VBAに任せるのは「決まったことを、決まったとおりに、間違えずに繰り返す」部分だけ。この線引きを最初にはっきりさせておくことが、失敗しないコツです。
なお、関数と数式の工夫だけでミスを減らす方法についてはExcel数量計算書のミスを防ぐ自動化の仕組みで整理しています。VBAはその延長線上にある、もう一段強力な手段だとお考えください。
準備：開発タブの表示とマクロ有効ブックの保存
まず、マクロを書くための入口を用意します。Excelの「開発」タブは既定では表示されていません。
- 「ファイル」→「オプション」→「リボンのユーザー設定」を開く
- 右側の「メインタブ」の一覧にある「開発」のチェックボックスをオンにする
- OKを押すとリボンに「開発」タブが現れる（一度オンにすれば、オフにするまで表示され続けます）
コードを書く画面（Visual Basic Editor）は、開発タブの「Visual Basic」ボタンから開きます。開いたら「挿入」→「標準モジュール」を選び、そこにコードを貼り付けてください。
保存形式は必ず .xlsm
マクロを含むブックは、通常の .xlsx では保存できません。「Excel マクロ有効ブック（.xlsm）」を選んで保存してください。うっかり .xlsx で上書きすると、書いたコードがすべて消えます。私は一度これをやって、半日分の作業を失いました。
マクロがブロックされるときは「信頼できる場所」
社内サーバやNAS上のファイルを開くと、マクロが無効化されて動かないことがあります。これはExcelのセキュリティ機能によるものです。既定では「警告を表示してすべてのマクロを無効にする」設定になっており、署名のないマクロを常用する場合は、そのブックを置くフォルダを「信頼できる場所」として登録するのが正攻法です（「ファイル」→「オプション」→「トラストセンター」→「トラストセンターの設定」→「信頼できる場所」）。ネットワーク上のフォルダを登録する場合は、同じ画面の「ネットワーク上の信頼できる場所を許可する」もあわせて確認してください。
セキュリティ設定はお使いの環境の管理方針に左右されます。社内ポリシーでロックされている場合は、勝手に変えず情報システム部門に相談してください。
マクロの置き場所：アドインで共有するか、ブックに埋め込むか
コードを書き始める前に決めておきたいのが「どこに置くか」です。私の運用は、案件を問わず使うものはアドインにして共有し、その案件かぎりの簡単なものはブックに直接埋め込む、という使い分けです。
- アドイン（.xlam）で共有：総括表への集計、端数処理、シートの一括作成など、どの業務でも使うもの。1ファイルを直せば全案件に反映される
- ブックに埋め込み（.xlsm）：その案件の様式に合わせた転記や特殊な集計など。ブックと一緒に動くので、渡した相手の環境でもそのまま使える
アドインの作り方は簡単です。標準モジュールにコードを書いたブックを「Excel アドイン（*.xlam）」形式で保存し、「開発」タブ →「Excel アドイン」→「参照」から登録します。以後、どのブックを開いていてもマクロが使えます。自分ひとりで使うだけなら個人用マクロブック（PERSONAL.XLSB）という手もありますが、共有はできないので、チームで配るならアドインです。
ここでひとつ、必ず引っかかる落とし穴があります。アドインに移したとたん、ThisWorkbook の指す先が変わります。 ThisWorkbook は「コードが書かれているブック」を意味するため、アドインの中ではアドイン自身を指してしまいます。このあと紹介するコードをそのままアドインに移すと、操作したいはずの計算書ではなくアドインの中を探しにいって、何も起きません。
' ブックに埋め込む（.xlsm）とき ― コードのあるブック自身を操作する
Set wsSum = ThisWorkbook.Worksheets("総括表")
' アドイン（.xlam）に移すとき ― 操作対象は「いま開いている」ブック
Set wsSum = ActiveWorkbook.Worksheets("総括表")ActiveWorkbook は「いま前面にあるブック」です。アドイン化するときは ThisWorkbook をすべて ActiveWorkbook に置き換える、と覚えておいてください。逆に埋め込みで使うなら ThisWorkbook のままが安全です。別のブックを間違って書き換える心配がありません。
社内で配る方法は2つあります。共有フォルダに .xlam を1つ置いて各自に登録してもらう方法と、各自のPCにコピーを配る方法です。前者は差し替え1回で全員に反映できる反面、誰かが使っている間はファイルを更新できないことがあります。作り込んでいる時期は各自にコピーを配り、内容が固まってきたら共有フォルダへ移す、という進め方が現実的です。いずれの場合も、置き場所を前述の「信頼できる場所」に登録しておく必要があります。
マクロ①：複数シートの数量を総括表へ自動集計する
最も効果が大きいのがこれです。工種ごとに分かれた計算書シートから、品目名と数量を拾って一枚の総括表にまとめます。手作業の転記が丸ごと消えます。
前提は次のとおりです。各計算書シートのB列に品目名、E列に数量が入っており、集計対象の行は6行目以降。集計先は「総括表」という名前のシートとします。ご自身の様式に合わせて、列記号と開始行の数字だけ書き換えてください。
Option Explicit
Sub 数量を総括表に集計()
Dim ws As Worksheet, wsSum As Worksheet
Dim lastRow As Long, i As Long, outRow As Long
Set wsSum = ThisWorkbook.Worksheets("総括表")
Application.ScreenUpdating = False
' 出力先を初期化（2行目以降をクリア。1行目は見出しとして残す）
wsSum.Range("A2:D10000").ClearContents
outRow = 2
For Each ws In ThisWorkbook.Worksheets
' 総括表そのものは集計対象から外す
If ws.Name <> "総括表" Then
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 6 To lastRow
' 品目名があり、数量が数値の行だけを拾う
If ws.Cells(i, "B").Value <> "" _
And IsNumeric(ws.Cells(i, "E").Value) _
And ws.Cells(i, "E").Value <> "" Then
wsSum.Cells(outRow, 1).Value = ws.Name ' 工種（シート名）
wsSum.Cells(outRow, 2).Value = ws.Cells(i, "B").Value ' 品目
wsSum.Cells(outRow, 3).Value = ws.Cells(i, "E").Value ' 数量
wsSum.Cells(outRow, 4).Value = ws.Name & "!E" & i ' 出典セル
outRow = outRow + 1
End If
Next i
End If
Next ws
Application.ScreenUpdating = True
MsgBox outRow - 2 & " 件を集計しました。", vbInformation
End SubポイントはD列に「出典セル」を書き出しているところです。集計結果を見て「この数量はどこから来たのか」を後から追えるようにしておくと、照査が一気に楽になります。自動化した計算書で一番怖いのは、値の出所が分からなくなることです。
なお、集計結果を値ではなく数式リンクとして持ちたい場合は、Valueへの代入を wsSum.Cells(outRow, 3).Formula = "=" & ws.Name & "!E" & i に変えれば、元シートの修正が総括表へ自動で反映されます。設計変更が多い業務ではこちらが有利です。
マクロ②：端数処理を基準どおりに一括適用する
数量には工種ごとに丸めの規則があります。表示上は丸まって見えても中身が生値のままだと、合計が合わない、電子納品のチェックで指摘される、といったことが起きます。そこで丸めを値として確定させるマクロです。
ここで必ず知っておくべき落とし穴があります。VBAの Round 関数は、Excelのワークシート関数 ROUND とは丸め方が違います。VBAの Round は「銀行家の丸め（最も近い偶数に丸める）」を行うため、たとえば Round(0.12345, 4) は 0.1234 を返します。一方 WorksheetFunction.Round(0.12345, 4) は 0.1235 を返します。
数量計算書で私たちが期待しているのは後者の四捨五入です。VBAで丸めるときは、必ず Application.WorksheetFunction.Round を使ってください。これを知らずにVBAの Round を使い、0.5刻みの値が合わないと悩んだのは私自身です。
Option Explicit
Sub 選択範囲を四捨五入して確定()
Dim c As Range
Dim keta As Variant
keta = InputBox("小数点以下の桁数を入力してください（例：2）", "端数処理", 2)
If keta = "" Then Exit Sub ' キャンセル時
If Not IsNumeric(keta) Then
MsgBox "数値を入力してください。", vbExclamation
Exit Sub
End If
If MsgBox("選択範囲の数値を四捨五入して値で確定します。" & vbCrLf & _
"元に戻せません。よろしいですか？", vbOKCancel + vbExclamation) <> vbOK Then
Exit Sub
End If
Application.ScreenUpdating = False
For Each c In Selection
If IsNumeric(c.Value) And c.Value <> "" Then
' VBAのRoundではなくワークシート関数のROUNDを使う（四捨五入）
c.Value = Application.WorksheetFunction.Round(c.Value, CInt(keta))
End If
Next c
Application.ScreenUpdating = True
MsgBox "端数処理が完了しました。", vbInformation
End Sub切り上げ・切り捨てが必要な工種では、WorksheetFunction.RoundUp / WorksheetFunction.RoundDown に置き換えてください。工種ごとの丸め規則は発注者の基準に従うものですから、マクロ側で勝手に統一せず、適用範囲を選択してから実行する形にしてあります。
マクロ③：工種ごとの計算書シートを様式から一括生成する
3つ目は、様式シートを工種の数だけ複製し、シート名と表題を自動でセットするマクロです。20工種あれば20回繰り返していた作業が、ボタン1回で終わります。
「様式」という名前のひな形シートと、「工種一覧」シートのA列に工種名を並べておく前提です。
Option Explicit
Sub 工種シートを一括作成()
Dim wsTmpl As Worksheet, wsList As Worksheet
Dim lastRow As Long, i As Long
Dim koushu As String
Dim created As Long
Set wsTmpl = ThisWorkbook.Worksheets("様式")
Set wsList = ThisWorkbook.Worksheets("工種一覧")
lastRow = wsList.Cells(wsList.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To lastRow
koushu = Trim(CStr(wsList.Cells(i, "A").Value))
If koushu <> "" Then
If Not SheetExists(koushu) Then
wsTmpl.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
.Name = koushu
.Range("B2").Value = koushu & " 数量計算書" ' 表題セルは様式に合わせる
End With
created = created + 1
End If
End If
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox created & " シートを作成しました。", vbInformation
End Sub
' 同名シートの有無を調べる補助プロシージャ
Private Function SheetExists(nm As String) As Boolean
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = nm Then
SheetExists = True
Exit Function
End If
Next ws
End FunctionSheetExists で既存シートを飛ばしているので、工種一覧に行を追加して再実行すれば、不足分だけが作られます。何度実行しても壊れない――この性質を持たせておくと、実務での安心感がまるで違います。
シート名には / ? * [ ] : といった文字が使えず、31文字までという制限があります。工種名にこれらが含まれていると実行時にエラーになりますので、一覧側の表記を整えておいてください。
マクロ④：図面から拾った値を数量計算書へ転記する
ここまでの3例はExcelの中だけで完結する話でした。ですが実務でいちばん効いたのは、図面から拾った値を数量欄へ転記する部分の自動化です。延長、面積、高さ、基数――CADで拾った数字を計算書に打ち込む作業は、量が多いうえに、打ち間違えても計算書の中では辻褄が合ってしまうため照査ですり抜けます。ここを人の手から外す効果は非常に大きいものがあります。
図面側からの取り出しは、AutoCADのデータ書き出し（DATAEXTRACTION）や属性の抽出でCSVにするのが一般的です。AutoLISPで拾わせる方法は前回の記事で触れました。ここでは、その結果が「図面値」というシートに次の形で並んでいる前提で、計算書側へ流し込むマクロを示します。
- A列：転記先のシート名（工種）
- B列：品目名（計算書のB列の表記と完全に一致させる）
- C列：図面から拾った値
Option Explicit
Sub 図面値を計算書へ転記()
Dim wsSrc As Worksheet, ws As Worksheet
Dim lastRow As Long, i As Long, r As Long
Dim shName As String, item As String
Dim hit As Boolean, okCount As Long, ng As String
Set wsSrc = ThisWorkbook.Worksheets("図面値")
lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lastRow
shName = Trim(CStr(wsSrc.Cells(i, "A").Value))
item = Trim(CStr(wsSrc.Cells(i, "B").Value))
If shName <> "" And item <> "" Then
hit = False
' マクロ③で作った SheetExists をそのまま使う
If SheetExists(shName) Then
Set ws = ThisWorkbook.Worksheets(shName)
For r = 6 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
' 結合セルでも左上の値で判定する（後述）
If Trim(CStr(ws.Cells(r, "B").MergeArea.Cells(1, 1).Value)) = item Then
ws.Cells(r, "F").Value = ws.Cells(r, "E").Value ' 旧値をF列に控える
ws.Cells(r, "E").Value = wsSrc.Cells(i, "C").Value ' 図面値を転記
ws.Cells(r, "E").Interior.Color = RGB(255, 242, 204) ' 転記箇所に色を付ける
okCount = okCount + 1
hit = True
Exit For
End If
Next r
End If
If Not hit Then ng = ng & vbCrLf & " ・" & shName & " / " & item
End If
Next i
Application.ScreenUpdating = True
If ng = "" Then
MsgBox okCount & " 件を転記しました。", vbInformation
Else
MsgBox okCount & " 件を転記しました。" & vbCrLf & vbCrLf & _
"次の項目は転記先が見つかりませんでした：" & ng, vbExclamation
End If
End Subこのマクロで大事にしているのは、次の3点です。
- 見つからなかったものを必ず知らせる：品目名の表記ゆれ（「基礎砕石」と「基礎砕石工」など）で転記先が見つからないことは日常的に起きます。黙って飛ばすマクロは、数量が抜けたまま完成した計算書を作ってしまいます
- 旧値を残す：上書き前の値を隣の列に控えておけば、図面の修正が数量にどう効いたかを差分で確認できます。設計変更のたびにこれが効きます
- 転記した箇所に色を付ける：どこが機械で入った値で、どこが手入力かがひと目で分かります。照査で見るべき場所がはっきりします
逆に、品目名の突合をマクロに賢くやらせようとする（あいまい一致など）のはお勧めしません。表記を揃えるのは様式側の仕事です。マクロは完全一致だけを見て、合わないものは人に返す。この割り切りのほうが、結局は事故が起きません。
動作を安定させる、3つの書き方の作法
1. Option Explicit を必ず先頭に書く
モジュールの先頭に Option Explicit と書くと、宣言していない変数を使ったときにエラーで教えてくれます。変数名のタイプミスは、書いた本人でもまず見つけられません。1行の保険で、原因不明の誤動作をほぼ防げます。
2. 処理中は画面更新と再計算を止める
シートを何十枚も操作するマクロでは、画面の更新と自動再計算が速度の足を引っ張ります。画面更新をオフにするとコードの実行が速くなり、再計算を手動に切り替えれば処理中の無駄な計算がなくなります。
Sub 重い処理のひな形()
Dim calcMode As XlCalculation
calcMode = Application.Calculation ' 現在の設定を控えておく
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo Cleanup
' ここに本体の処理を書く
Cleanup:
' 中断しても必ず元に戻す
Application.Calculation = calcMode
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "エラー：" & Err.Description, vbCritical
End Sub大事なのは必ず元に戻すことです。途中でエラーが出たまま再計算が手動のまま放置されると、以後Excelが計算しなくなり、気づかないまま古い値で成果品を作ってしまいます。上のように On Error GoTo で戻り先を用意しておいてください。
3. 小さく作って、小さく試す
いきなり全工程を1本のマクロにしないことです。「集計だけ」「丸めだけ」と機能を分けて作り、必ずコピーしたテスト用ブックで動かす。設計と同じで、部材ごとに確かめてから組み上げるほうが結局は速く進みます。
マクロが静かに壊れる2大原因：セル結合と行挿入
マクロを実務で使い続けていると、トラブルの原因はだいたい2つに絞られてきます。セル結合と行の挿入です。どちらも、エラーを出さずに間違った値を返してくるところが厄介です。
セル結合：値は左上のセルにしか入っていない
結合されたセルの値は、見た目の中央ではなく結合範囲の左上のセルにだけ格納されています。残りのセルは空です。ですから結合された品目欄をループで読むと、2行目以降が空として扱われ、その行がまるごと拾われないという事故が起きます。エラーは出ません。ただ、数量が1件足りない計算書ができあがります。
対策は MergeArea を使うことです。結合範囲のどのセルから見ても、左上の値を取りに行けます。
' 結合セルでも確実に値を取る
v = ws.Cells(r, "B").MergeArea.Cells(1, 1).Value
' 結合セルへ書き込むときも、左上のセルを指定する
ws.Cells(r, "B").MergeArea.Cells(1, 1).Value = "基礎砕石"また、結合セルを含む範囲は、形の違うところへコピーしようとするとエラーで止まりますし、並べ替えやオートフィルタも不安定になります。本音を言えば、マクロで扱う表に結合セルは使わないのがいちばんです。 体裁上どうしても必要なら、セルの書式設定にある「選択範囲内で中央」で代用できないか検討してみてください。見た目はほぼ同じで、セルは結合されません。
行の挿入：セル番地を直書きしたマクロだけが取り残される
もうひとつが行挿入です。ワークシートの数式は行を挿入すれば自動で追従しますが、VBAのコードに直接書いた行番号や列記号は追従しません。「合計はE45」と書いたマクロは、上に1行挿入された瞬間から、ずれて別の項目になったE45を読み続けます。これもエラーは出ません。
対策は、位置を固定で書かないことです。実務で使いやすいのは次の2つです。
- 名前定義を使う：合計セルに「合計_土工」などの名前を付けておけば、行を挿入しても名前は正しいセルを指し続けます
- 見出しを検索して位置を求める：「数量」という見出しを
Findで探し、その列番号を使う。様式で列が1本増えても、マクロ側は直さずに済みます
' ① 名前定義を使う（行を挿入しても追従する）
gokei = ThisWorkbook.Names("合計_土工").RefersToRange.Value
' ② 見出しを探して列位置を求める（5行目が見出し行の場合）
Dim f As Range, colQty As Long
Set f = ws.Rows(5).Find(What:="数量", LookAt:=xlWhole)
If f Is Nothing Then
MsgBox "見出し「数量」が見つかりません。様式を確認してください。", vbCritical
Exit Sub
End If
colQty = f.Column②のように、見つからなかったら止める書き方にしておくのが肝心です。見出しが見つからないまま既定の列で処理を続ければ、また静かに間違えます。様式が変わったらマクロが止まる――これは不便ではなく、安全装置だと考えてください。
実務で失敗しないための運用ルール
30年の実務で、マクロが原因のトラブルはたいてい技術ではなく運用で起きます。私が自分に課しているルールを挙げておきます。
- マクロの実行結果は元に戻せない：VBAで書き換えた内容にCtrl+Zは効きません。実行前に必ずファイルを複製しておくこと
- まず1シートで試す：全シート一括の前に、テスト用の1枚で意図どおり動くか確認する
- 値の出所を残す：集計マクロには出典セルを書き出させ、照査で追跡できるようにする
- 誰でも読める名前をつける：プロシージャ名は「数量を総括表に集計」のように日本語で構いません。半年後の自分と、引き継ぐ後任のために
- コードにコメントを残す：どの列が何を指すかを1行書いておくだけで、様式変更時の修正が段違いに楽になる
- 成果品にマクロを残さない：電子納品では .xlsx など指定された形式で提出するのが原則です。納品用は必ず値のみのブックとして別に作ること
- 様式を変えたらマクロも見直す：列を1本挿入しただけで、列位置を固定したマクロは静かに間違った値を拾い始めます
最後の項目がとくに危険です。マクロは「エラーを出さずに間違える」ことがあります。様式の変更とマクロの保守はセットだと考えてください。数量計算書そのものの様式を先に固めておく重要性については、数量計算書テンプレートの統一化で詳しく書いています。
まとめ
Excel VBAは、数量計算書づくりの「単純だが量が多く、間違えられない」部分をまるごと引き受けてくれます。今回の要点を整理します。
- 自動化するのは形が決まっていて判断の要らない作業だけ。設計者の判断が入る部分は手元に残す
- 準備は「開発タブを表示」「.xlsm で保存」「必要なら信頼できる場所に登録」の3点
- マクロ①：各シートから品目と数量を拾って総括表へ集計。出典セルも一緒に書き出す
- マクロ②：端数処理は
Application.WorksheetFunction.Roundを使う。VBAのRoundは銀行家の丸めなので四捨五入にならない - マクロ③：様式シートを工種の数だけ複製。既存シートは飛ばし、何度実行しても壊れない作りにする
- マクロ④：図面から拾った値を数量欄へ転記する。見つからなかった項目は必ず知らせ、旧値を残し、転記箇所に色を付ける
- 置き場所は「共通で使うものはアドイン（.xlam）、案件かぎりのものはブック埋め込み（.xlsm）」。アドインに移すときは
ThisWorkbookをActiveWorkbookに書き換える - 静かに壊れる2大原因はセル結合と行挿入。読み取りは
MergeArea、位置の指定は名前定義か見出し検索で - 書き方の作法は「Option Explicit」「画面更新と再計算を止めて必ず元に戻す」「小さく作って小さく試す」
- 運用では実行前のバックアップと様式変更時のマクロ見直しを徹底する
まずはマクロ①を、ご自身の様式に合わせて列記号だけ書き換えて動かしてみてください。転記に費やしていた時間が、そのまま検討や照査の時間に変わります。それが自動化のいちばんの価値だと、私は考えています。
著者：AI土木研究室 / 土木設計歴30年。建設コンサルタントとしてインフラ整備に携わりながら、AI・自動化ツールの研究開発を行う。AI土木研究室（doboku-ai.jp）運営。
コメント