第一章 函數(shù)基礎知識
1、按shift+F3是插入彈出“插入函數(shù)”對話框的快捷鍵。
2、當在單元格中鍵入“=”號時，在“名稱框”中會顯示出常用的函數(shù)列表，可以選擇所需要的。
3、在單元格中輸入公式時，當鍵入函數(shù)名稱時，或者鍵入函數(shù)名稱和左括號時，可按ctrl+shift+A組合鍵顯示函數(shù)的參數(shù)說明。
4、如果在單元格中輸入的公式返回錯誤的信息，想了解這個錯誤信息的含義，如“#NAME”，可以選中此單元格，鼠標光標移動到緊挨此單元格左側的智能標記圖標上，就會出現(xiàn)“公式中包含不可識別的文本”之類的錯誤信息說明。
第二章 數(shù)學和三角函數(shù)
1、SUM函數(shù)的參數(shù)不能超過30個，如果需要30個以上參數(shù)時，可以在引用的參數(shù)兩邊多加一對括號，這樣就突破了這個限制。如：計算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值，則也會算在內，可以使用下面的公式實現(xiàn)求平均數(shù)時，只對不等于0的值求平均數(shù)：
用數(shù)組公式： {=AVERAGE(IF(A1:A10<>0,A1:A10))}
提示：計算時會先得到一個含有數(shù)值和邏輯值的數(shù)組，再對這個數(shù)組求平均，由于AVERAGE函數(shù)會忽略邏輯值，所以就只對不等于0的數(shù)值求平均數(shù)。
3、INT（）是向下取整函數(shù)。即向數(shù)軸向左的方向取整。
例如：=INT(9.9) 結果是 9
=INT(-9.9) 結果是 -10
注意INT（）函數(shù)和TRUNC（）函數(shù)的區(qū)別。
4、TRUNC（數(shù)值或單元格引用，指定取整精度）函數(shù)是取整函數(shù)，且是真正的取整函數(shù)，即截取數(shù)字的整數(shù)部分，正數(shù)、負數(shù)同樣對待。
如：=TRUNC(8.4) 結果是8
=TRUNC(-8.4) 結果是-8 ,而如果是=INT(-8.4) 則結果就是-9
注意：
（1）TRUNC（）函數(shù)和INT（）函數(shù)的區(qū)別。
（2）取整精度默認為0，也可以指定，如： =TRUNC（4.867,2） 結果是4.86
=TRUNC(-9.2389,3) 結果是-9.238
5、CEILING（要四舍五入的數(shù)值，是需要四舍五入的乘數(shù)）函數(shù)
用法：此函數(shù)是將第一個參數(shù)向上舍入（沿絕對值增大的方向）為最接近的第二個參數(shù)的倍數(shù)。
注意：第一個參數(shù)和第二個參數(shù)的正負號必須統(tǒng)一；無論數(shù)字符號如何，都按遠離 0 的方向向上舍入;最終結果肯定是第2個參數(shù)的整數(shù)倍。
例1：=CEILING(0.234, 0.01) 結果是將0.234向上舍入到最接近的0.01的24倍，即0.01*24等于0.24,0.234向上舍入到0.24
例2：=CEILING(5.7,4) 結果是將5.7舍入到4的2倍，即4*2等于8，5.7向上舍入到8。而不能是4的1倍，因為4*1等于4，而4小于5.7
例3：=CEILING(4.42,0.1) 結果是將4.42舍入到0.1的45倍，即0.1*45等于4.5，4,42向上舍入到4.5
例4：=CEILING(1.5, 0.1) 結果是1.5，因為1.5已經(jīng)是0.1的15倍了，所以保持不變。
6、COMBIN（對象的總數(shù)量，為每一組合中對象的數(shù)量）
用法：求數(shù)學當中的組合數(shù)。
注意：第2個參數(shù)應當小于等于第1個參數(shù)；兩個參數(shù)都必須大于等于0。
例1：求從8個對象中取2個對象進行的組合數(shù)
=COMBIN(8,2) 結果是28
例2：求從4個對象中取3個對象的組合數(shù)
=COMBIN(4,3) 結果是4
7、刪除單元格中文本中的空格符，可以用=SUBSTITUTE（Text,” ”,””）函數(shù)，但是文本中含有ASCII碼為160的空格符,公式要變?yōu)?/span>: =SUBSTITUTE(SUBSTITUTE(Text,” ”,””),CHAR(160),””)
8、刪除空白行的一種方法。
選中要操作的區(qū)域，執(zhí)行“編輯”/“定位”/“定位條件”/選“空值”，“確定”后即可將選中區(qū)域中的空白單元格選中，再執(zhí)行“編輯”/“刪除”/“整行”即可。
注意：此操作要確保其他非空行中的所有單元格內均有數(shù)據(jù)，否則會出現(xiàn)誤刪除記錄的現(xiàn)象。
9、INDIRECT（引用的文本，a1）函數(shù)
注意：
（1）如果引用的文本是對另一個工作簿的引用，則該工作簿必須被打開，否則函數(shù)返回#REF！
（2）a1參數(shù)是一個邏輯值，表示引用類型是A1引用樣式還是R1C1引用樣式，為TRUE或省略時表示A1引用樣式
10、EXP(number)函數(shù)計算e的number次冪。其中e為2.71828182845904
例1：exp(1) 結果是2.71828182845904，表示e的1次冪
例2：exp(2) 結果是 7.389056099，表示e的2次冪
第三章 統(tǒng)計函數(shù)
11、MAX（）函數(shù)和MIN（）函數(shù)的參數(shù)最多為30個。
12、計算指定區(qū)域的最大值。
比如數(shù)據(jù)在A1：A10，計算此區(qū)域中的最大值
方法一： =MAX(a1:a10)
方法二： =SMALL(A1:A10,COUNTA(A1:A10))
注意：MAX函數(shù)的參數(shù)引用如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數(shù)引用不能忽略邏輯值、文本，則要用MAXA()函數(shù)。
13、RAND（）函數(shù)返回0到1之間的隨即數(shù)，每次工作表計算都返回一個新的值。
要生成a與b之間的隨機實數(shù)，可以用公式=RAND（）*（b-a）+a
14、ROUNDUP（數(shù)值，四舍五入后的數(shù)字的位數(shù)）函數(shù)將指定數(shù)值返回為向上舍入的數(shù)值。
例1：=roundup(4.982,1) 結果為5.0
例2：=roundup(3.14159,3) 結果為3.142
例3：=ROUNDUP(-3.14159, 1) 結果為-3.2
注意：這里的向上舍入指遠離0值。
15、FREQUENCY（）函數(shù)
語法：
FREQUENCY(數(shù)據(jù)源,分段點)
結果：
以分段點為間隔，統(tǒng)計數(shù)據(jù)源值在各段出現(xiàn)的頻數(shù)
其中：
數(shù)據(jù)源：為對一行/一列單元格或一個連續(xù)的單元格區(qū)域的引用；也可以是對一個單元格引用。
分段點：為對一行/一列單元格或一個連續(xù)的單元格區(qū)域的引用；也可以是對一個單元格引用。
數(shù)據(jù)引用支持跨工作表、工作簿
公式輸入方法：
以多單元格數(shù)組方式輸入，且必須是縱向數(shù)組；
所選單元格數(shù)比分段點個數(shù)大1，以統(tǒng)計數(shù)據(jù)源大于分段點最大值的頻數(shù)
16、RANK（）函數(shù)
RANK（）函數(shù)對重復數(shù)的排位是相同的，如果兩個相同的數(shù)值出現(xiàn)時，它們的排名是相同的，比如都是第5位，而 不會是第5位和第6位，這里的第6位將被忽略，而直接跳到第7位。
17、利用SMALL（區(qū)域，COUNT(區(qū)域)）函數(shù)可以統(tǒng)計區(qū)域中的最大值。
注意：SMALL（）函數(shù)忽略被統(tǒng)計區(qū)域中的空白單元格、邏輯值、文本。
18、FORECAST（）函數(shù)是根據(jù)已有的數(shù)值來計算或預測未來值。
19、TRIMMEAN（數(shù)組或引用，要去除的數(shù)據(jù)點比例）函數(shù)
常規(guī)做法是：
=(SUM(A1:A12)-MAX(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12)-2)
而利用TRIMMEAN函數(shù)就方便多了，因為一個最高和一個最低是2個數(shù)，占總個數(shù)12的百分比是2/12，即1/6，所以公式可以寫成：
=TRIMMEAN(A1:A12,1/6)
結果和上面的公式相同。
(2)如果要去掉兩個最好分和兩個最低分，對剩下數(shù)求平均值，則可以直接用公式：
=TRIMMEAN(A1:A12,4/12)
20、DCOUNT（）函數(shù)
返回數(shù)據(jù)庫或數(shù)據(jù)清單的列中滿足指定條件并且包含數(shù)字的單元格個數(shù)。
21、DMAX（數(shù)據(jù)列表或單元格區(qū)域，要統(tǒng)計的列名稱或列序號，條件）
例如：
上海員工原工資總數(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) 結果是6
注意：MODE參數(shù)中的數(shù)組或引用中的文本、空白單元格、邏輯值將被忽略，但含有零值的單元格將被計算在內，解決的方法如下：
例如：要統(tǒng)計A1：A12中出現(xiàn)頻率最高的數(shù)值，但零值不計算在內：
用數(shù)組公式 {=MODE(IF(A1:A12=0,FALSE,A1:A12))} 即利用了MODE函數(shù)忽略邏輯值的原理。
3、求幾何平均數(shù)GEOMEAN（）函數(shù)
幾何平均數(shù)的計算公式如下：
提示：可以用公式 =product(區(qū)域)^(1/count(區(qū)域)) 代替GEOMEAN（）函數(shù)。
第四章 日期與時間函數(shù)
1、求兩個日期之間的天數(shù)差。
假設在A1填入2006-12-1,在A2填入2006-12-31，則公式：Datedif(a1,a2,”d”)即可。
當然最簡單的方法是直接用公式： =a2-a1即可。
2、DATE（年，月，日）函數(shù)
參數(shù)中的年可以為1至4位數(shù)值默認情況下EXCEL使用1900日期系統(tǒng)：
（1）如果 year 位于 0（零）到 1899（含）之間，則 Excel 會將該值加上 1900，再計算年份。例如，DATE(100,1,2) 將返回 2000 (1900+100) 年 1 月 2 日。
（2）如果 year 位于 1900 到 9999（含）之間，則 Excel 將使用該數(shù)值作為年份。例如，DATE(2000,1,2) 將返回 2000 年 1 月 2 日。
（3）如果 year 小于 0 或大于等于 10000，則 Excel 將返回錯誤值 #NUM!。
第五章 文本和數(shù)據(jù)函數(shù)
1、CELL（信息類型，引用）
函數(shù)返回某一個引用區(qū)域的左上角的單元格格式、位置或內容等信息。
如果“引用”忽略，則返回最后更改的單元格所對應的信息。
如：=mid(CELL(“filename”),find（”[“, CELL(“filename”)）+1，255）
返回最后修改的單元格所在的工作表，而如果想返回當前單元格所在工作表應該用：
=MID(CELL("filename",A1),FIND(")",CELL("filename",A1))+1,255)
2、CELL（）、MID（）、LEFT（）、RIGHT（）等函數(shù)
=CELL("filename") '獲取當前工作簿的路徑、文件名、工作簿名稱
=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"))) ’獲取當前工作表名稱
3、DOLLAR（）函數(shù)和RMB（）函數(shù)
DOLLAR（）函數(shù)可以在數(shù)值前添加美元標識＄
RMB（）函數(shù)可以在數(shù)值前添加人民幣標識￥
注意：使用“格式”菜單中的“單元格”命令來設置包含數(shù)字的單元格的格式與使用 DOLLAR 函數(shù)直接設置數(shù)字的格式之間的區(qū)別在于：DOLLAR 函數(shù)將結果轉換為文本，而使用“單元格”命令設置格式的數(shù)字仍為數(shù)字。但可以繼續(xù)在公式中使用由 DOLLAR 函數(shù)設置了格式的數(shù)字，因為 Microsoft Excel 在計算公式時會將以文本值輸入的數(shù)字轉換為數(shù)字。
4、PHONETIC（）函數(shù)
要在EXCEL中的漢字添加拼音，按以下步驟操作：
選中要設置拼音的單元格區(qū)域，執(zhí)行“格式”/“拼音信息”/“顯示或隱藏”，使輸入拼音的區(qū)域顯示出來，再執(zhí)行“格式”/“拼音信息”/“編輯”，就可以在單元格的上半部分輸入相應的拼音了。
但一般不建議這樣做，因為無法輸入帶聲調的拼音，可以先在WORD中輸入相應漢字，選中漢字并執(zhí)行“格式”/“中文版式”/“拼音指南”，打開“拼音指南”對話框，單擊“組合”按鈕，將拼音字符組合在一起，選中拼音字符串并按CTRL+C，將拼音字符串復制下來，切換到EXCEL中，將帶聲調的拼音字符串粘貼到EXCEL中要編輯拼音的上半部空白處即可。如果要對拼音做調整，可以依次單擊“格式”/“拼音信息”/“設置”。
如果要將單元格中的拼音字符串提取出來，可以用PHONETIC（）函數(shù)，假設含有漢語拼音的字符串在A1單元格，則在C1輸入公式=PHONETIC（A1）即可。
提示：如果用PHONETIC（）函數(shù)提取出來的拼音信息含有漢字，解決的方法是手工清除源單元格中漢字之間的空格即可。
如果PHONETIC（）函數(shù)的參數(shù)為單元格區(qū)域，則返回單元格區(qū)域左上角單元格中的拼音字符串。
5、如何清除單元格中用ALT+回車鍵進行的回車換行符？
可以按以下步驟操作：
編輯/替換，在“查找內容”框中鍵入 ALT鍵的同時，從小鍵盤輸入10這兩個數(shù)值，輸入完畢后，查找框中不會出現(xiàn)什么內容，然后直接單擊“全部替換”按鈕，即可將所有通過ALT+回車組合產生的換行符刪除了。
6、MID（文本，開始位置，字符數(shù)）函數(shù)
用途：返回文本字符串從指定位置開始的特定數(shù)目的字符。
注意：
（1）如果 開始位置 大于文本長度，則 MID 返回空文本 ("")。
（2）如果 開始位置 小于文本長度，但 開始位置 加上 字符數(shù) 超過了文本的長度，則 MID 只返回最多到文本末尾的字符。
（3）如果 開始位置 小于 1，則 MID 返回錯誤值 #VALUE!。
（4）如果 字符數(shù) 是負數(shù)，則 MID 返回錯誤值 #VALUE!。
7、PROPER（文本）函數(shù)
用途：將文本字符串的首字母或任何非字母字符之后的首字母轉換成大寫，而將其余字母轉換成小寫。
8、ASC（）函數(shù)
用途：將全角（雙字節(jié)）字符變?yōu)榘虢牵▎巫止?jié)）字符。
9、WIDECHAR（）函數(shù)
用途：將字符串中的半角（單字節(jié)）字符變?yōu)槿牵p字節(jié)）字符。
第七章 邏輯函數(shù)
1、公歷紀年中如何判斷某年份是否為閏年。
公歷紀年中閏年的判斷方法是：如果年份能被400整除或者年份能被4整除但卻不能被100整除，則此年份為閏年，否則此年份為平年。
根據(jù)此判斷方法，在EXCEL中可以編公式判斷某年份是否為閏年：
假設在A1單元格存放年份，則在B1單元格寫公式：
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"閏年","平年")
也可以將此功能做成自定義函數(shù)，方便以后調用：
Function isLeapYear(year As Integer) As Boolean '判斷某年份是否為閏年
isLeapYear = (year Mod 400 = 0) Or ((year Mod 4 = 0) And (year Mod 100 <> 0))
End Function
調用時，假設年份在A1，在B1單元格輸入 =isleapyear(A1) 返回結果為TRUE表示是閏年，返回為FALSE表示不是閏年
2、中國式排名的方法
使用RANK對區(qū)域排名時，如果出現(xiàn)相同的數(shù)據(jù)時，排名是按1、1、3、4、5、5、7…這樣的形式，請問，如何才能實現(xiàn)依然按1、1、2、3、4、4、5…這樣的順序排名？
比如：
B列為銷售數(shù)量，C列是用RANK（）函數(shù)做的排名，現(xiàn)在想實現(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公式向下拖動復制到D9即可。
第八章 查找和引用函數(shù)
1、如何禁止在單元格輸入數(shù)據(jù)時出現(xiàn)空格？
可以通過數(shù)據(jù)有效性來實現(xiàn)。比如以在A列輸入數(shù)值并禁止輸入空格為例，具體方法是：
選中A列，執(zhí)行“數(shù)據(jù)”/“有效性”/在“設置”選項卡的“允許”下拉框中選擇“自定義”，在下面的公式框中輸入公式 =ISERROR(FIND(" ",A1)) ，然后可以在“出錯警告”選項卡里輸入出現(xiàn)錯誤時彈出的自定義提示信息。
2、ISNONTEXT（value）函數(shù)
如果value的值不是文本，則此函數(shù)返回TRUE，如果value值引用的單元格為空白單元格時，函數(shù)返回TRUE
3、根據(jù)指定的日期，判斷其所屬月份的上旬、中旬、下旬。
如圖示例：根據(jù)C2單元格的日期判斷是上旬、中旬、下旬。可以用LOOKUP函數(shù)和DAY（）函數(shù)，在D2單元格輸入公式：=LOOKUP(DAY(C2),{1,11,21},{"上旬","中旬","下旬"})
比如C4單元格的2005-12-19，DAY（C4）結果為19，然后在{1,11,21}里查找19，因為沒找到19，所以查找小于等于19的值，結果找到11，最后返回11對應的數(shù)組{"上旬","中旬","下旬"}中的第2個值“中旬”。
4、MAX（）函數(shù)的參數(shù)如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數(shù)引用不能忽略邏輯值、文本，則要用MAXA()函數(shù)。
例如：
統(tǒng)計A1：A12中最后出現(xiàn)數(shù)值為65的單元格所在行號,結果是11
{=MAX(IF(A1:A12=65,ROW(A1:A12),""))}
公式中間過程IF(A1:A12=65,ROW(A1:A12),"")的結果是"",2, "","","",6, "","","","",11, ""
然后對這個數(shù)組求最大值，因為MAX（）忽略空白字符串，所以2、6、11中最大值是11
5、在單元格區(qū)域生成指定目錄下的文件列表。
利用EXCEL中的宏表函數(shù)FILES（）可以生成指定目錄下的文件列表，結果是一個水平文本數(shù)組。
例如：想在A列填充“D:\mp3音樂\書香音樂\河月”文件夾下的文件名列表，可以執(zhí)行“插入”/“名稱”/“定義”，在名稱框中填寫FILELISTS，在引用位置填=FILES("D:\mp3音樂\書香音樂\河月\*.*") ，然后再添加一個名稱ML，引用位置填寫=TRANSPOSE(FILELISTS)
現(xiàn)在關閉定義名稱對話框，選中A列，輸入ML ，然后按CTRL+SHIFT+回車結束輸入。
這樣會在A列顯示出指定目錄下的文件名列表，A列出現(xiàn)的#N/A表示的是選中的單元格個數(shù)大于文件名列表的個數(shù)。
6、MOD（被除數(shù)，除數(shù)）函數(shù)
可以返回兩數(shù)相除的余數(shù)，結果的正負號與除數(shù)相同。
MOD（）函數(shù)可以用INT函數(shù)取代： mod(n,d)=n-d*int(n/d)
7、 ROW（引用）函數(shù)
返回引用的行號，如果省略“引用”，則對ROW（）所在單元格的引用，如果“引用”是一個單元格區(qū)域，則結果是一個垂直
第九章 工程和數(shù)據(jù)庫函數(shù)
1、單位換算函數(shù)CONVERT（引用單元格，原單位，新單位）
例如：單元格A1的數(shù)值單位是“磅”，現(xiàn)在想轉換成單位“公斤”，則公式：
=CONVERT(A1,"lbm","kg")
例如：單元格A2是“英尺”數(shù)據(jù)，單元格B2是“英寸”數(shù)據(jù)，現(xiàn)在想將A2和B2數(shù)據(jù)同時轉換成“米”，并求和，則C2填公式：
=CONVERT(A2,"ft","m")+CONVERT(B2,"in","m")
提示：如果結果為#NAME，則要先加載分析工具庫，步驟：工具/加載宏/“分析工具庫”打勾。
2、用DSUM（）函數(shù)實現(xiàn)多條件求和
例如：下圖是一份原料采購清單，現(xiàn)在想對采購時間在2005-12-10日以前（含2005-12-10日），并且商品名稱為顯示器的采購金額進行求和，首先要在E2：F3單元格建立條件區(qū)域，接著在G3單元格輸入公式：
=DSUM(A2:D20,"金額",E2:F3) 其中A2：D20是整個數(shù)據(jù)區(qū)域，“金額”是要計算的列字段標題，必須要與數(shù)據(jù)區(qū)域的列字段標題保持一致，E2：F3是條件區(qū)域，條件區(qū)域也要有字段標題。
颶 風
2007年1月8日
聯(lián)系客服
