1、EXCEL第一章在人力资源与行政管理中的应用CONTENTS01收发文件登记表02办公用品采购记录表03办公用品领用登记表04办公室值班记录表目录05文件、资料借阅管理表06车辆使用与管理表新建工作簿的方法01在Excel工作窗口中创建在系统中创建工作簿文件工作表的移动和复制页面设置与打印预览02在页眉中添加公司logo 纸张大小和页边距单击快速访问工具栏的保存按钮。三种方法1LGO1.1.2 保存工作簿2单击功能区的【文件】【保存】按钮或【另存为】按钮。按组合键，或是按组合键。3对于新建工作簿的首次保存，“保存”和“另存为”命令的作用完全相同。对于之前已经保存过的现有工作簿，再次执行保存操作
2、时，【保存】命令直接将编辑修改后的内容保存到当前工作簿中，工作簿的文件名和保存路径不会有任何变化。【另存为】命令则会打开【另存为】对话框，允许用户对文件名和保存路径重新进行设置，得到当前工作簿的副本。LGO“保存”和“另存为”的区别页眉设置对话框LGO在页眉中添加公司logo 在页眉中插入图片自定义快速访问工具栏LGO1.1.5 打印预览打印预览在部分公司，办公用品的采购、发放、管理工作要求由办公室统一负责，采购部门与办公室分别配备人员对办公用品到货明细进行记录。LGO1.2 办公用品采购记录表企业为控制办公消耗成本，规范办公用品的发放、领用，会要求对办公用品的领用进行登记管理。LGO1.3
3、办公用品领用登记表移动或复制工作表LGO工作表的复制和移动在选项卡中选择移动或复制工作表命令大多数公司都会执行办公室人员值班制度，用以维护日常工作秩序，及时反馈信息和处置突发性事件。LGO1.4 办公室值班记录表为了保证重要文件资料的完整性及保密性，很多公司会对阅读和使用涉密文件、资料进行登记管理。LGO1.5 文件、资料借阅管理表设置单元格边框完成后的效果车辆管理的统一化和制度化，是真实反应公司车辆实际情况的基础，以下介绍车辆使用与管理表的制作。LGO1.6 车辆使用与管理表Excel允许用户根据自己的需要和使用习惯，对选项卡和命令组进行显示、隐藏以及次序的调整。LGO可定制的自定义选项卡显
4、示选项卡添加自定义选项卡和命令快速访问工具栏中的命令按钮不会因为功能选项卡的切换而隐藏，因此使用会更加方便。LGO可定制的快速访问工具栏自定义快速访问工具栏在快速访问工具栏上添加命令EXCEL第二章在人力资源与行政管理中的应用CONTENTS01数据源表格和报表表格02结构合理的员工信息表03制作岗位变动表04人事信息数据查询表目录1CONTENTS05制作员工生日信息表06劳动合同到期前提醒07对重要的文件进行保护08自动跳转的工作表目录目录2合并单元格手工添加汇总行1 制作基础数据表注意事项使用空格对齐日期格式不规范第一行写入文件标题防止工号重复输入输入身份证号码2 结构合理的员工信息表身
5、份证号中提取性别提取出生日期计算员工年龄和转正时间美化员工信息表防止工号的重复输入设置数据有效性员工工号必须具有唯一性，在员工信息表中可以根据工号区分重名员工。因此在输入数据时要限制重复数据的录入。自定义错误提示公式的概念和组成要素EXCEL公式是指以等号“=”为引导，通过运算符、函数、参数等按照一定的顺序组合进行数据运算处理的等式。（1）、运算符：是指一些符号，如加（+）、减（-）、乘（*）、除（/）等。（2）、单元格引用：可以是当前工作表中的单元格，也可以是当前工作簿其他工作表中的单元格或是其他工作簿中的单元格。（3）、值或字符串：比如数字8或字符“A”。（4）、工作表函数和参数：例如用于
6、求和的SUM函数以及它的参数。（5）、括号：控制着公式中各表达式的计算顺序。相对引用、绝对引用和混合引用在公式中，如果A1单元格公式为“=B1”，那么A1就是B1的引用单元格，B1就是A1的从属单元格。从属单元格与引用单元格之间的位置关系称为单元格引用的相对性，可分为3种不同的引用方式，即相对引用、绝对引用和混合引用，不同引用方式用美元符号“$”进行区别。当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变，称为相对引用。当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变，称为绝对引用。当复制公式到其他单元格时，Excel仅保持所引用单元格的行或列方
7、向之一的绝对位置不变，而另一个方向位置发生变化，这种引用方式称为混合引用。在编辑栏内选中公式中的单元格地址部分，然后依次按键，可以在不同引用方式之间进行切换。单元格的引用类型及特点引用类型A1样式特点绝对引用=$A$1公式向右向下复制时，都不会改变引用关系。行绝对引用、列相对引用=A$1公式向下复制时，不改变引用关系。公式向右复制时，引用的列标发生变化。行相对引用、列绝对引用=$A1公式向右复制时，不改变引用关系。公式向下复制时，引用的行号发生变化。相对引用=A1公式向右向下复制均会改变引用关系。COUNTIF函数统计不同性别人数用于在指定区域中，统计符合某个条件的单元格的数量。B$2:B$1
8、0是要统计数量的单元格范围，D3是指定要统计的条件。利用数据有效性制作下拉菜单设置有效性序列来源利用数据有效性的“序列”条件，可以限制在单元格区域中必须输入某一特定序列中的内容。假如需要输入性别信息，可以通过设置数据有效性，在下拉列表中选择输入内容。从下拉列表中选择输入用自定义格式快速输入员工工号设置自定义单元格格式“自定义”格式类型允许用户创建和使用符合一定规则的数字格式，应用于数值或文本数据时，可以改变数据的显示方式。完整的自定义格式代码分为4个区段，并且以半角分号“;”间隔，每个区段中的代码可以对相应类型的数值产生作用：大于0;小于0;等于0;文本将自定义格式转换为实际值在剪贴板中粘贴内
9、容Office剪贴板是用来临时存放交换信息的临时存储区域，可以看作是信息的中转站，能够在不同单元格或是不同工作表之间做数据的移动或复制。利用剪贴板功能，可以将自定义格式转换为单元格中的实际值。输入身份证号码先设置单元格数字格式为文本，然后输入身份证号码身份证号码是一类比较特殊的数据，如果在单元格中直接输入身份证号码时，Excel会默认作为数值型数据处理，由于超过Excel的最大数据处理精度，15位之后的部分将全部变为0。先输入一个半角撇号，然后输入身份证号码认识Excel运算符Excel包含4种类型的运算符：（1）、算术运算符：主要包括加、减、乘、除、百分比以及乘幂等各种常规的算术运算。（2）
10、、比较运算符：用于比较数据的大小，包括对文本或数值的比较。（3）、文本运算符：主要用于将字符或字符串进行连接与合并。（4）、引用运算符：这是Excel特有的运算符，主要用于产生单元格引用。认识Excel运算符当公式中使用多个运算符时，Excel将根据各个运算符的优先级顺序进行运算，对于同一级次的运算符，则按从左到右的顺序运算。顺序符号说明1:（空格）,引用运算符：冒号、单个空格和逗号2-算术运算符：负号（取得与原值正负号相反的值）3%算术运算符：百分比4算术运算符：乘幂5*和/算术运算符：乘和除（注意区别数学计算中的、）6+和-算术运算符：加和减7&文本运算符：连接文本8=,=,比较运算符：比
11、较两个值（注意区别数学计算中的、）认识IF函数IF函数用于执行真假值的判断，根据逻辑计算的真假值返回不同的结果。如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。第一个参数是结果可能为TRUE或FALSE的任意值或表达式。第二个参数是判断结果为TRUE时所要返回的结果。第三参数是判断结果为FALSE时所要返回的结果。身份证有关的计算计算性别：=IF(MOD(MID(C2,17,1),2),男,女)出生日期：=TEXT(MID(C2,7,8),0-00-00)*1DATEDIF函数DATEDIF函数用于计算两个日期之间的天数、月数或年数。该
12、函数是一个隐藏的的日期函数，在Excel的函数列表中没有显示此函数，帮助文件中也没有相关说明。第一参数表示时间段内的起始日期，第二参数代表时间段内的结束日期，第三参数为所需信息的返回类型，该参数不区分大小写。unit参数函数返回结果Y时间段中的整年数M时间段中的整月数D时间段中的天数MD日期中天数的差。忽略日期中的月和年YM日期中月数的差。忽略日期中的日和年YD日期中天数的差。忽略日期中的年VLOOKUP函数使用通配符的查询使用动态的第三参数IFERROR函数3 制作岗位变动表VLOOKUP函数函数作用：在数据区域首列中查找一个内容，并返回与之对应的其他列中的内容。函数用法：VLOOKUP(要
13、查找的内容,要查找的区域,返回该区域第几列的内容,精确匹配还是近似匹配)VLOOKUP函数的特点：1）、VLOOKUP函数查找值支持使用通配符（?号和*号）进行查询。2）、第4个参数决定了查找方式。如果为0或FASLE，用精确匹配方式进行查找，而且支持无序查找；如果为1或TRUE，则使用模糊匹配方式进行查找，并且要求第2个参数的首列或首行按升序排列。3）、第3个参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。4）、如果查找值与数据区域关键字的数据类型不一致，也会返回错误值#N/A。5）、如果有多条满足条件的记录时，只能返回第一个满足条件的记录。6）、查询区域中的
14、首列必须要包含查询值，否则无法正常查询。IFERROR函数函数作用：如果公式的计算结果错误，则返回指定的值，否则返回公式的结果。函数用法：IFERROR(用于检查错误值的公式,公式计算结果为错误值时要返回的值)定义名称名称是被特殊命名的公式，也是以等号“=”开头，组成元素可以是常量数据、常量数组、单元格引用、或是函数公式等，已定义的名称可以在其他名称或公式中调用。创建名称可以使公式变得更加简洁，另外创建名称可以生成动态的数据源，是动态图表制作的必要的手段之一。使用名称框根据所选内容创建使用名称管理器使用【定义名称】命令定义名称OFFSET函数COUNTA函数MATCH函数4 制作员工人事信息数
15、据查询表OFFSET函数函数作用：该函数以指定的引用为参照，通过给定偏移量得到新的引用，返回的引用可以为一个单元格或单元格区域，也可以指定返回的行数或列数。函数用法：OFFSET(基点,需要偏移的行数,需要偏移的列数,新引用的行数,新引用的列数)COUNTA函数与MATCH函数COUNTA函数作用：计算指定范围中不为空的单元格的个数。MATCH函数作用：在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。MATCH函数用法：=MATCH(要查找的内容,在哪个区域查找,查找的方式)数据筛选文本筛选数值筛选生成连续序号SUBTOTAL函数用法数据排序单条件排序多条件排序自定义序列使用自
16、定义序列排序5 制作员工生日信息表数据筛选筛选数据列表的作用是只显示符合用户指定条件的行，隐藏不符合条件的其他行。TODAY函数生成系统当前的日期AND函数和OR函数两种常用的逻辑关系，“与”和“或”数据排序单条件排序多条件排序自定义序列使用自定义序列排序6 劳动合同到期前提醒数据排序Excel 2010能够支持64个排序条件，可以根据需要按行或列，进行升序、降序或是自定义排序操作，而且可以根据单元格内的背景色以及字体颜色等条件进行排序。数值排序多条件排序常用函数TODAY函数用于生成系统当前的日期。AND函数和OR函数分别对应两种常用的逻辑关系，即“与”和“或”。对于AND函数，所有参数的逻
17、辑值为真时返回 TRUE，只要一个参数的逻辑值为假即返回FALSE。对于OR函数，当所有参数的逻辑值都为假时，才返回FALSE，只要一个参数的逻辑值为真，即返回TRUE。条件格式使用Excel的条件格式功能，能够快速对特定条件的单元格进行突出标识，使数据更加直观易读。用户可以预置一种单元格格式或是单元格内的图形效果，在符合指定的条件时，自动应用于目标单元格。可预置的单元格格式包括单元格边框、底纹、字体颜色等，单元格图形效果包括数据条、色阶和图标集三种类型。按F12键，在弹出的【另存为】对话框底部，单击【工具】【常规选项】，弹出【常规选项】对话框。用户可以为工作簿设置更多的保存选项。两种方法1L
18、GO7 对重要文件进行保护2单击【文件】选项卡，再依次单击【保护工作簿】【用密码进行加密】，在弹出的【加密文档】对话框中输入密码。【公式】选项卡，单击【定义名称】，弹出【新建名称】对话框，在“名称”文本框中输入“目录”，在“引用位置”文本框输入以下公式：=INDEX(GET.WORKBOOK(1),ROW(A1)&T(NOW()两个步骤1LGO8 自动跳转的工作表目录2在B1单元格输入以下公式向下复制，到出现空白单元格为止。=IFERROR(HYPERLINK(目录&!A1,MID(目录,FIND(,目录)+1,99),)INDEX函数根据指定的行号和列号，返回指定区域中的值。HYPERLIN
19、K函数能够生成链接的特殊函数。EXCEL第三章在人力资源与行政管理中的应用CONTENTS01员工结构统计分析表02员工离职原因分析表03分析员工离职率04全年在职职工人数变化目录05统计分析人事信息定位可见单元格3.1 制作员工结构统计分析表建立工作表副本不同学历员工的人数和占比3.1.2 统计员工学历构成设置值显示方式数据透视表结构值区域行区域列区域筛选区域数据透视表常用术语术语含义数据源用于创建数据透视表的数据列表列字段等价于数据列表中的列行字段在数据透视表中具有行方向的字段页字段数据透视表中进行分页的字段字段标题用于描述字段内容项组成字段的成员组一组项目的组合分类汇总数据透视表中对一行
20、或一列单元格的分类汇总刷新重新计算数据透视表，反映目前数据源的状态在数据透视表的值区域单击鼠标右键，在快捷菜单中单击【值汇总依据】命令，可以根据需要选择求和、计数、平均值、最大值、最小值、乘积等多种汇总方式。数据透视表值显示方式选项数值区域字段显示为无计算数据透视表中的原始数据总计的百分比每个数值项占所有汇总的百分比值列汇总的百分比每个数值项占列汇总的百分比值行汇总的百分比每个数值项占行汇总的百分比值百分比以选定的参照项为100%，其余项基于该项的百分比父行汇总的百分比在多个行字段的情况下，以父行汇总为100%，计算每个数值项的百分比父列汇总的百分比在多个列字段的情况下，以父列汇总为100%，
21、计算每个数值项的百分比父级汇总的百分比某一项数据占父级总和的百分比差异以选中的某个基本项为参照，显示其余项与该项的差异值差异百分比以选中的某个基本项为参照，显示其余项与该项的差异值百分比按某一字段汇总根据选定的某一字段进行汇总按某一字段汇总的百分比将根据字段汇总的结果显示为百分比升序排列对某一字段进行排名，显示按升序排列的序号降序排列对某一字段进行排名，显示按降序排列的序号指数计算数据的相对重要性。使用公式：单元格的值总体汇总之和/（行总计列总计）常用布局1调整数据透视表布局常用布局22季度加班费数据透视表筛选器两个季度加班费不显示分类汇总数据透视表分类汇总显示方式在右键菜单中操作员工年龄结构
22、3.1.3 统计员工年龄结构为年龄字段创建组每一年入职人数3.1.4 统计每年入职人数为日期字段创建组3.1.5 分析员工性别占比认识图表元素标题图例项纵坐标轴横坐标轴数据系列网格线数据标签趋势线图表区绘图区插入切片器3.1.6 各部门性别构成动态图表用切片器查看不同部门信息数据透视图中的图表元素图例字段轴字段报表筛选字段保存模板使用模板创建统一样式的图表应用模板冻结窗格3.2 员工离职原因分析表不同月份、不同原因的离职人数员工离职率：离职率是企业用以衡量内部人力资源流动状况的一个重要指标，通过对离职率的考察，可以了解企业对员工的吸引和满意程度。一定范围内的员工流动，能够使企业利用优胜劣汰的人
23、才竞争机制，保持企业的活力和创新能力。但是离职率过高，则会影响到企业的稳定发展，使人力资源成本增加、组织效率下降。3.3 用组合图表分析员工离职率各年度员工离职率Excel 2010内置了11种图表类型，包括：柱形图、折线图、饼图、条形图、面积图、XY散点图、股价图、曲面图、圆环图、气泡图和雷达图，每种图表类型还包含多种子图表类型。Excel中的图表类型用图表展示人数变化的优势：将全年在职职工人数绘制成柱形图，并且按不同季度使用颜色进行区分，能够使图表看起来更加直观。3.4 全年在职职工人数变化全年在职人数变化COUTNIFS函数作用：用于对某一区域内满足多重条件的单元格进行计数。COUTNI
24、FS函数用法：COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)3.5 统计分析人事信息统计分析人士信息EXCEL第四章在人力资源与行政管理中的应用CONTENTS01人员增补申请表02招聘费用预算表03招聘面试通知单04用甘特图展示招聘进度目录1CONTENTS05制作带照片的员工胸卡06重命名员工照片07招聘录用统计分析表目录24.1 制作人员增补申请表增补申请表人员增补申请表能方便人力资源管理部门统计企业的招聘人数，帮助人事部门招聘到更加适合岗位需求的人员，是人事管理的必备资料之一。4.2 制作招聘费用预算表招聘费用预算表常规的招聘费用包括广告宣传费、海报制作
25、费用、场地租用费用、表格资料的打印复印费用和招聘人员的餐饮费和交通费用等。4.3 制作招聘面试通知单Word中的面试通知单经过对应聘人员的初步筛选，人力资源部筛除明显不符合岗位任职资格的人员，接下来的工作是通知基本符合录用要求的人员参加面试。如果招聘人员较少时可以电话直接通知，但是如果招聘岗位较多，使用电子邮件通知的方式会更方便快捷。插入合并域4.3.3用邮件合并发送面试通知单修改域代码4.4 用甘特图展示项目进度甘特图效果甘特图又称为项目进度图，以图示的方式展示活动列表和时间刻度，形象地表示出项目的活动顺序与持续时间。甘特图的横轴表示时间，纵轴表示项目，线条表示在整个期间的计划和实际活动完成
26、情况，能够直观地表明任务计划在什么时候进行，及实际进展与计划要求的对比。由此可发现某项任务还剩下哪些工作要做，并可评估工作进度。4.5 制作带照片的员工胸卡利用Excel人事表格中的数据和Word的邮件合并功能相结合的方法批量制作员工胸卡，能够节约时间和成本。邮件合并添加照片输入照片域代码 注意照片域代码的插入位置创建人事数据表在Excel中准备好数据创建“员工胸卡”Word文档在Word中制作胸卡框架复制到记事本修改文件扩展名.由txt”修改为“.bat”使用公式=ren&A2&.jpg&B2&.jpg“4.6 重命名员工照片双击运行文件扩展名利用Excel人事表格中的数据和Word的邮件合
27、并功能相结合的方法批量制作员工胸卡，能够节约时间和成本。邮件合并添加照片输入照片域代码 注意照片域代码的插入位置创建人事数据表在Excel中准备好数据创建“员工胸卡”Word文档在Word中制作胸卡框架各月份招聘完成情况折线图不同渠道招聘占比动态图表招聘情况汇总表年度招聘完成情况汇总表4.7 招聘录用统计分析表各招聘渠道分析图表快速求和4.7.1制作招聘情况汇总表对比各月份完成率变化4.7.2年度招聘完成情况汇总表设置迷你图颜色添加高低点连线4.7.3各月份招聘完成情况折线图设置迷你图颜色定义名称4.7.4制作不同渠道招聘占比的动态图表设置迷你图颜色4.7.5各招聘渠道分析图表图表最终效果饼图
28、主要用于显示一个数据系列中每一项的大小与各项总和的比例，以每个数据点的扇区面积显示数据大小。使用饼图时需要注意：要绘制的数值没有负值和零值。类别一般不要超过七个。EXCEL第五章在人力资源与行政管理中的应用CONTENTS01培训计划完成统计表 02员工培训考核统计表03培训成绩统计分析表目录5.1 培训计划完成统计表统计表效果制定出培训计划后，还需要对培训计划的落实情况进行跟踪。如果培训计划和实际培训的差异较大，则需要进一步分析产生的原因，确定具体是培训计划本身的制定存在瑕疵还是实际培训执行阶段出现问题，因此需要制作一份培训计划完成统计表对培训计划完成情况进行汇总。Excel常用快捷键执行操
29、作快捷键组合查看帮助文件F1重复最后一次操作F4显示“定位”对话框F5重新计算F9另存为F12显示“单元格格式”对话框Ctrl+1复制选定区域Ctrl+C剪切选定区域Ctrl+X粘贴选定区域Ctrl+V撤消最后一次操作Ctrl+Z选定活动单元格所在的当前区域Ctrl+A保存当前操作Ctrl+S打印工作表Ctrl+P打开新工作表Ctrl+O（字母O）新建工作簿Ctrl+N打开查找对话框Ctrl+F打开替换对话框Ctrl+H清除选定区域的内容Delete删除选定区域Ctrl+-（数字小键盘的减号）插入行或列Ctrl+（数字小键盘的加号）选定当前区域Ctrl+*（数字小键盘的乘号）5.2 制作员工培
30、训考核统计表培训考核统计表每次（期）培训结束后，培训主管部门组织培训员工进行考试，及时评定、汇总考核的成绩。本节学习员工培训考核统计表的制作，以便对员工的学习情况等进行评价记录。简单的高级筛选认识LOOKUP函数用RANK函数对成绩排名IF函数多条件判断5.3 成绩统计分析表IF函数多条件判断在Excel中对指定数值进行多个条件的判断时，需要根据每一个条件依次进行判断。5.3.1判断培训成绩等次认识LOOKUP函数典型用法为：=LOOKUP(1,0/(条件),目标区域)如果查询区域中有多个符合条件的记录，LOOKUP函数仅返回最后一条记录。RANK函数返回一列数字的数字排位。第一参数是需要找到
31、其排位的数字。第二参数是指定数字列表的范围。第三参数用于指定数字排位的方式，如果为0（零）或省略，按照降序排列。如果不为零，则按照升序排列。5.3.2培训成绩排名中国式排名无论有几个并列名次，后续的排名紧跟前面的名次顺延生成，并列排名不占用名次。5.3.2提取指定条件的员工名单高级筛选高级筛选功能是自动筛选的升级，不仅包含了自动筛选的所有功能，而且能够设置更多更复杂的筛选条件。位于同一行的各个条件表示相互之间是“与”的关系。位于不同行的各个条件表示相互之间是“或”的关系。EXCEL第六章在人力资源与行政管理中的应用CONTENTS01制作考勤表02出勤情况分析03不同部门工资增幅变化目录05计
32、算销售提成04制作、汇总工资表6.1 整理考勤机数据考勤机数据虽然市面上的考勤机种类非常多，功能也越来越强大，但是实际应用中，仍然需要人力资源管理人员对请假、迟到、早退、未打卡等一些异常情况进行单独处理，最终以考勤机的打卡时间为依据，辅以请休假等特殊情况的记录生成考勤表。6.1.1统计出勤状态和早退迟到时间出勤状态公式=IF(D2-INT(D2)=-18:00),正常,迟到/早退)日期和时间数据日期和时间混合型的数据，实际上是由整数组成的日期序列值和由小数组成的时间序列值两部分组成。统计迟到早退次数筛选不重复记录6.1.2制作加班统计表公式的复制与填充方法1拖曳填充柄。单击输入公式的单元格，指
33、向该单元格右下角，当鼠标指针变为黑色“”字型填充柄时，按住鼠标左键向下拖曳至目标单元格。方法2双击填充柄。单击输入公式的单元格，双击该单元格右下角的填充柄，公式将向下填充到当前单元格所位于的不间断区域的最后一行。方法3 填充公式。在一个单元格中输入公式后，然后选中需要输入相同公式的单元格区域，按组合键或单击【开始】选项卡的【填充】下拉按钮，在扩展菜单中单击【向下】按钮。按指定条件求和SUMIF函数第一参数用于判断条件的单元格区域。第二参数用于确定求和的条件。第三参数是要求和的实际单元格区域。其语法可以理解为：SUMIF(条件判断区域,求和条件,求和区域)求和条件使用通配符按多个条件求和SUMI
34、FS函数用法：SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,)多求和条件使用通配符除了使用考勤机打卡记录员工的考勤之外，还有部分企业会使用“考勤表”的形式记录员工出勤。设计合理的考勤表不仅能够直观显示员工的出勤状况，还可以减少统计人员的工作量。6.2 用Excel制作考勤表考勤表常用日期类函数 DAY函数返回某个日期的在一个月中的天数，结果是介于 1 到 31 之间的整数。YEAR函数返回对应于某个日期的年份，结果为19009999之间的整数。MONTH函数返回日期中的月份，结果是介于1（一月）到12（十二月）之间的整数。DATE函数可以根据指定的年份数、月份数和一月中的天数
35、返回日期序列。EOMONTH函数返回指定日期之前或之后指定月份数的月末日期。WEEKDAY函数返回对应于某个日期的一周中的第几天。一个月结束后，通常需要制作一份月度出勤汇总表，对员工的出勤情况进行汇总后张贴公示或提请领导签字后再以此制作工资表。6.3 制作月度出勤汇总表汇总表效果6.4 部门出勤情况分析统计部门出勤情况时，通常会统计部门的出勤率或是缺勤率，部门出勤率的计算方法为部门实际出勤天数之和除以应出勤天数之和*100%。部门出勤率分析表平均出勤率分析表6.5 制作职工带薪年休假申请审批单凡连续工作1年以上的均可以享受带薪年休假，职工在休假期间享受与正常工作期间相同的工资收入。累计工作满1
36、年不满10年的，年休假5天；满10年不满20年的，年休假10天；满20年的年休假15天。职工年休假记录台帐带薪年休假申请审批单6.6 用图表展示不同部门工资增幅变化年度工资结构分析通常包括薪资增长状况、不同薪资结构对比、不同职位和级别的薪资数据、奖金福利状况、长期激励措施以及未来薪资走势分析等。本节以工资增幅数据为例，学习使用图表展示不同部门工资增幅变化的情况。不同部门工资增幅变化6.7 使用条件格式展示工资增幅变化使用图表展示数据的优点是效果美观，但是其操作步骤较多，刚刚学习Excel的用户往往不容易掌握，可以使用条件格式实现类似的效果。不同部门工资增幅变化6.8 制作工资表企业有每月为职工
37、代扣、代缴工资薪金所得部分个人所得税的义务。根据有关法规：工资薪金所得以每月收入额减除费用3500元后的余额为应纳税所得额，即：月应纳税所得额=月工资收入额-3500（基数）月应纳税额=月应纳税所得额适用税率-速算扣除数个人所得税税率表级数级数 含税级距含税级距税率税率(%)(%)计算公式计算公式速算扣除数速算扣除数1 1不超过1500元的 3T=(A-3500)*3%-002 2超过1500元至4500元的部分 10T=(A-3500)*10%-1051053 3超过4500元至9000元的部分 20T=(A-3500)*20%-5555554 4超过9000元至35000元的部分 25T=
38、(A-3500)*25%-100510055 5超过35000元至55000元的部分 30T=(A-3500)*30%-277527756 6超过55000元至80000元的部分 35T=(A-3500)*35%-550555057 7超过80000元的部分 45T=(A-3500)*45%-1350513505本表含税级距指以每月收入额减除费用本表含税级距指以每月收入额减除费用35003500元后的余额或者减除附加减除费用后的余额。元后的余额或者减除附加减除费用后的余额。6.8.2 计算个人所得税个税计算公式=ROUND(MAX(应税工资-3500)*3;10;20;25;30;35;45%
39、-0;105;555;1005;2755;5505;13505,0),2)6.9 分类汇总工资表在月度工资表中往往需要按照部门统计当月工资的小计，使用分类汇总功能可以简化统计工作量，提高工作效率。不同部门工资增幅变化 部门分类汇总统计 打印不同汇总结果 高亮显示部门小计6.10 每页带小计的工资表如果工资表的数据量比较多，当需要打印时，在每一页的最后加上本页小计，在最后一页加上总计，能够增加表格的可读性。类似的打印效果在一些财务软件中已经实现了自动化，用Excel自带的功能也可以实现这样的效果。设置后的打印预览效果6.11 按部门拆分工作表如果将工资表中的内容按不同的部门分别拆分到不同工作表内
40、，能够便于查询和打印。本节学习按部门拆分工资表的技巧。显示报表筛选页6.12 批量制作员工工资条工资条是单位定期给员工反映工资的凭据，记录着员工的月收入分项和收入总额，通常包括工号、职工姓名、基本工资、奖金福利、代缴保险、应发工资、个人所得税和实发工资等项目。工资条的制作可通过多种方法来实现，主要有排序法、函数法以及邮件合并法等。工资条效果6.13 计算销售提成销售提成计算主要包括以下四种：1、纯提成制，销售人员的工资全部由提成构成，不发放固定的基本工资。2、底薪+提成制，销售人员的工资由基本工资和业绩提成两部分构成。3、底薪+提成+奖金制，销售人员除了底薪和业绩提成外，还会获得部分奖金收入。
41、4、底薪+奖金制，销售人员的收入主要以底薪和与企业整体利益挂钩的奖金组成。阶梯式销售提成计算6.14 用图表展示历年平均薪酬变化以企业历年平均薪酬数据为例，学习使用图表展示历年平均薪酬变化的技巧完成后的图表效果EXCEL第七章在人力资源与行政管理中的应用CONTENTS01创建社保缴费统计表02计算社会保险基数目录7.1 创建社保缴费统计表完成后的表格效果社会保险费是企业成本和职工工资总额的重要组成部分，主要包括基本养老保险、基本医疗保险、失业保险、工伤保险和生育保险等强制性基本保险费用，同时还包括企业依法设立的各项补充保险费用。使用MEDIAN函数使用IF函数MIN函数和MAX函数7.2 计
42、算社会保险基数使用IF函数=IF(D2$B$15,$B$15,IF(D2$C$15,$C$15,D2)首先以B15单元格的缴费基数上限14076为参照，如果D2单元格中的平均工资大于缴费基数的上限，则返回B15单元格中的值。继续判断D2单元格中的平均工资如果低于C15单元格的缴费基数下限，则返回C15单元格中的值，否则仍然返回D2单元格中的值。MIN函数和MAX函数=MIN($B$15,MAX(D2,$C$15)该公式利用MAX函数和MIN函数，在上年度月平均工资和缴费基数限值之间进行比较。“MAX(D2,$C$15)”部分，用MAX函数计算出D2单元格平均工资与C15单元格的缴费基数下限的最
43、大值，这一步的作用是如果平均工资低于缴费基数下限，则返回缴费基数下限，否则返回D2本身的值。再使用MIN函数，在缴费基数上限和MAX函数的计算结果之间进行比较，并返回其中的较小值。这一步的作用是如果MAX函数的计算结果大于缴费基数上限，则仍然返回缴费基数上限。此技巧在实际工作经常被用户使用，公式模型通常为：=MIN(上限,MAX(下限,数值本身)7.2 计算社会保险基数使用MEDIAN函数=MEDIAN(D2,$B$15,$C$15)MEDIAN函数用来返回一组数的中值，正确使用MEDIAN函数将会比IF函数写的公式更加简洁。如果参数集合中包含偶数个数字，函数MEDIAN将返回位于中间的两个数的平均值。7.2 计算社会保险基数排序规则是按照姓氏笔画数的多少排列，同笔画的姓名按起笔顺序排列，如果同姓则对姓名第二、第三字按该规则排序。按姓氏笔画排序
