《excel函數(shù)實(shí)例活用100談》讀書筆記

第10頁(yè)共10頁(yè)第一章函數(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ù)說(shuō)明。4、如果在單元格中輸入的公式返回錯(cuò)誤的信息，想了解這個(gè)錯(cuò)誤信息的含義，如“#NAME”，可以選中此單元格，鼠標(biāo)光標(biāo)移動(dòng)到緊挨此單元格左側(cè)的智能標(biāo)記圖標(biāo)上，就會(huì)出現(xiàn)“公式中包含不可識(shí)別的文本”之類的錯(cuò)誤信息說(shuō)明。第二章數(shù)學(xué)和三角函數(shù)1、SUM函數(shù)的參數(shù)不能超過(guò)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注意：（1）TRUNC（）函數(shù)和INT（）函數(shù)的區(qū)別。（2）取整精度默認(rèn)為0，也可以指定，如：=TRUNC（4.867,2）結(jié)果是4.86=TRUNC(-9.2389,3)結(jié)果是-9.2385、CEILING（要四舍五入的數(shù)值，是需要四舍五入的乘數(shù)）函數(shù)用法：此函數(shù)是將第一個(gè)參數(shù)向上舍入（沿絕對(duì)值增大的方向）為最接近的第二個(gè)參數(shù)的倍數(shù)。注意：第一個(gè)參數(shù)和第二個(gè)參數(shù)的正負(fù)號(hào)必須統(tǒng)一；無(wú)論數(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é)果是47、刪除單元格中文本中的空格符，可以用=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ù)注意：（1）如果引用的文本是對(duì)另一個(gè)工作簿的引用，則該工作簿必須被打開，否則函數(shù)返回#REF！（2）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次冪第三章統(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）+a14、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ù)語(yǔ)法：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ù)值來(lái)計(jì)算或預(yù)測(cè)未來(lái)值。19、TRIMMEAN（數(shù)組或引用，要去除的數(shù)據(jù)點(diǎn)比例）函數(shù)例如：左邊的示例，（1）求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é)果和上面的公式相同。(2)如果要去掉兩個(gè)最好分和兩個(gè)最低分，對(duì)剩下數(shù)求平均值，則可以直接用公式：=TRIMMEAN(A1:A12,4/12)

20、DCOUNT（）函數(shù)返回?cái)?shù)據(jù)庫(kù)或數(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ù)忽略邏輯值的原理。3、求幾何平均數(shù)GEOMEAN（）函數(shù)幾何平均數(shù)的計(jì)算公式如下：提示：可以用公式=product(區(qū)域)^(1/count(區(qū)域))代替GEOMEAN（）函數(shù)。第四章日期與時(shí)間函數(shù)1、求兩個(gè)日期之間的天數(shù)差。假設(shè)在A1填入2006-12-1,在A2填入2006-12-31，則公式：Datedif(a1,a2,”d”)即可。當(dāng)然最簡(jiǎn)單的方法是直接用公式：=a2-a1即可。2、DATE（年，月，日）函數(shù)參數(shù)中的年可以為1至4位數(shù)值默認(rèn)情況下EXCEL使用1900日期系統(tǒng)：（1）如果year位于0（零）到1899（含）之間，則Excel會(huì)將該值加上1900，再計(jì)算年份。例如，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將返回錯(cuò)誤值#NUM!。第五章文本和數(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í)￥注意：使用“格式”菜單中的“單元格”命令來(lái)設(shè)置包含數(shù)字的單元格的格式與使用DOLLAR函數(shù)直接設(shè)置數(shù)字的格式之間的區(qū)別在于：DOLLAR函數(shù)將結(jié)果轉(zhuǎn)換為文本，而使用“單元格”命令設(shè)置格式的數(shù)字仍為數(shù)字。但可以繼續(xù)在公式中使用由DOLLAR函數(shù)設(shè)置了格式的數(shù)字，因?yàn)镸icrosoftExcel在計(jì)算公式時(shí)會(huì)將以文本值輸入的數(shù)字轉(zhuǎn)換為數(shù)字。4、PHONETIC（）函數(shù)要在EXCEL中的漢字添加拼音，按以下步驟操作：選中要設(shè)置拼音的單元格區(qū)域，執(zhí)行“格式”/“拼音信息”/“顯示或隱藏”，使輸入拼音的區(qū)域顯示出來(lái)，再執(zhí)行“格式”/“拼音信息”/“編輯”，就可以在單元格的上半部分輸入相應(yīng)的拼音了。 但一般不建議這樣做，因?yàn)闊o(wú)法輸入帶聲調(diào)的拼音，可以先在WORD中輸入相應(yīng)漢字，選中漢字并執(zhí)行“格式”/“中文版式”/“拼音指南”，打開“拼音指南”對(duì)話框，單擊“組合”按鈕，將拼音字符組合在一起，選中拼音字符串并按CTRL+C，將拼音字符串復(fù)制下來(lái)，切換到EXCEL中，將帶聲調(diào)的拼音字符串粘貼到EXCEL中要編輯拼音的上半部空白處即可。如果要對(duì)拼音做調(diào)整，可以依次單擊“格式”/“拼音信息”/“設(shè)置”。 如果要將單元格中的拼音字符串提取出來(lái)，可以用PHONETIC（）函數(shù)，假設(shè)含有漢語(yǔ)拼音的字符串在A1單元格，則在C1輸入公式=PHONETIC（A1）即可。提示：如果用PHONETIC（）函數(shù)提取出來(lái)的拼音信息含有漢字，解決的方法是手工清除源單元格中漢字之間的空格即可。如果PHONETIC（）函數(shù)的參數(shù)為單元格區(qū)域，則返回單元格區(qū)域左上角單元格中的拼音字符串。5、如何清除單元格中用ALT+回車鍵進(jìn)行的回車換行符？可以按以下步驟操作：編輯/替換，在“查找內(nèi)容”框中鍵入ALT鍵的同時(shí)，從小鍵盤輸入10這兩個(gè)數(shù)值，輸入完畢后，查找框中不會(huì)出現(xiàn)什么內(nèi)容，然后直接單擊“全部替換”按鈕，即可將所有通過(guò)ALT+回車組合產(chǎn)生的換行符刪除了。6、MID（文本，開始位置，字符數(shù)）函數(shù)用途：返回文本字符串從指定位置開始的特定數(shù)目的字符。注意：（1）如果開始位置大于文本長(zhǎng)度，則MID返回空文本("")。（2）如果開始位置小于文本長(zhǎng)度，但開始位置加上字符數(shù)超過(guò)了文本的長(zhǎng)度，則MID只返回最多到文本末尾的字符。（3）如果開始位置小于1，則MID返回錯(cuò)誤值#VALUE!。（4）如果字符數(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é)）字符。第七章邏輯函數(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)用：FunctionisLeapYear(yearAsInteger)AsBoolean'判斷某年份是否為閏年isLeapYear=(yearMod400=0)Or((yearMod4=0)And(yearMod100<>0))EndFunction調(diào)用時(shí)，假設(shè)年份在A1，在B1單元格輸入=isleapyear(A1)返回結(jié)果為TRUE表示是閏年，返回為FALSE表示不是閏年2、中國(guó)式排名的方法使用RANK對(duì)區(qū)域排名時(shí)，如果出現(xiàn)相同的數(shù)據(jù)時(shí)，排名是按1、1、3、4、5、5、7…這樣的形式，請(qǐng)問(wèn)，如何才能實(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即可。第八章查找和引用函數(shù)1、如何禁止在單元格輸入數(shù)據(jù)時(shí)出現(xiàn)空格？可以通過(guò)數(shù)據(jù)有效性來(lái)實(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ù)返回TRUE3、根據(jù)指定的日期，判斷其所屬月份的上旬、中旬、下旬。如圖示例：根據(jù)C2單元格的日期判斷是上旬、中旬、下旬。可以用LOOKUP函數(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ù)的參數(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),""))}公式中間過(guò)程IF(A1:A12=65,ROW(A1:A12),"")的結(jié)果是"",2,"","","",6,"","","","",11,""然后對(duì)這個(gè)數(shù)組求最大值，因?yàn)镸AX（）忽略空白字符串，所以2、6、11中最大值是115、在單元格區(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列顯示