基于Excel的学生收费系统设计与实现

□尤彩虹

( 山西管理职业学院，山西 临汾 041051)

学生收费一直是学校财务部门的一项复杂、繁重的工作，手工收费不仅填写发票的工作量大，而且统计收费金额的效率低，手工收取学费的方式已面临困境。本人结合多年来Excel的教学经验，设计开发了简单易用、成本低廉的基于Excel的学生收费系统，经过使用、修改和完善，系统基本成熟。现将系统设计经验做一总结，以期对其它院校的学生收费工作具有借鉴和参考价值。

一、需求分析

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)交费欠费情况查询和打印：在“学生信息”工作表中，设置自动筛选后，通过设置筛选条件实现查询和打印各班级交费和学生欠费情况。(6)离校生信息处理：学生退学、转学或毕业后，可以通过筛选出离校生，将离校生信息复制，然后选择“编辑”菜单中的“选择性粘贴”的“数值”项，将毕业生信息粘贴到新文件中保存，然后在“学生信息”工作表中将离校生信息删除。离校生交费信息处理方法与离校生信息处理方法相同。使用“选择性粘贴”的目的是使通过公式计算的数值转化为固定数值，使数值不受其它工作表数值的影响。

2.技术支持。基于Excel的学生收费系统的主要技术是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单元格，单击“工具”-“保护”-“允许用户编辑区域”-点击“新建”-点击“确定”-点击“保护工作表”-设置密码。“非事业收费票据”也设置相应的单元格保护。(2)工作表保护：学生交费工作结束后，对“交费信息”工作表设置保护，设置方法如下：选择“交费信息”工作表，单击“工具”-“保护”-“保护工作表”-设置密码。学生基本信息导入或录入后，用同样的方法对“学生信息”工作表设置保护。(3)工作簿保护：对工作簿设置打开文件口令，设置方法如下：“工具”-“选项”-“安全性”-设置打开权限密码。

四、结束语

使用Excel开发的学生收费系统，实现了学生交费、票据打印、欠费统计和汇总查询等功能，提高了财务人员的工作效率，并能够及时、准确地进行统计，为财务人员对帐和领导决策提供准确的数据。目前，系统还存在一些不足，如未编制学号的新生交费；对于汇款和银行代扣的学生交费批量打印票据等问题，还有待进一步改进和完善。

参考文献：

[1]吴毅松.基于Excel VBA的学生收费系统[J].大连民族学院学报,2006,(1).

[2]何旵阳,刘仕贤.高职院校学生收费系统设计[J].电脑与信息,2008,(8).