利用Excel及VBA实现单位设备信息化管理.doc

利用Excel及VBA实现单位设备信息化管理摘要：本文通过简述利用Excel管理设备并通过VBA自动实现单位设备的信息化管理。 关键词：Excel VBA 设备的信息化管理 1 情况概述 本人所在单位是一所高校，在过去设备长期使用手工管理的方式，近年随着设备数量增加虽然部分使用Excel，但是因为管理中经手人众多，Excel的使用水平参差不齐再加上没有标准的规范。导致了目前整个设备管理低效、混乱。鉴于这种情况，学校决定全面采用信息化管理。 2 解决方案 按照现状我们决定，采用Excel管理设备并通过VBA自动实现。首阶段目标：全面方便的了解设备情况，对各类设备的数量进行统计。 设计思路：在Excel中建立“设备详细信息”表、“校园楼宇分布示意图”表和“楼层房间分布示意图”表。这3个表在后文中我们分别称为信息表、校园图、楼层图。在校园图和楼层图中表现出各个楼以及楼内部结构，并统计出每个房间的设备数量。需要了解房间设备具体情况时，通过某个操作（比如双击对应目标）显示出该房间设备的详细信息。 3 方案的具体实施 3.1 建立“信息表” 该表内容为设备各种属性，越全面越好。有了这些详细的数据，用数据透视表功能在几十秒内就能按照要求统计结果。需要注意的是，要给每台设备建立一条记录，如1个房间有多台同样设备就要有多条记录，但是设备编号、使用人信息不同。 3.2 建立“校园图”和各楼宇的“楼层图” 用绘制自定义图形画出每一栋楼的外形，然后按照它的地理位置将它分布到合适位置。这样就画出了整个校园的楼宇分布图。 给单元格写入门牌号模拟1个房间，按照房间分布情况1层层排列单元格，就画出了每栋楼的“楼层图”。我们用1张工作表建立1栋楼的示意图，并且将表名称改为对应楼名称。我们在每个门牌号下方留出1个空单元格，调整到合适大小（能够写的下包含的各类设备数量）用来填写统计的设备数量。 3.3 校园图?陴楼层图 在“校园图”中，选中1个楼宇?陴右键单击?陴超链接?陴本文档中对应“楼层图”所在的工作表。这样将来直接单击它就会打开对应楼的“楼层图”。 3.4 统计每个房间的设备情况 通过门牌号要在“信息表”中统计出该房间各类设备的数量，并填到下方空单元格内。 ①建立1个“统计数量”表。②表格第1行建立表头，内容为“门牌号、设备类型（计算机、打印机、笔记本等）。③表格第A列第2 行开始填入所有房间的门牌号。④以门牌号在“信息表”中查询出该房间各类设备数量填入对应类别下。具体可用“=sumproduct((信息!$F$2:$F$3000=统计数量!$A17)*(信息!$G$2:$G$3000=统计数量!B$1))”公式实现。公式说明：该公式用sumproduct函数在“信息表”F列（设备信息中F列为地址）中查询和“统计数量表”$A17（为具体门牌号地址，如：4-206）内容相同的记录，同时又在G列（设备信息中G列为设备类型）中查询和B$1（“统计数量”表头中的设备类型）相同的记录。当2个条件都满足是记一次数。最终统计出全部3000记录中符合条件的设备总数。用同样方法得到其他类别设备总数。⑤用字符串运算符“”将统计总数变成文字。字符运算符，可以直接将前后字符链接起来。例如：“=IF(B3=0,”“,B3”台”B$1”;”)”。公式说明：B3是设备数量（如为1），B1是设备类别（如为计算机），结果是“1台计算机；”。IF用来判断房间没有该类型设备时的情况。用同样方法将别的类型变成文字描述。再用“”将它们都连起来，就形成了该房间设备的完整文字描述。如：“1台计算机；2台打印机；3台笔记本”。⑥将上面的结果直接引用到“楼层图”中门牌号下方空单元格内显示。 3.5 双击门牌号单元格时，高亮显示该房间设备详细记录 后面的功能我们用VBA代码编写的宏实现。 This WorkBook中的代码： “双击鼠标左键”事件宏 这个宏用来在工作表中发生“双击鼠标左键”事件时，调用“高亮显示对应记录”宏 Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 高亮显示对应记录 End Sub 模块中的代码： “高亮显示对应记录”宏 这个宏用来在双击一个门牌号单元过后，将地址相对应的记录设置为高亮。过程如下：①将被双击的 “门牌号”赋值给1个变量。②选取信息表?陴清除整个背景色?陴字体设为默认色。③用For Next建立1个循环，循环次数为表中据记录条数。④在循环中将每一记录对应地址单元格内容和赋值变量比较，当相同时把这一行设置为高亮，进行下一次循