EXCEL函數(shù)培訓(xùn)課件

Excel函數(shù)匯報(bào)人:Excel中所提de函數(shù)其實(shí)是一些預(yù)定義de公式,它們使用一些稱為參數(shù)de特定數(shù)值按特定de順序或結(jié)構(gòu)進(jìn)行計(jì)算.Excel函數(shù)一共有一一類,分別是:數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)、以及用戶自定義函數(shù).Excel函數(shù)介紹:目錄CONTENTS第一部分:相關(guān)概念第二部分:常用函數(shù)零一第一部分:相關(guān)概念一.函數(shù)語(yǔ)法:由函數(shù)名+括號(hào)+參數(shù)組成例:求和函數(shù):SUM（A一,B二,…).參數(shù)與參數(shù)之間用逗號(hào)”,”隔開參數(shù)包括單元格引用和常量,文本型常量需要用引號(hào),如字符竄“abc”.第一部分:相關(guān)概念二.運(yùn)算符:☆公式運(yùn)算符:加（+）、減（-）、乘（*）、除（/）、百分號(hào)（%）、乘冪（＾）

☆比較運(yùn)算符:大與（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>）☆引用運(yùn)算符:區(qū)域運(yùn)算符（:）、聯(lián)合運(yùn)算符（,）第一部分:相關(guān)概念三.單元格de相對(duì)引用與絕對(duì)引用:例:A一$A一鎖定第A列A$一鎖定第一行$A$一鎖定第A列與第一行第一部分:相關(guān)概念零二第二部分:常用函數(shù)（一）統(tǒng)計(jì)函數(shù)（二）邏輯函數(shù)（三）文本函數(shù)（四）查找與引用函數(shù)（五）日期函數(shù)（六）信息函數(shù)第二部分:常用函數(shù)求和:SUM()條件求和:SUMIF()求個(gè)數(shù):COUNT()條件求個(gè)數(shù):COUNTIF()求算術(shù)平均數(shù):AVERAGE()四舍五入函數(shù):ROUND()排位函數(shù):RANK()（一）統(tǒng)計(jì)函數(shù)一.求和:SUM()■SUM(數(shù)值一,數(shù)值二,…)=SUM(六,八)=六+八=一四=SUM(A一,C一)=六+八=一四=SUM(A一:C一)=六+七+八=二一（一）統(tǒng)計(jì)函數(shù)

ABC一六七八二.條件求和:SUMIF()■SUMIF(查找de范圍,條件（即對(duì)象）,要求和de范圍)例

=SUMIF(A一:A四,”>=二零零”,B一:B四)=四零+六零+八零=一八零=SUMIF(A一:A四,”<三零零”,C一:C四)=一五零零+一零零零=二五零零（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零三.求個(gè)數(shù):COUNT()■COUNT(數(shù)值一,數(shù)值二,…)例

=COUNT(A一:A四)=四=COUNT(A一:C四)=一二（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零四.條件求個(gè)數(shù):COUNTIF()■COUNTIF(范圍,條件)例

=COUNTIF(A一:A四,”<>二零零”)=三=COUNTIF(A一:C四,”>=一零零零”)=四=COUNTIF(A一:C四,”>=一零零”)零-COUNTIF(A一:C四,”>二零零零”)=七（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零五.求算術(shù)平均數(shù):AVERAGE()■AVERAGE(數(shù)值一,數(shù)值二,…)例

=AVERAGE(A一,B二)=六零=AVERAGE(A一:A四)=二五零（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零六.四舍五入函數(shù):ROUND()■ROUND(數(shù)值,保留de小數(shù)位數(shù))例

=ROUND(A一/A三,二)=零.三三（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零七.排位函數(shù):RANK()■RANK(數(shù)值,范圍,序別)一-升序零-降序例

=RANK(A一,A一:A四,一)=一=RANK(A一,A一:A四,零)=四=RANK(A一,A一:C四,一)=五（一）統(tǒng)計(jì)函數(shù)

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零執(zhí)行條件函數(shù)IF()將條件按且方式結(jié)合函數(shù)AND()將條件按或方式結(jié)合函數(shù)OR()將條件值反過(guò)來(lái)函數(shù)NOT()（二）邏輯函數(shù)一.條件函數(shù):IF()■IF(條件,執(zhí)行條件真,執(zhí)行條件假)–可執(zhí)行七層嵌套例A一=五A二=三A三=六A四=七=IF(A一>A二,一,零)=一=IF(A一<A二,一,零)=零=IF(A一>A二,IF(A三>A四,八,九),一)=九（二）邏輯函數(shù)二.將條件按且方式結(jié)合函數(shù)AND■AND(條件)括號(hào)中條件值均為TRUE,則為TRUE；如果任一個(gè)為FALSE,則為FALSE例=AND(五>三,二>一)=TRUE=AND(一>三,二>一)=FALSE（二）邏輯函數(shù)三.將條件按或方式結(jié)合函數(shù)OR■OR(條件)括號(hào)中條件值均為FALSE,則為FALSE；如果任一個(gè)為TRUE,則為TRUE例=OR(一>三,二>一)=TRUE=OR(一>三,二>五)=FALSE（二）邏輯函數(shù)四.將條件值反過(guò)來(lái)函數(shù)NOT■NOT(條件)括號(hào)中條件值為FALSE,則為TRUE；如果為TRUE,則為FALSE例=NOT(五>三)=FALSE=NOT(一>三)=TRUE（二）邏輯函數(shù)截取函數(shù):LEFT()、RIGHT()、MID()計(jì)算字符長(zhǎng)度:LEN()合并字符函數(shù):CONCATENATE()在字符串中查找特定字符:FIND()比較兩個(gè)字符是否完全相符:EXACT()將數(shù)值轉(zhuǎn)化為文本:TEXT()將數(shù)值型字符轉(zhuǎn)換為數(shù)值:VALUE()（三）文本函數(shù)一.截取函數(shù):LEFT()、RIGHT()、MID()■LEFT(文本,截取長(zhǎng)度)--從左邊截取例=LEFT(“abcdefg”,三)=abc■RIGHT(文本,截取長(zhǎng)度)--從右邊截取例=RIGHT(“abcdefg”,三)=efg■MID(文本,開始位,截取長(zhǎng)度)--從中間截取例=MID(“abcdefg”,二,三)=bcd（三）文本函數(shù)二.計(jì)算字符長(zhǎng)度:LEN()■LEFT(文本)例=LEN(“abcdefg”)=七三.合并字符函數(shù):CONCATENATE()通常使用&符號(hào)■CONCATENATE(文本一,文本二,…)例=CONCATENATE(“ab”,“cd”,“ef”)=abcdef=“ab”&“cd”&“ef”=abcdef（三）文本函數(shù)四.在字符串中查找特定字符:FIND()■FIND(文本,范圍,數(shù)值)--數(shù)值表示查找第幾個(gè)

例=FIND(“a”,“abcadeafga”,二)=四=FIND(“a”,“abcadeafga”,三)=七=FIND(“a”,“abcadeafga”,四)=一零（三）文本函數(shù)五.比較兩個(gè)字符是否完全相符:EXACT()■EXACT(文本一,文本二)

例=EXACT(“a”,“a”)=TRUE=EXACT(“a”,“ab”)=FALSE=EXACT(“a”,“A”)=FALSE（三）文本函數(shù)六.將數(shù)值轉(zhuǎn)化為文本:TEXT()■TEXT(數(shù)值,參數(shù))--參數(shù)一般為零例=TEXT(“一二三四”,零)=一二三四七.將數(shù)值型字符轉(zhuǎn)化為數(shù)值:VALUE()■VALUE(數(shù)值文本)例=VALUE(“一二三四”)=一二三四（三）文本函數(shù)一.以列de方式查找表格中de值:VLOOKUP()■VLOOKUP(對(duì)象,范圍,數(shù)值,FALSE)對(duì)象----需要查找de文本范圍----至少包含對(duì)象及需要返回de值所在de列de連續(xù)范圍數(shù)值----需要返回de值所在列de列數(shù)FALSE----精確查找

例=VLOOKUP(A一,A一:D一零,三,FALSE)（四）查找函數(shù)返回日期序列年份YEAR()返回日期序列月份MONTH()返回日期序列特定天DATE()返回系統(tǒng)今天日期TODAY()返回系統(tǒng)今天日期與時(shí)間NOW()（五）日期函數(shù)一.返回日期序列年份YEAR()■YEAR(日期)例A一=二零一一-零七-一四

=YEAR(A一)=二零一一)（五）日期函數(shù)二.返回日期序列月份MONTH()■MONTH(日期)例A一=二零一一-零七-一四=MONTH(A一)=七（五）日期函數(shù)三.返回日期序列特定天DATE()■DATE(日期)例A一=二零一一-零七-一四

=DATE(A一)=一四（五）日期函數(shù)四.返回系統(tǒng)今天日期TODAY()■TODAY()=TODAY()=今天de日期例如:二零一一-零七-一四五.返回系統(tǒng)今天日期與時(shí)間NOW()■NOW()=NOW()=二零一一-零七-一四一四:二五:四五（五）日期函數(shù)檢測(cè)是否為錯(cuò)誤值函數(shù)ISERROR()檢測(cè)是否為錯(cuò)誤值#N/A函數(shù)ISNA()檢測(cè)引用單元格是否為空ISBLANK()檢測(cè)一個(gè)值是否是數(shù)值ISNUMBER()檢測(cè)一個(gè)值是否是邏輯值ISLOGICAL()上述函數(shù)返回結(jié)果為一個(gè)邏輯值TRUE或者FALSE（六）信息函數(shù)謝

謝Excel函數(shù)培訓(xùn)教材演講人:一、相對(duì)引用和絕對(duì)引用二、常用函數(shù)解析三、結(jié)合公式自定義數(shù)據(jù)有效性和條件格式培訓(xùn)大綱相對(duì)引用和絕對(duì)引用一、相對(duì)引用和絕對(duì)引用如圖:C一=A一+B一,如果將C一單元格復(fù)制粘貼到D一,得到de結(jié)果是七而不是原來(lái)是單元格de值五,

這是因?yàn)閺?fù)制過(guò)程中我們一并也把公式復(fù)制到了D一,當(dāng)計(jì)算結(jié)果欄位由原來(lái)deC一偏移到D一時(shí),則參與計(jì)算de其他欄位也會(huì)同時(shí)發(fā)生偏移,即D一=B一+C一.倘若將A一、B一絕對(duì)引用,那么參與公式計(jì)算是相應(yīng)欄位就被鎖定,若此時(shí)將C一復(fù)制粘貼到其他欄位,得到de結(jié)果就是五如果只是想粘貼數(shù)值,則可在粘貼de時(shí)候用“選擇性粘貼”只針對(duì)數(shù)值進(jìn)行粘貼,如下圖在將C一欄位粘貼到D一欄位時(shí),右擊—選擇性粘貼—數(shù)值,則de得到de結(jié)果將是原來(lái)C一欄位de數(shù)值五（無(wú)論是絕對(duì)引用還是相對(duì)引用）.一、相對(duì)引用和絕對(duì)引用混合引用是有絕對(duì)de行或相對(duì)de列.絕對(duì)引用列是采用$A一、$B一等形式.絕對(duì)引用行是采用A$一、B$一等形式.如果公式所在單元格de位置改變,則相對(duì)引用de改變而絕對(duì)引用de不變.如果多行或多列de復(fù)制公式,相對(duì)引用自動(dòng)調(diào)整而絕對(duì)引用將不做調(diào)整.例如,下圖公式中絕對(duì)引用了A列和二三行,公式復(fù)制過(guò)程中只對(duì)相對(duì)引用de部分進(jìn)行調(diào)整.注:相對(duì)引用與絕對(duì)引用可以通過(guò)快捷鍵“F四”進(jìn)行相互切換.常用函數(shù)解析（一）日期與時(shí)間函數(shù)TODAY():返回日期格式de當(dāng)前日期NOW():返回日期時(shí)間格式de當(dāng)前日期和時(shí)間WEEKDAY():返回某日期de星期數(shù).在默認(rèn)情況下,它de值為一(星期天)到七(星期六)之間de一個(gè)整數(shù).語(yǔ)法:WEEKDAY(serial_number,return_type)參數(shù):Serial_number是要返回日期數(shù)de日期,通常用帶引號(hào)de文本串(如“二零零一/零二/二六”).Return_type為確定返回值類型de數(shù)字,數(shù)字一或省略,則一至七代表星期天到星期六；數(shù)字二,則一至七代表星期一到星期天；數(shù)字三,則零至六代表星期一到星期天.（一）日期與時(shí)間函數(shù)NETWORKDAYS(）函數(shù)專門用于計(jì)算兩個(gè)日期值之間完整de工作日數(shù)值.這個(gè)工作日數(shù)值將不包括雙休日和專門指定de其他各種假期.語(yǔ)法:NETWORKDAYS(Start_date,End_date,Holidays)Start_date表示開始日期；End_date為終止日期,Holidays表示作為特定假日de一個(gè)或多個(gè)日期.這些參數(shù)值既可以手工輸入,也可以對(duì)單元格de值進(jìn)行引用.例如,下圖中要計(jì)算二零一二-五-一到二零一二-五-三一de工作日,A二為起始日期,B二為終止日期C二:C四為特定de假日.計(jì)算公式如下圖:注:手工輸入de日期需加英文狀態(tài)de引號(hào).可Ctrl+.進(jìn)行切換.（一）日期與時(shí)間函數(shù)WORKDAY:返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和專門指定de假日)de某一日期de值,并扣除周末或假日.語(yǔ)法:WORKDAY(start_date,days,holidays)參數(shù):Start_date為開始日期；Days為Start_date之前或之后不含周末及節(jié)假日de天數(shù)；Days是正值將產(chǎn)生未來(lái)日期、負(fù)值產(chǎn)生過(guò)去日期；Holidays為可選de數(shù)據(jù)清單,表示需要從工作日中排除de日期值(如法定假日或非法定假日).此清單可以是包含日期de單元格區(qū)域,也可以是由代表日期de序列號(hào)所構(gòu)成de數(shù)組常量.例如,下圖中要計(jì)算二零一二-一二-二五前排除特定節(jié)假日往前、后推一零個(gè)工作日de日期,計(jì)算公式如下:（一）日期與時(shí)間函數(shù)DATEDIF（）返回兩個(gè)日期之間de年\月\日間隔數(shù).語(yǔ)法:DATEDIF(start_date,end_date,unit),Start_date代表時(shí)間段內(nèi)de第一個(gè)日期或起始日期.End_date它代表時(shí)間段內(nèi)de最后一個(gè)日期或結(jié)束日期.Unit為所需信息de返回類型.Unit返回"Y"時(shí)間段中de整年數(shù)."M"時(shí)間段中de整月數(shù)."D"時(shí)間段中de天數(shù)."YD"start_date與end_date日期中天數(shù)de差.忽略日期中de年."MD"start_date與end_date日期中天數(shù)de差.忽略日期中de月和年."YM"start_date與end_date日期中月數(shù)de差.忽略日期中de日和年.注:結(jié)束日期必須大于起始日期.（二）統(tǒng)計(jì)函數(shù)COUNTIF（）用于計(jì)算區(qū)域中滿足給定條件de單元格de個(gè)數(shù).語(yǔ)法:COUNTIF(range,criteria)參數(shù):Range為需要計(jì)算其中滿足條件de單元格數(shù)目de單元格區(qū)域.Criteria為確定哪些單元格將被計(jì)算在內(nèi)de條件,其形式可以為數(shù)字、表達(dá)式或文本.（二）統(tǒng)計(jì)函數(shù)COUNTIFS（）用于計(jì)算區(qū)域中滿足給定多條件de單元格de個(gè)數(shù).語(yǔ)法:COUNTIFS(range一,criteria一,range二,criteria二,…)Rang一為需要計(jì)算其中滿足條件一de單元格數(shù)目de單元格區(qū)域.Criteria一為確定哪些單元格將被計(jì)算在內(nèi)de第一個(gè)條件,Rang二為需要計(jì)算其中滿足條件二de單元格數(shù)目de單元格區(qū)域.Criteria二為確定哪些單元格將被計(jì)算在內(nèi)de第二個(gè)條件,以此類推,每一對(duì)range和criteria之間用逗號(hào)隔開.（二）統(tǒng)計(jì)函數(shù)SUMIF():根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和.語(yǔ)法:SUMIF(range,criteria,sum_range)參數(shù):Range為用于條件判斷de單元格區(qū)域,Criteria是由數(shù)字、邏輯表達(dá)式等組成de判定條件,Sum_range為需要求和de單元格、區(qū)域或引用.例如,下圖中要計(jì)算銷售部de工資總和,A列為用于條件判斷de單元格區(qū)域,$E$四為判斷條件,C列需要求和de區(qū)域.計(jì)算公式如下:（二）統(tǒng)計(jì)函數(shù)SUMIFS():根據(jù)指定de多條件對(duì)若干單元格、區(qū)域或引用求和.語(yǔ)法:SUMIFS(sum_range,criteria_range一,criteria一,criteria_range二,criteria二,...)參數(shù):sum_range是需要求和de實(shí)際單元格區(qū)域criteria_range一為用于條件一判斷de單元格區(qū)域.criteria一為條件一.criteria_range二為用于條件二判斷de單元格區(qū)域.criteria二為條件二.例如,下圖中要計(jì)算求八月一日以前de筆記本銷量總額以及求八月一日到八月七日期間服務(wù)器de銷量總額,屬于多條件引用求和.計(jì)算公式如下:（三）查找和引用函數(shù)VLOOKUP（）:在表格或數(shù)值數(shù)組de首列查找指定de數(shù)值,并由此返回表格或數(shù)組當(dāng)前行中指定列處de數(shù)值.語(yǔ)法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)參數(shù):Lookup_value為需要在數(shù)據(jù)表第一列中查找de數(shù)值,Table_array為需要在其中查找數(shù)據(jù)區(qū)域.Col_index_num為table_array中待返回de匹配值de列序號(hào).Range_lookup為一邏輯值,指明函數(shù)VLOOKUP返回時(shí)是精確匹配還是近似匹配.如果Range_lookup為一或省略,則返回近似匹配值（模糊匹配）,如果找不到精確匹配值,則返回小于lookup_valuede最大數(shù)值；如果range_value為零,函數(shù)VLOOKUP將返回精確匹配值.注:一.查找值一定要在第一列.二.模糊匹配時(shí)第一列一定要升序排列.（三）查找和引用函數(shù)精確查找案例,下圖中要查找AB-S零五de銷售額,G三為要查找de數(shù)值,A:D為要查找de區(qū)域,四表示要查找值在區(qū)域de第四列,零表示精確查找,公式如下圖所示:模糊查找案例,下圖中要查找收入$二一,五六六對(duì)應(yīng)de稅率,B三為要查找de數(shù)值,D三:F九為要查找de區(qū)域,三表示要查找值在區(qū)域de第三列,一表示模糊查找,公式如下圖所示:（三）查找和引用函數(shù)OFFSET（）,以指定de引用為參照系,通過(guò)給定偏移量得到新de引用.返回de引用可以是一個(gè)單元格或單元格區(qū)域,并可以指定返回de行數(shù)或列數(shù).如下圖使用OFFSET函數(shù)實(shí)現(xiàn)動(dòng)態(tài)圖表,OFFSET最少包含了三個(gè)參數(shù),其中第一個(gè)參數(shù)表示從該欄位進(jìn)行偏移（起點(diǎn)）,第二個(gè)參數(shù)表示行偏移數(shù),第三個(gè)參數(shù)表示列偏移數(shù),所以當(dāng)?shù)诙?、第三參?shù)值變化時(shí),對(duì)結(jié)果區(qū)域引用并插入圖表,則該圖表就是會(huì)隨參數(shù)值de變化而變化.（四）文本函數(shù)CONCATENATE（）:將若干文字串合并到一個(gè)文字串中,其功能與"&"運(yùn)算符相同.語(yǔ)法:CONCATENATE(text一,text二,...)參數(shù):Text一,text二,...為將要合并成單個(gè)文本de文本項(xiàng),這些文本項(xiàng)可以是文字串、數(shù)字或?qū)蝹€(gè)單元格de引用.LEN()函數(shù)返回文本字符串中de字符數(shù).語(yǔ)法:LEN(text)Text是要查找其長(zhǎng)度de文本.空格將作為字符進(jìn)行計(jì)數(shù).（四）文本函數(shù)MOD():函數(shù)返回兩數(shù)相除de余數(shù).結(jié)果de正負(fù)號(hào)與除數(shù)相同.語(yǔ)法:MOD(number,divisor)Number為被除數(shù)；Divisor為除數(shù).right（）:根據(jù)所指定de字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符.語(yǔ)法:RIGHT(text,num_chars)參數(shù):Text是包含要提取字符de