excel函数indirect的复杂应用

 

函式及其用途

为了对表格中带有中文的URL进行转码,我首先将带有中文的单元格筛选了出来。这里顺便提一下如何判断单元格中是否包含文字:=IF(LEN(F112)=LENB(F112),"不含汉字","有汉字")

然后将这些URL复制到新的表,用函数将带中文的单元格进行了转码。可是如何将处理过的内容粘贴回筛选状态的总表里面呢?

列表在进行过筛选后是无法直接把数值粘贴进来的。尝试过定位可见单元后粘贴,仍然出错。在一个论坛里找到了答案。见下面的例子:

目标:将分表李雷的数据汇总到总表中

方法:

  1. 在总表对李雷进行筛选;
  2. 在总表D3中输入=INDIRECT(B3&"!D"&COUNTIF(B$1:B3,B3)+2)
  3. 然后下拉

但是这个函数是什么意思呢?我要如何应用到自己的表格呢?研究了两小时,终于破解了。

函式分解

因为这是个嵌套函数,要搞清楚函式各部分的作用。我分别百度了:INDIRECT, INDIRECT &, INDIRECT !B, COUNTIF, INDIRECT +1。弄清楚这些,就知道如何修改这个函数,应用到自己的表格里面了。

&符号

不好意思,其实我是EXCEL小白,&符号其实和在他处用法一致,就是起到连接的作用。

B3&"!D"

例子中B3的值刚好就是李雷这个表的名称。所以这个其实就是表示李雷!D,这个我们应该都知道是表示李雷这个表的D列。如果李雷这个表是默认的名称sheet2呢?就应该将这句换成:"sheet3!D"

B$1:B3

代表B列第一行至第3行,前者坐标不会因为拖动而改变,后者往下拉会自动增长。

Countif

这个函数是用来统计某数据区域中指定值出现的个数。例子中COUNTIF(B$1:B3,B3)的结果就是1。

+2

COUNTIF(B$1:B3,B3)的结果是1,那么+2以后就是3。这个值3在INDIRECT函式中的意思就是第3列的意思。那为什么+2呢?因为例子中李雷这个表的表头占两行,因此对应的位置要+2

 

至此,这个复杂等式就变成:=INDIRECT("sheet3!D3") 这样不难理解了吧。

INDIRECT函数

Indirect 这个函数,我是第一次用。看了几个有关indirect 的描述,都无法搞清楚下面这个复杂公式的涵义,这让我很焦虑。但看完下面这个例子就基本上懂了, 我的理解是直接或者间接取值,或取坐标。:

 

我们要取得A1中的值, 其实非常简单,直接=A1。但如何用INDIRECT函数来完成呢?有两种方法:

  • =INDIRECT(B1)
  • =INDIRECT("A1")

以上两个函数都能取得值:1982属狗,处女座。然而不带""的函数,我们可以使用拖拉的方式对后面的单元格轻松进行取值。带""时,表示先定位A1单元格,然后取其内容。不带""号时呢,表示先取该单元格内容,然后定位内容所指单元格。是不是有点绕?不然怎么叫Indirect呢。

应用

根据以上理解,在自己的表格上应用过后,结果正确,说明我这段分析成功了。经过这次,我发现将复杂变简单是一项非常重要的技能。简单的记忆性的能记住就记住,忘记了百度很快能搜索出来。但是,你如果直接搜索像这样复杂的事情,搜索引擎还是没有这么智能的。

=INDIRECT(G112&"!B"&COUNTIF(G$1:G112,G112)+1)

后记

虽然要达到我的目的还有其他一些方法,但是学会了这个复杂函数还是觉得受益良多。我还是比较迷恋EXCEL的,总觉得没有它无法完成的任务。虽然不求能VBA,但为今后学习一些复杂的式子打了点底子,能快速将别人的式子应用到自己的表格已经是很不错技能了。

 

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: