第3章Excel2020常用函數(shù)課件

1、第3章Excel2020常用函數(shù)2022/7/25第3章Excel2020常用函數(shù)第三章 常用函數(shù) 本章內(nèi)容：介紹Excel 2007函數(shù)的基本概念及常用函數(shù)的使用方法，并舉例加以說明。常用函數(shù)包括：數(shù)學(xué)和三角函數(shù)、邏輯函數(shù)、日期和時間函數(shù)、文本函數(shù)、查找和引用函數(shù) 教學(xué)目標：通過本章的學(xué)習(xí)，使同學(xué)們對Excel 2007的函數(shù)有個大致的了解，能夠使用函數(shù)解決一些實際問題2第3章Excel2020常用函數(shù)第三章 常用函數(shù) 函數(shù)的概述 數(shù)學(xué)和三角函數(shù) 邏輯函數(shù) 日期和時間函數(shù) 文本函數(shù)查找和引用函數(shù)3第3章Excel2020常用函數(shù)第1節(jié) 函數(shù)的概述 函數(shù)的基本概念 函數(shù)的分類 使用函數(shù) 使用函

2、數(shù)的優(yōu)勢 獲得Excel 函數(shù)的幫助預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式分為12類、356個函數(shù)直接使用、利用“插入函數(shù)”對話框簡化公式、實現(xiàn)特殊運算、實現(xiàn)智能判斷、提高工作效率單元格內(nèi)的工具提示使用“函數(shù)參數(shù)”對話框使用Excel幫助4第3章Excel2020常用函數(shù)如果函數(shù)以公式的形式出現(xiàn)，請在函數(shù)名稱前面鍵入等號 = 函數(shù)的結(jié)構(gòu) SUM(A10,B5:B10,50,37)1.1 函數(shù)的基本概念函數(shù)名參數(shù)，可以是常量、邏輯值、數(shù)組、錯誤值或單元格引用等 Excel 2007的函數(shù)是指預(yù)先定義好，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式5第3章Excel2020常用函數(shù) 函數(shù)與公式

3、的區(qū)別 函數(shù)與公式既有區(qū)別又互相聯(lián)系。函數(shù)是Excel預(yù)先定義好的特殊公式，而公式由用戶自行設(shè)計對工作表進行計算和處理的計算式 如：“=SUM(E1:H1)*A1+26”1.1 函數(shù)的基本概念 Excel 2007的函數(shù)是指預(yù)先定義好，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式函數(shù)6第3章Excel2020常用函數(shù) 89個ATP(分析工具包)函數(shù)，這些函數(shù)現(xiàn)在是Excel默認安裝的一部分5個新的通用函數(shù)7個新的多維數(shù)據(jù)集函數(shù)，這些函數(shù)有助人們連接多維數(shù)據(jù)庫 1.2 函數(shù)的分類 Excel 2007提供了356個函數(shù)。它還包含下面的內(nèi)容7第3章Excel2020常用函數(shù)1.2 函數(shù)的分類 Excel

4、 2007提供了的函數(shù)?？煞譃?2類：分 類功 能 簡 介數(shù)據(jù)庫函數(shù)對數(shù)據(jù)清單中的數(shù)據(jù)進行分析、查找、計算等日期與時間對日期和時間進行計算、設(shè)置及格式化處理工程函數(shù)用于工程數(shù)據(jù)分析與處理信息函數(shù)對單元格或公式中數(shù)據(jù)類型進行判定財務(wù)函數(shù)進行財務(wù)分析及財務(wù)數(shù)據(jù)的計算邏輯函數(shù)進行邏輯判定、條件檢查統(tǒng)計函數(shù)對工作表數(shù)據(jù)進行統(tǒng)計、分析查找函數(shù)查找特定的數(shù)據(jù)或引用公式中的特定信息文本函數(shù)對公式、單格中的字符、文本進行格式化或運算數(shù)學(xué)函數(shù)進行數(shù)學(xué)計算等外部函數(shù)進行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)用戶用vba編寫，用于完成特定功能的函數(shù)8第3章Excel2020常用函數(shù)直接使用函數(shù) 在單元格中直

5、接使用函數(shù)，是以“=”開始，然后輸入函數(shù)名及函數(shù)所需要的參數(shù)，函數(shù)的參數(shù)用括號括起來，各參數(shù)之間用逗號分隔 例如：現(xiàn)有我國不同地區(qū)的西紅柿價格數(shù)據(jù)，如果計算天津地區(qū)的西紅柿平均價格 1.3 使用函數(shù)9第3章Excel2020常用函數(shù) 使用AVERAGE (number1, number2,.)函數(shù) 1.3 使用函數(shù)在要得到結(jié)果的單元格內(nèi)直接輸入“=AVERAGE(B3:B7)”10第3章Excel2020常用函數(shù)計算所有地區(qū)的西紅柿平均價格 1.3 使用函數(shù)11第3章Excel2020常用函數(shù)利用“插入函數(shù)”對話框使用函數(shù) 1.3 使用函數(shù)選中要顯示結(jié)果的單元格在“公式”選項卡“函數(shù)庫”組，單

6、擊“插入函數(shù)”12第3章Excel2020常用函數(shù)利用“插入函數(shù)”對話框使用函數(shù) 1.3 使用函數(shù)在“或選擇類別”下拉列表中選擇要使用函數(shù)所在的類別，這里選擇“常用函數(shù)”，然后再“選擇函數(shù)”列表中找到求平均值的函數(shù)AVERAGE13第3章Excel2020常用函數(shù)利用“插入函數(shù)”對話框使用函數(shù) 1.3 使用函數(shù)14第3章Excel2020常用函數(shù)簡化公式 函數(shù)可以使一些復(fù)雜的公式更易于使用，使復(fù)雜的數(shù)學(xué)表達式輸入更加簡化。比如，要計算B3:D7區(qū)域中數(shù)據(jù)的平均值時，使用公式計算量大，需要寫成：“=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/1

7、5”而使用函數(shù)將相當(dāng)簡單，只要寫成：“=AVERAGE(B3:D7)” 1.4 使用函數(shù)的優(yōu)勢15第3章Excel2020常用函數(shù)實現(xiàn)特殊運算 在日常的工作中，有一些計算使用公式不但復(fù)雜，而且不具有特定的專業(yè)知識還無法表達，那么就可以使用現(xiàn)有的函數(shù)進行特殊運算 例如，按揭貸款的等額分期償還額的計算必須用到高等數(shù)學(xué)的知識，而使用PMT函數(shù)(計算在固定利率下，貸款的等額分期償還額)就相當(dāng)簡單 PMT(rate,nper,pv,fv,type) 1.4 使用函數(shù)的優(yōu)勢貸款利率付款期總數(shù)未來值現(xiàn)值付款時間16第3章Excel2020常用函數(shù)實現(xiàn)特殊運算 1.4 使用函數(shù)的優(yōu)勢貸款利率付款期總數(shù)現(xiàn)值17

8、第3章Excel2020常用函數(shù)實現(xiàn)智能判斷 用函數(shù)還可以實現(xiàn)智能判斷 例如，在學(xué)校，老師統(tǒng)計學(xué)生的考試成績，如果，其成績大于等于80則為“優(yōu)秀”,如果成績大于等于59且小于80則為“及格”,否則為“不及格”。這類問題用人工計算很不方便，可以使用函數(shù)來進行自動化處理 1.4 使用函數(shù)的優(yōu)勢18第3章Excel2020常用函數(shù)實現(xiàn)智能判斷 1.4 使用函數(shù)的優(yōu)勢19第3章Excel2020常用函數(shù)提高工作效率 在學(xué)校的期末總結(jié)中，常會需要按學(xué)生的考試成績進行排位的問題，當(dāng)然可以使用“排序”按鈕來完成，但是這樣做比較麻煩，如果使用排位函數(shù)RANK就比較方便，尤其是紀錄很多、相同分數(shù)很多的情況下，不

9、用RANK函數(shù)其工作量不可想像 RANK(number,ref,order)1.4 使用函數(shù)的優(yōu)勢需要排位的數(shù)字引用的區(qū)域，絕對地址排位的方式20第3章Excel2020常用函數(shù)提高工作效率1.4 使用函數(shù)的優(yōu)勢21第3章Excel2020常用函數(shù) 由于Excel 2007的函數(shù)眾多，功能很強，在課中無法全面的介紹這些函數(shù)，所以我們要使用Excel 的幫助文件。Excel 2007 提供了很好的幫助文件。Excel有三個級別的幫助：單元格內(nèi)的工具提示、“函數(shù)參數(shù)”對話框和Excel的幫助。函數(shù)參數(shù)對話框是尋找?guī)椭淖詈棉k法。使用單元格內(nèi)的工具提示 在任何單元格內(nèi)，都可以輸入等號、函數(shù)名稱和左括

10、號。Excel的工具提示將顯示預(yù)期的參數(shù)1.5 獲得Excel 函數(shù)的幫助22第3章Excel2020常用函數(shù)使用“函數(shù)參數(shù)”對話框 “函數(shù)參數(shù)”對話框可以逐步幫助你建立函數(shù)1.5 獲得Excel 函數(shù)的幫助每個獨立參數(shù)的結(jié)果當(dāng)前參數(shù)的介紹函數(shù)的描述要求的參數(shù)函數(shù)的初步結(jié)果幫助的鏈接23第3章Excel2020常用函數(shù)使用Excel幫助 有關(guān)函數(shù)的幫助非常全面。在每個函數(shù)的幫助主題中都可以找到下面的內(nèi)容 函數(shù)的語法 “注釋”提供了函數(shù)的疑難解答 實例 相關(guān)函數(shù)1.5 獲得Excel 函數(shù)的幫助24第3章Excel2020常用函數(shù) 數(shù)學(xué)函數(shù) 基本數(shù)學(xué)函數(shù) 舍人和取整函數(shù) 求和函數(shù) 三角函數(shù)第2節(jié)

11、 數(shù)學(xué)和三角函數(shù)25第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù)ABS計算數(shù)值的絕對值COMBIN計算指定元素數(shù)目的組合數(shù)FACT計算給定正整數(shù)的階乘MOD計算兩數(shù)相除的余數(shù)，其結(jié)果的正負號與除數(shù)相同PI 計算圓周率，精確到小數(shù)點后14位POWER計算給定數(shù)字的乘冪。PRODUCT 計算所有參數(shù)的乘積RAND產(chǎn)生一個大于等于0小于1的隨機數(shù)RANDBETWEEN產(chǎn)生位于兩個指定數(shù)值之間的一個隨機數(shù) SIGN 返回數(shù)值的符號。正數(shù)返回1，零返回0，負數(shù)時返回-1SQRT計算某一正數(shù)的算術(shù)平方根EXP計算e的n次冪LN、LOG、LOG10 計算對數(shù)值MDETERM 計算一個矩陣行

12、列式的值26第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù) 舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機排號 學(xué)校組織考試時，經(jīng)常需要將學(xué)生的學(xué)號秩序打亂，并隨機編排考號，使用RAND隨機函數(shù)可以方便的實現(xiàn)以上功能。 RAND函數(shù)的格式： RAND()在單元格D2內(nèi)輸入公式“=RAND()”并向下填充，這樣在D列各單元格內(nèi)生存了0-1之間的任意數(shù)27第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù) 舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機排號 按列D “排序”,可以看到B、C二列已無序排列,再在A2單元格中輸入第一個考號,并按序列向下填充,將D列刪除或清空數(shù)據(jù)28第3章Excel

13、2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-舍人和取整函數(shù)INT 將數(shù)值向下舍入計取整ODD、EVEN 將數(shù)值舍入為奇或偶數(shù)TRUNC 將數(shù)值的小數(shù)部分截去，返回整數(shù)ROUND 按指定位數(shù)四舍五入數(shù)值ROUNDUP 、ROUNDDOWN 按指定位數(shù)向上或向下四舍五入數(shù)值29第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-舍人和取整函數(shù) 舉例: odd, even 功能：將數(shù)據(jù)舍入為奇數(shù)或偶數(shù) odd(n) : 將n向絕對值增大方向舍入為最接近的奇數(shù) even(n): 將n向絕對值增大方向舍入為最接近的偶數(shù) odd(31.3) 產(chǎn)生結(jié)果 33 even(-5.4) 產(chǎn)生結(jié)果 -630第3章Excel20

14、20常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù)SUBTOTAL返回數(shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總SUM計算某一單元格區(qū)域中所有數(shù)值之和SUMIF根據(jù)指定條件對若干單元格、區(qū)域或引用求和SUMPRODUCT計算數(shù)組間對應(yīng)的元素相乘，并返回乘積之和SUMSQ計算所有參數(shù)的平方和SUMX2MY2計算兩數(shù)組中對應(yīng)數(shù)值的平方差之和SUMX2PY2計算兩數(shù)組中對應(yīng)數(shù)值的平方和的總和SUMXMY2計算兩數(shù)組中對應(yīng)數(shù)值之差的平方和31第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：某學(xué)校的考試成績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和32第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：某

15、學(xué)校的考試成績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù) SUMIF(range，criteria，sum_range)條件判斷的單元格區(qū)域判定條件需要求和的單元格、區(qū)域或引用33第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：某學(xué)校的考試成績統(tǒng)計表，要統(tǒng)計女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù) 在求和的單元格輸入公式： “=SUMIF(B2:B4,”=女”,F2:F9)” 即得到結(jié)果34第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：COUNTIF 功能：為滿足指定條件的單元計數(shù) 格式：Countif( 計數(shù)區(qū)域, “

16、計數(shù)條件” ) = countif（D2:D9,“80”）35第3章Excel2020常用函數(shù)2.2 三角函數(shù)DEGREES 將弧度轉(zhuǎn)換為角度RADIANS 角度轉(zhuǎn)換為弧度SIN 求正弦值 ASIN 求反正弦值 COS 求余弦值 ACOS 求反余弦值 TAN 求正切值 ATAN 求反正切值 36第3章Excel2020常用函數(shù) 邏輯函數(shù)是用來判斷真假值，或者進行復(fù)合檢驗的Excel函數(shù)。在Excel中提供了七種邏輯函數(shù)第3節(jié) 邏輯函數(shù)AND 當(dāng)AND的參數(shù)全部為TRUE時，返回結(jié)果為TRUE，否則為FALSE OR 當(dāng)OR的參數(shù)中，如果任一參數(shù)為TRUE，返回結(jié)果 TRUE，否則為FALSE

17、NOT NOT函數(shù)用于對參數(shù)值求反TRUE 返回邏輯值 TRUE FALSE 返回邏輯值 FALSE IF 對指定的條件計算結(jié)果為 TRUE 或 FALSE，返回不同的結(jié)果IFERROR 如果公式計算出錯誤則返回您指定的值；否則返回公式結(jié)果37第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：IF 在學(xué)校的管理工作中，常常要對學(xué)生的考試成績進行統(tǒng)計分析。假定現(xiàn)有某班級的考試成績，我們按照各科平均分對學(xué)生的考試成績進行綜合評定。評定規(guī)則：如果平均分超過90時，評定為優(yōu)秀，如果平均分小于90并且超過60則認為是合格的，否則記作不合格38第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：

18、根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù)IF(logical_test,value_if_true,value_if_false)logical_test 為 TRUE 時返回的值測試表達式logical_test 為 FALSE時返回的值39第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù) (以B7為例）： “ =IF(B660,IF(B690,”優(yōu)秀“,”合格“),”不合格“)”40第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù)不及格E260E280E290E270及格中等良好優(yōu)秀為英語成績建立五級分轉(zhuǎn)換1、if(E260,if(E270,if

19、(E280, if(E290,優(yōu)秀，良好)，中等)，及格)，不及格）41第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：OR AND OR (logical1,logical2,logicaln) 功能：當(dāng)只要有一個參數(shù)的計算結(jié)果為TRUE時,OR函數(shù)的計算結(jié)果為TRUE, 否則，計算結(jié)果為FALSE AND (logical1,logical2,logicaln) 功能：當(dāng)所有參數(shù)的計算結(jié)果都是TRUE時,AND函數(shù)的計算結(jié)果為TRUE, 否則，計算結(jié)果為FALSE42第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：OR AND43第3章Excel2020常用函數(shù)第3節(jié) 邏輯

20、函數(shù) 舉例：OR AND44第3章Excel2020常用函數(shù)第4節(jié)日期和時間函數(shù)DATA返回當(dāng)前日期和時間的序列號NOW返回當(dāng)前日期和時間的序列號TODAY返回當(dāng)前日期的序列號YEAR返回某日期對應(yīng)的年份MONTH返回以序列號表示的日期中的月份DAY返回以序列號表示的某日期的天數(shù)HOUR返回時間值的小時數(shù)45第3章Excel2020常用函數(shù)第4節(jié)日期和時間函數(shù) 舉例:顯示當(dāng)前的日期與時間46第3章Excel2020常用函數(shù)第5節(jié) 文本函數(shù)LOWER將一個文字串中的所有大寫字母轉(zhuǎn)換為小寫字母，但不改變文本中的非字母的字符UPPER將文本轉(zhuǎn)換成大寫形式PROPER 將文本字符串的首字母及任何非字母

21、字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫LEFT根據(jù)所指定的字符數(shù)，返回文本字符串中左邊的幾個字符MID返回文本字符串中從指定位置開始的特定數(shù)目的字符RIGHT根據(jù)所指定的字符數(shù)，返回文本字符串中右邊的幾個字符 Trim清除字符串中的空白。 EXACT 比較兩個字符串，如果它們完全相同，則返回 TRUE；否則，返回 FALSE47第3章Excel2020常用函數(shù)第5節(jié) 文本函數(shù) 舉例：LEFT RIGHT MID LEFT功能：返回文本字符串中左邊的幾個字符語法：LEFT(text,num_chars)參數(shù)：text是包含要提取字符的文本串。num_chars指定要由 LEFT 所提取的字符數(shù) MID功能：返回文本字符串中從指定位置開始的特定數(shù)目的字符語法：MID(text,start_num,num_chars)參數(shù)：text是包含要提取字符的文本串。start_num是文本中要提取的第一個字符的位置。num_chars指定從文本串中返回字符的個數(shù)48第3章Excel2020常用函數(shù)第5節(jié) 文本函數(shù) 舉例：LEFT RIGHT MID RIGHT功能：返回文本字符串中右邊的幾個字符語法：RIGHT(text,num_chars)參數(shù)：text是包含要提取字符的文本串。num