Excel VBA在产品工时管理系统中的应用.doc

Excel VBA在产品工时管理系统中的应用第4期2002年l1月锅炉制造BOERMAMACITIRGNo.4Nov.2002文章编号:CN231249(2002)04005904ExcelVBA在产品工时管理系统中的应用关琦(上海德莱赛兰压缩机有限公司,上海207o)摘要:本文分析了Exce1.VBA在企业办公系统和信息管理系统中的应用,提出了利用ExcelVBA技术实现产品工时管理的方案.关键词:产品工时汇总;ExcelVBA;方案;效果中图分类号:TK39文献标识码:BApplicationofExcelVBAInProductManhourManagementSystemGUANQ/(ShanghaiDelesailanCompressorCo.,hd.,Shanghai200070,China)Abstract:ThispaperanalyzestheapplicationofExcelVBAinEnterpriseOfficeAutomationSystemandInformationManagementSystem,anddemonstratesitsusageinaProductManhourManagementsystem.Keywords:Productman-hoursummary;ExcelVBA;pmgrmamae;effectO引言MicrosoftExcel是微软公司提供的一个强大的电子表格处理软件,它是微软公司Microsoftof-fice套装办公软件的一个重要组成部分.在这套办公软件中,有一个十分重要的组件VBA组件,利用此组件,用户可以使许多繁琐,机械的13常工作得以自动实现,从而极大地提高用户的工作效率以及应用程序的实用性.1背景MicrosoftExcel是分析和提供信息的强有力的工具,它功能强大,界面友好,使用方便.利用VBA可更灵活,更有效,更方便地管理和操作企业信息数据,提高信息管理的自动化水平,进而提高企业的工作效率和竞争力.企业的信息化管理不仅仅是企业数据信息的计算机输入和输出,而且应为对数据信息的全面综合管理.使用MicrosoftExcel进行数据信息的管理,可快捷,方便地得到数据信息的汇总或分析结果.合理利用计算机技术是企业信息化管理的基础.企业计算机的使用,不能只当作打字机,打印机,应充分利用现有软件的潜能,进行有效的开发,解决13常繁琐的重复工作,为企业的信息资源管理提供有力的工具,降低劳动成本,同时为企业节省购买额外软件的开支.1.1使用Excel提供的程序设计语言VisualBasicforApplication(VBA)的优点将大量,重复的操作记录为一个VBA程序,实现复杂操作的自动化.通过控制Excel对象,在一个工作表中灵活收稿日期-20020612作者简介:关琦(1968一).女.毕业于哈尔滨理工大学计算机系.rI.工程师.?60?锅炉制造总第186期地控制其它工作表,数据表或文档,从而轻松实现不同工作表之间的数据交互.ExcelVBA编写简单,使用方便.在Excel中,利用记录宏和VisualBasic编辑器编写VBA程序;利用菜单或图形控件等方式执行VBA程序;在VBA程序中用ADO技术访问数据库.1.2使用ExcelVBA的适用范围在工作表中完成大量重复的操作.例如:每台产品的工时计算,汇总.在工作表中完成各种通用操作.例如:产品工时汇总表的打开,关闭.在工作表中完成各种交互操作.例如:提取其他工时汇总表的数据进行汇总,查询.限制工作表中某种数据的范围.利用ExcelVBA实现一个集成化的信息控制系统.下面通过具体实例一产品工时管理系统,介绍ExcelVBA的应用.2方案企业生产中的工时定额是生产单位产品或完成一项工作所必需消耗的工时,它对产品成本有着直接的影响,也是企业技术和管理水平的重要标志之一.2.1需求分析产品工时的汇总,首先是零件工序的工时编制,汇总后成为零件工时.部件工时是由零件工时汇总而成,部件工时最终汇总成为产品工时.目前,产品工时汇总主要靠手工查询汇总,l台产品的工时汇总需要56d时间,汇总时需要查阅产品所有零部件的工时,查阅量很大,工作繁琐,人工计算误差大.基于这种情况,我们设计了产品工时管理系统,用以实现产品工时汇总,有效地解决了产品工时的管理.下面简要列出产品工时汇总的具体需求:工时定额是由小时和分钟构成,其中小时是累计时间.如:某零件基本工时为25h34m_in,格式应为:25.34.根据需要确定零件工时数据库的结构:由于不同产品的基本结构相同,产品的零部件结构也相同,在进行工时输入时,多是通用件的工时输入,为减少重复输入,建立零件工时数据库,需要某零件即可调用此零件的工时信息.产品结构分析:每台产品都是由部件,组件和零件3部分组成,其中部件和组件工时包含零件工时,以上3部分都需要工时输入,三者之间又有一定的联系.每台产品中都有部件,组件和零件,每个部件中都有组件和零件,每个组件由零件组成,所以在产品工时汇总中,首先汇总零件工时,然后汇总组件工时和部件工时,最后才能得到产品工时的汇总结果.2.2功能设计2.2.1系统结构产品工时管理系统包括产品工时的输入,汇总及管理等功能,系统结构如图(1)所示:图1系统结构2.2.2功能说明用户需求:工时定额员在产品工时管理时,提出不同的需求,需求流程如图(2)所示:图2需求流程模拟文件的选择:工时输入文件有3个:零件工序工时输入表,即工艺过程卡;组件的零件工时输入表,即工艺路线定额表;部件的组件和零件工时输入表,即工艺路线定额表.对不同的工时输入,用户选择不同的模板文件.通用件工时的入库:建立通用零件工时数据库,规范化管理工时第4期关琦:ExcelVBA在产品工时管理系统中的应用?6l?数据,在产品工时汇总时,快速调用,减少大量重复工作.产品工时的汇总:选择组成产品的所有部件工时输入文件,提取所有部件的汇总工时,自动汇总产品工时.零件工时的提取:在组件和部件中提取已汇总的零件工时,可同时打开多个已汇总零件工时文件,自动提取到组件和部件工时定额表中的固定位置.通用件工时的调用:在通用零件工时数据库中,输入待调用零件的图号,即可在固定位置获得此零件的工时信息.零件工时数据库的结构字段名称:序号零件名称零件图号工序名称工序基本工时工序准备工时零件基本工时小计零件准备工时小计备注3方案要点分析3.1工时数据的格式在Excel菜单”格式化”中,选择”单元格”,选取标签”数字”中的”自定义”,形式为:h.mln.由于工时是完成一项任务的合计时间,它是一种累计时间,Excel提供了这种累计时间的格式.在此系统中工时的格式均采用累计时间的格式代码:h.mln,快速,准确显示工时数据的运算结果,使用户一目了然.例如:工时25h56min,输入格式为:25:56,显示格式为:25.56.显示格式遵从了手工输入习惯,使各种用户都能理解.3.2模板文件的制作5种模板文件包括3种工时输入文件,产品工时汇总文件和数据库表文件.工时输入文件包括工艺过程卡,组件工艺路线定额表和部件工艺路线定额表.工艺过程卡记录零件工序工时,固定格式的工艺过程卡页数由零件的工序数量决定;工艺路线定额表记录组件或部件的零件工时,固定格式的工艺路线定额表页数由组件或部件的零件数量决定.根据用户输入的数量,VBA程序自动复制表格的页数.模板表格的复制,代码如下:Range(“A1”).Selt激活Al单元格Fori=1ToPActiveCel1.Offset(O,O).Range(“A1:AA80”).Selt选中Al至AA80单元格区域Selection.Copy复制选中区域的内容ActiveCel1.Offset(80,0).Cells(1,1).Select选中以上区域的下一行1列单元格ActiveSheet.Paste粘贴复制内容Nexti3.3工序,零件和部件工时的自动汇总在模板文件中,使用Excel公式,确定汇总单元格的结果.当用户完成工序,零件和部件工时的输入,零件和部件汇总工时自动在汇总单元格中显示.工时自动汇总,代码如下:Forj=1ToPCells(i*80+79,25).Select选中Y159单元格ActiveCel1.FormulaR1C1=“=SUM(R一80c,R一2c1)”定义公式=Y79+Z77Cells(i*80+79,26).Select选中Z159单元格ActiveCel1.FormulaR1C1=“=SUM(R一80c,R一1c)”定义公式=Z79+Z78Nextj3.4组件或部件工时输入中,调用已汇总的零件工时通过打开已汇总的零件工时汇总表的方式,提取零件汇总工时到部件工时表中的固定位置,可同时打开多个零件工时汇总表,提取完成后,自动关闭所有零件工时汇总表.零件工时的提取,代码如下:f=Application.Dialogs(xlDialogOpen).Show显示打开文件对话框,多选文件同时打开n=Application.Workbooks.Count已打开文件的数量ActiveWorkbook.Save保存汇总表ActiveWindow.WindowState=xlMinimized汇总表最小化Forl=lTonlActiveWindow.Close关闭其它文件NextlActiveWindow.WindowState=xlMaximized汇总表最大化3.5通用件工时的调用首先打开通用件工时数据库,用户输入调用零件或部件的图号,在当前工作表中的固定位置提取了此零件或部件的工时信息.代码如下:?62?锅炉制造总第186期Workbooks.)enFileName(“E:lnotlrpartdatabase.xls”)打开数据表文件StaticPP=Application.InputBox(prompt:=“Pleaseinputtheborrowedpartdrawingno:”,Title:=“Inputpartdwgno”,Default:=filepage,Type:=2)输入通用件图号3.6产品零件工时的入库零件和部件的工时汇总表全部集中到产品数据透视表中,通过VBA程序调入到Access数据库中.4项目实施效果利用计算机进行产品工时汇总,快捷,方便,数据准确.提高了产品工时管理的效率,缩短了产品成本核算的周期,同时避免了手工统计的差错.以前1台产品工时汇总需要6d时间,采用此项目2d即可汇总1台产品,数据准确无误.利用数据库管理产品工时,不再需要反复查阅产品工艺路线工时表,通过数据库可随时进行查询,浏览,调用,为今后的办公自动化作好基础.此项目实现工时数据的规范化管理,并且操作实时性强.5结论在企业的成本核算中,有许多数据是由Excel工作表处理,如产品材料的消耗定额,采购物质统计表等等,成本核算时,经常需要分类,汇总,计算分析,利用ExcelVBA,可以大大简化操作过程,提高工程的成本核算效率.(编辑:陈青云)(上接58页)2一C1)/2D1=B1/2*A2/2一(B1+B3/2)*C2D2=3.14*B4*B4*B4/32D3=D1/D2I)4=(0.5*A2一C2)/(3.14*B4*B4/4)D5=(4/3)*IMI)6=SQRT(D3*D3+4*D4*D4)E1=(AVERAGE(B1,B3)一B1/2)*C2E2=3.14*B4*B4*B4/32E3=El/E2FA=C2/(3.14*B4*B4/4)E5=(4/3)*E4E6=SQRT(E3*E3+4*E4*E4)F1=链片1厚度G1=销轴2直径G2=3.14*G1*G1*G1/32G3=0.5*A2*F1/,:G2ca=0.5*AZ/(3.14*G1*G1/4)G5=(4/3)*G4G6=SQRT(G3*G3+4*G4*ca)D