[人力资源管理]EXCEL在人力资源中的应用.ppt

《[人力资源管理]EXCEL在人力资源中的应用.ppt》由会员分享，可在线阅读，更多相关《[人力资源管理]EXCEL在人力资源中的应用.ppt（48页珍藏版）》请在三一文库上搜索。

1、Excel在人力资源管理中的应用,Coach：周泓，希哲管理咨询首席咨询师 Mobile：13901805998 MSN:psy-,开头语：,OFFICE通用的命令功能今天不介绍（保存、字体等） 我们不是软件开发者，只是应用者 只是一些经验之谈 有些我不懂 有些也暂时不必要懂 学习过程中 听我讲一讲 看我做一做 自己做一做,开头语,Excel是微软公司出品的Office系列办公软件中的一个组件 Excel有以下几个方面的应用： 数值计算：创建、分析、实施你能想到的任意类型的数据分析。 创建图表：创建多种图表。 组织列表：使用行-列的形式高效的存储列表。 访问其他数据：从多种数据源导入数据 自动

2、化复杂的任务：通过使用Excel的宏功能只需单击鼠标一次就可以执行一个非常复杂的任务。,课程培训目的,如果你是Excel的初学者，通过本次培训你可以： 认识excel，定制个性化表单 提升数据处理速度 标准化，格式化，美化你的Excel文件 学会打印文件非规则图表 了解公式与函数操作 了解初步统计与图表 如果你已经长期使用Excel的，通过本次培训你可以： 煮熟“夹生饭”，了解更多Excel的基础操作 简化操作步骤 提高数据分析统计技巧,培训的内容,定制个性化Excel（有效性、条件格式） 打印设置（页面设置、设置标题、页眉页脚设置等） 简单数据处理（特殊输入、选择性粘帖、复制表单、筛选、输入

3、表单等） 公式与常用函数 透视表制作 图表制作介绍 EXCEL在人力资源管理中的典型应用 Q&A,定制个性化Excel,应用程序窗口,认识Excel窗口。,Excel外观与功能的调整,1. 水平滚动条的长短设定 2. “工具”选项“”视图“ 3. “工具”选项“”常规“,数据有效性,统一输入格式 避免输入错误 界面友好规范,示例,定义名称（区域）,名称引用： 作用：定位（f5），用在公式中起绝对引用功能（f3），跨工作表操作。 定义名称的方法：选择区域或单元格，插入名称定义 数字不可能用于命名 单元格地址不能用于命名 名字中不能空格 不能以EXCEL已有的命令命名 名字写完后要回车确认。 删除

4、名称：插入名称定义 可用于二级下拉菜单以及函数快速查找中简化命令,示例,条件格式,示例,2. 打印设置,页面设置,“文件”页面设置”。 页面设置是针对打印的文件进行格式上的调整 页面设置包括： 页面 页边距 页眉/页脚 工作表,页眉/页脚,页眉和页脚的设置。,页眉预览框,页眉内容选择 页脚内容选择,页脚预览框,工作表,选择打印区域 打印标题行 打印标题列 网格线 先行后列，先列后行,3. 简单数据处理,一次键入，自动填充,将鼠标指针放在单元格的右下角，直至出现黑色十字形 (+)。 在填充范围内拖住填充柄。 双击。 技巧 可以上下左右拖。 可以等差序列和等比数列拖，如3，6，9。 除了数字形式，

5、可以填写各种类型的 Excel 列表：星期一、星期二；9:00、10:00；第 1 季度、第 2 季度或者 1 月 15 日、2 月 15 日。 工具-选项-自定义序列。 “编辑”填充”,特殊输入,非重复,单元格密码,前提：单元格保护要锁定、隐藏,单元格密码,选择性粘贴,在一般情况下，我们要的不是公式，而是公式计算的结果，为了防止数据的变化，我们会用到“选择性粘贴”命令。,选择性粘贴,公式：将公式进行粘贴； 数值：将公式所得结果以数值的方式粘贴； 格式：仅仅将单元格的格式粘贴过去，内容不变。 批注：仅仅粘贴批注，其他不变。 加/减/乘/除：将被复制的数字加/减/乘/除在目标单元格的数字上。 跳

6、过空单元格：空单元格将不被复制和粘贴； 转置：行列位置颠倒；,查找与替换,1查找：“编辑”查找”，或者Ctrl+F 2. 替换：“编辑”替换”，或者Ctrl+H 可选参数： 区分大小写 单元格匹配 区分全/半角 范围 搜索 查找范围 格式 *，？,筛选,应用多个筛选 筛选某一列后，您可以在另一列上再次筛选，然后继续筛选其他列，依次类推。 使用“前 10 个”查找最大或最小的项 前 10 个”是另一个用途广泛的筛选，您可以对包含数字或日期的列使用“前 10 个”。 使用“前 10 个”，您可以查找数值最大的项或者数值最小的项（最小或最大的数字或日期）。虽然叫“前 10 个”，但它并不局限于仅查找

7、前 10 项或最后 10 项。您可以选择要查看的项数：最少 1 项，最多 500 项。 使用自定义自动筛选方式来查找自动筛选箭头旁边的列表中所没有的项，例如： 一定范围内的值，如两个特定数字之间的所有数字。 一定范围外的值，如所有早于或晚于某一特定日期对的日期。 等于或不等于另一个值的值。 包含在其他文本中的特定文本。,记录单式输入,使用记录单可以方便我们进行： 录入数据 查看数据 “数据”记录单”,贺卡邮寄,数据采集,重排窗口,“窗口”重排窗口” 当需要频繁的在两个窗口之间转换或者需要将两个窗口进行对比时，我们会选择并排的方式 水平并排：将目前所有打开的工作簿水平排列； 垂直并排：将目前所有

8、打开的工作簿垂直排列； 层叠：将目前所有打开的工作簿垂直排列； 平铺+当前活动工作簿的窗口：最大化当前窗口,保护和隐藏工作表,当工作表过多或有些工作表不希望被浏览到时我们可以将这些工作表进行隐藏。 “格式”工作表”隐藏” “格式”工作表”取消隐藏” 保护工作表往往是很重要的，我们可以将Excel工作表设定为保护模式，限定修改权甚至复制权。 “工具”保护”保护工作表”，不选择任何选项； 在“单元格”保护”中将不需要锁定的区域取消“锁定”，可以保护部分工作表不被选定。仅选择“选定未锁定的单元格”。,Excel表格与Word之间的转换,邮件合并 第一步：设置成信封样式：打开一个word，设置为信封样

9、式（自定义大小），也可以是订单模板 第二步：拉出邮件合并快捷工具：视图-工具栏-邮件合并； 第三步：导入excel文档：工具-信函与邮件-邮件合并，在右边选择“信函-下一步-使用当前文档-下一步-使用现有列表-浏览打开excel文档,后,前,4. 公式与函数,创建公式： 先输入，再输入表达式 单元格的引用方式 相对引用（a1)：公式在列中复制时，行号在变，列标不变，公式在行中复制时，列标在变，行号不变 绝对引用($a$1)：公式复制时不能动要加$ (F4) 混合引用($A1 3次F4 ,A$1 2次F4 )：适合用在向两个方向上复制的表格中。,位置引用,公式,创建公式,y=（3x+1）/2,函

10、数,函数的结构 函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。,是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。,函数,函数的选项板 公式选项板-帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的“编辑公式“按钮，或是单击“常用“工具栏中的“粘贴函数“ 按钮之后，就会在编辑栏下面出现公式选项板。,函数,函数的分类 1. 数据库函数 当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数 2. 日期与时间函数 通过日期与时间函数，可以在公式中分析和

11、处理日期值和时间值 3. 工程函数 工程工作表函数用于工程分析。分：对复数进行处理的函数、在不同的数字系统（如2，8，10，16进制）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数 4. 财务函数 财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值 5. 信息函数 可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为 IS 的工作表函数，在单元格满足条件时返回 TRUE，如ISEVEN ，ISBLANK,函数,函数的分类 6. 逻辑函数 使用逻辑函数可以进行真假值判断，或者进行复合检验。 7. 查询和引用函数 当需要

12、在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数 8. 数学和三角函数 通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算 9. 统计函数 统计工作表函数用于对数据区域进行统计分析 10. 文本函数 通过文本函数，可以在公式中处理文字串 。 11. 用户自定义函数 如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数,公式与函数的差异,函数可以看成多个公式组合而成的结果。 但是有些高级函数不是纯粹的数学运算，这是公式所不能表达的 要求： 必须以=开始； 运算符如*&等；

13、 单元格引用包括对单元格和范围的命名； 可以插入值或字符串如79或“合计”等； 函数或者它们的参数包括一些函数如求SUM、AVERAGE等也可以作为参数使用； 括号用来控制着公式中各表达式的被处理次序。 提示： 函数最多七层嵌套 引号输入使用英文状态,常用函数,无条件求和SUM函数 该函数是求30个以内参数的和。公式为= SUM（参数1，参数2，参数N） 条件求和SUMIF函数 SUMIF函数的功能是根据指定条件对若干单元格求和，公式为=SUMIF(range,criteria,sum_range) 式中 range用于条件判断的单元格区域； criteria确定哪些单元格将被相加求和的条件，

14、其形式可以为数字、表达式或文本； sum_range需要求和的实际单元格 AVERAGE函数 AVERAGE函数的功能是计算给定参数的算术平均值。公式为= AVERAGE（参数1，参数2，参数N） 单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内 COUNT函数和COUNTIF函数 COUNT函数的功能是计算给定区域内数值型参数的数目。公式为= COUNT（参数1，参数2，参数N） COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为= COUNTIF（range，criteria） range需要计算其中满足条件的单元格数目的单元

15、格区域； criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。,常用函数,IF函数 也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。公式为= IF(logical_test,value_if_true,value_if_false) logical_test条件表达式，其结果要么为 TRUE，要么为 FALSE，可使用任何比较运算符； value_if_truelogical_test 为 TRUE 时返回的值；value_if_fal

16、selogical_test 为 FALSE 时返回的值。,例如，某企业对各个销售部门的销售业绩进行评价，评价标准及各个销售部门在2002年的销售业绩汇总如图2-16所示，评价计算步骤如下：,销售部门业绩评价,方法1： （1）选定单元格区域C3:C12。 （2）直接输入以下公式：“=IF(B3:B12100000,“差“, IF(B3:B12200000, “一般“, IF(B3:B12300000,“好“,IF(B3:B12400000,“较好“,“很好“)”。 （3）按“Crtl+Shift+Enter”组合键。 方法2： 直接在单元格C3中输入公式“=IF(B3100000,“差“,IF

17、(B3200000,“一般“,IF(B3300000,“好“,IF(B3400000,“较好“,“很好“)”后，将其向下填充复制到C4C12单元格中。,公式组合,常用函数,AND、OR和NOT函数 = AND（条件1，条件2，条件N） = OR（条件1，条件2，条件N） = NOT（条件） AND函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，否则，只要有一个条件不满足即返回FALSE。 OR函数表示逻辑或，只要有一个条件满足时，OR函数返回TRUE，只有当所有条件都不满足时才返回FALSE。 NOT函数只有一个逻辑参数，它可以计算出TRUE或FALS

18、E的逻辑值或逻辑表达式。如果逻辑值为 FALSE，函数 NOT 返回 TRUE；如果逻辑值为 TRUE，函数 NOT 返回FALSE。 这3个函数一般与IF函数结合使用,常用函数,AND、OR和NOT函数 某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%，则计算过程如下,（1）在单元格D3中输入公式“=IF(AND(B3300000,C3/B31%),15%,10%)”，将其向下填充复制到D4C10单元格中。 （2）选取单元格区域E3:E10，输入公式“=B3:B10*D3:D10

19、”，按“Crtl+Shift+Enter”组合键。 则各销售部门的销售奖金提成比例及奖金提取额如图2-17所示。,奖金提成比例及提取额的计算,逻辑函数,Vlookup,=Vlookup(lookup_value,table_array,col_index_num,range_lookup) lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。 table_array：数据源，命名数据源。 col_index_num：返回被找信息所在的列数。 range_lookup:是否精确匹配： true:-1相似匹配。 如果 range_lookup 为 TRUE，则 table_ar

20、ray 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。 false:0 精确匹配。 如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。,应用,获得更多的有关函数的信息,选择“粘贴函数”,选择函数类型,选择具体函数,查看函数说明,寻求更多的函数帮助,追踪单元格引用检查公式,追踪引用的单元格 追踪从属单元格 追踪单元格引用

21、错误 使用“公式审核”工具栏追踪单元格引用,审查公式,5. 透视表制作,透视表与统计,创建数据透视表 改变数据透视表的版式 创建数据透视图 刷新数据透视表 设置数据透视表的格式 字段与项的重命名 对项目排序 显示字段的顶端或底端项 使用总计和分类汇总 改变数据透视表的计算 用数据透视表合并计算数据区域,示例,6. 图表制作,图表制作,图表类型 柱形图 条形图 折线图 饼图 散点图 面积图 环形图 雷达图 股价图 ,图表经验,图表类型与使用数据 柱形图少于6项的数据比较 条形图6-8项的数据比较 折线图大于8项的数据比较或序列趋势性的数据 饼图数据内部占比性质比较 散点图趋势不明显，重点在看分布规律 面积图累积数据比较 环形图同饼图 雷达图同一主体的不同角度比较 股价图不同主体高中低区间型比较 ,图表准备,建议： 数据整理简洁，避免数据表承载数据过大 透视表结果最好拷贝另存数据做图 图表信息一目了然，避免备注太多 必要的背景图片可以虚做 ,END,thanks,Q&A MSN:psy- Tel:13901805998,