原材料卷积成本利用ExcelVBA自动生成

李剑侠

摘 要：原材料卷积成本对产品的定价有很大影响，因此快速、及时地生成原材料卷积成本可以使财务方面能做到心里有数，提前知道产品的加工成本。该文即利用Excel VBA强大的编程计算功能，通过编写Excel VBA代码实现原材料卷积成本的自动生成。解决了人工输入公式进行计算，需要时间长且易出现错误的问题。经过试用，效果良好。

关键词：Excel VBA；卷积成本；原材料成本

中图分类号：TP31 文献标志码：A

0 引言

财务成本核算或进行价格管理时，需要根据BOM对生产加工的产品的原材料进行核算，作为控制成本或制定销售价格的依据。对于有很多层级的零部件来说，原材料的卷积成本计算是一个非常复杂的过程。人工计算耗时较多，且易出现错误。计算一个大约由2 000种零件构成的零部件原材料卷积成本，人工输入计算大约需要2周时间。现为了提早对原材料成本进行预测，提高工作效率，研究利用Excel VBA对原材料的卷积成本进行自动计算。VBA①是集成在Microsoft Office应用程序中的一种程序设计语言，能够实现Office自动化，从而极大地提升了工作效率。VBA集成在Excel中就是Excel VBA。

1 对测试数据分析

该文对原有成本处理过程进行分析。原有数据采用手工输入公式进行计算，公式为最末级的原材料成本为最末级零件的数量和单件原材料成本的乘积，非末级的原材料成本为（本级的单件原材料成本+下级原材料成本）×本级零件的数量，即为本级的原材料成本+所有下级的每一级材料成本之和，每级的材料成本=每级的数量×每级的单件材料成本。即，卷积原材料成本公式为：Cij=Cj+Cj-1+…+Ci（设J 为该零部件级次，I为该零部件的末级级次，J>=I 则Ci为末级级次的原材料成本，Cj为该零部件各级级次原材料成本，Cij为该级级次卷积的原材料成本）。

2 设计思路

以数据库的方式去看待数据，把对数据的手动操作动作分解为以下几步：

（1）判断级次，由于只有末级的计算公式是特别的，是最末级零件的数量和单件原材料成本的乘积；而非末级是本身的材料费加上下級材料费的卷积成本×非末级所需数量。

（2）根据级次制出树形级次零件表。

（3）根据级次制出树形级次数量表。

（4）计算每级本身的原材料成本。

（5）对零部件每级本身的原材料成本进行数据透视，并获取级次卷积原材料成本。

3 设计代码

3.1 编制判断级次代码

根据零件号列与哪级的零件号相等，可以判断其级次为几 （假定零部件的最大层级为6级来设计代码）。为此定义了3个变量，分别为hangshu，lieshu和r，变量类型为integer，意思分别为共有多少行原始数据，第几列（几级），循环的行数。具体代码如下：

Sub jici（）

Dim hangshu As Integer

Dim lieshu As Integer

Dim r As Integer

hangshu = Cells（Rows.Count， "B"）.End（xlUp）.Row

For r = 2 To hangshu

For lieshu = 1 To 6

If Cells（r， 2） = Cells（r， lieshu + 2） Then

Cells（r， 12） = lieshu

End If

Next

Next

End Sub

3.2 编制级次树形级次零件表、零件树形级次零件表代码及计算每级原材料成本代码

定义了jicishuliang过程和4个变量，即hangshu，lieshu，r和jishu。jishu是批零部件的级，其他变量定义同上（略）。

Range（"B2：H" & hangshu）.Copy Range（"Z2"）

For r=2 To hangshu

Select Case② Cells（r，12）.Value

Case 1：Cells（r，13）=Cells（r，9）

Cells（r，19）=Round（（Cells（r，9）*Cells（r，10）），2）

Case 2：Cells（r，13）=Cells（r-1，13）

Cells（r，14）=Cells（r，9）

Cells（r，19）=Round（（Cells（r，13） * Cells（r，14） *_ Cells（r，10）），2）

Cells（r，20）=Round（（Cells（r，14） * Cells（r，10）），2）

Cells（r，27）=Cells（r-1，27）

以下共6层，代码均以此类推。最后各级原材料成本赋值代码示象下：

Case Else：End Select：Next

Sheets（"Sheet1"）.Select

End Sub

3.3 编制原材料卷积成本代码

定义jicicailiaofei过程和6个变量，即hangshu，lieshu，jish，m和jicifei。M是控制循环次数年的变量，jicifei是原材料每级次的成本。除jicifei类型是double外，其他都是interger。变量定义同上（略）。

hangshu=Cells（Rows.Count， "B"）.End（xlUp）.Row

For r=2 To hangshu

Select Case Cells（r， 12）.Value

Case 1：jicifei=0：jicifei=jicifei+Cells（r，19）

m=1：Do While Cells（r+m，27）=Cells（r，27）

jicifei=jicifei+Cells（r+m，19）

m=m+1

Loop

Cells（r，11）=Round（jicifei，2）

以下共6层，代码均以此类推。最后各级原材料总卷积成本赋值代码如下：

Case Else

End Select

Next

End Sub

3.4 设置计算材料费按钮代码，实现一键②完成计算

定义计算材料费过程。为不使屏幕抖动，在执行计算时加入语句：Application.ScreenUpdating=False，在计算结束后，再恢复其值。运行结束后提示"计算完毕！！！"

3.5 测试及修改代码

运行代码进行测试。有问题时，程序会自动停下来，修改代码再进行调试，如此反复，直到程序能正常运行。

4 结语

程序设计思路很重要，极大地节约了程序的设计时间。原来该工作需耗时2周左右，现在仅需十几秒。操作简便，按一下按钮即可得出想要的结果。目前只能自动生成计算6级以内的产品原材料卷积成本。

参考文献

[1]Excel Home.Excel2007VBA实战技巧精粹[M].北京：人民邮电出版社，2013.

[2]黄朝阳.Excel2010VBA入门与提高[M].北京：电子工业出版社，2014.