excel在人力资源管理中的简单应用

EXCEL在人力资源管理中的简单应用目录基本设置2数据透视表4模板：人事信息统计表31常用函数33一、模板：人事信息统计表1、模板展示人力资源信息统计表（演示）.xls2、包含特色（1）输入身份证号码，可以由公式自动生成：“性别”“出生日期”“年龄”“生日提醒”“身份证所属省份”“身份证所属地区”一、模板：人事信息统计表2、包含特色（2）输入入公司时间，可以由公式自动生成：“工龄”“试用期到期时间”“试用期到期15天提醒”“退休日期”一、模板：人事信息统计表2、包含特色（3）输入劳动合同终止日期，可以在合同到期40天时提醒（4）设置下拉菜单，可以直接选取下拉项目：“户口类型”“政治面貌”“婚姻状况”“学历”“保险类别”一、模板：人事信息统计表2、包含特色（5）根据人事基本信息，可以自动生成统计报表：按部门统计人数按性别统计人数按学历统计人数按年龄范围统计人数按入职时间范围统计人数二、基本设置1、设置下拉列表（1）样例：二、基本设置1、设置下拉列表（2）适用范围：某列数据的值只在几个固定值中间选择一个，比如：政治面貌、学历、性别、部门等二、基本设置1、设置下拉列表（3）操作步骤（A）引用同一工作表内的数据：如果同一工作表的某列区域就是下拉菜单想要的数据，例如引用工作表Sheet1的B2:B5。B2:B5分别有以下数据：中共党员、共青团员、群众、其他，操作如下：二、基本设置1、设置下拉列表（a）选择要设置的单元格，例如A1单元格；（b）选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口；二、基本设置1、设置下拉列表（c）在“设置”选项卡中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据“=$B$2:$B$5”，也可以直接点击单元格选择B2：B5区域→按“确定”完成，再次选择该A1单元格，就出现了下拉菜单。二、基本设置1、设置下拉列表二、基本设置1、设置下拉列表（B）引用不同工作表内的数据（必须为引用的区域定义名称）：如果不同工作表的某列区域就是下拉菜单想要的数据，譬如工作表Sheet1的A1单元格要引用工作表Sheet2的A2:A8区域。工作表Sheet2的A2:A8分别有以下数据：博士、硕士、本科、专科、高中、中专技校、初中及以下，操作如下：二、基本设置1、设置下拉列表（a）为引用单元格区域定义名称：选择Sheet2的A2:A8单元格区域→右键→“命名单元格区域”→弹出“新建名称”窗口，在“名称”右面输入“学历”→“引用位置”下面输入“=Sheet2!$A$2:$A$8”，也可以直接点击单元格选择A2:A8区域→点击“确定”完成命名单元格区域。二、基本设置1、设置下拉列表二、基本设置1、设置下拉列表（b）选中要设置的单元格，比如Sheet1表的A1，选择菜单栏的“数据”→“数据有效性”→出现“数据有效性”弹出窗口；二、基本设置1、设置下拉列表（c）在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾→在“来源”下面输入数据“=学历”→按“确定”完成，再次选择该A1单元格，就出现了下拉菜单。二、基本设置1、设置下拉列表二、基本设置2、插入复选框（窗体控件）（1）样例：二、基本设置2、插入复选框（窗体控件）（2）适用范围：适合于列出来各种备选答案，让使用者直接选择即可，可以作为多选题目二、基本设置2、插入复选框（窗体控件）（3）操作步骤：此功能的实现是根据“开发工具”这个选项来完成的，因此要确保“开发工具”选项卡存在。二、基本设置2、插入复选框（窗体控件）（a）Office按钮－Excel选项－常用，勾上“在功能区显示开发工具选项卡”。二、基本设置2、插入复选框（窗体控件）（b）点击“开发工具”“插入”“表单控件”“复选框（窗体控件）”，然后在需要插入复选框的单元格直接画出复选框即可。二、基本设置2、插入复选框（窗体控件）（c）如果需要选中多个复选框时，第一个复选框往往不容易选中，此时可以在第一个复选框上点击鼠标右键，此时复选框即被选中，然后按着Ctrl键再点击其他的复选框即可以比较容易的选中。三、常用函数1、文本函数：LEN、LEFT、RIGHT、MID函数（1）函数的格式及其含义LEN(text)返回文本串的字符数LEFT(string,n)从字符串左端取指定个数的字符RIGHT(string,n)从字符串右端取指定个数的字符MID(text,start_num,num_chars)从字符串中的某一位开始，返回指定数目的字符三、常用函数1、文本函数：LEN、LEFT、RIGHT、MID函数（2）演示实例三、常用函数2、日期和时间函数：DATE、TIME、TODAY、NOW函数（1）函数的格式及其含义：DATE（n1，n2，n3）返回由n1，n2，n3代表特定的日期：n1-n2-n3TIME（n1，n2，n3）返回由n1，n2，n3代表特定的时间：n1：n2：n3TODAY（）根据计算机设定的时间，返回当前的日期NOW（）根据计算机设定的时间，返回当前的日期三、常用函数2、日期和时间函数：DATE、TIME、TODAY、NOW函数（2）演示实例：三、常用函数3、数学函数：ROUND、ROUNDDOWN、ROUNDUP函数（1）函数的格式及其含义：ROUND(number,num_digits)返回按指定位数进行四舍五入的数值ROUNDDOWN(number,num_digits)返回按指定位数进行向下舍入数字的数值（舍九法）ROUNDUP(number,num_digits)返回按指定位数进行向上舍入数字的数值（进一法）三、常用函数3、数学函数：ROUND、ROUNDDOWN、ROUNDUP函数（2）演示实例：三、常用函数4、查找与引用函数：VLOOKUP函数（1）【案例】：请统计全年每个员工的应发工资总额及平均工资，并进行工资分析（按部门分析、按岗位分析）三、常用函数4、查找与引用函数：VLOOKUP函数（2）【思路】1、全年员工有变动，因此任何一个月的工资表中的人员都不完全，因此必须首先整理一份完整的全员名单；2、将每人每个月的工资整理在一张excel表上；3、统计全年每人的工资总额以及平均工资4、按部门、岗位进行工资汇总分析三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（A）VLOOKUP函数的标准格式VLOOKUP（查找值，区域，列序号，逻辑值）“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或字符串。；“区域”：数组所在的区域；“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；“逻辑值”：为TRUE或FALSE。TRUE或省略，则返回近似匹配值，为FALSE（或者为“0”），则返回精确匹配值。三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（A）整理工资汇总表中的人员名单：将12月份工资表中的人员复制在“工资汇总表”中，然后将“工资汇总表”中的人员分别在1月-11月工资表中查找，如果显示“#N/A”的，则说明是在“工资汇总表”中无此人，则将此人复制到“工资汇总表”中，整理一份全年完整的人员名称。（或者如果人事信息表中有全年所有的在职人员和离职人员的，则直接使用即可。）三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（A）关于excel2007筛选后复制的小窍门：如果筛选后直接复制的话，粘贴的仍然是全部表格内容，可以通过以下方法：（a）进行2次以上的筛选操作，复制粘贴（b）筛选后选择需要复制的区域，选择“开始”、“查找和选择”、“定位条件”、“可见单元格”，再进行复制粘贴，就是筛选后内容三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（B）在“工资汇总表”中利用“VLOOKUP函数”将每个员工每个月的工资查找与引用，整理成完整的年度工资汇总表。三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（C）在“工资汇总表”中利用求和公式与求平均值公式分别计算出每个员工“全年的工资总额”和“月平均工资”注意：在计算“月平均工资”时，如果某员工某月由于尚未入职或已经离职，工资为“0”时，单元格必须为空，而不能写成“0”三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】求和公式：SUM（A2：P2）求平均值公式：AVERAGE（A2：P2）三、常用函数4、查找与引用函数：VLOOKUP函数（3）【解决方案】（D）利用数据透视表进行数据分析：四、数据透视表1、“数据透视表”的功能:能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，四、数据透视表2、“数据透视表”的步骤：第一步，单击工作表数据区域中的任意一个单元格。切换至功能区的“插入”选项卡，单击“数据透视表”命令，打开“创建数据透视表”对话框。四、数据透视表2、“数据透视表”的步骤：四、数据透视表2、“数据透视表”的步骤：第二步，默认情况下，excel将整个数据区域作为数据透视表的源数据，同时设置数据透视表的保存位置为一个新工作表。四、数据透视表2、“数据透视表”的步骤：第三步，在“数据透视表字段列表”窗格中，根据所要分析的问题，选择各个字段，并将其拖放至“报表筛选”、“列标签”、“行标签”和“数值”这4个区域窗格。四、数据透视表2、“数据透视表”的步骤：四、数据透视表四、数据透视表四、数据透视表3、“数据透视表”的注意事项：（1）数据透视表所选择的区域内的第一行不能为空白，所选择的区域内，不能有空白列。（2）对于“数值”区域内汇总的方式，一般默认为“计数”，如果需要对其进行求和或求平均值时，则可以选中某列中的任意一个单元格，点击右键，选择“数据汇总依据”，可以选择“求和”或“平均值”四、数据透视表3、“数据透视表”的注意事项：（3）如果需要对数据透视表内的数据进行排序时，可以选中任意一列中的任意一个单元格，点击鼠标右键，选择“排序”，“其他排序选项”，然后可以根据需求进行排序。