第2章 EXCEL 高級應用

1、第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分1/101 第第2 2章章 Excel Excel 高級應用高級應用2.1 基本概念基本概念2.2 函數及其應用函數及其應用2.3 數組公式數組公式2.4 數據分析與管理數據分析與管理第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分2/101 2.1 基本基本 概念概念 2.1.1 公式公式 2.1.2 運算符運算符 2.1.3 單元格引用單元格引用 2.1.4 函數函數 第章第章 Excel Excel 高級應用高級應

2、用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分3/101 2.1.1公公式式公式是公式是Excel中對數據進行運算和判斷的表達式。輸入中對數據進行運算和判斷的表達式。輸入公式時，必須以等號（公式時，必須以等號（“=”）開頭，其語法表示為：）開頭，其語法表示為：表達式表達式其中表達式由運算數和運算符組成。運算數可以是常其中表達式由運算數和運算符組成。運算數可以是常量、單元格或區(qū)域引用、名稱或函數等。運算符包括算術量、單元格或區(qū)域引用、名稱或函數等。運算符包括算術運算符、比較運算符和文本運算符。運算符對公式中的元運算符、比較運算符和文本運算符。運算符對公式中的元素進行特定類型的運

3、算。如果在輸入表達式時需要加入函素進行特定類型的運算。如果在輸入表達式時需要加入函數，可以在編輯框左端的數，可以在編輯框左端的“函數函數”下拉列表框中選擇函數。下拉列表框中選擇函數。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分4/101 2.1.2運運算符算符1算術運算符算術運算符 算術運算符有：負號（算術運算符有：負號（“ ”）、百分數（）、百分數（“”）、乘冪）、乘冪（“”）、乘（）、乘（“*”）和除（）和除（“/”）、加（）、加（“+”）和減（）和減（“ ”）。）。其運算順序與數學中的相同。其運算順序與數學中的相同。 2

4、比較運算符比較運算符Excel中的比較運算符有：等于（中的比較運算符有：等于（“=”）、小于（）、小于（“”）、小于等于（）、小于等于（“=”）、不）、不等于（等于（“”）。）。3字符運算符字符運算符字符運算符只有一個，就是字符運算符只有一個，就是“&”。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分5/101 2.1.3單單元格引用元格引用單元格的引用分為相對引用、絕對引用和混合引用3種。1相對引用 相對引用的形式就是在公式中直接將單元格的地址寫出來，例如公式：“=C4+D4+E4+ F4”，就是一個相對引用 2絕對引

5、用 如果在復制公式時不希望公式中的單元格地址隨公式變化，那么可以使用絕對引用。絕對引用的方法是：在列標和行號前各加上一個美元符號（“$”），如C5單元格可以表示成$C$5， 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分6/101 2.1.3單單元格引用元格引用3混合引用混合引用 如果只對如果只對“列列”或只對或只對“行行”進行絕對引用，如進行絕對引用，如$A5或或B$3，則稱這種引用為混合引用。，則稱這種引用為混合引用。 4引用運算符引用運算符 使用引用運算符可以將單元格的數據區(qū)域合并進行使用引用運算符可以將單元格的數據區(qū)域合

6、并進行計算，引用運算符有：冒號（計算，引用運算符有：冒號（“：”）、逗號）、逗號（“，”）、空格和感嘆號（）、空格和感嘆號（“！”）。）。冒號（冒號（“：”）是區(qū)域運算符，對左右兩個引用之）是區(qū)域運算符，對左右兩個引用之間，包括兩個引用在內的矩形區(qū)域內所有單元格進行引間，包括兩個引用在內的矩形區(qū)域內所有單元格進行引用。用。 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分7/101 逗號（逗號（“，”）是聯合引用運算符，聯合引用）是聯合引用運算符，聯合引用是將多個引用區(qū)域合并為一個區(qū)域進行引用。是將多個引用區(qū)域合并為一個區(qū)域進行引

7、用?？崭袷墙徊嬉眠\算符，它取幾個引用區(qū)域相空格是交叉引用運算符，它取幾個引用區(qū)域相交的公共部分（又稱為交的公共部分（又稱為“交交”）。）。 感嘆號（感嘆號（“！”）是三維引用運算符，利用它）是三維引用運算符，利用它可以引用另一張工作表中的數據，其表示形式為：可以引用另一張工作表中的數據，其表示形式為：“工作表名工作表名!單元格引用區(qū)域單元格引用區(qū)域”。2.1.3單單元格引用元格引用第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分8/101 函數是函數是Excel中系統預定義的公式，如中系統預定義的公式，如SUM、AVERAGE等

8、。通常，函數通過引用參數接收數據，等。通常，函數通過引用參數接收數據，并返回計算結果。函數由函數名和參數構成。并返回計算結果。函數由函數名和參數構成。函數的格式為：函數名函數的格式為：函數名(參數參數,參數參數,) 其中函數名用英文字母表示，函數名后的括號其中函數名用英文字母表示，函數名后的括號是不可少的，參數在函數名后的括號內，參數可以是不可少的，參數在函數名后的括號內，參數可以是常量、單元格引用、公式或其他函數，參數的個是常量、單元格引用、公式或其他函數，參數的個數和類別由該函數的性質決定。數和類別由該函數的性質決定。 2.1.4 函數函數第章第章 Excel Excel 高級應用高級應用

9、 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分9/101 2.2 函數及其應用函數及其應用2.2.1 2.2.1 日期和時間函數日期和時間函數 2.2.2 2.2.2 數值計算函數數值計算函數 2.2.3 2.2.3 統計函數統計函數2.2.4 2.2.4 邏輯函數邏輯函數2.2.5 2.2.5 文本函數文本函數2.2.6 2.2.6 查找函數查找函數2.2.7 2.2.7 財務函數財務函數2.2.8 2.2.8 數據庫函數數據庫函數2.2.9 2.2.9 測試函數測試函數 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1

10、時32分10/101 2.2.1 日期和時間函數日期和時間函數 1求當前系統日期函數求當前系統日期函數TODAY 格式：格式：TODAY() 功能：返回當前的系統日期。功能：返回當前的系統日期。2求當前系統日期和時間函數求當前系統日期和時間函數NOW 格式：格式：NOW（）（） 功能：返回當前的系統日期和時間。功能：返回當前的系統日期和時間。3年函數年函數YEAR 格式：格式：YEAR(serial_number) 功能：返回指定日期所對應的四位的年份。返回功能：返回指定日期所對應的四位的年份。返回值為值為 1900 到到 9999 之間的整數。之間的整數。參數說明：參數說明：serial_n

11、umber為一個日期值，其中包含為一個日期值，其中包含要查找的年份。要查找的年份。 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分11/101 4小時函數小時函數HOUR 格式：格式：HOUR(serial_number) 功能：返回指定時間值中的小時數。即一個介于功能：返回指定時間值中的小時數。即一個介于 0 (12:00 AM) 到到 23 (11:00 PM) 之間的一個整數值。之間的一個整數值。參數說明：參數說明：serial_number表示一個時間值，其中包表示一個時間值，其中包含要查找的小時。含要查找的小時。 2.

12、2.1 日期和時間函數日期和時間函數 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分12/101 2.2.2 2.2.2 數值計算函數數值計算函數1求和函數求和函數SUM格式：格式：SUM(參數參數1,參數參數2,)功能：求參數所對應數值的和。參數可以是常數或單元格功能：求參數所對應數值的和。參數可以是常數或單元格引用。引用。2條件求和函數條件求和函數SUMIF格式：格式：SUMIF(range,criteria,sum_range)功能：根據指定條件對指定數值單元格求和。功能：根據指定條件對指定數值單元格求和。參數說明：參數說

13、明：range代表條件判斷的單元格區(qū)域；代表條件判斷的單元格區(qū)域；criteria為為指定條件表達式；指定條件表達式；sum_range代表需要求和的實際單元格區(qū)域。代表需要求和的實際單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分13/101 2.2.2 2.2.2 數值計算函數數值計算函數3取整函數取整函數INT 格式：格式：INT(number) 功能：求功能：求number的最大整數部分。的最大整數部分。 4四舍五入函數四舍五入函數ROUND 格式：格式：ROUND(number,num_digits) 功能：

14、對指定數據功能：對指定數據number，四舍五入保留，四舍五入保留num_digits位小數。位小數。5求余數函數求余數函數MOD 格式：格式：MOD(number,divisor) 功能：返回兩數相除的余數。結果的正負號與除功能：返回兩數相除的余數。結果的正負號與除數相同。數相同。參數說明：參數說明：number為被除數；為被除數；divisor為除數。為除數。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分14/101 1統計計數函數統計計數函數COUNT 格式：格式：COUNT(number1,number2,) 功能：統計

15、給定數據區(qū)域中所包含的數值型數據的功能：統計給定數據區(qū)域中所包含的數值型數據的單元格個數。單元格個數。 與與COUNT函數相類似的還有以下函數：函數相類似的還有以下函數：COUNTA(value1,value2, .)函數計算參數列表函數計算參數列表(value1,value2, .)中所包含的非空值的單元格個數。中所包含的非空值的單元格個數。COUNTBLANK(range)函數用于計算指定單元格區(qū)域函數用于計算指定單元格區(qū)域(range)中空白單元格的個數。中空白單元格的個數。MAX(number1,number2, .)函數用于求參數表中對應函數用于求參數表中對應數字的最大值。數字的最大

16、值。MIN(number1,number2, .)函數用于求參數表中對應函數用于求參數表中對應數字的最小值。數字的最小值。 2.2.3 2.2.3 統計函數統計函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分15/101 2條件統計函數條件統計函數COUNTIF 格式：格式：COUNTIF(range,criteria) 功能：在給定數據區(qū)域內統計滿足條件的單元格的功能：在給定數據區(qū)域內統計滿足條件的單元格的個數。個數。 其中：其中：range為需要統計的單元格數據區(qū)域，為需要統計的單元格數據區(qū)域，criteria為條件，其形

17、式可以為常數值、表達式或文本。為條件，其形式可以為常數值、表達式或文本。 3排位函數排位函數RANK 格式：格式：RANK(number,ref,order) 功能：返回一個數值在指定數據區(qū)域中的排位。功能：返回一個數值在指定數據區(qū)域中的排位。 參數說明：參數說明：number為需要排位的數字；為需要排位的數字；ref為數為數字列表數組或對數字列表的單元格引用；字列表數組或對數字列表的單元格引用；order為為一數字，指明排位的方式（一數字，指明排位的方式（0或省略，降序排位；或省略，降序排位；非非0，升序排位）。，升序排位）。 2.2.3 2.2.3 統計函數統計函數第章第章 Excel E

18、xcel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分16/101 1AND函數函數 格式：格式：AND(logical1,logical2, .) 功能：返回邏輯值。如果所有參數值均為邏輯功能：返回邏輯值。如果所有參數值均為邏輯“真真（TRUE）”，則返回邏輯，則返回邏輯“TRUE”，否則返回邏輯，否則返回邏輯“FALSE”。 參數說明：參數說明：logical1,logical2, .：表示待測試的條件值或表達：表示待測試的條件值或表達式，最多為式，最多為30個。個。 與與AND函數相類似的還有以下函數：函數相類似的還有以下函數：OR(logical1,l

19、ogical2, .) 函數返回邏輯值。僅當所有參數值均函數返回邏輯值。僅當所有參數值均為邏輯為邏輯“假（假（FALSE）”時，返回邏輯假值時，返回邏輯假值“FALSE”，否則，否則返回邏輯真值返回邏輯真值“TRUE”。NOT(logical) 函數對參數值求反。函數對參數值求反。2.2.4 2.2.4 邏輯函數邏輯函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分17/101 2.2.4 邏輯函數邏輯函數2. IF函數函數 格式：格式：IF(logical,value_if_true,value_if_false) 功能：根據

20、條件判斷的結果來決定返回相應的功能：根據條件判斷的結果來決定返回相應的輸出結果。輸出結果。 參數說明：參數說明：logical為要判斷的邏輯表達式；為要判斷的邏輯表達式；value_if_true表示當條件判斷為邏輯表示當條件判斷為邏輯“真真（TRUE）”時要輸出的內容，如果忽略返回時要輸出的內容，如果忽略返回“TRUE”；value_if_false表示當條件判斷為邏輯表示當條件判斷為邏輯“假（假（FALSE）”時要輸出的內容，如果忽略返時要輸出的內容，如果忽略返回回“FALSE”。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32

21、分18/101 1文本比較函數文本比較函數EXACT格式：格式：EXACT(text1,text2) 功能：比較字符串功能：比較字符串text1是否與字符串是否與字符串text2相同。如果兩相同。如果兩個字符串相同，則返回測試結果個字符串相同，則返回測試結果“TRUE”，反之則返回，反之則返回“FLASE”。 注意：測試時區(qū)分大小寫。注意：測試時區(qū)分大小寫。2文本查找函數文本查找函數SEARCH格式：格式：SEARCH(find_text,within_text,start_num) 功能：判斷字符串功能：判斷字符串find_text是否包含在字符串是否包含在字符串within_text中，若

22、包含，則返回該字符串在原字符串中的起始位置，反之，中，若包含，則返回該字符串在原字符串中的起始位置，反之，則返回錯誤信息則返回錯誤信息“#VALUE!”。參數說明：參數說明：within_text為原始字符串，為原始字符串，find_text為要查找為要查找的字符串，的字符串，start_num表示從第幾個字符開始查找，缺省時則從表示從第幾個字符開始查找，缺省時則從第第1個字符開始查找。個字符開始查找。2.2.5 文本函數文本函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分19/101 2.2.5 文本函數文本函數注意：注意：

23、（1）該函數不區(qū)分大小寫（即同一個字符的大）該函數不區(qū)分大小寫（即同一個字符的大寫與小寫相同）；寫與小寫相同）； （2）查找時可使用通配符）查找時可使用通配符“?”和和“*”,但必須在但必須在通配符前加上符號通配符前加上符號“”； （3）查找時若要區(qū)分大小寫，可用函數：）查找時若要區(qū)分大小寫，可用函數：=FIND(find_text,within_text,start_num)實現，其用實現，其用法與法與SEARCH相同。相同。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分20/101 3截取子字符串函數截取子字符串函數 （1）

24、左截函數）左截函數LEFT 格式：格式：LEFT(text,num_chars) 功能：將字符串功能：將字符串text從左邊第從左邊第1個字符開始，向右截取個字符開始，向右截取num_chars個字符。個字符。（2）右截函數）右截函數RIGHT(text,num_chars)格式：格式：RIGHT(text,num_chars) 功能：將字符串功能：將字符串text從右邊第從右邊第1個字符開始，向左截取個字符開始，向左截取num_chars個字符。個字符。（3）截取任意位置子字符串函數）截取任意位置子字符串函數MID格式：格式：MID(text,start_num,num_chars) 功能：

25、將字符串功能：將字符串text從第從第start_num個字符開始，向左截取個字符開始，向左截取num_chars個字符。個字符。參數說明：參數說明：text是原始字符串，是原始字符串，start_num為截取的位置，為截取的位置，num_chars為要截取的字符個數。為要截取的字符個數。 2.2.5 文本函數文本函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分21/101 4字符替換函數字符替換函數REPLACE格式：格式：REPLACE(old_text,start_num,num_chars,new_text) 功能：對

26、指定字符串，從指定位置開始，用新字符串來替換功能：對指定字符串，從指定位置開始，用新字符串來替換原有字符串中的若干個字符。原有字符串中的若干個字符。 參數說明：參數說明：old_text是原有字符串；是原有字符串；start_num是從原字符串是從原字符串中第幾個字符位置開始替換；中第幾個字符位置開始替換；num_chars是原字符串中從起始位置是原字符串中從起始位置開始需要替換的字符個數；開始需要替換的字符個數；new_text是要替換成的新字符串。是要替換成的新字符串。注意：注意：（1）當）當num_chars為為0時則表示從時則表示從start_num之后插入新字符之后插入新字符串串ne

27、w_text；（2）當）當new_text為空時，則表示從第為空時，則表示從第start_num個字符開始個字符開始,刪除刪除num_chars個字符。個字符。 2.2.5 文本函數文本函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分22/101 2.2.5 文本函數文本函數5數據格式轉換函數數據格式轉換函數TEXT格式：格式：TEXT(value,format_text)功能：將數值功能：將數值(value)轉換為按指定數字格式轉換為按指定數字格式(format_text)表示的文本。如表示的文本。如“TEXT(123.45

28、6,$0.00)”的值為的值為“$123.46”，“TEXT(1234,dbnum2)”的值為的值為“壹仟貳佰叁拾肆壹仟貳佰叁拾肆”。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分23/101 2.2.5 文本函數文本函數 參數說明：參數說明：Value 為數值、計算結果為數字值的公式，或為數值、計算結果為數字值的公式，或對包含數字值的單元格的引用。對包含數字值的單元格的引用。Format_text 為為“單元格格單元格格式式”對話框中對話框中“數字數字”選項卡上選項卡上“分類分類”框中的文本形式的數框中的文本形式的數字格式。通

29、過字格式。通過“格式格式”菜單調用菜單調用“單元格單元格”命令，然后在命令，然后在“數數字字”選項卡上設置單元格的格式，只會更改單元格的格式而不選項卡上設置單元格的格式，只會更改單元格的格式而不會影響其中的數值。使用函數會影響其中的數值。使用函數 TEXT 可以將數值轉換為帶格可以將數值轉換為帶格式的文本，而其結果將不再作為數字參與計算。式的文本，而其結果將不再作為數字參與計算。UPPER(text)函數用于將文本函數用于將文本(text)轉換成大寫形式。轉換成大寫形式。LOWER(text)函數用于將文本函數用于將文本(text)轉換成小寫形式。轉換成小寫形式。 第章第章 Excel Exc

30、el 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分24/101 1 1 列匹配填充函數列匹配填充函數VLOOKUP VLOOKUP 格式：格式：VLOOKUP(lookup_value,table_array,col_index_num,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) range_lookup) 功能：在數據表的首列查找與指定的數值相匹配的功能：在數據表的首列查找與指定的數值相匹配的值，并將指定列的匹配值填入當前數據表的當前列中。值，并將指定列的匹配值填入當前數據表的當前

31、列中。2.2.6 查找函數查找函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分25/101 參數說明：參數說明：lookup_value是要與數據表是要與數據表table_array第一列相匹配的內容，它可以是數值、單元引用或文第一列相匹配的內容，它可以是數值、單元引用或文本字符串；本字符串；table_array是要查找的單元格區(qū)域、數據是要查找的單元格區(qū)域、數據表或數組；表或數組；col_index_num為一個常數值，表示當為一個常數值，表示當lookup_value與數據表與數據表table_array第一列內容相匹配

32、第一列內容相匹配時，需要在當前列填入時，需要在當前列填入table_array區(qū)域中第幾列的內區(qū)域中第幾列的內容（為一列號）；容（為一列號）；range_lookup為一邏輯值，可取為一邏輯值，可取TRUE或或FALSE，當取，當取TRUE或缺省時，則返回近似或缺省時，則返回近似匹配值，即如果找不到精確匹配值，則返回小于匹配值，即如果找不到精確匹配值，則返回小于lookup_value的最大數值；若取的最大數值；若取FALSE，則返回精確，則返回精確匹配值，如果找不到，則返回錯誤信息匹配值，如果找不到，則返回錯誤信息“#N/A”。 2.2.6 查找函數查找函數第章第章 Excel Excel

33、高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分26/101 2.2.6 查找函數查找函數2 行匹配填充函數行匹配填充函數HLOOKUP 格式：格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 功能：在數據表的首行查找與指定的數值相匹配的功能：在數據表的首行查找與指定的數值相匹配的值，并將指定行的匹配值填入當前數據表的當前行中。值，并將指定行的匹配值填入當前數據表的當前行中。參數及使用方法與參數及使用方法與VLOOKUP函數相類似。函數相類似。第章第章 Excel Excel 高級應用高

34、級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分27/101 1求資產折舊值函數求資產折舊值函數SLN 格式：格式：SLN(cost,salvage,life)功能：求某項資產在一個期間中的線性折舊值。功能：求某項資產在一個期間中的線性折舊值。參數說明：參數說明：cost為資產原值；為資產原值；salvage為資產在折舊為資產在折舊期末的價值（也稱為資產殘值）；期末的價值（也稱為資產殘值）；life為折舊期限為折舊期限（有時也稱作資產的使用壽命）。（有時也稱作資產的使用壽命）。2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦

35、公軟件高級應用2022年5月5日1時32分28/101 2求貸款按年（或月）還款數函數求貸款按年（或月）還款數函數 PMT 格式：格式：PMT(rate，nper，pv，fv，type) 功能：求指定貸款期限的某筆貸款，按固定利率及功能：求指定貸款期限的某筆貸款，按固定利率及等額分期付款方式每期的付款額。等額分期付款方式每期的付款額。 參數說明：參數說明：rate為貸款利率；為貸款利率；nper 為該項貸款的總為該項貸款的總貸款期限；貸款期限；pv 為從該項貸款開始計算時已經入賬的款為從該項貸款開始計算時已經入賬的款項（或一系列未來付款當前值的累積和）；項（或一系列未來付款當前值的累積和）；f

36、v 為未來為未來值（或在最后一次付款后希望得到的現金余額），缺值（或在最后一次付款后希望得到的現金余額），缺省時為省時為0；type為一邏輯值，用于指定付款時間是在期為一邏輯值，用于指定付款時間是在期初還是在期末（初還是在期末（1表示期初，表示期初，0表示期末，缺省時為表示期末，缺省時為0）。）。2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分29/101 3求貸款按每月應付利息數函數求貸款按每月應付利息數函數 IPMT 格式：格式：IPMT(rate，per，nper，pv，fv) 功能：求指定貸款期

37、限的某筆貸款，按固定利率功能：求指定貸款期限的某筆貸款，按固定利率及等額分期付款方式在某一給定期限內每月應付的及等額分期付款方式在某一給定期限內每月應付的貸款利息。貸款利息。 參數說明：參數說明：rate為貸款利率；為貸款利率；per為計算利率的期為計算利率的期數（如計算第一個月的利息則為數（如計算第一個月的利息則為1，計算第二個月的，計算第二個月的利息則為利息則為2，依此類推），依此類推），nper 為該項貸款的總貸款為該項貸款的總貸款期數；期數；pv 為從該項貸款開始計算時已經入賬的款項為從該項貸款開始計算時已經入賬的款項（或一系列未來付款當前值的累積和）；（或一系列未來付款當前值的累積和

38、）；fv 為未來為未來值（或在最后一次付款后希望得到的現金余額），值（或在最后一次付款后希望得到的現金余額），缺省時為缺省時為02.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分30/101 4求某項投資的未來收益值函數求某項投資的未來收益值函數FV格式：格式：FV(rate,nper,pmt,pv,type)功能：基于固定利率及等額分期付款方式，返功能：基于固定利率及等額分期付款方式，返回某項投資的未來值。回某項投資的未來值。參數說明：參數說明：rate為各期利率；為各期利率；nper為總投資期，即該項

39、投資的付款期總數；為總投資期，即該項投資的付款期總數；pmt為各期所應支付的金額，其數值在整個年為各期所應支付的金額，其數值在整個年金期間保持不變；金期間保持不變；2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分31/101 pv為現值，即從該項投資開始計算時已經入帳為現值，即從該項投資開始計算時已經入帳的款項，或一系列未來付款的當前值的累積和，的款項，或一系列未來付款的當前值的累積和，也稱為本金。如果省略也稱為本金。如果省略 PV，則假設其值為零，則假設其值為零，并且必須包括并且必須包括 pmt 參數

40、；參數；type為數字為數字0或或1，用以指定各期的付款時間是，用以指定各期的付款時間是在期初還是期末，在期初還是期末，O表示期末，表示期末，1表示期初，缺表示期初，缺省時為省時為0。 2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分32/101 5求某項投資的現值函數求某項投資的現值函數PV 格式：格式：PV(rate,nper,pmt,fv,type) 功能：返回投資的現值?，F值為一系列未來付款的功能：返回投資的現值。現值為一系列未來付款的當前值的累積和。當前值的累積和。 參數說明：參數說明：rat

41、e為貸款利率；為貸款利率；nper為總投資（或貸款）期，即該項投資（或貸款）為總投資（或貸款）期，即該項投資（或貸款）的付款期總數；的付款期總數；pmt為各期所應支付的金額，其數值在整個年金期間為各期所應支付的金額，其數值在整個年金期間保持不變；保持不變；2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分33/101 fv為未來值，或在最后一次支付后希望得到的為未來值，或在最后一次支付后希望得到的現金余額，缺省時為現金余額，缺省時為0（一筆貸款的未來值即為零）；（一筆貸款的未來值即為零）；type為數字為

42、數字0或或1，用以指定各期的付款時間是，用以指定各期的付款時間是在期初還是期末在期初還是期末, O表示期末，表示期末，1表示期初，缺省時表示期初，缺省時為為0。 2.2.7 財務函數財務函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分34/101 1數據庫函數的概念數據庫函數的概念 數據庫函數是用于對存儲在數據清單或數數據庫函數是用于對存儲在數據清單或數據庫中的數據進行分析、判斷，并求出指定數據據庫中的數據進行分析、判斷，并求出指定數據區(qū)域中滿足指定條件的值。根據所具有的不同的區(qū)域中滿足指定條件的值。根據所具有的不同的功能，分

43、為數據庫信息函數和數據庫分析函數，功能，分為數據庫信息函數和數據庫分析函數，前者用于獲取數據庫中的信息，后者主要用于分前者用于獲取數據庫中的信息，后者主要用于分析數據庫的數據信息。析數據庫的數據信息。2.2.8 數據庫函數數據庫函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分35/101 2.2.8 數據庫函數數據庫函數數據庫函數的格式及參數的含義如下：數據庫函數的格式及參數的含義如下：函數格式：函數格式： 函數名函數名(database,field,criteria)。參數說明：參數說明：Database為構成數據清單或數據

44、庫的單元格數據為構成數據清單或數據庫的單元格數據區(qū)域。區(qū)域。Field為指定函數所使用的數據列，為指定函數所使用的數據列，Field 可以是可以是文本，即兩端帶引號的標志項，如文本，即兩端帶引號的標志項，如“出生日期出生日期”或或“年齡年齡”等；此外，等；此外，Field 也可以是代表數據清單中數也可以是代表數據清單中數據列位置的數字：據列位置的數字：1 表示第一列，表示第一列，2 表示第二列，等等。表示第二列，等等。Criteria為一組包含給定條件的單元格區(qū)域?？蔀橐唤M包含給定條件的單元格區(qū)域?？梢詾閰狄詾閰?criteria 指定任意區(qū)域，只要它至少包含一指定任意區(qū)域，只要它至少包含

45、一個列標志和列標志下方用于設定條件的單元格。個列標志和列標志下方用于設定條件的單元格。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分36/101 2.2.8 數據庫函數數據庫函數2數據庫函數的格式及使用方法數據庫函數的格式及使用方法（1）DAVERAGE 功能功能:返回數據庫或數據清單中滿足指定條返回數據庫或數據清單中滿足指定條件的列中數值的平均值。件的列中數值的平均值。 語法語法:DAVERAGE(database，field，criteria) 參數參數:Database構成列表或數據庫的單元格區(qū)構成列表或數據庫的單元格區(qū)域

46、。域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一組包含給定條件的單元格區(qū)域。為一組包含給定條件的單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分37/101 （2）DSUM 功能功能:返回數據清單或數據庫的指定列中，滿足給定條件單返回數據清單或數據庫的指定列中，滿足給定條件單元格中的數字之和。元格中的數字之和。 語法語法:DSUM(database，field，criteria) 參數參數:Database構成列表或數據庫的單元格區(qū)域。構成列表或數據庫的單元格區(qū)域。Field指定指定

47、函數所使用的數據列。函數所使用的數據列。Criteria為一組包含給定條件的單元格為一組包含給定條件的單元格區(qū)域。區(qū)域。（3）DCOUNT 功能功能:返回數據庫或數據清單指定字段中，滿足給定條件并返回數據庫或數據清單指定字段中，滿足給定條件并且包含數字的單元格的個數。且包含數字的單元格的個數。 語法語法:DCOUNT(database，field，criteria) 參數參數:Database構成列表或數據庫的單元格區(qū)域。構成列表或數據庫的單元格區(qū)域。Field指定指定函數所使用的數據列。函數所使用的數據列。Criteria為一組包含給定條件的單元格為一組包含給定條件的單元格區(qū)域。區(qū)域。 2.

48、2.8 數據庫函數數據庫函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分38/101 （4）DCOUNTA 功能功能:返回數據庫或數據清單指定字段中滿足給定返回數據庫或數據清單指定字段中滿足給定條件的非空單元格數目。條件的非空單元格數目。 語法語法:DCOUNTA(database，field，criteria) 參數參數:Database構成列表或數據庫的單元格區(qū)域。構成列表或數據庫的單元格區(qū)域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一組包含為一組包含給定條件的單元格區(qū)域。給定條件的單元

49、格區(qū)域。2.2.8 數據庫函數數據庫函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分39/101 （5）DGET 功能功能:從數據清單或數據庫中提取符合指定條件的從數據清單或數據庫中提取符合指定條件的單個值。單個值。語法語法:DGET(database，field，criteria) 參數參數:Database構成列表或數據庫的單元格區(qū)域。構成列表或數據庫的單元格區(qū)域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一組包含為一組包含給定條件的單元格區(qū)域。給定條件的單元格區(qū)域。 注意：注意： 若滿足

50、條件的只有一個值，則求出這個值；若滿足條件的只有一個值，則求出這個值； 若滿足條件的有多個值，則結果為；若滿足條件的有多個值，則結果為；#NUM!； 若沒有滿足條件的值，則結果為；若沒有滿足條件的值，則結果為；#VALUE!2.2.8 數據庫函數數據庫函數第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分40/101 2.2.8 數據庫函數數據庫函數（6）DMAX功能功能:返回數據清單或數據庫的指定列中，滿返回數據清單或數據庫的指定列中，滿足給定條件單元格中的最大數值。足給定條件單元格中的最大數值。語法語法:DMAX(databas

51、e，field，criteria)參數參數:Database構成列表或數據庫的單元格區(qū)構成列表或數據庫的單元格區(qū)域。域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一為一組包含給定條件的單元格區(qū)域。組包含給定條件的單元格區(qū)域。 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分41/101 2.2.8 數據庫函數數據庫函數（7）DMIN功能功能:返回數據清單或數據庫的指定列中滿足返回數據清單或數據庫的指定列中滿足給定條件的單元格中的最小數字。給定條件的單元格中的最小數字。語法語法:DMIN(datab

52、ase，field，criteria)參數參數:Database構成列表或數據庫的單元格區(qū)構成列表或數據庫的單元格區(qū)域。域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一為一組包含給定條件的單元格區(qū)域。組包含給定條件的單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分42/101 2.2.8 數據庫函數數據庫函數（8）DPRODUCT功能功能:返回數據清單或數據庫的指定列中，滿返回數據清單或數據庫的指定列中，滿足給定條件單元格中數值乘積。足給定條件單元格中數值乘積。語法語法:DPRODUC

53、T(database，field，criteria)參數參數:Database構成列表或數據庫的單元格區(qū)構成列表或數據庫的單元格區(qū)域。域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為為一組包含給定條件的單元格區(qū)域。一組包含給定條件的單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分43/101 2.2.8 數據庫函數數據庫函數（9）DSTDEV功能功能:將列表或數據庫的列中滿足指定條件將列表或數據庫的列中滿足指定條件的數字作為一個樣本，估算樣本總體的標準偏的數字作為一個樣本，估算樣本總體的

54、標準偏差。差。語法語法:DSTDEV(database，field，criteria)參數參數:Database構成列表或數據庫的單元格構成列表或數據庫的單元格區(qū)域。區(qū)域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一組包含給定條件的單元格區(qū)域。為一組包含給定條件的單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分44/101 2.2.8 數據庫函數數據庫函數（10）DVAR功能功能:將數據清單或數據庫的指定列中滿足將數據清單或數據庫的指定列中滿足給定條件單元格中的數字作為一個樣本，估算給

55、定條件單元格中的數字作為一個樣本，估算樣本總體的方差。樣本總體的方差。語法語法:DVAR(database，field，criteria)參數參數:Database構成列表或數據庫的單元格構成列表或數據庫的單元格區(qū)域。區(qū)域。Field指定函數所使用的數據列。指定函數所使用的數據列。Criteria為一組包含給定條件的單元格區(qū)域。為一組包含給定條件的單元格區(qū)域。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分45/101 2.2.8 數據庫函數數據庫函數（11）GETPIVOTDATA功能功能:返回存儲在數據透視表報表中的數據。如果

56、報表中的返回存儲在數據透視表報表中的數據。如果報表中的匯總數據可見，則可以使用函數匯總數據可見，則可以使用函數GETPIVOTDATA從數據透視表從數據透視表報表中檢索匯總數據。報表中檢索匯總數據。語法語法:GETPIVOTDATA(pivot_table，name)參數參數:Data_field為包含要檢索的數據的數據字段的名稱為包含要檢索的數據的數據字段的名稱(放放在引號中在引號中)。Pivot_table在數據透視表中對任何單元格、單元格在數據透視表中對任何單元格、單元格區(qū)域或定義的單元格區(qū)域的引用，該信息用于決定哪個數據數據區(qū)域或定義的單元格區(qū)域的引用，該信息用于決定哪個數據數據透視表

57、包含要檢索的數據。透視表包含要檢索的數據。Field1，Item1，Field2，Item2為為1到到14對用于描述檢索數據的字段名和項名稱，可以任意次序排列。對用于描述檢索數據的字段名和項名稱，可以任意次序排列。第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分46/101 2.2.9 測試函數測試函數1IS類函數類函數 IS函數包括函數包括ISBLANK、ISTEXT、ISERR、ISERROR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER和和ISREF函數，統稱為函數，統稱為IS類函數，可類函數，可以檢驗

58、數值的數據類型并根據參數取值的不同而以檢驗數值的數據類型并根據參數取值的不同而返回返回TRUE或或FALSE。 第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分47/101 2.2.9 測試函數測試函數2 TYPE測試函數測試函數 TYPE函數可以測試不同類型的數據，其函數格式為：函數可以測試不同類型的數據，其函數格式為： =TYPE(value)其中其中Value 可以為任意類型的數據，如數值、文本、邏輯可以為任意類型的數據，如數值、文本、邏輯值等。函數的返回值為一數值，具體意義為：值等。函數的返回值為一數值，具體意義為： 1數

59、值數值 2文本文本 4邏輯邏輯 16誤差值誤差值 64數組數組第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分48/101 2.3 數組公式數組公式2.3.1 2.3.1 數組的概念數組的概念2.3.2 2.3.2 數組公式的建立數組公式的建立2.3.3 2.3.3 數組公式的應用數組公式的應用第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5月5日1時32分49/101 2.3.1 數組的概念數組的概念 數組是一些元素的簡單集合，這些元素可以共數組是一些元素的簡單集合，這些元素可以共

60、同參與運算，也可以個別參與運算。在同參與運算，也可以個別參與運算。在Excel中，中，數組就是若干單元格的集合，它們可以是一維的，數組就是若干單元格的集合，它們可以是一維的，也可以是二維的，這些維對應著行和列。例如，也可以是二維的，這些維對應著行和列。例如，一維數組可以存儲在一行（橫向數組）或一列一維數組可以存儲在一行（橫向數組）或一列（縱向數組）的范圍內。二維數組可以存儲在一（縱向數組）的范圍內。二維數組可以存儲在一個矩形的單元格范圍內。個矩形的單元格范圍內。2.3.1 2.3.1 數組的概念數組的概念第章第章 Excel Excel 高級應用高級應用 辦公軟件高級應用辦公軟件高級應用2022年5