單詞Substitute的含義為替換,Substitute函數(shù)的功能有點兒類似Excel中的查找替換命令,但確更加的靈活和方便。 Substitute函數(shù)屬于文本查找類函數(shù),作用就是將查找到的字符替換成指定的字符。其語法結(jié)構(gòu)為:=Substitute(字符串,被替換字符串,替換字符串,[替換位置]);當省略“替換位置”時,默認值為1。 一、Substitute函數(shù):替換指定的字符串。 目的:將“學歷”中的“大本”替換為“大學本科”。 方法: 在目標單元格中輸入公式:=SUBSTITUTE(F3,'大本','大學本科')。 解讀: 當省略參數(shù)“替換位置”時,默認值為1。 二、Substitute函數(shù):加密指定內(nèi)容。 目的:加密身份證號碼中的出生日期。 方法: 在目標單元格中輸入公式:=SUBSTITUTE(C3,MID(C3,7,8),'********')。 解讀: 1、參數(shù)“被替換字符串”為:MID(C3,7,8),Mid函數(shù)的作用為從指定的字符串中提取指定長度的值,所以,被替換的字符串是一個動態(tài)獲取的過程,其用法非常靈活,各位親在應用的過程中要靈活對待哦! 2、此操作不具有“字典”功能,不可逆哦! 三、Substitute函數(shù):字符串計數(shù)。 目的:統(tǒng)計同一個單元格內(nèi)字符串的個數(shù), 方法: 在目標單元格中輸入公式:=LEN(C3)-LEN(SUBSTITUTE(C3,'、',''))+1。 解讀: 1、Len函數(shù)的作用為:返回文本字符串中的字符個數(shù)。 2、公式中,首先用Len獲取當前單元格的總長度,然后減去除分隔符(“、”)之外的字符個數(shù),+1修正即可得到字符串的個數(shù)。+1也很好理解,除最后一個字符串外,前面的每個字符串都和唯一的“、”一一對應,要計算的是總長度,當然+1進行修正。 3、Substitute函數(shù)的第二個參數(shù)“、”為字符串中的分隔符。 四、Substitute函數(shù):帶單位求和。 目的:計算員工的總“月薪”。 方法: 在目標單元格中輸入公式:=SUMPRODUCT(SUBSTITUTE(H3:H12,'元','')*1)&'元'。 解讀: 1、公式中,首先用Substitute函數(shù)將H3:H12單元格區(qū)域的“元”替換為空值,并×1轉(zhuǎn)換為數(shù)值類型,最后用Sumproduct進行求和,并在和值的后面添加“元”。 2、如果用Sum函數(shù)替代Sumproduct函數(shù),則填充時必須用Ctrl+Shift+Enter。 五、Substitute函數(shù):數(shù)據(jù)分列。 目的:將“組員”分配到不同 方法: 在目標單元格中輸入公式:=TRIM(MID(SUBSTITUTE($C3,'、',REPT(' ',100)),COLUMN(A1)*100-99,100))。 解讀: 1、Rept函數(shù)的作用為:根據(jù)指定次數(shù)重復文本,即將“ ”重復100次。 2、SUBSTITUTE($C3,'、',REPT(' ',100))的作用就是將“、”替換成100個空格。 3、Mid函數(shù)的作用為:從文本字符串中指定的起始位置起返回指定長度的字符。所以MID(SUBSTITUTE($C3,'、',REPT(' ',100)),COLUMN(A1)*100-99,100)的作用為提取被替換后字符串中從1、101、201……(請注意Column函數(shù)的作用)開始,長度為100的字符串。 4、Trim函數(shù)的作用為:刪除字符串中多余的空格。所以最終得到成員姓名。 結(jié)束語: 關于Substitute函數(shù)的應用技巧,今天就分享到這里了,如果親有更多的案例,可以在留言區(qū)或則私信小編討論哦! 如果需要聯(lián)系文檔,親掃描二維碼下載哦! |
|