Excel中COLUMNS这函数把我坑死啦

职场   2024-09-30 00:18   江苏  
今天给公司维护模板,被COLUMNS坑死了,下面来给大家分享一下,避避坑!


▍事项背景

简单模拟一下数据,其中有一列数据,使用了公式,比如下面的VLOOKUP函数,实际数据由于列数较多,当时懒得数,就用了COLUMNS来替代!

目前这个函数公式完全没有问题,还可以在数据源中新增列,动态更新!
▼第三参数COLUMNS
=VLOOKUP(E2,Sheet2!$A:$AD,COLUMNS($A:$AD),)


但是当我们要维护模板,新增列或者删除列,坑就大了

下面我们通过动画来观察一下!

▍天坑来了

最近维护模板,需要在表中插入一列,增加点内容,于是被坑死了!

新增列,发现公式变成了下面这样,COLUMNS($A:$AD)变成了COLUMNS($A:$AE),导致公式错误这不是我们期望的,因为我们在后面插入的一列
=VLOOKUP(E2,Sheet2!$A:$AD,COLUMNS($A:$AE),)

▼ 动画演示


很多同学可能讲,这也没什么修改一下就好了,但是这只是演示的一个公式,实际的模板中列很多,类似的公式也很多,很多嵌套的复杂公式,逐个简单不大现实!

那么,是否有什么办法,可以快速处理这个问题呢?答案就在下面

▍填坑指南

想要填这个坑,首先要搞清楚,为什么会变动,明明我们已经使用$锁定了呀!锁定只是公式填充不会变化,但是插入行列还是会发现区域变化,这本来是好事,但是这里却是大坑!

其实,因为单元格地址前面没有添加工作表名称,所以默认就是当前表中的区域所以在当前表中插入才会发生变化!

处理方法,也非常简单,只要指定一个表且这个表一般不会发生区域变化即可!

下面是我琢磨出的小技巧,2秒轻松搞定!

核心操作说明:
1、Ctrl+H,调出替换对话框
2、查询内容中输入:COLUMNS(
3、替换内容中输入: COLUMNS(辅助表!
4、点击全部替换即可!

の这里"辅助表"可以根据实际情况修改,以后不想这个区域发生变化,可以考虑一个空白表!如果想随着引用表同步变化,那么推荐修改为公式引用表的名称!

替换后,变成了下面的公式,通过动画发现,插入行列不再发生变化!
=VLOOKUP(E2,Sheet2!$A:$AD,COLUMNS(辅助表!$A:$AD),)


▼ 动画演示

今天的内容就到这里,一个小小的知识点,但是坑不小,本质还是基础不牢地动山摇,大家只有知其然知其所以然才能避开类似的坑!

Excel办公实战
分享Excel及WPS函数、技巧、VBA、PBI、JSA等办公技能。助你轻松实现办公自动化!易办公◎早下班!
 最新文章