第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ù)、查找和引用函數(shù) 教學(xué)目標(biāo)：通過本章的學(xué)習(xí)，使同學(xué)們對(duì)Excel 2007的函數(shù)有個(gè)大致的了解，能夠使用函數(shù)解決一些實(shí)際問題2第3章Excel2020常用函數(shù)第三章 常用函數(shù) 函數(shù)的概述 數(shù)學(xué)和三角函數(shù) 邏輯函數(shù) 日期和時(shí)間函數(shù) 文本函數(shù)查找和引用函數(shù)3第3章Excel2020常用函數(shù)第1節(jié) 函數(shù)的概述 函數(shù)的基本概念 函數(shù)的分類 使用函數(shù) 使用函

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

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

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

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

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

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

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

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

10、號(hào)。Excel的工具提示將顯示預(yù)期的參數(shù)1.5 獲得Excel 函數(shù)的幫助22第3章Excel2020常用函數(shù)使用“函數(shù)參數(shù)”對(duì)話框 “函數(shù)參數(shù)”對(duì)話框可以逐步幫助你建立函數(shù)1.5 獲得Excel 函數(shù)的幫助每個(gè)獨(dú)立參數(shù)的結(jié)果當(dāng)前參數(shù)的介紹函數(shù)的描述要求的參數(shù)函數(shù)的初步結(jié)果幫助的鏈接23第3章Excel2020常用函數(shù)使用Excel幫助 有關(guān)函數(shù)的幫助非常全面。在每個(gè)函數(shù)的幫助主題中都可以找到下面的內(nèi)容 函數(shù)的語法 “注釋”提供了函數(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計(jì)算數(shù)值的絕對(duì)值COMBIN計(jì)算指定元素?cái)?shù)目的組合數(shù)FACT計(jì)算給定正整數(shù)的階乘MOD計(jì)算兩數(shù)相除的余數(shù)，其結(jié)果的正負(fù)號(hào)與除數(shù)相同PI 計(jì)算圓周率，精確到小數(shù)點(diǎn)后14位POWER計(jì)算給定數(shù)字的乘冪。PRODUCT 計(jì)算所有參數(shù)的乘積RAND產(chǎn)生一個(gè)大于等于0小于1的隨機(jī)數(shù)RANDBETWEEN產(chǎn)生位于兩個(gè)指定數(shù)值之間的一個(gè)隨機(jī)數(shù) SIGN 返回?cái)?shù)值的符號(hào)。正數(shù)返回1，零返回0，負(fù)數(shù)時(shí)返回-1SQRT計(jì)算某一正數(shù)的算術(shù)平方根EXP計(jì)算e的n次冪LN、LOG、LOG10 計(jì)算對(duì)數(shù)值MDETERM 計(jì)算一個(gè)矩陣行

12、列式的值26第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù) 舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機(jī)排號(hào) 學(xué)校組織考試時(shí)，經(jīng)常需要將學(xué)生的學(xué)號(hào)秩序打亂，并隨機(jī)編排考號(hào)，使用RAND隨機(jī)函數(shù)可以方便的實(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é)生隨機(jī)排號(hào) 按列D “排序”,可以看到B、C二列已無序排列,再在A2單元格中輸入第一個(gè)考號(hào),并按序列向下填充,將D列刪除或清空數(shù)據(jù)28第3章Excel

13、2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-舍人和取整函數(shù)INT 將數(shù)值向下舍入計(jì)取整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向絕對(duì)值增大方向舍入為最接近的奇數(shù) even(n): 將n向絕對(duì)值增大方向舍入為最接近的偶數(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返回?cái)?shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總SUM計(jì)算某一單元格區(qū)域中所有數(shù)值之和SUMIF根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和SUMPRODUCT計(jì)算數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和SUMSQ計(jì)算所有參數(shù)的平方和SUMX2MY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)值的平方差之和SUMX2PY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)值的平方和的總和SUMXMY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)值之差的平方和31第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：某學(xué)校的考試成績(jī)統(tǒng)計(jì)表，要統(tǒng)計(jì)女同學(xué)的英語成績(jī)總和32第3章Excel2020常用函數(shù)2.1 數(shù)學(xué)函數(shù)-求和函數(shù) 舉例：某

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

16、計(jì)數(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ù)是用來判斷真假值，或者進(jìn)行復(fù)合檢驗(yàn)的Excel函數(shù)。在Excel中提供了七種邏輯函數(shù)第3節(jié) 邏輯函數(shù)AND 當(dāng)AND的參數(shù)全部為TRUE時(shí)，返回結(jié)果為TRUE，否則為FALSE OR 當(dāng)OR的參數(shù)中，如果任一參數(shù)為TRUE，返回結(jié)果 TRUE，否則為FALSE

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

18、根據(jù)規(guī)則，我們?cè)诰C合評(píng)定中使用IF函數(shù)IF(logical_test,value_if_true,value_if_false)logical_test 為 TRUE 時(shí)返回的值測(cè)試表達(dá)式logical_test 為 FALSE時(shí)返回的值39第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù) 舉例：根據(jù)規(guī)則，我們?cè)诰C合評(píng)定中使用IF函數(shù) (以B7為例）： “ =IF(B660,IF(B690,”優(yōu)秀“,”合格“),”不合格“)”40第3章Excel2020常用函數(shù)第3節(jié) 邏輯函數(shù)不及格E260E280E290E270及格中等良好優(yōu)秀為英語成績(jī)建立五級(jí)分轉(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)只要有一個(gè)參數(shù)的計(jì)算結(jié)果為TRUE時(shí),OR函數(shù)的計(jì)算結(jié)果為TRUE, 否則，計(jì)算結(jié)果為FALSE AND (logical1,logical2,logicaln) 功能：當(dāng)所有參數(shù)的計(jì)算結(jié)果都是TRUE時(shí),AND函數(shù)的計(jì)算結(jié)果為TRUE, 否則，計(jì)算結(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í)間函數(shù)DATA返回當(dāng)前日期和時(shí)間的序列號(hào)NOW返回當(dāng)前日期和時(shí)間的序列號(hào)TODAY返回當(dāng)前日期的序列號(hào)YEAR返回某日期對(duì)應(yīng)的年份MONTH返回以序列號(hào)表示的日期中的月份DAY返回以序列號(hào)表示的某日期的天數(shù)HOUR返回時(shí)間值的小時(shí)數(shù)45第3章Excel2020常用函數(shù)第4節(jié)日期和時(shí)間函數(shù) 舉例:顯示當(dāng)前的日期與時(shí)間46第3章Excel2020常用函數(shù)第5節(jié) 文本函數(shù)LOWER將一個(gè)文字串中的所有大寫字母轉(zhuǎn)換為小寫字母，但不改變文本中的非字母的字符UPPER將文本轉(zhuǎn)換成大寫形式PROPER 將文本字符串的首字母及任何非字母

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