原材料卷積成本利用ExcelVBA自動生成

李劍俠

摘 要：原材料卷積成本對產(chǎn)品的定價有很大影響，因此快速、及時地生成原材料卷積成本可以使財務(wù)方面能做到心里有數(shù)，提前知道產(chǎn)品的加工成本。該文即利用Excel VBA強大的編程計算功能，通過編寫Excel VBA代碼實現(xiàn)原材料卷積成本的自動生成。解決了人工輸入公式進行計算，需要時間長且易出現(xiàn)錯誤的問題。經(jīng)過試用，效果良好。

關(guān)鍵詞：Excel VBA；卷積成本；原材料成本

中圖分類號：TP31 文獻標志碼：A

0 引言

財務(wù)成本核算或進行價格管理時，需要根據(jù)BOM對生產(chǎn)加工的產(chǎn)品的原材料進行核算，作為控制成本或制定銷售價格的依據(jù)。對于有很多層級的零部件來說，原材料的卷積成本計算是一個非常復雜的過程。人工計算耗時較多，且易出現(xiàn)錯誤。計算一個大約由2 000種零件構(gòu)成的零部件原材料卷積成本，人工輸入計算大約需要2周時間。現(xiàn)為了提早對原材料成本進行預(yù)測，提高工作效率，研究利用Excel VBA對原材料的卷積成本進行自動計算。VBA①是集成在Microsoft Office應(yīng)用程序中的一種程序設(shè)計語言，能夠?qū)崿F(xiàn)Office自動化，從而極大地提升了工作效率。VBA集成在Excel中就是Excel VBA。

1 對測試數(shù)據(jù)分析

該文對原有成本處理過程進行分析。原有數(shù)據(jù)采用手工輸入公式進行計算，公式為最末級的原材料成本為最末級零件的數(shù)量和單件原材料成本的乘積，非末級的原材料成本為（本級的單件原材料成本+下級原材料成本）×本級零件的數(shù)量，即為本級的原材料成本+所有下級的每一級材料成本之和，每級的材料成本=每級的數(shù)量×每級的單件材料成本。即，卷積原材料成本公式為：Cij=Cj+Cj-1+…+Ci（設(shè)J 為該零部件級次，I為該零部件的末級級次，J>=I 則Ci為末級級次的原材料成本，Cj為該零部件各級級次原材料成本，Cij為該級級次卷積的原材料成本）。

2 設(shè)計思路

以數(shù)據(jù)庫的方式去看待數(shù)據(jù)，把對數(shù)據(jù)的手動操作動作分解為以下幾步：

（1）判斷級次，由于只有末級的計算公式是特別的，是最末級零件的數(shù)量和單件原材料成本的乘積；而非末級是本身的材料費加上下級材料費的卷積成本×非末級所需數(shù)量。

（2）根據(jù)級次制出樹形級次零件表。

（3）根據(jù)級次制出樹形級次數(shù)量表。

（4）計算每級本身的原材料成本。

（5）對零部件每級本身的原材料成本進行數(shù)據(jù)透視，并獲取級次卷積原材料成本。

3 設(shè)計代碼

3.1 編制判斷級次代碼

根據(jù)零件號列與哪級的零件號相等，可以判斷其級次為幾 （假定零部件的最大層級為6級來設(shè)計代碼）。為此定義了3個變量，分別為hangshu，lieshu和r，變量類型為integer，意思分別為共有多少行原始數(shù)據(jù)，第幾列（幾級），循環(huán)的行數(shù)。具體代碼如下：

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是控制循環(huán)次數(shù)年的變量，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 設(shè)置計算材料費按鈕代碼，實現(xiàn)一鍵②完成計算

定義計算材料費過程。為不使屏幕抖動，在執(zhí)行計算時加入語句：Application.ScreenUpdating=False，在計算結(jié)束后，再恢復其值。運行結(jié)束后提示"計算完畢！！！"

3.5 測試及修改代碼

運行代碼進行測試。有問題時，程序會自動停下來，修改代碼再進行調(diào)試，如此反復，直到程序能正常運行。

4 結(jié)語

程序設(shè)計思路很重要，極大地節(jié)約了程序的設(shè)計時間。原來該工作需耗時2周左右，現(xiàn)在僅需十幾秒。操作簡便，按一下按鈕即可得出想要的結(jié)果。目前只能自動生成計算6級以內(nèi)的產(chǎn)品原材料卷積成本。

參考文獻

[1]Excel Home.Excel2007VBA實戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2013.

[2]黃朝陽.Excel2010VBA入門與提高[M].北京：電子工業(yè)出版社，2014.