Excel在工资管理中的应用.docx

Excel在工资管理中的应用一、 任务一 输入工资数据1、 新建Excel工作簿，命名为“工资核算”，打开“工资核算表”工作薄，将sheet1命名为“基本工资信息表”。在此表中输入如下图内容：2、 将sheet2命名为“工资核算单”,建立如下工资项目：职工代码、职工姓名、性别、年龄、部门、工作岗位、职工类别、事假天数、病假天数、基本工资、岗位工资、职务津贴、奖金、事假扣款、病假扣款、应发工资、住房公积金、个人所得税和补发工资，如图：3、 设置数据有效性：（1） “性别”列数据有性的设置（2）“部门”数据有效性的设置（3）“工作岗位”数据有效性的设置：（4）职工类别数据有效性的设置：4、 输入数据：根据表41中所给数据(课本76页)，完成记录后的效果如下图：5、添加批注：操作方法：选择单元格单击右键，弹出下拉菜单单击插入批注命令，在文本框中输入所需要信息即可。如图所示二、 任务二 设置工资项目计算工资项目：1、 使用IF（）函数岗位工资：2、 职务津贴：职位津贴是基本工资与岗位工资之和的10%：因此：L4=(J4+K4)*0.13、 使用IF（）函数奖金：奖金根据职工所在部门的不同而有所差别，具体规定如下表：第一种方法：=IF(E4=企划部,1000,IF(OR(E4=组装部,E4=机修部),900,IF(E4=销售部,600,800)第二种方法：=IF(E4=企划部,1000,IF(OR(E4=财务部,E4=后勤部,E4=供应部),800,IF(OR(E4=组装部,E4=机修部),900,600)4、 事假扣款：ROUND()四舍五入函数；N4=ROUND(J4/22*H4,2)5、 病假扣款：根据公司的规定，请一天病假扣款50元。因此：O4 =I4*506、 应发工资：应发工资为基本工资、岗位工资、职务津贴与奖金和扣款和病假扣款。P4 =SUM(J4:M4)-N4-O47、 住房公积金：根据公司规定，住房公积金为应发工资的15%，因此：O4round(P4*15%,2)8、 个人所得税：所得税根据应发工资的数额而确定R4=IF(P4-2000=0,0,IF(0P4-2000=500,(P4-2000)*0.05,IF(P4-2000=2000,(P4-2000)*0.1-25,IF(P4-2000=5000,(P4-2000)*0.15-125,(P4-2000)*0.2-375)9、 实发工资：S4P4Q4R4三、 任务三：工资数据的查询与统计分析1、 在“工资核算表”中利用筛选功能进行工资数据的查询（1） 以“职工姓名”为依据进行进行查询。例如：查询职工姓名为“李丽”的职工的工资情况：操作方法：a、打开“工资核算”工作表，将光标放在工作表任务的单元格中； b、单击“数据”菜单筛选按钮，进入筛选姿态，如下图C、单击“职工姓名”列按钮，选择“文本筛选”：单击“等于”，如下图：查询结果如下图：（2） 以“部门”为依据进行查询,查询“机修部”所有职工的工资情况，结果如下图：（3） 退出筛选状态，单击“筛选”按钮，恢复数据。2、 将sheet3工作表命名为“工资总额汇总表”依据部门和职工类别的统计分析（1） 计算每一部门每一职工类别“应发工资”的汇总数：如下图：（2） 创建透视图：操作方法：a、选择“工资总额汇总表”，插入柱形图形堆积柱形图，结果如下图：（）新建sheet4工作表，命名为“工资平均数汇总表”，计算每一部门每一职工类别“应发工资”的平均数汇总表，如下图：（）新建sheet工作表，命名为“百分比表”，百比表的制作：a、制作部门“应发工资”所占的百分比表操作方法：插入如下的透视表，值汇总方式为“求和”，值显示方式为“全部汇总百分比”，结果下图：b、各职工类别应发工资占应发工资总和的百分比设置方法：值汇总方式为“求和”，值显示方式为“行汇总的百分比”，结果如下图：C、各部门应发工资占应发工资总和的百分比 设置方法：值汇总方式为“求和”，值显示方式为“列汇总的百分比”，结果如下图：（）新建sheet6工作表，命名为“工资总额统计表”，利用“工资核算”工作表制作“工资总额统计表”透视表，字段设置如下图结果如下图：任务四 编制工资费用分配表操作步骤：一、 新建sheet7工作表，命名为“工资费用分配表”。 1、 打开“工资费用分配表”，选择A1单元格，输入“工资费用分配表”；2、 选择A1：H1单元格区域，设置“合并及居中”。3、 根据下图设置工资费用分配表相关栏目：4、 按照（课本93页）512题的操作步骤设置表格，结果如下：3、 编制工资费用分配表注释：GETPIVOTDATA（）函数是从数据透表中查索汇总数据。操作方法：（1）选择D5单元格，输入“=”，点击“工资总额汇总表”，选择“企划部”对应的合计金额I9单元格，回车确认之后，“工资总额汇总表”I9单元格的数据就会引用过来。同样，继续完成其他数据的输入（2） 定义“职工福利费”计算公式在E5单元格输入“D5*14%，按回车键确认。选择E5单元格，用填充柄把公式复制到E12单元格中。（3） 定义“合计”计算公式。选择D5：D12单元格区域，单元“自动求和”图表按钮，完成工资总额求和。同样地，完成职工福