excel-公式與函數(shù)的應(yīng)用

excel-公式與函數(shù)的應(yīng)用第一頁，共26頁。一、教學(xué)重點：

公式的應(yīng)用二、教學(xué)難點：

公式的編輯三、教學(xué)方法：

任務(wù)驅(qū)動法第一頁第二頁，共26頁。公式的規(guī)則所有的Excel公式都具有相同的基本結(jié)構(gòu)：1、以等號（=）開頭，后面跟隨一個或多個運算碼。2、運算碼可以是值、單元格引用、單元格區(qū)域、或者函數(shù)名，其間以一個或多個運算符連接。3、運算符包括算術(shù)運算符、邏輯運算符或者字符連接符（&）等。第二頁第三頁，共26頁。公式的規(guī)則1、Excel在一個公式中允許使用的最大字符數(shù)為1024個。2、公式中運算符與運算碼之間可以加入空格，以便分隔公式中的各個部分，從而提高公式的可讀性。第三頁第四頁，共26頁。公式的編輯1．公式的輸入（1）選中要輸入公式的單元格；（2）輸入“=”作為準(zhǔn)備輸入公式的開始；（3）輸入組成該公式的所有運算碼和運算符；（4）按回車鍵對輸入的公式表示確認(rèn)。第四頁第五頁，共26頁。公式的編輯2．公式的修改（1）按F2鍵，可以直接編輯修改單元格中的內(nèi)容；（2）雙擊該單元格，可以直接編輯修改單元格中的內(nèi)容；（3）選擇需要進(jìn)行編輯的單元格，然后單擊編輯欄，在編輯欄中對公式進(jìn)行修改。當(dāng)編輯結(jié)束后按【Enter】鍵即可完成操作。如果要取消編輯操作，可以按Esc鍵。第五頁第六頁，共26頁。公式返回的錯誤信息返回值錯誤提示原因#NAME?使用了EXCEL不能識別的名字#N/A使用了不能使用的值#NULL!指定了無效的“空”區(qū)域#NUM！公式中使用了無效數(shù)字值#REF!引用了無效單元格#VALUE!不正確的參數(shù)或運算對象######列寬不夠＃DIV/0!除數(shù)為0第六頁第七頁，共26頁。3．公式的移動和復(fù)制公式移動和復(fù)制的操作方法與單元格移動和復(fù)制類似。選擇性粘貼編輯——清除應(yīng)用公式的注意事項公式中所有的運算符都必須在英文半角狀態(tài)下輸入公式中的字符串常量需要以英文雙引號（“”）括起來第七頁第八頁，共26頁。一、在電子表格中應(yīng)用公式算術(shù)運算符+－*/^(乘方）%（百分比）比較運算符＝><>=<=<>比較運算的結(jié)果為TRUE或FALSE文本連接運算符:&假如B2單元格內(nèi)容：信息時代，D6單元格內(nèi)容：ABC。則=B2&D6的值是信息時代ABC=“21世紀(jì)是”&B2的值是21世紀(jì)是信息時代

第八頁第九頁，共26頁。一、在電子表格中應(yīng)用公式運算符的優(yōu)先級()>算術(shù)運算>連接運算>比較運算高低-（取負(fù)）>%

>^>*/

>

+-例如：-5%*6/5+10第九頁第十頁，共26頁。二、相對引用、絕對引用、混合引用對單元格地址的三種引用方式絕對引用相對引用混合引用相對引用：直接使用單元格或單元格區(qū)域的名稱作為引用名稱。＝A5+D6=F4+F8絕對引用：在單元格名稱的列標(biāo)與行號前加符號“$”表示絕對引用。＝$A$4+$B$5混合引用：相對引用與絕對引用相結(jié)合=F$3+$D4說明：1、當(dāng)公式使用相對引用時，把公式復(fù)制到一個新的位置，公式中的單元格引用也跟隨變化，但相對于公式所在單元格的位置不變。2、在公式中使用絕對引用，不論公式復(fù)制到何處，公式中所引用的單元格不發(fā)生變化。第十頁第十一頁，共26頁。二、相對引用、絕對引用、混合引用跨表格數(shù)據(jù)的引用1.引用同一工作薄其他工作表中的單元格或區(qū)域工作表標(biāo)簽名!單元格地址2..引用同一工作薄多張工作表的相同單元格或區(qū)域第一個工作表名：最后一個工作表名!單元格地址3.不同工作薄之間的數(shù)據(jù)引用

[工作簿名]工作表標(biāo)簽名!單元格地址第十一頁第十二頁，共26頁。三、電子表格中的常用函數(shù)1、求和函數(shù)：SUM（number1，number2……number30）2、求平均數(shù)函數(shù)：

AVERAGE（number1，number2……number30）3、求最大值和最小值：MAX（number1，number2……number30）MIN（number1，number2……number30）調(diào)用方法:“插入”——“函數(shù)”編輯欄上的fx按鈕工具欄上的自動填充按鈕第十二頁第十三頁，共26頁。利用公式或函數(shù)完成以下任務(wù)任務(wù)1：計算銷售數(shù)據(jù)完成“北京路分店”、山東路分店、香港路分店的上半年、下半年、全年、總計的計算。完成占銷售總額比例的計算。任務(wù)2：完成“教學(xué)計劃”表中黃色區(qū)域的計算。任務(wù)3：計算“人事表”中職稱工資。職稱工資=基本工資*10%第十三頁第十四頁，共26頁。任務(wù)4：計算“第二單元人事表”中的工齡、校齡4、取整函數(shù)INT（）：將數(shù)字向下舍入到最接近的整數(shù)例：＝INT（8.9)結(jié)果為8例：＝INT（－8.9）結(jié)果為－998.98-8-8.9-9將數(shù)字向下舍入到最接近的整數(shù)0第十四頁第十五頁，共26頁。三、電子表格中的常用函數(shù)5、返回當(dāng)前系統(tǒng)日期：TODAY（）＝TODAY()結(jié)果為=INT(TODAY()-人事表!E4)/365)第十五頁第十六頁，共26頁。6、條件函數(shù)IF(測試條件，滿足條件時的輸出結(jié)果，不滿足條件時的輸出結(jié)果）例1：”學(xué)生成績表”，條件如下：大于等于300分顯示及格，小于300分顯示不及格＝IF(成績表!G2>=300,及格,不及格）第十六頁第十七頁，共26頁。三、電子表格中的常用函數(shù)（續(xù)）例2：計算業(yè)務(wù)津貼：博導(dǎo)350元，其余人為0。=IF(人事表!I4=“博導(dǎo)”,350,0)例3：根據(jù)學(xué)生成績劃分等級。=IF(C2>=90,“優(yōu)秀”,

IF(C2>=80,“良好”,

IF(C2>=70,“中等”,

IF(C2>=60,“及格”,“不及格”))))第十七頁第十八頁，共26頁。任務(wù)5：用IF函數(shù)計算“人事表”中業(yè)務(wù)津貼：博導(dǎo)350元，其余人為0

任務(wù)6：用IF函數(shù)輸入“人事表”中基本工資。教授1500，副教授1200，講師1000，助教800.

任務(wù)7：用IF函數(shù)計算“人事表”中個人所得稅。當(dāng)應(yīng)發(fā)工資合計在800（含800）元以下時，不交稅；當(dāng)應(yīng)發(fā)工資在800以上，1300以下，超過800部分按5%計稅。當(dāng)應(yīng)發(fā)工資在1300以上，超過1300部分按10%計稅，800-1300之間的部分按5%計稅。如計算秦小寧公式如下：

=IF(K3<800,0,IF(K3<=1300,(K3-800)*5%,(K3-1300)*10%+500*5%)）第十八頁第十九頁，共26頁。三、電子表格中的常用函數(shù)7、計數(shù)函數(shù)Count（）：格式：Count（指定區(qū)域）：計算個數(shù)，只能計算數(shù)值型數(shù)據(jù)，不能計算文本、空單元格。例如：計算“學(xué)生成績表”中學(xué)生的人數(shù)。公式：=COUNT(G3:G15)區(qū)域可以選擇有數(shù)值的任意列第十九頁第二十頁，共26頁。三、電子表格中的常用函數(shù)8、COUNTIF函數(shù)：計算制定區(qū)域中滿足給定條件的單元格數(shù)目。注意：該函數(shù)要求第二個參數(shù)為文本字符串。格式：COUNTIF(指定區(qū)域,條件)例1：計算“人事表”中低于平均工資的人數(shù) =COUNTIF(K3:K26,“<”&K28)例2：計算“學(xué)生成績表”成績達(dá)到優(yōu)秀以上的人數(shù) =COUNTIF(C2:C10,“>90")例3：計算“學(xué)生成績表”中男生人數(shù)和女生人數(shù)=COUNTIF(B3:B15,"男")=COUNTIF(B3:B15,“女")第二十頁第二十一頁，共26頁。排位函數(shù)Rank函數(shù)格式：=RANK(指定的單元格,排序的范圍,0或非0)如，=RANK(A1,$A$1:$A$5,0)第二十一頁第二十二頁，共26頁。四、工作表的安全與保護(hù)1、鎖定與隱藏鎖定：工作表中選定的單元格區(qū)域不能被更改、移動、調(diào)整或刪除；隱藏：工作表中選定的單元格區(qū)域中的公式（包括函數(shù)）不會在編輯欄中顯示。注意：“鎖定”與“隱藏”操作只有在實施工作表保護(hù)后才有效，且必須先設(shè)置單元格格式中的保護(hù)功能，順序不能顛倒。第二十二頁第二十三頁，共26頁。四、工作表的安全與保護(hù)2、工作表保護(hù)操作過程選取所有單元格或需要保護(hù)的單元格區(qū)域，然后進(jìn)行如下設(shè)置：格式－>單元格－>保護(hù)－>鎖定、隱藏選項工具－>保護(hù)－>保護(hù)