首页时事民生政务教育文化科技财富体娱健康情感

旅行百科职场楼市企业乐活学术汽车时尚创业美食幽默美体文摘

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),)

▼ 动画演示

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

http://mp.weixin.qq.com/s?__biz=MzU2NTIyMTI3OA==&mid=2247507594&idx=1&sn=ea4e0f68bc0b57988e8e21b56996fd72

Excel办公实战

Excel函数、VBA、PBI等全系列教程第一人。助你轻松实现办公自动化！宗旨:易办公◎早下班！

最新文章

微软重磅更新TRIMRANGE，公式再也不卡了

Excel+WPS函数一课全搞定！

成语类型判断(UNIQUE+MATCH+正则等)

PQ从入门到精通通关课程，限时优惠！

Excel动态数据透视图你会吗？

SUMIFS函数按月求和搞不懂怎么办？

REDUCE函数这个用法真的妙不可言！

结合正则函数学习一下动态数组核心扩展技巧

函数 | REPLACE函数用法详解

DELCOLS函数，删除数组指定列！

Excel&WPS函数教程重大更新

VBA只模拟VLOOKUP函效果？更强！

Excel包含关系数据查询，你这样写就对了！

Excel全系列教程，加入学个够！

简单聊一下Excel中的各种提取唯一值问题

PowerQuery从入门到精通课程

这才是我认可的涨跌柱形图！

秒懂Excel文本函数-下篇

如何使用VBA提取文件名称，坑太深！

一个字符串秒懂10+函数【上篇】

Excel函数100，一次全学完！

Excel一键生成目录,白嫖全部代码模版！

动态更新工作表名称，Excel+WPS

小练习，写一下VBA和M函数解法！

Excel按月求和怎么处理？

Excel中COLUMNS这函数把我坑死啦

数据分类汇总一文足够，8类处理方案+Pandas

Excel数据透视表从入门到精通！

Excel中这个工具，文本处理非常丝滑！

微软重要更新，上聚光灯！WPS用户挥挥手！

2024年Excel数据查找终极指南！

精通Excel数据查询是什么体验！

VBA批量提取文件名称，0基础写法

VBA按列拆分，字典+二维数组，新思路

Excel函数进阶实战案例，分组+正则，有难度！

比Vlookup好用10倍！高效办公神技，看完我就服了

Excel模板制作，必备技能！

WPS一个核心函数搞定动态多表合并！

精通Excel是什么体验？

Excel数据分析之最大连续周！

VLOOKUP会了，但是没全会！

Excel动画教程，轻松学办公必备函数！

VBA常用代码大全，5小时整理，完结！

为什么SUM求和为0？怎么办？

WPS专属函数+Excel，5种解法通关这个难题！

函数+VBA+PQ+Pandas+JSA=七龙珠！

提供办公效率的聚合函数难题终于解决了！

VBA中取消筛选报错！ShowAllData不懂！

Excel新函数实战用法指南，正则走一波！

PowerQuery从入门到精通课程

分类

原创标签

时事社会财经军事教育体育科技汽车科学房产搞笑综艺明星音乐动漫游戏时尚健康旅游美食生活摄影宠物职场育儿情感小说曲艺文化历史三农文学娱乐电影视频图片新闻宗教电视剧纪录片广告创意壁纸头像心灵鸡汤星座命理教育培训艺术文化金融财经健康医疗美妆时尚餐饮美食母婴育儿社会新闻工业农业时事政治星座占卜幽默笑话独立短篇连载作品文化历史科技互联网

发布位置

广东北京山东江苏河南浙江山西福建河北上海四川陕西湖南安徽湖北内蒙古江西云南广西甘肃辽宁黑龙江贵州新疆重庆吉林天津海南青海宁夏西藏香港澳门台湾美国加拿大澳大利亚日本新加坡英国西班牙新西兰韩国泰国法国德国意大利缅甸菲律宾马来西亚越南荷兰柬埔寨俄罗斯巴西智利卢森堡芬兰瑞典比利时瑞士土耳其斐济挪威朝鲜尼日利亚阿根廷匈牙利爱尔兰印度老挝葡萄牙乌克兰印度尼西亚哈萨克斯坦塔吉克斯坦希腊南非蒙古奥地利肯尼亚加纳丹麦津巴布韦埃及坦桑尼亚捷克阿联酋安哥拉