怎样用EXCEL开发管理信息系统

1、 用EXCEL开发管理信息系统摘 要：Excel97是Windows95环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性，内嵌有一种Visual Basic for Application(简称VBA)超级宏语言，读者在熟练应用Excel的基础上，可按用户的需求用VBA建立适用的信息系统。关键词：EXCEL97 VBA 管理信息系统（MIS）目前国内管理信息系统(MIS)开发研制一般采用人们熟悉的dBaseX、Foxbase或Foxpro等,本文介绍了如何用Excel开发MIS。一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel处理这些部分均显得

2、游刃有余。Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。在本文笔者用中文Excel97 for Windows开发了水电管理系统、销售管理系统、试卷分析系统。充分感受到其强大功能和方便性。下面详细阐述水电管理系统、销售管理系统、试卷分析系统的开发过程。应用程序一：水电计价系统水电管理是每一个单位每月都要进行的工作。 下面我们以保定农校水电计价系统为例讲解VBA在水电管理中的应用。1、 水电计价系统的功能本程序具有如下功能：自动计算功能：可完成各户水费、电费、

3、合计的计算。自动汇总功能：自动汇总全校教职工楼的用电量、用水量及分类汇总。查询功能：可查询各户的电表数、水表数、电费数、水费数。报表的打印输出功能。系统保护功能：具有口令保护、工作表保护以防非法用户进入和修改。2、 程序设计过程（1）程序总体设计（图1）（2）界面设计水电计价系统主界面如图2所示。（图2）主界面是以EXCEL工作表作为输入输出界面。具体设计如下：选定一个工作表并命名为“主界面”。选定单元格区域，把区域颜色设置成你喜欢的颜色。放置命令按钮，并给按钮指定宏。分割区域并冻结，锁定界面使之不能滚动。工作表加密保护使用户不能修改。记录单界面如（图3）所示。具体设计过程如下：选定一个工作表

4、并命名为“记录单”。制表（如上图所示 ）。选定年、月、水表数、电表数空白单元格区域，取消锁定。放置命令按钮，并给按钮指定宏。工作表保护使用户不能修改。主报表如（图4、图5）所示。具体设计过程如下：选定一个工作表并命名为“主报表”。制表（如上图所示 ）。纵向冻结A、B两列，横向冻结1-5行。放置命令按钮，并给按钮指定宏。输入公式：计算上次表底、本月查表记录、水电用量、水、电费、水电费合计的校内、校外合计、楼房合计、平房合计。在C338单元格输入下面公式，拖动填充柄复制公式至L338。=SUMIF($M$6:$M$331,"=l校内",C6:C331)+SUMIF($M$6:$

5、M$331,"=p校内",C6:C331) 在C339单元格输入下面公式，拖动填充柄复制公式至L339。=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=p校外",C6:C331)在C340单元格输入下面公式，拖动填充柄复制公式至L340。=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=l校内",C6:C331)在C341单元格输入下面公式，拖动填充柄复制公式至L341。=S

6、UMIF($M$6:$M$331,"=p校外",C6:C331)+SUMIF($M$6:$M$331,"=p校内",C6:C331)工作表保护使用户不能修改。查表档案界面如（下图）所示：具体设计过程如下：选定一个工作表并命名为“档案”。制表（如图6所示 ）。纵向冻结A、B两列，横向冻结1-3行。放置命令按钮，并给按钮指定宏。工作表保护使用户不能修改。查询档案如（下图）所示：具体设计过程如下：选定一个工作表并命名为“查询”。制表（如图7所示 ）。案").ActivateRange("C4:AB329").SelectSelec

7、tion.ClearContentsRange("a1").SelectWorksheets("主界面").ActivateEnd Sub应用程序二：销售管理系统在商业企业的经营活动中，主要业务活动是进销存。本程序主要解决销售的问题，关于进货和库存留给读者自己在原来程序的基础上进一步开发。1、 销售管理系统的功能自动计算进货金额、销货金额和毛利。分类汇总功能，可按商品和经手人代码进行分类汇总。打印功能查询功能（可按商品和经手人代码进行查询）。2、 销售管理系统的程序设计销售管理系统总体设计界面设计：（图8）主界面如（图9）所示：界面具体设计过程：选定一工

8、作表，重命名为“主界面”。选定工作表区域，设置区域颜色。在区域内添加应用程序标题和命令按钮。给按钮指定宏 。对主界面进行口令保护。分割工作表区域并冻结，以防屏幕滚动。发货单如（图10）所示：发货单界面设计过程如下：按图10格式制表。放置命令按钮。选定A5,C5,E5,E10,A7:F9解锁定。在G7,G8,G9单元格里依次输入公式"E7*F7","=E8*F8","=E9*F9",?ú?G10单元格里输入 "=SUM(G7:G9)"。保护工作表。毛利核算表如（图11）所示：界面具体设计过程：选定一工作表重

9、命名为“毛利核算”。按图11所示制表。应用程序三：试卷分析系统1、 试卷分析系统的功能自动统计全班人数、缺考人数、实考人数、优、良、及格、不及格人数。自动计算平均成绩、不及格率、各成绩段人数百分比。自动绘制成绩分布曲线。打印功能。可打印成绩单和试卷分析表。2、 程序设计过程试卷分析系统的总体设计界面设计主界面如图12所示：主界面具体设计过程：选定一个工作表并命名为“主界面”。选定单元格区域，把区域颜色设置成你喜欢的颜色。放置命令按钮，并给按钮指定宏。分割区域并冻结，锁定界面使之不能滚动。工作表加密保护使用户不能修改。成绩单界面如图13所示：界面具体设计过程如下：选定一个工作表并命名为“成绩单”

10、。制表（如图13所示 ）。选定“考试”空白单元格区域，取消锁定。放置命令按钮，并给按钮指定宏。输入函数：在F4,L4两个单元格里分别输入“=IF(AND(C4<60,C4<>""),"不及格",IF(AND(C4>=60,C4<=74.9),"及格",IF(AND(C4<90,C4>=75),"良",IF(AND(C4<=100,C4>=90),"优",IF(C4="*","缺考","&quo

11、t;)”，在C29里输入公式统计全班人数“=50-COUNTBLANK(C4:C28)-COUNTBLANK(I4:I28)”，在F29输入公式“=COUNTIF(成绩单!$C$4:$C$28,"=*")+COUNTIF(成绩单!$I$4:$I$28,"=*")，在I29输入公式“=C29-F29”计算实考人数。 在D30单元格里输入公式“=COUNTIF($F$4:$F$28,"=优")+COUNTIF($L$4:$L$28,"=优")”计算优的人数，在D31单元格里输入公式“=COUNTIF($F$4:$F$2

12、8,"=良")+COUNTIF($L$4:$L$28,"=良")”计算良的人数，在I30单元格里输入公式“=COUNTIF($F$4:$F$28,"=及格")+COUNTIF($L$4:$L$28,"=及格")”计算及格的人数，在I31单元格里输入公式“=COUNTIF($F$4:$F$28,"=不及格")+COUNTIF($L$4:$L$28,"=不及格")”计算不及格的人数，工作表保护使用户不能修改。试卷分析打印界面如图14所示：界面具体设计过程如下：选定一个工作表并命名

13、为“打印界面”。选定单元格区域，把区域颜色设置成你喜欢的颜色。放置命令按钮，并给按钮指定宏。分割区域并冻结，锁定界面使之不能滚动。工作表加密保护使用户不能修改。试卷分析界面如图15所示：界面具体设计过程：选定一个工作表并命名为“试卷分析”。制表（如图15所示 ）。放置命令按钮，并给按钮指定宏。选定G4:P4 G7:P7绘制折线图。在单元格输入公式：A6:"=AVERAGE(成绩单!C4:C28,成绩单!I4:I28)"B6:"=MAX(成绩单!$C$4:$C$28,成绩单!$I$4:$I$28)"C6:"=MIN(成绩单!$C$4:$C$28,

14、成绩单!$I$4:$I$28)"D6:"=COUNTIF(成绩单!$C$4:$C$28,"<60")+COUNTIF(成绩单!$I$4:$I$28,"<60")"E6:"=D6/N8"G6:"=COUNTIF(成绩单!C4:C28,"<=9")+COUNTIF(成绩单!I4:I28,"<=9")"H6:"=COUNTIF(成绩单!C4:C28,"<=19")+COUNTIF(成绩单!I4

15、:I28,"<=19")-G6"I6:"=COUNTIF(成绩单!C4:C28,"<=29")+COUNTIF(成绩单!I4:I28,"<=29")-H6-G6"J6:"=COUNTIF(成绩单!C4:C28,"<=39")+COUNTIF(成绩单!I4:I28,"<=39")-I6-H6-G6"K6:"=COUNTIF(成绩单!C4:C28,"<=49")+COUNTIF(成绩单

16、!I4:I28,"<=49")-J6-I6-H6-G6"L6:"=N8-SUM(G6:K6,M6:P6)"M6:"=COUNTIF(成绩单!C4:C28,">=60")+COUNTIF(成绩单!I4:I28,">=60")-试卷分析!N6-试卷分析!O6-试卷分析!P6"N6:"=COUNTIF(成绩单!C4:C28,">=70")+COUNTIF(成绩单!I4:I28,">=70")-试卷分析!P6-试卷

17、分析!O6"O6:"=COUNTIF(成绩单!C4:C28,">=80")+COUNTIF(成绩单!I4:I28,">=80")-试卷分析!P6"P6:"=COUNTIF(成绩单!C4:C28,">=90")+COUNTIF(成绩单!I4:I28,">=90")"G7:"=G6/$N$8"并选定G7单元格拖动填充柄复制至P7单元格。C8:"=成绩单!C29"H8:"=成绩单!C29"

18、N8:"=C8-H8"工作表保护使用户不能修改。姓名库界面图16所示：界面具体设计过程如下：选定一个工作表并命名为“姓名库”。制表（如图16所示 ）。3、 试卷分析程序的VBA代码及说明Sub 宏1() 指定给主界面上的成绩录入按钮Sheets("成绩单").Select 激活成绩单工作表End Sub Sub 宏2() 指定给主界面上的分析按钮Application.MaxChange = .001ActiveWorkbook.PrecisionAsDisplayed = FalseCalculate 执行自动计算End SubSub 宏4() 指定给

19、主界面上的打印按钮Sheets("打印界面").Select 激活打印界面工作表End SubSub 宏5() 指定给打印界面上的打印成绩单按钮Sheets("成绩单").SelectActiveWindow.SelectedSheets.PrintOut Copies:=1End Sub 打印成绩单Sub 宏6() 指定给打印界面上的打印试卷分析按钮Sheets("试卷分析").SelectActiveWindow.SelectedSheets.PrintOut Copies:=1End SubSub 宏7() 指定给返回按钮She

20、ets("主界面").SelectEnd Sub 返回主界面Sub 宏11() 指定给主界面上的帮助按钮Sheets("帮助窗口").Select 激活帮助工作表界面End SubSub urxm() 指定给成绩单界面上的返回主界面按钮Dim bjj As Stringbjj = Workbooks("ujfx.xls").Worksheets("成绩单").Range("c2").ValueIf bjj = "财1" ThenWorksheets("姓名库&quo

21、t;).Range("a3:a27").Copy destination:=Worksheets("成绩单").Range("b4:b28")Worksheets("姓名库").Range("b3:b27").Copy destination:=Worksheets("成绩单").Range("h4:h28")ElseIf bjj = "财2" ThenWorksheets("姓名库").Range("c3

22、:c27").Copy destination:=Worksheets("成绩单").Range("b4:b28")Worksheets("姓名库").Range("d3:d27").Copy destination:=Worksheets("成绩单").Range("h4:h28")ElseIf bjj = "" ThenWorksheets("姓名库").Range("a100:a124").Copy

23、destination:=Worksheets("成绩单").Range("b4:b28")Worksheets("姓名库").Range("b100:b124").Copy destination:=Worksheets("成绩单").Range("h4:h28")ElseEnd Iffanhui 调用FANHUI宏End Sub 以上代码是根据成绩单上C2单元格的值用选择语句从姓名库里取姓名，然后放到成绩单的相应单元格里。Sub fanhui()Worksheets(&q

24、uot;主界面").ActivateEnd Sub下面就信息输入、信息处理、信息输出界面设计、代码设计和数据保护等方面的问题作一小结：一、信息输入最常用的信息输入方法有三种,一是在工作表(WorkSheets)的单元格(Cells)中直接输入文字和数据(重复数据可由填充把柄拖拉而得);二是采用输入框(InputBox)输入少量数据;三是自行设计对话窗体(Forms),在其上可加入标签框、文本框、列表框、分组框、选择框、滚动条和命令按钮等,然后设计其对象格式(如属性、保护、控制项、对齐、字体和图案等)。当然,Windows提供的剪裁板进行数据的复制或粘贴也不失为一种常用的方法。此外,E

25、xcel还提供了强大的文件转换功能,可将Txt文件、dBase、Lotus 1-2-3文件、QuattroPro文件、MS Work文件、SYLK文件、DIF文件等直接转换为Excel工作簿文件,这就为数据的共享、快速录入及转换提供了极大的方便。二、信息处理按行业的不同,信息处理的要求有较大的差别,常用的信息处理包括数据的修改、查询、检索、排序和统计汇总。在dBase中,这些工作一般需要编程设计,而在Excel中,几乎所有的工作均可由Excel本身提供的功能轻松地完成。最常用的是右端"数据"菜单中的各项功能"记录单"选项提供了数据库记录查询、输入、修改或

26、删除的简单方法;"排序"选项提供了数据库记录同时按三个字段的排序方法,可以按行或按列、以升序或降序、考虑或不考虑大写来进行排序;"筛选"选项提供了隐藏除符合指定条件外的所有数据的方法,为数据库同类记录的查询和重点显示提供了方便;"分类汇总"选项提供了同类数据的汇总及统计方法;"数据透视表"选项可以迅速使一个复杂的表格变得容易阅读和理解,它可以方便地概括出感兴趣的字段,从不同的角度"透视"出求和值、计数值、平均值、最大值、最小值、乘积值、标准偏差、总体标准偏差、变异值、总体变异值。表格设计是MIS

27、的重点和难点之一,然而在Excel中,表格设计却易如反掌。Excel的工作簿(WorkBooks)是一个三维电子表格,一个工作簿由若干个工作表构成,每个工作表本身就是一张表格,它最多可达16384行256列,足以满足表格设计的需要。表格中每一行的高度和每一列的宽度均可随意调节,多余的行和列可以隐藏起来,表格中的每个单元格可有不同的字体、字号、边框线、前景色、数据格式、对齐方式和保护方式,在每个单元格中还可插入不同的文字附注和声音附注。此外,在每个工作表中可任意绘制直线、曲线、矩形、椭圆、箭头和各种窗体控件(Controls),并且还可插入其他Windows应用程序的图片和对象,支持DDL和OL

28、E。Excel的这些功能为灵活设计各种复杂的表格提供了极大的方便。统计图表是各类MIS的特色之一,能否设计出美观易懂的图表从一个方面反映了MIS生命力的强弱。Excel的作图能力特别强大,操作使用方便,它可从工作表中灵活地提取所需的数据,快速生成二维或三维的条形图、柱形图、饼图、环形图、散列图、曲面图、雷达图等。三、信息输出Excel具有所见即所得的功能,凡是在工作表上能显示出来的内容(文字、数据、图形、图表)均可打印出来,超宽表格可以自动分页打印;其打印选项十分丰富,完全不需要自己编写另外的打印驱动代码。四、界面制作在Excel中,MIS的界面既可采用对话窗体进行设计,也可在工作表上直接进行设计,窗体的各种控件可随意放置在工作表上。在录制或编写了宏代码后,还可根据自己的需要任意修改Excel的菜单、工具箱以及提示行显示信息,而隐藏Excel本身的菜单系统,使MIS仿佛脱离了Excel处于单独运行之中。为了在进入Windows的同时就进入MIS界面,可在Windows的初始化文件WIN.INI中加入LOAD=C:Excel5Excel.EXE /E,在C:Excel5XLSTART子目录中加入MIS的界面控制程序,这样将在启动Windows的同