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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

例=FIND(“a”,“abcadeafga”,二)=四=FIND(“a”,“abcadeafga”,三)=七=FIND(“a”,“abcadeafga”,四)=一零（三）文本函數(shù)五.比較兩個字符是否完全相符: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(對象,范圍,數(shù)值,FALSE)對象----需要查找de文本范圍----至少包含對象及需要返回de值所在de列de連續(xù)范圍數(shù)值----需要返回de值所在列de列數(shù)FALSE----精確查找

例=VLOOKUP(A一,A一:D一零,三,FALSE)（四）查找函數(shù)返回日期序列年份YEAR()返回日期序列月份MONTH()返回日期序列特定天DATE()返回系統(tǒng)今天日期TODAY()返回系統(tǒng)今天日期與時間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)今天日期與時間NOW()■NOW()=NOW()=二零一一-零七-一四一四:二五:四五（五）日期函數(shù)檢測是否為錯誤值函數(shù)ISERROR()檢測是否為錯誤值#N/A函數(shù)ISNA()檢測引用單元格是否為空ISBLANK()檢測一個值是否是數(shù)值ISNUMBER()檢測一個值是否是邏輯值ISLOGICAL()上述函數(shù)返回結(jié)果為一個邏輯值TRUE或者FALSE（六）信息函數(shù)謝

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

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