怎樣用EXCEL開發(fā)管理信息系統(tǒng)

1、 用EXCEL開發(fā)管理信息系統(tǒng)摘 要：Excel97是Windows95環(huán)境下的一種電子表格軟件,可向用戶提供史無前例的超強(qiáng)功能和易用性，內(nèi)嵌有一種Visual Basic for Application(簡(jiǎn)稱VBA)超級(jí)宏語言，讀者在熟練應(yīng)用Excel的基礎(chǔ)上，可按用戶的需求用VBA建立適用的信息系統(tǒng)。關(guān)鍵詞：EXCEL97 VBA 管理信息系統(tǒng)（MIS）目前國(guó)內(nèi)管理信息系統(tǒng)(MIS)開發(fā)研制一般采用人們熟悉的dBaseX、Foxbase或Foxpro等,本文介紹了如何用Excel開發(fā)MIS。一般來說,一套MIS主要包括信息輸入、信息處理、信息輸出三大組成部分,用Excel處理這些部分均顯得

2、游刃有余。Excel是Windows環(huán)境下的一種電子表格軟件,可向用戶提供史無前例的超強(qiáng)功能和易用性。它同時(shí)具有電子數(shù)據(jù)表、圖表和數(shù)據(jù)庫(kù)的功能,具有極強(qiáng)的分析性能、報(bào)表制作工具和豐富的統(tǒng)計(jì)圖表。在本文筆者用中文Excel97 for Windows開發(fā)了水電管理系統(tǒng)、銷售管理系統(tǒng)、試卷分析系統(tǒng)。充分感受到其強(qiáng)大功能和方便性。下面詳細(xì)闡述水電管理系統(tǒng)、銷售管理系統(tǒng)、試卷分析系統(tǒng)的開發(fā)過程。應(yīng)用程序一：水電計(jì)價(jià)系統(tǒng)水電管理是每一個(gè)單位每月都要進(jìn)行的工作。 下面我們以保定農(nóng)校水電計(jì)價(jià)系統(tǒng)為例講解VBA在水電管理中的應(yīng)用。1、 水電計(jì)價(jià)系統(tǒng)的功能本程序具有如下功能：自動(dòng)計(jì)算功能：可完成各戶水費(fèi)、電費(fèi)、

3、合計(jì)的計(jì)算。自動(dòng)匯總功能：自動(dòng)匯總?cè)＝搪毠堑挠秒娏?、用水量及分類匯總。查詢功能：可查詢各戶的電表數(shù)、水表數(shù)、電費(fèi)數(shù)、水費(fèi)數(shù)。報(bào)表的打印輸出功能。系統(tǒng)保護(hù)功能：具有口令保護(hù)、工作表保護(hù)以防非法用戶進(jìn)入和修改。2、 程序設(shè)計(jì)過程（1）程序總體設(shè)計(jì)（圖1）（2）界面設(shè)計(jì)水電計(jì)價(jià)系統(tǒng)主界面如圖2所示。（圖2）主界面是以EXCEL工作表作為輸入輸出界面。具體設(shè)計(jì)如下：選定一個(gè)工作表并命名為“主界面”。選定單元格區(qū)域，把區(qū)域顏色設(shè)置成你喜歡的顏色。放置命令按鈕，并給按鈕指定宏。分割區(qū)域并凍結(jié)，鎖定界面使之不能滾動(dòng)。工作表加密保護(hù)使用戶不能修改。記錄單界面如（圖3）所示。具體設(shè)計(jì)過程如下：選定一個(gè)工作表

4、并命名為“記錄單”。制表（如上圖所示 ）。選定年、月、水表數(shù)、電表數(shù)空白單元格區(qū)域，取消鎖定。放置命令按鈕，并給按鈕指定宏。工作表保護(hù)使用戶不能修改。主報(bào)表如（圖4、圖5）所示。具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名為“主報(bào)表”。制表（如上圖所示 ）?？v向凍結(jié)A、B兩列，橫向凍結(jié)1-5行。放置命令按鈕，并給按鈕指定宏。輸入公式：計(jì)算上次表底、本月查表記錄、水電用量、水、電費(fèi)、水電費(fèi)合計(jì)的校內(nèi)、校外合計(jì)、樓房合計(jì)、平房合計(jì)。在C338單元格輸入下面公式，拖動(dòng)填充柄復(fù)制公式至L338。=SUMIF($M$6:$M$331,"=l校內(nèi)",C6:C331)+SUMIF($M$6:$

5、M$331,"=p校內(nèi)",C6:C331) 在C339單元格輸入下面公式，拖動(dòng)填充柄復(fù)制公式至L339。=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=p校外",C6:C331)在C340單元格輸入下面公式，拖動(dòng)填充柄復(fù)制公式至L340。=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=l校內(nèi)",C6:C331)在C341單元格輸入下面公式，拖動(dòng)填充柄復(fù)制公式至L341。=S

6、UMIF($M$6:$M$331,"=p校外",C6:C331)+SUMIF($M$6:$M$331,"=p校內(nèi)",C6:C331)工作表保護(hù)使用戶不能修改。查表檔案界面如（下圖）所示：具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名為“檔案”。制表（如圖6所示 ）?？v向凍結(jié)A、B兩列，橫向凍結(jié)1-3行。放置命令按鈕，并給按鈕指定宏。工作表保護(hù)使用戶不能修改。查詢檔案如（下圖）所示：具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名為“查詢”。制表（如圖7所示 ）。案").ActivateRange("C4:AB329").SelectSelec

7、tion.ClearContentsRange("a1").SelectWorksheets("主界面").ActivateEnd Sub應(yīng)用程序二：銷售管理系統(tǒng)在商業(yè)企業(yè)的經(jīng)營(yíng)活動(dòng)中，主要業(yè)務(wù)活動(dòng)是進(jìn)銷存。本程序主要解決銷售的問題，關(guān)于進(jìn)貨和庫(kù)存留給讀者自己在原來程序的基礎(chǔ)上進(jìn)一步開發(fā)。1、 銷售管理系統(tǒng)的功能自動(dòng)計(jì)算進(jìn)貨金額、銷貨金額和毛利。分類匯總功能，可按商品和經(jīng)手人代碼進(jìn)行分類匯總。打印功能查詢功能（可按商品和經(jīng)手人代碼進(jìn)行查詢）。2、 銷售管理系統(tǒng)的程序設(shè)計(jì)銷售管理系統(tǒng)總體設(shè)計(jì)界面設(shè)計(jì)：（圖8）主界面如（圖9）所示：界面具體設(shè)計(jì)過程：選定一工

8、作表，重命名為“主界面”。選定工作表區(qū)域，設(shè)置區(qū)域顏色。在區(qū)域內(nèi)添加應(yīng)用程序標(biāo)題和命令按鈕。給按鈕指定宏 。對(duì)主界面進(jìn)行口令保護(hù)。分割工作表區(qū)域并凍結(jié)，以防屏幕滾動(dòng)。發(fā)貨單如（圖10）所示：發(fā)貨單界面設(shè)計(jì)過程如下：按圖10格式制表。放置命令按鈕。選定A5,C5,E5,E10,A7:F9解鎖定。在G7,G8,G9單元格里依次輸入公式"E7*F7","=E8*F8","=E9*F9",?ú?G10單元格里輸入 "=SUM(G7:G9)"。保護(hù)工作表。毛利核算表如（圖11）所示：界面具體設(shè)計(jì)過程：選定一工作表重

9、命名為“毛利核算”。按圖11所示制表。應(yīng)用程序三：試卷分析系統(tǒng)1、 試卷分析系統(tǒng)的功能自動(dòng)統(tǒng)計(jì)全班人數(shù)、缺考人數(shù)、實(shí)考人數(shù)、優(yōu)、良、及格、不及格人數(shù)。自動(dòng)計(jì)算平均成績(jī)、不及格率、各成績(jī)段人數(shù)百分比。自動(dòng)繪制成績(jī)分布曲線。打印功能?？纱蛴〕煽?jī)單和試卷分析表。2、 程序設(shè)計(jì)過程試卷分析系統(tǒng)的總體設(shè)計(jì)界面設(shè)計(jì)主界面如圖12所示：主界面具體設(shè)計(jì)過程：選定一個(gè)工作表并命名為“主界面”。選定單元格區(qū)域，把區(qū)域顏色設(shè)置成你喜歡的顏色。放置命令按鈕，并給按鈕指定宏。分割區(qū)域并凍結(jié)，鎖定界面使之不能滾動(dòng)。工作表加密保護(hù)使用戶不能修改。成績(jī)單界面如圖13所示：界面具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名為“成績(jī)單”

10、。制表（如圖13所示 ）。選定“考試”空白單元格區(qū)域，取消鎖定。放置命令按鈕，并給按鈕指定宏。輸入函數(shù)：在F4,L4兩個(gè)單元格里分別輸入“=IF(AND(C4<60,C4<>""),"不及格",IF(AND(C4>=60,C4<=74.9),"及格",IF(AND(C4<90,C4>=75),"良",IF(AND(C4<=100,C4>=90),"優(yōu)",IF(C4="*","缺考","&quo

11、t;)”，在C29里輸入公式統(tǒng)計(jì)全班人數(shù)“=50-COUNTBLANK(C4:C28)-COUNTBLANK(I4:I28)”，在F29輸入公式“=COUNTIF(成績(jī)單!$C$4:$C$28,"=*")+COUNTIF(成績(jī)單!$I$4:$I$28,"=*")，在I29輸入公式“=C29-F29”計(jì)算實(shí)考人數(shù)。 在D30單元格里輸入公式“=COUNTIF($F$4:$F$28,"=優(yōu)")+COUNTIF($L$4:$L$28,"=優(yōu)")”計(jì)算優(yōu)的人數(shù)，在D31單元格里輸入公式“=COUNTIF($F$4:$F$2

12、8,"=良")+COUNTIF($L$4:$L$28,"=良")”計(jì)算良的人數(shù)，在I30單元格里輸入公式“=COUNTIF($F$4:$F$28,"=及格")+COUNTIF($L$4:$L$28,"=及格")”計(jì)算及格的人數(shù)，在I31單元格里輸入公式“=COUNTIF($F$4:$F$28,"=不及格")+COUNTIF($L$4:$L$28,"=不及格")”計(jì)算不及格的人數(shù)，工作表保護(hù)使用戶不能修改。試卷分析打印界面如圖14所示：界面具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名

13、為“打印界面”。選定單元格區(qū)域，把區(qū)域顏色設(shè)置成你喜歡的顏色。放置命令按鈕，并給按鈕指定宏。分割區(qū)域并凍結(jié)，鎖定界面使之不能滾動(dòng)。工作表加密保護(hù)使用戶不能修改。試卷分析界面如圖15所示：界面具體設(shè)計(jì)過程：選定一個(gè)工作表并命名為“試卷分析”。制表（如圖15所示 ）。放置命令按鈕，并給按鈕指定宏。選定G4:P4 G7:P7繪制折線圖。在單元格輸入公式：A6:"=AVERAGE(成績(jī)單!C4:C28,成績(jī)單!I4:I28)"B6:"=MAX(成績(jī)單!$C$4:$C$28,成績(jī)單!$I$4:$I$28)"C6:"=MIN(成績(jī)單!$C$4:$C$28,

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

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

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

17、分析!O6"O6:"=COUNTIF(成績(jī)單!C4:C28,">=80")+COUNTIF(成績(jī)單!I4:I28,">=80")-試卷分析!P6"P6:"=COUNTIF(成績(jī)單!C4:C28,">=90")+COUNTIF(成績(jī)單!I4:I28,">=90")"G7:"=G6/$N$8"并選定G7單元格拖動(dòng)填充柄復(fù)制至P7單元格。C8:"=成績(jī)單!C29"H8:"=成績(jī)單!C29"

18、N8:"=C8-H8"工作表保護(hù)使用戶不能修改。姓名庫(kù)界面圖16所示：界面具體設(shè)計(jì)過程如下：選定一個(gè)工作表并命名為“姓名庫(kù)”。制表（如圖16所示 ）。3、 試卷分析程序的VBA代碼及說明Sub 宏1() 指定給主界面上的成績(jī)錄入按鈕Sheets("成績(jī)單").Select 激活成績(jī)單工作表End Sub Sub 宏2() 指定給主界面上的分析按鈕Application.MaxChange = .001ActiveWorkbook.PrecisionAsDisplayed = FalseCalculate 執(zhí)行自動(dòng)計(jì)算End SubSub 宏4() 指定給

19、主界面上的打印按鈕Sheets("打印界面").Select 激活打印界面工作表End SubSub 宏5() 指定給打印界面上的打印成績(jī)單按鈕Sheets("成績(jī)單").SelectActiveWindow.SelectedSheets.PrintOut Copies:=1End Sub 打印成績(jī)單Sub 宏6() 指定給打印界面上的打印試卷分析按鈕Sheets("試卷分析").SelectActiveWindow.SelectedSheets.PrintOut Copies:=1End SubSub 宏7() 指定給返回按鈕She

20、ets("主界面").SelectEnd Sub 返回主界面Sub 宏11() 指定給主界面上的幫助按鈕Sheets("幫助窗口").Select 激活幫助工作表界面End SubSub urxm() 指定給成績(jī)單界面上的返回主界面按鈕Dim bjj As Stringbjj = Workbooks("ujfx.xls").Worksheets("成績(jī)單").Range("c2").ValueIf bjj = "財(cái)1" ThenWorksheets("姓名庫(kù)&quo

21、t;).Range("a3:a27").Copy destination:=Worksheets("成績(jī)單").Range("b4:b28")Worksheets("姓名庫(kù)").Range("b3:b27").Copy destination:=Worksheets("成績(jī)單").Range("h4:h28")ElseIf bjj = "財(cái)2" ThenWorksheets("姓名庫(kù)").Range("c3

22、:c27").Copy destination:=Worksheets("成績(jī)單").Range("b4:b28")Worksheets("姓名庫(kù)").Range("d3:d27").Copy destination:=Worksheets("成績(jī)單").Range("h4:h28")ElseIf bjj = "" ThenWorksheets("姓名庫(kù)").Range("a100:a124").Copy

23、destination:=Worksheets("成績(jī)單").Range("b4:b28")Worksheets("姓名庫(kù)").Range("b100:b124").Copy destination:=Worksheets("成績(jī)單").Range("h4:h28")ElseEnd Iffanhui 調(diào)用FANHUI宏End Sub 以上代碼是根據(jù)成績(jī)單上C2單元格的值用選擇語句從姓名庫(kù)里取姓名，然后放到成績(jī)單的相應(yīng)單元格里。Sub fanhui()Worksheets(&q

24、uot;主界面").ActivateEnd Sub下面就信息輸入、信息處理、信息輸出界面設(shè)計(jì)、代碼設(shè)計(jì)和數(shù)據(jù)保護(hù)等方面的問題作一小結(jié)：一、信息輸入最常用的信息輸入方法有三種,一是在工作表(WorkSheets)的單元格(Cells)中直接輸入文字和數(shù)據(jù)(重復(fù)數(shù)據(jù)可由填充把柄拖拉而得);二是采用輸入框(InputBox)輸入少量數(shù)據(jù);三是自行設(shè)計(jì)對(duì)話窗體(Forms),在其上可加入標(biāo)簽框、文本框、列表框、分組框、選擇框、滾動(dòng)條和命令按鈕等,然后設(shè)計(jì)其對(duì)象格式(如屬性、保護(hù)、控制項(xiàng)、對(duì)齊、字體和圖案等)。當(dāng)然,Windows提供的剪裁板進(jìn)行數(shù)據(jù)的復(fù)制或粘貼也不失為一種常用的方法。此外,E

25、xcel還提供了強(qiáng)大的文件轉(zhuǎn)換功能,可將Txt文件、dBase、Lotus 1-2-3文件、QuattroPro文件、MS Work文件、SYLK文件、DIF文件等直接轉(zhuǎn)換為Excel工作簿文件,這就為數(shù)據(jù)的共享、快速錄入及轉(zhuǎn)換提供了極大的方便。二、信息處理按行業(yè)的不同,信息處理的要求有較大的差別,常用的信息處理包括數(shù)據(jù)的修改、查詢、檢索、排序和統(tǒng)計(jì)匯總。在dBase中,這些工作一般需要編程設(shè)計(jì),而在Excel中,幾乎所有的工作均可由Excel本身提供的功能輕松地完成。最常用的是右端"數(shù)據(jù)"菜單中的各項(xiàng)功能"記錄單"選項(xiàng)提供了數(shù)據(jù)庫(kù)記錄查詢、輸入、修改或

26、刪除的簡(jiǎn)單方法;"排序"選項(xiàng)提供了數(shù)據(jù)庫(kù)記錄同時(shí)按三個(gè)字段的排序方法,可以按行或按列、以升序或降序、考慮或不考慮大寫來進(jìn)行排序;"篩選"選項(xiàng)提供了隱藏除符合指定條件外的所有數(shù)據(jù)的方法,為數(shù)據(jù)庫(kù)同類記錄的查詢和重點(diǎn)顯示提供了方便;"分類匯總"選項(xiàng)提供了同類數(shù)據(jù)的匯總及統(tǒng)計(jì)方法;"數(shù)據(jù)透視表"選項(xiàng)可以迅速使一個(gè)復(fù)雜的表格變得容易閱讀和理解,它可以方便地概括出感興趣的字段,從不同的角度"透視"出求和值、計(jì)數(shù)值、平均值、最大值、最小值、乘積值、標(biāo)準(zhǔn)偏差、總體標(biāo)準(zhǔn)偏差、變異值、總體變異值。表格設(shè)計(jì)是MIS

27、的重點(diǎn)和難點(diǎn)之一,然而在Excel中,表格設(shè)計(jì)卻易如反掌。Excel的工作簿(WorkBooks)是一個(gè)三維電子表格,一個(gè)工作簿由若干個(gè)工作表構(gòu)成,每個(gè)工作表本身就是一張表格,它最多可達(dá)16384行256列,足以滿足表格設(shè)計(jì)的需要。表格中每一行的高度和每一列的寬度均可隨意調(diào)節(jié),多余的行和列可以隱藏起來,表格中的每個(gè)單元格可有不同的字體、字號(hào)、邊框線、前景色、數(shù)據(jù)格式、對(duì)齊方式和保護(hù)方式,在每個(gè)單元格中還可插入不同的文字附注和聲音附注。此外,在每個(gè)工作表中可任意繪制直線、曲線、矩形、橢圓、箭頭和各種窗體控件(Controls),并且還可插入其他Windows應(yīng)用程序的圖片和對(duì)象,支持DDL和OL

28、E。Excel的這些功能為靈活設(shè)計(jì)各種復(fù)雜的表格提供了極大的方便。統(tǒng)計(jì)圖表是各類MIS的特色之一,能否設(shè)計(jì)出美觀易懂的圖表從一個(gè)方面反映了MIS生命力的強(qiáng)弱。Excel的作圖能力特別強(qiáng)大,操作使用方便,它可從工作表中靈活地提取所需的數(shù)據(jù),快速生成二維或三維的條形圖、柱形圖、餅圖、環(huán)形圖、散列圖、曲面圖、雷達(dá)圖等。三、信息輸出Excel具有所見即所得的功能,凡是在工作表上能顯示出來的內(nèi)容(文字、數(shù)據(jù)、圖形、圖表)均可打印出來,超寬表格可以自動(dòng)分頁(yè)打印;其打印選項(xiàng)十分豐富,完全不需要自己編寫另外的打印驅(qū)動(dòng)代碼。四、界面制作在Excel中,MIS的界面既可采用對(duì)話窗體進(jìn)行設(shè)計(jì),也可在工作表上直接進(jìn)行設(shè)計(jì),窗體的各種控件可隨意放置在工作表上。在錄制或編寫了宏代碼后,還可根據(jù)自己的需要任意修改Excel的菜單、工具箱以及提示行顯示信息,而隱藏Excel本身的菜單系統(tǒng),使MIS仿佛脫離了Excel處于單獨(dú)運(yùn)行之中。為了在進(jìn)入Windows的同時(shí)就進(jìn)入MIS界面,可在Windows的初始化文件WIN.INI中加入LOAD=C:Excel5Excel.EXE /E,在C:Excel5XLSTART子目錄中加入MIS的界面控制程序,這樣將在啟動(dòng)Windows的同