EXCEL函数及公式ppt课件.ppt

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