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

好好学习好好学习 天天向天天向上上企业日常工资管理企业日常工资管理实验内容实验内容 工资管理是整个企业财务管理中不可或缺的组成部分，也是企业每个月必须核算的一项工作，本章主要介绍Excel在企业日常工资管理中的应用。利用Excel强大的数据处理能力，可以轻松地完成诸如汇总工资、分析工资组成及制作并打印工资条等工作。创建工资明细表 制作工资单 分类汇总工资 运用图表分析平均工资 建立工资数据透视表实验目的与要求实验目的与要求 掌握运用Excel进行工资管理的思路和方法，熟练运用分类汇总、插入图表、建立数据透视表对工作表数据进行管理与分析。6/21/20246/21/20241 1企业日常工资管理实验内容8/10/20231好好学习好好学习 天天向天天向上上企业日常工资管理企业日常工资管理-实验准备实验准备 DAYS360函数主要功能主要功能：按每年360天返回两个日期相差的天数（每月30天）使用格式使用格式：DAYS360(start_date,end_date,method)参数说明参数说明：start_date和end_date是要计算天数差值的起止 日期。method是一个指定计算方法的逻辑值：FALSE或忽略，使用美国（NASD）方法；TRUE，使用欧洲方法。INT函数主要功能主要功能：将数值向下取整为最接近的整数使用格式使用格式：INT(number)参数说明参数说明：number为要取整的实数6/21/20246/21/20242 2企业日常工资管理-实验准备 DAYS360函数8/10/2好好学习好好学习 天天向天天向上上企业日常工资管理企业日常工资管理-实验准备实验准备 NOW NOW函数函数函数函数主要功能：主要功能：返回当前日期和时间所对应的序列号。如果在输 入函数前，单元格的格式为“常规”，则结果将设 为日期格式。使用格式：使用格式：NOW()函数说明：函数说明：Excel可将日期存储为可用于计算的序列号。默 认情况下，1900年1月1日的序列号是1而2008年 1月1日的序列号是39448，这是因为它距1900年 1月1日有39448天。序列号中小数点右边的数字表示时间，左边 的数字表示日期。例如，序列号.5 表示时间为中午12:00。函数 NOW只 有在重新计算工作表，或执行含有此函数的宏时改变。它 并不会随时更新。6/21/20246/21/20243 3企业日常工资管理-实验准备 NOW函数8/10/20233好好学习好好学习 天天向天天向上上企业日常工资管理企业日常工资管理-实验准备实验准备 COLUMN函数函数主要功能：主要功能：返回一引用的列号使用格式：使用格式：COLUMN（reference）参数说明：参数说明：reference,准备求取其列号的单元格或连续的单元格区域；如果忽略，则使用包含column函数的单元格。案案例例说说明明：为了快速生成每位员工的工资单，本案例主要用了两个知识 点：单元格混合引用和用COLUMN函数返回数字。比如B6单 元格：=VLOOKUP($C4,工资明细表,COLUMN(I1),FALSE)，为了 在向右复制公式时查找值C4单元格不变动，故列号采取了绝 对引用，向下复制公式行号还可以变动。COLUMN函数主要 返回列号，所以向右复制公式时，列号随着单元格列的变化 而变化；向下复制公式时，由于单元格的列不变，而返回的 列号也不变，刚好满足公式要求。6/21/20246/21/20244 4企业日常工资管理-实验准备 COLUMN函数8/10/20好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表实验准备实验准备 要建立工资管理系统，首先要构建出工资明细表的基本框架，即表格中应包含哪些要素，这一点不同的企业会有不同的要求。工资的构成比较复杂，请根据企业实际情况设置，相对固定的有基本工资、岗位工资、奖金、应发工资、实发工资等。实验要求实验要求 根据企业实际情况，熟练运用Excel制作基本工资表、岗 位工资表、奖金纪录表等，并建立每张数据表与工资明细 表之间的联系。熟练运用公式计算个人所得税 运用Excel自动复制功能快速生成工资单6/21/20246/21/20245 5创建工资明细表8/10/20235好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step01:打开“工资管理系统”工作簿，将“sheet1”重新命名为“基本工资表”。输入表格内容，并设置表格线、字体等。6/21/20246/21/20246 6创建工资明细表-实验指导Step01:打开“工资管理系统”工好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导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；将这些公式复制到相应单元格即可，结果如下页图所示。6/21/20246/21/20247 7创建工资明细表-实验指导Step02:根据工龄工资、基本工资好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step02:根据工龄工资、基本工资标准，填制基本工资表根据工龄工资、基本工资标准，填制基本工资表。6/21/20246/21/20248 8创建工资明细表-实验指导Step02:根据工龄工资、基本工资好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step03:Step03:制作岗位工资表。制作岗位工资表。F5单元格：=IF(E5=“经理”,2000,IF(E5=“主任”,1500,1000)，将公式向下复制到相应单元格。6/21/20246/21/20249 9创建工资明细表-实验指导Step03:制作岗位工资表。8/1好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step04:Step04:制作奖金记录表。制作奖金记录表。H13单元格：=IF(E13100000,E13*5%,E13*3%)=IF(E13100000,E13*5%,E13*3%)将该公式复制到H14、H20、H21、H24单元格。方法如下：右键单击右键单击H13H13单元格，选择单元格，选择“复制复制”命令；命令；然后选中然后选中H14H14、H20H20、H21H21、H24H24单元格，右键单击单元格，右键单击该单元格，选择该单元格，选择“粘贴粘贴”命令即可，或者打开命令即可，或者打开“选择性粘贴选择性粘贴”对话框，选择对话框，选择“公式公式”复选框，确复选框，确定即可。定即可。6/21/20246/21/20241010创建工资明细表-实验指导Step04:制作奖金记录表。H13好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step05:Step05:制作补贴记录表。制作补贴记录表。F5单元格：=IF(E5=“经理”,300,IF(E5=“主任”,250,200)，将公式往下复制到相应单元格。6/21/20246/21/20241111创建工资明细表-实验指导Step05:制作补贴记录表。8/1好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step06:Step06:制作员工考勤表。制作员工考勤表。H6单元格：=E6*20+F6*200+G6*70I6单元格：=IF(H6=0,200,0)6/21/20246/21/20241212创建工资明细表-实验指导Step06:制作员工考勤表。H6单好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导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*10%=K4*10%，M4M4单元格：单元格：=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。生成工资明细表：生成工资明细表：选选中中G G4 4:P P4 4单单元元格格区区域域，将将鼠鼠标标定定位位到到该该单单元元格格区区域域右右下下角角，出出现现黑黑色色十十字字时时，按按住住鼠鼠标标左左键键向向下下拖拖动动鼠鼠标标至至最最后后一一位位员员工工，释释放放鼠鼠标标即即可可一一次次性性计计 算算 出出 所所 有有 员员 工工 的的 工工 资资 金金 额额。6/21/20246/21/20241313创建工资明细表-实验指导Step07:制作工资明细表并计算实好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导采用速算扣除数法计算超额累进税率的个人所得税，计算公式为：应纳税额应纳税额=应纳税所得额应纳税所得额适用税率适用税率-速算扣除数速算扣除数 6/21/20246/21/20241414创建工资明细表-实验指导采用速算扣除数法计算超额累进税率的个好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step08:Step08:制作工资单。制作工资单。为“工资明细表”定义名称，即将“工资明细表！B4:P23”单元格区域定义为“工资明细表”。根据单位实际，设计工资单表格。6/21/20246/21/20241515创建工资明细表-实验指导Step08:制作工资单。8/10好好学习好好学习 天天向天天向上上创建工资明细表创建工资明细表-实验指导实验指导Step08:Step08:制作工资单。制作工资单。填制第一位员工的工资单填制第一位员工的工资单B4B4单元格：单元格：=NOW()=NOW()；C4C4单元格：单元格：JZ1001JZ1001；D4D4单元格：单元格：=VLOOKUP(C4,=VLOOKUP(C4,工资明细表工资明细表,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单元格。单元格。快快速速生生成成每每位位员员工工的的工工资资单单。选选中中B B2 2:I I6 6单单元元格格区区域域，将将鼠鼠标标定定位位到到该该单单元元格格区区域域右右下下角角，出出现现黑黑色色十十字字时时，按按住住鼠鼠标标左左键键向向下下拖拖动动鼠鼠标标至至最最后后一一位位员员工工，释释放放鼠鼠标标即即可可一一次次性性生生成成所所有有员员工工的的工工资资单单。6/21/20246/21/20241616创建工资明细表-实验指导Step08:制作工资单。填制第一位好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析实验准备实验准备制作完成的工资明细表实验要求实验要求 掌握排序、分类汇总的方法 熟练运用图表、数据透视表进行数据分析6/21/20246/21/20241717工资数据管理与分析8/10/202317好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总 如果工作表中的数据太多，某些信息我们很难直接看出来，这时就需要对数据进行分类与汇总，将相同类型的数据统计出来。分类汇总具有较为强大的功能，根据需要分析数据的实际情况，可以汇总得到各类不同的结果，分类汇总结果采用分级显示的方式来显示工作表内的数据，主要包括求和、求平均值、最大最小值、计数等。在在分分类类汇汇总总之之前前，一一定定要要按按分分类类字字段段进进行行排排序序。排序是按照字母、颜色的升序或降序以及数值顺序来组织数据，要进行排序的数据字段称之为关键字。Step01:打开“工资管理系统”工作簿，插入一张工作表并将其重新命名为“分类汇总工资”，将制作完成的工资明细表复制过来。6/21/20246/21/20241818工资数据管理与分析-分类汇总 8/10/2023好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总Step02:Step02:分类汇总各部门平均工资。分类汇总各部门平均工资。按按“所所属属部部门门”关关键键字字进进行行排排序序：选选中中工工资资明明细细表表中中任任一一单单元元格格，选选择择“数数据据排排序序”按按钮钮，打打开开“排排序序”对对话话框框。选选择择主主要要关关键键字字：所所属属部部门门；次次要要关关键键字字：职职位位，都都按按升升序序排排列列。提示：提示：不同类型关键字的排序规则如下不同类型关键字的排序规则如下数值：按数值大小；数值：按数值大小；字母：按字母先后顺序；字母：按字母先后顺序；日期：按日期先后顺序；日期：按日期先后顺序；汉字：按汉语拼音的顺序或按笔画的顺序；汉字：按汉语拼音的顺序或按笔画的顺序；逻辑值：升序时逻辑值：升序时FALSEFALSE排在排在TRUETRUE前面，降序时则相前面，降序时则相 反；空格，总是排在最后。反；空格，总是排在最后。6/21/20246/21/20241919工资数据管理与分析-分类汇总Step02:分类汇总各部门平均好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总Step02:Step02:分类汇总各部门平均工资。分类汇总各部门平均工资。分分类类汇汇总总各各部部门门平平均均工工资资：选选中中排排序序后后的的工工资资明明细细表表中中的的任任一一单单元元格格，选选择择“数数据据分分类类汇汇总总”命命令令，打打开开“分分类类汇汇总总”对对话话框框。分类字段：所属部门；分类字段：所属部门；汇总方式：平均值；汇总方式：平均值；汇总项：实发工资；汇总项：实发工资；其它默认其它默认，6/21/20246/21/20242020工资数据管理与分析-分类汇总Step02:分类汇总各部门平均好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-分类汇总分类汇总 分类汇总表有三页表，分别为：总计平均值（所有员工平均工资），所属部门平均值及其汇总表。分类汇总表有三页表，分别为：总计平均值（所有员工平均工资），所属部门平均值及其汇总表。6/21/20246/21/20242121工资数据管理与分析-分类汇总 分类汇总表有三页表，分好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-图表分析图表分析 运用Excel图表分析表格数据，可以更直观、更形象地展示表格中的数据记录、分析表格中数据的趋势和状态。Excel提供了多种类型的图标，用户可以根据需要轻松地创建具有专业外观的图表。Step01:打开“分类汇总工资”数据表，选择汇总结果的第2页表，按着【Ctrl】键，选中“所属部门”和“实发工资”两列数据（总计平均值不选）。6/21/20246/21/20242222工资数据管理与分析-图表分析 运用Excel图表好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-图表分析图表分析Step02:Step02:插入柱形图。插入柱形图。选选择择“插插入入图图表表标标准准类类型型”命命令令，打打开开“图图表表选选项项”对对话话框框。图图表表类类型型选选择择：柱柱形形图图；子子图图表表类类型型选选择择：三三维维簇簇 状状 柱柱 形形 图图。6/21/20246/21/20242323工资数据管理与分析-图表分析Step02:插入柱形图。选择好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-图表分析图表分析常用图标准类型介绍常用图标准类型介绍柱形图：柱形图包括簇状柱形图、堆积柱形图、三维簇状图等，常用 于显示一段时间内数据的变化或各项目之间的比较情况。折线图：折线图包括折线图、堆积折线图、数据点折线图、三维折线 图等，非常适合于显示在相等时间间隔下数据的变化趋势。饼图：包括饼图、三维饼图、分离型三维饼图、复合饼图等，常用于 显示每一个组成部分数值相对于总体总数值的大小或所占百分 比，分离型饼图还可以强调每个组成部分单独的值。XY散点图：包括散点图、平滑散点图、折现散点图等，用于显示若干 数据系列中各数值之间的关系，或者将两组数据绘制为XY 坐标的一个系列。股价图：包括盘高-盘低-收盘图、开盘-高盘-低盘-收盘图、成交量-高 盘-低盘-收盘图、成交量-开盘-高盘-低盘-收盘图，制图所需 的各项数据系列必须按次序排列。股价图常用于显示股价的 波动情况，也可用于科学数据的显示，如显示每天温度的波 动情况等。6/21/20246/21/20242424工资数据管理与分析-图表分析返回8/10/202324好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-图表分析图表分析Step02:Step02:插入柱形图。插入柱形图。图表标题：平均实发工资图表标题：平均实发工资图例：取消显示图例复选框图例：取消显示图例复选框数据表：选中显示数据表复选框数据表：选中显示数据表复选框6/21/20246/21/20242525工资数据管理与分析-图表分析Step02:插入柱形图。图表好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-数据透视表数据透视表 数据透视表是汇总数据的交互式表格，是对工作表数据的重新组合，从不同的角度考察数据，使用数据表达的信息更加清楚明了，常用于制作各种分析报表和统计报表。Step01:打开“工资管理系统”工作簿，插入一张工作表并将其重新命名为“工资数据透视表”，将工资明细表复制过来。6/21/20246/21/20242626工资数据管理与分析-数据透视表 8/10/2023好好学习好好学习 天天向天天向上上工资数据管理与分析工资数据管理与分析-数据透视表数据透视表Step02:Step02:制作工资数据透视表。制作工资数据透视表。选择选择“数据数据数据透视表和数据透视图数据透视表和数据透视图”命令，打开其导向图命令，打开其导向图对话框。对话框。6/21/20246/21/20242727工资数据管理与分析-数据透视表Step02:制作工资数据透视