《EXCEL在人力资源管理中应用-案例及技巧》由会员分享，可在线阅读，更多相关《EXCEL在人力资源管理中应用-案例及技巧（39页珍藏版）》请在金锄头文库上搜索。
1、-E*cel在管理中的应用案例01第1局部 E*cel根底概念1. 名称名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：插入名称定义，选中需要删除的名称点击删除按钮，点确定。名称的引用：需要引用*单元格时输入该单元格的名称：名称第1步：选中需要命名的*个单元格或单元格区域。第2步：在左上角名称框输入命名后回车。删除已有命名：选择插入名称定义；选中需要删除的名称，点击删除按钮。名称命名的优点：1) 防止绝对引用的错误2) 对公式进展文字化表述，让公式更加容易理解3) 可以在整个工作簿用，引用便2.
2、 常用引用函数row: 返回指定单元格的行号column: 返回指定单元格的列标match: 返回查找值在查找围中的序号 =match(查找值，查找围，0) 其第三个参数为0，表示查找准确值address: 返回单元格名称，其参数为行号列标或计算行号列标的表达式 =address(行号，列标) =address(1,1) 此公式返回A1indirect: 返回单元格的值.其参数为单元格名称 =indirect(A1),假设A1=10,则indirect返回10inde*: 在*区域查找*个位置的值 =inde*(查找区域，查找值所在的行号，查找值所在的列号)offset：指定基点，指定位移量，
3、得到单元格引用 单个单元格引用：=offset(基点单元格，向下移动的行，向右移动的列) 区域的引用：=offset(基点单元格，向下移动的行，向右移动的列，区域 包括的行数，区域包括的列数) 向上和向左移动时，位移量为负值。 =offset(A1,1,1) 将得到B2单元格的值3. 动态引用动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相似，但比简单的相对引用用途广泛。常用的动态引用的实现法有：引用函数嵌套。比方offset和row,column嵌套；inde*和match，indirect,address嵌套等；引用函数与控件结合使用，此类控件包括组合框，滚动条
4、。动态引用的作用：实现对于单元格的动态引用；进展动态分析；制作动态报表。4. 运算类型数值运算：1 + 1 = 2逻辑运算：1 0 = TRUE在逻辑运算中，TRUE=1，FALSE=0我们利用逻辑运算进展条件判断在E*cel中常用的一些逻辑函数：if,and,or,not,iserror等and: 只有当所有条件全部满足，才会返回true的逻辑值 语法：and(条件1，条件2，)or: 满足其中任一个条件，都会返回true的逻辑值 语法：or(条件1，条件2，条件3,)5. 函数调用的语法函数名称参数1，参数2，6. 绝对引用和相对引用：绝对引用：所引用的单元格不随着公式的复制而移动的引用式
5、。相对引用：所引用的单元格随着公式的复制而移动的引用式。改变引用式的法：1) 在公式栏中选中需要改变引用式的单元格，按F4键2) 给需要引用的单元格定义名称，然后在公式中引用该名称7. 数组公式对单元格区域进展多重计算的计算式。与普通计算公式的区别是录入公式完毕后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。如上图例，使用一个公式计算出所有产品的金额合计，引用的是所有的单价和所有的数量，执行的是多重计算。8. 错误提示E*cel中存在错误的类型，比方1/0=*DIV/0!, todas()=*NAMES!iserror是一个逻辑函数，用以判断*
6、个单元格的值是否是一个错误，是错误则返回TRUE,不是错误则返回FALSE.iserror有时可以和if函数嵌套进展一些较为复杂的判断。9. 有取值区间的随机数=最小值最大值最小值*rand()10. 循环引用是单元格引用其自身的引用式。可以设置E*cel允进展循环引用：工具-选项-重新计算：将迭代计算选项打钩选中。行列互换函数式：利用transpose函数+数组公式实现。首先选中行列数和原区域相反的一个区域；然后输入transpose函数最后按下组合键ctrl+shift+enter第2局部E*cel设置1 显示当前文件的完整路径菜单区域右键菜单Web2 显示菜单项全部菜单视图工具栏自定义选
7、项始终显示整个菜单3 鼠标移动向工具选项编辑按Enter键后移动向4 隐藏界面要素工具选项视图包括：网格线，滚动条，工作表标签，行号列标等。5 以显示值为准工具选项重新计算以显示精度为准行的合计与列的合计有时出现不相等的情况。可以采用以下法解决。选择工具选项重新计算以显示精度为准，选中该选项。6 自定义序列工具选项自定义序列7 改变文件保存位置工具选项常规默认文件位置8 改变文件用户名工具选项用户名9 单元格自动换行格式单元格对齐自动换行第3局部 E*cel根底操作1. 快速选中数据表的整行或者整列ctrl + shift + 下箭头/右箭头2. 快速选中区域ctrl + shift + 83
8、. 行列互换复制需要进展行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴选中转置选项4. 一个单元格容输入为多行alt + 回车键5. 显示公式ctrl + 6. 冻结窗口选择需要进展冻结的单元格位置，选择窗口冻结窗格7. 自定义格式语法： 大于条件值格式；小于条件值格式；等于条件值格式；文本格式8. 缩放数值 0.00， 按百万缩放 0.0，按万缩放 0.00, 按千缩放9. 不复制隐藏的行或列a首先选中需要复制的被隐藏了一些行或列的表格区域；b 然后点击编辑-定位-定位条件，在其中选择可见单元格；c 复制表格区域，粘贴即可。案例02 一 文本的处理1. 等长文本的分割从字符串的左边
9、取字符：=Left(字符串，文本长度)从字符串的右边取字符：=Right(字符串，文本长度)从字符串的中间取字符：=Mid(字符串，文本起始位置，文本长度)2. 不等长文本的分割第1步：选中要进展分割的字符串区域。第2步：点击数据分列，在文本分列向导中选择适合的分隔符。第3步：设置需要导入的列以及放置该列的位置。3. 文本的合并=Concatenate(文本1，文本2，)使用连接符&：动态表头：=ABC公司&YEAR(NOW()&年&MONTH(NOW()&月报表4. 有效性选中需要设置有效性的区域，点击数据有效性，在有效性条件中选择序列，在来源中录入或选取列表。输入信息：事前提醒。出错警告：
10、事后提示。如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：=该列表的名称5. 有效性的其他用法不允录入重复数据的有效性设定：countif(e:e,e12)=1输入的容中必须包括*字符：=not(iserror(find(中国,g30)二级选项：首先将一级选项的每个工程定义一个名称，该名称容包括相应的二级工程；制作一级工程的有效性；制作二级工程的有效性：容为：=INDIRECT(g6)，其中g6为设定了有效性的一级选项所在的单元格。6. 圈示无效数据。选择工具公式审核显示公式审核工具栏，点击圈示无效数据按钮。该工具可以将所有不符合有效性设定的容圈示出来。二日期
11、的处理1. Datedif 函数：用于计算两个日期之间的年数，月数，天数。=Datedif(开场日期，完毕日期，y)第三个参数：y：表示年数m：表示月数d：表示天数2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2),DAY(G2),d)3. *日期的星期=weekday(A2,2) 返回的值是3，则表示该日期是星期三。4. 两个日期间的工作日天数=networkdays(开场日期，完毕日期，节假日列表)需要首先加载分析工具库才能使用此工具案例03数据查询1. VLOOKUPVLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数
12、组当前行中指定列处的数值。VLOOKUP 中的 V 代表垂直。VLOOKUP(lookup_value,table_array,col_inde*_num,range_lookup)Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该围的第一列。col_inde*_num 为需要调转的数据位于第二个参数中定义的围的第几列。必须为单纯数值。range_lookup 定义大致匹配
13、或准确匹配。False或0:准确匹配；true或忽略或1：如果无法找到准确匹配的值，则就查找并匹配比查找值小的最近似的值。2. HLOOKUP()Hlookup中的H是水平的意思，在横向排列的表格中查询数据用Hlookup，垂直排列的表格查询用Vlookup函数。其参数含义与VLOOKUP近似。案例04 查询表信息查询表利用有效性和VLOOKUP函数实现。利用有效性实现索引字段的切换。利用VLOOKUP函数查询与索引字段相关的信息。案例05 数据筛选1. 自动筛选略2. 高级筛选由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进展筛选就需要采用高级筛选的式。在需要进展筛选的数据表外设
14、定筛选条件。在对话框中设定：列表区域为数据表区域；条件区域为刚刚在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。3. 条件格式条件格式可以使得符合特定条件的记录按照*种设定的格式显示。1) 选中要进展条件格式设定的单元格围2) 执行【格式】【条件格式】3) 在条件格式设置界面进展条件的设定：4. 统计Frequency(需要进展统计的数据区域，分段点)对数据进展区间统计。此函数需要和数组公式一起使用。此函数是根据数值的大小划分区间进展统计的。列出需要分析的工资区间，选中需要计算统计结果的单元格区域，输入函数：按下组合键：ctrl + shift + enter5. 排名次=rank(需排名数值，排名数据围，排序式)排序式：0或忽略降序，否则为升序。案例06 窗体控件的使用1. 下拉菜单的制作。选择视图工具栏窗体，出现窗体工具条，在工具条上选择组合框。按下鼠标左键，拖放出适宜的大小后释放鼠标，出现下拉菜单形状。选中该下拉菜单，右键选择设置控件格式在控制菜单上，去选择相应的数据源。三维阴影选项是下拉菜单显示效果设定。单元格：该处指定的单元格中将存储在控件中被选中的工程在数据源中的序列号。我们经常利用该序列号引用控件选中的值，还可以利用该值制作动态图表。根据报销单上填报的日期自动生成该报销单的起止日期。使用最大值
