昨天講人民幣阿拉伯數字轉中文大寫的公式，最後用兩個SUBSTITUTE函數替換「零角」為「零」，「零分」為「整」。今天，就有朋友問韓老師SUBSTITUTE函數的具體用法，那韓老師就來總結一下。
功能
在某一文本字符串中替換指定的文本。
語法SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE 函數語法具有下列參數：
文本 必需。 需要替換其中字符的文本，或對含有文本（需要替換其中字符）的單元格的引用。
old_text 必需。 需要替換的文本。
new_text 必需。 用於替換 old_text 的文本。
Instance_num 可選。 指定要用 new_text 替換 old_text 的事件。 如果指定了 instance_num，則只有滿足要求的 old_text 被替換。 否則，文本中出現的所有 old_text 都會更改為 new_text。
特點
1、區分大小寫和全形半角：當text中沒有包含 old_text指定的字符串時，函數結果與text相同；
2、當第三個參數為空文本或是省略，而只保留參數前的逗號時，相當於將 old_text替換；
3、當第四個參數省略，text中與 old_text相同的文本將被替換；
4、如果第四個參數有指定，比如「2」，則只有第二次出現的old_text被替換。
以上四個特點舉例如下：
公式：=SUBSTITUTE(D1,"及","合")，把「不及格」統一改為「不合格」：
比如：火車票上的身份證號碼從第11位開始隱藏4位，實現公式：=SUBSTITUTE(C2,MID(C2,11,4),"****")
其中：
text：C2，身份證所在單元格；
old_text:：MID(C2,11,4)，用MID函數從身份證號碼的第11位取4位；
new_text:：「****」。
公式為：=LEN(D2)-LEN(SUBSTITUTE(D2,6,))
其中：
LEN(D2)：D2字符串的長度；
LEN(SUBSTITUTE(D2,6,))：替換掉了6以後字符串的長度。
在F11單元格輸入公式：
{=(AVERAGE(--SUBSTITUTE(F2:F10,"分",)))}
（CTRL+SHIFT+ENTER結束）
其中：
{=--SUBSTITUTE(F2:F10,"分",)}，公式內「--」稱為「減負運算」，{=SUBSTITUTE(F2:F10,"分",)}的結果是一串文本，前面加一個「-」，是通過取負數將文本轉換成數值，再加一個「-」，即負負得正。
「--減負運算」常用於公式中把文本轉換為數字。
比如下圖表中的員工姓名和業績擠在一個單元格裡，要求統計業績最大值。
在C2單元格輸入公式：
{=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))}，（TRL+SHIFT+RNTER結束）。
{=ROW($1:$100)}：
返回值是1-100組成的數組{1；2；3；4；5；6；7……98；99；100}
{=SUBSTITUTE(B2,ROW($1:$100),)}：
將B2內的文本依次刪除1~100數值以後，返回100組文本組成的數組，如下圖：
關於SUBSTITUTE函數的用法參考昨天的文章：Excel | 數據輸入不規範，部分帶數量單位，怎麼計算平均值？。
{=SUBSTITUTE(B2,ROW($1:$100),)<>B2}：
返回值是一組TURE與FALSE組成的100個邏輯值數組，將刪除了數字後的文本與B2單元格相對比，如果不等於B2返回TURE,如果等於B2返回FALSE。
{＝(SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100)}：
將得到的一級邏輯值與1~100數值相乘，TURE相當於1，FALSE相當於0,相乘以後得到的結果是一個數組，該數組由100個數值組成，分別是B2單元格中包含的所有數字和0。
最後用MAX函數對上述數組內的數值求最大值。
連結：http://pan.baidu.com/s/1qYuiock
密碼：uma1