避开VLookup和查找函数。这是Excel中最好的查找公式!

今天我就重点讲解一下Excel中这个不为人知的“交叉算子-空间”。

我们先来看一张图,了解一下在Excel中使用INDEX、VLOOKUP、HLOOKUP、LOOKUP实现“交叉查询”有多麻烦:

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

别说是初学者的白。用了很多年的Excel老手,一不留神就出错了。更可怕的是,各组函数之间的相互调用和混合引用使得调试和查错变得非常困难。

如果你觉得这些函数写不出来,试试“空格”操作:

交叉算子的使用场景和兼容性;

利用交叉算子使用间接函数的技巧:

Excel中剩余的2个引用运算符;

空间算符,这是一个超高效的技能击杀LOOKUP系列,而且极高,兼容性超强。把表情包带走吧~

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

快来和小北一起学习这个鲜为人知的Excel交叉算子吧~

–01 –

参考运算符

在Excel中,实际上有四种类型的运算符:算术、比较、文本和引用。这条推文就是“参考运营商”之一。

“引用运算符”可以分为三种类型,其中两种是您经常使用的,如下所示:

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

这里不解释冒号和逗号。下面重点说一下“空间”是如何计算的,也就是返回“共享单元格”。

那么哪个单元格是公式=B1:B8 B6:J6的结果呢?不是他们共同的部分,也就是下面的B6牢房。

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

关于这个交叉算子的基本运算规则,了解了它的原理之后,我们来试试如何高效的使用它。

–02 –

空格+单元格命名

在之前的推文中,我们尝试使用空格+单元格命名来获取交叉查询的数据。这里,我们再巩固一下。

首先,快速选择二维表格,然后单击“公式”选项卡下的“从选定内容创建”。

最后,检查“第一行”和“最左边的列”并单击确定,如下面的GIF所示:

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

此时,数据源的“最左边的列”和“最上面的列”会自动添加到命名区域中。点击“名称管理器”快速查看。

接下来怎么用?很简单。比如要查询4部门产品6的销量,可以直接用公式“= 4部门产品6”一键完成查询。

没错,中间用一个空格连两个字就行了。效果如下:

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

回过头来用查找数列对比之前的公式,是不是瞬间太简化了?没有比较,就没有伤害:

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

继续优化这个公式。细心的朋友可能已经发现,B14和C14单元格是“部门4”和“产品6”。

所以可以直接引用这两个单元格吗?比如:=B14 C14,能不能返回一个正确的结果?很遗憾,答案是否定的。

Vlookup、Lookup函数全让开,这才是Excel中最牛的查找公式!

这是为什么呢?因为单元格的内容本质上是“文本”,而直接写在编辑栏里的一般是“变量”。

两者看起来一样,其实不一样!接下来,我们将使用一个函数来解决这个问题,并将文本转换为变量。

–03 –

间接功能

INDIRECT的作用是返回文本字符串指定的引用。总共有两个参数,你只需要理解第一个,就是带引号的字符串。

这句话听起来可能很绕口。记住我们上面的公式,将公式修改为“=INDIRECT(B14) INDIRECT(C14)”:

这样我们就把字符串转换成带引号的变量,销量的结果可以随着部门和产品的变化而动态变化。

另外,还有一点比较重要。如果有任何以数字、字母C和R开头的已定义名称,名称的第一部分将自动加下划线。

比如将“一月”定义为“_一月”,这样我们在公式前的引用也会变成“_一月”。

小总结:如果题目不特别,那么可以考虑使用“空格符”。如果需要动态引用单元格的内容,可以使用INDIRECT函数。