簡易原価積上げ計算（Excel/VBA)の説明とサンプルコード

　製品構成展開されたツリーデータを使用して、製品を構成する部材の原価を積上げ、製品原価を計算するExcel/VBAの要点を説明します。BOMを使用することで原価や重量の積上げ計算をすることができます。最下層の部品から最上位の製品に系列を遡って数値を積み上げます。
　それでは、実際にExcel/VBAを使用して原価積上げをしてみましょう。

まず、Excelには以下のようにデータが入力されている前提です。

　レベル,品名,費目,標準原価,数量,積上げ原価
　0,EndAssy,組立費,10,1,
　1,SubAssy1,組立費,10,2,
　2,Part11,購入費,10,10,
　2,Part12,材料・加工費,10,10,
　1,SubAssy2,組立費,10,2,
　2,Part21,購入費,10,10,
　2,Part22,材料・加工費,10,10,

　計算結果は、積上げ原価の列にセットされます。

　レベル,品名,費目,標準原価,数量,積上げ原価
　0,EndAssy,組立費,10,1,850
　1,SubAssy1,組立費,10,2,420
　2,Part11,購入費,10,10,100
　2,Part12,材料・加工費,10,10,100
　1,SubAssy2,組立費,10,2,420
　2,Part21,購入費,10,10,100
　2,Part22,材料・加工費,10,10,100

　最終行から１行目まで上に遡って原価を積上げます。各階層の積上げ原価は配列に保持します。
　（ここが、ミソです。）

　Part22の実際原価は、標準原価ｘ数量で、10ｘ10=100
　この実際原価100を一つ上のSubAssy2の配列に保持します。
　Part21の実際原価は、10ｘ10=100
　この実際原価100を一つ上のSubAssy2の配列に加算します。
　これでSubAssy2の構成品であるPart21とPart22の合計原価は、200

　SubAssy2（自身）の組立費10と構成品の合計原価200を加算して
　SubAssy2の原価は、210になります。
　SubAssy2は2個必要なので、210ｘ2=420
　この積上げ原価420を一つ上のEndAssyの配列に保持します。

　同様に、Part12とPart11の合計原価は、200
　SubAssy1の原価は、210になります。
　SubAssy1は2個必要なので、210ｘ2=420
　この積上げ原価420を一つ上のEndAssyの配列に加算します。

　これで、EndAssyの構成品である、SubAssy1とSubAssy2の合計原価は、840

　最後に、EndAssy（自身）の組立費10と構成品の合計原価840を加算して
　EndAssyの原価は、850

　EndAssyは1個必要なので、積上げ原価は、850ｘ1=850

以下に原価を積上げ計算するVBAのサンプルコードと要点の説明を記載します。
コピーしてExcelのVBエディタに貼り付けて実行して下さい。

Sub SetCost()

  Dim CT, CTx, CTx_Pre(20), QTY As Single
  Dim Level, Level_Pre, Row, Row_End As Integer
  
  Set strShtName = ActiveSheet
  
  '最終行を取得
  Row_End = Cells(Rows.Count, 1).End(xlUp).Row
  Row = Row_End
  
  '1行目まで繰り返す。
  Do Until (Row = 1)
  
     'シートから各値を取得
     Level = strShtName.Cells(Row, 1).Value
     CT = strShtName.Cells(Row, 4).Value
     QTY = strShtName.Cells(Row, 5).Value

    '階層が上がった場合
     If Level_Pre > Level Then
  
        '実際原価に
       「自身の原価 ＋ Σ構成品の積上げ原価 × 数量」
         をセットする。
        CTx = (CT + CTx_Pre(Level)) * QTY
     
    '階層が同じか下がった場合
     Else
  
       '実際原価に「自身の原価ｘ数量」をセットする。
        CTx = CT * QTY
    
        For i = Level_Pre To Level
            CTx_Pre(i) = 0
        Next i
    
     End If
  
     If Level > 0 Then
  
       'ひとつ上の階層（親）の積上げ原価に、
        自身の実際原価を加算する。
        CTx_Pre(Level - 1) = CTx_Pre(Level - 1) + CTx
     
     End If

     strShtName.Cells(Row, 6) = CTx
  
     Level_Pre = Level
     Row = Row - 1
  
  Loop
  On Error GoTo 0
End Sub

　いかがでしたか。　重量積上げも同じロジックで可能です。
　ERPやPLMが無くても、数千行くらいのBOMであればこれで十分です。
　見積に使用したり、コスト削減、重量軽減の設計改善に活用できます。
　例えば、部品を安価なものに変更したら製品の原価がどうなるか、
　どのSubAssyに原価や重量の比重がかかっているか等
　しかし、この積上げ計算を手作業や関数で行うのは結構大変ですよね。

PR広告！TechAcademy [テックアカデミー] PR広告！システム開発業者を完全無料でご紹介します！【EMEAO!】