Excel在会计和财务中应用资料：项目三习题.docVIP

【技能强化训练】 实训目标：根据提供的有关资料运用EXCEL设计一个简单的教师工资系统。 实训资料： 1.主要功能如下： （1）超课时费生成功能； （2）工龄工资数据生成功能； （3）工资数据生成； （4）工资数据汇总功能； （5）工资查询。 2.基本资料如下： （1）员工代码、姓名、职称与部门对照表（见图3-41）。 操作步骤： 1） 启动EXCEL，新建“职工工资管理”工作簿； 2） 将工作表“Sheet1”重命名为“员工基本信息表”； 3） 建立如图3-41所示的表格； 4） 选定A1至D9这个区域，定义区域名为“员工基本信息区”； （2）职称与基本工资、津贴、基本课时数、超课时费（每学时）和工龄工资（每年）对照表，如图3-42所示。 操作步骤： 1） 接前面“职工工资管理”工作簿； 2） 将工作表“Sheet2”重命名为“工资核算标准表”； 3） 建立如图3-42所示的表格； 4） 选定A1至F5这个区域，定义区域名为“工资核算标准区”； 要求： （1）根据已知初始化数据表（图3-41和图3-42）生成课时费用表的数据，发图3-43所示。 操作步骤： 1） 接前面“职工工资管理”工作簿； 2） 将工作表“Sheet3”重命名为“课时费表”； 3） 建立如图3-43所示的表格； 4） 将“员工基本信息表”中的代码和姓名复制到本表代码和姓名对应单元格； 5） “实际课时”列数据根据教师实际完成的工作量来填，假定数据为8个老师的周课时依次为：12、14、10、8、16、20、6、4，那就将12、14、10、8、16、20、6、4这8个数依次填列到C2、C3、C4、C5、C6、C7、C8、C9单元格； 6） 将实际周课时减去周标准课时，再乘以4得到一个月的超课时数，表中超课时数单元格D2中可以填入下列公式：=(C2-VLOOKUP(VLOOKUP(A2,员工信息区,3,FALSE),工资核算标准区,5,FALSE))*4，将此公式复制到D3到D9； 7） 将超课时数乘以职称对应的课时费标准，得到超课时费，在E2单元格中填入下列公式：=VLOOKUP(VLOOKUP(A2,员工信息区,3,FALSE),工资核算标准区,6,FALSE)*D2，将此公式复制到E3到E9； 8） 选定A1到E9，将区域定义名称为：课时费区； （2）根据已知初始化数据表（图3-41和图3-42）生成工龄表的有关数据，如图3-44所示。 操作步骤： 1） 接前面“职工工资管理”工作簿； 2） 添加一个新的工作表，并命名为“工龄工资表”； 3） 建立如图3-44所示的表格； 4） 代码、姓名从前面表格复制填列； 5） 上班时间按实际填写；（假定为：1995-8-1、1992-8-1、1992-8-1、2012-8-1、1992-8-1、1992-8-1、1995-8-1、1992-8-1） 6） 在现在时间栏D2单元格填入公式：=NOW（），将公式复制到本列其字单元格； 7） 工龄工资由工龄年数乘以工龄工资标准得到，在单元格E2填写公式：=(YEAR(D2)-YEAR(C2)+1)*VLOOKUP(VLOOKUP(A2,员工信息区,3,FALSE),工资核算标准区,4,FALSE)，将此单元格公式复制到本列其他单元格； 8） 选定A1至E9，将区域定义名称为：工龄工资区； （3）根据初始化数据表，超课时费表和工资有关数据生成工资表数据，如图3-45所示。 操作步骤： 1）接前面“职工工资管理”工作簿； 2）添加一个新的工作表，并命名为“工资表”； 3）建立如图3-45所示的表格； 4）代码、姓名、部门数据从前面相关表格复制填列； 5）基本工资、津贴、工龄工资、超课时费都从前面表格引用得出数据，具体公式为：D2单元格为：=VLOOKUP(VLOOKUP(A2,员工信息区,3,FALSE),工资核算标准区,2,FALSE) E2单元格为：=VLOOKUP(VLOOKUP(A2,员工信息区,3,FALSE),工资核算标准区,3,FALSE) F2单元格为：=VLOOKUP(A2,工龄工资区,5) G2单元格为：=VLOOKUP(A2,课时费区,5) 6）工资总额由基本工资、津贴、工龄工资、超课时费几项相加得出，H2公式为： =SUM(D2:G2) 7）所得税的计算比较复杂，这里采用简化处理，假定统一采用税率5%，扣除数为0，免征额为3500，这个税段，则I2公式为：=IF(H2-35000,(H2-3500)*0.05,0)；复制该公式到本列其它单元格；想要更准确可以参照教材所得税的处理方法； 8）实发工资单元格J2：=H2-I2； （4）对基本工资、超课时费、所得税统计汇总，生成统计汇总表，如图3-46所示。 职业核心能力测评表 操作步骤： 1）接前