每隔一段时间，在会员微信群里，就会有不同的学员朋友问一个相同的问题，整列引用会不会拖累函数的计算速度？比如说，如下所示的VLOOKUP函数，它引用了A~C整列，会显著降低计算效率吗？![]()
摊手，如果你有看过我的函数系列教程的话，答案其实是显而易见的：不会。当第2参数查找范围为单元格引用时，VLOOKUP函数有自我修剪功能，它会对查询范围做优化调整。简单而言，它会获取查询范围和工作表已使用单元格区域两者之间的交集。如上图所示的表格，虽然公式设置了查询范围是A~C整列，但它会自动调整为A1:C9单元格区域。因此，查询范围使用整列引用并不会显著降低VLOOKUP函数的计算效率。
拥有类似VLOOKUP函数这种自我修剪引用区域的函数还有很多，大部分都是查询引用类函数，例如SUMIF(S)/COUNTIF(S)/INDEX/MATCH/XLOOKUP等等。
但并不是所有的函数对整列引用都有自我修剪功能，特别是数组和迭代计算类型，典型如IF/SUMPRODUCT/FILTER/SORT(BY)/MAP/REDUCE等等……
另外，可能需要额外说明![]()
以下VLOOKUP函数公式也不具有自我优化功能。=VLOOKUP(E2,IF({1,0},B:B,A:A),2,0)
这条VLOOKUP函数的第2参数查询范围看起来是单元格引用，但实际上却是数组运算，详情参考往期推文：「VLOOKUP函数的这个用法我建议你千万别用
=VLOOKUP(E2,$A$2:$C$9,3,0)
上面这个VLOOKUP函数的查询范围固定为A2:C9单元格区域。当新增一条记录到A10:C10单元格区域，如果不调整对应的查询范围，很明显就可能导致计算数据遗漏。
整列引用可以避免以上问题。它有一个非常巨大的好处，当数据新增或者删除时，不需要做额外的调整，毕竟无论数据增减，都还在整列之中。换而言之，整列引用变向起到了动态引用实际数据范围的效果。但是，如果一个函数没有自我修剪引用区域的功能（类似VLOOKUP函数那样），通常我也不建议你使用整列引用。原因无它，计算冗余太多，计算效率实在太差了。1列有1048576个单元格，n列直接翻n倍，这体量Excel的小身板很难承重。![]()
打个响指，更多的时候还是建议将计算范围设置到5000行左右，大部分朋友的Excel的数据量都不会超过这个数……对吧？上图中所使用的FILTER函数如下，它的计算范围为A2:C5000。通常而言，在这个范围内，数据的新增和删除不会影响函数的正确运算，同时，计算效率也不会显著下降。=FILTER(A2:C5000,B2:B5000=F1)
前段时间，微软在Excel 365 的beta测试版本中发布了一个新函数，叫做TRIMRANGE。TRIM是修剪的意思，RANGE是单元格引用的意思，顾名思义，这个函数的作用就是修剪单元格引用的范围。=TRIMRANGE (引用区域、[删掉空行]、[删掉空列])
第1参数指定引用区域，第2/3参数指定修剪空行/空列，有以下4种删减方式。
如果2/3参数全部省略，则表示删减引用区域周围所有的空白行列。如下图所示，公式省略了第2和第3参数，则删除引用区域A:F列周围所有的空白行和列，返回实际数据区域B2:D11。
![]()
以下公式将第2参数设置为1，删掉B列前面的空行，返回引用区域B2:B1048576。
以下公式将第2参数设置为2，删掉B列后面的空行，返回引用区域B1:B11
以下公式将第2参数设置为3，删掉B列前后的空行，返回引用区域B2:B11
在实际应用中，使用TRIMRANGE函数删减行的情况最为常见。微软对此提供了语法糖，或者说简写形式，如下：
“B.:B” 在冒号前加一个小数点，表示删减实际数据前面的空白行
“B:.B” 在冒号后加一个小数点，表示删减实际数据后面的空白行
“B.:.B” 在冒号前后各加一个小数点，表示删减实际数据前后的空白行。
如你所想，TRIMRANGE函数的应用需求场景是显而易见的：准确引用动态的数据区域。换而言之，当你的实际数据范围不固定时，就应该立刻想起TRIMRANGE函数的好来。这就好像每当你和现在的她/他吵架时，就自然想起前任的好来![]()
![]()
以FILTER函数为例，使用TRIMRANGE函数搭配整列引用，公式如下：
=FILTER(TRIMRANGE(A:C),TRIMRANGE(B:B)=F1)
换用语法糖，公式会简洁许多，如下：
打个响指，没了。右下角点个赞，挥挥手，咱们下期再见。
⏩需要系统学习Excel却找不到优质教程？学习Excel的过程中遇到疑难问题，却找不到人及时作出解答？
加入我的付费社群，学习+训练+答疑，与5000+在线会员一起，同微软最有价值专家MVP同行，全面精进Excel之道![]()
🚂>>~
超低价Excel终身会员：一次付费
永久迭代学习，学习问题永久答疑
![]()
本文由公众号“Excel星球”首发。
点击阅读原文系统学习Excel！
