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

1、怎样用EXCEL开发管理信息系统 LEKIBM standardization office【IBM5AB- LEKIBMK08- LEKIBM2C】怎样用EXCEL开发管理信息系统 用EXCEL开发管理信息系统摘 要：Excel97是Windows95环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性，内嵌有一种Visual Basic for Application(简称VBA)超级宏语言，读者在熟练应用Excel的基础上，可按用户的需求用VBA建立适用的信息系统。关键词：EXCEL97 VBA 管理信息系统（MIS）目前国内管理信息系统(MIS)开发研制一般采用人们熟悉的d

2、BaseX、Foxbase或Foxpro等,本文介绍了如何用Excel开发MIS。一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel处理这些部分均显得游刃有余。Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。在本文笔者用中文Excel97 for Windows开发了水电管理系统、销售管理系统、试卷分析系统。充分感受到其强大功能和方便性。下面详细阐述水电管理系统、销售管理系统、试卷分析系统的开发过程。应用程序一：水电计价系统水电

3、管理是每一个单位每月都要进行的工作。 下面我们以保定农校水电计价系统为例讲解VBA在水电管理中的应用。1、 水电计价系统的功能本程序具有如下功能：自动计算功能：可完成各户水费、电费、合计的计算。自动汇总功能：自动汇总全校教职工楼的用电量、用水量及分类汇总。查询功能：可查询各户的电表数、水表数、电费数、水费数。报表的打印输出功能。系统保护功能：具有口令保护、工作表保护以防非法用户进入和修改。2、 程序设计过程（1）程序总体设计（）（2）界面设计水电计价系统主界面如图2所示。（）主界面是以EXCEL工作表作为输入输出界面。具体设计如下：选定一个工作表并命名为“主界面”。选定单元格区域，把区域颜色设

4、置成你喜欢的颜色。放置命令按钮，并给按钮指定宏。分割区域并冻结，锁定界面使之不能滚动。工作表加密保护使用户不能修改。记录单界面如（）所示。具体设计过程如下：选定一个工作表并命名为“记录单”。制表（如上图所示 ）。选定年、月、水表数、电表数空白单元格区域，取消锁定。放置命令按钮，并给按钮指定宏。工作表保护使用户不能修改。主报表如（、）所示。具体设计过程如下：选定一个工作表并命名为“主报表”。制表（如上图所示 ）。纵向冻结A、B两列，横向冻结1-5行。放置命令按钮，并给按钮指定宏。输入公式：计算上次表底、本月查表记录、水电用量、水、电费、水电费合计的校内、校外合计、楼房合计、平房合计。在C338单

5、元格输入下面公式，拖动填充柄复制公式至L338。=SUMIF($M$6:$M$331,=l校内,C6:C331)+SUMIF($M$6:$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单元格输入下面公式，拖动填充柄复制公

6、式至L341。=SUMIF($M$6:$M$331,=p校外,C6:C331)+SUMIF($M$6:$M$331,=p校内,C6:C331)工作表保护使用户不能修改。查表档案界面如（下）所示：具体设计过程如下：选定一个工作表并命名为“档案”。制表（如所示 ）。纵向冻结A、B两列，横向冻结1-3行。放置命令按钮，并给按钮指定宏。工作表保护使用户不能修改。查询档案如（下）所示：具体设计过程如下：选定一个工作表并命名为“查询”。制表（如所示 ）。纵向冻结A列，横向冻结1-3行。放置命令按钮，并给按钮指定宏。工作表保护使用户不能修改。(3)、水电计价系统的VBA代码及说明Dim s As Varia

7、ntDim y As VariantDim u As Variant 模块级变量声明Sub shuru() 指定给主界面输入按钮s = Worksheets(主界面).Range(v1).Value 获取密码For y2 = 1 To 2x = InputBox(请输入密码：, 保定农校水电计价系统)If x = s ThenWorksheets(主界面).Activate(按钮 2).Select = vbb(按钮 3).Select = ibda(按钮 5).Select = 退出(按钮 4).Select = 报表查询Worksheets(记录单).ActivateExit SubEls

8、eIf x = ThenMsgBox 请输入密码ElseExit SubEnd IfNext y2End Sub 以上是根据密码正确与否选择执行代码段Sub 返回1() 指定给返回按钮Worksheets(主界面).ActivateEnd SubSub 存档() 指定给记录单上的存档按钮thecode = vbYesNo + vbDefaultButton2 + vbExclamation + vbapplicationmodelMsgBox 请对输入进行检查，是否正确？, thecodethereply = MsgBox(prompt:=请对输入进行检查，是否正确？, Buttons:=th

9、ecode)Select Case thereplyCase vbYesm = Worksheets(记录单).Range(c5)n = Worksheets(记录单).Range(d5)If m And n ThenWorksheets(记录单).Range(c5:d330).SelectElseIf m And n = ThenWorksheets(记录单).Range(c5:c330).SelectElseIf m = And n ThenWorksheets(记录单).Range(d5:d330).SelectElseIf m = And n = ThenExit SubElseEnd

10、 IfWorksheets(报表).ActivateIf m And n ThenWorksheets(报表).ActivateWorksheets(报表).Range(e6).SelectElseIf m And n = ThenWorksheets(报表).ActivateWorksheets(报表).Range(e6).SelectElseIf m = And n ThenWorksheets(报表).ActivateWorksheets(报表).Range(f6).SelectElseEnd IfWorksheets(档案).Activatey1 = Worksheets(记录单).R

11、ange(c2).Valuex = 0Do Until z = y1x = x + 1z = Worksheets(档案).Cells(2, x).ValueLoopIf m And n ThenWorksheets(档案).Cells(4, x).SelectElseIf m = And n ThenWorksheets(档案).Cells(4, x + 1).SelectElseIf m And n = ThenWorksheets(档案).Cells(4, x).SelectElseEnd IfWorksheets(主界面).ActivateCase vbNoEnd SelectExit

12、 SubEnd Sub 以上代码是把录入数据存入主报表和查表档案Sub 返回2() 指定给主报表上的返回主界面按钮Worksheets(主界面).ActivateEnd SubSub 取电表底() 指定给主报表上的取电表底按钮y = InputBox(请输入电表底（y0,1,2.12）：, 保定农校水电计价系统)If y = y0 ThenWorksheets(档案).ActivateRange(c4:c329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 1 ThenWorksheets(档案).ActivateRange(

13、e4:e329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 2 ThenWorksheets(档案).ActivateRange(g4:g329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 3 ThenWorksheets(档案).ActivateRange(i4:i329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 4 ThenWorksheets(档案).ActivateRange(

14、k4:k329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 5 ThenWorksheets(档案).ActivateRange(m4:m329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 6 ThenWorksheets(档案).ActivateRange(o4:o329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 7 ThenWorksheets(档案).ActivateRange(

15、q4:q329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 8 ThenWorksheets(档案).ActivateRange(s4:s329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 9 ThenWorksheets(档案).ActivateRange(u4:u329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 10 ThenWorksheets(档案).ActivateRange

16、(w4:w329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 11 ThenWorksheets(档案).ActivateRange(y4:y329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = 12 ThenWorksheets(档案).ActivateRange(aa4:aa329).SelectWorksheets(报表).ActivateRange(c6).SelectElseIf y = ThenExit SubElseEnd IfWith App

17、lication.Calculation = xlAutomatic.MaxChange = .001End With = FalseEnd Sub 以上代码是使用选择语句从查表档案中取电表底Sub 取水表底() 指定给主报表上的取电表底按钮u = InputBox(请输入水表底（y0,1,2.12）：, 保定农校水电计价系统)If u = y0 ThenWorksheets(档案).ActivateRange(d4:d329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 1 ThenWorksheets(档案).Activa

18、teRange(f4:f329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 2 ThenWorksheets(档案).ActivateRange(h4:h329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 3 ThenWorksheets(档案).ActivateRange(j4:j329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 4 ThenWorksheets(档案).Activa

19、teRange(l4:l329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 5 ThenWorksheets(档案).ActivateRange(n4:n329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 6 ThenWorksheets(档案).ActivateRange(p4:p329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 7 ThenWorksheets(档案).Activa

20、teRange(r4:r329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 8 ThenWorksheets(档案).ActivateRange(t4:t329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 9 ThenWorksheets(档案).ActivateRange(v4:v329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 10 ThenWorksheets(档案).Activ

21、ateRange(x4:x329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 11 ThenWorksheets(档案).ActivateRange(z4:z329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = 12 ThenWorksheets(档案).ActivateRange(ab4:ab329).SelectWorksheets(报表).ActivateRange(d6).SelectElseIf u = ThenExit SubElseEnd If

22、With Application.Calculation = xlAutomatic.MaxChange = .001End With = FalseEnd Sub 以上代码是使用选择语句从查表档案中取水表底Sub bsy() 由报表存档过程调用Worksheets(主界面).Activatex = 3Do While Not (IsEmpty(Worksheets(主界面).Cells(11, x).Value)x = x + 1LoopWorksheets(主界面).Cells(11, x).Value = Worksheets(记录单).Range(c2)Worksheets(主界面).

23、Cells(11, x + 1).Value = 月Worksheets(主界面).Cells(12, x).Value = uWorksheets(主界面).Cells(13, x).Value = yWorksheets(主界面).Cells(12, x + 1).Value = 月Worksheets(主界面).Cells(13, x + 1).Value = 月Worksheets(报表).ActivateRange(c3:f5).SelectRange(c334:f336).SelectRange(a1).Select = 6End Sub 以上代码是把报表月份和表底月份放入主界面上

24、的表格里Sub 返回3() 指定给返回主界面按钮Worksheets(主界面).ActivateEnd SubSub 打印设置() 指定给主界面上的打印设置按钮With .PrintTitleRows = $1:$5.PrintTitleColumns = End With = With .LeftHeader = .CenterHeader = 水电计价系统.RightHeader = 第 &P 页.LeftFooter = .CenterFooter = .RightFooter = .LeftMargin = .RightMargin = (.96063).TopM.HeaderMarg

25、in = (.5118110.PrintHeadings = False.PrintGridlines = False.PrintNotes = False.PrintQuality = Array(360, 180).CenterHorizontally = True.CenterVertically = False.Orientation = xlPortrait.Draft = False.PaperSize = xlPaperUser.FirstPageNumber = xlAutomatic.Order = xlDownThenOver.BlackAndWhite = False.Zoom = 100End WithEnd Sub 进行打印设置Sub vbb() 指定给主界面上的主报表按钮Worksheets(报表).ActivateEnd Sub 激活主报表Sub ibda() 指定给主界面上的报表查询按钮Worksheets(档案).ActivateEnd Sub 激活档案工作表