一、SUBSTITUTE函數(shù)

函數(shù)作用：在某一文本字符串中替換指定的文本。

函數(shù)語法=SUBSTITUTE(查找的單元格文本字符串,原字符串,新字符串,替換第幾個(gè))

1、基本用法：替換字符串信息

如下圖所示，把A2單元格里的D字符，全部替換成B字符

使用公式=SUBSTITUTE(A2,'D','B')

2、進(jìn)階用法：去除單元格數(shù)據(jù)后進(jìn)行計(jì)算

如下圖所示，這是產(chǎn)品信息表，有單價(jià)和數(shù)量這兩列數(shù)據(jù)，它們都帶單位，需要進(jìn)行匯總計(jì)算總金額。

使用公式=SUBSTITUTE(B2,'元','')*SUBSTITUTE(C2,'斤','')

然后填充其它單元格數(shù)據(jù)即可

其實(shí)上面的公式，就是利用了SUBSTITUTE函數(shù)替換功能，先替換掉單元格數(shù)據(jù)的單位，然后再進(jìn)行相乘求和。

二、TEXTJOIN函數(shù)

函數(shù)作用：文本鏈接函數(shù)，使用分隔符鏈接列表或文本字符串區(qū)域。

函數(shù)語法=TEXTJOIN(分隔符, 忽略空白單元格, 字符串1…)

1、基本用法：將文本信息合并到一塊并且用逗號(hào)隔開

如下圖所示，把所有姓名合并到一塊并且用逗號(hào)隔開。

使用公式=TEXTJOIN(',',TRUE,A2:A8)

解讀：

①、第一個(gè)參數(shù)分隔符“、”可以根據(jù)實(shí)際需要自定義。

②、第二個(gè)參數(shù)“TRUE”代表忽略空白單元格。

③、第三個(gè)參數(shù)“A2:A8”就是要合并到一塊的字符串。

2、進(jìn)階用法：按條件查詢合并文本

如下圖所示，左側(cè)是員工薪資表，統(tǒng)計(jì)薪資大于5000的員工名單。

使用公式=TEXTJOIN(',',TRUE,IF(B2:B8>5000,A2:A8,''))

然后通過三鍵組合【Ctrl+Shift+Enter】獲取數(shù)據(jù)信息。

公式解讀：

①上面公式主要是借助IF函數(shù)，使用公式IF(B2:B8>5000,A2:A8,'')查詢出滿足條件的文本，返回的是一個(gè)數(shù)組。然后將其放在TEXTJOIN的第三參數(shù)中即可。

②因?yàn)閃PS Office暫不支持動(dòng)態(tài)數(shù)組，故必須使用【Ctrl+Shift+Enter】鍵獲取數(shù)組數(shù)據(jù)。

三、TEXT函數(shù)

函數(shù)作用：TEXT函數(shù)是一個(gè)非常強(qiáng)大的轉(zhuǎn)換函數(shù)。

函數(shù)語法：=TEXT(值,數(shù)值格式）

第一參數(shù)值：為數(shù)值、計(jì)算結(jié)果為數(shù)字值的公式，或?qū)Π瑪?shù)字值的單元格的引用。

第二參數(shù)數(shù)值格式：為“單元格格式”對(duì)話框中“數(shù)字”選項(xiàng)卡上“分類”框中的文本形式的數(shù)字格式。

1、基本用法：轉(zhuǎn)換日期格式

①轉(zhuǎn)換成中文日期格式

使用公式=TEXT(C3,'[DBnum1]yyyy年m月d日')；

②轉(zhuǎn)換成以年月日為單位的日期格式

使用公式=TEXT(C3,'YYYY年M月D日')；

③轉(zhuǎn)換成日期對(duì)應(yīng)的星期格式

使用公式=TEXT(C3,'AAAA')；

2、進(jìn)階用法：判斷業(yè)績?cè)鰷p

如下圖所示，可以用TEXT函數(shù)判斷員工業(yè)績?cè)鰷p情況

使用公式=TEXT(D4-C4,'增加0元;減少0元;持平')

公式解釋：

TEXT函數(shù)用于判斷兩個(gè)數(shù)值相減的結(jié)果。使用的格式代碼是：'增加0元;減少0元;持平”

三段格式代碼用分號(hào)隔開，分別表示大于0、和小于0、等于0的情況。

格式碼中的0有特殊含義，表示要處理的值本身。

1、當(dāng)D4-C4大于0，顯示“增加n元”

2、當(dāng)D4-C4小于0，顯示“減少n元”

3、當(dāng)D4-C4等于0，顯示“持平”