EXCEL函數實例活用100談》讀書筆記

第一章 函數基礎知識

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.238

5、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) 結果是4

7、刪除單元格中文本中的空格符，可以用=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）+a

14、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 函數設置了格式的數字，因為 Microsoft Excel 在計算公式時會將以文本值輸入的數字轉換為數字。

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)),"閏年","平年")

也可以將此功能做成自定義函數，方便以後調用：

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對區域排名時，如果出現相同的數據時，排名是按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值引用的單元格為空白單元格時，函數返回TRUE

3、根據指定的日期，判斷其所屬月份的上旬、中旬、下旬。

如圖示例：根據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中最大值是11

5、在單元格區域生成指定目錄下的文件列表。

利用EXCEL中的宏表函數FILES（）可以生成指定目錄下的文件列表，結果是一個水平文本數組。

例如：想在A列填充「D:mp3音樂書香音樂河月」文件夾下的文件名列表，可以執行「插入」/「名稱」/「定義」，在名稱框中填寫FILELISTS，在引用位置填=FILES("D:mp3音樂書香音樂河月*.*") ，然後再添加一個名稱ML，引用位置填寫=TRANSPOSE(FILELISTS)

現在關閉定義名稱對話框，選中A列，輸入ML ，然後按CTRL+SHIFT+回車結束輸入。

這樣會在A列顯示出指定目錄下的文件名列表，A列出現的#N/A表示的是選中的單元格個數大於文件名列表的個數。

6、MOD（被除數，除數）函數

可以返回兩數相除的餘數，結果的正負號與除數相同。

MOD（）函數可以用INT函數取代： mod(n,d)=n-d*int(n/d)

7、 ROW（引用）函數

返回引用的行號，如果省略「引用」，則對ROW（）所在單元格的引用，如果「引用」是一個單元格區域，則結果是一個垂直

第九章 工程和資料庫函數

1、單位換算函數CONVERT（引用單元格，原單位，新單位）

例如：單元格A1的數值單位是「磅」，現在想轉換成單位「公斤」，則公式：

=CONVERT(A1,"lbm","kg")

例如：單元格A2是「英尺」數據，單元格B2是「英寸」數據，現在想將A2和B2數據同時轉換成「米」，並求和，則C2填公式：

=CONVERT(A2,"ft","m")+CONVERT(B2,"in","m")

提示：如果結果為#NAME，則要先載入分析工具庫，步驟：工具/載入宏/「分析工具庫」打勾。

2、用DSUM（）函數實現多條件求和

例如：下圖是一份原料採購清單，現在想對採購時間在2005-12-10日以前（含2005-12-10日），並且商品名稱為顯示器的採購金額進行求和，首先要在E2：F3單元格建立條件區域，接著在G3單元格輸入公式：

=DSUM(A2:D20,"金額",E2:F3) 其中A2：D20是整個數據區域，「金額」是要計算的列欄位標題，必須要與數據區域的列欄位標題保持一致，E2：F3是條件區域，條件區域也要有欄位標題。

颶 風

2007年1月8日