专栏名称: 秋叶Excel
和秋叶一起学Excel，免费获取Excel模板大全、插件合集！还有Excel真人视频、图文教程，助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人！
目录
相关文章推荐
麦音乐  ·  我看见许许多多他人的面孔 ·  昨天  
FM1007福建交通广播  ·  女歌手唐伯虎被曝拖欠劳务费，其工作室被起诉立 ... ·  昨天  
国家大剧院  ·  月光女神、音乐剧皇后……国际巨星莎拉·布莱曼 ... ·  昨天  
向阳光明草  ·  44年前的难忘记忆：《年轻的朋友来相会》—— ... ·  昨天  
向阳光明草  ·  44年前的难忘记忆：《年轻的朋友来相会》—— ... ·  昨天  
天津市文化和旅游局  ·  盛世华章 精彩津年丨图书馆里 民乐“快闪” ·  3 天前  
51好读  ›  专栏  ›  秋叶Excel

微软重磅更新！这个Excel新函数，厉害到颤抖！

秋叶Excel  · 公众号  ·  · 2024-11-18 11:30

正文

点击 蓝字【秋叶 Excel】 👆
发送【6】
免费领 1000+篇 Excel 精选教程！
本文作者：小爽
本文编辑：竺兰


大家好，我是在「玩弄」新函数的小爽鸭~

在查找数据时，我们通常会使用 Vlookup 函数来查找。

=Vlookup(查找值，查找区域，返回数据在查找区域的第几列数，精确匹配/近似匹配)

如下图，会根据数据源的多少选择查找区域： $A$2:$C$8


但是如果数据源又新增了，则需要重新调整区域的大小。

为了能够自动扩展，表哥表姐们一般会直接 选择整列 。


这样也能得出正确结果。

只不过 Vlookup 函数是按照逐行的方式进行查找的，数据量少还好，一旦多了，再加上函数嵌套，整个公式运行速度就可能特别慢，甚至 直接卡死 。

问了一下 AI （kimi） ，也是不太建议在函数中，使用整列区域作为参数的。


那该怎么办呢？

修剪函数


Office 365 新推出的 Trimrange 函数，就是专门用来解决这个问题的。

PS : Office 365 Beta 版本已经更新了该函数，WPS 目前不可用。

Trimrange，顾名思义：Trim 修剪+Range 单元格区域，就是 修剪单元格区域 。

如下图，我们想选择 A 到 E 列之间的表格区域。


只需要使用 Trimrange 函数，就能 自动排除空白行或列，保留有效区域 。


如果新增数据，函数也会自动调整引用范围~

函数语法


Trimrange 函数的语法规则也非常简单，它可以选择 上下左右所修剪的方向 。

=T rimrange (要修剪的区域,[上下方向修剪]，[左右方向修剪])

❶ 第一参数 : 就是需要修剪的单元格区域。
❷ 第二参数 : 上下的修剪方式。
  • 0，不修剪行
  • 1，修剪区域上的空白行（上修剪）
  • 2，修剪区域下空白行（下修剪）
  • 3，上下空白行都修剪，也就是默认状态


❸ 第三参数 : 修剪区域左右空白列（ 同理第二参数 ）。

比如，我们第二参数选 2 也就是下修剪，可以看到没有进行上修剪，如下图：


写这个函数很麻烦？

别担心，它贴心的为常用的三种修剪方式提供了语法糖 （全修剪，左上修剪，右下修剪） 。

什么是 语法糖 ？

此糖非彼「糖」，它其实就是一种简写方式，可以使我们的公式看起来更简洁。

我们选择动态区域时，使用的溢出范围运算符#，它其实也可以当做一种简写的语法糖。


Trimrange 函数的语法糖很简单，只需要多加个点.


我们直接选择区域是下面这样子的： 
=A:E


在冒号前后加各一个点. 
=A.:.E
所选区域就变成全修剪 ↓


在冒号左边加一个点. 
=A.:E
就变成左上修剪 ↓


在冒号右边加一个点. 
=A:.E

就变成右下修剪 ↓

实际运用


在数据透视表中，除了使用智能表格，想实现动态扩展数据源还有一种方式，就是使用 Offset+Counta 函数。

操作步骤 👇

该工作表名称为 数据 。

使用 Counta 函数确定数据表的行数和列数。 

=COUNTA($A:$A)=COUNTA($1:$1)


Offset 函数返回的是单元格引用，所以我们可以使用 Offset 函数配合 Counta 函数扩展数据区域。 

=OFFSET(数据!$A$1,,,COUNTA(数据!$A:$A),COUNTA(数据!$1:$1))


利用名称管理器，将函数名称自定义： offset区域


插入数据透视表，区域选择「offset区域」：


设置数据透视表，如下：


然后我们在数据源中新增一条数据。


右键更新数据透视表，就可以实现「更新数据源，数据透视表自动扩展」的效果。


大家有没有发现，这样做有一个很大的问题？

上面是使用 Counta 函数， 利用整行整列确定数据源行数和列数 的。

数据源表中，没人动还没事，大不了就更新慢点，但假如我乱入一些无用信息。

Offset 扩展出来的数据源就有点问题了。


那我们看看 Trimrange 函数？

它返回的也是单元格引用，又可以修剪区域，所以它可以同时取代 Offset+Counta 在这里的扩展作用。

而且函数更为简单 !

假设我们数据源区域限制在 A 列~E 列之间，当这部分区域的数据更新，数据透视表数据源自动扩展。

同样的步骤，我们只需要定义名称： 
=数据!$A.:.$E


插入数据透视表，表区域： trimrange区域 。


设置数据透视表。


新增数据信息。


将数据透视表右键更新，新增的数据也自动更新了。


相比传统方法 （Offset 和 Counta） ， 使用 Trimrange （语法糖 $A.:.$E） ， 不仅在运行速度上更快，而且比 Counta 函数来定位更为灵活。

即便在数据表 $A.:.$E 区域之外编辑单元格的无用信息，不会影响整体的数据源扩展。

若是有严格的数据区域限制，比如我们的数据源只在 A1:E18 区域内，同理，只需 A1.:.E18 定义名称，设置区域即可。






请到「今天看啥」查看全文


推荐文章
麦音乐  ·  我看见许许多多他人的面孔
昨天
FM1007福建交通广播  ·  女歌手唐伯虎被曝拖欠劳务费，其工作室被起诉立案！最新回应：如何偿还正内部协商！本人发声
昨天
国家大剧院  ·  月光女神、音乐剧皇后……国际巨星莎拉·布莱曼要来北京艺术中心啦！
昨天
向阳光明草  ·  44年前的难忘记忆：《年轻的朋友来相会》——上世纪八十年代新青年的精神颂歌！
昨天
向阳光明草  ·  44年前的难忘记忆：《年轻的朋友来相会》——上世纪八十年代新青年的精神颂歌！
昨天
天津市文化和旅游局  ·  盛世华章 精彩津年丨图书馆里 民乐“快闪”
3 天前
投行圈  ·  你的时间值多少钱？
7 年前
笑点研究所  ·  思聪不但赢在了人生的起跑线上，还赢在了游戏的起跑线上！
7 年前
风青杨  ·  你那么年轻，却一心只想过老年人的生活
7 年前
金错刀  ·  年赚70亿却给高管开2000月薪，众叛亲离后蹲了6年监狱，创维老板得了一个最痛教训！
7 年前
CMKT咨询圈  ·  有一种失败叫瞎忙
7 年前
Sov5搜索   ·   小百科   ·   今天看啥   ·   移动版
51好读 - 好文章就要读起来!