Excel讲解及练习员工工资表

模块三 Excel 电子表格制作项目一不变项目二制作员工工资表（ 调整了任务和素材 ）项目说明工资是企业在一定时间内直接支付给本单位员工的劳动报酬，也是企业进行各种费用计提的基础。工资管理是企业管理的重要组成部分，是每个单位财会部门最基本的业务之一。手工进行工资核算，需要占用财务人员大量的精力和时间，并且容易出错， 采用计算机进行工资核算可以有效提高工资核算的准确性和及时性。项目分析本项目要求建立员工工资表，计算其中的相关数据，对工资数据进行查询与汇总分析，并打印工资发放条。2010 年 4 月份某公司员工基本工资和出勤情况如表3-2-1所示。表 3-2-1 2010年 4 月份某公司员工基本工资和出勤情况表职工姓名性别年龄所属职工基本事假病假代码部门类别工资天数天数A001许振男57管理部管理人员3000A002徐仁华女55管理部管理人员28002A003张焱女36管理部管理人员26005B001郑昂男27销售部销售员2000B002李帆男32销售部销售员2000B003吴星男36销售部销售员150015B004唐嘉男45销售部销售员1500B005孙丽女29销售部销售员150020C001许涛男21生产部工人12002C002陈苏苏女26生产部工人1200C003王飞飞女22生产部工人120016任务 1建立员工工资表并输入基本数据分析或说明： 建立基本工资表主要是搭建工资表的框架，在工资表中列出一些有关员工工资的项目，并输入基本数据。在输入数据时为了防止出错可以进行有效性控制。步骤：1、启动 Excel 应用程序，单击“文件”“另存为”菜单项，在弹出的“另存为”对话框中，输入文件名“工资”，文件类型为“.xls”。2、双击工作表标签，将工作表“sheet1 ”重命名为“ 2010 年 4 月”。3、在工作表第一行输入如下标题：职工代码、 姓名、性别、年龄、所属部门、职工类别、基本工资、岗位工资、奖金、应发合计、事假天数、事假扣款、病假天数、病假扣款、扣款合计、养老金、医疗金、公积金、应发工资、所得税、实发工资。4、参照表 3-2-1 中的数据输入第一列“职工代码”和第二列“姓名”。5、光标放在 C2 单元格，单击“数据”“有效性”菜单项，打开“数据有效性”对话框，如图3-2-1 所示。6、在“允许”列表中选择“序列”，在“来源”中输入“男, 女”（用英文逗号隔开），单击“确定”按钮完成设置。7、将光标移动到C2 单元格右下角，当光标变为实心的十字形时，按住鼠标左键向下拖拉，将C2 单元格的有效性控制复制到C列其他单元格。图 3-2-1数据有效性对话框8、根据表格 3-2-1的数据输入“性别” 、“年龄”“所属部门”和“职工类别”等数据，其中“所属部门”和“职工类别”两列数据采用有效性控制输入，最后结果如图 3-2-2所示。图 3-2-2 2010年 4 月工资表任务 2计算“岗位工资” 、“奖金”和“应发合计”分析或说明：IF 函数可以根据设定的条件在相应单元格中自动填入数据。在月工资表中，岗位工资和奖金根据每位员工的职工类别不同而有所不同，具体情况如下表3-2-2 所示。本任务是先用IF 函数输入岗位工资和奖金，然后用公式“应发合计2010 年 4=基本工资+岗位工资+奖金”计算应发合计。表 3-2-2岗位工资和奖金表职工类别岗位工资奖金管理人员500200工人200300奖金销售额提成比例（销售额3 万元，提成比例为2，销售员3003 万元销售额10 万元，比例为4，销售额10 万元，比例为8）步骤：1、光标放在 H2 单元格，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。2、 在弹出的“函数参数”对话框中，输入各项参数，如图3-2-3 所示。图 3-2-3 if函数参数对话框之一3、用鼠标按住H2 单元格右下角的填充柄向下拖拉复制函数，输入其他人员的岗位工资。4、打开素材文件夹中的“销售额.doc ”文档，单击“编辑”“复制”菜单项，复制全部数据。5、光标放在工作表sheet2 的 A1 单元格 , 单击“编辑”“粘贴”菜单项，将 Word 文档表格中的数据转换成Excel 工作表，双击工作表标签“sheet2 ”，将其重命名为“销售表” 。6、光标放在“ 2010年 4月”工作表的I2 单元格，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。7、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-4 所示。8、用鼠标左键按住I2 单元格右下角的填充柄向下拖拉复制函数，输入其他人员的奖金，结果如图3-2-5 所示。9、光标放在I5单元格，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。10、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-6所示。在Excel 编辑栏内可得到如下 IF 函数的表达式：“ =IF(F5= 销售员 ,IF( 销售表 !E2=10, 销售表 !E2*8%, 销售表 !E2*4%)*10000) ”。11、 用鼠标左键按住I5 单元格的填充柄，向下拖拉到I9 为止，输入其他销售员的奖金。12、 单击 J2 单元格，输入公式： “ =G2+H2+I2 ”，单击“确定”按钮，计算应发合计。13、 用鼠标左键按住J2 单元格的填充柄， 向下拖拉输入其他人员的应发合计。图 3-2-4 if函数参数对话框之二图 3-2-5计算奖金图 3-2-6 if函数参数对话框之三任务 3计算“事假扣款” 、“病假扣款”和“扣款合计”分析或说明：该公司规定，病事假应扣款额和病事假天数有关，具体扣款金额如下表3-2-3 所示。本任务利用IF 函数计算病事假扣款，然后用公式“扣款合计事假扣款+病假扣款”计算扣款合计。表 3-2-3 病事假扣款表应扣款金额（元）病事假天数病假事假工人其他人员 15（应发合计 /22 ）事假天数200300 15应发合计 80400500步骤：1、光标放在 L2 单元格中，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。2、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-7所示。在编辑栏内得到IF函数的表达式为： “ =IF(K215,J2/22*K2,J2*80%)”。图 3-2-7 if函数参数对话框之四3、用鼠标左键按住L2 单元格右下角的填充柄向下拖拉，计算其他人员的事假扣款。4、光标放在 N2 单元格中，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。5、在弹出的“函数参数”对话框中，输入各项参数，所图3-2-8所示。在编辑栏内得到IF,200,300),IF(F2=函数的表达式为：工人 ,400,500)。=IF(M2=0,0,IF(M2=15,IF(F2=工 人图 3-2-8 if函数参数对话框之五6、用鼠标左键按住N2 单元格右下角的填充柄向下拖拉，计算其他人员的病假扣款。7、在 O2单元格中输入公式： “ =L2+N2”，单击“确定” 按钮，计算扣款合计。8、用鼠标左键按住O2单元格右下角的填充柄向下拖拉，计算其他人员的扣款合计。9、选择 L2： O12 数据区域，单击“格式”“单元格”菜单项，在弹出的对话框中单击“数字”选项卡。10、 在“数字”选项卡的“数值”分类中，将事假扣款、病假扣款、扣款合计的小数位数设置为2 位，如图3-2-9 所示。图 3-2-9各项扣款任务 4计算“应发工资”分析或说明：应发工资 =应发合计扣款合计养老金医疗金公积金。其中，养老金按应发合计的8扣除，医疗金按应发合计的2扣除，公积金按应发合计的10扣除。本任务先分别计算“养老金”、“医疗金”和“公积金”，然后再用上述公式计算应发工资。步骤：1、 光标放在 P2 单元格中，输入公式： “ =J2*8% ”，按回车键算出养老金。2、 用鼠标左键按住P2 单元格右下角的填充柄向下拖拉，计算其他人员的养老金。3、 光标放在 Q2 单元格中，输入公式： “ =J2*2% ”，按回车键算出医疗金。4、 用鼠标左键按住Q2 单元格右下角的填充柄向下拖拉，计算其他人员的医疗金。5、 光标放在 R2 单元格中，输入公式： “ =J2*10% ”，按回车键算出公积金。6、 用鼠标左键按住R2 单元格右下角的填充柄向下拖拉，计算其他人员的公积金。7、 光标放在 S2 单元格中，输入公式： “ =J2-O2-P2-Q2-R2 ”，按回车键算出应发工资。8、 用鼠标左键按住S2 单元格右下角的填充柄向下拖拉，计算其他人员的应发工资，如图 3-2-10所示。图 3-2-10计算应发工资任务 5计算“所得税”和“实发工资”分析或说明： 我国相关税法规定，工资、薪金所得应缴纳个人所得税。即每月取得工资收入后， 先减去个人承担的基本养老保险金、医疗保险金和失业保险金，以及按省级政府规定标准缴纳的住房公积金，再减去2000 元/ 月（个人所得税起征额为2000 元），为应纳税所得额，按5% 至 45% 的九级超额累进税率计算应缴纳个人所得税。计算公式为：“个人所得税 = 应纳税所得额税率 - 速算扣除数” 。表 3-2-4所得税税率表级数全月应纳税所得额税率 %速算扣除数（元）1不超过 500 元的502超过 500 元至 2000 元的部分10253超过 2000元至 5000 元的部分151254超过 5000元至 20000 元的部分203755超过 20000元至 40000 元的部分2513756超过 40000元至 60000 元的部分3033757超过 60000元至 80000 元的部分3563758超过 80000元至 100000 元的部分40103759超过 100000 元的部分4515375步骤：1、 光标放在T2 单元格，单击“插入”“函数”菜单项，在弹出的“粘贴函数”对话框中选择IF 函数，单击“确定”按钮。2、 在弹出的“函数参数”对话框中，输入各项参数，所图3-2-11所示。在编辑栏内得到IF函数的表达式为：“ =IF(S2-2000500,0,IF(S2-20002000,(S2-2000)*10%-25,IF(S2-20005000,(S2-2000)*15%-125,IF(S2-200020000,(S2-2000)*20%-375,复核 ) ”。3、 考虑到该企业员工的应发工资在扣除 2000 元之后，没有超过 20000 元的，所以 IF 函数只判断到 S2-200020000，当 S2-2000 20000 时，作复核处理。图 3-2-11 if函数参数对话框之六4、 用鼠标左键按住T2 单元格右下角的填充柄向下拖拉，计算其他人员的所得税。5、 光标放在 U2 单元格中，输入公式： “ =S2-T2”，按回车键计算实发工资。6、 用鼠标左键按住 U2 单元格右下角的填充柄向下拖拉，计算其他人员的实发工资，如图 3-2-12 所示。图 3-2-12所得税和实发工资项目总结本项目是制作一张员工工资表，包括工资表数据输入与统计分析。在数据输入时可以利用有效性控制和IF 函数，快速正确地输入指定数据。工资表数据输入完成后，可以运用数据透视表及数据透视图进行汇总及统计分析。拓展延伸1、 IF 函数的使用函数格式： if(logical_test,value_if_true,value_if_false)。其中：“ logical_test”表示设定的条件；“ value_if_true”表示当目标单元格与设定条件相符时返回的函数值；“ value_if_false”表示当目标单元格与设定条件不符时返回的函数值。IF 函数可以嵌套使用，最多可以嵌套7 层。以表3-2-5 为例，不同分数段要求输入不同的等级，则 IF 函数为：“ IF( 成绩 60，“不及格”， IF ( 成绩 70，“及格”， IF ( 成绩 80, “中”， IF ( 成绩 90，“良”，“优” ) ”。表 3-2-5 学生成绩表成绩等级成绩 60不及格60成绩 70及格70成绩 80中80成绩 90良90成绩优2、有效性控制Excel 强大的制表功能给我们的工作带来了方便，但是在数据录入过程中难免会出错，比如重复的身份证号码， 超出范围的无效数据等。 其实可以通过有效性控制来限定输入的内容，以避免错误数据的输入。 把既定的规律化的输入内容作为选项， 在有效性设置对话框中按“序列”进行设置。从序列的来源划分，启用这种“序列”的方法也是有多种的，下面简要描述设置方法。简单序列值可直接输入序列项目较少，并且固定不变的，可以直接在相应的单元格设置有效性的时候，在数据来源框中输入具体的选项。比如，希望在 A1 单元格只能输入“未开始”、“进行中”、“已完成” 、“已延期” 四个选项中的一个， 则在有效性设置对话框中的来源输入框中输入：“未开始 , 进行中 , 已完成 , 已延期” （选项值之间的逗号是英文输入状态下的逗号）。此外，通过有效性设置对话框的其他选项卡，可以设置“输入信息”和“出错警告”信息。 “输入信息” 是指设置有效性控制的单元格被选中时出现的输入提示性信息； “出错警告”则是在设置有效性控制的单元格中输入错误数据时出现的警告信息或说明。导入数据序列若在要设置有效性控制单元格的同一工作表中已存在数据序列， 则可通过导入的方式将数据序列导入。值得注意的是， 数据有效性控制只是在直接向单元格键入数据时才显示信息和阻止无效输入。当数据是以复制、填充、或公式计算的方式输入时，将不会出现有效性控制的提示。自我练习制作一张教师工资表，要求如下：1、项目设置：姓名、职称、基本工资、课时数、课时津贴基数、课时津贴、应发工资、所得税、实发工资。其中，姓名、职称、课时数的数据自拟。2、基本工资、课时津贴基数和职称有关，具体情况如下：职称基本工资课时津贴基数高级职称300030中级职称250025初级职称2000203、课时津贴课时数课时津贴基数。4、所得税缴税方法参照教材要求。5、基本工资、课时津贴、所得税的计算要求用IF 函数。