愛華網(wǎng)Excel函數(shù)公式應(yīng)用（三）
六、函數(shù)的基礎(chǔ)知識(shí)及其實(shí)例分析1、第一章 函數(shù)基礎(chǔ)知識(shí)
（1）按shift+F3是插入彈出“插入函數(shù)”對(duì)話框的快捷鍵。
（2）當(dāng)在單元格中鍵入“=”號(hào)時(shí)，在“名稱框”中會(huì)顯示出常用的函數(shù)列表，可以選擇所需要的。
（3）在單元格中輸入公式時(shí)，當(dāng)鍵入函數(shù)名稱時(shí)，或者鍵入函數(shù)名稱和左括號(hào)時(shí)，可按ctrl+shift+A組合鍵顯示函數(shù)的參數(shù)說明。
（4）如果在單元格中輸入的公式返回錯(cuò)誤的信息，想了解這個(gè)錯(cuò)誤信息的含義，如“#NAME”，可以選中此單元格，鼠標(biāo)光標(biāo)移動(dòng)到緊挨此單元格左側(cè)的智能標(biāo)記圖標(biāo)上，就會(huì)出現(xiàn)“公式中包含不可識(shí)別的文本”之類的錯(cuò)誤信息說明。
2、第二章 數(shù)學(xué)和三角函數(shù)
（1）SUM函數(shù)的參數(shù)不能超過30個(gè)，如果需要30個(gè)以上參數(shù)時(shí)，可以在引用的參數(shù)兩邊多加一對(duì)括號(hào)，這樣就突破了這個(gè)限制。如：計(jì)算A1：A32的和可以用公式：
=SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32))
（2）AVERAGE函數(shù)是求平均值的函數(shù)，如果參數(shù)引用中包含0值，則也會(huì)算在內(nèi)，可以使用下面的公式實(shí)現(xiàn)求平均數(shù)時(shí)，只對(duì)不等于0的值求平均數(shù)：
用數(shù)組公式： {=AVERAGE(IF(A1:A10<>0,A1:A10))}
提示：計(jì)算時(shí)會(huì)先得到一個(gè)含有數(shù)值和邏輯值的數(shù)組，再對(duì)這個(gè)數(shù)組求平均，由于AVERAGE函數(shù)會(huì)忽略邏輯值，所以就只對(duì)不等于0的數(shù)值求平均數(shù)。
（3）INT（）是向下取整函數(shù)。即向數(shù)軸向左的方向取整。
例如：=INT(9.9)結(jié)果是 9
=INT(-9.9) 結(jié)果是 -10
注意INT（）函數(shù)和TRUNC（）函數(shù)的區(qū)別。
（4）TRUNC（數(shù)值或單元格引用，指定取整精度）函數(shù)是取整函數(shù)，且是真正的取整函數(shù)，即截取數(shù)字的整數(shù)部分，正數(shù)、負(fù)數(shù)同樣對(duì)待。
如：=TRUNC(8.4) 結(jié)果是8
=TRUNC(-8.4) 結(jié)果是-8 ,而如果是=INT(-8.4) 則結(jié)果就是-9
注意：
a.TRUNC（）函數(shù)和INT（）函數(shù)的區(qū)別。
b.取整精度默認(rèn)為0，也可以指定，如： =TRUNC（4.867,2）結(jié)果是4.86
=TRUNC(-9.2389,3) 結(jié)果是-9.238
（5）CEILING（要四舍五入的數(shù)值，是需要四舍五入的乘數(shù)）函數(shù)
用法：此函數(shù)是將第一個(gè)參數(shù)向上舍入（沿絕對(duì)值增大的方向）為最接近的第二個(gè)參數(shù)的倍數(shù)。
注意：第一個(gè)參數(shù)和第二個(gè)參數(shù)的正負(fù)號(hào)必須統(tǒng)一；無論數(shù)字符號(hào)如何，都按遠(yuǎn)離 0 的方向向上舍入;最終結(jié)果肯定是第2個(gè)參數(shù)的整數(shù)倍。
例1：=CEILING(0.234, 0.01)結(jié)果是將0.234向上舍入到最接近的0.01的24倍，即0.01*24等于0.24,0.234向上舍入到0.24
例2：=CEILING(5.7,4)結(jié)果是將5.7舍入到4的2倍，即4*2等于8，5.7向上舍入到8。而不能是4的1倍，因?yàn)?*1等于4，而4小于5.7
例3：=CEILING(4.42,0.1) 結(jié)果是將4.42舍入到0.1的45倍，即0.1*45等于4.5，4,42向上舍入到4.5
例4：=CEILING(1.5, 0.1) 結(jié)果是1.5，因?yàn)?.5已經(jīng)是0.1的15倍了，所以保持不變。
（6）COMBIN（對(duì)象的總數(shù)量，為每一組合中對(duì)象的數(shù)量）
用法：求數(shù)學(xué)當(dāng)中的組合數(shù)。
注意：第2個(gè)參數(shù)應(yīng)當(dāng)小于等于第1個(gè)參數(shù)；兩個(gè)參數(shù)都必須大于等于0。
例1：求從8個(gè)對(duì)象中取2個(gè)對(duì)象進(jìn)行的組合數(shù)
=COMBIN(8,2)結(jié)果是28
例2：求從4個(gè)對(duì)象中取3個(gè)對(duì)象的組合數(shù)
=COMBIN(4,3)結(jié)果是4
（7）刪除單元格中文本中的空格符，可以用=SUBSTITUTE（Text,” ”,””）函數(shù)，但是文本中含有ASCII碼為160的空格符,公式要變?yōu)? =SUBSTITUTE(SUBSTITUTE(Text,” ”,””),CHAR(160),””)
（8）刪除空白行的一種方法。
選中要操作的區(qū)域，執(zhí)行“編輯”/“定位”/“定位條件”/選“空值”，“確定”后即可將選中區(qū)域中的空白單元格選中，再執(zhí)行“編輯”/“刪除”/“整行”即可。
注意：此操作要確保其他非空行中的所有單元格內(nèi)均有數(shù)據(jù)，否則會(huì)出現(xiàn)誤刪除記錄的現(xiàn)象。
（9）INDIRECT（引用的文本，a1）函數(shù)
注意：
a.如果引用的文本是對(duì)另一個(gè)工作簿的引用，則該工作簿必須被打開，否則函數(shù)返回#REF！
b.a1參數(shù)是一個(gè)邏輯值，表示引用類型是A1引用樣式還是R1C1引用樣式，為TRUE或省略時(shí)表示A1引用樣式
（10）EXP(number)函數(shù)計(jì)算e的number次冪。其中e為2.71828182845904
例1：exp(1) 結(jié)果是2.71828182845904，表示e的1次冪
例2：exp(2) 結(jié)果是 7.389056099，表示e的2次冪
3、第三章 統(tǒng)計(jì)函數(shù)
（11）MAX（）函數(shù)和MIN（）函數(shù)的參數(shù)最多為30個(gè)。
（12）計(jì)算指定區(qū)域的最大值。
比如數(shù)據(jù)在A1：A10，計(jì)算此區(qū)域中的最大值
方法一： =MAX(a1:a10)
方法二： =SMALL(A1:A10,COUNTA(A1:A10))
注意：MAX函數(shù)的參數(shù)引用如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數(shù)引用不能忽略邏輯值、文本，則要用MAXA()函數(shù)。
（13）RAND（）函數(shù)返回0到1之間的隨即數(shù)，每次工作表計(jì)算都返回一個(gè)新的值。
要生成a與b之間的隨機(jī)實(shí)數(shù)，可以用公式=RAND（）*（b-a）+a
（14）ROUNDUP（數(shù)值，四舍五入后的數(shù)字的位數(shù)）函數(shù)將指定數(shù)值返回為向上舍入的數(shù)值。
例1：=roundup(4.982,1)結(jié)果為5.0
例2：=roundup(3.14159,3) 結(jié)果為3.142
例3：=ROUNDUP(-3.14159, 1)結(jié)果為-3.2
注意：這里的向上舍入指遠(yuǎn)離0值。
（15）FREQUENCY（）函數(shù)
語法：
FREQUENCY(數(shù)據(jù)源,分段點(diǎn))
結(jié)果：
以分段點(diǎn)為間隔，統(tǒng)計(jì)數(shù)據(jù)源值在各段出現(xiàn)的頻數(shù)
其中：
數(shù)據(jù)源：為對(duì)一行/一列單元格或一個(gè)連續(xù)的單元格區(qū)域的引用；也可以是對(duì)一個(gè)單元格引用。
分段點(diǎn)：為對(duì)一行/一列單元格或一個(gè)連續(xù)的單元格區(qū)域的引用；也可以是對(duì)一個(gè)單元格引用。
數(shù)據(jù)引用支持跨工作表、工作簿
公式輸入方法：
以多單元格數(shù)組方式輸入，且必須是縱向數(shù)組；
所選單元格數(shù)比分段點(diǎn)個(gè)數(shù)大1，以統(tǒng)計(jì)數(shù)據(jù)源大于分段點(diǎn)最大值的頻數(shù)
（16）RANK（）函數(shù)
RANK（）函數(shù)對(duì)重復(fù)數(shù)的排位是相同的，如果兩個(gè)相同的數(shù)值出現(xiàn)時(shí)，它們的排名是相同的，比如都是第5位，而 不會(huì)是第5位和第6位，這里的第6位將被忽略，而直接跳到第7位。
（17）利用SMALL（區(qū)域，COUNT(區(qū)域)）函數(shù)可以統(tǒng)計(jì)區(qū)域中的最大值。
注意：SMALL（）函數(shù)忽略被統(tǒng)計(jì)區(qū)域中的空白單元格、邏輯值、文本。
（18）FORECAST（）函數(shù)是根據(jù)已有的數(shù)值來計(jì)算或預(yù)測未來值。
（19）TRIMMEAN（數(shù)組或引用，要去除的數(shù)據(jù)點(diǎn)比例）函數(shù)
例如：左邊的示例，
a.求A1：A12中去掉一個(gè)最高分、去掉一個(gè)最低分，然后求平均值：
常規(guī)做法是：
=(SUM(A1:A12)-MAX(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12)-2)
而利用TRIMMEAN函數(shù)就方便多了，因?yàn)橐粋€(gè)最高和一個(gè)最低是2個(gè)數(shù)，占總個(gè)數(shù)12的百分比是2/12，即1/6，所以公式可以寫成：
=TRIMMEAN(A1:A12,1/6)
結(jié)果和上面的公式相同。
b.如果要去掉兩個(gè)最好分和兩個(gè)最低分，對(duì)剩下數(shù)求平均值，則可以直接用公式：
=TRIMMEAN(A1:A12,4/12)
（20）DCOUNT（）函數(shù)
返回?cái)?shù)據(jù)庫或數(shù)據(jù)清單的列中滿足指定條件并且包含數(shù)字的單元格個(gè)數(shù)。
（21）DMAX（數(shù)據(jù)列表或單元格區(qū)域，要統(tǒng)計(jì)的列名稱或列序號(hào)，條件）
例如：
上海員工原工資總數(shù)是：=SUMIF(E3:E18,"上海",G3:G18)
上海員工原工資最高的是：=DMAX(B2:H18,"原工資",E20:E21)
也可以用數(shù)組公式：{=MAX((E3:E18="上海")*(G3:G18))}
上海員工原工資最低的是：=DMIN(B2:H18,G2,E20:E21)
也可以用數(shù)組公式：{=MIN(IF(((E3:E18="上海")*(G3:G18))=0,FALSE,(E3:E18="上海")*(G3:G18)))}
提示：加這句IF(((E3:E18="上海")*(G3:G18))=0,FALSE是利用MIN（）函數(shù)忽略邏輯值的的原理。
（22）求眾數(shù)函數(shù)MODE（）
眾數(shù)即出現(xiàn)頻率最高的數(shù)值。如下圖示例：
求A1：A12中出現(xiàn)頻率最高數(shù)值可以用公式 =mode(a1:a12)結(jié)果是6
注意：MODE參數(shù)中的數(shù)組或引用中的文本、空白單元格、邏輯值將被忽略，但含有零值的單元格將被計(jì)算在內(nèi)，解決的方法如下：
例如：要統(tǒng)計(jì)A1：A12中出現(xiàn)頻率最高的數(shù)值，但零值不計(jì)算在內(nèi)：
用數(shù)組公式 {=MODE(IF(A1:A12=0,FALSE,A1:A12))} 即利用了MODE函數(shù)忽略邏輯值的原理。
（23）求幾何平均數(shù)GEOMEAN（）函數(shù)
幾何平均數(shù)的計(jì)算公式如下：
提示：可以用公式 =product(區(qū)域)^(1/count(區(qū)域)) 代替GEOMEAN（）函數(shù)。
4、第四章 日期與時(shí)間函數(shù)
（1）求兩個(gè)日期之間的天數(shù)差。
假設(shè)在A1填入2006-12-1,在A2填入2006-12-31，則公式：Datedif(a1,a2,”d”)即可。
當(dāng)然最簡單的方法是直接用公式： =a2-a1即可。
（2）DATE（年，月，日）函數(shù)
參數(shù)中的年可以為1至4位數(shù)值默認(rèn)情況下EXCEL使用1900日期系統(tǒng)：
a.如果 year 位于 0（零）到 1899（含）之間，則 Excel 會(huì)將該值加上 1900，再計(jì)算年份。例如，DATE(100,1,2) 將返回 2000 (1900+100) 年 1 月 2 日。
b.如果 year 位于 1900 到 9999（含）之間，則 Excel 將使用該數(shù)值作為年份。例如，DATE(2000,1,2) 將返回 2000 年 1 月 2 日。
c.如果 year 小于 0 或大于等于 10000，則 Excel 將返回錯(cuò)誤值 #NUM!。
5、第五章 文本和數(shù)據(jù)函數(shù)
（1）CELL（信息類型，引用）
函數(shù)返回某一個(gè)引用區(qū)域的左上角的單元格格式、位置或內(nèi)容等信息。
如果“引用”忽略，則返回最后更改的單元格所對(duì)應(yīng)的信息。
如：=mid(CELL(“filename”),find（”[“, CELL(“filename”)）+1，255）
返回最后修改的單元格所在的工作表，而如果想返回當(dāng)前單元格所在工作表應(yīng)該用：
=MID(CELL("filename",A1),FIND(")",CELL("filename",A1))+1,255)
（2）CELL（）、MID（）、LEFT（）、RIGHT（）等函數(shù)
=CELL("filename")'獲取當(dāng)前工作簿的路徑、文件名、工作簿名稱
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) ‘獲取路徑
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename")]-1)‘獲取文件名
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(")",CELL("filename"))) ’獲取當(dāng)前工作表名稱
（3）DOLLAR（）函數(shù)和RMB（）函數(shù)
DOLLAR（）函數(shù)可以在數(shù)值前添加美元標(biāo)識(shí)＄
RMB（）函數(shù)可以在數(shù)值前添加人民幣標(biāo)識(shí)￥
注意：使用“格式”菜單中的“單元格”命令來設(shè)置包含數(shù)字的單元格的格式與使用 DOLLAR 函數(shù)直接設(shè)置數(shù)字的格式之間的區(qū)別在于：DOLLAR 函數(shù)將結(jié)果轉(zhuǎn)換為文本，而使用“單元格”命令設(shè)置格式的數(shù)字仍為數(shù)字。但可以繼續(xù)在公式中使用由 DOLLAR 函數(shù)設(shè)置了格式的數(shù)字，因?yàn)?Microsoft Excel 在計(jì)算公式時(shí)會(huì)將以文本值輸入的數(shù)字轉(zhuǎn)換為數(shù)字。
（4）PHONETIC（）函數(shù)
要在EXCEL中的漢字添加拼音，按以下步驟操作：
選中要設(shè)置拼音的單元格區(qū)域，執(zhí)行“格式”/“拼音信息”/“顯示或隱藏”，使輸入拼音的區(qū)域顯示出來，再執(zhí)行“格式”/“拼音信息”/“編輯”，就可以在單元格的上半部分輸入相應(yīng)的拼音了。
但一般不建議這樣做，因?yàn)闊o法輸入帶聲調(diào)的拼音，可以先在WORD中輸入相應(yīng)漢字，選中漢字并執(zhí)行“格式”/“中文版式”/“拼音指南”，打開“拼音指南”對(duì)話框，單擊“組合”按鈕，將拼音字符組合在一起，選中拼音字符串并按CTRL+C，將拼音字符串復(fù)制下來，切換到EXCEL中，將帶聲調(diào)的拼音字符串粘貼到EXCEL中要編輯拼音的上半部空白處即可。如果要對(duì)拼音做調(diào)整，可以依次單擊“格式”/“拼音信息”/“設(shè)置”。
如果要將單元格中的拼音字符串提取出來，可以用PHONETIC（）函數(shù)，假設(shè)含有漢語拼音的字符串在A1單元格，則在C1輸入公式=PHONETIC（A1）即可。
提示：如果用PHONETIC（）函數(shù)提取出來的拼音信息含有漢字，解決的方法是手工清除源單元格中漢字之間的空格即可。
如果PHONETIC（）函數(shù)的參數(shù)為單元格區(qū)域，則返回單元格區(qū)域左上角單元格中的拼音字符串。
（5）如何清除單元格中用ALT+回車鍵進(jìn)行的回車換行符？
可以按以下步驟操作：
編輯/替換，在“查找內(nèi)容”框中鍵入 ALT鍵的同時(shí)，從小鍵盤輸入10這兩個(gè)數(shù)值，輸入完畢后，查找框中不會(huì)出現(xiàn)什么內(nèi)容，然后直接單擊“全部替換”按鈕，即可將所有通過ALT+回車組合產(chǎn)生的換行符刪除了。
（6）MID（文本，開始位置，字符數(shù)）函數(shù)
用途：返回文本字符串從指定位置開始的特定數(shù)目的字符。
注意：
a.如果 開始位置 大于文本長度，則 MID 返回空文本 ("")。
b.如果 開始位置 小于文本長度，但開始位置加上 字符數(shù) 超過了文本的長度，則 MID 只返回最多到文本末尾的字符。
c.如果 開始位置小于 1，則 MID 返回錯(cuò)誤值 #VALUE!。
d.如果 字符數(shù) 是負(fù)數(shù)，則 MID 返回錯(cuò)誤值 #VALUE!。
（7）PROPER（文本）函數(shù)
用途：將文本字符串的首字母或任何非字母字符之后的首字母轉(zhuǎn)換成大寫，而將其余字母轉(zhuǎn)換成小寫。
（8）ASC（）函數(shù)
用途：將全角（雙字節(jié)）字符變?yōu)榘虢牵▎巫止?jié)）字符。
（9）WIDECHAR（）函數(shù)
用途：將字符串中的半角（單字節(jié)）字符變?yōu)槿牵p字節(jié)）字符。
6、第六章 邏輯函數(shù)
（1）公歷紀(jì)年中如何判斷某年份是否為閏年。
公歷紀(jì)年中閏年的判斷方法是：如果年份能被400整除或者年份能被4整除但卻不能被100整除，則此年份為閏年，否則此年份為平年。
根據(jù)此判斷方法，在EXCEL中可以編公式判斷某年份是否為閏年：
假設(shè)在A1單元格存放年份，則在B1單元格寫公式：
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"閏年","平年")
也可以將此功能做成自定義函數(shù)，方便以后調(diào)用：
Function isLeapYear(year As Integer) As Boolean '判斷某年份是否為閏年
isLeapYear = (year Mod 400 = 0) Or ((year Mod 4 = 0) And (year Mod 100 <> 0))
End Function
調(diào)用時(shí)，假設(shè)年份在A1，在B1單元格輸入 =isleapyear(A1) 返回結(jié)果為TRUE表示是閏年，返回為FALSE表示不是閏年
（2）中國式排名的方法
使用RANK對(duì)區(qū)域排名時(shí)，如果出現(xiàn)相同的數(shù)據(jù)時(shí)，排名是按1、1、3、4、5、5、7…這樣的形式，請(qǐng)問，如何才能實(shí)現(xiàn)依然按1、1、2、3、4、4、5…這樣的順序排名？
比如：
B列為銷售數(shù)量，C列是用RANK（）函數(shù)做的排名，現(xiàn)在想實(shí)現(xiàn)D列式的排名，可以在D2單元格輸入數(shù)組公式：
=SUM(IF(B$2:B$9>B2,1/COUNTIF(B$2:B$9,B$2:B$9),""))+1
輸入完畢后按CTRL+SHIFT+回車 表示作為數(shù)組公式輸入
然后將D2公式向下拖動(dòng)復(fù)制到D9即可。
7、第七章 查找和引用函數(shù)
（1）如何禁止在單元格輸入數(shù)據(jù)時(shí)出現(xiàn)空格？
可以通過數(shù)據(jù)有效性來實(shí)現(xiàn)。比如以在A列輸入數(shù)值并禁止輸入空格為例，具體方法是：
選中A列，執(zhí)行“數(shù)據(jù)”/“有效性”/在“設(shè)置”選項(xiàng)卡的“允許”下拉框中選擇“自定義”，在下面的公式框中輸入公式 =ISERROR(FIND(" ",A1))，然后可以在“出錯(cuò)警告”選項(xiàng)卡里輸入出現(xiàn)錯(cuò)誤時(shí)彈出的自定義提示信息。
（2）ISNONTEXT（value）函數(shù)
如果value的值不是文本，則此函數(shù)返回TRUE，如果value值引用的單元格為空白單元格時(shí)，函數(shù)返回TRUE
（3）根據(jù)指定的日期，判斷其所屬月份的上旬、中旬、下旬。
如圖示例：根據(jù)C2單元格的日期判斷是上旬、中旬、下旬?？梢杂肔OOKUP函數(shù)和DAY（）函數(shù)，在D2單元格輸入公式：=LOOKUP(DAY(C2),{1,11,21},{"上旬","中旬","下旬"})
比如C4單元格的2005-12-19，DAY（C4）結(jié)果為19，然后在{1,11,21}里查找19，因?yàn)闆]找到19，所以查找小于等于19的值，結(jié)果找到11，最后返回11對(duì)應(yīng)的數(shù)組{"上旬","中旬","下旬"}中的第2個(gè)值“中旬”。
（4）MAX（）函數(shù)
MAX（）函數(shù)參數(shù)如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數(shù)引用不能忽略邏輯值、文本，則要用MAXA()函數(shù)。
例如：
統(tǒng)計(jì)A1：A12中最后出現(xiàn)數(shù)值為65的單元格所在行號(hào),結(jié)果是11
{=MAX(IF(A1:A12=65,ROW(A1:A12),""))}
公式中間過程IF(A1:A12=65,ROW(A1:A12),"")的結(jié)果是"",2, "","","",6, "","","","",11, ""
然后對(duì)這個(gè)數(shù)組求最大值，因?yàn)镸AX（）忽略空白字符串，所以2、6、11中最大值是11
（5）在單元格區(qū)域生成指定目錄下的文件列表。
利用EXCEL中的宏表函數(shù)FILES（）可以生成指定目錄下的文件列表，結(jié)果是一個(gè)水平文本數(shù)組。
例如：想在A列填充“D:mp3音樂書香音樂河月”文件夾下的文件名列表，可以執(zhí)行“插入”/“名稱”/“定義”，在名稱框中填寫FILELISTS，在引用位置填=FILES("D:mp3音樂書香音樂河月*.*") ，然后再添加一個(gè)名稱ML，引用位置填寫=TRANSPOSE(FILELISTS)
現(xiàn)在關(guān)閉定義名稱對(duì)話框，選中A列，輸入ML ，然后按CTRL+SHIFT+回車結(jié)束輸入。
這樣會(huì)在A列顯示出指定目錄下的文件名列表，A列出現(xiàn)的#N/A表示的是選中的單元格個(gè)數(shù)大于文件名列表的個(gè)數(shù)。
（6）、MOD（被除數(shù)，除數(shù)）函數(shù)
可以返回兩數(shù)相除的余數(shù)，結(jié)果的正負(fù)號(hào)與除數(shù)相同。
MOD（）函數(shù)可以用INT函數(shù)取代： mod(n,d)=n-d*int(n/d)
（7）、ROW（引用）函數(shù)
返回引用的行號(hào)，如果省略“引用”，則對(duì)ROW（）所在單元格的引用，如果“引用”是一個(gè)單元格區(qū)域，則結(jié)果是一個(gè)垂直
8、第八章 工程和數(shù)據(jù)庫函數(shù)
（1）單位換算函數(shù)CONVERT（引用單元格，原單位，新單位）
例如：單元格A1的數(shù)值單位是“磅”，現(xiàn)在想轉(zhuǎn)換成單位“公斤”，則公式：
=CONVERT(A1,"lbm","kg")
例如：單元格A2是“英尺”數(shù)據(jù)，單元格B2是“英寸”數(shù)據(jù)，現(xiàn)在想將A2和B2數(shù)據(jù)同時(shí)轉(zhuǎn)換成“米”，并求和，則C2填公式：
=CONVERT(A2,"ft","m")+CONVERT(B2,"in","m")
提示：如果結(jié)果為#NAME，則要先加載分析工具庫，步驟：工具/加載宏/“分析工具庫”打勾。
（2）用DSUM（）函數(shù)實(shí)現(xiàn)多條件求和
例如：下圖是一份原料采購清單，現(xiàn)在想對(duì)采購時(shí)間在2005-12-10日以前（含2005-12-10日），并且商品名稱為顯示器的采購金額進(jìn)行求和，首先要在E2：F3單元格建立條件區(qū)域，接著在G3單元格輸入公式：
=DSUM(A2:D20,"金額",E2:F3)其中A2：D20是整個(gè)數(shù)據(jù)區(qū)域，“金額”是要計(jì)算的列字段標(biāo)題，必須要與數(shù)據(jù)區(qū)域的列字段標(biāo)題保持一致，E2：F3是條件區(qū)域，條件區(qū)域也要有字段標(biāo)題。
Excel函數(shù)應(yīng)用實(shí)例：按性別統(tǒng)計(jì)職工數(shù)
2008-1-4
a.函數(shù)分解
COUNTIF函數(shù)計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。
語法：COUNTIF(range,criteria)
Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域;Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。
b.實(shí)例分析
假設(shè)上面使用的人事管理工作表中有599條記錄，統(tǒng)計(jì)職工中男性和女性人數(shù)的方法是:選中單元格D601(或其他用不上的空白單元格)，統(tǒng)計(jì)男性職工人數(shù)可以在其中輸入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接著選中單元格D602，在其中輸入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回車后即可得到“男399人”、“女200人”。
上式中D2:D600是對(duì)“性別”列數(shù)據(jù)區(qū)域的引用，實(shí)際使用時(shí)必須根據(jù)數(shù)據(jù)個(gè)數(shù)進(jìn)行修改?！澳小被颉芭眲t是條件判斷語句，用來判斷區(qū)域中符合條件的數(shù)據(jù)然后進(jìn)行統(tǒng)計(jì)?！?amp;”則是字符連接符，可以在統(tǒng)計(jì)結(jié)果的前后加上“男”、“人”字樣，使其更具有可讀性。
七、Excel函數(shù)：IF函數(shù)
1、IF函數(shù)說明
IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù) IF 對(duì)數(shù)值和公式進(jìn)行條件檢測。
它的語法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。本參數(shù)可使用任何比較運(yùn)算符。
Value_if_true顯示在logical_test 為 TRUE 時(shí)返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 為 FALSE 時(shí)返回的值。Value_if_false 也可以是其他公式。
簡言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層，用 value_if_false 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測條件。
Excel 還提供了可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF 工作表函數(shù)。
2、IF函數(shù)應(yīng)用
（1） 輸出帶有公式的空白表單
圖5 人事分析表1
(2)以圖中所示的人事狀況分析表
由于各部門關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫，在總計(jì)欄（以單元格G5為例）公式為：=SUM(C5:F5)
我們看到計(jì)算為0的結(jié)果。如果這樣的表格打印出來就頁面的美觀來看顯示是不令人滿意的。是否有辦法去掉總計(jì)欄中的0呢？你可能會(huì)說，不寫公式不就行了。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了IF函數(shù)的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實(shí)現(xiàn)呢？只需將總計(jì)欄中的公式（僅以單元格G5為例）改寫成：
=IF(SUM(C5:F5),SUM(C5:F5),"")
通俗的解釋就是：如果SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則顯示字符串。
幾點(diǎn)說明：
a. SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；
b. ""表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。
如果對(duì)上述例子有了很好的理解后，我們就很容易將IF函數(shù)應(yīng)用到更廣泛的領(lǐng)域。比如，在成績表中根據(jù)不同的成績區(qū)分合格與不合格?，F(xiàn)在我們就以某班級(jí)的英語成績?yōu)槔唧w說明用法。
圖6
某班級(jí)的成績?nèi)鐖D6所示，為了做出最終的綜合評(píng)定，我們?cè)O(shè)定按照平均分判斷該學(xué)生成績是否合格的規(guī)則。如果各科平均分超過60分則認(rèn)為是合格的，否則記作不合格。
根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公式（以單元格B12為例）： =IF(B11>60,"合格","不合格")
語法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個(gè)參數(shù)即在單元格B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格B12中顯示不合格字樣。
在綜合評(píng)定欄中可以看到由于C列的同學(xué)各科平均分為54分，綜合評(píng)定為不合格。其余均為合格。
(3) 多層嵌套函數(shù)的應(yīng)用
在上述的例子中，我們只是將成績簡單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績通常是有多個(gè)等級(jí)的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來實(shí)現(xiàn)。仍以上例為例，我們?cè)O(shè)定綜合評(píng)定的規(guī)則為當(dāng)各科平均分超過90時(shí)，評(píng)定為優(yōu)秀。如圖7所示。
圖7
說明：為了解釋起來比較方便，我們?cè)谶@里僅做兩重嵌套的示例，您可以按照實(shí)際情況進(jìn)行更多重的嵌套，但請(qǐng)注意Excel的IF函數(shù)最多允許七重嵌套。
根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公式（以單元格F12為例）：
=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格"),"不合格")
語法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90（為了讓大家體會(huì)一下AND函數(shù)的應(yīng)用，寫成AND(F11>90)，實(shí)際上可以僅寫F11>90），如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格F12中顯示不合格字樣。
在綜合評(píng)定欄中可以看到由于F列的同學(xué)各科平均分為92分，綜合評(píng)定為優(yōu)秀。
八、Excel查詢與引用函數(shù)：HLOOKUP、LOOKUP、MATCH、VLOOKUP
1、LOOKUP函數(shù)與MATCH函數(shù)
LOOKUP函數(shù)可以返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。此系列函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，使用函數(shù) HLOOKUP。當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時(shí)，使用函數(shù) VLOOKUP。
如果需要找出匹配元素的位置而不是匹配元素本身，則應(yīng)該使用函數(shù) MATCH 而不是函數(shù) LOOKUP。MATCH函數(shù)用來返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。從以上分析可知，查找函數(shù)的功能，一是按搜索條件，返回被搜索區(qū)域內(nèi)數(shù)據(jù)的一個(gè)數(shù)據(jù)值；二是按搜索條件，返回被搜索區(qū)域內(nèi)某一數(shù)據(jù)所在的位置值。利用這兩大功能，不僅能實(shí)現(xiàn)數(shù)據(jù)的查詢，而且也能解決如"定級(jí)"之類的實(shí)際問題。
2、LOOKUP用于返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。
函數(shù) LOOKUP 有兩種語法形式：向量和數(shù)組。
（1） 向量形式
函數(shù) LOOKUP 的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。
其基本語法形式為LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value為函數(shù) LOOKUP 在第一個(gè)向量中所要查找的數(shù)值。Lookup_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。
Lookup_vector為只包含一行或一列的區(qū)域。Lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值。
需要注意的是Lookup_vector 的數(shù)值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數(shù) LOOKUP 不能返回正確的結(jié)果。文本不區(qū)分大小寫。
Result_vector 只包含一行或一列的區(qū)域，其大小必須與 lookup_vector 相同。
如果函數(shù) LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小于或等于 lookup_value 的最大數(shù)值。
如果 lookup_value 小于 lookup_vector 中的最小值，函數(shù) LOOKUP 返回錯(cuò)誤值 #N/A。
示例詳見圖3
圖3
（2） 數(shù)組形式
函數(shù) LOOKUP 的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的數(shù)值。通常情況下，最好使用函數(shù) HLOOKUP 或函數(shù) VLOOKUP 來替代函數(shù) LOOKUP 的數(shù)組形式。函數(shù) LOOKUP 的這種形式主要用于與其他電子表格兼容。關(guān)于LOOKUP的數(shù)組形式的用法在此不再贅述，感興趣的可以參看Excel的幫助。
3、 HLOOKUP與VLOOKUP
HLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。
VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。
當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，請(qǐng)使用函數(shù) HLOOKUP。
當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時(shí)，請(qǐng)使用函數(shù) VLOOKUP。
語法形式為：
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
其中，Lookup_value表示要查找的值，它必須位于自定義查找區(qū)域的最左列。Lookup_value 可以為數(shù)值、引用或文字串。
Table_array查找的區(qū)域，用于查找數(shù)據(jù)的區(qū)域，上面的查找值必須位于這個(gè)區(qū)域的最左列。可以使用對(duì)區(qū)域或區(qū)域名稱的引用。
Row_index_num為 table_array 中待返回的匹配值的行序號(hào)。Row_index_num 為 1 時(shí)，返回 table_array 第一行的數(shù)值，row_index_num 為 2 時(shí)，返回 table_array 第二行的數(shù)值，以此類推。
Col_index_num為相對(duì)列號(hào)。最左列為1，其右邊一列為2，依此類推.
Range_lookup為一邏輯值，指明函數(shù) HLOOKUP 查找時(shí)是精確匹配，還是近似匹配。
下面詳細(xì)介紹一下VLOOKUP函數(shù)的應(yīng)用。
簡言之，VLOOKUP函數(shù)可以根據(jù)搜索區(qū)域內(nèi)最左列的值，去查找區(qū)域內(nèi)其它列的數(shù)據(jù)，并返回該列的數(shù)據(jù)，對(duì)于字母來說，搜索時(shí)不分大小寫。所以，函數(shù)VLOOKUP的查找可以達(dá)到兩種目的：一是精確的查找。二是近似的查找。下面分別說明。
（1） 精確查找--根據(jù)區(qū)域最左列的值，對(duì)其它列的數(shù)據(jù)進(jìn)行精確的查找
示例：創(chuàng)建工資表與工資條
首先建立員工工資表
Excel函數(shù)應(yīng)用之統(tǒng)計(jì)函數(shù)
（周國彬2001年06月26日 09:47）
編者語：Excel是辦公室自動(dòng)化中非常重要的一款軟件，很多巨型國際企業(yè)都是依靠Excel進(jìn)行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對(duì)數(shù)據(jù)的自動(dòng)處理和計(jì)算，然而很多缺少理工科背景或是對(duì)Excel強(qiáng)大數(shù)據(jù)處理功能不了解的人卻難以進(jìn)一步深入。編者以為，對(duì)Excel函數(shù)應(yīng)用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份內(nèi)容的教學(xué)文章卻又很少見，所以特別組織了這一個(gè)《Excel函數(shù)應(yīng)用》系列，希望能夠?qū)xcel進(jìn)階者有所幫助。《Excel函數(shù)應(yīng)用》系列，將每周更新，逐步系統(tǒng)的介紹Excel各類函數(shù)及其應(yīng)用，敬請(qǐng)關(guān)注！
九、Excel統(tǒng)計(jì)函數(shù)
Excel的統(tǒng)計(jì)工作表函數(shù)用于對(duì)數(shù)據(jù)區(qū)域進(jìn)行統(tǒng)計(jì)分析。例如，統(tǒng)計(jì)工作表函數(shù)可以用來統(tǒng)計(jì)樣本的方差、數(shù)據(jù)區(qū)間的頻率分布等。是不是覺得好像是很專業(yè)范疇的東西？是的，統(tǒng)計(jì)工作表函數(shù)中提供了很多屬于統(tǒng)計(jì)學(xué)范疇的函數(shù)，但也有些函數(shù)其實(shí)在你我的日常生活中是很常用的，比如求班級(jí)平均成績，排名等。在本文中，主要介紹一些常見的統(tǒng)計(jì)函數(shù)，而屬于統(tǒng)計(jì)學(xué)范疇的函數(shù)不在此贅述，詳細(xì)的使用方法可以參考Excel幫助及相關(guān)的書籍。
在介紹統(tǒng)計(jì)函數(shù)之前，請(qǐng)大家先看一下附表中的函數(shù)名稱。是不是發(fā)現(xiàn)有些函數(shù)是很類似的，只是在名稱中多了一個(gè)字母A？比如，AVERAGE與AVERAGEA；COUNT與COUNTA。基本上，名稱中帶A的函數(shù)在統(tǒng)計(jì)時(shí)不僅統(tǒng)計(jì)數(shù)字，而且文本和邏輯值（如TRUE 和 FALSE）也將計(jì)算在內(nèi)。在下文中筆者將主要介紹不帶A的幾種常見函數(shù)的用法。
1、用于求平均值的統(tǒng)計(jì)函數(shù)AVERAGE、TRIMMEAN
（1）求參數(shù)的算術(shù)平均值函數(shù)AVERAGE
語法形式為AVERAGE(number1,number2, ...)
其中Number1, number2, ...為要計(jì)算平均值的 1～30 個(gè)參數(shù)。這些參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計(jì)算在內(nèi)。
（2）求數(shù)據(jù)集的內(nèi)部平均值TRIMMEAN
函數(shù)TRIMMEAN先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點(diǎn)，然后再求平均值。當(dāng)希望在分析中剔除一部分?jǐn)?shù)據(jù)的計(jì)算時(shí)，可以使用此函數(shù)。比如，我們?cè)谟?jì)算選手平均分?jǐn)?shù)中常用去掉一個(gè)最高分，去掉一個(gè)最低分，XX號(hào)選手的最后得分，就可以使用該函數(shù)來計(jì)算。
語法形式為TRIMMEAN(array,percent)
其中Array為需要進(jìn)行篩選并求平均值的數(shù)組或數(shù)據(jù)區(qū)域。Percent為計(jì)算時(shí)所要除去的數(shù)據(jù)點(diǎn)的比例，例如，如果 percent = 0.2，在 20 個(gè)數(shù)據(jù)點(diǎn)的集合中，就要除去 4 個(gè)數(shù)據(jù)點(diǎn)（20 x 0.2），頭部除去 2 個(gè)，尾部除去 2 個(gè)。函數(shù) TRIMMEAN 將除去的數(shù)據(jù)點(diǎn)數(shù)目向下舍為最接近的 2 的倍數(shù)。
（3）舉例說明：
示例中也列舉了帶A的函數(shù)AVERAGEA的求解方法。
求選手Annie的參賽分?jǐn)?shù)。在這里，我們先假定已經(jīng)將該選手的分?jǐn)?shù)進(jìn)行了從高到底的排序，在后面的介紹中我們將詳細(xì)了解排序的方法。
圖1
2、用于求單元格個(gè)數(shù)的統(tǒng)計(jì)函數(shù)COUNT
語法形式為COUNT(value1,value2, ...)
其中Value1, value2, ...為包含或引用各種類型數(shù)據(jù)的參數(shù)（1～30個(gè)），但只有數(shù)字類型的數(shù)據(jù)才被計(jì)數(shù)。函數(shù) COUNT 在計(jì)數(shù)時(shí)，將把數(shù)字、空值、邏輯值、日期或以文字代表的數(shù)計(jì)算進(jìn)去；但是錯(cuò)誤值或其他無法轉(zhuǎn)化成數(shù)字的文字則被忽略。
如果參數(shù)是一個(gè)數(shù)組或引用，那么只統(tǒng)計(jì)數(shù)組或引用中的數(shù)字；數(shù)組中或引用的空單元格、邏輯值、文字或錯(cuò)誤值都將忽略。如果要統(tǒng)計(jì)邏輯值、文字或錯(cuò)誤值，應(yīng)當(dāng)使用函數(shù) COUNTA。
舉例說明COUNT函數(shù)的用途，示例中也列舉了帶A的函數(shù)COUNTA的用途。仍以上例為例，要計(jì)算一共有多少評(píng)委參與評(píng)分（用函數(shù)COUNTA），以及有幾個(gè)評(píng)委給出了有效分?jǐn)?shù)（用函數(shù)COUNT）。
圖2
3、求區(qū)域中數(shù)據(jù)的頻率分布FREQUENCY
由于函數(shù) FREQUENCY 返回一個(gè)數(shù)組，必須以數(shù)組公式的形式輸入。
語法形式為FREQUENCY(data_array,bins_array)
其中Data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計(jì)算頻率。如果 data_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回零數(shù)組。Bins_array為一數(shù)組或?qū)?shù)組區(qū)域的引用，設(shè)定對(duì) data_array 進(jìn)行頻率計(jì)算的分段點(diǎn)。如果 bins_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回 data_array 元素的數(shù)目。
看起來FREQUENCY的用法蠻復(fù)雜的，但其用處很大。比如可以計(jì)算不同工資段的人員分布，公司員工的年齡分布，學(xué)生成績的分布情況等。這里以具體示例說明其基本的用法。
以計(jì)算某公司的員工年齡分布情況為例說明。在工作表里列出了員工的年齡。這些年齡為 28、25、31、21、44、33、22 和 35，并分別輸入到單元格 C4:C11。這一列年齡就是 data_array。Bins_array 是另一列用來對(duì)年齡分組的區(qū)間值。在本例中，bins_array 是指 C13:C16 單元格，分別含有值 25、30、35、和 40。以數(shù)組形式輸入函數(shù) FREQUENCY，就可以計(jì)算出年齡在 25歲以下、26~30歲、31~35歲、36~40歲和40歲以上各區(qū)間中的數(shù)目。本例中選擇了5個(gè)垂直相鄰的單元格后，即以數(shù)組公式輸入下面的公式。返回的數(shù)組中的元素個(gè)數(shù)比 bins_array（數(shù)組）中的元素個(gè)數(shù)多 1。第五個(gè)數(shù)字1表示大于最高間隔 (40) 的數(shù)值（44）的個(gè)數(shù)。函數(shù) FREQUENCY 忽略空白單元格和文本值。
{=FREQUENCY(C4:C11,C13:C16)}等于 {2;2;2;1;1}
圖3
4、一組用于求數(shù)據(jù)集的滿足不同要求的數(shù)值的函數(shù)
（1）求數(shù)據(jù)集的最大值MAX與最小值MIN
這兩個(gè)函數(shù)MAX、MIN就是用來求解數(shù)據(jù)集的極值（即最大值、最小值）。函數(shù)的用法非常簡單。語法形式為函數(shù)（number1,number2,...），其中Number1,number2,... 為需要找出最大數(shù)值的 1 到 30 個(gè)數(shù)值。如果要計(jì)算數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。因此如果邏輯值和文本不能忽略，請(qǐng)使用帶A的函數(shù)MAXA或者M(jìn)INA 來代替。
（2）求數(shù)據(jù)集中第K個(gè)最大值LARGE與第k個(gè)最小值SMALL
這兩個(gè)函數(shù)LARGE、SMALL與MAX、MIN非常想像，區(qū)別在于它們返回的不是極值，而是第K個(gè)值。語法形式為：函數(shù)(array,k)，其中Array為需要找到第 k 個(gè)最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。K為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置（如果是LARGE為從大到小排，若為SMALL函數(shù)則從小到大排）。
說到這，大家可以想得到吧。如果K=1或者K=n（假定數(shù)據(jù)集中有n個(gè)數(shù)據(jù)）的時(shí)候，是不是就可以返回?cái)?shù)據(jù)集的最大值或者最小值了呢。
（3） 求數(shù)據(jù)集中的中位數(shù)MEDIAN
MEDIAN函數(shù)返回給定數(shù)值集合的中位數(shù)。所謂中位數(shù)是指在一組數(shù)據(jù)中居于中間的數(shù)，換句話說，在這組數(shù)據(jù)中，有一半的數(shù)據(jù)比它大，有一半的數(shù)據(jù)比它小。
語法形式為MEDIAN(number1,number2, ...)其中Number1, number2,...是需要找出中位數(shù)的 1 到 30 個(gè)數(shù)字參數(shù)。如果數(shù)組或引用參數(shù)中包含有文字、邏輯值或空白單元格，則忽略這些值，但是其值為零的單元格會(huì)計(jì)算在內(nèi)。
需要注意的是，如果參數(shù)集合中包含有偶數(shù)個(gè)數(shù)字，函數(shù) MEDIAN 將返回位于中間的兩個(gè)數(shù)的平均值。
（4） 求數(shù)據(jù)集中出現(xiàn)頻率最多的數(shù)MODE
MODE函數(shù)用來返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。跟 MEDIAN 一樣，MODE 也是一個(gè)位置測量函數(shù)。
語法形式為MODE(number1,number2, ...)其中Number1, number2, ... 是用于眾數(shù)（眾數(shù)指在一組數(shù)值中出現(xiàn)頻率最高的數(shù)值）計(jì)算的 1 到 30 個(gè)參數(shù)，也可以使用單一數(shù)組（即對(duì)數(shù)組區(qū)域的引用）來代替由逗號(hào)分隔的參數(shù)。
（5） 以上函數(shù)的示例
以某單位年終獎(jiǎng)金分配表為例說明。在示例中，我們將利用這些函數(shù)求解該單位年終獎(jiǎng)金分配中的最高金額、最低金額、平均金額、中間金額、眾數(shù)金額以及第二高金額等。
詳細(xì)的公式寫法可從圖中清楚的看出，在此不再贅述。
圖4
5、用來排位的函數(shù)RANK、PERCENTRANK
（1）一個(gè)數(shù)值在一組數(shù)值中的排位的函數(shù)RANK
數(shù)值的排位是與數(shù)據(jù)清單中其他數(shù)值的相對(duì)大小，當(dāng)然如果數(shù)據(jù)清單已經(jīng)排過序了，則數(shù)值的排位就是它當(dāng)前的位置。數(shù)據(jù)清單的排序可以使用Excel提供的排序功能完成。
語法形式為RANK(number,ref,order) 其中Number為需要找到排位的數(shù)字；Ref 為包含一組數(shù)字的數(shù)組或引用。Order為一數(shù)字用來指明排位的方式。
如果 order 為 0 或省略，則Excel 將 ref 當(dāng)作按降序排列的數(shù)據(jù)清單進(jìn)行排位。
如果 order 不為零，Microsoft Excel 將 ref 當(dāng)作按升序排列的數(shù)據(jù)清單進(jìn)行排位。
需要說明的是，函數(shù) RANK 對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。嗯，這就好像并列第幾的概念啊。例如，在一列整數(shù)里，如果整數(shù) 10 出現(xiàn)兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數(shù)值）。
（2）求特定數(shù)值在一個(gè)數(shù)據(jù)集中的百分比排位的函數(shù)PERCENTRANK
此PERCENTRANK函數(shù)可用于查看特定數(shù)據(jù)在數(shù)據(jù)集中所處的位置。例如，可以使用函數(shù) PERCENTRANK 計(jì)算某個(gè)特定的能力測試得分在所有的能力測試得分中的位置。
語法形式為PERCENTRANK(array,x,significance) 其中Array為彼此間相對(duì)位置確定的數(shù)字?jǐn)?shù)組或數(shù)字區(qū)域。X為數(shù)組中需要得到其排位的值。Significance為可選項(xiàng)，表示返回的百分?jǐn)?shù)值的有效位數(shù)。如果省略，函數(shù) PERCENTRANK 保留 3 位小數(shù)。
（3）與排名有關(guān)的示例
仍以某單位的年終獎(jiǎng)金分配為例說明，這里以員工Annie的排名為例說明公式的寫法。
獎(jiǎng)金排名的公式寫法為：
=RANK(C3,$C$3:$C$12)
百分比排名的公式寫法為：
=PERCENTRANK($C$3:$C$12,C3)
圖5
以上我們介紹了Excel統(tǒng)計(jì)函數(shù)中比較常用的幾種函數(shù)，更多的涉及專業(yè)領(lǐng)域的統(tǒng)計(jì)函數(shù)可以參看附表以及各種相關(guān)的統(tǒng)計(jì)學(xué)書籍。
6、函數(shù)、函數(shù)說明及語法形式附表：
函數(shù)名稱
函數(shù)說明
語法形式
AVEDEV
返回一組數(shù)據(jù)與其均值的絕對(duì)偏差的平均值，即離散度。
AVEDEV(number1,number2, ...)
AVERAGE
返回參數(shù)算術(shù)平均值。
AVERAGE(number1,number2, ...)
AVERAGEA
計(jì)算參數(shù)清單中數(shù)值的平均值（算數(shù)平均值）。不僅數(shù)字，而且文本和邏輯值（如TRUE 和 FALSE）也將計(jì)算在內(nèi)。
AVERAGEA(value1,value2,...)
BETADIST
返回 Beta 分布累積函數(shù)的函數(shù)值。Beta 分布累積函數(shù)通常用于研究樣本集合中某些事物的發(fā)生和變化情況。
BETADIST(x,alpha,beta,A,B)
BETAINV
返回 beta 分布累積函數(shù)的逆函數(shù)值。即，如果 probability = BETADIST(x,...)，則 BETAINV(probability,...) = x。beta 分布累積函數(shù)可用于項(xiàng)目設(shè)計(jì)，在給定期望的完成時(shí)間和變化參數(shù)后，模擬可能的完成時(shí)間。
BETAINV(probability,alpha,beta,A,B)
BINOMDIST
返回一元二項(xiàng)式分布的概率值。
BINOMDIST(number_s,trials,probability_s,cumulative)
CHIDIST
返回 γ2 分布的單尾概率。γ2 分布與 γ2 檢驗(yàn)相關(guān)。使用 γ2 檢驗(yàn)可以比較觀察值和期望值。
CHIDIST(x,degrees_freedom)
CHIINV
返回 γ2 分布單尾概率的逆函數(shù)。
CHIINV(probability,degrees_freedom)
CHITEST
返回獨(dú)立性檢驗(yàn)值。函數(shù) CHITEST 返回 γ2 分布的統(tǒng)計(jì)值及相應(yīng)的自由度。
CHITEST(actual_range,expected_range)
CONFIDENCE
返回總體平均值的置信區(qū)間。置信區(qū)間是樣本平均值任意一側(cè)的區(qū)域。
CONFIDENCE(alpha,standard_dev,size)
CORREL
返回單元格區(qū)域 array1 和 array2 之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。
CORREL(array1,array2)
COUNT
返回參數(shù)的個(gè)數(shù)。利用函數(shù) COUNT 可以計(jì)算數(shù)組或單元格區(qū)域中數(shù)字項(xiàng)的個(gè)數(shù)。
COUNT(value1,value2, ...)
COUNTA
返回參數(shù)組中非空值的數(shù)目。利用函數(shù)COUNTA 可以計(jì)算數(shù)組或單元格區(qū)域中數(shù)據(jù)項(xiàng)的個(gè)數(shù)。
COUNTA(value1,value2, ...)
COVAR
返回協(xié)方差，即每對(duì)數(shù)據(jù)點(diǎn)的偏差乘積的平均數(shù)，利用協(xié)方差可以決定兩個(gè)數(shù)據(jù)集之間的關(guān)系。
COVAR(array1,array2)
CRITBINOM
返回使累積二項(xiàng)式分布大于等于臨界值的最小值。此函數(shù)可以用于質(zhì)量檢驗(yàn)。
CRITBINOM(trials,probability_s,alpha)
DEVSQ
返回?cái)?shù)據(jù)點(diǎn)與各自樣本均值偏差的平方和。
DEVSQ(number1,number2,...)
EXPONDIST
返回指數(shù)分布。使用函數(shù) EXPONDIST 可以建立事件之間的時(shí)間間隔模型。
EXPONDIST(x,lambda,cumulative)
FDIST
返回 F 概率分布。使用此函數(shù)可以確定兩個(gè)數(shù)據(jù)系列是否存在變化程度上的不同。
FDIST(x,degrees_freedom1,degrees_freedom2)
FINV
返回 F 概率分布的逆函數(shù)值。
FINV(probability,degrees_freedom1,degrees_freedom2)
FISHER
返回點(diǎn) x 的 Fisher 變換。該變換生成一個(gè)近似正態(tài)分布而非偏斜的函數(shù)。
FISHER(x)
FISHERINV
返回 Fisher 變換的逆函數(shù)值。使用此變換可以分析數(shù)據(jù)區(qū)域或數(shù)組之間的相關(guān)性。
FISHERINV(y)
FORECAST
根據(jù)給定的數(shù)據(jù)計(jì)算或預(yù)測未來值。
FORECAST(x,known_y's,known_x's)
FREQUENCY
以一列垂直數(shù)組返回某個(gè)區(qū)域中數(shù)據(jù)的頻率分布。
FREQUENCY(data_array,bins_array)
FTEST
返回 F 檢驗(yàn)的結(jié)果。F 檢驗(yàn)返回的是當(dāng)數(shù)組 1 和數(shù)組 2 的方差無明顯差異時(shí)的單尾概率。可以使用此函數(shù)來判斷兩個(gè)樣本的方差是否不同。
FTEST(array1,array2)
GAMMADIST
返回伽瑪分布?？梢允褂么撕瘮?shù)來研究具有偏態(tài)分布的變量。伽瑪分布通常用于排隊(duì)分析。
GAMMADIST(x,alpha,beta,cumulative)
GAMMAINV
返回伽瑪分布的累積函數(shù)的逆函數(shù)。
GAMMAINV(probability,alpha,beta)
GAMMALN
返回伽瑪函數(shù)的自然對(duì)數(shù)，Γ(x)。
GAMMALN(x)
GEOMEAN
返回正數(shù)數(shù)組或數(shù)據(jù)區(qū)域的幾何平均值。
GEOMEAN(number1,number2, ...)
GROWTH
根據(jù)給定的數(shù)據(jù)預(yù)測指數(shù)增長值。
GROWTH(known_y's,known_x's,new_x's,const)
HARMEAN
返回?cái)?shù)據(jù)集合的調(diào)和平均值。調(diào)和平均值與倒數(shù)的算術(shù)平均值互為倒數(shù)。
HARMEAN(number1,number2, ...)
HYPGEOMDIST
返回超幾何分布。
HYPGEOMDIST(sample_s,number_sample,
population_s,number_population)
INTERCEPT
利用已知的 x 值與 y 值計(jì)算直線與 y 軸的截距。
INTERCEPT(known_y's,known_x's)
KURT
返回?cái)?shù)據(jù)集的峰值。
KURT(number1,number2, ...)
LARGE
返回?cái)?shù)據(jù)集里第 k 個(gè)最大值。使用此函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來選擇數(shù)值。
LARGE(array,k)
LINEST
使用最小二乘法計(jì)算對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。
LINEST(known_y's,known_x's,const,stats)
LOGEST
在回歸分析中，計(jì)算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)組。
LOGEST(known_y's,known_x's,const,stats)
LOGINV
返回 x 的對(duì)數(shù)正態(tài)分布累積函數(shù)的逆函數(shù)。
LOGINV(probability,mean,standard_dev)
LOGNORMDIST
返回 x 的對(duì)數(shù)正態(tài)分布的累積函數(shù)。
LOGNORMDIST(x,mean,standard_dev)
MAX
返回?cái)?shù)據(jù)集中的最大數(shù)值。
MAX(number1,number2,...)
MAXA
返回參數(shù)清單中的最大數(shù)值。
MAXA(value1,value2,...)
MEDIAN
返回給定數(shù)值集合的中位數(shù)。中位數(shù)是在一組數(shù)據(jù)中居于中間的數(shù)。
MEDIAN(number1,number2, ...)
MIN
返回給定參數(shù)表中的最小值。
MIN(number1,number2, ...)
MINA
返回參數(shù)清單中的最小數(shù)值。
MINA(value1,value2,...)
MODE
返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。
MODE(number1,number2, ...)
NEGBINOMDIST
返回負(fù)二項(xiàng)式分布。
NEGBINOMDIST(number_f,number_s,probability_s)
NORMDIST
返回給定平均值和標(biāo)準(zhǔn)偏差的正態(tài)分布的累積函數(shù)。
NORMDIST(x,mean,standard_dev,cumulative)
NORMINV
返回給定平均值和標(biāo)準(zhǔn)偏差的正態(tài)分布的累積函數(shù)的逆函數(shù)。
NORMINV(probability,mean,standard_dev)
NORMSDIST
返回標(biāo)準(zhǔn)正態(tài)分布的累積函數(shù)，該分布的平均值為 0，標(biāo)準(zhǔn)偏差為 1。
NORMSDIST(z)
NORMSINV
返回標(biāo)準(zhǔn)正態(tài)分布累積函數(shù)的逆函數(shù)。該分布的平均值為 0，標(biāo)準(zhǔn)偏差為 1。
NORMSINV(probability)
PEARSON
返回 Pearson（皮爾生）乘積矩相關(guān)系數(shù)，r，這是一個(gè)范圍在 -1.0 到 1.0 之間（包括 -1.0 和 1.0 在內(nèi)）的無量綱指數(shù)，反映了兩個(gè)數(shù)據(jù)集合之間的線性相關(guān)程度。
PEARSON(array1,array2)
PERCENTILE
返回?cái)?shù)值區(qū)域的 K 百分比數(shù)值點(diǎn)?？梢允褂么撕瘮?shù)來建立接受閥值。例如，可以確定得分排名在 90 個(gè)百分點(diǎn)以上的檢測侯選人。
PERCENTILE(array,k)
PERCENTRANK
返回特定數(shù)值在一個(gè)數(shù)據(jù)集中的百分比排位。此函數(shù)可用于查看特定數(shù)據(jù)在數(shù)據(jù)集中所處的位置。例如，可以使用函數(shù) PERCENTRANK 計(jì)算某個(gè)特定的能力測試得分在所有的能力測試得分中的位置。
PERCENTRANK(array,x,significance)
PERMUT
返回從給定數(shù)目的對(duì)象集合中選取的若干對(duì)象的排列數(shù)。排列可以為有內(nèi)部順序的對(duì)象或?yàn)槭录娜我饧匣蜃蛹?。排列與組合不同，組合的內(nèi)部順序無意義。此函數(shù)可用于彩票計(jì)算中的概率。
PERMUT(number,number_chosen)
POISSON
返回泊松分布。泊松分布通常用于預(yù)測一段時(shí)間內(nèi)事件發(fā)生的次數(shù)，比如一分鐘內(nèi)通過收費(fèi)站的轎車的數(shù)量。
POISSON(x,mean,cumulative)
PROB
返回一概率事件組中落在指定區(qū)域內(nèi)的事件所對(duì)應(yīng)的概率之和。如果沒有給出 upper_limit，則返回 x _range 內(nèi)值等于 lower_limit 的概率。
PROB(x_range,prob_range,lower_limit,upper_limit)
QUARTILE
返回?cái)?shù)據(jù)集的四分位數(shù)。四分位數(shù)通常用于在銷售額和測量值數(shù)據(jù)集中對(duì)總體進(jìn)行分組。例如，可以使用函數(shù) QUARTILE 求得總體中前 25% 的收入值。
QUARTILE(array,quart)
RANK
返回一個(gè)數(shù)值在一組數(shù)值中的排位。數(shù)值的排位是與數(shù)據(jù)清單中其他數(shù)值的相對(duì)大?。ㄈ绻麛?shù)據(jù)清單已經(jīng)排過序了，則數(shù)值的排位就是它當(dāng)前的位置）。
RANK(number,ref,order)
RSQ
返回根據(jù) known_y's 和 known_x's 中數(shù)據(jù)點(diǎn)計(jì)算得出的 Pearson 乘積矩相關(guān)系數(shù)的平方。有關(guān)詳細(xì)信息，請(qǐng)參閱函數(shù) REARSON。R 平方值可以解釋為 y 方差與 x 方差的比例。
RSQ(known_y's,known_x's)
SKEW
返回分布的偏斜度。偏斜度反映以平均值為中心的分布的不對(duì)稱程度。正偏斜度表示不對(duì)稱邊的分布更趨向正值。負(fù)偏斜度表示不對(duì)稱邊的分布更趨向負(fù)值。
SKEW(number1,number2,...)
SLOPE
返回根據(jù) known_y's 和 known_x's 中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的重直距離與水平距離的比值，也就是回歸直線的變化率。
SLOPE(known_y's,known_x's)
SMALL
返回?cái)?shù)據(jù)集中第 k 個(gè)最小值。使用此函數(shù)可以返回?cái)?shù)據(jù)集中特定位置上的數(shù)值。
SMALL(array,k)
STANDARDIZE
返回以 mean 為平均值，以 standard-dev 為標(biāo)準(zhǔn)偏差的分布的正態(tài)化數(shù)值。
STANDARDIZE(x,mean,standard_dev)
STDEV
估算樣本的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映相對(duì)于平均值（mean）的離散程度。
STDEV(number1,number2,...)
STDEVA
估算基于給定樣本的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映數(shù)值相對(duì)于平均值（mean）的離散程度。文本值和邏輯值（如 TRUE 或 FALSE）也將計(jì)算在內(nèi)。
STDEVA(value1,value2,...)
STDEVP
返回以參數(shù)形式給出的整個(gè)樣本總體的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映相對(duì)于平均值（mean）的離散程度。
STDEVP(number1,number2,...)
STDEVPA
計(jì)算樣本總體的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映數(shù)值相對(duì)于平均值（mean）的離散程度。
STDEVPA(value1,value2,...)
STEYX
返回通過線性回歸法計(jì)算 y 預(yù)測值時(shí)所產(chǎn)生的標(biāo)準(zhǔn)誤差。標(biāo)準(zhǔn)誤差用來度量根據(jù)單個(gè) x 變量計(jì)算出的 y 預(yù)測值的誤差量。
STEYX(known_y's,known_x's)
TDIST
返回學(xué)生 t- 分布的百分點(diǎn)（概率），t 分布中數(shù)值 (x) 是 t 的計(jì)算值（將計(jì)算其百分點(diǎn)）。t 分布用于小樣本數(shù)據(jù)集合的假設(shè)檢驗(yàn)。使用此函數(shù)可以代替 t 分布的臨界值表。
TDIST(x,degrees_freedom,tails)
TINV
返回作為概率和自由度函數(shù)的學(xué)生 t 分布的 t 值。
TINV(probability,degrees_freedom)
TREND
返回一條線性回歸擬合線的一組縱坐標(biāo)值（y 值）。即找到適合給定的數(shù)組 known_y's 和 known_x's 的直線（用最小二乘法），并返回指定數(shù)組 new_x's 值在直線上對(duì)應(yīng)的 y 值。
TREND(known_y's,known_x's,new_x's,const)
TRIMMEAN
返回?cái)?shù)據(jù)集的內(nèi)部平均值。函數(shù) TRIMMEAN 先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點(diǎn)，然后再求平均值。當(dāng)希望在分析中剔除一部分?jǐn)?shù)據(jù)的計(jì)算時(shí)，可以使用此函數(shù)。
TRIMMEAN(array,percent)
TTEST
返回與學(xué)生氏- t 檢驗(yàn)相關(guān)的概率?？梢允褂煤瘮?shù) TTEST 判斷兩個(gè)樣本是否可能來自兩個(gè)具有相同均值的總體。
TTEST(array1,array2,tails,type)
VAR
估算樣本方差。
VAR(number1,number2,...)
VARA
估算基于給定樣本的方差。不僅數(shù)字，文本值和邏輯值（如 TRUE 和 FALSE）也將計(jì)算在內(nèi)。
VARA(value1,value2,...)
