基于VBA的包装桶管理系统的实现
作者：靳建水，王卫华，赵庆云
【摘要】包装桶的信息管理繁琐，针对物流管理难点，在原有的EXCEL 表格基础上用VBA 进行开发，制作了包装桶信息管理系统，实现了对5000 多个包装桶的流动跟踪，统计检索。可方便查出外在滞留桶信息等，大大提高了工作效率，确保了统计的准确率，快速性，提高了公司资产的自动化管理水平。
【关键词】VBA；EXCEL；包装桶；管理系统
1 引言
EXCEL 表格处理数据功能强大，应用广泛。在EXCEL 里处理数据操作直观，修改方便，还可以处理较复杂的数学运算及逻辑运算。利用VBA 可以在EXCEL 里进行二次开发，可实现复杂的数据统计[1]。用户对EXCEL 的熟练运用也利于系统应用。系统的应用改变了数据查询，录入信息等手动方式，使包装桶的跟踪管理变得不再杂乱繁琐。
2 系统背景
随着公司快速发展，电解液产量增长较快，从而电解液包装桶也在短短几年从几百个迅速增长至5 000 多个，每天发货桶发给谁，有多少个，发货日期，空桶从哪里回来，回桶数是否一致等都需要准确统计。而包装桶在不同客户的滞留期也不相同，有的1 个月即可回桶，有的3 个月还不能回来。这又要求对外在桶进行统计，看看超过3 个月的有哪些桶，都在哪家客户手里。手动统计在桶数量较少时还能应付，桶数量庞大时便力不从心，效率低下，漏洞百出。不锈钢桶的成本较高，一个2 000 多元，如果丢失严重，对公司资产造成很大损失，因而公司对包装桶自动化管理的需求越来越迫切。
3 系统分析
原有的包装桶手动录入的表格是EXCEL 表格，员工将每个桶对应唯一编号，每500 个桶占一页，按日期对每个桶的发货、回桶进行记录。
从原始记录看，桶的记录比较杂乱，存在的问题主要有：
(1)同一客户的名称录入不一样，例如“哈尔滨光宇”有时省略为“哈光宇”；
(2)很多桶存在信息丢失，例如1# 桶在11.5.30 发给哈尔滨光宇后，回桶记录丢失，后来又在11.5.30 发给东莞正德邦；
(3)存在已经丢失的桶，例如10# 桶和12# 桶没有任何记录，视为丢失；
(4)发货的客户名称和回桶的客户名称应该一致，可很多回桶的客户名称是物流公司。从原始表格提取多个表，组成数据库，从而实现自动检索难度较大，主要困难有：
1)将原始表分解为多个表单，例如分解为发货信息表、回桶信息表等，在这些表中还要包含桶的最后状态，即是否在公司外，在哪家客户手中，滞留了多长时间，某个桶周转了几次，某家客户手中现有那些桶等，操作起来非常复杂。
2)建立起多个表后，还要将5 000 多个桶的信息分别手动录入多个表中，工作量大。
3)很多桶在流通当中存在信息丢失，信息错误等，需要进行核实、修改以符合数据库要求，如表单主键不能为空等[2]。
综合以上情况，决定在原有的EXCEL 表格的基础上进行VBA 开发，不改变现有的表格格式，既符合员工的使用习惯，又减少了开发的工作量，将统计结果显示在指定的页中，便于员工方便地进行打印及二次处理。
4 系统设计
4.1 功能设计
根据用户需求，系统主要实现以下功能：
（1）按桶号检索，即检索出某个桶的所有流通信息。
（2）按客名称检索，即检索出某个客户曾经使用过哪些桶。
（3）按日期检索，即某天发出哪些桶，发给谁；收回哪些桶。
（4）外在包装桶检索，即检索出所有外在桶的信息，具体分为某家客户现滞留哪些桶，滞留多久；也可按日期查出，某天发出去的桶，有哪些还没有回来，都在哪个客户手中。在检索结果中也可统计出哪些桶丢失。
（5）发货桶录入功能。
（6）返回桶录入功能。
在主界面中“包装桶检索”比较直观，选中检索条件，点击“检索按钮”即可在相应页面中显示检索结果。“外在包装桶检索”中分为“一次检索”和“条件检索”，由于原始数据量大，为了提高检索效率，先用“一次检索”统计出所有外在桶的信息，在此基础上再进行条件检索，可大大缩短检索时间，只要原始表格没有改动，“一次检索”使用一次即可。
以前手动录入数据相当麻烦，比如录入0008# 桶的发货信息，需要先打开“0001-0500”页面，找到0008# 桶所在行，再顺着该行找到记录末尾的空格，核实该空格处在“发货日期”列，无误后输入发货日期，客户名称。录入回桶信息和录入发货信息一样，费时费力，很容易看错行，看错列，录入错误时，再一一检查修改，耗费精力。实现自动录入后，只需要在指定区域输入录入信息，点击“输入”按钮，即可将录入信息自动分配到各个桶号位置。在录入前还增加“检查”功能，即核实录入信息是否有误，并在输入区域中自动显示出最近一次的回桶客户名称。例如录入0001# 桶发货信息，当该桶已经发出，最后不是回桶记录，不应该再有发货信息，则这时会提示错误，并自动跳转到相应页面，例如0001# 桶最后信息是发货信息，于11.10.12 发给哈尔滨光宇，这时再录入发货信息，则会提示错误。
在录入回桶信息时也同样增加了“检查”功能，并显示出最近一次发货客户名称。当回桶客户名称与发货客户名称不一样时，能够很方便的看出。0001# 桶的回桶客户名称是“福斯特（岳阳）”，而上一次发货客户名称是“哈尔滨光宇”，这样可以很快发现，回桶信息有误。
4.2 程序设计
4.2.1 检索程序设计。
程序设计是在VB 编辑环境下进行，现以“外在检索”功能为例进行说明。“一次检索”先从原始数据中提取出基础数据，即对每个桶进行判断，最后是发货信息还是回桶信息，如果是“发货信息”说明桶还没有回来，将该桶号和最后一次发货信息提取出来；如果是“回桶信息”则说明桶已经收回，跳过判断下一桶号。由于原始数据是每500 个桶一个页面,系统预留到6 000 个桶，共有12 个页面，针对每一个页面做一个子程序，共有12 个子程序。在主程序中调用各个子程序。
Call 外在桶检索1
Call 外在桶检索2
…
Call 外在桶检索12
由图1 的原始表单可以看出，页面的第1 列是“桶号”，第2 列是“回桶日期”，第3 列是“客户名称”，第4 列是“发货日期”，第5 列是“客户名称”，以此类推，从中可发现，从第4 列开始，每4 列是一个发收循环，而且“发货日期”和发货的“客户名称”是同时出现的，不会只有“发货日期”而没有发货的“客户名称”这种情况，同理“回桶日期”和回桶的“客户名称”也是同时存在。由此建立如下算法，设某桶号所在行的最后数据单元格的列数为n，令z=(n-3)/4，若z<0 则桶丢失，若z 为正整数包括0 则桶在公司内，若z 为正小数则桶在公司外。EXCEL 表格中最大列数是256 列，设某桶号所在的行是x，定义m=256，调用如下语句：
While (IsEmpty(Cells(x, m)))
m = m - 1
Wend
先判断cells(x，256)单元格是否为空，若为空判断cells(x，255) 单元格是否为空，以此类推直到对应的单元格不为空为止，取该单元格的列数值，即为n，再运用上述算法进行判断桶的状态，算法语句如下：
Cells(i + 2, n1 + 4) = (Cells(i + 2, n1 + 3) - 3) / 4
If Cells(i + 2, n1 + 4) < 0 Then
Cells(i + 2, n1 + 5) =“丢失”
ElseIf (Int(Cells(i + 2, n1 + 4)) < Cells(i + 2, n1 + 4)) Then
Cells(i + 2, n1 + 5) =“在外”
Else
Cells(i + 2, n1 + 5) =“在内”
End If
程序中将各个中间变量及结果用数组进行存储，然后再输出到“外在桶检索结果”页面中指定的单元格中，作为条件检索的基础数据。程序中Cells(i + 2, n1 + 3)即为“外在桶检索结果”中存储列数n 的单元格。Cells(i + 2, n1 + 4)用来存储计算结果，Cells(i + 2, n1 + 5)用来显示桶的状态。检索子程序流程图如图1 所示。
条件检索的程序设计主要在初次检索的结果中进行筛选，根据客户要求，按“客户名称”检索时要能够实现模糊检索，比如查询“哈尔滨光宇”滞留了哪些桶，则只需要输入“光宇”即可检索出发货客户名称中包含“光宇”字样的所有信息。这里调用InStr()函数[3]来实现，具体语句如下：
While Not (IsEmpty(Cells(n, 1)))
If InStr(Cells(n, 2), c1) <> 0 Then
a(i) = Cells(n, 1)
b(i) = Cells(n, 2)
c(i) = Cells(n, 3)
i = i + 1
End If
n = n + 1
Wend
当InStr(Cells(n, 2), c1) <> 0 时，说明包含关键字，将桶的信息存入数组中。
4.2.2 录入程序设计。录入程序分为发货信息录入程序和回桶信息录入程序，设计思路基本一致，这里以发货信息录入为例进行说明。在指定区域输入要录入的信息后，先检查信息是否有误，在检查程序里边需要判断桶的当前状态，判断方法和查询外在桶方法一样，用同样的算法语句实现。检查无误，即可启动录入程序，程序先将各个桶的发货信息存储在数组中，然后根据桶号转到相应的表单中，如桶号为3008#，则转入“3001-3500”页面内，在该页面内，找到桶号为“3008”的那一行，接着找出该行最后不为空的单元格，在该单元格后填入对应的发货信息。程序流程图如图2 所示。
5 结论
本系统已投入实际应用3 个月，运行稳定，使用效果良好，对于自动化管理的效果，员工感受颇深。公司的资产管理有很多值得挖潜、提升的地方，加强自动化管理可发挥出强大的作用，既提高了工作效率，节约了人力物力，也增强了公司的竞争力，因而自动化管理值得大家去细化、研究。
[参考文献]
[1]刘利国.基于Excel 的管道工程控制测量计算[J].中国科技博览,2012,(15):128-129.
[2]高阳.数据库技术与应用[M].北京：电子工业出版社，2008.
[3]化柏林.用VBA 实现文献计量分析研究中的数据预处理技术[J].现代图书情报技术,2007,（4）:70-74.
