Excel 的修剪函數(shù) Trimrange

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