EXCEL在人力资源管理中应用案例及技巧Word文档下载推荐.docx
《EXCEL在人力资源管理中应用案例及技巧Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《EXCEL在人力资源管理中应用案例及技巧Word文档下载推荐.docx（129页珍藏版）》请在冰豆网上搜索。
2.常用引用函数
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
逻辑运算：
1>
0=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，…）
6.绝对引用和相对引用：
绝对引用：
所引用的单元格不随着公式的复制而移动的引用式。
相对引用：
所引用的单元格随着公式的复制而移动的引用式。
改变引用式的法：
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设置
1显示当前文件的完整路径菜单区域－右键菜单－Web
2显示菜单项全部菜单
视图－工具栏－自定义－选项－始终显示整个菜单
3鼠标移动向工具－选项－编辑－按Enter键后移动向
4隐藏界面要素工具－选项－视图
包括：
网格线，滚动条，工作表标签，行号列标等。
5以显示值为准工具－选项－重新计算－以显示精度为准
行的合计与列的合计有时出现不相等的情况。
可以采用以下法解决。
选择“工具”－“选项”－“重新计算”－“以显示精度为准”，选中该选项。
6自定义序列工具－选项－自定义序列
7改变文件保存位置工具－选项－常规－默认文件位置
8改变文件用户名工具－选项－用户名
9单元格自动换行格式－单元格－对齐－自动换行
第3部分Excel基础操作
1.快速选中数据表的整行或者整列ctrl+shift+下箭头/右箭头
2.快速选中区域ctrl+shift+8
3.行列互换复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴－选中“转置”选项
4.一个单元格容输入为多行alt+回车键
5.显示公式ctrl+~
6.冻结窗口选择需要进行冻结的单元格位置，选择窗口－冻结窗格
7.自定义格式语法：
[大于条件值]格式；
[小于条件值]格式；
[等于条件值]格式；
文本格式
8.缩放数值0.00，，按百万缩放0“.”0，按万缩放0.00,按千缩放
9.不复制隐藏的行或列a首先选中需要复制的被隐藏了一些行或列的表格区域；
b然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；
c复制表格区域，粘贴即可。
案例02
一文本的处理
1.等长文本的分割从字符串的左边取字符：
=Left（字符串，文本长度）
从字符串的右边取字符：
=Right（字符串，文本长度）
从字符串的中间取字符：
=Mid（字符串，文本起始位置，文本长度）
2.不等长文本的分割第1步：
选中要进行分割的字符串区域。
点击“数据”－“分列”，在文本分列向导中选择适合的分隔符。
第3步：
设置需要导入的列以及放置该列的位置。
3.文本的合并=Concatenate（文本1，文本2，…）
使用连接符&
：
动态表头：
="
ABC公司"
&
YEAR（NOW（））&
"
年"
MONTH（NOW（））&
月报表"
4.有效性选中需要设置有效性的区域，点击“数据”－“有效性”，在有效性条件中选择“序列”，在来源中录入或选取列表。
输入信息：
事前提醒。
出错警告：
事后提示。
如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：
“=该列表的名称”
5.有效性的其他用法
不允录入重复数据的有效性设定：
countif（e:
e,e12）=1
输入的容中必须包括某字符：
=not（iserror（find（"
中国"
g30）））
二级选项：
首先将一级选项的每个项目定义一个名称，该名称容包括相应的二级项目；
制作一级项目的有效性；
制作二级项目的有效性：
容为：
=INDIRECT（g6），其中g6为设定了有效性的一级选项所在的单元格。
6.圈示无效数据。
选择“工具”－“公式审核”－“显示公式审核工具栏”，点击“圈示无效数据”按钮。
该工具可以将所有不符合有效性设定的容圈示出来。
二日期的处理
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.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函数查询与索引字段相关的信息。
案例05数据筛选
1.自动筛选（略）
2.高级筛选
由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的式。
在需要进行筛选的数据表外设定筛选条件。
在对话框中设定：
列表区域为数据表区域；
条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。
3.条件格式
条件格式可以使得符合特定条件的记录按照某种设定的格式显示。
1）选中要进行条件格式设定的单元格围
2）执行【格式】【条件格式】
3）在条件格式设置界面进行条件的设定：
4.统计
Frequency（需要进行统计的数据区域，分段点）对数据进行区间统计。
此函数需要和数组公式一起使用。
此函数是根据数值的大小划分区间进行统计的。
列出需要分析的工资区间，选中需要计算统计结果的单元格区域，输入函数：
按下组合键：
ctrl+shift+enter
5.排名次=rank（需排名数值，排名数据围，排序式）排序式：
0或忽略降序，否则为升序。
案例06窗体控件的使用
1.下拉菜单的制作。
选择“视图”－“工具栏”－“窗体”，出现“窗体”工具条，在工具条上选择“组合框”。
按下鼠标左键，拖放出合适的大小后释放鼠标，出现下拉菜单形状。
选中该下拉菜单，右键选择“设置控件格式”
在“控制”菜单上，去选择相应的数据源。
“三维阴影”选项是下拉菜单显示效果设定。
单元格：
该处指定的单元格中将存储在控件中被选中的项目在数据源中的序列号。
我们经常利用该序列号引用控件选中的值，还可以利用该值制作动态图表。
根据报销单上填报的日期自动生成该报销单的起止日期。
使用最大值函数MAX和最小值函数MIN。
制作单选按钮。
使用窗体工具条上的“选项按钮”
2.界面定制
行号列标，滚动条，工作表标签的隐藏。
选择“工具”－
升级会员 