Excel函数和公式讲座(肖贵喜)

EXCEL函數及公式華南檢測中心肖貴喜2002.12.2內容:儲存格運算元函數公式應用宏及VBA簡介儲存格在公式中的表示:儲存格在公式中的表示:公式中會使用相對參照,向左.上為負公式中的計算運算元運算子負責指定公式元素的計算類型。MicrosoftExcel有四種不同類型的計算運算子：算術、比較、文字、以及參照算術運算子可以執行基本的數學運算，像是：加、減、乘、結合數字、以及產生數字結果。算術運算子意義範例+(加號)加法3+3-(減號)減法3-1負-1*(星號)乘3*3/(斜線)除法3/3%(百分比符號)百分比20%^(脫字符號)乘冪3^2(相當於3*3)比較運算子比較運算子可以比較二個值，產生邏輯值TRUE或FASLE。比較運算子意義範例=(等號)等於A1=B1(大於符號)大於A1B1(小於符號)小於A1B1=(大於或等於符號)大於或等於A1=B1=(小於或等於符號)小於或等於A1=B1(不等於符號)不等於A1B1文字運算子&參照運算子文字運算子&可以結合一個或多個文字值，產生一個文字。文字運算子意義範例&(與)連接或連結二個值，產生一個連續文字值North&wind產生Northwind參照運算子可以結合儲存格範圍加以計算。參照運算子意義範例:(冒號)範圍運算子，可以將一個參照位址擴大到二個參照位址之間(包含這二個參照位址)的所有儲存格B5:B15,(逗號)聯合運算子，可以將多個參照位址結合成一個參照位址SUM(B5:B15,D5:D15)(一個空格)交集運算子，可以將一個參照位址擴大到二個參照位址的共同儲存格SUM(B5:B15A7:D7)這個範列中的二個範圍內都有儲存格B7。函數:----數學和三角函數----IS函數----邏輯函數----時間函數----其他函數(工程.財務.統計.文字.資料庫.參照函數)(MID,FIND,COUNTIF,UPPER,RAND,ROUNDSMALL,INDEX....)數學及三角函數ABS取絕對值函數傳回數值的絕對值ACOS傳回數值的反餘弦值ACOSH傳回數值的反雙曲線餘弦值ASIN傳回數值的反正弦值ASINH傳回數值的反雙曲線正弦值ATAN傳回數值的反正切值ATAN2傳回x和y座標的反正切值ATANH傳回數值的反雙曲線正切值COMBIN傳回所給定物件個數的組合個數COS傳回數值的餘弦值邏輯函數AND---如果所有的引數都為TRUE，則傳回TRUEFALSE----傳回FALSE邏輯值IF----指定要執行的邏輯測試NOT---將引數的邏輯值反轉OR----如果任何一個引數為TRUE，則傳回TRUETRUE----傳回TRUE邏輯值日期和時間函數DATE傳回特定日期的序列值DATEVALUE將文字形式的日期轉換成序列值DAY將序列值轉換成月份中的日期。HOUR將序列值轉換成小時數MINUTE將序列值轉換成分鐘數MONTH將序列值轉換成月份數NETWORKDAYS傳回兩個日期之間的完整工作日天數NOW傳回目前日期和時間的序列值SECOND將序列值轉換成秒數TIME傳回特定時間的序列值TIMEVALUE將文字形式的時間轉換成序列值TODAY傳回今天日期的序列值WEEKDAY將序列值轉換成星期數WORKDAY傳回在指定的工作日數前或後的日期序列數YEAR將序列數轉換成年份數ISxx函數函數會傳回TRUE的情況ISBLANKValue指的是空白儲存格。ISERRValue指的是#N/A之外的任何一種錯誤值。ISERRORValue指的是任何一種錯誤值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!)。ISLOGICALValue指的是邏輯值。ISNAValue指的是錯誤值#N/A(無法使用的數值)。ISNONTEXTValue指的是任何非文字的項目。(請注意；如果數值參照到空白儲存格，則此函數也會傳回TRUE)。ISNUMBERValue指的是數字。ISREFValue指的是參照。ISTEXTValue指的是文字。開始寫公式公式開始一定為=,可直接寫,也可用工具條圖標插入語法:每個函數都有其固定的語法函數在公式中的運用應用范例:IF函數語法如果您指定的情況結果為TRUE，則傳回一個值，若結果為FALSE，則傳回另一個值。IF函數可用以測試數值和公式的條件IF函數語法IF(logical_test,value_if_true,value_if_false)=IF(A=B,”C“,”D”)=IF(A=B,”C”,IF(E=F,”K,”D”))=IF(A=B,IF(B=C,”E”,”F”),”D”)可嵌套使用注意有無引號之區別可有語法錯誤提示及自動糾正,也可對應括號顯示不同顏色可使用多達七層的IF函數,巢狀IF函數公式的復制與固定通過拉動可實現公式的復制公式中固定儲存格用固定符號$不同工作表數據的引用同一文件工作表名稱!儲存格不同文件例:=‘c:\windows\[fai.xls]sheet1’!c5錯誤值的顯示工作表公式有時會傳回錯誤值(如#DIV/0!,#N/A,#VALUE!,#REF!,及#NUM!等)是十分平常的事情。此些錯誤值的產生係由於事先未能預期的數值所導致。*****除數公式就是其中一個最佳範例。如果來源資料儲存格包含零值，那麼就會傳回#DIV/0!錯誤值。錯誤值的意義####在儲存格中輸入的數值太長，儲存格無法全部顯示#DIV/0!當公式的除數為0(零)時，會出現#DIV/0!錯誤值#N/A錯誤值出現時，表示函數或公式中有無效值當MicrosoftExcel無法識別公式中的文字時，#NAME?錯誤值出現當您使用的引數或運算元的類型錯誤，或公式自動校正特性無法更正公式時，#VALUE!錯誤值出現當儲存格參照無效時，#REF!錯誤值出現抑制錯誤值顯示使用函數來抑制錯誤值顯示的一般方法可表達如下：=IF(ISERROR(formula),,formula)其中formula係您所要抑制顯示錯誤值的公式。如果formula傳回錯誤值，則該表示式傳回(null字元)，否則傳回formula的結果值。其他抑制錯誤值顯示的方法係使用Excel'97「設定格式化條件」的功能。「設定格式化條件」允許您根據儲存格的內容顯示各種不同的格式。就錯誤值而言，您需要遵循下列步驟：1.選擇所要格式化的儲存格(範例中為$F$19)2.在功能表中選擇「格式」「設定格式化條件」3.在第一個下拉方塊中，選擇公式為4.在鄰近的編輯方塊中輸入：=ISERROR($F$19)5.按下格式鈕，並選擇一種格式，，如使用藍色背景及深藍色字型作為格式。6.按下確定鈕。經由上述步驟所產生的結果：若有錯誤值則該儲存格的格式為藍色背景及深藍色字型巨集(宏).VBA簡介:MicroVisualBasicForApplication如果您經常在MicrosoftExcel中執行某個任務，可以使用巨集來將該任務自動化。巨集是存放在VisualBasic模組中的一系列指令和函數，每次您需要執行此任務時都可以執行它。錄製巨集的方式就好像以錄音機來錄音一樣。錄好之後，您就可以執行巨集來重複或重播這些指令。在您錄製或撰寫巨集時，請設計好您要巨集執行的步驟和指令。如果您在錄製巨集時發生了錯誤，也可以錄製所做的修正動作。每次您錄製巨集時，巨集都會存放在貼附到活頁簿的新模組中。您可以使用VisualBasic編輯器來編輯巨集、將某個模組中的巨集複製到另一個模組內、在不同的活頁簿之間複製巨集、變更存放巨集的模組名稱或變更巨集的名稱。