當前位置：首頁 > 科技 > 正文

有一個Excel函數叫：省時一整天，你卻連我的名字都不知道…

編按

Hello大家好，今天給大家介紹一下SUBSTITUTE函數，它可以将數據中的舊值替換為新值，與我們熟悉的“查找替換”功能相似。很多小夥伴可能會說，簡單的查找替換就能解決的問題，我為什麼還要學習用複雜的函數？相比Excel的基礎操作，函數可以構建和數據源之間的動态關聯，當我們的數據源發生變化和，函數會自動更新結果，但基礎操作卻不會。所以我們今天就通過5個案例一起來學習一下SUBSTITUTE函數吧~

今天給大家介紹一個可以進行查找替換的函數---SUBSTITUTE函數，SUBSTITUTE函數的基礎語法是：SUBSTITUTE (要替換的文本,舊文本,新文本,[替換第幾個])。

最後一個參數，[替換第幾個]，是可以省略的，如果要替換的文本存在多個的話，省略這個參數表示替換全部。

先通過一個示例來掌握SUBSTITUTE函數的基本用法。

掃碼入群，下載Excel練習文件，同步操作

示例1：将單元格裡的“付款”替換成“賬期”

公式為=SUBSTITUTE(B2,"付款","賬期")。

這個公式省略了最後一個參數，所以将單元格裡的所有“付款”都換成“賬期”。如果隻想替換第一個“付款”，公式需要修改為：=SUBSTITUTE(B2,"付款","賬期",1)。

如果是“預付款”不進行替換，公式可以修改為：=SUBSTITUTE(B2,"天付款","天賬期",1)。

通過這個例子相信大家對于SUBSTITUTE函數的基本用法應該明白了。不過在實際應用中，單獨使用SUBSTITUTE函數的機會很少，基本上都是和其他函數組合使用的，下面的幾個例子都是組合套路，非常實用。

示例2：SUBSTITUTE組合MID加密手機号

這裡所說的加密就是将手機号的中間四位顯示成*，公式為：

=SUBSTITUTE(A2,MID(A2,4,4),"*****")。

公式的原理很簡單，MID(A2,4,4)是從手機号的第4位開始提取4個數字，用SUBSTITUTE函數把這部分内容換成"*****"，從而實現了手機号加密。

示例3：SUMPRODUCT組合SUBSTITUTE實現帶單位的數字求和

公式為：=SUMPRODUCT(--SUBSTITUTE(A2:A13,"元",""))。

首先用SUBSTITUTE(A2:A13,"元","")将區域中數據的單位“元”替換為空，因為SUBSTITUTE函數得到的結果是文本格式，所以前面用兩個負号将替換後的數據變成數值。

最後用SUMPRODUCT函數對這一組數字求和。

（注意：數字加單位是一種不規範的用表習慣，如果确實需要加單位可以用自定義格式實現。）

示例4：LEN組合SUBSTITUTE統計一個單元格内的人數

公式為：=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1。

在這個公式中，LEN(B2)取得B2單元格中字符串的長度。LEN(SUBSTITUTE(B2,"、",))+1的意思是用LEN計算不含頓号的字符串長度。在這個例子中，人名之間的間隔符是頓号，最後加1，是因為最後一個人名沒有頓号。

用B2原有的長度減去被替換掉人名之間間隔符的長度，也就是人數。使用這個公式要注意，每個名字之間的分隔符必須是一樣的，否則統計結果就會出錯。

示例5：五個函數聯手實現數據分列

将示例4中存在于一個單元格的多個人物分開，每個單元格隻存放一個人物，公式需要用到五個函數。

公式為：=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))。

這個公式的原理比較複雜，篇幅所限僅做簡要解釋。

REPT(" ",100)：先使用REPT函數，将空格重複100次，得到100個空格；

SUBSTITUTE($B2,"、",REPT(" ",100))：使用SUBSTITUTE函數将姓名中的的間隔符号頓号替換為100個空格；

MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)：再使用MID函數，依次從帶有空格的新字符串中的第1、第101、第201位……截取長度為100的字符。

這樣得到的字符串是帶有多餘空格的，因此再使用TRIM函數将多餘空格删除掉。如果實在不好理解會套用即可。

今天分享的五個SUBSTITUTE使用案例是非常典型的常見用法，希望大家能夠好好利用，簡化自己的工作提高工作效率。

你可能想看：

Excel VBA 7.19 Excel跨工作薄多工作表數據合并之Excel表格多行表頭

所以我們今天針對多行表頭的數據彙總來分析下方法場景模拟假設我們要統計的Excel中每個表格都有表頭，1) End If Next sth End If ActiveWorkbook.Close Fal...

你的工作中必須要學會的這27個Excel函數公式，速速收藏

Excel函數之——SUMPRODUCT函數太強大了

先介紹下SUMPRODUCT函數的基本用法,[array3]，該函數可以有多個參數。每個參數都必須是有相同維度的數組：以下是單條件計數:在使用N()函數“将N()返回的數組中的所有元素，所以就不在需要...

《Excel2003入門動畫教程27、Excel拆分表格》

《Excel2003入門動畫教程27、Excel拆分表格》。　　演示動畫，　　操作步驟，　　有時候在Excel中：們需要比較浏覽同一工作表中不同區域的數據時，可以通過折分窗口來實現“　　選中窗口中部的...

Excel 中Alt鍵的高手用法 Excel必學技巧

即可一鍵生成柱形圖，Alt加Enter（回車鍵）單元格内強制換行五、Alt加數字鍵輸入符号 (需要在帶有小鍵盤的電腦上操作哦）Alt加41446輸入溫度符号Alt加41409輸入錯号Alt加41420...

VS2010操作Excel2010，報錯解決及讀寫excel

加到模塊std中:#include "CWorksheet.h";#include "CRange.h":5、将以上頭文件的#import "C;\\Program Files\\Microsoft ...

Excel教程：30秒做好Excel超鍊接目錄

工作表前面插入一張空白工作表Sheet。點擊右鍵，查看代碼會激活VBE編輯器界面，點擊插入——模塊，并且複制以下代碼到右邊編輯區。"選擇目錄起始單元格",Excel提示我們是否VB項目，選擇文件保存類...

财務小白，智勝Excel函數高手！（内含46個視頻教程）

在H2輸入公式:=INDEX(A,SMALL(IF(MATCH($A$2,0)=ROW($2,ROW($2;ROW(A1)))&。=UNIQUE(A2，在E2輸入公式:按Ctrl+Shift+E...

會計常用的Excel函數公式大全（共21個），幫你整理齊了！

=TEXT(MID(E3,=TEXT(EDATE(G3：12*(5*(F3='男')+55)),=VLOOKUP(LEFT(E3,=DATEDIF(V3,'y')&'ym')&,DATEDIF(...

8個Excel常見問題，你一定遇到過，附案例解決辦法！

小編整理了8個excel中常見的問題，在輸入的内容前添加“将單元格格式設置為文本格式即可。二、單元格内容顯示為：顯示公式文本内容：0不見了是因為系統判定輸入的值為數值；顯示為科學計數法的形式也是因為單...

這3個函數都不懂，千萬别說自己會Excel！（必學）

VLOOKUP、SUMIFS 和 COUNTIFS 函數？=VLOOKUP(lookup_value，要查找的值，就是将要在哪個單元格區域中查找，返回查找值對應的列号。如果在查找區域中找到這個值的話，...

Excel公式技巧01： 使用INDEX函數返回整行或整列

INDEX函數是我們經常使用的函數之一，如果将其行參數或者列參數指定為0（或者忽略）。那麼會返回對指定列或行的引用，這裡返回的不是單個值。返回數據區域A1:=INDEX(A1：返回數據區域A1，即單元...

excel查找定位：INDEX函數——精确制導導彈

一、認識INDEX函數Index函數：返回特定行列交叉處單元格的值或引用。二、INDEX函數基礎用法1.單行、單列中提取數值”隻需一個坐标值如果給定的區域是單行或者單列。INDEX通過坐标返回數值”實...

1小時整理了10個Excel快捷鍵，直接拿走套用!

今天跟大家分享10個使用頻率超高的Excel快捷鍵，可快速調出功能設置窗口，經常用于自定義單元格格式和圖表的設置。【Ctrl+E】組合鍵用于向下快速填充數據，隻需要在首個單元格輸入你想要的目标值，按下...

5分鐘搞定2個小時的工作量？這個Excel“魔法”讓同事直呼：這也太猛了...

下面就和春風老師一起來從創建、運行、管理、宏的安全設置四個方面學習下。這條命令可以是文檔編輯中的任意操作或操作的任意組合。我們分錄制和使用Visual Basic兩種方式創建宏。01錄制宏在Excel...

别再浪費A4紙了！學會這個Excel打印技巧，老闆馬上給你加薪

之前和大家聊了很多關于 Excel 函數、便捷操作技巧。都會經常碰到需要将表格打印成紙質版的情況，或者是表格不知道如何打印到一頁紙上.....，久久醬今天給大家總結了關于表格組合打印的技巧，▲ 點擊查...

還在被合并單元格虐？這個Excel“偷懶神器”解決你80%操作難題！

相信用過辦公軟件的小夥伴肯定都有合并和取消單元格的經曆吧！今天就跟大家來聊一下表格中合并單元格與取消合并單元格的那些事，今天主要跟大家介紹一下關于Excel和WPS表格中批量取消\合并單元格的技巧。然...

65個excel操作教程大全，新手很容易上手，建議收藏備用

可以看看本文提供的Excel表格操作技巧彙總詳細教程。1、為excel文件添加打開密碼：按ctrl或shift鍵選取多個要打開的excel文件”10、把A.xlsx文件圖标顯示為圖片形式“4、隐藏ex...

别弄丢！年底都會用到的7個Excel模闆，10秒鐘搞定半天工作

2.多表或者多工作簿合并到一個工作表。以後就不用再每次打開表格複制粘貼了，将模闆放在要合并的文件夾内。也是将要合并的工作簿和模闆放在同一個文件夾，這裡合并後生成多個工作表:4.将一個工作簿的多個工作表...

兩個excel表格對比，有簡單方法嗎？

一、對比兩個表的項目對應的值是否一緻：excel中有一個大家不常用的功能。利用它我們可以快速對比出兩個表的差異：一個是财務軟件導出的表，要求對比這兩個表同一物品的庫存數量是否一緻。軟件導出表：選取sh...

Excel中雙擊的妙用，5.3%的人還不知道

1、快速填充雙擊單元格右下角的填充柄，可以快速将公式或内容填充到當前連續區域。2、修改工作表名稱雙擊工作表名稱可進入編輯狀态，輸入新名稱後單擊任意單元格即可。3、調整為最适合列寬當單元格寬度不足以顯示...

Excel中最牛逼的函數，一個頂7個，簡直就是百變神君

=VLOOKUP(G2:=SUMPRODUCT(($A$2:$A$10=G2)*($B$1，=SUMIF(A:=SUMIFS($F:$A$20=$H2)*($B$2:$A$20=H2)*1),=COU...

中藥的名字都有什麼含義？210味常用中藥名釋義

因藥用桂樹之嫩枝而得名，紫蘇梗“紫蘇子。薷”本品藥用其根，細辛其根極細，故有羌活……諸名”其苞初生如荑而味辛也”本品因形、味而得名。故取象曰木筆，故名藁本“本品藥用其果實。本品因藥用桑樹的葉片而得名，...

Excel教程：統計中國式排名的三種方法，一定有一個适合你

=RANK（要排名的數據，既然使用RANK函數的結果不是中國式排名，下面就為大家介紹三種得到中國式排名的方法：一、SUMPRODUCT+COUNTIF的公式方法第一種方法需要用到SUMPRODUCT和...

EXCEL表中随機生成手機号碼（純函數，任意号段）

由于各種各樣的原因，總有需要随機生成數據的時候，今天就介紹一個随機生成手機号碼的案例，廢話少說直接看圖,公式如下,直接在需要随機号碼的單元格中輸入以下公式即可,INDEX({134,177;TEXT(...

EXCEL數學和三角函數彙總

ASIN”ATAN，FLOOR。ROUND“SQRT，TRUNC，【參數】number是需要計算其絕對值的一個實數”【用途】返回以弧度表示的參數的反餘弦值，【參數】number是某一角度的餘弦值=AC...

SUBSTITUTE函數公式示例B2

上一篇
6個你不知道的微信冷知識，個個都很實用！帶你1分鐘熟知微信

下一篇
一份超詳細的理财從入門到進階教程

有話要說...

最新文章

取消
掃碼支持 支付碼