感
谢
关
注
导读
这两天有个朋友问我为什么有个表格的公式,每当插入列的时候结果就会出错,不是说公式里面的单元格引用在插入列的时候会自动调整变化吗?为什么插入列还会对公式结果造成影响呢?
今天,村长就以COLUMN函数为例,跟大家分享插入列对公式结果造成的影响。
在日常的工作中,我们经常会遇到一些嵌套了COLUMN函数的公式,例如下面这个公式
函数公式:
=VLOOKUP($A10,$A$1:$D$7,COLUMN(B1),0)
这里VLOOKUP函数的第3参数采用COLUMN(B1)是因为COLUMN(B1)返回的是B1所在列号B列的列号,也就是2,在公式右拉的时候COLUMN(B1)会依次变成COLUMN(C1)、COLUMN(D1),也就是依次返回C列和D列的列号,分别得到3和4,刚好是1月、2月、3月结果在$A$1:$D$7单元格区域中要返回的列数。
这样子写公式比较灵活,否则如果VLOOKUP函数的第3参数用固定数值2/3/4,那么公式将无法右拉,要每个单元格写一个公式,非常的不方便。
然而,这样子写函数公式也有一个弊端,我们可以看一下下面这个动画
我们发现当我们在A列前面插入一列空列的时候,1月对应的公式就由原来的
=VLOOKUP($A10,$A$1:$D$7,COLUMN(B1),0)变成了
=VLOOKUP($B10,$B$1:$E$7,COLUMN(C1),0)
查找的区域自动由$A$1:$D$7调整为$B$1:$E$7,往后移动了一列,VLOOKUP函数的第3参数也由COLUMN(B1)变成了COLUMN(C1),也就是由2变成了3,也就是说1月的结果将返回$B$1:$E$7单元格区域的第3列,然而我们都知道插入列后1月的结果依然是在$B$1:$E$7单元格区域的第2列,所以插入列后1月、2月、3月的结果都发生了错位,导致结果全部错误。
所以,针对这些问题,村长在这里跟大家分享一下我自己的函数使用心得:
1、如果只是自己使用的表格,而且是临时性的需要用函数公式引用数据,那么可以使用COLUMN或者ROW对公式进行嵌套;
2、如果是作为一个需要经常使用的固定模板,那么函数公式尽量不要用COLUMN或者ROW函数进行嵌套,原因就像我们前面演示的那样,嵌套了COLUMN或者ROW函数的公式,只要行列发生变化,就有可能会导致结果错误;例如上面这个例子我们可以使用稳定性更强的公式
=VLOOKUP($A10,$A$1:$D$7,MATCH(B9,$A$1:$D$1,0),0)
都说条条大道通罗马,但是这些大道既有大路也有小路,小路可能也是捷径,更快更简单,但是对于不熟悉道路的人来说,走小路很容易发生危险。
所以,不要以为自己使用的是别人设定好了公式的模板就可以高枕无忧,如果你不懂函数公式,请不要随意改变表结构,因为这样极有可能会导致你的公式结果发生错误。
未知只会让你如履薄冰,学习才能让你无所畏惧
跨越未知,走向未来,加油!
致各位亲
联系客服