基于EXCEL的生产计划进度管理 系统的设计与实现

李成华，崔坤明，刘陨双，陈 伟，何源源，朱志坤

(航空工业成都飞机工业(集团)有限责任公司，成都 610092)

0 引言

随着产品生命周期和交货期的缩短，企业的快速交货能力和响应需求变化能力是企业在未来竞争中取得成功的关键[1]。为解决生产过程中的信息准确、快速的传递，提升生产环节与产品设计、生产计划等环节的信息交互，航空制造业中运用ERP等信息系统来效整合生产过程中内外部物流、资金流、信息流，实现生产资源优化配置[2]。ERP的运用提高了企业的信息化水平、促进了工作效率的提升，但是它作为一个信息系统并不能完全涵盖用户实际的所有需求，目前，发现在生产管理过程中由于公司采取“架次管理”而生产车间采取“批次管理”，在“架次管理”数据向“批次管理”模式转化的过程中存在工作量大，数据失真等现象，造成制造车间生产的零件与公司需求匹配出现问题，影响整机交付进度。

航空制造业属于单件小批量生产，因此该行业内生产管理方面的研究大都是基于“架次管理”思路进行零件准时配套率方面的研究。目前公司的生产管理系统也是基于“架次管理”相关理论开发的；“批次管理”是被广泛熟知的管理模式，有相应成熟的理论，在生产中具有缩短零件加工周期，加强产品质量跟踪，降低生产成本等诸多好处，广泛运用于实际生产。两种生产模式往往是运用于不同的生产环境而独立存在，而在航空制造业中，对于零件车间来讲，特别是小件生产车间，由于零件数量种类多，而又同时面对两种管理模式，给生产带来了巨大的挑战。对制造车间的生产管理人员来讲，首先，接收公司的基于“架次管理”的生产数据，然后，自我内化生成车间“批次管理”的生产数据，且按批生产的零件必须满足公司“架次管理”的零件交付节点，在数据转换过程中存在诸多难题。由于批架次数据互相转换仅在较少的环境中出现，因此，与此相关的研究较少，因此深入研究批架次数据转换对生产管理有一定的现实意义和实用价值。

本文针对架次管理数据，结合制造车间批次管理的生产实际和满足公司架次管理的零件交付节点的要求，基于ADO技术，VBA二次开发技术，数据库原理等开发生产进度管理系统，自动实现公司“架次计划”和车间“批次计划”数据的自动匹配与关联，并明确了零件的交付节点，质量编号等关键信息。

通过系统的使用，应该能将计划员从繁琐计划编排中解脱出来，将精力更多的放在进度监控、生产能力平衡等更重要的工作中，解决由于公司“架次管理”和机加车间的“批次管理”[3]的冲突造成部分零件进度监管失控，交付节点不能保证的问题，对促进生产顺利进行，提高员工工作效率有显著的促进作用。

1 现状与需求分析

1.1 “架次管理”与“批次管理”

公司以整机交付为目标，因此，在管理方面是以整机为对象，采取架次管理的方式[4]，将机型进行拆分，依据零件装配的先后顺序对各个零件制定交付节点，只有零件节点都保证了，才能保证装配按照计划进度有序进行，如果有零件在需要装配的时候没有配套到位，将造成装配无法进行，直至缺项零件到位为止。在公司计划中不同架次的同一零件交付节点通常不一样。

生产车间以零件交付为目标，为了减少浪费，采取批次管理的方式，将公司计划中多架次的同一图号零件按经济批量组批进行投产。采取小批量生产的方式，不仅减少了准备时间在每一个零件总生产时间中的比重，而且还减少了零件在生产流转过程中的浪费和等待时间，提高了设备利用率，降低了生产成本。组批生产的零件总数为该批次包含架次零件的总和，该批零件的交付时间为架次中的最早交付时间。“批次管理”和“架次管理”示意图如图1所示。

图1 “批次管理”和“架次管理”示意图

1.2 现存问题

从计划员编排生产计划方面考虑，目前依靠人为的将多架次零件组批进行投产，人工进行计划的拆分重组，计划完工时间的确定等，由于数据量大，完全依靠人工进行计划的维护，不仅工作复杂繁琐，而且数据在“架次管理”和“批次管理”的转换的过程中存在信息丢失，造成部分零件遗漏监控，存在直到零件快要交付时候才发现并未投产，造成制造车间相当被动。

工段在执行生产任务是也存在诸多问题。由于车间生产任务量大，工段的生产任务很饱满，工人在加工过程中不是按照零件的缓急程度顺次加工零件，而是选择工时高，油水重的零件先加工，将油水少的零件放一边。由于他们并不关心零件的交付节点，在利益的驱动下经常造成交付节点紧急而工时不高的零件一直处于等待加工状态[5]，此时如果调度员再遗漏了对该项零件的催促，那么该漏项零件交付必然存在问题。

1.3 目标需求

通过上述分析，在组批计划编制过程中，由于人工无法有效处理大量数据，存在数据的丢失等现象，从而造成生产目标不明确，零件交付节点无法保证。因此，应梳理现工作流程，通过开发工具软件，解决流程中的数据转换问题，使公司的“架次管理”计划数据和制造车间的“批次管理”计划数据实现无缝链接，做到生产目标明确，计划有序进行，提高零件准时交付率。

2 生产计划进度管理系统的设计与实现

2.1 EXCEL二次开发可通过三种方式进行

(1)VBA内部开发

所谓内部开发就是使用EXCE内嵌的VBA来开发。用VBA对EXCEL二次开发简单、方便、效率高，并且用它开发也可以使用自己设计的界面[6]，把数据存储于EXCEL的单元格中。

(2)COM插件的开发

所谓的COM是Component Object Model的缩写，是组建对象模型的意思，COM插件也叫COM加载项，实际上是一个动态链接库，我们可以开发程序作为动态链接库连接到程序中，从而扩展其功能。通常COM插件没有自己的界面，只是在宿主程序中集成一个菜单和命令条，然后等待响应。

(3)Automation技术

此种方法是通过VB、VC等高级编程语言，利用Automation技术访问EXCEL对象的方法和属性来获取，设置及处理数据。

从需求和效率方面考虑，本文采用VBA来进行二次开发，完成生产计划进度管理系统的开发。

2.2 工作流程分析

生产车间的最终目的完成承接公司的生产任务，因此车间计划员首先消化吸收公司下达的架次计划，结合车间实际，确定零件的经济批量[7]，然后按经济批量编制组批计划，组批计划需要完善相关信息，包括：起始架次，终止架次，计划完工，最迟完工，质量编号，下达数量，完工数量。然后按组批计划下达制造大纲，生产工段按照计划进行生产，如果生产过程顺利，零件按时入库，该计划顺利完成，并在计划表中对已经完成的计划做标识，如果生产出现差错，如零件报废，延期等，则该项计划完成失败，需根据实际情况对组批计划表中数据做适当调整，并完善相关信息，然后重新按计划执行。最后通过核查组批计划表中所有计划的完工状态，如果计划均完工，则该机型当前批生产完成，工作结束。计划员工作流程图如图2所示。

图2 计划员工作流程图

目前的工作流程是合理的，但是全程皆人工操作效率低，错误率高，通过软件计算来完成流程中的关键环节，解决现阶段批计划编制的关键问题。

2.3 数据关系分析

公司架次管理的计划数据可通过公司ERP系统导出为Excel文件，数据中包含与零件加工所有相关的信息共计72项，但是与生产计划相关的属性有“图号”，“起始架次”，“终止架次”，“单机数量”，“计划完工”，“最迟完工”共6项，这些属性同时也存在制造车间计划表中。

为了让零件有追溯性，生产车间为每一批次的零件编有唯一的质量编号，质量编号为零件的“身份证”，同一质量编号的零件有完全相同的特性，可以互换使用。为了让公司表中的计划数据与零件相对应，需要在公司计划表中添加“质量编号”列来接收车间计划表数据的写入。

公司计划表中计划是按照架次安排的，计划中与数量相关的有“单机数量”和“完工数量”，两者的值相等，在车间计划表中与数量相关的有“下达数量”和“完工数量”，“下达数量”是单批次经济加工数量，但是在实际生产过程中难免会报废零件，因此“完工数量”为最终加工成功的数量，“完工数量”≤“下达数量”。在编排生产计划时将公司计划表与制造车间计划表数据以图号作为关键字进行匹配，车间计划表中的一条记录与公司计划表中的多条记录相对应。对应关系式通过“单机数量”与“下达数量”之间的求和关系决定的，多条记录中的“单机数量”列求和的值为车间计划表中的“下达数量”。当零件入库后，计划员会将零件实际完工数量填入制造车间计划表中，在后续核查计划实际完工的情况时候，通过将“完工数量”按公司架次计划表中“单机数量”逐项分解，并将“完工数量”写入。

制造车间计划表中的“图号”，“下达数量”，“完工数量”，“质量编号”，“批次”为制定计划时候人工填入的数据，其余数据需要从公司计划表中自动写入。在计划下达时仅填写“下达数量”，此时“完工数量”为空，为了使“批次管理”的计划能够保证公司“架次管理”计划的节点需要，车间表中的起始架次为公司表中最小的架次，终止架次为最大的架次，计划完工时间和最迟完工时间均为公司表中的最小时间。公司计划表与制造车间计划表之间的数据关系以及数据的互相写入方式如图3所示。

图3 数据关系图

当两类计划运算关联后，将公司计划表中匹配的计划标记为“蓝色”；公司计划表中未标色的记录代表需要继续下达计划的零件计划清单，为计划员提供编制计划依据。当零件入库后，计划员将车间计划表中的“完工数量”完善，通过运行计划核查程序，将利用“完工数量”进行计划的分解，公司计划表中实际完工的计划将标记为“绿色”，最终通过不同的颜色显性的标识每一条计划的状态。两表中计划的一对多关系图如图4所示。图4中架次1～架次n为单批次加工完成架次计划，实际完成为架次1～架次5(标记为绿色)，架次6～架次n(标记为蓝色)为未完成的计划，需要重新下达补制计划。

图4 一对多关系图

公司架次计划表和车间批次计划表数据关联后的对应关系图如图5所示。

图5 架次计划与批次计划关系图

2.4 基于循环嵌套的比较算法

由于是要提取架次计划表中数据将批次计划表信息完善，因此需要利用循环嵌套的结构，且外层循环为制造车间的批次计划表中记录的循环，内层循环为公司架次计划表中记录的循环。外层循环将读取制造车间计划中的相应数据，通过在内层循环中利用“下达数量”和“完工数量”与公司计划中的“单机数量”进行运算匹配，同时完善公司计划表中的“完工数量”，为了提高用户体验对计划的状态用不同的颜色进行标注。此算法要求零件“单机数量”小于等于车间计划表中的“下达数量”时才能进行计划的分解。程序主要流程图如图6所示。

图6 程序主要流程图

2.5 通过排序提高数据匹配效率

批计划表中需要从架次计划表中提取起止架次及完工时间，如果靠编程去计算满足条件记录集中的完工时间的和架次大小的比较将会面对大量比较、排序操作，效率低。为了避免上述算法的缺陷，通过进行数据匹配前，将公司架次计划表中记录依据“图号”，“计划完工”，“最迟完工”，“架次”为关键词顺次升序排序，然后选取满足条件记录集的“首记录”和“尾记录”即可达到目的，快捷高效。

排序通过调用工作表对象Worksheet下的Sort方法，设置相应的参数即可快速实现数据的排序[8]，而不用人工采取“冒泡法”去排序。由于要实现多关键字的组合排序，只需要设置不同的参数，依次调用几次Sort方法即可。排序的核心伪代码如下：

Private Sub SortData()

Dim partnamecol as Integer′零件图号所在列数

‘companysheet表中的数据按图号升序排列

Worksheets("companysheet").Sort.SortFields.Add Key:=Columns(partnamecol), SortOn:=xlSortOnValues,

Order:=xlAscending,_ DataOption:=xlSortNormal'

“companysheet”

…

‘类似的重复调用几次，可实现多关键字组合排序。

End Sub

2.6 补制计划的输出

在制造过程中出现零件报废，需要对报废零件重新下达补制计划。通过将目前总的完成数量按照架次计划中的“单机数量”进行拆分，直到完成数量全部分解到具体的每项计划中，剩下未被分配到的计划为需补制计划，补制计划中的“完工数量”<“单机数量”。通过综合考虑“单机数量”，“完工数量”和缺项计划三要素，可以明确的得出某一架次缺项零件清单及零件项数。

2.7 人机交互优化

在实际工作中往往需要对表格的样式进行调整，可能会有列的删减和顺序的调整，为了屏蔽类似操作对程序执行的影响，在程序中采取动态的读取关键字列的方式，屏蔽属性排列位置的变化对数据匹配造成的影响。

由于数据总量大，为了便于查看，通过给不同状态的计划记录添加相应背景色突出显示[9]。单元格背景颜色设置的核心伪代码如下：

Private Sub ContrastData()′单元格赋值及背景色设置

Dim i,j as Integer

′对表格中行号为i列号为j的单元格实施操作

With Worksheets("companysheet").cells(i,j)

.value=“8801600918”′赋值

.Interior .Color = 5296274 ′设置背景色

End With

End Sub

2.8 窗体的设计与调用

Excel提供了VBA编辑器，通过按快捷键“Alt+F11”调出编辑器，用户可以自定义窗体，通过窗体实现参数的输入。在完成开发后，需要将文件另存为.xla的加载宏格式，放入系统的AddIns文件夹下，然后执行菜单命令“工具—加载项”勾选需要加载的.xla文件。这样在每一次启动Excel的时候程序后台都会运行加载宏文件。通过系统宏调用自定义窗体，宏函数的设置如下：

Sub MyTool()

Myform1.show ‘显示自定义窗体

End sub

为提高宏调用的效率，可以通过执行菜单命令“工具—自定义”，在自定义对话框中将对应的宏命令加入工具栏中以方便随时调用，自定义工具栏如图7所示[10]。

图7 自定义工具栏

3 运行情况及结果分析

通过系统的运用，工作最大的数据关联工作全有系统自动完成，不仅极大的减轻了计划员的工作负担，而且保证了信息传递的准确性。在系统投入使用的一年时间内，计划员能够管理更多的机型，工作成效提升明显。由于系统可进行P1，P2计划查询，对零件进度监控精准，零件准时交付率显著提升，不再出现由于计划员工作疏忽引起零件无法准时交付的现象。

下面以民机A320项目为例来说明系统应用前后带来的变化，该项目涉及零件690余项，公司订单约260余架等，项目相关信息详见表1，关键指标改善效果如表2所示。

表1 A320项目基础信息

表2 系统应用前后关键指标改善情况说明

从表1、表2可以看出在针对A320这种大项目，系统运用后将计划编制时间缩减为原来时间的1.5%，给计划员的工作效率带来是颠覆性的改变，同时将零件延期交付数量减少84.67%，显著提高零件的准时交付率。

4 结论

本文从航空军工企业生产管理的实际出发，理清公司架次管理和制造车间批次管理的关系，基于Excel平台二次开发生产计划进度管理系统。系统具有单计划编排，整机计划编排，完工计划核查等功能，做到将架次计划和批次计划的数据无缝对接，自动确定每一条计划的交付日期，自动关联每一架次零件的质量编号等，通过系统的使用免去了大量繁复的人工操作，同时也解决了由于人为因素造成的计划管理失控，零件交付节点难以保证的问题。系统的运用提高了管理效率，明确了工作目标，显著提高了零件准时交付率，为后续装配赢得宝贵时间。

[1] Wang Jianhai,Zhang shu.Architecture of manufacuring execution system in the extended enterprise[J].Chinese Journal of Mechanical Engineering,2002,15(1):90-93.

[2] 张星,曾鹏飞,郝永平.面向离散型制造业精益生产管理集成化系统研究[J].制造业自动化,2015,37(9):18-21.

[3] 王伟,王旭东,孟飙.飞机生产中的批架次管理技术[J].沈阳航空航天大学学报,2015,32(6):87-92.

[4] 黎小平,宁宣熙.基于架次管理的装配过程零件动态配套性算法[J].计算机集成制造系统,1999,5(4):66-71.

[5] 张军,张贵宝,庄新村.离散制造车间工时管理模式的分析与改进[J].工程机械,2006,37(10):70-72.

[6] 郭来军.浅谈EXCEL二次开发的三种方式[J].辽宁科技学院学报,2007,9(4):23,29.

[7] 李耸,孙岩,蒋浩康．产品数据的批架次有效性管理[J].企业研究,2014(16):53-54．

[8] 刘增杰.Excel 2010 VBA入门与实战[M].北京:清华大学出版社,2012.

[9] 李政.VBA应用基础及实例教程[M].北京:国防工业出版社,2005.

[10] 刘顺涛,陈雪梅,赵正大,等.基于CATIA二次开发的数模信息提取及组织技术研究[J].航空制造技术,2014(19):78-80.