基于Excel VBA的轴承成本核算系统

王玉国，张　阗

（洛阳轴承研究所有限公司，河南 洛阳471039）

基于Excel VBA的轴承成本核算系统

王玉国，张阗

（洛阳轴承研究所有限公司，河南 洛阳471039）

摘 要：利用Excel VBA的分析计算功能，通过不同设计方法生成轴承的数学模型，建立核算产品各项成本的系统，实现了轴承成本核算快速、准确、可随市场变化的功能，有着非常强的适应性。

关键词：轴承；成本核算；Excel VBA；数学模型

1 前言

转型升级、提质增效是近几年我国各个行业面临的首要问题。对于轴承这种传统基础行业，市场竞争日益激烈。轴承的型号有上万种，但仍不能满足客户的不同要求，有时候非标定制也成为很多轴承企业不得不面对的问题。对于没有生产过的产品，能否准确地核算成本，直接决定着轴承的市场竞争力。

为了解决以上问题，必须要对轴承各个生产过程的成本进行综合准确的核算。本文介绍了利用Excel VBA的分析计算功能，快速准确核算出产品的各项成本的核算系统。

2 成本核算方案

轴承的主要成本包括材料成本、辅助消耗成本、人工成本等。为了比较准确完整地核算轴承的各项成本，必须以加工的主要工序为主线，对各个工序下具体的各项成本进行分项计算，最终核算出轴承各零件的材料、辅助消耗、人工等各项成本。

以轴承套圈为例，套圈的具体的加工工序为：锻造→车加工→热处理→磨加工→装配。

一般轴承各工序的成本主要分为两种：一种是以材料及能耗成本为主要成本的工序；一种是以人工成本为主要成本的工序。

其中锻造和热处理属于以材料及能耗为主要成本的工序，一般以材料的单重价格来计算成本；车加工、磨加工及装配属于以人工成本为主要成本的工序，一般以工时价格来计算成本。

3 主程序

3.1 本核算系统的主要流程

参数输入——运行主程序——优化轴承结构参数生成轴承的数学模型——根据具体的轴承结构参数对不同材料、不同工序的具体成本进行核算——最终输出计算结果。

图1 程序流程图

3.2 输入主参数

参数的输入主要是为了计算轴承的外形尺寸及计算系数，不同结构类型的轴承需要的参数有所不同，例如，深沟球轴承只需要内径、外径、高度等基本外形就可以计算；圆柱滚子轴承除了内径、外径、高度等参数外还要提供复圆尺寸，如表1所示。

表1 轴承主参数输入

在成本计算一栏创建子程序名称，并选定需要输入的轴承主参数，如表2所示。

表2 建立子程序

3.3 程序计算生成数学模型

双击子程序回车即可运行主程序，该主程序针对不同结构类型的轴承，需要不同编写。根据输入的轴承参数通过各类轴承的设计方法优化出接近真正设计结果的轴承参数，建立数学模型。部分代码如下。

Private Function 圆柱滚子（dd As Double，d As Double，b As Double，Fw 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：...圆柱内外壁厚差.csv"，tmp， dd）

s=Val（tmp（1））

se=si-s

de=d-2*se

…

End Function

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

各个工序的成本核算可以利用生成的数学模型，分别计算本工序下产品的具体尺寸及重量。

为了方便数据的更新，及时准确地计算零件的成本，需要及时更新程序中各工序用到的价格。数据端口的文件，扩展名为.CSV格式的Excle表格文件，利用以下子程序代码可以方便地读取文件中的数据。

Sub 查表（文件名 As String，输出结果（）As String，条件As Double）

Dim 查询个数 As Integer，参数1 As String，未超出 As Boolean，i As Integer

Open 文件名 For Input As 1#

Line Input #1，参数

Do

参数1=Replace（参数，"，"，""，，1）

查询个数=查询个数+1

Loop Until InStr（参数，"，"）=0

ReDim 输出结果（查询个数）

Do

For i =0 To 查询个数

Input #1，输出结果（i）

Next

If 条件1＜=Val（输出结果（0））Or 输出结果（0）="—" Then 未超出=True:Exit Do

Loop Until EOF（1）

Close#1

End Sub

3.5 针对不同类型的工序计算成本

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

以锻造工序为例，计算出了轴承外圈的内径、外径、高度，就可以通过不同尺寸及结构类型的锻造留量来计算该零件锻件的重量。锻件的留量值需要通过生产实践总结的数据为准，不同厂家可根据自己的实际情况确定，如表3所示。

通过以上代码，即可在VBA中读取文件“锻件留量.csv”中的数据，将B列“留量”这一列的数据读入数组“输出结果（）”中。

在运行主程序中，通过call命令调用子程序即可实现对表格内容的查询，并将其输入指定变量中，代码如下：

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

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

该段代码中“查表（"E：...锻件留量.csv"，tmp，d）”，括号中的参数要与查表子程序中定义的文件名、输出结果、条件一一对应。

其中“文件名”需定义为需要查询的表格的带路径的名称，程序可根据指定路径及文件名查询文件内容。

表3 锻件留量

通过查询表2，子程序将表2中的磨加工费输入到指定变量“磨加工费”中。代码如下：

Call 查表（"E：...单列圆柱磨加工工时.csv"，tmp，dd）

磨加工费 = Val（tmp（1））

通过以上方法将所有工序的单项成本一一计算后，总体相加即可得到该轴承整套生产所需的成本。为了区分不同结构类型的成本，可以分别计算。代码如下：

成本NU=套圈成本NU+保持架成本+铆钉成本+滚动体成本。

成本N=套圈成本N+保持架成本+铆钉成本+滚动体成本。

然后通过计算锻件所需材料的重量及查询的材料价格即可得到计算零件的锻件成本。代码如下：

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

dll=Val（tmp（1））

外圈锻件价格=π/4*（b+dll）*（（d+dll）^2-（d2-dll）^2）*7.85*10^（-6）*1.1*材料价格

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

ddll=Val（tmp（1））

内圈锻件价格=π/4*（b+ddll）*（（dd2+ddll）^2-（dd-ddll）^2）*7.85*10^（-6）*1.1*材料价格

……

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

以磨加工为例，主要计算人工成本。需要统计具体每种类型、各工序、各个尺寸段下的工时成本。这个需要通过大量的数据统计及整理后才能得出比较合理的工时及工时单价，一般轴承厂都会有自己的工时系统，可以加以利用即可得出该工序的人工成本。表4是以分尺寸段、分工序针对单列圆柱滚子轴承的磨加工工时汇总表。

表4 单列圆柱滚子轴承磨加工工时汇总

……

最后将需要的计算成本赋值给子程序同名变量，就可将最终计算结果显示到Excle表格中。

If InStr（lx，"N"）Then

圆柱滚子 =成本N

End If

If InStr（lx，"NU"）Then

圆柱滚子 =成本NU

End If

……

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

现在多数轴承厂家都有自己的电子样本，可以利用Excle软件特有的拖拽功能自动实现几十个型号产品的成本核算。整个计算过程只需要几十秒钟就可以完成，见表5、表6。

表5 拖拽前成本核算

表6 拖拽后成本核算

4 结束语

本系统利用Excel VBA的强大功能，快速、准确地将轴承的各项成本核算综合进行，具有拖拽计算的高效率、数学模型的精准化、数据更新的灵活性等优异性能，市场应用广泛，使用价值高。

但该系统还有很多不足的地方，需要通过使用过程中不断的完善才可以更加方便、快捷、准确的核算成本，为企业节约成本、提高效率做出进一步的贡献。

参考文献：

［1］ 马维峰.Excel VBA应用开发从基础到实践［M］.电子工业出版社，2006.8.

［2］ 罗刚君，章兰新，黄朝阳.Excel2010VBA编程与实践［M］.电子工业出版社，2010.11.

［3］ 贾育珠.多品种小批量轴承生产成本的核算［J］.轴承，2003（7）.

（编辑：林小江）

中图分类号：TH133.33

文献标识码：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