基于VBA语言的Excel二次开发在静态病害数据处理中的应用

基于VBA语言的Excel二次开发在
静态病害数据处理中的应用
潘洋1王章骏2
（1.广州地铁设计研究院股份有限公司，广东 广州 510000；2.中国铁路上海局集团有限公司
杭州工务段，浙江 衢州 310000）
摘要：编制生产计划是车间的一项重要工作，利用Excel对静态病害数据库进行分析处理是编制生产计划的重要依据之一。

文章利用VBA语言对Excel进行了二次开发。

通过循环识别病害数据库中临时补修以及更高级别的病害及其道岔位置、病害是否位于转辙部位等信息，依据车站、岔道号、病害是否位于转辙部分等属性对病害信息进行多条件排序，实现了道岔扣分情况的自动统计。

基于VBA语言的二次开发实现了车间病害库数据的自动分析处理，提高了车间的工作效率，探索了车间自动化办公的新途径。

关键词：Excel；VBA；数据处理
中图分类号：TP311 文献标识码：A 文章编号：2096-1936（2023）06-0005-04
DOI：10.19301/ki.zncs.2023.06.002
Application of Excel secondary development based on VBA language in
static disease data processing
PAN Yang WANG Zhang-jun
Abstract：The preparation of production plan is an important work in the workshop, and the analysis and processing of static disease database by using Excel is one of the important bases for the preparation of production plan. In the paper, the secondary development of Excel is carried out by using VBA language. Firstly, the information of temporary repair and above grade of disease and its turnout location, whether the disease is located in the rutting part, etc. in the disease database is identified by circulation, and then the disease information is sorted by multiple conditions based on the attributes of station, turnout number, whether the disease is located in the rutting part, etc., and the automatic statistics of turnout deduction is realized. The secondary development based on VBA language realizes the automatic analysis and processing of the workshop's disease database data, which greatly improves the workshop's working efficiency and explores a new way of the workshop's automated Office.
Key words：Excel; VBA; data processing
1　研究背景与意义
Excel因强大的功能与友好的交互界面，被广泛地应用到各种各样的办公场景之中。

利用该软件可以完成信息保存、数据计算、数据分析、信息动态发布等任务[1]。

虽然Excel已经集成了较多的辅助运算功能，但在许多办公场景下，工作人员经常面对数据量大、操作重复性高、处理步骤烦琐等问题，人力、物力和时间投入较多，严重影响工作效率。

若将此类工作内容的处理方式转化为程序操作自动完成，将大
收稿日期：2023-02-15
作者简介：潘洋，硕士，助理工程师，研究方向为交通运输规划与管理。

引用本文：潘洋,王章骏.基于VBA语言的Excel二次开发在静态病害数据处理中的应用[J].智能城市,2023,9(6):5-8.
幅提高工作效率，进一步保障结果的精确性[2]。

Microsoft Visual Basic for Applications，简写为VBA是Microsoft Office的核心组件之一，作为嵌入式二次开发引擎被内置到Excel软件中[3]。

在Office 97版之后，Excel增了Visual Basic编辑器，可以方便地编辑VBA程序。

因此，文章将在VBA语言环境下对Excel进行二次开发。

在编制生产计划时，静态病害数据库是必须分析的对象。

安全生产系统可以导出“病害库情况表”，以便对病害数据展开进一步分析。

在分析静态病害数据时，常涉及的操作包括区分病害等级、判断病害是否位于转辙部位、添加道岔上下行侧的位置信息以及各组道岔的扣分情况等。

当病害条数较多时，操作步骤一般需要投入较多的时间成本，且效率较低。

文章将针对该问题，进行了Excel的二次开发，实现病害数据的自动处理，以提高工作效率与精确度。

Excel二次开发的应用不仅限于此，但目前在车间的工作中利用Excel的二次开发较少，文章旨在将Excel二次开发的思想引入日常工作中。

2　病害数据自动识别方案及实现
病害数据自动识别程序流程图如图1所示。

用户在Excel中的每一步操作都可以通过“开发工具”选项卡中的宏录制进行记录，然后获知每一步操作的命令行，极大地降低了Excel二次开发的学习成本，避免了底层功能的重复开发。

文章将利用宏命令，调用Excel的基础功能，并将所有的操作步骤整合到程序中加以实现。

2.1　整体程序流程设计
为实现病害库情况表的自动处理与分析，优先对程序的整体流程进行设计。

整个程序可以分为两个循环。

在第一个循环中实现了临时补修以及更高等级病害用醒目色标注，添加道岔的上下行侧信息，判断是否位于转辙部位等功能。

在第二个循环中实现道岔的扣分信息统计。

2.2　行列的删除与添加
由于安全生产系统导出的病害情况表格形式统一且不变，所以表中每一个项目所在列均保持一致，区别在于病害内容以及条数。

对编程工作而言，借助列数索引可以避免复杂的字段检索，借此达到增删的目的。

删除首行与首列的代码为：Rows(1).Delete
Columns(1).Delete
如果需要一次性删除多行或多列，可以利用Union命令选择某一区域，然后执行删除操作。

删除多行与多列的代码为：Union(Range("A: A"),Range("C:D")).Delete Shift:=xlToLeft。

演示代码表示为删除第A，C，D列。

除删除外，还需要向表格中添加额外的统计项目，此过程需要添加额外的列，文章以添加“病害是否位于转辙部位”为例，代码表示为：
aur=Worksheets(1).UsedRange
icols=UBound(aur,2)
Cells(1,icols+1)="病害是否位于转辙部位"
代码中，前两行为明确最后一列的位置，最后一行添加了一列新的统计项目。

2.3　循环判断与信息记录
第一个大循环中欲实现的功能如区分病害等级，判断病害是否位于转辙部位，所依据的信息均包含在生产系统导出的表格中，只需要简单判断就可以实现。

以判断病害是否位于转辙部位为例，判断病害位置的代码为：
For i=2 to icows
’判断是否包含“转辙”字段
If InStr(Cells(i,N).Value,"转辙")＞0 Then
Cells(i,M)="是"
’将单元格标红
Cells(i,M).Intetior.ColorIndex=3
End if
Next i 图1　病害数据自动识别程序流程图
代码中，N与M为常量，分别代表“病害项目”列位置与“是否位于转辙部位”列位置。

程序执行步骤：判断第i行病害的N列是否包含“转辙”字段，若包含，则病害位于转辙部位，则在M记录“是”，并将单元格标红，接着判断下一个病害，直到结束。

道岔的上下行侧信息并不存在于生产系统导出的表格中，需要根据实际先行添加信息条件。

假设有车站A，含有4组道岔，其中1、2号道岔位于上行侧；3、4号道岔位于下行侧，则判断道岔位置的代码为：
For i=2 to icows
’将道岔编号赋值给dc
dc=Cells(i,M).Value
’将车站名赋值给sta
sta=Cells(i,N).Value
’道岔名为车站名+N+道岔编号，如，AN1
dc=station&"N"&dc
Select Case dc
’若是AN1，AN2道岔，同时完成信息录入Case"AN1","AN2"
’则在“行别”列记录“上”
Cells(i,k)="上"
Case Else
’其他则在“行别”列记录“下”
Cells(i,4)="下"
End Select
Next i
利用case语句，可以同时实现上下行信息的录入与判断。

2.4　多条件排序
在实际生产过程中，病害库的病害数据是在不同的时间逐条录入的，即同一设备的病害可能不在一段时间内连续录入，这就导致段生产系统导出的病害库情况表中同一设备的病害数据没有集中排列，更多地表现为散乱分布于整个表中。

在编制生产计划时，一组道岔设备的扣分总数是一个重要的参照指标。

转辙部位与非转辙部位的扣分项要分开统计，所以有必要对表中的病害内容进行重新排序，可以方便第二个大循环的执行，也能够在一定程度上为生产计划编制人员提供便利。

文章对病害按“车站”“道岔号”“是否位于转辙部位”等三项进行排序，排序后，病害数据将会先根据车站集中，再根据道岔号集中，最后按照是
否位于转辙部位进行集中。

Excel的多条件排序可以利用sort函数，病害多条件排序的代码为：
’清除已存在的排序设置
ActiveWorkbook.Worksheets("sheet1").Sort.Sort. Fields.Clear
’以下设置第一个排序条件，即按“车站”排序
ActiveWorkbook.Worksheets("sheet1").Sort.Sort. Fields.Add Key:=Range("M2:M"&irows),
SortOn:=xlSortOnValues,
Order:=xlAscending,
DataOption:=xlSortNormal
’以下设置第二个排序条件，即按“道岔号”排序
ActiveWorkbook.Worksheets("sheet1").Sort.Sort. Fields.Add Key:=Range("N2:N"&irows),
SortOn:=xlSortOnValues,
Order:=xlAscending,
DataOption:=xlSortTextAsNumbers
’以下设置第三个排序条件，即按“是否位于转辙部位”排序
ActiveWorkbook.Worksheets("sheet1").Sort.Sort. Fields.Add Key:=Range("K2:K"&irows),
SortOn:=xlSortOnValues,
Order:=xlAscending,
DataOption:=xlSortNormal
’以下执行排序
With ActiveWorkbook.Worksheets("sheet1").Sort
.SetRange Range("A1:Z"&irows)
.Header=xlYes
.MatchCase=False
.Orientation=xlTopToBottom
.SortMethod=xlPinYin
.Apply
End With
代码中，M、N、K、Z均为常量，分别代表“车站”列、“道岔号”列、“是否位于转辙部位”列以及最后一列。

2.5　道岔扣分情况统计
第二个循环中，需要对每一组道岔分别进行扣分统计，并按转辙部位与非转辙部位分开统计。

由于病害设备不完全是道岔，还包含股道或者线路，此部分内容不参与统计，所以程序流程较为复杂。

道岔扣分程序流程如图2所示。

3　程序执行结果及分析
文章随机抽取段生产系统静态病害库中的数据共计485条，导出后如图3所示，为了方便展示，图中表格省略了一部分列与行。

由图2可以得到，导出的表格“总扣分数”并不是设备的总体扣分，仅为某条病害项目的单条扣分；“行别”列统计了线路设备，对道岔所处上下行侧方面的信息还需补充；“超限等级”列所有的等级格式均一致，难以让工作人员快速地识别出临时补修和超临修等级的病害；由“道岔号”列可以发现，同一道岔设备并没有集中分布，这对分析工作造成了不便。

Excel执行程序只需要在“宏”选项卡中选中准备执行的程序，并点击“运行”即可。

文章所编程序执行后效果如图3所示。

由处理后的表格可以看出，“行别”列已经添加了道岔的上下行侧信息，并使用不同颜色对内容进行了区分；“超限等级”列将严重病害用醒目的
配色标出；观察“道岔号”列，可以发现所有的设备已经按设备名称集中排列；在列的最后添加了“是否位于转辙部位”列以及“道岔扣分情况”列。

在“是否位于转辙部位”列，程序进行了判断，并将位于转辙部位的病害记录为“是”，然后进行深色标记；在“道岔扣分情况”列，程序对每一组道岔按转辙部位与非转辙部位分别进行了扣分统计，并在记录结果后标注不同颜色。

至此，文章对病害情况表进行的分析项目全部由程序实现了自动处理，且结果准确。

4　结语
文章利用VBA语言进行了Excel的二次开发，将程序自动化办公的思想引入到车间的生产工作中，实现了静态数据库的自动处理，极大地提高了工作效率。

在未来的工作中，应加强对自动化办公的探索，以避免其他种类的重复性、烦琐性操作。

参考文献
[1] 刘铭.基于VBA 对Excel 2007二次开发应用研究[J].现代
电子技术,2010,33(10):55-57,62.
[2] 田静.Excel 二次开发在工程物资管理中的应用[J].智能城
市,2020,6(12):134-135.
[3] 李防.完全掌握Excel VBA 高效办公超级手册[M].北京:机
械工业出版社,2011.
图2　道岔扣分程序流程
图3　程序处理后的病害库情况表。