第3章-Excel2007-常用函數(shù)

1第三章常用函數(shù)Excel2007數(shù)據(jù)分析

及其應(yīng)用2第三章常用函數(shù)本章內(nèi)容：介紹Excel2007函數(shù)的基本概念及常用函數(shù)的使用方法，并舉例加以說明。常用函數(shù)包括：數(shù)學(xué)和三角函數(shù)、邏輯函數(shù)、日期和時間函數(shù)、文本函數(shù)、查找和引用函數(shù)教學(xué)目標(biāo)：通過本章的學(xué)習(xí)，使同學(xué)們對Excel2007的函數(shù)有個大致的了解，能夠使用函數(shù)解決一些實(shí)際問題3第三章常用函數(shù)

函數(shù)的概述數(shù)學(xué)和三角函數(shù)邏輯函數(shù)日期和時間函數(shù)文本函數(shù)查找和引用函數(shù)第1節(jié)函數(shù)的概述

函數(shù)的基本概念函數(shù)的分類

使用函數(shù)

使用函數(shù)的優(yōu)勢

獲得Excel函數(shù)的幫助預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式分為12類、356個函數(shù)直接使用、利用“插入函數(shù)”對話框簡化公式、實(shí)現(xiàn)特殊運(yùn)算、實(shí)現(xiàn)智能判斷、提高工作效率4單元格內(nèi)的工具提示使用“函數(shù)參數(shù)”對話框使用Excel幫助如果函數(shù)以公式的形式出現(xiàn)，請?jiān)诤瘮?shù)名稱前面鍵入等號

=函數(shù)的結(jié)構(gòu)

SUM(A10,B5:B10,50,37)51.1函數(shù)的基本概念函數(shù)名參數(shù)，可以是常量、邏輯值、數(shù)組、錯誤值或單元格引用等Excel2007的函數(shù)是指預(yù)先定義好，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式函數(shù)與公式的區(qū)別函數(shù)與公式既有區(qū)別又互相聯(lián)系。函數(shù)是Excel預(yù)先定義好的特殊公式，而公式由用戶自行設(shè)計(jì)對工作表進(jìn)行計(jì)算和處理的計(jì)算式

如：“=SUM(E1:H1)*A1+26”61.1函數(shù)的基本概念Excel2007的函數(shù)是指預(yù)先定義好，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式函數(shù)

89個ATP(分析工具包)函數(shù)，這些函數(shù)現(xiàn)在是Excel默認(rèn)安裝的一部分5個新的通用函數(shù)7個新的多維數(shù)據(jù)集函數(shù)，這些函數(shù)有助人們連接多維數(shù)據(jù)庫

71.2函數(shù)的分類Excel2007提供了356個函數(shù)。它還包含下面的內(nèi)容81.2函數(shù)的分類Excel2007提供了的函數(shù)。可分為12類：分類功能簡介數(shù)據(jù)庫函數(shù)對數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計(jì)算等日期與時間對日期和時間進(jìn)行計(jì)算、設(shè)置及格式化處理工程函數(shù)用于工程數(shù)據(jù)分析與處理信息函數(shù)對單元格或公式中數(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ù)對工作表數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析查找函數(shù)查找特定的數(shù)據(jù)或引用公式中的特定信息文本函數(shù)對公式、單格中的字符、文本進(jìn)行格式化或運(yùn)算數(shù)學(xué)函數(shù)進(jìn)行數(shù)學(xué)計(jì)算等外部函數(shù)進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)用戶用vba編寫，用于完成特定功能的函數(shù)直接使用函數(shù)在單元格中直接使用函數(shù)，是以“=”開始，然后輸入函數(shù)名及函數(shù)所需要的參數(shù)，函數(shù)的參數(shù)用括號括起來，各參數(shù)之間用逗號分隔

例如：現(xiàn)有我國不同地區(qū)的西紅柿價格數(shù)據(jù)，如果計(jì)算天津地區(qū)的西紅柿平均價格

91.3使用函數(shù)

使用AVERAGE(number1,[number2],...)函數(shù)

101.3使用函數(shù)在要得到結(jié)果的單元格內(nèi)直接輸入“=AVERAGE(B3:B7)”計(jì)算所有地區(qū)的西紅柿平均價格

111.3使用函數(shù)利用“插入函數(shù)”對話框使用函數(shù)

121.3使用函數(shù)選中要顯示結(jié)果的單元格在“公式”選項(xiàng)卡“函數(shù)庫”組，單擊“插入函數(shù)”利用“插入函數(shù)”對話框使用函數(shù)

131.3使用函數(shù)在“或選擇類別”下拉列表中選擇要使用函數(shù)所在的類別，這里選擇“常用函數(shù)”，然后再“選擇函數(shù)”列表中找到求平均值的函數(shù)AVERAGE利用“插入函數(shù)”對話框使用函數(shù)

141.3使用函數(shù)簡化公式函數(shù)可以使一些復(fù)雜的公式更易于使用，使復(fù)雜的數(shù)學(xué)表達(dá)式輸入更加簡化。比如，要計(jì)算B3:D7區(qū)域中數(shù)據(jù)的平均值時，使用公式計(jì)算量大，需要寫成：“=(B3+B4+B5+B6+B7+C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15”而使用函數(shù)將相當(dāng)簡單，只要寫成：“=AVERAGE(B3:D7)”

151.4使用函數(shù)的優(yōu)勢實(shí)現(xiàn)特殊運(yùn)算在日常的工作中，有一些計(jì)算使用公式不但復(fù)雜，而且不具有特定的專業(yè)知識還無法表達(dá)，那么就可以使用現(xiàn)有的函數(shù)進(jìn)行特殊運(yùn)算

例如，按揭貸款的等額分期償還額的計(jì)算必須用到高等數(shù)學(xué)的知識，而使用PMT函數(shù)(計(jì)算在固定利率下，貸款的等額分期償還額)就相當(dāng)簡單

PMT(rate,nper,pv,fv,type)

161.4使用函數(shù)的優(yōu)勢貸款利率付款期總數(shù)未來值現(xiàn)值付款時間實(shí)現(xiàn)特殊運(yùn)算

171.4使用函數(shù)的優(yōu)勢貸款利率付款期總數(shù)現(xiàn)值實(shí)現(xiàn)智能判斷用函數(shù)還可以實(shí)現(xiàn)智能判斷

例如，在學(xué)校，老師統(tǒng)計(jì)學(xué)生的考試成績，如果，其成績大于等于80則為“優(yōu)秀”,如果成績大于等于59且小于80則為“及格”,否則為“不及格”。這類問題用人工計(jì)算很不方便，可以使用函數(shù)來進(jìn)行自動化處理

181.4使用函數(shù)的優(yōu)勢實(shí)現(xiàn)智能判斷

191.4使用函數(shù)的優(yōu)勢提高工作效率

在學(xué)校的期末總結(jié)中，常會需要按學(xué)生的考試成績進(jìn)行排位的問題，當(dāng)然可以使用“排序”按鈕來完成，但是這樣做比較麻煩，如果使用排位函數(shù)RANK就比較方便，尤其是紀(jì)錄很多、相同分?jǐn)?shù)很多的情況下，不用RANK函數(shù)其工作量不可想像

RANK(number,ref,order)201.4使用函數(shù)的優(yōu)勢需要排位的數(shù)字引用的區(qū)域，絕對地址排位的方式提高工作效率211.4使用函數(shù)的優(yōu)勢由于Excel2007的函數(shù)眾多，功能很強(qiáng)，在課中無法全面的介紹這些函數(shù)，所以我們要使用Excel的幫助文件。Excel2007提供了很好的幫助文件。Excel有三個級別的幫助：單元格內(nèi)的工具提示、“函數(shù)參數(shù)”對話框和Excel的幫助。函數(shù)參數(shù)對話框是尋找?guī)椭淖詈棉k法。使用單元格內(nèi)的工具提示

在任何單元格內(nèi)，都可以輸入等號、函數(shù)名稱和左括號。Excel的工具提示將顯示預(yù)期的參數(shù)221.5獲得Excel函數(shù)的幫助使用“函數(shù)參數(shù)”對話框

“函數(shù)參數(shù)”對話框可以逐步幫助你建立函數(shù)231.5獲得Excel函數(shù)的幫助每個獨(dú)立參數(shù)的結(jié)果當(dāng)前參數(shù)的介紹函數(shù)的描述要求的參數(shù)函數(shù)的初步結(jié)果幫助的鏈接使用Excel幫助

有關(guān)函數(shù)的幫助非常全面。在每個函數(shù)的幫助主題中都可以找到下面的內(nèi)容

函數(shù)的語法

“注釋”提供了函數(shù)的疑難解答實(shí)例相關(guān)函數(shù)241.5獲得Excel函數(shù)的幫助

數(shù)學(xué)函數(shù)

基本數(shù)學(xué)函數(shù)

舍人和取整函數(shù)求和函數(shù)

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

舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機(jī)排號學(xué)校組織考試時，經(jīng)常需要將學(xué)生的學(xué)號秩序打亂，并隨機(jī)編排考號，使用RAND隨機(jī)函數(shù)可以方便的實(shí)現(xiàn)以上功能。

RAND函數(shù)的格式：

RAND()在單元格D2內(nèi)輸入公式“=RAND()”并向下填充，這樣在D列各單元格內(nèi)生存了0-1之間的任意數(shù)282.1數(shù)學(xué)函數(shù)-基本數(shù)學(xué)函數(shù)

舉例：用函數(shù)RAND函數(shù)為學(xué)生隨機(jī)排號按列D

“排序”,可以看到B、C二列已無序排列,再在A2單元格中輸入第一個考號,并按序列向下填充,將D列刪除或清空數(shù)據(jù)292.1數(shù)學(xué)函數(shù)-舍人和取整函數(shù)INT將數(shù)值向下舍入計(jì)取整ODD、EVEN將數(shù)值舍入為奇或偶數(shù)TRUNC將數(shù)值的小數(shù)部分截去，返回整數(shù)ROUND按指定位數(shù)四舍五入數(shù)值ROUNDUP、ROUNDDOWN按指定位數(shù)向上或向下四舍五入數(shù)值302.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é)果33even(-5.4)產(chǎn)生結(jié)果-6312.1數(shù)學(xué)函數(shù)-求和函數(shù)SUBTOTAL返回?cái)?shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總SUM計(jì)算某一單元格區(qū)域中所有數(shù)值之和SUMIF根據(jù)指定條件對若干單元格、區(qū)域或引用求和SUMPRODUCT計(jì)算數(shù)組間對應(yīng)的元素相乘，并返回乘積之和SUMSQ計(jì)算所有參數(shù)的平方和SUMX2MY2計(jì)算兩數(shù)組中對應(yīng)數(shù)值的平方差之和SUMX2PY2計(jì)算兩數(shù)組中對應(yīng)數(shù)值的平方和的總和SUMXMY2計(jì)算兩數(shù)組中對應(yīng)數(shù)值之差的平方和322.1數(shù)學(xué)函數(shù)-求和函數(shù)舉例：某學(xué)校的考試成績統(tǒng)計(jì)表，要統(tǒng)計(jì)女同學(xué)的英語成績總和

332.1數(shù)學(xué)函數(shù)-求和函數(shù)舉例：某學(xué)校的考試成績統(tǒng)計(jì)表，要統(tǒng)計(jì)女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù)SUMIF(range，criteria，sum_range)

條件判斷的單元格區(qū)域判定條件需要求和的單元格、區(qū)域或引用342.1數(shù)學(xué)函數(shù)-求和函數(shù)舉例：某學(xué)校的考試成績統(tǒng)計(jì)表，要統(tǒng)計(jì)女同學(xué)的英語成績總和。此例問題可以使用SUMIF函數(shù)在求和的單元格輸入公式：

“=SUMIF(B2:B4,”=女”,F2:F9)”

即得到結(jié)果

352.1數(shù)學(xué)函數(shù)-求和函數(shù)舉例：COUNTIF

功能：為滿足指定條件的單元計(jì)數(shù)格式：Countif(計(jì)數(shù)區(qū)域,“計(jì)數(shù)條件”)=countif（D2:D9,“>80”）

362.2三角函數(shù)DEGREES將弧度轉(zhuǎn)換為角度RADIANS角度轉(zhuǎn)換為弧度SIN求正弦值A(chǔ)SIN求反正弦值COS求余弦值A(chǔ)COS求反余弦值TAN求正切值A(chǔ)TAN求反正切值邏輯函數(shù)是用來判斷真假值，或者進(jìn)行復(fù)合檢驗(yàn)的Excel函數(shù)。在Excel中提供了七種邏輯函數(shù)37第3節(jié)邏輯函數(shù)AND當(dāng)AND的參數(shù)全部為TRUE時，返回結(jié)果為TRUE，否則為FALSEOR當(dāng)OR的參數(shù)中，如果任一參數(shù)為TRUE，返回結(jié)果

TRUE，否則為FALSENOTNOT函數(shù)用于對參數(shù)值求反TRUE返回邏輯值

TRUEFALSE返回邏輯值

FALSEIF對指定的條件計(jì)算結(jié)果為

TRUE或

FALSE，返回不同的結(jié)果IFERROR如果公式計(jì)算出錯誤則返回您指定的值；否則返回公式結(jié)果38第3節(jié)邏輯函數(shù)

舉例：IF

在學(xué)校的管理工作中，常常要對學(xué)生的考試成績進(jìn)行統(tǒng)計(jì)分析。假定現(xiàn)有某班級的考試成績，我們按照各科平均分對學(xué)生的考試成績進(jìn)行綜合評定。評定規(guī)則：如果平均分超過90時，評定為優(yōu)秀，如果平均分小于90并且超過60則認(rèn)為是合格的，否則記作不合格39第3節(jié)邏輯函數(shù)

舉例：根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù)IF(logical_test,value_if_true,value_if_false)logical_test為TRUE時返回的值測試表達(dá)式logical_test為FALSE時返回的值40第3節(jié)邏輯函數(shù)

舉例：根據(jù)規(guī)則，我們在綜合評定中使用IF函數(shù)(以B7為例）：“=IF(B6>60,IF(B6>90,”優(yōu)秀“,”合格“),”不合格“)”41第3節(jié)邏輯函數(shù)不及格E2>60E2>80E2>90E2>70及格中等良好優(yōu)秀為英語成績建立五級分轉(zhuǎn)換1、if(E2>60,if(E2>70,if(E2>80,if(E2>90,優(yōu)秀，良好)，中等)，及格)，不及格）42第3節(jié)邏輯函數(shù)

舉例：ORANDOR(logical1,logical2,……,logicaln)

功能：當(dāng)只要有一個參數(shù)的計(jì)算結(jié)果為TRUE時,OR函數(shù)的計(jì)算結(jié)果為TRUE,否則，計(jì)算結(jié)果為FALSEAND(logical1,logical2,……,logicaln)

功能：當(dāng)所有參數(shù)的計(jì)算結(jié)果都是TRUE時,AND函數(shù)的計(jì)算結(jié)果為TRUE,否則，計(jì)算結(jié)果為FALSE43第3節(jié)邏輯函數(shù)

舉例：ORAND44第3節(jié)邏輯函數(shù)

舉例：ORAND45第4節(jié)日期和時間函數(shù)DATA返回當(dāng)前日期和時間的序列號NOW返回當(dāng)前日期和時間的序列號TODAY返回當(dāng)前日期的序列號YEAR返回某日期對應(yīng)的年份MONTH返回以序列號表示的日期中的月份DAY返回以序列號表示的某日期的天數(shù)HOUR返回時間值的小時數(shù)46第4節(jié)日期和時間函數(shù)

舉例:顯示當(dāng)前的日期與時間47第5節(jié)文本函數(shù)LOWER將一個文字串中的所有大寫字母轉(zhuǎn)換為小寫字母，但不改變文本中的非字母的字符UPPER將文本轉(zhuǎn)換成大寫形式PROPER將文本字符串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫LEFT根據(jù)所指定的字符數(shù)，返回文本字符串中左邊的幾個字符MID返回文本字符串中從指定位置開始的特定數(shù)目的字符RIGHT根據(jù)所指定的字符數(shù)，返回文本字符串中右邊的幾個字符

Trim清除字符串中的空白。

EXACT比較兩個字符串，如果它們完全相同，則返回

TRUE；否則，返回

FALSE48第5節(jié)文本函數(shù)

舉例：LEFTRIGHTMID

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ù)49第5節(jié)文本函數(shù)

舉例：LEFTRIGHTMID

RIGHT功能：返回文本字符串中右邊的幾個字符語法：RIGHT(text,num_chars)參數(shù)：text是包含要提取字符的文本串。num_chars指定希望

RIGHT提取的字符數(shù)

50第5節(jié)文本函數(shù)查找函數(shù)

引用函數(shù)51第6節(jié)查找和引用函數(shù)52

第6節(jié)查找和引用函數(shù)-查找函數(shù)

LOOKUP返回一行或一列區(qū)域中或者數(shù)組中的某個值HLOOKUP在表格數(shù)組首行查找指定的值，并返回表格數(shù)組當(dāng)前行中指定行的值VLOOKUP在表格數(shù)組首列查找指定的值，并返回表格數(shù)組當(dāng)前行中指定列的值CHOOSE可以根據(jù)給定的索引值，從多達(dá)254個待選參數(shù)中選出相應(yīng)的值53

第6節(jié)查找和引用函數(shù)-查找函數(shù)

舉例：現(xiàn)有學(xué)生信息表如果我想查找某個學(xué)生的年齡，可以用LOOKUP函數(shù)

LOOKUP(lookup_value,lookup_vector,result_vector)指定查找對象指定定位區(qū)域指定查找返回結(jié)果區(qū)域要求lookup_vector和result_vector的區(qū)域必須對等54

第6節(jié)查找和引用函數(shù)-查找函數(shù)

舉例：現(xiàn)有學(xué)生信息表如果我想查找某個學(xué)生的年齡，公式“=LOOKUP(A5,A3:A8,B3:B8)”返回22(張雷的年齡)公式“=LOOKUP(A5,A3:A8,c3:c8)”返回男(張雷的性別)55

第6節(jié)查找和引用函數(shù)-引用函數(shù)

ADDRESS按照給定的行號和列號，建立文本類型的單元格地址COLUMN返回指定單元格引用

的列號。ROW返回指定單元格引用

的行號。AREAS返回引用中包含的區(qū)域個數(shù)COLUMNS返回?cái)?shù)組或引用的列數(shù)ROWS返回引用或數(shù)組的行數(shù)。INDEX返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用9、春去春又回，新桃換舊符。在那