EXCEL在人力资源管理中应用(案例与技巧)

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