第10頁共10頁第一章函數基礎知識1、按shift+F3是插入彈出“插入函數”對話框的快捷鍵。2、當在單元格中鍵入“=”號時，在“名稱框”中會顯示出常用的函數列表，可以選擇所需要的。3、在單元格中輸入公式時，當鍵入函數名稱時，或者鍵入函數名稱和左括號時，可按ctrl+shift+A組合鍵顯示函數的參數說明。4、如果在單元格中輸入的公式返回錯誤的信息，想了解這個錯誤信息的含義，如“#NAME”，可以選中此單元格，鼠標光標移動到緊挨此單元格左側的智能標記圖標上，就會出現“公式中包含不可識別的文本”之類的錯誤信息說明。第二章數學和三角函數1、SUM函數的參數不能超過30個，如果需要30個以上參數時，可以在引用的參數兩邊多加一對括號，這樣就突破了這個限制。如：計算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函數是求平均值的函數，如果參數引用中包含0值，則也會算在內，可以使用下面的公式實現求平均數時，只對不等于0的值求平均數：用數組公式：{=AVERAGE(IF(A1:A10<>0,A1:A10))}提示：計算時會先得到一個含有數值和邏輯值的數組，再對這個數組求平均，由于AVERAGE函數會忽略邏輯值，所以就只對不等于0的數值求平均數。3、INT（）是向下取整函數。即向數軸向左的方向取整。例如：=INT(9.9)結果是9 =INT(-9.9)結果是-10注意INT（）函數和TRUNC（）函數的區別。4、TRUNC（數值或單元格引用，指定取整精度）函數是取整函數，且是真正的取整函數，即截取數字的整數部分，正數、負數同樣對待。如：=TRUNC(8.4)結果是8 =TRUNC(-8.4)結果是-8,而如果是=INT(-8.4)則結果就是-9注意：（1）TRUNC（）函數和INT（）函數的區別。（2）取整精度默認為0，也可以指定，如：=TRUNC（4.867,2）結果是4.86=TRUNC(-9.2389,3)結果是-9.2385、CEILING（要四舍五入的數值，是需要四舍五入的乘數）函數用法：此函數是將第一個參數向上舍入（沿絕對值增大的方向）為最接近的第二個參數的倍數。注意：第一個參數和第二個參數的正負號必須統一；無論數字符號如何，都按遠離0的方向向上舍入;最終結果肯定是第2個參數的整數倍。例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已經是0.1的15倍了，所以保持不變。6、COMBIN（對象的總數量，為每一組合中對象的數量）用法：求數學當中的組合數。注意：第2個參數應當小于等于第1個參數；兩個參數都必須大于等于0。例1：求從8個對象中取2個對象進行的組合數=COMBIN(8,2)結果是28例2：求從4個對象中取3個對象的組合數=COMBIN(4,3)結果是47、刪除單元格中文本中的空格符，可以用=SUBSTITUTE（Text,””,””）函數，但是文本中含有ASCII碼為160的空格符,公式要變為:=SUBSTITUTE(SUBSTITUTE(Text,””,””),CHAR(160),””)8、刪除空白行的一種方法。選中要操作的區域，執行“編輯”/“定位”/“定位條件”/選“空值”，“確定”后即可將選中區域中的空白單元格選中，再執行“編輯”/“刪除”/“整行”即可。注意：此操作要確保其他非空行中的所有單元格內均有數據，否則會出現誤刪除記錄的現象。9、INDIRECT（引用的文本，a1）函數注意：（1）如果引用的文本是對另一個工作簿的引用，則該工作簿必須被打開，否則函數返回#REF！（2）a1參數是一個邏輯值，表示引用類型是A1引用樣式還是R1C1引用樣式，為TRUE或省略時表示A1引用樣式10、EXP(number)函數計算e的number次冪。其中e為2.71828182845904例1：exp(1)結果是2.71828182845904，表示e的1次冪例2：exp(2)結果是7.389056099，表示e的2次冪第三章統計函數11、MAX（）函數和MIN（）函數的參數最多為30個。12、計算指定區域的最大值。比如數據在A1：A10，計算此區域中的最大值方法一：=MAX(a1:a10)方法二：=SMALL(A1:A10,COUNTA(A1:A10))注意：MAX函數的參數引用如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數引用不能忽略邏輯值、文本，則要用MAXA()函數。13、RAND（）函數返回0到1之間的隨即數，每次工作表計算都返回一個新的值。要生成a與b之間的隨機實數，可以用公式=RAND（）*（b-a）+a14、ROUNDUP（數值，四舍五入后的數字的位數）函數將指定數值返回為向上舍入的數值。例1：=roundup(4.982,1)結果為5.0例2：=roundup(3.14159,3)結果為3.142例3：=ROUNDUP(-3.14159,1)結果為-3.2注意：這里的向上舍入指遠離0值。15、FREQUENCY（）函數語法：FREQUENCY(數據源,分段點)結果：以分段點為間隔，統計數據源值在各段出現的頻數其中：數據源：為對一行/一列單元格或一個連續的單元格區域的引用；也可以是對一個單元格引用。分段點：為對一行/一列單元格或一個連續的單元格區域的引用；也可以是對一個單元格引用。數據引用支持跨工作表、工作簿公式輸入方法：以多單元格數組方式輸入，且必須是縱向數組；所選單元格數比分段點個數大1，以統計數據源大于分段點最大值的頻數16、RANK（）函數RANK（）函數對重復數的排位是相同的，如果兩個相同的數值出現時，它們的排名是相同的，比如都是第5位，而不會是第5位和第6位，這里的第6位將被忽略，而直接跳到第7位。17、利用SMALL（區域，COUNT(區域)）函數可以統計區域中的最大值。注意：SMALL（）函數忽略被統計區域中的空白單元格、邏輯值、文本。18、FORECAST（）函數是根據已有的數值來計算或預測未來值。19、TRIMMEAN（數組或引用，要去除的數據點比例）函數例如：左邊的示例，（1）求A1：A12中去掉一個最高分、去掉一個最低分，然后求平均值：常規做法是：=(SUM(A1:A12)-MAX(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12)-2)而利用TRIMMEAN函數就方便多了，因為一個最高和一個最低是2個數，占總個數12的百分比是2/12，即1/6，所以公式可以寫成：=TRIMMEAN(A1:A12,1/6)結果和上面的公式相同。(2)如果要去掉兩個最好分和兩個最低分，對剩下數求平均值，則可以直接用公式：=TRIMMEAN(A1:A12,4/12)

20、DCOUNT（）函數返回數據庫或數據清單的列中滿足指定條件并且包含數字的單元格個數。21、DMAX（數據列表或單元格區域，要統計的列名稱或列序號，條件）例如：上海員工原工資總數是：=SUMIF(E3:E18,"上海",G3:G18)上海員工原工資最高的是：=DMAX(B2:H18,"原工資",E20:E21)也可以用數組公式：{=MAX((E3:E18="上海")*(G3:G18))}上海員工原工資最低的是：=DMIN(B2:H18,G2,E20:E21) 也可以用數組公式：{=MIN(IF(((E3:E18="上海")*(G3:G18))=0,FALSE,(E3:E18="上海")*(G3:G18)))}提示：加這句IF(((E3:E18="上海")*(G3:G18))=0,FALSE是利用MIN（）函數忽略邏輯值的的原理。22、求眾數函數MODE（）眾數即出現頻率最高的數值。如下圖示例：求A1：A12中出現頻率最高數值可以用公式=mode(a1:a12)結果是6注意：MODE參數中的數組或引用中的文本、空白單元格、邏輯值將被忽略，但含有零值的單元格將被計算在內，解決的方法如下：例如：要統計A1：A12中出現頻率最高的數值，但零值不計算在內：用數組公式{=MODE(IF(A1:A12=0,FALSE,A1:A12))}即利用了MODE函數忽略邏輯值的原理。3、求幾何平均數GEOMEAN（）函數幾何平均數的計算公式如下：提示：可以用公式=product(區域)^(1/count(區域))代替GEOMEAN（）函數。第四章日期與時間函數1、求兩個日期之間的天數差。假設在A1填入2006-12-1,在A2填入2006-12-31，則公式：Datedif(a1,a2,”d”)即可。當然最簡單的方法是直接用公式：=a2-a1即可。2、DATE（年，月，日）函數參數中的年可以為1至4位數值默認情況下EXCEL使用1900日期系統：（1）如果year位于0（零）到1899（含）之間，則Excel會將該值加上1900，再計算年份。例如，DATE(100,1,2)將返回2000(1900+100)年1月2日。（2）如果year位于1900到9999（含）之間，則Excel將使用該數值作為年份。例如，DATE(2000,1,2)將返回2000年1月2日。（3）如果year小于0或大于等于10000，則Excel將返回錯誤值#NUM!。第五章文本和數據函數1、CELL（信息類型，引用）函數返回某一個引用區域的左上角的單元格格式、位置或內容等信息。如果“引用”忽略，則返回最后更改的單元格所對應的信息。如：=mid(CELL(“filename”),find（”[“,CELL(“filename”)）+1，255）返回最后修改的單元格所在的工作表，而如果想返回當前單元格所在工作表應該用：=MID(CELL("filename",A1),FIND(")",CELL("filename",A1))+1,255)2、CELL（）、MID（）、LEFT（）、RIGHT（）等函數=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（）函數和RMB（）函數DOLLAR（）函數可以在數值前添加美元標識＄RMB（）函數可以在數值前添加人民幣標識￥注意：使用“格式”菜單中的“單元格”命令來設置包含數字的單元格的格式與使用DOLLAR函數直接設置數字的格式之間的區別在于：DOLLAR函數將結果轉換為文本，而使用“單元格”命令設置格式的數字仍為數字。但可以繼續在公式中使用由DOLLAR函數設置了格式的數字，因為MicrosoftExcel在計算公式時會將以文本值輸入的數字轉換為數字。4、PHONETIC（）函數要在EXCEL中的漢字添加拼音，按以下步驟操作：選中要設置拼音的單元格區域，執行“格式”/“拼音信息”/“顯示或隱藏”，使輸入拼音的區域顯示出來，再執行“格式”/“拼音信息”/“編輯”，就可以在單元格的上半部分輸入相應的拼音了。 但一般不建議這樣做，因為無法輸入帶聲調的拼音，可以先在WORD中輸入相應漢字，選中漢字并執行“格式”/“中文版式”/“拼音指南”，打開“拼音指南”對話框，單擊“組合”按鈕，將拼音字符組合在一起，選中拼音字符串并按CTRL+C，將拼音字符串復制下來，切換到EXCEL中，將帶聲調的拼音字符串粘貼到EXCEL中要編輯拼音的上半部空白處即可。如果要對拼音做調整，可以依次單擊“格式”/“拼音信息”/“設置”。 如果要將單元格中的拼音字符串提取出來，可以用PHONETIC（）函數，假設含有漢語拼音的字符串在A1單元格，則在C1輸入公式=PHONETIC（A1）即可。提示：如果用PHONETIC（）函數提取出來的拼音信息含有漢字，解決的方法是手工清除源單元格中漢字之間的空格即可。如果PHONETIC（）函數的參數為單元格區域，則返回單元格區域左上角單元格中的拼音字符串。5、如何清除單元格中用ALT+回車鍵進行的回車換行符？可以按以下步驟操作：編輯/替換，在“查找內容”框中鍵入ALT鍵的同時，從小鍵盤輸入10這兩個數值，輸入完畢后，查找框中不會出現什么內容，然后直接單擊“全部替換”按鈕，即可將所有通過ALT+回車組合產生的換行符刪除了。6、MID（文本，開始位置，字符數）函數用途：返回文本字符串從指定位置開始的特定數目的字符。注意：（1）如果開始位置大于文本長度，則MID返回空文本("")。（2）如果開始位置小于文本長度，但開始位置加上字符數超過了文本的長度，則MID只返回最多到文本末尾的字符。（3）如果開始位置小于1，則MID返回錯誤值#VALUE!。（4）如果字符數是負數，則MID返回錯誤值#VALUE!。7、PROPER（文本）函數用途：將文本字符串的首字母或任何非字母字符之后的首字母轉換成大寫，而將其余字母轉換成小寫。8、ASC（）函數用途：將全角（雙字節）字符變為半角（單字節）字符。9、WIDECHAR（）函數用途：將字符串中的半角（單字節）字符變為全角（雙字節）字符。第七章邏輯函數1、公歷紀年中如何判斷某年份是否為閏年。公歷紀年中閏年的判斷方法是：如果年份能被400整除或者年份能被4整除但卻不能被100整除，則此年份為閏年，否則此年份為平年。根據此判斷方法，在EXCEL中可以編公式判斷某年份是否為閏年：假設在A1單元格存放年份，則在B1單元格寫公式：=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"閏年","平年")也可以將此功能做成自定義函數，方便以后調用：FunctionisLeapYear(yearAsInteger)AsBoolean'判斷某年份是否為閏年isLeapYear=(yearMod400=0)Or((yearMod4=0)And(yearMod100<>0))EndFunction調用時，假設年份在A1，在B1單元格輸入=isleapyear(A1)返回結果為TRUE表示是閏年，返回為FALSE表示不是閏年2、中國式排名的方法使用RANK對區域排名時，如果出現相同的數據時，排名是按1、1、3、4、5、5、7…這樣的形式，請問，如何才能實現依然按1、1、2、3、4、4、5…這樣的順序排名？比如：B列為銷售數量，C列是用RANK（）函數做的排名，現在想實現D列式的排名，可以在D2單元格輸入數組公式：=SUM(IF(B$2:B$9>B2,1/COUNTIF(B$2:B$9,B$2:B$9),""))+1輸入完畢后按CTRL+SHIFT+回車表示作為數組公式輸入然后將D2公式向下拖動復制到D9即可。第八章查找和引用函數1、如何禁止在單元格輸入數據時出現空格？可以通過數據有效性來實現。比如以在A列輸入數值并禁止輸入空格為例，具體方法是：選中A列，執行“數據”/“有效性”/在“設置”選項卡的“允許”下拉框中選擇“自定義”，在下面的公式框中輸入公式=ISERROR(FIND("",A1))，然后可以在“出錯警告”選項卡里輸入出現錯誤時彈出的自定義提示信息。2、ISNONTEXT（value）函數如果value的值不是文本，則此函數返回TRUE，如果value值引用的單元格為空白單元格時，函數返回TRUE3、根據指定的日期，判斷其所屬月份的上旬、中旬、下旬。如圖示例：根據C2單元格的日期判斷是上旬、中旬、下旬。可以用LOOKUP函數和DAY（）函數，在D2單元格輸入公式：=LOOKUP(DAY(C2),{1,11,21},{"上旬","中旬","下旬"})比如C4單元格的2005-12-19，DAY（C4）結果為19，然后在{1,11,21}里查找19，因為沒找到19，所以查找小于等于19的值，結果找到11，最后返回11對應的數組{"上旬","中旬","下旬"}中的第2個值“中旬”。4、MAX（）函數的參數如果是邏輯值、文本、空白單元格，則將被忽略。如果要求參數引用不能忽略邏輯值、文本，則要用MAXA()函數。例如：統計A1：A12中最后出現數值為65的單元格所在行號,結果是11{=MAX(IF(A1:A12=65,ROW(A1:A12),""))}公式中間過程IF(A1:A12=65,ROW(A1:A12),"")的結果是"",2,"","","",6,"","","","",11,""然后對這個數組求最大值，因為MAX（）忽略空白字符串，所以2、6、11中最大值是115、在單元格區域生成指定目錄下的文件列表。利用EXCEL中的宏表函數FILES（）可以生成指定目錄下的文件列表，結果是一個水平文本數組。例如：想在A列填充“D:\mp3音樂\書香音樂\河月”文件夾下的文件名列表，可以執行“插入”/“名稱”/“定義”，在名稱框中填寫FILELISTS，在引用位置填=FILES("D:\mp3音樂\書香音樂\河月\*.*")，然后再添加一個名稱ML，引用位置填寫=TRANSPOSE(FILELISTS)現在關閉定義名稱對話框，選中A列，輸入ML，然后按CTRL+SHIFT+回車結束輸入。這樣會在A列顯示