怎样用EXCEL开发管理信息系统.docx
《怎样用EXCEL开发管理信息系统.docx》由会员分享，可在线阅读，更多相关《怎样用EXCEL开发管理信息系统.docx（17页珍藏版）》请在冰豆网上搜索。
怎样用EXCEL开发管理信息系统
用EXCEL开发管理信息系统
摘要：
Excel97是Windows95环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性，内嵌有一种VisualBasicforApplication（简称VBA）超级宏语言，读者在熟练应用Excel的基础上，可按用户的需求用VBA建立适用的信息系统。
关键词：
EXCEL97VBA管理信息系统（MIS）
目前国内管理信息系统（MIS）开发研制一般采用人们熟悉的dBaseX、Foxbase或Foxpro等,本文介绍了如何用Excel开发MIS。
一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel处理这些部分均显得游刃有余。
Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。
它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。
在本文笔者用中文Excel97forWindows开发了水电管理系统、销售管理系统、试卷分析系统。
充分感受到其强大功能和方便性。
下面详细阐述水电管理系统、销售管理系统、试卷分析系统的开发过程。
应用程序一：
水电计价系统
水电管理是每一个单位每月都要进行的工作。
下面我们以保定农校水电计价系统为例讲解VBA在水电管理中的应用。
1、水电计价系统的功能
本程序具有如下功能：
自动计算功能：
可完成各户水费、电费、合计的计算。
自动汇总功能：
自动汇总全校教职工楼的用电量、用水量及分类汇总。
查询功能：
可查询各户的电表数、水表数、电费数、水费数。
报表的打印输出功能。
系统保护功能：
具有口令保护、工作表保护以防非法用户进入和修改。
2、程序设计过程
（1）程序总体设计（图1）
（2）界面设计
水电计价系统主界面如图2所示。
（图2）
主界面是以EXCEL工作表作为输入输出界面。
具体设计如下：
选定一个工作表并命名为“主界面”。
选定单元格区域，把区域颜色设置成你喜欢的颜色。
放置命令按钮，并给按钮指定宏。
分割区域并冻结，锁定界面使之不能滚动。
工作表加密保护使用户不能修改。
记录单界面如（图3）所示。
具体设计过程如下：
选定一个工作表并命名为“记录单”。
制表（如上图所示）。
选定年、月、水表数、电表数空白单元格区域，取消锁定。
放置命令按钮，并给按钮指定宏。
工作表保护使用户不能修改。
主报表如（图4、图5）所示。
具体设计过程如下：
选定一个工作表并命名为“主报表”。
制表（如上图所示）。
纵向冻结A、B两列，横向冻结1-5行。
放置命令按钮，并给按钮指定宏。
输入公式：
计算上次表底、本月查表记录、水电用量、水、电费、水电费合计的校内、校外合计、楼房合计、平房合计。
在C338单元格输入下面公式，拖动填充柄复制公式至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单元格输入下面公式，拖动填充柄复制公式至L341。
=SUMIF（$M$6:
$M$331,"=p校外",C6:
C331）+SUMIF（$M$6:
$M$331,"=p校内",C6:
C331）
工作表保护使用户不能修改。
查表档案界面如（下图）所示：
具体设计过程如下：
选定一个工作表并命名为“档案”。
制表（如图6所示）。
纵向冻结A、B两列，横向冻结1-3行。
放置命令按钮，并给按钮指定宏。
工作表保护使用户不能修改。
查询档案如（下图）所示：
具体设计过程如下：
选定一个工作表并命名为“查询”。
制表（如图7所示）。
案"）.Activate
Range（"C4:
AB329"）.Select
Selection.ClearContents
Range（"a1"）.Select
Worksheets（"主界面"）.Activate
EndSub
应用程序二：
销售管理系统
在商业企业的经营活动中，主要业务活动是进销存。
本程序主要解决销售的问题，关于进货和库存留给读者自己在原来程序的基础上进一步开发。
1、销售管理系统的功能
自动计算进货金额、销货金额和毛利。
分类汇总功能，可按商品和经手人代码进行分类汇总。
打印功能
查询功能（可按商品和经手人代码进行查询）。
2、销售管理系统的程序设计
销售管理系统总体设计
界面设计：
（图8）
主界面如（图9）所示：
界面具体设计过程：
选定一工作表，重命名为“主界面”。
选定工作表区域，设置区域颜色。
在区域内添加应用程序标题和命令按钮。
给按钮指定宏。
对主界面进行口令保护。
分割工作表区域并冻结，以防屏幕滚动。
发货单如（图10）所示：
发货单界面设计过程如下：
按图10格式制表。
放置命令按钮。
选定A5,C5,E5,E10,A7:
F9解锁定。
在G7,G8,G9单元格里依次输入公式"＝E7*F7","=E8*F8","=E9*F9",?
ú?
G10单元格里输入"=SUM（G7:
G9）"。
保护工作表。
毛利核算表如（图11）所示：
界面具体设计过程：
选定一工作表重命名为“毛利核算”。
按图11所示制表。
应用程序三：
试卷分析系统
1、试卷分析系统的功能
自动统计全班人数、缺考人数、实考人数、优、良、及格、不及格人数。
自动计算平均成绩、不及格率、各成绩段人数百分比。
自动绘制成绩分布曲线。
打印功能。
可打印成绩单和试卷分析表。
2、程序设计过程
试卷分析系统的总体设计
界面设计
主界面如图12所示：
主界面具体设计过程：
选定一个工作表并命名为“主界面”。
选定单元格区域，把区域颜色设置成你喜欢的颜色。
放置命令按钮，并给按钮指定宏。
分割区域并冻结，锁定界面使之不能滚动。
工作表加密保护使用户不能修改。
成绩单界面如图13所示：
界面具体设计过程如下：
选定一个工作表并命名为“成绩单”。
制表（如图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="*","缺考",""）））））”，在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$28,"=良"）+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所示：
界面具体设计过程如下：
选定一个工作表并命名为“打印界面”。
选定单元格区域，把区域颜色设置成你喜欢的颜色。
放置命令按钮，并给按钮指定宏。
分割区域并冻结，锁定界面使之不能滚动。
工作表加密保护使用户不能修改。
试卷分析界面如图15所示：
界面具体设计过程：
选定一个工作表并命名为“试卷分析”。
制表（如图15所示）。
放置命令按钮，并给按钮指定宏。
选定G4:
P4G7:
P7绘制折线图。
在单元格输入公式：
A6:
"=AVERAGE（成绩单!
C4:
C28,成绩单!
I4:
I28）"
B6:
"=MAX（成绩单!
$C$4:
$C$28,成绩单!
$I$4:
$I$28）"
C6:
"=MIN（成绩单!
$C$4:
$C$28,成绩单!
$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:
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（成绩单!
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-试卷分析!
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"
N8:
"=C8-H8"
工作表保护使用户不能修改。
姓名库界面图16所示：
界面具体设计过程如下：
选定一个工作表并命名为“姓名库”。
制表（如图16所示）。
3、试卷分析程序的VBA代码及说明
Sub宏1（）‘指定给主界面上的成绩录入按钮
Sheets（"成绩单"）.Select‘激活成绩单工作表
EndSub
Sub宏2（）‘指定给主界面上的分析按钮
Application.MaxChange=.001
ActiveWorkbook.PrecisionAsDisplayed=False
Calculate‘执行自动计算
EndSub
Sub宏4（）‘指定给主界面上的打印按钮
Sheets（"打印界面"）.Select‘激活打印界面工作表
EndSub
Sub宏5（）‘指定给打印界面上的打印成绩单按钮
Sheets（"成绩单"）.Select
ActiveWindow.SelectedSheets.PrintOutCopies:
=1
EndSub‘打印成绩单
Sub宏6（）‘指定给打印界面上的打印试卷分析按钮
Sheets（"试卷分析"）.Select
ActiveWindow.SelectedSheets.PrintOutCopies:
=1
EndSub
Sub宏7（）‘指定给返回按钮
Sheets（"主界面"）.Select
EndSub‘返回主界面
Sub宏11（）‘指定给主界面上的帮助按钮
Sheets（"帮助窗口"）.Select‘激活帮助工作表界面
EndSub
Suburxm（）‘指定给成绩单界面上的返回主界面按钮
DimbjjAsString
bjj=Workbooks（"ujfx.xls"）.Worksheets（"成绩单"）.Range（"c2"）.Value
Ifbjj="财1"Then
Worksheets（"姓名库"）.Range（"a3:
a27"）.Copydestination:
=Worksheets（"成绩单"）.Range（"b4:
b28"）
Worksheets（"姓名库"）.Range（"b3:
b27"）.Copydestination:
=Worksheets（"成绩单"）.Range（"h4:
h28"）
ElseIfbjj="财2"Then
Worksheets（"姓名库"）.Range（"c3:
c27"）.Copydestination:
=Worksheets（"成绩单"）.Range（"b4:
b28"）
Worksheets（"姓名库"）.Range（"d3:
d27"）.Copydestination:
=Worksheets（"成绩单"）.Range（"h4:
h28"）
ElseIfbjj=""Then
Worksheets（"姓名库"）.Range（"a100:
a124"）.Copydestination:
=Worksheets（"成绩单"）.Range（"b4:
b28"）
Worksheets（"姓名库"）.Range（"b100:
b124"）.Copydestination:
=Worksheets（"成绩单"）.Range（"h4:
h28"）
Else
EndIf
fanhui‘调用FANHUI宏
EndSub‘以上代码是根据成绩单上C2单元格的值用选择语句从姓名库里取姓名，然后放到成绩单的相应单元格里。
Subfanhui（）
Worksheets（"主界面"）.Activate
EndSub
下面就信息输入、信息处理、信息输出界面设计、代码设计和数据保护等方面的问题作一小结：
一、信息输入
最常用的信息输入方法有三种,一是在工作表（WorkSheets）的单元格（Cells）中直接输入文字和数据（重复数据可由填充把柄拖拉而得）;二是采用输入框（InputBox）输入少量数据;三是自行设计对话窗体（Forms）,在其上可加入标签框、文本框、列表框、分组框、选择框、滚动条和命令按钮等,然后设计其对象格式（如属性、保护、控制项、对齐、字体和图案等）。
当然,Windows提供的剪裁板进行数据的复制或粘贴也不失为一种常用的方法。
此外,Excel还提供了强大的文件转换功能,可将Txt文件、dBase、Lotus1-2-3文件、QuattroPro文件、MSWork文件、SYLK文件、DIF文件等直接转换为Excel工作簿文件,这就为数据的共享、快速录入及转换提供了极大的方便。
二、信息处理
按行业的不同,信息处理的要求有较大的差别,常用的信息处理包括数据的修改、查询、检索、排序和统计汇总。
在dBase中,这些工作一般需要编程设计,而在Excel中,几乎所有的工作均可由Excel本身提供的功能轻松地完成。
最常用的是右端"数据"菜单中的各项功能∶"记录单"选项提供了数据库记录查询、输入、修改或删除的简单方法;"排序"选项提供了数据库记录同时按三个字段的排序方法,可以按行或按列、以升序或降序、考虑或不考虑大写来进行排序;"筛选"选项提供了隐藏除符合指定条件外的所有数据的方法,为数据库同类记录的查询和重点显示提供了方便;"分类汇总"选项提供了同类数据的汇总及统计方法;"数据透视表"选项可以迅速使一个复杂的表格变得容易阅读和理解,它可以方便地概括出感兴趣的字段,从不同的角度"透视"出求和值、计数值、平均值、最大值、最小值、乘积值、标准偏差、总体标准偏差、变异值、总体变异值。
表格设计是MIS的重点和难点之一,然而在Excel中,表格设计却易如反掌。
Excel的工作簿（WorkBooks）是一个三维电子表格,一个工作簿由若干个工作表构成,每个工作表本身就是一张表格,它最多可达16384行256列,足以满足表格设计的需要。
表格中每一行的高度和每一列的宽度均可随意调节,多余的行和列可以隐藏起来,表格中的每个单元格可有不同的字体、字号、边框线、前景色、数据格式、对齐方式和保护方式,在每个单元格中还可插入不同的文字附注和声音附注。
此外,在每个工作表中可任意绘制直线、曲线、矩形、椭圆、箭头和各种窗体控件（Controls）,并且还可插入其他Windows应用程序的图片和对象,支持DDL和OLE。
Excel的这些功能为灵活设计各种复杂的表格提供了极大的方便。
统计图表是各类MIS的特色之一,能否设计出美观易懂的图表从一个方面反映了MIS生命力的强弱。
Excel的作图能力特别强大,操作使用方便,它可从工作表中灵活地提取所需的数据,快速生成二维或三维的条形图、柱形图、饼图、环形图、散列图、曲面图、雷达图等。
三、信息输出
Excel具有所见即所得的功能,凡是在工作表上能显示出来的内容（文字、数据、图形、图表）均可打印出来,超宽表格可以自动分页打印;其打印选项十分丰富,完全不需要自己编写另外的打印驱动代码。
四、界面制作
在Excel中,MIS的界面既可采用对话窗体进行设计,也可在工作表上直接进行设计,窗体的各种控件可随意放置在工作表上。
在录制或编写了宏代码后,还可根据自己的需要任意修改Excel的菜单、工具箱以及提示行显示信息,而隐藏Excel本身的菜单系统,使MIS仿佛脱离了Excel处于单独运行之中。
为了在进入Windows的同时就进入MIS界面,可在Windows的初始化文件WIN.INI中加入LOAD=C:
\Excel5\Excel.EXE/E,在C:
Excel5\XLSTART子目录中加入MIS的界面控制程序,这样将在启动Windows的同时直接进入Excel环境并启动MIS的操作界面,而不显示Excel的启动封面;Auto-Open是含有特殊含义的子程序名称,它可在打开工作簿的同时运行Auto-Open中的语句。
五、代码设计
对于简单的MIS,一般不需要进行代码设计,用Excel本身提供的各项功能就可满足要求。
除具有通用的工作表函数外,Excel还提供了丰富的日期和时间函数、财务分析函数、统计分析函数和假设分析函数。
例如在B1单元格输入公式"=AVERAGE（A1:
A9）",就可直接求出从A1到A9中各单元数据的平均值。
然而对于比较复杂的MIS,则必须使用Excel的编程语言?
VisualBasicforApplication（VBA,也就是Excel的"宏"）。
VBA直接嵌入Excel的工作簿中,其语法规则非常类似于VisualBasic,比较容易掌握。
VBA包含丰富的各种函数、方法、对象、属性和语句。
理解并逐步掌握VBA中数量庞大的各类方法、对象和属性是在Excel平台上进行二次开发的关键技术。
Excel的所有菜单操作均可通过VBA编程实现,此外VBA还可以实现Excel本身不具备的功能,如API函数的调用。
利用Excel提供的"录制宏"功能是初步学习VBA的简捷方法。
熟悉VBA之后,就可自己修改宏并编写新的宏。
需要说明的是,中文Excel的宏支持汉字过程名和变量名,这就为程序的阅读和维护带来了便利。
六、数据保护
MIS的数据保护能力是用户和软件开发人员共同关心的问题。
用dBase开发的MIS,往往需要为设计数据库的保密功能而绞尽脑汁。
然而在Excel中,您不必编写任何程序代码就可达到数据保护的目的。
Excel为用户提供了多级保护手段,可以自行选择是否保护每个单元格或每个控件,是否保护某张工作表,是否保护每个工作簿,是否需要输入打开工作簿的口令。
一般而言,打开工作簿的口令由用户设定,其他保护功能由程序设计人员设定,一经设置了保护功能,任何人（包括程序设计者）在不知道口令的情况下均无法越权使用。
Excel内置的强大功能以及提供的二次开发平台,为快速、简便地开发出具有专业水平的MIS提供了高效的手段,其众多功能在本文无法逐一涉及,尚需读者在使用中摸索。
升级会员 