Excel 的修剪函數(shù) Trimrange

Excel 的修剪函數(shù) Trimrange 秋葉Excel 2024/11/24 12:00:06 責(zé)編：夢(mèng)澤評(píng)論：12 原文標(biāo)題：《微軟重磅更新！這個(gè) Excel 新函數(shù)，厲害到顫抖！》  大家好，我是在「玩弄」新函數(shù)的小爽鴨~  在查找數(shù)據(jù)時(shí)，我們通常會(huì)使用 Vlookup 函數(shù)來(lái)查找。  =Vlookup (查找值，查找區(qū)域，返回?cái)?shù)據(jù)在查找區(qū)域的第幾列數(shù)，精確匹配 / 近似匹配)  如下圖，會(huì)根據(jù)數(shù)據(jù)源的多少選擇查找區(qū)域：$A$2:$C$8    但是如果數(shù)據(jù)源又新增了，則需要重新調(diào)整區(qū)域的大小。  為了能夠自動(dòng)擴(kuò)展，表哥表姐們一般會(huì)直接選擇整列。    這樣也能得出正確結(jié)果。  只不過(guò) Vlookup 函數(shù)是按照逐行的方式進(jìn)行查找的，數(shù)據(jù)量少還好，一旦多了，再加上函數(shù)嵌套，整個(gè)公式運(yùn)行速度就可能特別慢，甚至直接卡死。  問(wèn)了一下 AI（kimi），也是不太建議在函數(shù)中，使用整列區(qū)域作為參數(shù)的。    那該怎么辦呢？  1、修剪函數(shù) Office 365 新推出的 Trimrange 函數(shù)，就是專門用來(lái)解決這個(gè)問(wèn)題的。  PS : Office 365 Beta 版本已經(jīng)更新了該函數(shù)，WPS 目前不可用。  Trimrange，顧名思義：Trim 修剪 + Range 單元格區(qū)域，就是修剪單元格區(qū)域。  如下圖，我們想選擇 A 到 E 列之間的表格區(qū)域。    只需要使用 Trimrange 函數(shù)，就能自動(dòng)排除空白行或列，保留有效區(qū)域。    如果新增數(shù)據(jù)，函數(shù)也會(huì)自動(dòng)調(diào)整引用范圍~    2、函數(shù)語(yǔ)法 Trimrange 函數(shù)的語(yǔ)法規(guī)則也非常簡(jiǎn)單，它可以選擇上下左右所修剪的方向。  =Trimrange (要修剪的區(qū)域,[上下方向修剪]，[左右方向修剪])  ? 第一參數(shù): 就是需要修剪的單元格區(qū)域。  ? 第二參數(shù): 上下的修剪方式。  0，不修剪行  1，修剪區(qū)域上的空白行（上修剪）  2，修剪區(qū)域下空白行（下修剪）  3，上下空白行都修剪，也就是默認(rèn)狀態(tài)    ? 第三參數(shù): 修剪區(qū)域左右空白列（同理第二參數(shù)）。  比如，我們第二參數(shù)選 2 也就是下修剪，可以看到?jīng)]有進(jìn)行上修剪，如下圖：    寫這個(gè)函數(shù)很麻煩？  別擔(dān)心，它貼心的為常用的三種修剪方式提供了語(yǔ)法糖（全修剪，左上修剪，右下修剪）。  什么是語(yǔ)法糖？  此糖非彼「糖」，它其實(shí)就是一種簡(jiǎn)寫方式，可以使我們的公式看起來(lái)更簡(jiǎn)潔。  我們選擇動(dòng)態(tài)區(qū)域時(shí)，使用的溢出范圍運(yùn)算符#，它其實(shí)也可以當(dāng)做一種簡(jiǎn)寫的語(yǔ)法糖。    Trimrange 函數(shù)的語(yǔ)法糖很簡(jiǎn)單，只需要多加個(gè)點(diǎn).  我們直接選擇區(qū)域是下面這樣子的：  =A:E   在冒號(hào)前后加各一個(gè)點(diǎn).  =A.E 所選區(qū)域就變成全修剪 ↓    在冒號(hào)左邊加一個(gè)點(diǎn).  =A.E 就變成左上修剪 ↓    在冒號(hào)右邊加一個(gè)點(diǎn).  =A:.E 就變成右下修剪 ↓    3、實(shí)際運(yùn)用 在數(shù)據(jù)透視表中，除了使用智能表格，想實(shí)現(xiàn)動(dòng)態(tài)擴(kuò)展數(shù)據(jù)源還有一種方式，就是使用 Offset+Counta 函數(shù)。  操作步驟 ??  該工作表名稱為數(shù)據(jù)。  使用 Counta 函數(shù)確定數(shù)據(jù)表的行數(shù)和列數(shù)。  =COUNTA($A:$A)=COUNTA($1:$1)   Offset 函數(shù)返回的是單元格引用，所以我們可以使用 Offset 函數(shù)配合 Counta 函數(shù)擴(kuò)展數(shù)據(jù)區(qū)域。  =OFFSET(數(shù)據(jù)!$A$1,,,COUNTA(數(shù)據(jù)!$A:$A),COUNTA(數(shù)據(jù)!$1:$1))   利用名稱管理器，將函數(shù)名稱自定義：offset 區(qū)域    插入數(shù)據(jù)透視表，區(qū)域選擇「offset 區(qū)域」：    設(shè)置數(shù)據(jù)透視表，如下：    然后我們?cè)跀?shù)據(jù)源中新增一條數(shù)據(jù)。    右鍵更新數(shù)據(jù)透視表，就可以實(shí)現(xiàn)「更新數(shù)據(jù)源，數(shù)據(jù)透視表自動(dòng)擴(kuò)展」的效果。    大家有沒(méi)有發(fā)現(xiàn)，這樣做有一個(gè)很大的問(wèn)題？  上面是使用 Counta 函數(shù)，利用整行整列確定數(shù)據(jù)源行數(shù)和列數(shù)的。  數(shù)據(jù)源表中，沒(méi)人動(dòng)還沒(méi)事，大不了就更新慢點(diǎn)，但假如我亂入一些無(wú)用信息。  Offset 擴(kuò)展出來(lái)的數(shù)據(jù)源就有點(diǎn)問(wèn)題了。    那我們看看 Trimrange 函數(shù)？  它返回的也是單元格引用，又可以修剪區(qū)域，所以它可以同時(shí)取代 Offset+Counta 在這里的擴(kuò)展作用。  而且函數(shù)更為簡(jiǎn)單!  假設(shè)我們數(shù)據(jù)源區(qū)域限制在 A 列~E 列之間，當(dāng)這部分區(qū)域的數(shù)據(jù)更新，數(shù)據(jù)透視表數(shù)據(jù)源自動(dòng)擴(kuò)展。  同樣的步驟，我們只需要定義名稱：  =數(shù)據(jù)!$A.:.$E   插入數(shù)據(jù)透視表，表區(qū)域：trimrange 區(qū)域。    設(shè)置數(shù)據(jù)透視表。    新增數(shù)據(jù)信息。    將數(shù)據(jù)透視表右鍵更新，新增的數(shù)據(jù)也自動(dòng)更新了。    相比傳統(tǒng)方法（Offset 和 Counta），使用 Trimrange（語(yǔ)法糖 $A.:.$E），不僅在運(yùn)行速度上更快，而且比 Counta 函數(shù)來(lái)定位更為靈活。  即便在數(shù)據(jù)表 $A.:.$E 區(qū)域之外編輯單元格的無(wú)用信息，不會(huì)影響整體的數(shù)據(jù)源擴(kuò)展。  若是有嚴(yán)格的數(shù)據(jù)區(qū)域限制，比如我們的數(shù)據(jù)源只在 A1:E18 區(qū)域內(nèi)，同理，只需 A1.:.E18 定義名稱，設(shè)置區(qū)域即可。    4、總結(jié)一下 使用函數(shù)編寫公式的時(shí)候，身為表哥表姐的我們，希望能夠自動(dòng)擴(kuò)展區(qū)域，于是迫不得已選擇整列區(qū)域的引用，但在多重函數(shù)嵌套后，容易造成公式卡頓，運(yùn)行假死狀態(tài)。  Office 365 新出的 Trimrange 函數(shù)就是用來(lái)解決這個(gè)問(wèn)題的，WPS 目前還并未更新。  根據(jù)官方解釋，Trimrange 函數(shù)可以從范圍或數(shù)組的外邊緣中，排除所有空行和 / 或列。  ? 參數(shù)中可以指定上下左右的修剪方式。  ? 同時(shí)該函數(shù)提供常用三種修剪方式的語(yǔ)法糖：  全修剪 A.:.E  左上修剪 A.:E  右下修剪 A:.E  最后帶大家回顧一下以前擴(kuò)展數(shù)據(jù)源，制作動(dòng)態(tài)更新的數(shù)據(jù)表的函數(shù)方法（Offset+Counta），現(xiàn)在有 Trimrange 就變得很簡(jiǎn)單了（A.:.E）。  本文來(lái)自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小爽