計(jì)算單元格中指定字符的個(gè)數(shù)

計(jì)算A1單元格的字符串中大寫(xiě)字母S的個(gè)數(shù),，可以使用如下公式：=LEN(A1)-LEN(SUBSTITUTE(A1,”S”,””)),。該公式就是使用SUBSTITUTE函數(shù)將大寫(xiě)字母S用空字符替換，就是將所有的S剔除,，然后再比較A1的字符數(shù)和去除S后的字符數(shù),，差值就是大寫(xiě)字母S的個(gè)數(shù)，由于SUBSTITUTE函數(shù)對(duì)字符串的大小寫(xiě)是敏感的,，所有如果要計(jì)算A1單元格中所有字母s的數(shù)量（大小寫(xiě)都包括）,，則公式應(yīng)該是：=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,”S”,””),”s”,””))。

  計(jì)算某單元格中子字符串出現(xiàn)的次數(shù)

計(jì)算特定子字符串在某單元格（字符串）中出現(xiàn)的次數(shù),，首先也是用空字符替代子字符串,，然后再計(jì)算替代前后的字符數(shù)的差值，得到的結(jié)果就是包含子字符串的字符數(shù),，用這個(gè)字符數(shù)在除以子字符串的長(zhǎng)度,，就是子字符串出現(xiàn)的次數(shù)了,，基本公式為：=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)，這個(gè)公式表示在A1字符串中B1子字符串出現(xiàn)的次數(shù),。

但是這個(gè)公式并不嚴(yán)謹(jǐn),，因?yàn)?/span>SUBSTITUTE函數(shù)區(qū)分大小寫(xiě)，如果A1和B1中的字符大小寫(xiě)格式不一樣,，而你又不想?yún)^(qū)分大小寫(xiě),，上面的公式就需要一些改進(jìn)：=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))，同樣上面公式中可以用LOWER函數(shù)代替UPPER函數(shù),，目的就是在替換（去除子字符串）時(shí)將A1和B1中的字符串統(tǒng)一大小寫(xiě)的格式。

  去除尾隨的減號(hào)

有些會(huì)計(jì)制度規(guī)定使用一個(gè)尾隨的減號(hào)（負(fù)號(hào)）來(lái)表示一個(gè)負(fù)值,，如果你將這樣的數(shù)據(jù)導(dǎo)入Excel,，那些帶有尾隨減號(hào)的數(shù)據(jù)會(huì)被解釋為文本。下面的公式可以在導(dǎo)入數(shù)據(jù)后檢查是否有尾隨減號(hào)的數(shù)據(jù),，如果有則將其減號(hào)刪除并返回一個(gè)負(fù)數(shù),，例如將A1單元格中的365.48-改變成-365.48：=IF(RIGHT(A1,1)=”-”,LEFT(A1,LEN(A1)-1)*-1,A1)。

  用序數(shù)表示數(shù)字

用序數(shù)來(lái)表示數(shù)字,，比如今天是某月份的第一天用1st來(lái)表示（尤其是使用英語(yǔ)的國(guó)家）,，我們就是將1（數(shù)字）轉(zhuǎn)換為1st（字符串），方法就是在數(shù)字后面添加一個(gè)后綴,，后綴一共有四種,，在末位是1、2,、3時(shí)分別添加 st,、nd、rd,，其余添加th,，不過(guò)還有特殊的，就是11,、12,、13也添加后綴th，如此而已,，用Excel公式實(shí)現(xiàn)的方法如下：

=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),”th”,IF(OR(VALUE(RIGHT(A1))={1,2,3}),CHOOSE(RIGHT(A1),”st”,”nd”,”rd”),”th”))

其中VALUE函數(shù)的功能是將代表數(shù)字的文本字符串轉(zhuǎn)換成數(shù)字,，有一個(gè)參數(shù)，是文本或者包含要轉(zhuǎn)換文本的引用,，CHOOSE函數(shù)的功能是根據(jù)給定的索引值,，從參數(shù)串中選擇相應(yīng)的值或操作，它的參數(shù)數(shù)量在從2到255之間,，其中第一個(gè)參數(shù)必需的,，是指定所選定的值,，該值須為1到254之間的數(shù)字（整數(shù)，如果非整數(shù)會(huì)自動(dòng)截尾取整）,，這個(gè)數(shù)字表示一個(gè)取值的序號(hào),，該參數(shù)是多少，就執(zhí)行后面哪一項(xiàng)的操作（從第二個(gè)參數(shù)開(kāi)始計(jì)算）,，第二個(gè)參數(shù)是必需的,，其后是可選的，例如=CHOOSE(2,A1,A2,A3,A4)中第一個(gè)參數(shù)2表示,，執(zhí)行后面第二個(gè)操作,，該公式就返回A2單元格的值。

  用數(shù)字來(lái)確定列名（字母）

下面公式根據(jù)單元格A1中的數(shù)字返回對(duì)應(yīng)的列名的字母（從A到XFD）,，例如A1單元格的內(nèi)容是30,，公式返回值的是第30列的字母AD：=LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)。其中ADDRESS函數(shù)的功能是創(chuàng)建一個(gè)以文本方式對(duì)工作簿某一單元格的引用,，該函數(shù)的參數(shù)中前兩個(gè)數(shù)值表示該單元格的行號(hào)和列號(hào),，本例中1是行號(hào)，A1單元格包含的值是列號(hào),，后面還有三個(gè)可選參數(shù),，第三個(gè)參數(shù)是一個(gè)數(shù)值，是返回的引用類型,，默認(rèn)1表示絕對(duì)單元格引用（如$A$1）,，2表示絕對(duì)行號(hào)相對(duì)列號(hào)（A$1），3表示相對(duì)行號(hào)絕對(duì)列號(hào)（$A1）,，4是相對(duì)單元格引用（A1）,，第四個(gè)參數(shù)是邏輯值，如果是TRUE,，表示返回A1樣式,；如果是FALSE，返回R1C1樣式引用,，默認(rèn)為TRUE,，最后一個(gè)參數(shù)若存在，則是一個(gè)文本值,，指定外部引用的工作表名稱,，如=ADDRESS(1,1,,,”Sheet2”) 返回 Sheet2!$A$1，如果忽略該參數(shù)返回的是當(dāng)前工作表的單元格,。

回頭我們看看上面的公式,，我們會(huì)發(fā)現(xiàn)該公式?jīng)]有檢查錯(cuò)誤，就是說(shuō)如果A1單元格中的數(shù)值大于16384或者小于1,，該公式的返回值是#VALUE!,，我們要處理一下錯(cuò)誤,，如果出現(xiàn)上述情況可以返回一個(gè)“Invalid Column”（無(wú)效列），公式如下：=IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),”Invalid Column”),，IFERROR函數(shù)的功能是如果第一個(gè)參數(shù)的值（表達(dá)式）正確返回自身的值,，如果錯(cuò)誤則返回第二個(gè)參數(shù)的指定值，兩個(gè)參數(shù)都是必需的,。注意：該公式在Excel2003并不適用,，因?yàn)闆](méi)有IFERROR函數(shù)，如果要想在2003以前的版本中實(shí)現(xiàn)該返回值,，公式如下：

=IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),”Invalid Column”, LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),，這里的ISERR函數(shù)的功能是判斷相應(yīng)的表達(dá)式是否返回錯(cuò)誤值（不包括#N/A），如果是返回TRUE,，不是返回FALSE,，另外還有許多功能相似的以IS開(kāi)頭的函數(shù)，（ISBLANK,，ISERROR，ISLOGICAL,，ISNA,，ISNONTEXT，ISNUMBER,，ISREF,，ISTEXT）這類函數(shù)都是判斷相應(yīng)的值，返回TRUE或FALSE,，以后就不一一解釋了,。

  從路徑中提取文件名

下面公式就是從完整的給定路徑中提取文件名，例中的系統(tǒng)路徑分隔符是默認(rèn)的反斜杠（”\”）,，如果是其他格式的分隔符就會(huì)返回錯(cuò)誤,。

=MID(A1,FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))+1,LEN(A1))

具體實(shí)現(xiàn)過(guò)程就是先計(jì)算反斜杠的個(gè)數(shù)，再用星號(hào)替換最后一個(gè)反斜杠,，在再找到星號(hào)的位置后,，然后從后面開(kāi)始取值，就是這么實(shí)現(xiàn)的,。