数量計算書をエクセルで作る方法

三浦プロジェクトは土木とEXCELの情報サイトです

エクセルで条件判断を自動化する方法【共通仮設費率の計算】

  • 修正 2003年7月20日に公開した記事を、一部修正して再度公開しました。

EXCEL活用テクニック

【関数の作り方（実務編）】

前回はＶＢＡの入門用として、わかりやすい公式を軸に話を展開してみました。今回は、実用にも耐えるコードをひとつ紹介したいと思います。コンピュータの前で電卓を叩いたり、繰り返し行う処理なのに、いつも同じ文献の同じ箇所を参照している。実にばかげた話ですが、無意識のうちにやってしまう事、決して少なくありません。そこで登場するのが、条件分岐を利用した関数のテクニックです。

土木工事のイメージ

Visual Basicの使い方

  1. 【Visual Basic】ボタンをクリックすると、エディターが開きます。エディター内のメニューバーから、[挿入] > [標準モジュール]を選択します。
    マクロの作り方を説明1
  2. ここで、プロジェクトエクスプローラに[Module1]が作成されたことを確認してください。この部分に、functionプロシージャのコードを書くことによって、ユーザー定義関数が使えるようになります。
    マクロの作り方を説明2

  3. コーディング

    下記の例は共通仮設費率を計算するためのコードです。個々の命令文の詳細については、専門書や、Excelのヘルプ等を参照してください。まず、共通仮設費率を算出する際の条件は、２１種類の工種区分と３種類の対象額の範囲による区分があります。そこで、複数の条件判断ができるＩｆステートメントを利用して、工種区分を選択し、対象額の範囲をサブルーチンに委ねるというプログラムにより計算をおこないます。では、そのコードの構成を見ていきます。先頭行の（　）内に示される引数は、工種区分と対象額の２種類で、この種類や範囲によって共通仮設費率が決まります。ここで、河川工事を例に挙げれば、Ｉｆステートメントの条件の部分、『もし～ならば処理Ａをしなさい』の～の部分が"河川工事"です。処理Ａはそれ以下のステートメントで、kr、a、b、の定数及びサブルーチン『算定式1』になります。Ｉｆステートメントにより、実際に実行されるステートメントは、２０行程度でしかないのは一目瞭然です。コードは長くなりましたが、内容は実に簡単なものとなります。実務において、同一作業の中で工種区分が変更になる事は、それほど多くはありません。しかし、一度作成したファイルの再使用は頻繁にあり、ミスは起こりうる事ですので、煩わしい作業はなるべく省略したいものです。

                            例　共通仮設費率の計算

Function kr(工種区分 As String, 対象額 As Currency) As Double
'copyright(c)2024 三浦プロジェクト
Dim p As Currency
Dim a As Single, b As Single
Dim krs As Double, krl As Double

p = 対象額
If 工種区分 = "河川工事" Then
    krs = 12.53
    a = 238.6
    b = -0.1888
    krl = 4.77
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "河川・道路構造物工事" Then
    krs = 20.77
    a = 1228.3
    b = -0.2614
    krl = 5.45
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "海岸工事" Then
    krs = 13.08
    a = 407.9
    b = -0.2204
    krl = 4.24
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "道路改良工事" Then
    krs = 12.78
    a = 57#
    b = -0.0958
    krl = 7.83
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "鋼橋架設工事" Then
    krs = 38.36
    a = 10668.4
    b = -0.3606
    krl = 6.06
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "ＰＣ橋工事" Then
    krs = 27.04
    a = 1636.8
    b = -0.2629
    krl = 7.05
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "舗装工事" Then
    krs = 17.09
    a = 435.1
    b = -0.2074
    krl = 5.92
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "砂防・地すべり等工事" Then
    krs = 15.19
    a = 624.5
    b = -0.2381
    krl = 4.49
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "公園工事" Then
    krs = 10.8
    a = 48#
    b = -0.0956
    krl = 6.62
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "電線共同溝工事" Then
    krs = 9.96
    a = 40#
    b = -0.0891
    krl = 6.31
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "情報ボックス工事" Then
    krs = 18.93
    a = 494.9
    b = -0.2091
    krl = 6.5
    算定式1 p, a, b, kr, krs, krl
ElseIf 工種区分 = "橋梁保全工事" Then
    krs = 27.32
    a = 7050.2
    b = -0.3558
    krl = 6.79
    算定式2 p, a, b, kr, krs, krl
ElseIf 工種区分 = "道路維持工事" Then
    krs = 23.94
    a = 4118.1
    b = -0.3548
    krl = 5.97
    算定式3 p, a, b, kr, krs, krl
ElseIf 工種区分 = "河川維持工事" Then
    krs = 9.05
    a = 26.8
    b = -0.0748
    krl = 6.76
    算定式3 p, a, b, kr, krs, krl
ElseIf 工種区分 = "共同溝等工事（１）" Then
    krs = 8.86
    a = 68.3
    b = -0.1267
    krl = 4.53
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "共同溝等工事（２）" Then
    krs = 13.79
    a = 92.5
    b = -0.1181
    krl = 7.37
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "トンネル工事" Then
    krs = 28.71
    a = 4164.9
    b = -0.3088
    krl = 5.59
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "下水道工事（１）" Then
    krs = 12.85
    a = 422.4
    b = -0.2167
    krl = 4.08
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "下水道工事（２）" Then
    krs = 13.32
    a = 485.4
    b = -0.2231
    krl = 4.08
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "下水道工事（３）" Then
    krs = 7.64
    a = 13.5
    b = -0.0353
    krl = 6.34
    算定式4 p, a, b, kr, krs, krl
ElseIf 工種区分 = "コンクリートダム" Then
    krs = 12.29
    a = 105.2
    b = -0.11
    krl = 9.02
    算定式5 p, a, b, kr, krs, krl
ElseIf 工種区分 = "フィルダム" Then
    krs = 7.57
    a = 43.7
    b = -0.0898
    krl = 5.88
    算定式5 p, a, b, kr, krs, krl
Else
    kr = 0
End If
End Function
Sub 算定式1(p As Currency, a As Single, b As Single, kr As Double _
            , krs As Double, krl As Double)
If p <= 6000000 Then
    kr = krs
ElseIf p > 6000000 And p <= 1000000000 Then
    kr = Round(a * p ^ b, 2)
ElseIf p > 1000000000 Then
    kr = krl
End If
End Sub    
Sub 算定式2(p As Currency, a As Single, b As Single, kr As Double _
            , krs As Double, krl As Double)
If p <= 6000000 Then
    kr = krs
ElseIf p > 6000000 And p <= 300000000 Then
    kr = Round(a * p ^ b, 2)
ElseIf p > 300000000 Then
    kr = krl
End If
End Sub
Sub 算定式3(p As Currency, a As Single, b As Single, kr As Double _
            , krs As Double, krl As Double)
If p <= 6000000 Then
    kr = krs
ElseIf p > 6000000 And p <= 100000000 Then
    kr = Round(a * p ^ b, 2)
ElseIf p > 100000000 Then
    kr = krl
End If
End Sub
Sub 算定式4(p As Currency, a As Single, b As Single, kr As Double _
            , krs As Double, krl As Double)
If p <= 10000000 Then
    kr = krs
ElseIf p > 10000000 And p <= 2000000000 Then
    kr = Round(a * p ^ b, 2)
ElseIf p > 2000000000 Then
    kr = krl
End If
End Sub
Sub 算定式5(p As Currency, a As Single, b As Single, kr As Double _
            , krs As Double, krl As Double)
If p <= 300000000 Then
    kr = krs
ElseIf p > 300000000 And p <= 5000000000# Then
    kr = Round(a * p ^ b, 2)
ElseIf p > 5000000000# Then
    kr = krl
End If
End Sub

    FunctionからEnd Functionをコピーして、コードウィンドウに貼り付けます。

    マクロの作り方を説明3

  4. 関数の実行

    あらかじめセルA1に工種区分、B1に対象額を入力しておいてください。セルC1をアクティブにして、数式バーから【fx】をクリックします。すると、関数の挿入ダイアログボックスが表示され、関数の入力を促します。ここで、関数の分類からユーザー定義が追加されている事を確認してください。その中に、作成した関数【kr】があるはずです。選択後、ＯＫをクリックします。
    マクロの作り方を説明4
  5. 引数の入力ボックスに変わりましたら、セルA1に工種区分が入力されていますので、引数にセル範囲を指定します。同様に、対象額はセルB1を指定します。その状態で、入力ボックス下欄に数式の結果が返されるのを確認してください。
    マクロの作り方を説明3
  6. 確認後ＯＫをクリックすると、アクティブセルに共通仮設費率が返されます。引数の入力で、工種区分、対象額を直接入力しても何ら問題ありません。
    マクロの作り方を説明3

ダウンロード

共通仮設費率、現場管理費率、一般管理費等率、イメージアップ経費率がコーディングされた土木工事諸経費率計算関数（ノンプロテクト版）を公開しますのでご利用ください。著作権フリー、改変自由とします。

ソフトウエア・各種計算書・テンプレートのダウンロード

Excel活用テクニック