Excel财务管理实验教程课件-工资管理.ppt

1、好好学习好好学习 天天向上天天向上企业日常工资管理企业日常工资管理实验内容实验内容 工资管理是整个企业财务管理中不可或缺的组成部分，也是企业每个月必须核算的一项工作，本章主要介绍Excel在企业日常工资管理中的应用。利用Excel强大的数据处理能力，可以轻松地完成诸如汇总工资、分析工资组成及制作并打印工资条等工作。创建工资明细表 制作工资单 分类汇总工资 运用图表分析平均工资 建立工资数据透视表实验目的与要求实验目的与要求 掌握运用Excel进行工资管理的思路和方法，熟练运用分类汇总、插入图表、建立数据透视表对工作表数据进行管理与分析。10/24/202210/24/20221 1好好学习好好

2、学习 天天向上天天向上企业日常工资管理企业日常工资管理-实验准备实验准备 DAYS360函数主要功能主要功能：按每年360天返回两个日期相差的天数（每月30天）使用格式使用格式：DAYS360(start_date,end_date,method)参数说明参数说明：start_date和end_date是要计算天数差值的起止 日期。method是一个指定计算方法的逻辑值：FALSE或忽略，使用美国（NASD）方法；TRUE，使用欧洲方法。INT函数主要功能主要功能：将数值向下取整为最接近的整数使用格式使用格式：INT(number)参数说明参数说明：number为要取整的实数10/24/202

3、210/24/20222 2好好学习好好学习 天天向上天天向上企业日常工资管理企业日常工资管理-实验准备实验准备主要功能：主要功能：返回当前日期和时间所对应的序列号。如果在输 入函数前，单元格的格式为“常规”，则结果将设 为日期格式。使用格式：使用格式：NOW()函数说明：函数说明：Excel可将日期存储为可用于计算的序列号。默 认情况下，1900年1月1日的序列号是1而2008年 1月1日的序列号是39448，这是因为它距1900年 1月1日有39448天。序列号中小数点右边的数字表示时间，左边 的数字表示日期。例如，序列号.5 表示时间为中午12:00。函数 NOW只 有在重新计算工作表，

4、或执行含有此函数的宏时改变。它 并不会随时更新。10/24/202210/24/20223 3好好学习好好学习 天天向上天天向上企业日常工资管理企业日常工资管理-实验准备实验准备 COLUMN函数函数主要功能：主要功能：返回一引用的列号使用格式：使用格式：COLUMN（reference）参数说明：参数说明：reference,准备求取其列号的单元格或连续的单元格区域；如果忽略，则使用包含column函数的单元格。案例说明：案例说明：为了快速生成每位员工的工资单，本案例主要用了两个知识 点：单元格混合引用和用COLUMN函数返回数字。比如B6单 元格：=VLOOKUP($C4,工资明细表,CO

5、LUMN(I1),FALSE)，为了 在向右复制公式时查找值C4单元格不变动，故列号采取了绝 对引用，向下复制公式行号还可以变动。COLUMN函数主要 返回列号，所以向右复制公式时，列号随着单元格列的变化 而变化；向下复制公式时，由于单元格的列不变，而返回的 列号也不变，刚好满足公式要求。10/24/202210/24/20224 4好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表实验准备实验准备 要建立工资管理系统，首先要构建出工资明细表的基本框架，即表格中应包含哪些要素，这一点不同的企业会有不同的要求。工资的构成比较复杂，请根据企业实际情况设置，相对固定的有基本工资、岗位工资

6、、奖金、应发工资、实发工资等。实验要求实验要求 根据企业实际情况，熟练运用Excel制作基本工资表、岗 位工资表、奖金纪录表等，并建立每张数据表与工资明细 表之间的联系。熟练运用公式计算个人所得税 运用Excel自动复制功能快速生成工资单10/24/202210/24/20225 5好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step01:打开“工资管理系统”工作簿，将“sheet1”重新命名为“基本工资表”。输入表格内容，并设置表格线、字体等。10/24/202210/24/20226 6好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验

7、指导实验指导Step02:根据工龄工资、基本工资标准，填制基本工资表根据工龄工资、基本工资标准，填制基本工资表。F5单元格：=INT(DAYS360(E5,$B$3)/30)；G5单元格：=IF(F5=24,0,(F5-24)*5)；H5单元格：=IF(D5=“办公室”,3000,IF(D5=“客服部”,2800,IF(D5=“财务 部”,2500,IF(D5=“人事部”,2500,2000)；I5单元格：=G5+H5；将这些公式复制到相应单元格即可，结果如下页图所示。10/24/202210/24/20227 7好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导

8、Step02:根据工龄工资、基本工资标准，填制基本工资表根据工龄工资、基本工资标准，填制基本工资表。10/24/202210/24/20228 8好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step03:Step03:制作岗位工资表。制作岗位工资表。F5单元格：=IF(E5=“经理”,2000,IF(E5=“主任”,1500,1000)，将公式向下复制到相应单元格。10/24/202210/24/20229 9好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step04:Step04:制作奖金记录表。制作奖金记录表。H13单元

9、格：=IF(E1320000,E13=IF(E13100000,E131%,IF(E13100000,E13*5%,E135%,E13*3%)3%)将该公式复制到H14、H20、H21、H24单元格。方法如下：右键单击右键单击H13H13单元格，选择单元格，选择“复制复制”命令；命令；然后选中然后选中H14H14、H20H20、H21H21、H24H24单元格，右键单击单元格，右键单击该单元格，选择该单元格，选择“粘贴粘贴”命令即可，或者打开命令即可，或者打开“选择性粘贴选择性粘贴”对话框，选择对话框，选择“公式公式”复选框，复选框，确定即可。确定即可。10/24/202210/24/2022

10、1010好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step05:Step05:制作补贴记录表。制作补贴记录表。F5单元格：=IF(E5=“经理”,300,IF(E5=“主任”,250,200)，将公式往下复制到相应单元格。10/24/202210/24/20221111好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step06:Step06:制作员工考勤表。制作员工考勤表。H6单元格：=E6*20+F6*200+G6*70I6单元格：=IF(H6=0,200,0)10/24/202210/24/20221212好好学习好好

11、学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step07:Step07:制作工资明细表并计算实发工资金额。制作工资明细表并计算实发工资金额。计算第一位员工的实发工资金额，计算第一位员工的实发工资金额，G4G4单元格：单元格：=基本工资表基本工资表!I5!I5，H4H4单元格：单元格：=岗位工资表岗位工资表!F5!F5，I4I4单元格：单元格：=奖金纪录表奖金纪录表!J6!J6，J4J4单元格：单元格：=补贴记录表补贴记录表!F5!F5，K4K4单元格：单元格：=G4+H4+I4+J4=G4+H4+I4+J4，L4L4单元格：单元格：=K4=K4*10%10%，M4M4单

12、元格：单元格：=IF(K4-2000=0,0,IF(K4-2000=500,(K4-2000)*0.05,IF(K4-2000=2000,(K4-2000)*0.1-25,IF(K4-2000=5000,(K4-2000)*0.15-125,(K4-2000)*0.2-375)，N4N4单元格：单元格：=考勤表考勤表!H6!H6，O4O4单元格：单元格：=考勤表考勤表!I6!I6，P4P4单元格：单元格：=K4-L4-M4-N4+O4=K4-L4-M4-N4+O4。生成工资明细表：生成工资明细表：选中选中G4:P4G4:P4单元格区域，将鼠标定位单元格区域，将鼠标定位到该单元格区域右下角，出现

13、黑色到该单元格区域右下角，出现黑色十字时，按住鼠标左键向下拖动鼠十字时，按住鼠标左键向下拖动鼠标至最后一位员工，释放鼠标即可标至最后一位员工，释放鼠标即可一次性计算出所有员工的工资金额。一次性计算出所有员工的工资金额。10/24/202210/24/20221313好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导采用速算扣除数法计算超额累进税率的个人所得税，计算公式为：应纳税额应纳税额=应纳税所得额应纳税所得额适用税率适用税率-速算扣除数速算扣除数 10/24/202210/24/20221414好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实

14、验指导实验指导Step08:Step08:制作工资单。制作工资单。为“工资明细表”定义名称，即将“工资明细表！B4:P23”单元格区域定义为“工资明细表”。根据单位实际，设计工资单表格。10/24/202210/24/20221515好好学习好好学习 天天向上天天向上创建工资明细表创建工资明细表-实验指导实验指导Step08:Step08:制作工资单。制作工资单。填制第一位员工的工资单填制第一位员工的工资单B4B4单元格：单元格：=NOW()=NOW()；C4C4单元格：单元格：JZ1001JZ1001；D4D4单元格：单元格：=VLOOKUP(C4,=VLOOKUP(C4,工资明细表工资明细

15、表,2,FALSE),2,FALSE)；E4E4单元格：单元格：=VLOOKUP($C4,=VLOOKUP($C4,工资明细表工资明细表,COLUMN(D1),FALSE),COLUMN(D1),FALSE)；将该公式向右复制至将该公式向右复制至I4I4单元格。单元格。B6B6单元格：单元格：=VLOOKUP($C4,=VLOOKUP($C4,工资明细表工资明细表,COLUMN(I1),FALSE),COLUMN(I1),FALSE)；将该公式复制至将该公式复制至H6H6单元格。单元格。快速生成每位员工的工资单。快速生成每位员工的工资单。选中选中B2:I6B2:I6单元格区域，将鼠标定单元格区

16、域，将鼠标定位到该单元格区域右下角，出现黑位到该单元格区域右下角，出现黑色十字时，按住鼠标左键向下拖动色十字时，按住鼠标左键向下拖动鼠标至最后一位员工，释放鼠标即鼠标至最后一位员工，释放鼠标即可一次性生成所有员工的工资单。可一次性生成所有员工的工资单。10/24/202210/24/20221616好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析实验准备实验准备制作完成的工资明细表实验要求实验要求 掌握排序、分类汇总的方法 熟练运用图表、数据透视表进行数据分析10/24/202210/24/20221717好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与

17、分析-分类汇总分类汇总 如果工作表中的数据太多，某些信息我们很难直接看出来，这时就需要对数据进行分类与汇总，将相同类型的数据统计出来。分类汇总具有较为强大的功能，根据需要分析数据的实际情况，可以汇总得到各类不同的结果，分类汇总结果采用分级显示的方式来显示工作表内的数据，主要包括求和、求平均值、最大最小值、计数等。在分类汇总之在分类汇总之前，一定要按分类字段进行排序前，一定要按分类字段进行排序。排序是按照字母、颜色的升序或降序以及数值顺序来组织数据，要进行排序的数据字段称之为关键字。Step01:打开“工资管理系统”工作簿，插入一张工作表并将其重新命名为“分类汇总工资”，将制作完成的工资明细表复

18、制过来。10/24/202210/24/20221818好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总Step02:Step02:分类汇总各部门平均工资。分类汇总各部门平均工资。按按“所属部门所属部门”关关键字进行排序：选中工键字进行排序：选中工资明细表中任一单元格，资明细表中任一单元格，选择选择“数据数据排序排序”按按钮，打开钮，打开“排序排序”对话对话框。选择主要关键字：框。选择主要关键字：所属部门；次要关键字：所属部门；次要关键字：职位，都按升序排列。职位，都按升序排列。提示：提示：不同类型关键字的排序规则如下不同类型关键字的排序规则如下数值：按

19、数值大小；数值：按数值大小；字母：按字母先后顺序；字母：按字母先后顺序；日期：按日期先后顺序；日期：按日期先后顺序；汉字：按汉语拼音的顺序或按笔画的顺序；汉字：按汉语拼音的顺序或按笔画的顺序；逻辑值：升序时逻辑值：升序时FALSEFALSE排在排在TRUETRUE前面，降序时则相前面，降序时则相 反；空格，总是排在最后。反；空格，总是排在最后。10/24/202210/24/20221919好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总Step02:Step02:分类汇总各部门平均工资。分类汇总各部门平均工资。分类汇总各部门平均工资：选中分类汇总各部门

20、平均工资：选中排序后的工资明细表中的任一单元格，排序后的工资明细表中的任一单元格，选择选择“数据数据分类汇总分类汇总”命令，打开命令，打开“分 类 汇 总分 类 汇 总”对 话 框。对 话 框。分类字段：所属部门；分类字段：所属部门；汇总方式：平均值；汇总方式：平均值；汇总项：实发工资；汇总项：实发工资；其它默认其它默认，10/24/202210/24/20222020好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总 分类汇总表有三页表，分别为：总计平均值（所有员工平均工资），所属部门平均值及其汇总表。分类汇总表有三页表，分别为：总计平均值（所有员工平均

21、工资），所属部门平均值及其汇总表。10/24/202210/24/20222121好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-图表分析图表分析 运用Excel图表分析表格数据，可以更直观、更形象地展示表格中的数据记录、分析表格中数据的趋势和状态。Excel提供了多种类型的图标，用户可以根据需要轻松地创建具有专业外观的图表。Step01:打开“分类汇总工资”数据表，选择汇总结果的第2页表，按着【Ctrl】键，选中“所属部门”和“实发工资”两列数据（总计平均值不选）。10/24/202210/24/20222222好好学习好好学习 天天向上天天向上工资数据管理与分析工资

22、数据管理与分析-图表分析图表分析Step02:Step02:插入柱形图。插入柱形图。选择选择“插入插入图图表表标准类型标准类型”命令，打开命令，打开“图图表选项表选项”对话框。对话框。图表类型选择：图表类型选择：柱形图；子图表柱形图；子图表类型选择：三维类型选择：三维簇 状 柱 形 图。簇 状 柱 形 图。10/24/202210/24/20222323好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-图表分析图表分析常用图标准类型介绍常用图标准类型介绍柱形图：柱形图包括簇状柱形图、堆积柱形图、三维簇状图等，常用 于显示一段时间内数据的变化或各项目之间的比较情况。折线图：

23、折线图包括折线图、堆积折线图、数据点折线图、三维折线 图等，非常适合于显示在相等时间间隔下数据的变化趋势。饼图：包括饼图、三维饼图、分离型三维饼图、复合饼图等，常用于 显示每一个组成部分数值相对于总体总数值的大小或所占百分 比，分离型饼图还可以强调每个组成部分单独的值。XY散点图：包括散点图、平滑散点图、折现散点图等，用于显示若干 数据系列中各数值之间的关系，或者将两组数据绘制为XY 坐标的一个系列。股价图：包括盘高-盘低-收盘图、开盘-高盘-低盘-收盘图、成交量-高 盘-低盘-收盘图、成交量-开盘-高盘-低盘-收盘图，制图所需 的各项数据系列必须按次序排列。股价图常用于显示股价的 波动情况，

24、也可用于科学数据的显示，如显示每天温度的波 动情况等。10/24/202210/24/20222424好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-图表分析图表分析Step02:Step02:插入柱形图。插入柱形图。图表标题：平均实发工资图表标题：平均实发工资图例：取消显示图例复选框图例：取消显示图例复选框数据表：选中显示数据表复选框数据表：选中显示数据表复选框10/24/202210/24/20222525好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-数据透视表数据透视表 数据透视表是汇总数据的交互式表格，是对工作表数据的重新组合，从不同的角度考察数据，使用数据表达的信息更加清楚明了，常用于制作各种分析报表和统计报表。Step01:打开“工资管理系统”工作簿，插入一张工作表并将其重新命名为“工资数据透视表”，将工资明细表复制过来。10/24/202210/24/20222626好好学习好好学习 天天向上天天向上工资数据管理与分析工资数据管理与分析-数据透视表数据透视表Step02:Step02:制作工资数据透视表。制作工资数据透视表。选择选择“数据数据数据透视表和数据透视图数据透视表和数据透视图”命令，打开其导向图命令，打开其导向图对话框。对话框。10/24/202210/24/20222727