EXCEL常用函數(shù)教程講課

1、ExcelExcel常用函數(shù)應(yīng)用教程常用函數(shù)應(yīng)用教程EXCELEXCEL常用函數(shù)應(yīng)用教程常用函數(shù)應(yīng)用教程EXCELEXCEL函數(shù)的基本知識函數(shù)的基本知識邏輯函數(shù)的應(yīng)用邏輯函數(shù)的應(yīng)用文本函數(shù)的應(yīng)用文本函數(shù)的應(yīng)用日期與時間函數(shù)日期與時間函數(shù)數(shù)學(xué)與統(tǒng)計函數(shù)數(shù)學(xué)與統(tǒng)計函數(shù)查詢與引用函數(shù)查詢與引用函數(shù)數(shù)據(jù)庫函數(shù)的應(yīng)用數(shù)據(jù)庫函數(shù)的應(yīng)用Unit 1 EXCELUnit 1 EXCEL函數(shù)的基本知識函數(shù)的基本知識什么什么是函數(shù)是函數(shù)函數(shù)的參數(shù)和返回值函數(shù)的參數(shù)和返回值函數(shù)的地址符函數(shù)的地址符函數(shù)的套用函數(shù)的套用 Excel Excel中所提的函數(shù)其實是一些預(yù)定義的公式，它們使用一中所提的函數(shù)其實是一些預(yù)定義的

2、公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計算。些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計算。簡單點說，函數(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ù)2. 2. 函數(shù)的參數(shù)及返回值函數(shù)的參數(shù)及返回值1.1.參數(shù)可以是數(shù)字、文本、形如參數(shù)可以是數(shù)字、文本、形如 TRUE TRUE 或或 FALSE FALSE 的邏輯數(shù)值、數(shù)組、的邏輯數(shù)值、

3、數(shù)組、形如形如 #N/A #N/A 的錯誤值或單元格引用。函數(shù)的參數(shù)使用逗號隔開如的錯誤值或單元格引用。函數(shù)的參數(shù)使用逗號隔開如sum(A1,2,B2)sum(A1,2,B2)，其中，其中，A1,2,B2A1,2,B2都是都是sumsum函數(shù)的參數(shù)，用逗號隔開函數(shù)的參數(shù)，用逗號隔開另外要注意的是，函數(shù)使用的參數(shù)所用的符號，如逗號，引號要全部在另外要注意的是，函數(shù)使用的參數(shù)所用的符號，如逗號，引號要全部在英文輸入狀態(tài)下輸入英文輸入狀態(tài)下輸入( (半角符號半角符號) )。數(shù)字：數(shù)字：1 1、2 2、100100、1000010000文本：文本：astronastron、41239878473784

4、7412398784737847（以文本格式存儲的數(shù)字）（以文本格式存儲的數(shù)字）邏輯值：邏輯值：TRUE(TRUE(真真) )或非零（一般用數(shù)字或非零（一般用數(shù)字1 1表示）、表示）、FALSE(FALSE(假假) )或或0 0比如比如13,1313這個不等式的值為假。這個不等式的值為假。單元格：比如單元格：比如A47A47、B16B16數(shù)組：同一種格式存儲的單元格區(qū)域數(shù)組：同一種格式存儲的單元格區(qū)域 2.2.大部分函數(shù)都有一個返回值，返回你通過函數(shù)運算得出的結(jié)果大部分函數(shù)都有一個返回值，返回你通過函數(shù)運算得出的結(jié)果, ,返回值可返回值可以是一個數(shù)字，一個邏輯值，一個地址。另外要注意的是，要想

5、要函數(shù)返以是一個數(shù)字，一個邏輯值，一個地址。另外要注意的是，要想要函數(shù)返回正確的值，使用公式的單元格數(shù)值格式（快捷鍵回正確的值，使用公式的單元格數(shù)值格式（快捷鍵ctrl+1ctrl+1）一定要正確。）一定要正確。舉例：舉例：sum(1,2,3),sum(1,2,3),我們的目的是得出我們的目的是得出1+2+31+2+3相加的和，相加的和，sumsum函數(shù)返回的值是函數(shù)返回的值是6 6另外值得注意的是，對于函數(shù)的返回值如數(shù)值，它等同一個數(shù)值，而不要另外值得注意的是，對于函數(shù)的返回值如數(shù)值，它等同一個數(shù)值，而不要把它認(rèn)為是一個函數(shù)。它可以如其它數(shù)值一樣使用加（把它認(rèn)為是一個函數(shù)。它可以如其它數(shù)值一

6、樣使用加（+ +）、減（）、減（- -）、）、乘（乘（* *）、除（）、除（/ /）。）。如上例中，可以如上例中，可以A1=10-sum(1,2,3)A1=10-sum(1,2,3)，那么，那么A1=4A1=4。在這里，因為。在這里，因為sumsum（）函數(shù)（）函數(shù)的返回值是一個數(shù)值，可以直接把它當(dāng)數(shù)值用。的返回值是一個數(shù)值，可以直接把它當(dāng)數(shù)值用。 2. 2. 函數(shù)的參數(shù)及返回值函數(shù)的參數(shù)及返回值3. 3. 函數(shù)的地址符函數(shù)的地址符在我們使用函數(shù)時，會引用一些地址作為參數(shù)，比如在我們使用函數(shù)時，會引用一些地址作為參數(shù)，比如A47A47、B46B46等等EXCELEXCEL中有專門的地址引用符中

7、有專門的地址引用符$ $。使用是。使用是$ $引用地址時，是絕對引用，引用地址時，是絕對引用，不使用不使用$ $時，是相對引用。時，是相對引用。舉例：舉例：A47A47：相對引用，引用：相對引用，引用A47A47這個單元格。當(dāng)使用這種類型的地址時，這個單元格。當(dāng)使用這種類型的地址時，我們使用拖拉功能將公式引用到其它單元格時，這個相對我們使用拖拉功能將公式引用到其它單元格時，這個相對地址會發(fā)生變化地址會發(fā)生變化 3. 3. 函數(shù)的地址符函數(shù)的地址符$A$47:$A$47:絕對引用。無論將公式引用到哪一個單元格，這個地址不會絕對引用。無論將公式引用到哪一個單元格，這個地址不會改變，永遠(yuǎn)定格在改變，

8、永遠(yuǎn)定格在A47A47這個單元格。這個單元格。$A47$A47和和A$47:A$47:混合引用。只定格單元格的行或列，相對來說有點復(fù)雜，混合引用。只定格單元格的行或列，相對來說有點復(fù)雜，不常用。不常用。另外在引用地址時，冒號（：）在與單元格一起引用時，代表一片區(qū)域，另外在引用地址時，冒號（：）在與單元格一起引用時，代表一片區(qū)域，如如 (A1:A47) (A1:A47)代表的是從代表的是從A1A1一直到一直到A47A47的長條形區(qū)域，總共的長條形區(qū)域，總共4747個單元格，個單元格，如（如（A1:B3)A1:B3)代表以代表以A1A1和和B3B3為對角的一個方形區(qū)域，共為對角的一個方形區(qū)域，共9

9、 9個單元格。個單元格。注：在引用地址符時，我們可以使用注：在引用地址符時，我們可以使用F4F4鍵在這些地址間進(jìn)行切換鍵在這些地址間進(jìn)行切換 4. 4. 函數(shù)的套用函數(shù)的套用函數(shù)的套用，也就是函數(shù)的組合使用，單個函數(shù)已經(jīng)很厲害了，函數(shù)的套用，也就是函數(shù)的組合使用，單個函數(shù)已經(jīng)很厲害了，組合起來的函數(shù)威力會更強(qiáng)大。組合起來的函數(shù)威力會更強(qiáng)大。舉例：舉例：IF(SUM(1,2,3)5,“100”,“1000”)IF(SUM(1,2,3)5,“100”,“1000”)在這個函數(shù)中，邏輯函數(shù)在這個函數(shù)中，邏輯函數(shù)IFIF套用了套用了SUMSUM函數(shù)，它把函數(shù)，它把SUMSUM函數(shù)返回函數(shù)返回的結(jié)果作為

10、參數(shù)，如果的結(jié)果作為參數(shù)，如果1 1，2 2，3 3之和小于之和小于5 5，返回，返回100100，反之，返，反之，返回回1000.1000. Unit 2 Unit 2 邏輯函數(shù)的應(yīng)用邏輯函數(shù)的應(yīng)用IFIF函數(shù)函數(shù)ANDAND函數(shù)函數(shù)OROR函數(shù)函數(shù)NOTNOT函數(shù)函數(shù)1. IF1. IF函數(shù)函數(shù)IFIF在英文里是在英文里是“假如，如果假如，如果”的意思，顧名思義就是如果滿足某個條件（為真）的意思，顧名思義就是如果滿足某個條件（為真）則返回前面的一個值，不滿足條件（為假），返回后面的一個值。則返回前面的一個值，不滿足條件（為假），返回后面的一個值。語法：語法：IF(IF(邏輯值，返回值邏輯值

11、，返回值1 1，返回值，返回值2)2)，其中邏輯值類型為真或假，另外返，其中邏輯值類型為真或假，另外返回值是文本格式時，要用雙引號，如果為數(shù)字，可以不用雙引號?；刂凳俏谋靖袷綍r，要用雙引號，如果為數(shù)字，可以不用雙引號。舉例：如何判斷考試成績是否及格？舉例：如何判斷考試成績是否及格？IF(IF(A1A1 6060,“,“不及格不及格”,“”,“及格及格”)”)，如果單元格如果單元格A1A1中的成績小于中的成績小于6060，則，則返回不及格，反之，返回及格。返回不及格，反之，返回及格。IF(A160 IF(A184,“IF(A184,“優(yōu)秀優(yōu)秀”,“,“及格及格”)”)，這是，這是IFIF函數(shù)函數(shù)

12、套用套用IFIF函數(shù)，如果函數(shù)，如果A1A1小于小于6060，返回不及格，另外如果大于等于，返回不及格，另外如果大于等于8585，則返回，則返回優(yōu)秀，否則返回及格。優(yōu)秀，否則返回及格。2. AND2. AND函數(shù)函數(shù)ANDAND在英文里是在英文里是“和，與和，與”的意思的意思, ,只有當(dāng)所有條件都滿足時，才返回只有當(dāng)所有條件都滿足時，才返回ture(ture(真真) )，否則返回，否則返回false(false(假假),),一般要與其它函數(shù)套用，比如一般要與其它函數(shù)套用，比如IFIF函數(shù)。函數(shù)。語法：語法：AND(AND(條件條件1 1，條件，條件2 2，條件條件30)30)返回值：返回值：T

13、URETURE或或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. OR函數(shù)函數(shù)OROR在英文里是在英文里是“或，或者或，或者”的意思，只要滿足任一條件就的意思，只要滿足任一條件就返回返回ture(ture(真真

14、) )，否則返回，否則返回false(false(假假),),一般要與其它函數(shù)套一般要與其它函數(shù)套用，比如用，比如IFIF函數(shù)。函數(shù)。語法：語法：OR(OR(條件條件1 1，條件，條件2 2，條件條件30)30)舉例：舉例：oror(A160,B280),(A160,B280),當(dāng)當(dāng)A160,B280A160,B280這三個條件有一個滿足時，返回這三個條件有一個滿足時，返回TRUE,TRUE,否則否則FALSE.FALSE.4. not4. not函數(shù)函數(shù)notnot在英文里是在英文里是“ 不，非不，非”的意思，返回相反的值一般要的意思，返回相反的值一般要與其它函數(shù)套用，比如與其它函數(shù)套用，比

15、如IFIF函數(shù)。函數(shù)。語法：語法：not(not(條件條件) )舉例：舉例：not(A160),not(A160),當(dāng)當(dāng)A160A160時，返回時，返回FALSE,FALSE,否則否則TRUE.TRUE.Unit 3 Unit 3 文本函數(shù)的應(yīng)用文本函數(shù)的應(yīng)用比較函數(shù)比較函數(shù)excatexcat與等號與等號= =聯(lián)結(jié)函數(shù)聯(lián)結(jié)函數(shù)concatenateconcatenate與聯(lián)結(jié)號與聯(lián)結(jié)號& &LowerLower、upperupper、properproper、trimtrim函數(shù)函數(shù)leftleft、rightright、midmid函數(shù)函數(shù)1.1.比較函數(shù)比較函數(shù)excat

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

17、”，“不相同不相同”) )。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)結(jié)函數(shù)聯(lián)結(jié)函數(shù)concatenateconcatenate與聯(lián)結(jié)號與聯(lián)結(jié)號& &concatenateconcatenate是一個聯(lián)結(jié)函數(shù)，聯(lián)結(jié)號是一個聯(lián)結(jié)函數(shù)，聯(lián)結(jié)號& &與之有相同的功與之有相同的功能。能

18、。語法：語法：concatenate(concatenate(字符字符1,1,字符字符2 2，)，單獨的文本要，單獨的文本要使用雙引號使用雙引號舉例：舉例：1.concatenate(A1,B11.concatenate(A1,B1，“tron”),tron”),如果如果A1=AA1=A，B1=sB1=s，那么函數(shù)就會返回那么函數(shù)就會返回AstronAstron2.2. 如果如果C1=A1&B1&“tronC1=A1&B1&“tron”，那么，那么C1=AstronC1=Astron3. Lower3. Lower、upperupper、properproper

19、、trimtrim函數(shù)函數(shù)LowerLower函數(shù)是將文本中的字母轉(zhuǎn)換成小寫字母。函數(shù)是將文本中的字母轉(zhuǎn)換成小寫字母。UpperUpper函數(shù)是將文本中的字母轉(zhuǎn)換成大寫字母函數(shù)是將文本中的字母轉(zhuǎn)換成大寫字母ProperProper函數(shù)是將文本中的字符串首字母轉(zhuǎn)換成大寫字母，其余函數(shù)是將文本中的字符串首字母轉(zhuǎn)換成大寫字母，其余字符轉(zhuǎn)換成小寫字母。字符轉(zhuǎn)換成小寫字母。TrimTrim函數(shù)是去除字符串中多余空白字符。函數(shù)是去除字符串中多余空白字符。語法：語法： XXXX( XXXX(字符串），其中字符串），其中XXXXXXXX代表以上所有函數(shù)，它們的代表以上所有函數(shù)，它們的語法規(guī)則一樣，字符串代表內(nèi)

20、容是字符的地址引用比如語法規(guī)則一樣，字符串代表內(nèi)容是字符的地址引用比如A1,B2,A1,B2,也可以是單獨的字符串（要使用雙引號如也可以是單獨的字符串（要使用雙引號如“astron”astron”）3. Lower3. Lower、upperupper、properproper、trimtrim函數(shù)函數(shù)舉例：舉例：假如假如A1=ASTRON,B1=astron,C1=dongguan astron,A1=ASTRON,B1=astron,C1=dongguan astron,D1=DongGuan AstronD1=DongGuan Astronlower(A1)=astronlower(A1

21、)=astronUpper(B1)=ASTRONUpper(B1)=ASTRONProper(C1)=Dongguan AstronProper(C1)=Dongguan AstronTrim(D1)=DongGuan AstronTrim(D1)=DongGuan Astron4.left4.left、rightright、midmid函數(shù)函數(shù)LeftLeft函數(shù)是自字符串左邊開始提取字符串中的字符函數(shù)是自字符串左邊開始提取字符串中的字符RightRight函數(shù)是自字符串左邊開始提取字符串中的字符函數(shù)是自字符串左邊開始提取字符串中的字符MidMid函數(shù)是從字符串指定位置開始提取字符串中的字符

22、函數(shù)是從字符串指定位置開始提取字符串中的字符語法：語法：1.Left(1.Left(字符串，數(shù)值字符串，數(shù)值) )，其中數(shù)值為要提取的字符數(shù)，其中數(shù)值為要提取的字符數(shù)2.Right(2.Right(字符串，數(shù)值字符串，數(shù)值) )，其中數(shù)值為要提取的字符數(shù)，其中數(shù)值為要提取的字符數(shù)3.Mid(3.Mid(字符串，數(shù)值字符串，數(shù)值1 1，數(shù)值，數(shù)值2)2)，其中數(shù)值，其中數(shù)值1 1代表要提取代表要提取 的第一個字符的位置，數(shù)值的第一個字符的位置，數(shù)值2 2代表要提取的字符數(shù)。代表要提取的字符數(shù)。4. left4. left、rightright、midmid函數(shù)函數(shù)舉例：舉例：假如假如A1=Ast

23、ronA1=AstronLeft(A1,2)=AsLeft(A1,2)=AsRight(A1,2)=onRight(A1,2)=onMid(A1,2,2)=stMid(A1,2,2)=stUnit 4Unit 4 日期與時間函數(shù)日期與時間函數(shù)nownow、todaytoday函數(shù)函數(shù)yearyear、monthmonth、dayday、hourhour函數(shù)函數(shù)dateifdateif函數(shù)函數(shù)1. now1. now、todaytoday函數(shù)函數(shù)nownow函數(shù)是提取當(dāng)前系統(tǒng)日期和時間函數(shù)是提取當(dāng)前系統(tǒng)日期和時間TodayToday函數(shù)是提取當(dāng)前系統(tǒng)日期函數(shù)是提取當(dāng)前系統(tǒng)日期語法：語法：Now(

24、)Now()參數(shù)為空參數(shù)為空Today()Today()參數(shù)為空參數(shù)為空舉例：舉例：假定系統(tǒng)當(dāng)前時間為假定系統(tǒng)當(dāng)前時間為 2013/03/30 9:482013/03/30 9:481.1.如果如果A1=now(),A1=now(),那么那么A1= 2013/03/30 9:48A1= 2013/03/30 9:482.2.如果如果B1=today(),B1=today(),那么那么B1= 2013/03/30B1= 2013/03/302. year2. year、monthmonth、dayday、hourhour函數(shù)函數(shù)yearyear函數(shù)作用是提取日期中的年份函數(shù)作用是提取日期中的年份

25、 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)當(dāng)前時間為假定系統(tǒng)當(dāng)前時間為 A1=2011/11/17 9:58A1=2011/11/17 9:581.1. B1=year(A1),B1=year(A1),那么那么

26、B1= 2011B1= 20112.2. C1=month(A1),C1=month(A1),那么那么C1=11C1=113. D1=day(A1)3. D1=day(A1)，那么，那么D1=17D1=174. E1=hour(A1),4. E1=hour(A1),那么那么E1=9E1=93. dateif3. dateif函數(shù)函數(shù)dateifdateif函數(shù)作用是計算兩個日期之間相差的年數(shù)、月數(shù)、日數(shù)，這個函數(shù)比較適用于統(tǒng)計出生函數(shù)作用是計算兩個日期之間相差的年數(shù)、月數(shù)、日數(shù)，這個函數(shù)比較適用于統(tǒng)計出生年月年月語法：語法：Dateif(Dateif(起始日期，結(jié)束日期，參數(shù)類型起始日期，結(jié)束

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

28、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 Unit 5 5 數(shù)學(xué)與統(tǒng)計函數(shù)數(shù)學(xué)與統(tǒng)計函數(shù)SumSum、averageaverage、absabs、modmod函數(shù)函數(shù)sumifsumif、countifcountif函數(shù)函數(shù)maxmax、minmin、smallsmall、largelar

29、ge、rankrank函數(shù)函數(shù)valuevalue、isoddisodd、iseveniseven函數(shù)函數(shù)1.Sum1.Sum、averageaverage、absabs、modmod、powerpower函數(shù)函數(shù)SumSum函數(shù)作用是求和函數(shù)作用是求和 AverageAverage函數(shù)作用是求平均數(shù)函數(shù)作用是求平均數(shù)AbsAbs函數(shù)作用是求絕對值函數(shù)作用是求絕對值 ModMod函數(shù)作用是求余函數(shù)作用是求余 PowerPower函數(shù)作用是求函數(shù)作用是求n n次方次方語法：語法：Sum(Sum(數(shù)值數(shù)值1 1，數(shù)值，數(shù)值2)2) 其中數(shù)值可以直接是數(shù)字，也可以是包含數(shù)字的其中數(shù)值可以直接是數(shù)字，

30、也可以是包含數(shù)字的單元格，也可以是一片單元格區(qū)域。單元格，也可以是一片單元格區(qū)域。AverageAverage語法規(guī)則同上語法規(guī)則同上AbsAbs（數(shù)值）（數(shù)值）Mod(Mod(數(shù)值數(shù)值1 1，數(shù)值，數(shù)值2)2)，其中數(shù)值，其中數(shù)值1 1是被除數(shù)，數(shù)值是被除數(shù)，數(shù)值2 2是除數(shù)是除數(shù)Power(Power(數(shù)值數(shù)值1 1，數(shù)值，數(shù)值2)2)，其中數(shù)值，其中數(shù)值1 1是數(shù)，數(shù)值是數(shù)，數(shù)值2 2是多少次方是多少次方以上函數(shù)返回值皆為數(shù)字以上函數(shù)返回值皆為數(shù)字1.Sum1.Sum、averageaverage、absabs、modmod函數(shù)函數(shù)舉例：舉例：假設(shè)假設(shè) A1=1A1=1，B1=2,C1=

31、-2B1=2,C1=-21.sum(A1,B1)=31.sum(A1,B1)=32.average(A1,B1)=1.52.average(A1,B1)=1.53.abs(A1)=1,abs(C1)=23.abs(A1)=1,abs(C1)=24.mod(B1,A1)=0,mod(A1,B1)=14.mod(B1,A1)=0,mod(A1,B1)=15.power(B1,2)=4,5.power(B1,2)=4,另外另外 （數(shù)字（數(shù)字6 6上面的符號）也有這個功能，比如上面的符號）也有這個功能，比如B13=8B13=8另外要注意的是，想要減或者求負(fù)數(shù)，直接在單元格前加另外要注意的是，想要減或者

32、求負(fù)數(shù)，直接在單元格前加- -號就可以了號就可以了比如上面例子中比如上面例子中sum(A1,-B1)=-1sum(A1,-B1)=-12.sumif2.sumif、countifcountif函數(shù)函數(shù)SumifSumif函數(shù)作用是對區(qū)域內(nèi)符合條件的單元格求和函數(shù)作用是對區(qū)域內(nèi)符合條件的單元格求和CountifCountif函數(shù)作用是統(tǒng)計區(qū)域內(nèi)符合條件的單元格的數(shù)量函數(shù)作用是統(tǒng)計區(qū)域內(nèi)符合條件的單元格的數(shù)量語法：語法：Sumif(Sumif(條件區(qū)域，條件，要計算和的區(qū)域條件區(qū)域，條件，要計算和的區(qū)域) )Countif(Countif(區(qū)域，條件區(qū)域，條件) )舉例：舉例：2.sumif2.s

33、umif、countifcountif函數(shù)函數(shù)假設(shè)有如下表格假設(shè)有如下表格1.1.假如我們要計算假如我們要計算1 1班所有人的手工課的總分，可以使用如下函數(shù)班所有人的手工課的總分，可以使用如下函數(shù) SUMIF(A2:A5,“1”,G2:G5), SUMIF(A2:A5,“1”,G2:G5),在函數(shù)里面，前一區(qū)域在函數(shù)里面，前一區(qū)域A2:A5A2:A5代表查尋條件代表查尋條件“1”1”的查找區(qū)的查找區(qū) 域，中間的域，中間的“1”1”表示查找的條件表示查找的條件( (要使用雙引號要使用雙引號) )，后一區(qū)域，后一區(qū)域G2:G5G2:G5代表要加總的數(shù)字代表要加總的數(shù)字 區(qū)域區(qū)域2.2.假如我們要統(tǒng)

34、計手工課大于假如我們要統(tǒng)計手工課大于9090分的人數(shù)，可以使用分的人數(shù)，可以使用countif(G2:G5,countif(G2:G5,”90”90”) ), ,前面代表前面代表 要查找的區(qū)域，后面表示查找條件，記住要使用雙引號要查找的區(qū)域，后面表示查找條件，記住要使用雙引號3. max3. max、minmin、smallsmall、largelarge、rankrank函數(shù)函數(shù)MaMax x求一組數(shù)據(jù)的最大值求一組數(shù)據(jù)的最大值 MinMin求一組數(shù)據(jù)的最小值求一組數(shù)據(jù)的最小值SmallSmall求一組數(shù)據(jù)的第求一組數(shù)據(jù)的第N N小的值小的值 LargeLarge求一組數(shù)據(jù)的第求一組數(shù)據(jù)的第

35、N N大的值大的值RankRank作用是將一組數(shù)組排序作用是將一組數(shù)組排序語法：語法：Max(Max(數(shù)字?jǐn)?shù)字1 1，數(shù)字?jǐn)?shù)字n)n)，參數(shù)可以是單獨的數(shù)字，也可以是包含數(shù)字，參數(shù)可以是單獨的數(shù)字，也可以是包含數(shù)字的單元格。的單元格。MINMIN函數(shù)語法同上。函數(shù)語法同上。Small(Small(單元格區(qū)域，數(shù)字單元格區(qū)域，數(shù)字N)N)，其中前者為包含數(shù)字的單元格區(qū)域，后，其中前者為包含數(shù)字的單元格區(qū)域，后者為要查找的第者為要查找的第N N小的數(shù)字。小的數(shù)字。3. max3. max、minmin、smallsmall、largelarge、rankrank函數(shù)函數(shù)large(large(單元

36、格區(qū)域，數(shù)字單元格區(qū)域，數(shù)字N)N)，其中前者為包含數(shù)字的單元格區(qū)域，后者為要查，其中前者為包含數(shù)字的單元格區(qū)域，后者為要查找的第找的第N N大的數(shù)字。大的數(shù)字。Rank(Rank(需排序的數(shù)字，需排序的數(shù)組，排序類型需排序的數(shù)字，需排序的數(shù)組，排序類型) )，其中排序類型為，其中排序類型為0 0時，按降時，按降序排列，不為序排列，不為0 0時，按升序排列。時，按升序排列。舉例：舉例：假設(shè)假設(shè)A1,A2,A3,A4,A5A1,A2,A3,A4,A5分別為分別為1212，9 9，7 7，7 7，2121Max(A1:A5)=21Max(A1:A5)=21 Min(A1:A5)=7Min(A1:A

37、5)=7Small(A1:A5,3)=9Small(A1:A5,3)=9 Large(A1:A5,5)=7Large(A1:A5,5)=73. max3. max、minmin、smallsmall、largelarge、rankrank函數(shù)函數(shù)舉例舉例 假設(shè)假設(shè)A1,A2,A3,A4,A5A1,A2,A3,A4,A5分別為分別為1212，9 9，7 7，7 7，2121, , 使用使用RankRank（）函數(shù)（）函數(shù)對這個數(shù)組進(jìn)行排序，按降序?qū)@個數(shù)組進(jìn)行排序，按降序( (從大到小從大到小) )排列排列, ,其中其中B B列是排序后的序號列是排序后的序號B1=RANK(A1,$A$1:$A$

38、5,0),B1=RANK(A1,$A$1:$A$5,0),其中其中A1A1表示要排序的數(shù)字，表示要排序的數(shù)字，A1:A5A1:A5表示排序表示排序的數(shù)組，的數(shù)組，0 0表示按降序排列，另外要注意的是，引用排序的數(shù)組時，我表示按降序排列，另外要注意的是，引用排序的數(shù)組時，我們用了絕對地址引用們用了絕對地址引用$A$1:$A$5($A$1:$A$5(這個可以用這個可以用F4F4鍵在地圵符間進(jìn)行切換鍵在地圵符間進(jìn)行切換),),這樣我們在引用公式到這樣我們在引用公式到B2,B3B2,B3單元格時，這個地址區(qū)域不會變。單元格時，這個地址區(qū)域不會變。4.value4.value函數(shù)函數(shù)ValueValue

39、函數(shù)作用是將文本類型的數(shù)字轉(zhuǎn)換成數(shù)字類型，常用于出生日期，函數(shù)作用是將文本類型的數(shù)字轉(zhuǎn)換成數(shù)字類型，常用于出生日期，銀行卡等數(shù)據(jù)類型的轉(zhuǎn)換。銀行卡等數(shù)據(jù)類型的轉(zhuǎn)換。語法：語法：Value(Value(文本文本) )，其中文本必須為數(shù)字，只是存儲類型為文本格式，其中文本必須為數(shù)字，只是存儲類型為文本格式舉例：舉例： 假設(shè)假設(shè)A1=20111121A1=20111121，A2=5A2=5如果如果B1=2011-VALUE(MID(A1,3,2)B1=2011-VALUE(MID(A1,3,2)，那么，那么B1=2000.B1=2000.我們知道，我們知道，MIDMID函數(shù)函數(shù)的作用是取文本中的字符

40、（第三章第的作用是取文本中的字符（第三章第4 4小節(jié)），在這里，我們從小節(jié)），在這里，我們從A1A1單元單元格的第格的第3 3個字符開始取，取個字符開始取，取2 2個字符，得到字符個字符，得到字符1111，然后將用，然后將用valuevalue函數(shù)函數(shù)將其轉(zhuǎn)換成數(shù)字類型將其轉(zhuǎn)換成數(shù)字類型1111。最后與。最后與20112011相減得相減得20002000Unit 6 Unit 6 查詢與引用函數(shù)查詢與引用函數(shù)Vlookup,hlookupVlookup,hlookup函數(shù)函數(shù)Match,indexMatch,index函數(shù)函數(shù)1.1.vlookup,hlookupvlookup,hlookup

41、函數(shù)函數(shù)VlookupVlookup函數(shù)用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行函數(shù)用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。中指定列處的數(shù)值。H Hlookuplookup函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。中指定行處的數(shù)值。語法：語法：Vlookup(Vlookup(要查找的值，查找的區(qū)域，要返回列的序號，查找類型要查找的值，查找的區(qū)域，要返回列的序號，查找類型) )其中要注意的問題：其中要注意

42、的問題：1.1.要查找的值一定要位于查找區(qū)域的首列要查找的值一定要位于查找區(qū)域的首列2.2.查找的區(qū)域中不要有隱藏的行或列，否則返回的值會出錯查找的區(qū)域中不要有隱藏的行或列，否則返回的值會出錯3.3.查找類型為查找類型為0 0或或falsefalse（假）時，為精準(zhǔn)匹配，當(dāng)省略或為（假）時，為精準(zhǔn)匹配，當(dāng)省略或為truetrue時，為近似匹時，為近似匹 配（沒有找到要查找的值時，返回最接近于配（沒有找到要查找的值時，返回最接近于“查找值查找值”的值），這里我們常的值），這里我們常 常使用精準(zhǔn)匹配，保證查找的準(zhǔn)確性。常使用精準(zhǔn)匹配，保證查找的準(zhǔn)確性。HlookupHlookup函數(shù)語法與函數(shù)語法

43、與VlookupVlookup函數(shù)語法相同。函數(shù)語法相同。1.1.vlookup,hlookupvlookup,hlookup函數(shù)函數(shù)舉例：舉例：表表1 1（sheet1sheet1） 表表2 2（sheet2sheet2）如果我們要在表如果我們要在表2 2中根據(jù)姓名導(dǎo)出手工課的成績，可以使用如下函數(shù)中根據(jù)姓名導(dǎo)出手工課的成績，可以使用如下函數(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ù)時，可以在公式輸入欄里選擇VlookupVlookup函數(shù)

44、后直接用鼠函數(shù)后直接用鼠標(biāo)點擊輸入標(biāo)點擊輸入1.1.第一個參數(shù)代表我們要查找的值（根據(jù)姓名查找手工成績）要注意的是，查找值有相同的（有同名第一個參數(shù)代表我們要查找的值（根據(jù)姓名查找手工成績）要注意的是，查找值有相同的（有同名 的），返回第一個值所對應(yīng)的返回值的），返回第一個值所對應(yīng)的返回值2.2.第二個參數(shù)代表包含查找值的查找區(qū)域，這個查找區(qū)域可以在其他工作表上，也可以在同一張工作表上第二個參數(shù)代表包含查找值的查找區(qū)域，這個查找區(qū)域可以在其他工作表上，也可以在同一張工作表上 , ,在本例中我們的查找區(qū)域位于工作表一。查找區(qū)域使用絕對引用在本例中我們的查找區(qū)域位于工作表一。查找區(qū)域使用絕對引用$

45、,$,固定的查找區(qū)域我們要使用絕對引固定的查找區(qū)域我們要使用絕對引 用，不然將公式引用到其它單元格時，查找區(qū)域也會變動，。另外在上面例子中我們的查找值位于查用，不然將公式引用到其它單元格時，查找區(qū)域也會變動，。另外在上面例子中我們的查找值位于查 找區(qū)域的第一列（必須的）找區(qū)域的第一列（必須的）1.1.vlookup,hlookupvlookup,hlookup函數(shù)函數(shù)舉例：舉例：B2=VLOOKUP(A2,B2=VLOOKUP(A2,表表1 1的的$ $A A$2:$2:表表1 1的的$F$5,6,0$F$5,6,0) ), ,3.3.第三個參數(shù)第三個參數(shù)6 6代表我們要返回的值的列號（手工成

46、績位于第代表我們要返回的值的列號（手工成績位于第6 6列）列）4.4.第四個參數(shù)第四個參數(shù)0 0代表包含查找類型為精準(zhǔn)查找，一般我們要使用精準(zhǔn)查代表包含查找類型為精準(zhǔn)查找，一般我們要使用精準(zhǔn)查 找，保證查找值的準(zhǔn)確性找，保證查找值的準(zhǔn)確性HlookupHlookup函數(shù)使用與函數(shù)使用與VlookupVlookup函數(shù)相似，只是將行列互換而已。具體區(qū)別大函數(shù)相似，只是將行列互換而已。具體區(qū)別大家可以自己體驗一下。家可以自己體驗一下。2 2.Match,index.Match,index函數(shù)函數(shù)MatchMatch函數(shù)查找某值在數(shù)組中的位置。函數(shù)查找某值在數(shù)組中的位置。IndexIndex函數(shù)作用

47、是返回數(shù)組中某行某列的值函數(shù)作用是返回數(shù)組中某行某列的值語法：語法：1.Match(1.Match(要查找的值，數(shù)組，查找類型要查找的值，數(shù)組，查找類型) )，其中要查找的值可以是單獨輸入的值，其中要查找的值可以是單獨輸入的值 （要使用雙引號），也可以是包含查找值的單元格。數(shù)組包含查找值的一組單元（要使用雙引號），也可以是包含查找值的單元格。數(shù)組包含查找值的一組單元 格。查找類型包含格。查找類型包含3 3種，為種，為-1-1時時, ,參數(shù)中的數(shù)組須降序排列；為參數(shù)中的數(shù)組須降序排列；為1 1時，數(shù)組須升序時，數(shù)組須升序 排列；為排列；為0 0時，可以是無序排列。時，可以是無序排列。2.inde

48、x(2.index(數(shù)組，行，列數(shù)組，行，列) ), ,其中的行列是數(shù)組的相對的行列值，不是工作表的行列其中的行列是數(shù)組的相對的行列值，不是工作表的行列2 2.Match,index.Match,index函數(shù)函數(shù)舉例：舉例：在上面的數(shù)組中在上面的數(shù)組中1.1.A8A8=MATCH(4,A2:A6,0)=MATCH(4,A2:A6,0), ,其中，第一個參數(shù)其中，第一個參數(shù)4 4代表我們要在數(shù)組（代表我們要在數(shù)組（A2:A4A2:A4） 中查找中查找4 4這個值這個值, ,最后一個參數(shù)最后一個參數(shù)0 0代表我們查找的數(shù)組是無序排列。代表我們查找的數(shù)組是無序排列。2.B8=INDEX(A2:B6

49、,3,2)2.B8=INDEX(A2:B6,3,2)，其中，第，其中，第1 1個參數(shù)表示數(shù)組的區(qū)域，第個參數(shù)表示數(shù)組的區(qū)域，第2 2，3 3個參個參 數(shù)表示這個查找區(qū)域的行和列。數(shù)表示這個查找區(qū)域的行和列。Unit 7 Unit 7 數(shù)據(jù)庫函數(shù)的應(yīng)用數(shù)據(jù)庫函數(shù)的應(yīng)用Dget,DcountDget,Dcount函數(shù)函數(shù)Dmax,Dmin,Dsum,DaverageDmax,Dmin,Dsum,Daverage函數(shù)函數(shù)1. Dget,Dcount1. Dget,Dcount函數(shù)函數(shù)DD函數(shù)是數(shù)據(jù)庫類型函數(shù)，與其它類型函數(shù)不同的是前面多了一個字函數(shù)是數(shù)據(jù)庫類型函數(shù)，與其它類型函數(shù)不同的是前面多了一個

50、字母母D D（數(shù)據(jù)庫（數(shù)據(jù)庫databasedatabase的首字母），數(shù)據(jù)庫函數(shù)的使用功能更強(qiáng)大，可的首字母），數(shù)據(jù)庫函數(shù)的使用功能更強(qiáng)大，可以在查找引用中使用更加復(fù)雜的查找關(guān)系。以在查找引用中使用更加復(fù)雜的查找關(guān)系。1.Dget1.Dget函數(shù)提取滿足條件的單個記錄，有多個值符合條件時返回錯誤函數(shù)提取滿足條件的單個記錄，有多個值符合條件時返回錯誤2.Dcount2.Dcount函數(shù)返回滿足條件的單元格的個數(shù)函數(shù)返回滿足條件的單元格的個數(shù)語法：語法：DD函數(shù)的語法格式都一樣函數(shù)的語法格式都一樣DD（數(shù)據(jù)庫區(qū)域，函數(shù)要使用的數(shù)據(jù)列，查找條件），其中第一個參數(shù)表示構(gòu)成數(shù)據(jù)庫（數(shù)據(jù)庫區(qū)域，函數(shù)要使

51、用的數(shù)據(jù)列，查找條件），其中第一個參數(shù)表示構(gòu)成數(shù)據(jù)庫的單元格區(qū)域（如下例中的的單元格區(qū)域（如下例中的A3:F10A3:F10），第二個參數(shù)表示函數(shù)要使用的數(shù)據(jù)列，可以是用），第二個參數(shù)表示函數(shù)要使用的數(shù)據(jù)列，可以是用雙引號引用的文本，也可以是列號（如下例中的雙引號引用的文本，也可以是列號（如下例中的“語文語文”，1 1），第三個參數(shù)代表包含給），第三個參數(shù)代表包含給定條件的單元格區(qū)域，它至少包含一個列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格（如定條件的單元格區(qū)域，它至少包含一個列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格（如下例中的下例中的B1:B2B1:B2，B1:C2B1:C2）。）。1. Dget,Dcount1. Dget,Dcount函數(shù)函數(shù)舉例：舉例：1.Dcount()1.Dcount()，在上表中，如果我們想要統(tǒng)計語文，在上表中，如果我們想要統(tǒng)計語文8989的人數(shù)數(shù)量，可以在的人數(shù)數(shù)量，可以在B1B1中設(shè)置列標(biāo)志中設(shè)置列標(biāo)志（語文），在（語文），在B2B2中設(shè)置給定條件（中設(shè)置給定條件（8989）, ,在這里我們在在這里我們在A12A12單元格求結(jié)果單元格求結(jié)果 A12=DCOUNT(A3:F10