基于Excel VBA的軸承成本核算系統(tǒng)

王玉國，張　闐

（洛陽軸承研究所有限公司，河南 洛陽471039）

基于Excel VBA的軸承成本核算系統(tǒng)

王玉國，張闐

（洛陽軸承研究所有限公司，河南 洛陽471039）

摘 要：利用Excel VBA的分析計(jì)算功能，通過不同設(shè)計(jì)方法生成軸承的數(shù)學(xué)模型，建立核算產(chǎn)品各項(xiàng)成本的系統(tǒng)，實(shí)現(xiàn)了軸承成本核算快速、準(zhǔn)確、可隨市場變化的功能，有著非常強(qiáng)的適應(yīng)性。

關(guān)鍵詞：軸承；成本核算；Excel VBA；數(shù)學(xué)模型

1 前言

轉(zhuǎn)型升級、提質(zhì)增效是近幾年我國各個(gè)行業(yè)面臨的首要問題。對于軸承這種傳統(tǒng)基礎(chǔ)行業(yè)，市場競爭日益激烈。軸承的型號有上萬種，但仍不能滿足客戶的不同要求，有時(shí)候非標(biāo)定制也成為很多軸承企業(yè)不得不面對的問題。對于沒有生產(chǎn)過的產(chǎn)品，能否準(zhǔn)確地核算成本，直接決定著軸承的市場競爭力。

為了解決以上問題，必須要對軸承各個(gè)生產(chǎn)過程的成本進(jìn)行綜合準(zhǔn)確的核算。本文介紹了利用Excel VBA的分析計(jì)算功能，快速準(zhǔn)確核算出產(chǎn)品的各項(xiàng)成本的核算系統(tǒng)。

2 成本核算方案

軸承的主要成本包括材料成本、輔助消耗成本、人工成本等。為了比較準(zhǔn)確完整地核算軸承的各項(xiàng)成本，必須以加工的主要工序?yàn)橹骶€，對各個(gè)工序下具體的各項(xiàng)成本進(jìn)行分項(xiàng)計(jì)算，最終核算出軸承各零件的材料、輔助消耗、人工等各項(xiàng)成本。

以軸承套圈為例，套圈的具體的加工工序?yàn)椋哄懺臁嚰庸ぁ鸁崽幚怼ゼ庸ぁb配。

一般軸承各工序的成本主要分為兩種：一種是以材料及能耗成本為主要成本的工序；一種是以人工成本為主要成本的工序。

其中鍛造和熱處理屬于以材料及能耗為主要成本的工序，一般以材料的單重價(jià)格來計(jì)算成本；車加工、磨加工及裝配屬于以人工成本為主要成本的工序，一般以工時(shí)價(jià)格來計(jì)算成本。

3 主程序

3.1 本核算系統(tǒng)的主要流程

參數(shù)輸入——運(yùn)行主程序——優(yōu)化軸承結(jié)構(gòu)參數(shù)生成軸承的數(shù)學(xué)模型——根據(jù)具體的軸承結(jié)構(gòu)參數(shù)對不同材料、不同工序的具體成本進(jìn)行核算——最終輸出計(jì)算結(jié)果。

圖1 程序流程圖

3.2 輸入主參數(shù)

參數(shù)的輸入主要是為了計(jì)算軸承的外形尺寸及計(jì)算系數(shù)，不同結(jié)構(gòu)類型的軸承需要的參數(shù)有所不同，例如，深溝球軸承只需要內(nèi)徑、外徑、高度等基本外形就可以計(jì)算；圓柱滾子軸承除了內(nèi)徑、外徑、高度等參數(shù)外還要提供復(fù)圓尺寸，如表1所示。

表1 軸承主參數(shù)輸入

在成本計(jì)算一欄創(chuàng)建子程序名稱，并選定需要輸入的軸承主參數(shù)，如表2所示。

表2 建立子程序

3.3 程序計(jì)算生成數(shù)學(xué)模型

雙擊子程序回車即可運(yùn)行主程序，該主程序針對不同結(jié)構(gòu)類型的軸承，需要不同編寫。根據(jù)輸入的軸承參數(shù)通過各類軸承的設(shè)計(jì)方法優(yōu)化出接近真正設(shè)計(jì)結(jié)果的軸承參數(shù)，建立數(shù)學(xué)模型。部分代碼如下。

Private Function 圓柱滾子（dd As Double，d As Double，b As Double，F(xiàn)w As Double，Ew As Double，lx As String）As Double

Dim tmp（）As String

If Ew=0 Then

di=Fw

si=（di-dd）/2

Call查表（"E：...圓柱內(nèi)外壁厚差.csv"，tmp， dd）

s=Val（tmp（1））

se=si-s

de=d-2*se

…

End Function

3.4 可更新端口的建立及查詢

各個(gè)工序的成本核算可以利用生成的數(shù)學(xué)模型，分別計(jì)算本工序下產(chǎn)品的具體尺寸及重量。

為了方便數(shù)據(jù)的更新，及時(shí)準(zhǔn)確地計(jì)算零件的成本，需要及時(shí)更新程序中各工序用到的價(jià)格。數(shù)據(jù)端口的文件，擴(kuò)展名為.CSV格式的Excle表格文件，利用以下子程序代碼可以方便地讀取文件中的數(shù)據(jù)。

Sub 查表（文件名 As String，輸出結(jié)果（）As String，條件As Double）

Dim 查詢個(gè)數(shù) As Integer，參數(shù)1 As String，未超出 As Boolean，i As Integer

Open 文件名 For Input As 1#

Line Input #1，參數(shù)

Do

參數(shù)1=Replace（參數(shù)，"，"，""，，1）

查詢個(gè)數(shù)=查詢個(gè)數(shù)+1

Loop Until InStr（參數(shù)，"，"）=0

ReDim 輸出結(jié)果（查詢個(gè)數(shù)）

Do

For i =0 To 查詢個(gè)數(shù)

Input #1，輸出結(jié)果（i）

Next

If 條件1＜=Val（輸出結(jié)果（0））Or 輸出結(jié)果（0）="—" Then 未超出=True:Exit Do

Loop Until EOF（1）

Close#1

End Sub

3.5 針對不同類型的工序計(jì)算成本

3.5.1 以材料及能耗為主的工序成本核算

以鍛造工序?yàn)槔?，?jì)算出了軸承外圈的內(nèi)徑、外徑、高度，就可以通過不同尺寸及結(jié)構(gòu)類型的鍛造留量來計(jì)算該零件鍛件的重量。鍛件的留量值需要通過生產(chǎn)實(shí)踐總結(jié)的數(shù)據(jù)為準(zhǔn)，不同廠家可根據(jù)自己的實(shí)際情況確定，如表3所示。

通過以上代碼，即可在VBA中讀取文件“鍛件留量.csv”中的數(shù)據(jù)，將B列“留量”這一列的數(shù)據(jù)讀入數(shù)組“輸出結(jié)果（）”中。

在運(yùn)行主程序中，通過call命令調(diào)用子程序即可實(shí)現(xiàn)對表格內(nèi)容的查詢，并將其輸入指定變量中，代碼如下：

Call 查表（"E：...鍛件留量.csv"，tmp，d）

鍛件留量=Val（tmp（1））

該段代碼中“查表（"E：...鍛件留量.csv"，tmp，d）”，括號中的參數(shù)要與查表子程序中定義的文件名、輸出結(jié)果、條件一一對應(yīng)。

其中“文件名”需定義為需要查詢的表格的帶路徑的名稱，程序可根據(jù)指定路徑及文件名查詢文件內(nèi)容。

表3 鍛件留量

通過查詢表2，子程序?qū)⒈?中的磨加工費(fèi)輸入到指定變量“磨加工費(fèi)”中。代碼如下：

Call 查表（"E：...單列圓柱磨加工工時(shí).csv"，tmp，dd）

磨加工費(fèi) = Val（tmp（1））

通過以上方法將所有工序的單項(xiàng)成本一一計(jì)算后，總體相加即可得到該軸承整套生產(chǎn)所需的成本。為了區(qū)分不同結(jié)構(gòu)類型的成本，可以分別計(jì)算。代碼如下：

成本NU=套圈成本NU+保持架成本+鉚釘成本+滾動(dòng)體成本。

成本N=套圈成本N+保持架成本+鉚釘成本+滾動(dòng)體成本。

然后通過計(jì)算鍛件所需材料的重量及查詢的材料價(jià)格即可得到計(jì)算零件的鍛件成本。代碼如下：

Call 查表（"E：...\鍛件留量.csv"，tmp，d）

dll=Val（tmp（1））

外圈鍛件價(jià)格=π/4*（b+dll）*（（d+dll）^2-（d2-dll）^2）*7.85*10^（-6）*1.1*材料價(jià)格

Call 查表（"E：...鍛件留量.csv"，tmp，dd2）

ddll=Val（tmp（1））

內(nèi)圈鍛件價(jià)格=π/4*（b+ddll）*（（dd2+ddll）^2-（dd-ddll）^2）*7.85*10^（-6）*1.1*材料價(jià)格

……

3.5.2 以人工成本為主的工序成本核算

以磨加工為例，主要計(jì)算人工成本。需要統(tǒng)計(jì)具體每種類型、各工序、各個(gè)尺寸段下的工時(shí)成本。這個(gè)需要通過大量的數(shù)據(jù)統(tǒng)計(jì)及整理后才能得出比較合理的工時(shí)及工時(shí)單價(jià)，一般軸承廠都會(huì)有自己的工時(shí)系統(tǒng)，可以加以利用即可得出該工序的人工成本。表4是以分尺寸段、分工序針對單列圓柱滾子軸承的磨加工工時(shí)匯總表。

表4 單列圓柱滾子軸承磨加工工時(shí)匯總

……

最后將需要的計(jì)算成本賦值給子程序同名變量，就可將最終計(jì)算結(jié)果顯示到Excle表格中。

If InStr（lx，"N"）Then

圓柱滾子 =成本N

End If

If InStr（lx，"NU"）Then

圓柱滾子 =成本NU

End If

……

3.6 利用Excle拖拽功能批量處理成本核算

現(xiàn)在多數(shù)軸承廠家都有自己的電子樣本，可以利用Excle軟件特有的拖拽功能自動(dòng)實(shí)現(xiàn)幾十個(gè)型號產(chǎn)品的成本核算。整個(gè)計(jì)算過程只需要幾十秒鐘就可以完成，見表5、表6。

表5 拖拽前成本核算

表6 拖拽后成本核算

4 結(jié)束語

本系統(tǒng)利用Excel VBA的強(qiáng)大功能，快速、準(zhǔn)確地將軸承的各項(xiàng)成本核算綜合進(jìn)行，具有拖拽計(jì)算的高效率、數(shù)學(xué)模型的精準(zhǔn)化、數(shù)據(jù)更新的靈活性等優(yōu)異性能，市場應(yīng)用廣泛，使用價(jià)值高。

但該系統(tǒng)還有很多不足的地方，需要通過使用過程中不斷的完善才可以更加方便、快捷、準(zhǔn)確的核算成本，為企業(yè)節(jié)約成本、提高效率做出進(jìn)一步的貢獻(xiàn)。

參考文獻(xiàn)：

［1］ 馬維峰.Excel VBA應(yīng)用開發(fā)從基礎(chǔ)到實(shí)踐［M］.電子工業(yè)出版社，2006.8.

［2］ 羅剛君，章蘭新，黃朝陽.Excel2010VBA編程與實(shí)踐［M］.電子工業(yè)出版社，2010.11.

［3］ 賈育珠.多品種小批量軸承生產(chǎn)成本的核算［J］.軸承，2003（7）.

（編輯：林小江）

中圖分類號：TH133.33

文獻(xiàn)標(biāo)識(shí)碼：B

文章編號：1672-4852（2016）02-0029-04

收稿日期：2016-05-20.

作者簡介：王玉國（1983-），男，工程師.

Bearing cost accounting system based on Excel VBA

Wang Yuguo，Zhang Tian
（Luoyang Bearing Research Institute Co.，Ltd.，Luoyang 471039，China）

Abstract:Using Excel VBA function of analysis and calculation， through the different design methods to generate mathematical model of bearing， in order to establish the accounting system for various products cost ， realize the function of bearing cost accounting fast and accurately and along with the market shift， have the extremely strong compatibility.

Key words:bearing: cost account； Excel VBA；mathematical model