Excel在资产记账管理中的应用实践

徐欣源

摘 要： Excel作为办公自动化软件擅长处理各种电子表格，利用Excel提供的宏操作、表格函数等功能可以生成多种风格的统计报表，且能够显示数据走向和预测工作进度，这些功能可在一个工作簿中实现。

关键词： 表格； 资产； 管理； 打印； 宏

中图分类号：TP319 文献标志码：B 文章编号：1006-8228（2013）06-72-02

Application of Excel in accounting management

Xu Xinyuan

（Suzhou Power Supply Company Metrology Center， Suzhou， Jiangsu 215000， China）

Abstract： Excel， as office automation software， can deal with all kinds of electronic forms. VBA and form function， provided by the Excel， are used to generate various styles of statistical forms and graphs. Moreover， it can display the data trend and predict the work schedule. These functions can be realized just in one sheet.

Key words： Excel； asset； management； print； VBA

0 引言

人们日常工作需要用到表格，但并不一定知道表格也可以编程。学会Excel编程可以让我们体会到Excel的功能比我们想象的要强大。利用Excel编程可以提高日常工作效率。本文以资产记账管理为例探讨Excel编程的应用。

1 思路

根据开源软件社区的思想，设计一个软件，先让他的基本功能运行起来，也就是先设计一个实用模型图，然后根据实际需要，边使用边完善其功能。电表厂物资的基本流程如图1所示。

图1 电表的基本流程

记账管理所用表单，主要是用于从厂方进货到校验班出货（根据表库的配送计划）的批次与盘点管理。

首先设计表库（包括物供科的进货、出货流水账）。

有三方面的内容：

⑴ 统计盘点表格；

⑵ 进货清单；

⑶ 出货清单。

功能：

⑴ 表格根据进货出货自动完成统计（用公式实现）；

⑵ 在进货单和出货单上反映盘点表格中的当前库存，以便核实。

2 功能实现

2.1 输出统计表格

在统计表格中，用SUMIF语句搜索进货、出货单中符合条件的规格表，对其进行累加。

需要用到VGA编程——调用事件：

<？php

Private Sub Worksheet_Selectionchange（ByVal Target As Range）

'当前单元格的行数大于冻结窗口所在行时

If ActiveCell.Row>ActiveWindow.SplitRow Then

'将A1格的值设定为当前单元格所在行第2列的单元格的值

Range（"A1"）.Value=Cells（ActiveCell.Row， 2）.Value

'当C1格的值可以转化为数值时

End If

End Sub

配合公式：

=IF（ISBLANK（$A），""，OFFSET（新表库实时总存量！$B，MATCH（$A，

品种代码，0），8））

完成这一工作。

2.2 输出需要打印的审批单

根据管理工作所要求的格式，我们需要为每一批进表填写入库审批单。

实现过程是：

新建一张表格作为数据库表单，就是“批次”表单。纪录每一批表的进货和出货及货物地址，以及货物审批单的内容，例如：型号、厂家、常数、表精度、使用周期等。然后，通过审批表单打印输出每一批内容。

代码举例如下：

=OFFSET（批次！$C，MATCH（审批表打印！B4，批次！C：C，0）-2，4）

搜索与审批表打印单的B4（审批号）相符的批次表的一行输出打印该批次内容。

遇到的一个难题是：厂编号有时是字母开头加数字，这就不可以简单地使用公式生成终止厂编号。而需要先用公式

=LOOKUP（9E+307，--MID（C8，MIN（FIND（{0；1；2；3；4；5；6；7；8；9}，

C8&1234567890）），ROW（INDIRECT（"1："&LEN（C8）））））

提取数字，把该数字加电表数量减1等于终止厂号，然后提取字母：

=LEFT（C8，（FINDB（TEXT（F7，"@"），C8）-1））

最后，把字母与结果数字加起来输出：

=CONCATENATE（F8，F9）

2.3 随着数据量的增大，表格的打开速度变慢的处理方法

我们发现批次表上重复信息很多，如果能建立品种数据表单进行调用就能有效避免重复调用，于是建立了“品种”表单，大大地简化了数据。需对“审批表打印”单也做出相应的修改，添加嵌套的OFFSET语句。

2.4 制作界面页

综合以上两个方面的内容基本上就是一个简单的实用模型。在此基础上，为了使界面更加大方、友好，又作了一些页面设计，如图2所示。

图2 表库记账界面

BOOK模块：

<？

Sub auto_open（） '实现打开工作簿后自动关闭所有工具条

Dim Bar As CommandBar

For Each Bar In Application.CommandBars

On Error Resume Next

Bar.Visible=False

Next

Application.DisplayFormulaBar=False

Sheets（"主页面"）.Select '实现打开工作簿时总是出现主页面

Range（"A1"）.Select

End Sub

增加保存退出按钮：

<？

Private Sub CommandButton1_Click（）

ActiveWorkbook.Save

Application.Quit

End Sub

月底初始化程序：

<？

Sub 初始化（）

MsgBox "本操作只有系统管理员可以执行"，

vbExclamation， "危险操作"

MsgBox "备份了吗？"， vbYesNo， "请确认"

Dim t As String

t=InputBox（Chr（13） & Chr（15） & "请输入执行该操作的权限

口令（请勿输错）："）

a=123456

If Val（t）=a Then

月底还原，已录制“月底还原”宏的引用

MsgBox "已成功初始化"， vbInformation， "信息"

ElseIf t <> "" Then

MsgBox "你无权执行该项操作，请与系统管理员联系！"，

vbExclamation， "警告"

End If

End Sub

2.5 其他相关设置

⑴ 把两张表合并为一张表，建立记账主页面，美化表格界面。

⑵ 定义区域，并应用区域内容，以便规范输入文字。

⑶ 利用相对引用，根据批次表的库存信息，变化该条信息颜色。

⑷ 增加筛选用"宏"按钮，以便方便操作。

⑸ 增加常用库存分析表，以便随时掌握当前库存信息，得知进表需求信息和当前工作进度。

2.6 表格的运行说明

表格在使用之前，必须通过Excel软件的工具选项的安全性菜单，把安全性级别降到“中”或“低”，这样才能使表格的程序以“宏”的方式正常运行。如果不运行“宏”，表格的许多功能就不会加载。

3 结束语

本文所述Excel工作簿的应用，经过改进，最后达到了比较完美的效果，提高了日常工作效率，节省了大量的时间。

作为个人的统计工具，Excel足够了，但是它也有自身的缺点：Excel版本的更新带来的在一些配置比较低的机器上运行缓慢（这个主要是因为数据在Excel打开是全部需要调入内存的原因）；数据独占模式，也决定了它只适合个人单独使用，所谓的共享工作簿其实也只是记录了每个人的操作步骤，以便及时还原数据，并不是真正的多人操作。还有数据量增大和公式嵌套增多时出现运行缓慢的问题不可避免。

参考文献：

[1] 韩小良.EXCELVBA高效办公实用宝典（第一版）[M].中国铁道出版

社，2009.

[2] 赵志东.EXCELVBA技巧应用（第一版）[M].人民邮电出版社，2007.

[3] 具体案例：材料进出存明细表，来源：表格之家中文论坛.

[4] 参考相关函数来源：English forum：http：//www.excelforum.com/