原标题:《用了十多年Excel,只知道这个高能函数,别轻易用!》 “
函数是Excel中一个非常高能的函数。 同时它的语法非常简单,只有两个参数,第二个参数可以默认。
关于功能,陆水岭老师在《偷学这个小众高能功能,我再也没有加班过……》一文中有详细讲解。
但即便如此,一旦实践,该功能对于很多朋友来说仍然是一个“噩梦”!
本篇小花就为大家分析一下函数常见的误区,相信一定能为大家一扫阴霾!
1.关于引用样式
问题的来源是一位粉丝的留言。
消息中引用的公式如下:
=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)
说实话,不仅是这位小伙伴,我们几乎都习惯了函数省略第二个参数a1的样子,以至于当它采用R1C1引用风格的时候,我们都认不出来了!
Excel 单元格地址有两种引用样式:
❶ A1引用方式:英文字母代表列号,数字代表行号,默认为相对引用,“$”代表绝对引用;
❷ R1C1引用方式:用R+数字表示行号,C+数字表示列号,默认是绝对引用,用“[]”表示相对引用。
两者的对比如下:
留言中提到的公式中,参数a1为FALSE,表示使用了R1C1引用样式。 两个MATCH通过匹配条件值的序号连接R和C,形成完整的R1C1引用样式,然后根据地址进行引用求值,最终完成交叉查询!
公式如下:
=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)
所以,即使大多数时候,省略了第二个参数a1,我们还是不能轻易忘记!
2.关于参考地址
唯一可以正确处理的是代表引用地址的文本,绝大多数函数应用错误都集中在引用地址文本的构造上。
下图说明了构建参考地址文本的几种方法。
简单来说,对于前四种类型,无论你直接将函数的第一个参数设置为文本、单元格引用还是公式,只要最终能返回一个代表引用地址的完整文本,函数就可以运行正确。
方法五直接将需要引用的单元格B1作为参数,公式会先引用B1的值得到2,但是数字“2”不是完整的引用地址,导致函数无法计算。
这与第二种情况下直接使用文本“B2”作为参数不同。 后者不会进一步计算文本“B2”,文本“B2”是引用地址。
在情况①中,引用单元格A1作为参数,公式先引用A1的值得到“B2”,同样可以正确计算。
第五个案例是函数应用中的一个常见错误。 你踩过雷吗?
3.关于单引号
使用函数进行跨表引用是另一个错误重灾区!
即使引用地址清晰准确,函数还是无法计算出来!
为什么是这样?
错误公式如下:
=INDIRECT("1 月 广州B2")
这是因为有些工作表名称中包含一些特殊字符,如空格、星号等,导致函数无法识别工作表名称。 在这种情况下,您需要用单引号“'”将工作表名称括起来,以便函数能够正确识别它。
修正后的公式如下:
=INDIRECT("'1 月 广州'!B2")
那么如何判断是否需要加单引号呢? 很简单,用等号引用目标工作表的任意一个单元格,检查公式中是否包含单引号。
其实不需要单引号的时候可以用单引号,也能正确计算。
因此,当需要引用多个工作表时,加单引号是安全的!
4.关于跨表引用
让我们看看下面的例子。 小张需要计算三个特定城市当日销量的最大值,即需要在多个不连续的表中引用同一个单元格,然后求最大值。
小张努力设置了跨表引用公式,但结果是错误的。 让我们看看出了什么问题。
跨表引用错误的公式如下:
{=MAX(INDIRECT($D$2:$D$4&"!B2"))}
通过数组运算,($D$2:$D$4&"!B2")根据指示分别引用惠州、佛山、东莞三张表的B2单元格,然后用MAX函数求最大值. 这个公式似乎是正确的。 地方。
BUG是跨多表引用的结果是多维引用。 MAX函数无法对这个多维引用进行操作,只能返回第一个值,即!B2,导致结果出错。
一般来说,对多维引用的操作不使用特定的函数是无法直接进行的。 这些具体函数包括T函数、N函数、SUMIF函数、函数等。
本例中,我们只需要使用N函数,将函数的多维引用结果转换成数值形式,MAX函数就可以正确运行。
跨表参考校正公式如下:
{=MAX(N(INDIRECT($D$2:$D$4&"!B2")))}
跨多个表引用函数的方式很深。 有兴趣了解的可以期待小花的后续文章!
以上是小花拆解的功能常见的误区,包括:
❶ 忽略参数a1导致无法理解R1C1引用样式下的函数;
❷ 错误的构造导致参数不完整,代表参考地址的文字;
❸ 不加单引号,函数无法正确识别表名;
❹ 没有使用特定函数处理多维参考结果,导致无法操作其他嵌套函数。
看过本文的小伙伴们,坑功能不能再踩了! 如果还有其他小花没有提到的函数常见错误类型,欢迎留言与我们沟通!