交叉查找的另一种思路,利用indirect+自定义名称实现查找 查找公式比较直观 图文

2018年3月26日10:50:21 1 2,805 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

查找公式?你首先会想到Vlookup函数、Match函数、Lookup函数,武功的最高境界是无招胜有招,而今天兰色介绍的查找公式也是什么函数都不需要。

【例】如下图所示,要求根据费用和部分,从上表中查找对应的金额。

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

分析:实现上面的查找,可用的公式太多了。如:

=VLOOKUP(A10,A1:G6,MATCH(B10,1:1,),0)

=INDEX(A1:G6,MATCH(A10,A1:A6,0),MATCH(B10,A1:G1,0))

=LOOKUP(1,0/(A10=A2:A6),OFFSET(A2:A6,,MATCH(B10,B1:G1,0)))

是不是看上去都眼花缭乱的,有点晕。看看下面这个公式怎么样:

=差旅费 部门3

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

我类那个天,怎么可以这么短。

你看到的没错,看上去很复杂的查找,只需要用

= 列标题 + 空格 + 行标题

这样设置公式还有一个很牛的用处:当你双击公式单元格时,会在上表中用颜色框标出你所查找的值:

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

等等....我也要去试。别....你会出错的。因为还有一个关键步骤没有做:

选取表格 - 公式 - 定义的名称 - 根据所选内容创建 - 确定

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

公式的原理:

  • 空格在excel中也可以作运算符使用,叫交叉运算符,它可以返回前后两个区域交汇处的区域或值。

  • 通过定义名称,可以批量的按行和列标题定义名称。每个行标题和列标题都有相对应的名称和区域。通过 公式 - 名称管理器可以看到。(按shift全选点删除即可删除所有名称)

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

这个公式有一个小缺陷,就是不能直接引用条件并进行复制,如:

=A10 B10

比Vlookup 函数简单10倍,Excel最牛查找公式来了!

解决方法:使用Indirect函数,即:

=INDIRECT(A10) INDIRECT(B10)

还有一点需要注意,定义的名称中如果以数字、字母C和R开头(另外也不能和单元格地址相同,如A1,B100),名称最前会自动添加下划线,如1月会定义为_1月,所以在公式中数字前也要加下划线。如:

=工资 _1月

兰色说:看完今天的教程,是不是觉得很意外:excel公式竟然还可以这样用。其实空格只是excel的一个基本运算符,只是大部分人没有关注过它。只是这个公式限制有点多,如果不是特殊的标题,这个还是很好用的。

End.

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!

目前评论:1   其中:访客  1   博主  0

    • 匿名 9

      选取表格-公式-定义名称-根据所选内容创建时为什么出现是否替换现有的定义?