基于Excel的學(xué)生收費(fèi)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)

□尤彩虹

( 山西管理職業(yè)學(xué)院，山西 臨汾 041051)

學(xué)生收費(fèi)一直是學(xué)校財(cái)務(wù)部門的一項(xiàng)復(fù)雜、繁重的工作，手工收費(fèi)不僅填寫發(fā)票的工作量大，而且統(tǒng)計(jì)收費(fèi)金額的效率低，手工收取學(xué)費(fèi)的方式已面臨困境。本人結(jié)合多年來Excel的教學(xué)經(jīng)驗(yàn)，設(shè)計(jì)開發(fā)了簡(jiǎn)單易用、成本低廉的基于Excel的學(xué)生收費(fèi)系統(tǒng)，經(jīng)過使用、修改和完善，系統(tǒng)基本成熟。現(xiàn)將系統(tǒng)設(shè)計(jì)經(jīng)驗(yàn)做一總結(jié)，以期對(duì)其它院校的學(xué)生收費(fèi)工作具有借鑒和參考價(jià)值。

一、需求分析

1.用戶需求分析：根據(jù)財(cái)務(wù)部門提出的業(yè)務(wù)需要，系統(tǒng)要求完成的功能包括：交費(fèi)信息錄入；打印收費(fèi)票據(jù)；統(tǒng)計(jì)任意時(shí)段的收費(fèi)金額；統(tǒng)計(jì)查詢學(xué)生交費(fèi)和欠費(fèi)金額；打印學(xué)生交費(fèi)和欠費(fèi)表格。

2.系統(tǒng)需求分析：(1)學(xué)生信息要求：學(xué)生信息應(yīng)體現(xiàn)學(xué)生的基本情況，包括：學(xué)號(hào)、姓名、班級(jí)；學(xué)生的交費(fèi)情況，包括：學(xué)費(fèi)標(biāo)準(zhǔn)、應(yīng)交費(fèi)、減免學(xué)費(fèi)、已交費(fèi)和欠費(fèi)；學(xué)生的特殊情況，包括：減免學(xué)費(fèi)原因、離校原因等；學(xué)生離校后能夠?qū)﹄x校學(xué)生信息做相應(yīng)處理。(2)收費(fèi)業(yè)務(wù)要求：系統(tǒng)能夠?qū)W(xué)生交費(fèi)的詳細(xì)信息進(jìn)行記錄和查詢；能夠選擇不同的交費(fèi)方式(現(xiàn)金、匯款和銀行代扣)；能夠打印票據(jù)，票據(jù)是財(cái)政統(tǒng)一的鏈?zhǔn)饺?lián)票據(jù)，包括事業(yè)收款和非事業(yè)收款兩種，學(xué)費(fèi)和公寓費(fèi)屬于事業(yè)收款，書費(fèi)、體檢費(fèi)、押金和軍訓(xùn)費(fèi)屬于非事業(yè)收款。學(xué)生離校后能對(duì)離校學(xué)生的收費(fèi)信息做相應(yīng)處理。(3)數(shù)據(jù)安全性要求：對(duì)于學(xué)生信息、交費(fèi)信息的全部?jī)?nèi)容能夠進(jìn)行保護(hù)，信息一旦錄入，不允許進(jìn)行修改，若需強(qiáng)行修改，必須輸入正確的修改口令；同時(shí)對(duì)文件進(jìn)行保護(hù)，設(shè)置打開文件口令，以達(dá)到數(shù)據(jù)安全性的要求。(4)數(shù)據(jù)一致性要求：“交費(fèi)信息”和“事業(yè)收款票據(jù)”工作表的學(xué)號(hào)、姓名、班級(jí)、學(xué)費(fèi)標(biāo)準(zhǔn)數(shù)據(jù)要求與“學(xué)生信息”工作表的數(shù)據(jù)一致；“學(xué)生信息”工作表的每學(xué)年交費(fèi)金額要求與“交費(fèi)信息”的對(duì)應(yīng)數(shù)據(jù)一致。(5)操作員工作量要求：學(xué)生信息的錄入、交費(fèi)信息的錄入、學(xué)生交費(fèi)情況匯總和交費(fèi)票據(jù)的打印等操作要求盡量減少操作員的工作量。

二、系統(tǒng)設(shè)計(jì)

1.工作表設(shè)計(jì)：系統(tǒng)建立了一個(gè)Excel工作簿文件，該工作簿設(shè)置了四個(gè)工作表，具體如下：(1)學(xué)生信息(sheet1)：記錄學(xué)生的基本情況、交費(fèi)情況和特殊情況，具體包括：學(xué)號(hào)、姓名、班級(jí)、學(xué)費(fèi)、應(yīng)交費(fèi)、減免、第一學(xué)年、第二學(xué)年、第三學(xué)年、補(bǔ)交、已交費(fèi)、欠費(fèi)、備注。(2)交費(fèi)信息(sheet2)：記錄學(xué)生的交費(fèi)明細(xì)信息，具體包括：發(fā)票號(hào)、學(xué)號(hào)、姓名、班級(jí)、交費(fèi)方式、學(xué)費(fèi)、公寓費(fèi)、書費(fèi)、押金、體檢費(fèi)、軍訓(xùn)費(fèi)、合計(jì)、日期、收款人、備注。(3)事業(yè)收款票據(jù)(sheet3)：用于打印事業(yè)收款票據(jù)，包括：日期、學(xué)號(hào)、姓名、班級(jí)、收費(fèi)年度、收費(fèi)項(xiàng)目、收費(fèi)標(biāo)準(zhǔn)、收費(fèi)金額、合計(jì)金額大寫、小寫、收款人、制單人。(4)非事業(yè)收款票據(jù)(sheet4)：除“收費(fèi)項(xiàng)目”內(nèi)容與“事業(yè)收款票據(jù)”不同外，其余內(nèi)容相同。

2.功能設(shè)計(jì)：系統(tǒng)的各個(gè)工作表能夠完成的功能如下：(1)學(xué)生信息(sheet1)：能夠完成學(xué)生基本情況和特殊情況的錄入和查詢；能夠完成學(xué)生交費(fèi)和欠費(fèi)情況的統(tǒng)計(jì)、查詢和打印。(2)交費(fèi)信息(sheet2)：能夠完成學(xué)生各項(xiàng)費(fèi)用的收繳錄入和查詢；能夠統(tǒng)計(jì)任意時(shí)段的收費(fèi)金額。(3)事業(yè)收款票據(jù)(sheet3)：能夠完成事業(yè)收款票據(jù)的打印。(4)非事業(yè)收款票據(jù)(sheet4)：能夠完成非事業(yè)收款票據(jù)的打印。

三、系統(tǒng)實(shí)現(xiàn)

1.功能實(shí)現(xiàn)。(1)學(xué)生情況錄入：學(xué)生基本情況的“學(xué)號(hào)”、“姓名”和“班級(jí)”內(nèi)容從學(xué)院其它相關(guān)部門獲取。學(xué)生的“學(xué)費(fèi)”標(biāo)準(zhǔn)，通過錄入和Excel的填充功能完成。學(xué)生的特殊情況在備注列輸入。(2)學(xué)生收費(fèi)錄入：輸入“學(xué)號(hào)”后，“姓名”、“班級(jí)”和“學(xué)費(fèi)”的內(nèi)容通過在“學(xué)生信息”工作表中查找“學(xué)號(hào)”來獲取，“發(fā)票號(hào)”通過填充錄入，“交費(fèi)方式”通過下拉菜單選擇，“合計(jì)”通過函數(shù)計(jì)算，“日期”通過當(dāng)前日期函數(shù)獲取，其余內(nèi)容通過填充錄入。學(xué)生交費(fèi)時(shí)，可通過復(fù)制已交費(fèi)的信息后輸入學(xué)生“學(xué)號(hào)”來完成交費(fèi)工作，即操作員僅需要輸入“學(xué)號(hào)”就可完成學(xué)生收費(fèi)。(3)票據(jù)打印：以事業(yè)收款票據(jù)打印為例，樣張如下：

輸入“學(xué)號(hào)”后，“姓名”、“班級(jí)”和“學(xué)費(fèi)標(biāo)準(zhǔn)”的內(nèi)容通過在“學(xué)生信息”工作表中查找“學(xué)號(hào)”來獲取，“日期”通過當(dāng)前日期函數(shù)獲取，“收費(fèi)金額”通過“收費(fèi)標(biāo)準(zhǔn)”獲取，合計(jì)小寫通過求和函數(shù)計(jì)算，合計(jì)大寫通過小寫金額和函數(shù)完成，其余內(nèi)容直接錄入。操作員僅需輸入“學(xué)號(hào)”就可完成票據(jù)打印。(4)交費(fèi)欠費(fèi)情況統(tǒng)計(jì)：交費(fèi)情況統(tǒng)計(jì)包括統(tǒng)計(jì)任意時(shí)段的收費(fèi)金額和統(tǒng)計(jì)學(xué)生交費(fèi)欠費(fèi)金額兩種情況。統(tǒng)計(jì)任意時(shí)段的收費(fèi)金額：利用Excel的求和函數(shù)，選擇需要統(tǒng)計(jì)的收費(fèi)合計(jì)范圍即可。統(tǒng)計(jì)學(xué)生交費(fèi)欠費(fèi)金額：在“學(xué)生信息”工作表中，“欠費(fèi)”=“應(yīng)交費(fèi)―減免―已交費(fèi)”，應(yīng)交費(fèi)=(學(xué)費(fèi)+公寓費(fèi))×年級(jí)+其它費(fèi)合計(jì)，“減免”學(xué)費(fèi)金額手工錄入，“已交費(fèi)”為三個(gè)學(xué)年交的學(xué)費(fèi)和補(bǔ)交學(xué)費(fèi)的合計(jì)，各學(xué)年“交費(fèi)金額”通過在“學(xué)生收費(fèi)”工作表的相應(yīng)范圍查找“學(xué)號(hào)”獲取。每學(xué)期學(xué)生交費(fèi)前設(shè)置相應(yīng)內(nèi)容，學(xué)生交費(fèi)時(shí)“欠費(fèi)”可實(shí)現(xiàn)自動(dòng)計(jì)算。(5)交費(fèi)欠費(fèi)情況查詢和打?。涸凇皩W(xué)生信息”工作表中，設(shè)置自動(dòng)篩選后，通過設(shè)置篩選條件實(shí)現(xiàn)查詢和打印各班級(jí)交費(fèi)和學(xué)生欠費(fèi)情況。(6)離校生信息處理：學(xué)生退學(xué)、轉(zhuǎn)學(xué)或畢業(yè)后，可以通過篩選出離校生，將離校生信息復(fù)制，然后選擇“編輯”菜單中的“選擇性粘貼”的“數(shù)值”項(xiàng)，將畢業(yè)生信息粘貼到新文件中保存，然后在“學(xué)生信息”工作表中將離校生信息刪除。離校生交費(fèi)信息處理方法與離校生信息處理方法相同。使用“選擇性粘貼”的目的是使通過公式計(jì)算的數(shù)值轉(zhuǎn)化為固定數(shù)值，使數(shù)值不受其它工作表數(shù)值的影響。

2.技術(shù)支持?；贓xcel的學(xué)生收費(fèi)系統(tǒng)的主要技術(shù)是Excel函數(shù)，該系統(tǒng)用到的Excel函數(shù)主要有：

(1)VLOOKUP函數(shù)：“交費(fèi)信息”工作表的“姓名”通過“學(xué)號(hào)”從“學(xué)生信息”工作表獲取，用到VLOOKUP 函數(shù)，具體為：“=VLOOKUP(B2,學(xué)生信息!$A$2:$D$3000,2,0)”?！皩W(xué)生信息”工作表的“學(xué)年交費(fèi)金額”通過“學(xué)號(hào)” 從“交費(fèi)信息”工作表獲取，具體為：=VLOOKUP(A2,交費(fèi)信息!$B$1100:$L$2000,11,0)。在該函數(shù)的單元格引用中使用了絕對(duì)引用，函數(shù)復(fù)制后，查找范圍固定不變。該函數(shù)的使用實(shí)現(xiàn)了系統(tǒng)數(shù)據(jù)一致性的要求，同時(shí)也減輕了操作員的工作量。(2)大小寫金額轉(zhuǎn)換函數(shù)：“事業(yè)收款票據(jù)”工作表的大寫金額通過小寫金額換算，具體如下：“=IF(TRIM(F8)="","",IF(F8=0,"",""&SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(F8)),"[dbnum2]G/通用格式元;負(fù)[dbnum2]G/通用格式元;"&IF(F8>-0.5%,,"負(fù)"))&TEXT(RIGHT(FIXED(F8),2),"[dbnum2]0角0分;;"&IF(ABS(F8)>1%,"整",)),"零角",IF(ABS(F8)<1,,"零")),"零分","整")))”(3)TODAY函數(shù)：“交費(fèi)信息”和“收款票據(jù)”中的“日期”，均通過取當(dāng)前日期獲取，具體為：“=TODAY()”和“=TEXT(TODAY(),"yyyymmdd")”兩種不同的顯示方式。

3.系統(tǒng)安全性實(shí)現(xiàn)。對(duì)系統(tǒng)文件實(shí)施了單元格、工作表和工作簿的三重安全保護(hù)，以實(shí)現(xiàn)系統(tǒng)數(shù)據(jù)的安全性要求。(1)單元格保護(hù)：對(duì)于“事業(yè)收款票據(jù)”工作表，除B2(學(xué)號(hào))、F4(學(xué)費(fèi))和F5(公寓費(fèi))單元格外的其它所有單元格設(shè)置保護(hù)，只允許選擇，不允許修改，若要強(qiáng)行修改，必須輸入正確的密碼。設(shè)置方法如下：選擇B2、F4和F5單元格，單擊“工具”-“保護(hù)”-“允許用戶編輯區(qū)域”-點(diǎn)擊“新建”-點(diǎn)擊“確定”-點(diǎn)擊“保護(hù)工作表”-設(shè)置密碼?！胺鞘聵I(yè)收費(fèi)票據(jù)”也設(shè)置相應(yīng)的單元格保護(hù)。(2)工作表保護(hù)：學(xué)生交費(fèi)工作結(jié)束后，對(duì)“交費(fèi)信息”工作表設(shè)置保護(hù)，設(shè)置方法如下：選擇“交費(fèi)信息”工作表，單擊“工具”-“保護(hù)”-“保護(hù)工作表”-設(shè)置密碼。學(xué)生基本信息導(dǎo)入或錄入后，用同樣的方法對(duì)“學(xué)生信息”工作表設(shè)置保護(hù)。(3)工作簿保護(hù)：對(duì)工作簿設(shè)置打開文件口令，設(shè)置方法如下：“工具”-“選項(xiàng)”-“安全性”-設(shè)置打開權(quán)限密碼。

四、結(jié)束語

使用Excel開發(fā)的學(xué)生收費(fèi)系統(tǒng)，實(shí)現(xiàn)了學(xué)生交費(fèi)、票據(jù)打印、欠費(fèi)統(tǒng)計(jì)和匯總查詢等功能，提高了財(cái)務(wù)人員的工作效率，并能夠及時(shí)、準(zhǔn)確地進(jìn)行統(tǒng)計(jì)，為財(cái)務(wù)人員對(duì)帳和領(lǐng)導(dǎo)決策提供準(zhǔn)確的數(shù)據(jù)。目前，系統(tǒng)還存在一些不足，如未編制學(xué)號(hào)的新生交費(fèi)；對(duì)于匯款和銀行代扣的學(xué)生交費(fèi)批量打印票據(jù)等問題，還有待進(jìn)一步改進(jìn)和完善。

參考文獻(xiàn)：

[1]吳毅松.基于Excel VBA的學(xué)生收費(fèi)系統(tǒng)[J].大連民族學(xué)院學(xué)報(bào),2006,(1).

[2]何旵陽,劉仕賢.高職院校學(xué)生收費(fèi)系統(tǒng)設(shè)計(jì)[J].電腦與信息,2008,(8).