第十三講 EXCEL中的公式與函數(shù)

第十三

講EXCEL中的公式和函數(shù)教學(xué)目標(biāo):掌握如何在工作表中創(chuàng)建公式,以及怎樣在工作表中使用公式與函數(shù)進(jìn)行數(shù)據(jù)計算.重點(diǎn):

公式、函數(shù)的使用主要知識點(diǎn)：一、運(yùn)算符 （掌握）二、公式創(chuàng)建 （掌握）三、函數(shù)應(yīng)用 （掌握）四、單元格引用 （掌握）五、單元格命名和名稱應(yīng)用 （掌握）六、財務(wù)函數(shù)（Pmt、Ipmt、Pv、Fv、Nper）（了解）七：公式審核 （了解）一、運(yùn)算符1、算術(shù)運(yùn)算符：+、-、*、/、%、^（乘方）2、比較運(yùn)算符：=、>、<、>=、<=、<>3、文本運(yùn)算符：&（又稱連接運(yùn)算符,用于將兩個文本值連接到一起）4、引用運(yùn)算符：連續(xù)區(qū)域（：）例A1：B10不連續(xù)區(qū)域（，）例A1：A10，C1：C10交叉區(qū)域（空格）例B1:B10A1:B3（空格隔開）2、運(yùn)算順序：：空格，-（負(fù)號）%^*/+-&=、<、>、>=、<=、<>優(yōu)先級從高到低二、在單元格中建立、編輯公式：1、創(chuàng)建公式：輸入“=（20+30）/2”（直接輸入數(shù)據(jù)）輸入“=B3+C3+D3”（用地址表示其中的數(shù)據(jù)）輸入“=sum（B3:D3）”（用函數(shù)計算）按“Enter”鍵之后，便可得出計算結(jié)果2、公式的復(fù)制：選中區(qū)域，輸入公式后，按ctrl+Enter，則所有被選中的單元格自動被公式填充；輸入公式，拉動填充句柄；三、函數(shù)應(yīng)用基礎(chǔ)

格式：=函數(shù)名（參數(shù)，……）1、輸入函數(shù)：輸入=函數(shù)名（參數(shù)）單擊“fx”按鈕執(zhí)行“插入→函數(shù)”命令單擊常用工具欄上“自動求和”按鈕2、參數(shù)：表達(dá)式、單元格地址或名稱3、常用函數(shù)：（1）求和：=SUM(number1,number2,……）注：Number參數(shù)最多為30個，此參數(shù)可是數(shù)字，公式及單元格區(qū)域；若參數(shù)中包含空白單元格、邏輯值、文本或錯誤值將被忽略；（2）求平均值：=Average(number1,number2,……）注：區(qū)域中有空格則忽略，而0值則參與計算（3）求最大、最小值：=Max(number1,number2,……）=min(number1,number2,……）注：若參數(shù)中不包含數(shù)字，則返回值為0（4）鏈接函數(shù)：=Hyperlink（link_location,friendly_name)創(chuàng)建鏈接，用以打開存儲在磁盤、網(wǎng)絡(luò)服務(wù)器、internet或intranet中的文件；例：Hyperlink（“”,“你們好”）注：可直接創(chuàng)建鏈接在單元格上右擊——超鏈接插入——超鏈接（ctrl+k)——找到鏈接的文件——確定（5）IF

函數(shù)（難點(diǎn)）If(logical_test,value_if_true,value_if_false)執(zhí)行真假判斷IF嵌套IF（條件1，評價1，IF（條件2，評價2，IF（……）））IF函數(shù)函數(shù)名稱：IF

主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。

使用格式：=IF(Logical,Value_if_true,Value_if_false)

參數(shù)說明：Logical代表邏輯判斷表達(dá)式；Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如果忽略返回“FALSE”。

應(yīng)用舉例：在C29單元格中輸入公式：=IF(C26>=18,“符合要求”,“不符合要求”)，確定以后，如果C26單元格中的數(shù)值大于或等于18，則C29單元格顯示“符合要求”字樣，反之顯示“不符合要求”字樣。6）min（參數(shù)1,參數(shù)2,參數(shù)3,……） 求最小值（對數(shù)字進(jìn)行取最小值運(yùn)算）7）power（x,y）表示X的Y次方8）count（參數(shù)1,參數(shù)2,參數(shù)3,……） 統(tǒng)計函數(shù)（對數(shù)字個數(shù)進(jìn)行統(tǒng)計運(yùn)算）9）sumif（條件區(qū)，條件，求和區(qū)） 條件求和函數(shù)（根據(jù)指定條件對若干單元格求和）當(dāng)“求和區(qū)=條件區(qū)”時，求和區(qū)可省略等價于“條件求和向?qū)А?，?zhí)行“工具→向?qū)А鷹l件求和”命令10）countif（區(qū)域，條件） 條件統(tǒng)計函數(shù)（計算區(qū)域中滿足給定條件的 單元格的個數(shù)）11）lookup（條件，條件區(qū)，結(jié)果區(qū)） 查詢函數(shù)（查找滿足某種條件的值）“條件列”數(shù)據(jù)必須要按升序排序4、數(shù)組計算與數(shù)據(jù)計算的差別就在于確認(rèn)方式不同，按組合鍵“Ctrl+Alt+Enter”來確認(rèn)四、單元格引用

（引用：指引用某一地址指向的單元格的值）

類型：相對A1、絕對$A$1、混合$A1、A$1轉(zhuǎn)換：F4

1、相對引用：指用單元格所在的列標(biāo)和行號為其引用例：“=SUM（A1：A4）”注：公式復(fù)制后，相對引用地址公自動隨著位置的移動而相應(yīng)變化；2、絕對引用：公式移動或復(fù)制后，單元格引用不會變化例：“=$A$1+$A$2”3、混合引用：例：$A2,A$24、單元格名稱引用：選取區(qū)域——在編輯欄左框中輸入名稱，使用時可輸入“=名稱1+名稱2”等Eg：乘法表：=列中的列絕對*行中的行絕對五、單元格命名及名稱應(yīng)用單元格命名：是給選定的單元格或單元格區(qū)域起一個名字命名規(guī)則：單元格名稱中的字符只能包括字母、數(shù)字、下劃線或小數(shù)點(diǎn)單元格名稱要以字母開頭單元格名稱中不能有空格單元格名稱不能使用單元格地址單元格名稱不區(qū)分字母的大小寫單元格名稱的最大字符長度為255個字符1、命名方法1：

1）選中單元格或單元格區(qū)域

2）在名稱框中輸入“名稱”

3）按“Enter”鍵確定方法2：

1）選中單元格或單元格區(qū)域

2）執(zhí)行“插入→名稱→定義”命令

3）輸入名稱，單擊“確定”按鈕“定義名稱”對話框如下圖所示。2、指定名稱以“行標(biāo)”或“列標(biāo)”來作為該行或該列的名稱方法：

1）選中單元格區(qū)域（包含行標(biāo)題和列標(biāo)題）

2）執(zhí)行“插入→名稱→指定”命令

3）選方式

4）單擊“確定”

“指定名稱”對話框如右圖所示。3、名稱應(yīng)用

1）應(yīng)用于計算中，相當(dāng)于絕對引用，不會隨著結(jié)果單元格變化而變化

2）用于選定單元格區(qū)域，只要在“名稱框”選中該名稱，按“Enter”鍵，即可選中該名稱的區(qū)域。六、財務(wù)函數(shù)1、Pmt（Rate，Nper，Pv，fv，type）計算基于固定利率及等額分期付款方式，返回貸款每期付款額。

Type取值是0：年末或１：年初2、Ipmt（Rate，per，Nper，Pv，F(xiàn)v，Type）

Per其取值為１～nper之間計算基于固定利率及等額分期付款方式下，返回給定期間內(nèi)對投資的利息償還額。3、Nper（rate，pmt，pv，fv，type）計算基于固定利率及等額分期付款方式下，

返回某項(xiàng)投資的總期數(shù)，即應(yīng)當(dāng)付多少期才能還

清貸款的本金和利息。4、Fv（rate，nper，pmt，pv，type）用于計算資金在固定利率及等額分期付款方式下將來某個時期的價值。

5、Pv（rate，nper，pmt，fv，type）可以計算資金當(dāng)前的價值，是與未來值相對的，用ＰＶ函數(shù)可將未來的資金折算成現(xiàn)值，未來資金支付的形式，可以是一次，也可以是等額支付。6、財務(wù)函數(shù)應(yīng)用實(shí)例七、公式審核錯誤問題：公式所有的括號必須成對出現(xiàn)在引用單元格區(qū)域時，第一個單元格與最后一個單元格之間用冒號隔開在應(yīng)用工作薄中其他單元格區(qū)域時一定包含工作表名稱和路徑，如“[0804學(xué)生成績表.xls]sheet1!$e$4”向公式中輸入時，不能為數(shù)字設(shè)置特殊格式，如輸入$25,000,也只能在公式中輸入25000如果引用的工作表或工作簿的名字包含非字母字符，必須用單引號將這個字符括起來函數(shù)中輸入的參數(shù)必須符合要求

錯誤檢查移去引用單元格追蹤箭頭移去從屬單元格追蹤箭頭新批注圈釋無效數(shù)據(jù)清除無效數(shù)據(jù)標(biāo)識圈錯誤檢查移去引用單元格追蹤箭頭移去從屬單元格追蹤箭頭新批注圈釋無效數(shù)據(jù)清除無效數(shù)據(jù)標(biāo)識圈審核方法

1）選中要審核的單元格（區(qū)域）

2）單擊“公式審核”工具欄上的相應(yīng)按鈕或執(zhí)行“工具→公式審核→追蹤引用單元格”等相應(yīng)命令。圈無效數(shù)據(jù)，必須是設(shè)置過有效性的數(shù)據(jù)區(qū)域。1、追蹤引用單元格引用的單元格：指一個單元格中的數(shù)據(jù)是使用了其他單元格中的數(shù)據(jù)通過公式計算出來的。執(zhí)行“工具—公式審核—追蹤引用單元格”2、追蹤從屬單元格