image
时年 34 岁的 Excel 叔叔函数 VLOOKUP 要退休了。据微软统计，这是Excel中排名仅位于 SUM 和 AVERAGE 之后第三常用的函数 VLOOKUP。
让我们再来回顾一下VLOOKUP的使用方法吧。
image
PART
01
常用的查找与引用函数Excel中的查找与引用函数有多种，这里按照使用频率的高低和与工作的相关性大小，介绍几个常用的查找与引用函数。如下表所示。
| 函数名称 | 语法格式 | 功能说明 |
| VLOOKUP | VLOOKUP(lookup_value, table_array,
col_index_num, range_lookup) | 在数据区域的第一列中纵向查找指
定值，返回指定列号所对应的值 |
| MATCH | MATCH(lookup_value, lookup_array,
match_type) | 返回符合特定值、特定顺序的项在
数组中的相对位置 |
| INDEX | INDEX(array, row_num, column_num)或
INDEX(reference, row_num, column_num, area_num) | 返回表或区域中的值或对值的引用，前者是数组形式语法格式，后者是引用形式的语法格式 |
| INDIRECT | INDIRECT(ref_text, [a1]) | 返回文本字符串所指定的引用 |
| OFFSET | OFFSET(reference, rows, cols, height, width) | 以指定的引用为参照系，通过给定
偏移量返回新的引用 |
| CHOOSE | CHOOSE(index_num, value1, value2) | 根据给定的索引值，从参数串中选
出相应值。 |
image
PART
02
MATCH和INDIRECT函数
1、****MATCH函数的用法
在使用MATCH函数时，要求对区域中的行和列中的值是按照某种顺序排列的，如下表所示的A1：E5单元格区域，每一行或每一列的数据均以升序排列。
如果数据不按照有规律的顺序排列，在使用MATCH函数的时候就会显示“#N/A”错误。G2单元格中公式“=MATCH(50, C1:C5, 1)”返回的结果是5，这一公式表示在C1：C5这一列中查找“50”这个数字的位置，结果显示它位于C1：C5区域中顺数第5个单元格。第三个参数“1”表示该列数值按升序排列，如果是降序排列，则应该是“-1”。
<colgroup><col style="width: 52pt;" width="69" span="7"></colgroup>
| 20 | 25 | 30 | 35 | 40 |
|
|
| 25 | 30 | 35 | 40 | 45 |
| 5 |
| 30 | 35 | 40 | 45 | 50 |
|
|
| 35 | 40 | 45 | 50 | 55 |
|
|
| 40 | 45 | 50 | 55 | 60 |
|
|
2、****INDIRENCT函数的用法
INDIRENCT函数分为加引号和不加引号两种引用形式，其中加引号是对文本的引用，不加引号是对地址的引用。如果所引用的地址中没有相应的文本内容，则会返回错误值“#REF!”。如下表所示，在引用结果区域中分别应用了不同形式的公式显示出不同的结果。
<colgroup><col style="width: 52pt;" width="69" span="5"></colgroup>
| 数据区域 |
| 引用结果 |
| B2 | 数据之魅 |
| 数据之魅 | #REF! |
| 数据科学 | 魅力无限 |
| 数据科学 | 数据之魅 |
D2=INDIRECT(A2)
D3=INDIRECT(“A3”)
E2=INDIRECT(B2)
E3=INDIRECT(“B2”)
image
PART
03
万能的查询函数——VLOOKUP
在众多的查找和引用函数中，VLOOKUP函数是使用最频繁的，它是创建查询表的最好工具。如果你去面试的岗位要求中标明了熟练Excel的话，面试的时候基本都会问你使用过VLOOKUP吗？所以我们今天来通过一个实例来学习VLOOKUP。
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
其中参数解释如下：
lookup_value——需要在数据表第一列中进行查找的数值，可为数值、引用或字符串； table_array——需要在其中查找数据的数据表，使用对区域或区域名称的引用； col_index_num——table_array中将返回的匹配值的列号，col_index_num为1时，返回table_array第1列的值，col_index_num为2时，返回table_array第2列的值，以此类推； range_lookup——为一逻辑值，指明查找时是精确匹配还是近似匹配。如果为FALSE或0，则进行精确匹配，如果找不到，则返回错误值“#N/A”；如果为TRUE或1，将查找近似匹配值，参数省略时默认为近似匹配。
image
PART
04
VLOOKUP实例应用
1****、描述
一般企业都会有销售部门，管理部门的总经理会经常抽查销售部门的业绩情况，他担心部门经理在每季度汇报工作的时候虚报数据来人为拔高其部门的工作能力，因此年底时，总经理都会从基层的数据分析师处取得一张员工的销售记录表，用来了解实际情况。如下表所示的表格记录了销售部门20名应该在过去一年的销售业绩。
image
2****、案例分析
虽然只有二十条记录，但是有时候我们并不想全部看完，而只想随意抽查某些员工，来了解该部门的业绩分布。该表中列数太多，不便于横向查阅，因此我们可以为该表做一个简单的查询系统。
3****、案例操作
第1****步：****新建查询表。在原工作薄中新建“查询表”，并输入表格内容，优化表格样式，效果如下图所示。
| 员工个人查询表 |
| 员工编号 |
| 一季度销售额 |
|
| 员工姓名 |
| 二季度销售额 |
|
| 工龄 |
| 三季度销售额 |
|
| 排名 |
| 四季度销售额 |
|
| 奖金 |
| 年度总销售额 |
|
第2****步：****定义区域名称。在“原表”中，选取区域A1：M21，然后在“公式”下的“定义的名称”组中单击“定义名称”按钮，在弹出的“新建名称”对话框中的“名称”文本框输入“数据区域”，单击“确定”。如下图所示，此步骤是为了给所选定的区域命名，方便后面的操作中对该区域的引用。
image
第3****步：****添加下拉列表。切换至“查询表”中，选中B2单元格，然后在“数据”下单击“数据工具”组中的“数据验证”，在弹出的对话框中设置允许的验证条件为“序列”，然后在“来源”文本框引用“原表”中的A2：A21区域，即“员工编号”列，如下图上所示，此步骤是为了在B2中创建下拉列表，列表内容就是员工的编号，如下图下所示。
image
image
第4****步：****输入查询公式。在B3单元格输入公式“=VLOOKUP(2, 数据区域, 2)”，如下图所示，在该公式中，“
2”是需要查询的员工编号，“数据区域”是第2步中定义的区域名称，代表原表的A1：A21区域，以“员工编号”作为第一列，则“员工姓名”列为第2列，所以将第3个参数设置为“2”。
image
第5****步：****复制公式并修改参数。复制B3里的公式，粘贴到其他单元格，只需要修改第3个参数即可，如“年度总销售额”位于第11列，所以第3个参数为“11”，如下图所示。（由于还没有选择员工编号，所以所有的值都为“#N/A”）
image
第6步：****查看结果。修改之后，我们直接在B2的下拉列表选择员工编号，如R1009，则会在其他单元格自动显示编号对应的其他信息。如下图所示。
image
延伸
VLOOKUP用于纵向查找，如果要横向查找，则需要用HLOOKUP函数，它的语法格式为HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)，各参数的意义与VLOOKUP类似，但是注意，第三个参数是不同的，该参数返回的是在区域中的第几行。
4、决策分析
建立查询表之后，如果想抽查员工在上一年的业绩情况，就很简单方便了，这不仅实现了对数据的随机查询，而且简化了原本复杂的查询工作。这样会对我们的工作效率很有帮助。
欢迎关注，访问更多精彩：数据之魅。
如需转载，请联系授权，谢谢合作。
网友评论