在Excel中利用宏和函数提高计量物资库存查询效率

李焕秋

(国网冀北唐山市丰南区供电公司,河北 唐山063300)

供电公司计量班组在库房管理工作中，出入库物资的种类及数量较多，查询库存量的工作较为烦琐，工作效率不高。库房管理人员做的出入库记录大多在Excel或WPS电子表格中完成，若查询一两种物资的库存量工作量不大，但同时查询几十种或上百种物资的库存量，给库房盘点工作带来不便。根据遇到的实际问题，通过利用电子表格中的宏和函数，编制一个小程序，来解决以上问题，使计量库房管理更加准确、高效。下面以在Excel中利用宏和函数的方法为例，说明该程序的编制过程。

1 程序结构设计

在Excel中新建工作薄，命名为“计量物资出入库记录”，插入4个工作表，分别命名为“入库”“出库”“库存”“sheet1”。按照日常出入库记录中的格式，设计出合理的表格版式，“sheet1”为空表即可。“入库”工作表用来记录各种物资的入库情况，“出库”工作表用来记录各种物资的出库情况，“库存”工作表可以查看全部物资的入库、出库及库存情况、“sheet1”为录制宏的时候使用。在“库存”工作表中点击“开发工具”，点击“插入控件”，插入“ActiveX控件”中的“命令按钮”，加入一个按钮，命名为“数据刷新”。此时，该程序的结构设计基本完成，较为简单，点击“数据刷新”按钮，即可查看全部物资的出入库及库存情况。

2 编制方法

2.1 录制宏

在“入库”工作表中录制一个新的宏，命名为“数据更新”，下面是录制宏的内容。选择“入库”工作表中的“C”到“G”列复制，粘贴到“sheet1”工作表中。在“sheet1”工作表中，选中“A”到“E”列，打开“数据”菜单，选择“删除重复项”按钮，选中“型号”选项，点击“确定”，删除重复值，保留唯一值，如图1所示。

图1 删除重复项

点击右键，对唯一值进行复制，粘贴到“库存”工作表中对应列，点击“停止录制”按钮，宏录制完成。

2.2 插入函数

在“库存”工作表中，“入库数量”一列的“F2”单元格输入公式“=SUMIF(入库!$D:$D,$B2,入库!$H:$H)”，下拉复制大约100行（根据物资种类数量确定），在“出库数量”一列的“G2”单元格输入公式“=SUMIF(出库!$D:$D,$B2,出库!$H:$H)”，下拉复制大约100行即可，在“库存数量”列“H2”单元格输入公式“=F2-G2”，下拉复制大约100行即可。

在“库存”工作表中点击已经插入的“数据刷新”按钮，双击进入VBA设计界面，在Click事件中输入代码“Call数据更新”，可调用宏“数据更新”，完成库存量的查询工作。

目前，程序已经编制完成，点击“数据刷新”按钮，程序未出现中断，运行成功，全部物资的入库、出库及库存数量，全部查询出来。充分证明了VBA技术、宏、函数在Excel或WPS电子表格中强大的生命力，能够化繁为简，提高工作效率。