各位朋友,你们好。今天和你们分享的,不仅是函数的用法,更重要的是一种思维方式。
有朋友遇到一个问题:用函数查找数值,找到后要用于制作数据有效性,为什么OFFSET函数可以,VLOOKUP、LOOKUP、INDEX函数却不可以呢?
欢迎继续往下看。
写在前面
如下图所示,通过1行至11行的数据,判断【周辉煌】的职务。
这个题目很简单,我们可以用LOOKUP、VLOOKUP、OFFSET、INDIRECT、INDEX函数来实现,比如:
=VLOOKUP(B14,B2:F11,4,)
或者
=OFFSET(E1,MATCH(B14,B2:B11,),)
虽然这两个函数结果一样,但是背后却有着本质上的区别。
查找与引用
熟悉函数的朋友都知道,上面两个函数叫做查找与引用函数。可查看Excel的函数分类(见下图):
那么这个查找和引用,究竟有哪些区别呢?
一、什么是查找
查找的定义:在一些(有序的/无序的)数据元素中,通过一定的方法找出与给定关键字相同的数据元素的过程叫做查找。也就是根据给定的某个值,在查找表中确定一个关键字等于给定值的记录或数据元素。
从定义中,我们可以看出两点:
1、查找的对象是数据(或区域、数组)中的元素;
2、查找返回的结果是一个具体的元素(可以是数值、字符串)。
在前面的例子中,用VLOOKUP函数查找,返回的就是【销售】这个字符串。
二、什么是引用
参照查找的特点,我总结引用的特点如下:
1、引用的对象是一个单元格或连续的单元格区域;
2、引用返回的是一个对象(一个单元格或连续的单元格区域),只是在公式结果中,体现出来的是该对象的赋值(单元格中的值)。
在前面的例子中,引用函数OFFSET并不知道所要的数据在那个位置,所以需要Match函数配合,先找到数据所在的位置,然后再通过引用函数引用该单元格地址,并提取出单元格中的值。
查找和引用的相同点、不同点
一、相同点:
1、都可以找到结果,并返回结果;
二、不同点:
1、查找函数是根据条件在多个数据中直接找结果;
2、引用函数是先通过查找函数找到具体位置,再对相应位置的单元格引用,然后得到结果;
3、查找函数的结果是值,引用函数的结果是对象。
针对第3条,我给大家演示一个例子:
如上图所示,是图片联动中的案例。如果要查找的内容不是图片,而是数值的话,图片中红色框中的公式都可以使用(还可以再增加一个LOOKUP函数)。但是如果将数值换成图片对象,那么哪些函数能用?哪些函数不能用呢?我们直接看动图演示:
由于这里,数据有效性的的来源须是连续的单元格区域或者手动录入的数组。但是VLOOKUP查找函数的结果只是数值,就会提示引用无效;换成任意引用函数,就没有问题。
这就是查找函数与引用函数的本质区别。
怎么判断查找函数与引用函数
通过前面讲到的查找函数与引用函数的特点,我们可以从函数帮助文件中或许信息:
比如【INDEX】函数的帮助文件中是这样写的,很明显这个就是引用函数:
再比如【VLOOKUP】函数的帮助文件,在帮助文件中,直接明确告诉我们,VLOOKUP函数返回的只是值。
下表内容,是我整理出来的查找函数和引用函数(字符串查找不在此类函数中):
关于查找函数和引用函数,今天就讲到这里。
今天主要讲的是查找函数和引用函数的区别,其实,在绝大部分情况下,这两种函数通过其他函数配合、嵌套使用都是可以达到相同的效果的,只是在有些特殊情况下,会存在一定的影响,所以在选择和使用函数的时候,要注意下使用场景。
感谢你们的关注和支持!我是上班下班,我们下次再见。