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