嚴福文之常用函數(shù)教程

1、ExcelExcel常用函數(shù)應用教程常用函數(shù)應用教程講師：嚴福文講師：嚴福文EXCELEXCEL常用常用函數(shù)應用教程函數(shù)應用教程1.1.EXCELEXCEL函數(shù)的基本知識函數(shù)的基本知識2.2.邏輯函數(shù)的應用邏輯函數(shù)的應用3.3.文本函數(shù)的應用文本函數(shù)的應用4.4.日期與時間函數(shù)日期與時間函數(shù)5.5.查詢查詢與引用與引用函數(shù)函數(shù)Unit 1 EXCELUnit 1 EXCEL函數(shù)的基本知識函數(shù)的基本知識1.1.什么什么是函數(shù)是函數(shù)2.2.函數(shù)的參數(shù)和返回值函數(shù)的參數(shù)和返回值3.3.函數(shù)的地址符函數(shù)的地址符4.4.函數(shù)的套用函數(shù)的套用 Excel Excel中所提的中所提的函數(shù)函數(shù)其實是一些預定義

2、的公式，它們使用一其實是一些預定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結構進行計算。些稱為參數(shù)的特定數(shù)值按特定的順序或結構進行計算。簡單點說，函數(shù)是一組功能模塊，使用函數(shù)能幫助你實現(xiàn)某個簡單點說，函數(shù)是一組功能模塊，使用函數(shù)能幫助你實現(xiàn)某個功能。功能。舉例：舉例：SumSum函數(shù)可以實現(xiàn)數(shù)值的加法運算，函數(shù)可以實現(xiàn)數(shù)值的加法運算，maxmax函數(shù)可以返回一組函數(shù)可以返回一組數(shù)的最大值。數(shù)的最大值。 1.1.什么什么是函數(shù)是函數(shù)=（B4+25）/SUM(D5:F5)公式特定符號單元格地址運算操作符號EXCEL函數(shù)數(shù)值常量數(shù)值常量區(qū)域范圍引用函數(shù)說明2. 2. 函數(shù)的參數(shù)及返回值函

3、數(shù)的參數(shù)及返回值1.1.參數(shù)可以是數(shù)字、文本、形如參數(shù)可以是數(shù)字、文本、形如 TRUE TRUE 或或 FALSE FALSE 的邏輯數(shù)值、的邏輯數(shù)值、數(shù)組。數(shù)組。數(shù)字數(shù)字：1 1、2 2、100100、1000010000文本：文本：astronastron、412398784737847412398784737847（以文本格式存儲的數(shù)字）（以文本格式存儲的數(shù)字）邏輯值：邏輯值：TRUE(TRUE(真真) )或非零（一般用數(shù)字或非零（一般用數(shù)字1 1表示）、表示）、FALSE(FALSE(假假) )或或0 0比如比如13,1313這個不等式的值為假。這個不等式的值為假。單元格：比如單元格：

4、比如A47A47、B16B16數(shù)組：同一種格式存儲的單元格區(qū)域數(shù)組：同一種格式存儲的單元格區(qū)域注意：函數(shù)使用的參數(shù)所用的符號，如逗號，引號要全部在注意：函數(shù)使用的參數(shù)所用的符號，如逗號，引號要全部在英文輸入狀態(tài)下輸入英文輸入狀態(tài)下輸入( (半角符號半角符號) )。 錯誤類型原因舉例#N/A使用查找函數(shù)找不到數(shù)據(jù)#VALUE! 參數(shù)或操作數(shù)類型錯誤=sum(“a”,”b”)#Null！ 不正確的區(qū)域運算符=SUM(B2:C2 B3:C3)錯誤類型原因舉例#DIV/0!當數(shù)字被零 (0) 除=100/0#REF!單元格引用 無效=VLOOKUP(C,E3:F11,4,0)#列不夠寬，或使用了負的日

5、期或負的時間-2008-1-2#NAME? 函數(shù)名錯誤=sun(a1:a5)3 3. . 函數(shù)的地址符函數(shù)的地址符在我們使用函數(shù)時，會引用一些地址作為參數(shù)，比如在我們使用函數(shù)時，會引用一些地址作為參數(shù)，比如A47A47、B46B46等等EXCELEXCEL中有專門的地址引用符中有專門的地址引用符$ $。使用使用是是$ $引用地址時，是絕對引用引用地址時，是絕對引用，不，不使用使用$ $時，是相對引用。時，是相對引用。舉例：舉例：A47A47：相對引用相對引用，引用，引用A47A47這個單元格。當使用這種類型的地址時這個單元格。當使用這種類型的地址時，我們，我們使用拖拉功能將公式使用拖拉功能將公

6、式引用引用到到其它單元格時，這個其它單元格時，這個相對地址相對地址會發(fā)生會發(fā)生變化變化$A$47:$A$47:絕對引用絕對引用。無論將公式引用到哪一個單元格。無論將公式引用到哪一個單元格，地址永遠，地址永遠定格在定格在A47A47這個單元格。這個單元格。$A47$A47和和A$47:A$47:混合引用混合引用。只定格單元格的行或列，相對來說有點復雜。只定格單元格的行或列，相對來說有點復雜，不，不常用。常用。冒號（：）在與單元格一起引用時，代表一片區(qū)域，冒號（：）在與單元格一起引用時，代表一片區(qū)域，如如 (A1:A47) (A1:A47)代表的是從代表的是從A1A1一直到一直到A47A47的長條

7、形區(qū)域，總共的長條形區(qū)域，總共4747個單元格，個單元格，如（如（A1:B3)A1:B3)代表以代表以A1A1和和B3B3為對角的一個方形區(qū)域，共為對角的一個方形區(qū)域，共9 9個單元格。個單元格。 4. 4. 函數(shù)的套用函數(shù)的套用函數(shù)的套用，也就是函數(shù)的組合使用，單個函數(shù)已經(jīng)很厲害了，函數(shù)的套用，也就是函數(shù)的組合使用，單個函數(shù)已經(jīng)很厲害了，組合起來的函數(shù)威力會更組合起來的函數(shù)威力會更強大強大，最多支持最多支持7 7層套用。層套用。舉例：舉例：IF(SUM(A1,B2,C3IF(SUM(A1,B2,C3)5,“100”,“1000”)5,“100”,“1000”)在這個函數(shù)中，邏輯函數(shù)在這個函數(shù)

8、中，邏輯函數(shù)IFIF套用了套用了SUMSUM函數(shù)，它把函數(shù)，它把SUMSUM函數(shù)返回函數(shù)返回的結果作為參數(shù)，的結果作為參數(shù)，如果如果A1,B2,C3A1,B2,C3之之和小于和小于5 5，返回，返回100100，反之，返，反之，返回回1000.1000. Unit 2 Unit 2 邏輯函數(shù)的應用邏輯函數(shù)的應用1.1.IFIF函數(shù)函數(shù)2.2.ANDAND函數(shù)函數(shù)3.3.OROR函數(shù)函數(shù)4.4.NOTNOT函數(shù)函數(shù)1. IF1. IF函數(shù)函數(shù)IFIF在英文里是在英文里是“假如，如果假如，如果”的意思的意思，顧名思義就是如果滿足某個條件（為真），顧名思義就是如果滿足某個條件（為真）則返回前面的一個

9、值，不滿足條件（為假），返回后面的一個值。則返回前面的一個值，不滿足條件（為假），返回后面的一個值。語法：語法：IF(IF(邏輯值，返回值邏輯值，返回值1 1，返回值，返回值2)2)，其中邏輯值類型為真或假，另外，其中邏輯值類型為真或假，另外返返回值是文本格式時，要用雙引號回值是文本格式時，要用雙引號，如果為數(shù)字，可以不用雙引號。，如果為數(shù)字，可以不用雙引號。舉例：如何判斷考試成績是否及格？舉例：如何判斷考試成績是否及格？IF(IF(A1A1 6060,“,“不及格不及格”,“”,“及格及格”)”)，如果單元格如果單元格A1A1中的成績小于中的成績小于6060，則，則返回不及格，反之，返回及格

10、。返回不及格，反之，返回及格。IF(A160 IF(A184,“IF(A184,“優(yōu)秀優(yōu)秀”,“,“及格及格”)”)，這是，這是IFIF函數(shù)函數(shù)套用套用IFIF函數(shù)，如果函數(shù)，如果A1A1小于小于6060，返回不及格，另外如果大于等于，返回不及格，另外如果大于等于8585，則返回，則返回優(yōu)秀，否則返回及格。優(yōu)秀，否則返回及格。2. AND2. AND函數(shù)函數(shù)ANDAND在英文里是在英文里是“和，與和，與”的意思的意思, ,只有當所有條件都滿足時，才返回只有當所有條件都滿足時，才返回tureture( (真真) )，否則返回，否則返回false(false(假假),),一般要與其它函數(shù)套用，比如

11、一般要與其它函數(shù)套用，比如IFIF函數(shù)。函數(shù)。語法：語法：AND(AND(條件條件1 1，條件，條件2 2，條件條件30)30)返回值：返回值：TURETURE或或FALSEFALSE舉例：舉例：1.and(A160,B280),1.and(A160,B280),只有只有A160,B280A160,B280這三個條件這三個條件都滿足時，才返回都滿足時，才返回TRUE,TRUE,否則否則FALSE.FALSE.2.IF2.IF（and(A160,B280),1and(A160,B280),1，2 2），三個條件都滿足時，返回），三個條件都滿足時，返回1 1，否則返回否則返回2.2.3. OR3.

12、 OR函數(shù)函數(shù)OROR在英文里是在英文里是“或，或者或，或者”的意思的意思，只要滿足任一條件就，只要滿足任一條件就返回返回tureture( (真真) )，否則返回，否則返回false(false(假假),),一般要與其它函數(shù)套一般要與其它函數(shù)套用，比如用，比如IFIF函數(shù)。函數(shù)。語法：語法：OR(OR(條件條件1 1，條件，條件2 2，條件條件30)30)舉例：舉例：oror(A160,B280),(A160,B280),當當A160,B280A160,B280這三個條件有一個滿足時，返回這三個條件有一個滿足時，返回TRUE,TRUE,否則否則FALSE.FALSE.4. not4. not

13、函數(shù)函數(shù)notnot在英文里是在英文里是“ “ 不，非不，非”的意思的意思，返回相反的值一般要，返回相反的值一般要與其它函數(shù)套用，比如與其它函數(shù)套用，比如IFIF函數(shù)。函數(shù)。語法：語法：not(not(條件條件) )舉例：舉例：not(A160),not(A160),當當A160A160時，返回時，返回FALSE,FALSE,否則否則TRUE.TRUE.Unit 3 Unit 3 文本函數(shù)的應用文本函數(shù)的應用1.1.比較函數(shù)比較函數(shù)excatexcat與等號與等號= =2.2.聯(lián)結函數(shù)聯(lián)結函數(shù)concatenateconcatenate與聯(lián)結號與聯(lián)結號& &3.3.leftleft、right

14、right、midmid函數(shù)函數(shù)1.1.比較函數(shù)比較函數(shù)excatexcat與等號與等號= =ExcatExcat是一個比較函數(shù)，等號是一個比較函數(shù)，等號= =與之有相同的功能。返回值為邏輯值，與之有相同的功能。返回值為邏輯值，對于返回值為邏輯值的函數(shù)，一般與對于返回值為邏輯值的函數(shù)，一般與ifif等函數(shù)套用。等函數(shù)套用。語法：語法：excatexcat(A1,B1)(A1,B1)舉例：舉例：1.excat(A1,B1),1.excat(A1,B1),當當A1A1與與B1B1相同時，返回相同時，返回TRUE,TRUE,否則否則FALSE.FALSE. 與與ifif函數(shù)套用，函數(shù)套用，if(if

15、(excatexcat(A1,B1),”(A1,B1),”相同相同”，“不相同不相同”) )。2.2.使用等號比較時，如單元格使用等號比較時，如單元格C1=C1=（A1=B1A1=B1），如果），如果A1A1與與B1B1 相同，相同，C1=TRUE,C1=TRUE,否則否則C1=FALSEC1=FALSE 與與ifif函數(shù)套用，函數(shù)套用，if(A1=B1,“if(A1=B1,“相同相同”，“不相同不相同”) )。2.2.聯(lián)結函數(shù)聯(lián)結函數(shù)concatenateconcatenate與聯(lián)結號與聯(lián)結號& &concatenateconcatenate是一個聯(lián)結函數(shù)，聯(lián)結號是一個聯(lián)結函數(shù)，聯(lián)結號& &

16、與之有相同的功與之有相同的功能。能。語法：語法：concatenate(concatenate(字符字符1,1,字符字符2 2，)，單獨的文本要，單獨的文本要使用雙引號使用雙引號舉例：舉例：1.concatenate(A1,B11.concatenate(A1,B1，“trontron”),”),如果如果A1=AA1=A，B1=sB1=s，那么函數(shù)就會返回那么函數(shù)就會返回AstronAstron2.2. 如果如果C1=A1&B1&“C1=A1&B1&“trontron”，那么，那么C1=AstronC1=Astron3. left3. left、rightright、midmid函數(shù)函數(shù)舉例：

17、舉例：假如假如A1=AstronA1=Astron1.1.Left(A1,2)=AsLeft(A1,2)=As2.2.Right(A1,2)=onRight(A1,2)=on3.3.Mid(A1,2,2)=Mid(A1,2,2)=ststUnit 4Unit 4 日期與時間函數(shù)日期與時間函數(shù)1.1.nownow、todaytoday函數(shù)函數(shù)2.2.yearyear、monthmonth、dayday、hourhour函數(shù)函數(shù)3.3.dateifdateif函數(shù)函數(shù)1. now1. now、todaytoday函數(shù)函數(shù)nownow函數(shù)是提取當前系統(tǒng)日期和時間函數(shù)是提取當前系統(tǒng)日期和時間Today

18、Today函數(shù)是提取當前系統(tǒng)日期函數(shù)是提取當前系統(tǒng)日期語法：語法：Now()Now()參數(shù)為空參數(shù)為空Today()Today()參數(shù)為空參數(shù)為空舉例：舉例：假定系統(tǒng)當前時間為假定系統(tǒng)當前時間為 2016/06/25 2016/06/25 9:489:481.1.如果如果A1=now(),A1=now(),那么那么A1= A1= 2016/06/25 9:482016/06/25 9:482.2.如果如果B1=today(),B1=today(),那么那么B1= B1= 2016/06/252016/06/252. year2. year、monthmonth、dayday、hourhour函

19、數(shù)函數(shù)yearyear函數(shù)作用是提取日期中的年份函數(shù)作用是提取日期中的年份 monthmonth函數(shù)作用是提取日期中的月份函數(shù)作用是提取日期中的月份DayDay函數(shù)作用是提取日期中的日數(shù)函數(shù)作用是提取日期中的日數(shù) HourHour函數(shù)作用是提取日期中的小時數(shù)函數(shù)作用是提取日期中的小時數(shù)語法：語法：XXX(A1)XXX(A1)，其中，其中XXXXXX代表上面所有函數(shù)，它們語法規(guī)則一樣，代表上面所有函數(shù)，它們語法規(guī)則一樣，A1A1代表包含日期與代表包含日期與時間的單元格。時間的單元格。舉例：舉例：假定系統(tǒng)當前時間為假定系統(tǒng)當前時間為 A1=2016/06/25 10:58A1=2016/06/25

20、 10:581.1. B1=year(A1B1=year(A1),),那么那么B1= B1= 201620162.2. C1=month(A1),C1=month(A1),那么那么C1=06C1=063 3. D1=day(A1). D1=day(A1)，那么，那么D1=25D1=254. E1=hour(A1),4. E1=hour(A1),那么那么E1=10E1=103. 3. dateifdateif函數(shù)函數(shù)dateifdateif函數(shù)作用是計算兩個日期之間相差的年數(shù)、月數(shù)、日數(shù)，這個函數(shù)比較適用于統(tǒng)計出生函數(shù)作用是計算兩個日期之間相差的年數(shù)、月數(shù)、日數(shù)，這個函數(shù)比較適用于統(tǒng)計出生年月年

21、月語法：語法：DateifDateif( (起始日期，結束日期，參數(shù)類型起始日期，結束日期，參數(shù)類型) )其中參數(shù)類型包括：其中參數(shù)類型包括：“Y” Y” 時間段中的整年數(shù)時間段中的整年數(shù) ，“M” M” 時間段中的整月數(shù)時間段中的整月數(shù)，“D” D” 時間段時間段中中的天數(shù)的天數(shù)舉例：舉例：假定系統(tǒng)當前日期為假定系統(tǒng)當前日期為 2011/11/17 2011/11/17 單元格單元格A1A1中日期為中日期為2000/01/01,2000/01/01,下面例子分別計算起始下面例子分別計算起始日期與當前日期間相差的年數(shù)，月數(shù)，日數(shù)。日期與當前日期間相差的年數(shù)，月數(shù)，日數(shù)。1.1.如果如果B1=D

22、ATEDIF(A1,TODAY(),y),B1=DATEDIF(A1,TODAY(),y),那么那么B1=11B1=112.2.如果如果B1=DATEDIF(A1,TODAY(),“m),B1=DATEDIF(A1,TODAY(),“m),那么那么B1=142B1=1423.3.如果如果B1=DATEDIF(A1,TODAY(),“d),B1=DATEDIF(A1,TODAY(),“d),那么那么B1=4338B1=4338Unit 5 Unit 5 查詢與引用函數(shù)查詢與引用函數(shù)1.1.Vlookup,HlookupVlookup,Hlookup函數(shù)函數(shù)1.1.vlookup,hlookupv

23、lookup,hlookup函數(shù)函數(shù)VlookupVlookup函數(shù)用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行函數(shù)用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。中指定列處的數(shù)值。H Hlookuplookup函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列中指定行處的數(shù)值。中指定行處的數(shù)值。語法：語法：VlookupVlookup( (要查找的值，查找的區(qū)域，要返回列的序號，查找類型要查找的值，查找的區(qū)域，要返回列的序號，查找類型)

24、)其中要注意的問題：其中要注意的問題：1.1.要查找的值一定要位于查找區(qū)域的首列要查找的值一定要位于查找區(qū)域的首列2.2.查找的區(qū)域中不要有隱藏的行或列，否則返回的值會出錯查找的區(qū)域中不要有隱藏的行或列，否則返回的值會出錯3.3.查找類型為查找類型為0 0或或falsefalse（假）時，為精準匹配，當省略或為（假）時，為精準匹配，當省略或為truetrue時，為近似匹時，為近似匹 配（沒有找到要查找的值時，返回最接近于配（沒有找到要查找的值時，返回最接近于“查找值查找值”的值），這里我們常的值），這里我們常 常使用精準匹配，保證查找的準確性。常使用精準匹配，保證查找的準確性。HlookupH

25、lookup函數(shù)語法與函數(shù)語法與VlookupVlookup函數(shù)語法相同。函數(shù)語法相同。1.1.vlookup,hlookupvlookup,hlookup函數(shù)函數(shù)舉例舉例：表表1 1（sheet1sheet1） 表表2 2（sheet2sheet2）如果我們要在表如果我們要在表2 2中根據(jù)姓名導出手工課的成績，可以使用如下函數(shù)中根據(jù)姓名導出手工課的成績，可以使用如下函數(shù) B2=VLOOKUP(A2,B2=VLOOKUP(A2,表表1 1的的$ $A A$2:$2:表表1 1的的$F$5,6,0$F$5,6,0) ), ,填寫參數(shù)時，可以在公式輸入欄里選擇填寫參數(shù)時，可以在公式輸入欄里選擇Vlook