基于Excel的學生收費系統設計與實現

□尤彩虹

( 山西管理職業學院，山西 臨汾 041051)

學生收費一直是學校財務部門的一項復雜、繁重的工作，手工收費不僅填寫發票的工作量大，而且統計收費金額的效率低，手工收取學費的方式已面臨困境。本人結合多年來Excel的教學經驗，設計開發了簡單易用、成本低廉的基于Excel的學生收費系統，經過使用、修改和完善，系統基本成熟?，F將系統設計經驗做一總結，以期對其它院校的學生收費工作具有借鑒和參考價值。

一、需求分析

1.用戶需求分析：根據財務部門提出的業務需要，系統要求完成的功能包括：交費信息錄入；打印收費票據；統計任意時段的收費金額；統計查詢學生交費和欠費金額；打印學生交費和欠費表格。

2.系統需求分析：(1)學生信息要求：學生信息應體現學生的基本情況，包括：學號、姓名、班級；學生的交費情況，包括：學費標準、應交費、減免學費、已交費和欠費；學生的特殊情況，包括：減免學費原因、離校原因等；學生離校后能夠對離校學生信息做相應處理。(2)收費業務要求：系統能夠對學生交費的詳細信息進行記錄和查詢；能夠選擇不同的交費方式(現金、匯款和銀行代扣)；能夠打印票據，票據是財政統一的鏈式三聯票據，包括事業收款和非事業收款兩種，學費和公寓費屬于事業收款，書費、體檢費、押金和軍訓費屬于非事業收款。學生離校后能對離校學生的收費信息做相應處理。(3)數據安全性要求：對于學生信息、交費信息的全部內容能夠進行保護，信息一旦錄入，不允許進行修改，若需強行修改，必須輸入正確的修改口令；同時對文件進行保護，設置打開文件口令，以達到數據安全性的要求。(4)數據一致性要求：“交費信息”和“事業收款票據”工作表的學號、姓名、班級、學費標準數據要求與“學生信息”工作表的數據一致；“學生信息”工作表的每學年交費金額要求與“交費信息”的對應數據一致。(5)操作員工作量要求：學生信息的錄入、交費信息的錄入、學生交費情況匯總和交費票據的打印等操作要求盡量減少操作員的工作量。

二、系統設計

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

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

三、系統實現

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

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

2.技術支持?；贓xcel的學生收費系統的主要技術是Excel函數，該系統用到的Excel函數主要有：

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

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

四、結束語

使用Excel開發的學生收費系統，實現了學生交費、票據打印、欠費統計和匯總查詢等功能，提高了財務人員的工作效率，并能夠及時、準確地進行統計，為財務人員對帳和領導決策提供準確的數據。目前，系統還存在一些不足，如未編制學號的新生交費；對于匯款和銀行代扣的學生交費批量打印票據等問題，還有待進一步改進和完善。

參考文獻：

[1]吳毅松.基于Excel VBA的學生收費系統[J].大連民族學院學報,2006,(1).

[2]何旵陽,劉仕賢.高職院校學生收費系統設計[J].電腦與信息,2008,(8).