excel常用函數教程講課

1、Excel常用函數應用教程,EXCEL常用函數應用教程,EXCEL函數的基本知識邏輯函數的應用文本函數的應用日期與時間函數數學與統(tǒng)計函數查詢與引用函數數據庫函數的應用,Unit 1 EXCEL函數的基本知識,什么是函數函數的參數和返回值函數的地址符函數的套用,,Excel中所提的函數其實是一些預定義的公式，它們使用一些稱為參數的特定數值按特定的順序或結構進行計算。簡單點說，函數是一組功能模塊，使用函數能幫助你實

2、現(xiàn)某個功能。舉例：Sum函數可以實現(xiàn)數值的加法運算，max函數可以返回一組數的最大值。,,1.什么是函數,2. 函數的參數及返回值,1.參數可以是數字、文本、形如 TRUE 或 FALSE 的邏輯數值、數組、形如 #N/A 的錯誤值或單元格引用。函數的參數使用逗號隔開如sum(A1,2,B2)，其中，A1,2,B2都是sum函數的參數，用逗號隔開另外要注意的是，函數使用的參數所用的符號，如逗號，引號要全部在英文輸入狀態(tài)下輸入

3、(半角符號)。數字：1、2、100、10000文本：astron、412398784737847（以文本格式存儲的數字）邏輯值：TRUE(真)或非零（一般用數字1表示）、FALSE(假)或0比如13這個不等式的值為假。單元格：比如A47、B16數組：同一種格式存儲的單元格區(qū)域,,2.大部分函數都有一個返回值，返回你通過函數運算得出的結果,返回值可以是一個數字，一個邏輯值，一個地址。另外要注意的是，要想要函數返回正確的值

4、，使用公式的單元格數值格式（快捷鍵ctrl+1）一定要正確。舉例：sum(1,2,3),我們的目的是得出1+2+3相加的和，sum函數返回的值是6另外值得注意的是，對于函數的返回值如數值，它等同一個數值，而不要把它認為是一個函數。它可以如其它數值一樣使用加（+）、減（-）、乘（*）、除（/）。如上例中，可以A1=10-sum(1,2,3)，那么A1=4。在這里，因為sum（）函數的返回值是一個數值，可以直接把它當數值用。,

5、,2. 函數的參數及返回值,3. 函數的地址符,在我們使用函數時，會引用一些地址作為參數，比如A47、B46等EXCEL中有專門的地址引用符$。使用是$引用地址時，是絕對引用，不使用$時，是相對引用。舉例：A47：相對引用，引用A47這個單元格。當使用這種類型的地址時，我們使用拖拉功能將公式引用到其它單元格時，這個相對地址會發(fā)生變化,,3. 函數的地址符,$A$47:絕對引用。無論將公式引用到哪一個單元格，這個地址不會

6、改變，永遠定格在A47這個單元格。$A47和A$47:混合引用。只定格單元格的行或列，相對來說有點復雜，不常用。另外在引用地址時，冒號（：）在與單元格一起引用時，代表一片區(qū)域，如 (A1:A47)代表的是從A1一直到A47的長條形區(qū)域，總共47個單元格，如（A1:B3)代表以A1和B3為對角的一個方形區(qū)域，共9個單元格。注：在引用地址符時，我們可以使用F4鍵在這些地址間進行切換,,4. 函數的套用,函數的套用，也就是函數

7、的組合使用，單個函數已經很厲害了，組合起來的函數威力會更強大。舉例：IF(SUM(1,2,3)<5,“100”,“1000”)在這個函數中，邏輯函數IF套用了SUM函數，它把SUM函數返回的結果作為參數，如果1，2，3之和小于5，返回100，反之，返回1000.,,Unit 2 邏輯函數的應用,IF函數AND函數OR函數NOT函數,,1. IF函數,IF在英文里是“假如，如果”的意思，顧名思義就是如果滿足某個條件

8、（為真）則返回前面的一個值，不滿足條件（為假），返回后面的一個值。語法：IF(邏輯值，返回值1，返回值2)，其中邏輯值類型為真或假，另外返回值是文本格式時，要用雙引號，如果為數字，可以不用雙引號。舉例：如何判斷考試成績是否及格？IF(A184,“優(yōu)秀”,“及格”))，這是IF函數套用IF函數，如果A1小于60，返回不及格，另外如果大于等于85，則返回優(yōu)秀，否則返回及格。,,2. AND函數,AND在英文里是“和，與”的意思

9、,只有當所有條件都滿足時，才返回ture(真)，否則返回false(假),一般要與其它函數套用，比如IF函數。語法：AND(條件1，條件2，……條件30)返回值：TURE或FALSE舉例：1.and(A1>60,B280),只有A1>60,B280這三個條件都滿足時，才返回TRUE,否則FALSE.2.IF（and(A1>60,B280),1，2），三個條件都滿足時，返回1，否則返回2.,,3. OR函

10、數,OR在英文里是“或，或者”的意思，只要滿足任一條件就返回ture(真)，否則返回false(假),一般要與其它函數套用，比如IF函數。語法：OR(條件1，條件2，……條件30)舉例：or(A1>60,B280),當A1>60,B280這三個條件有一個滿足時，返回TRUE,否則FALSE.,,4. not函數,not在英文里是“ 不，非”的意思，返回相反的值一般要與其它函數套用，比如IF函數。語法：not(

11、條件)舉例：not(A1>60),當A1>60時，返回FALSE,否則TRUE.,,Unit 3 文本函數的應用,比較函數excat與等號=聯(lián)結函數concatenate與聯(lián)結號&Lower、upper、proper、trim函數left、right、mid函數,,1.比較函數excat與等號=,Excat是一個比較函數，等號=與之有相同的功能。返回值為邏輯值，對于返回值為邏輯值的函數，一般與if等函

12、數套用。語法：excat(A1,B1)舉例：1.excat(A1,B1),當A1與B1相同時，返回TRUE,否則FALSE. 與if函數套用，if(excat(A1,B1),”相同”，“不相同”)。2.使用等號比較時，如單元格C1=（A1=B1），如果A1與B1 相同，C1=TRUE,否則C1=FALSE 與if函數套用，if(A1=B1,“相同”，“不相同”)。,,2.聯(lián)結函數concatenate與聯(lián)結號&,

13、concatenate是一個聯(lián)結函數，聯(lián)結號&與之有相同的功能。語法：concatenate(字符1,字符2，……)，單獨的文本要使用雙引號舉例：1.concatenate(A1,B1，“tron”),如果A1=A，B1=s，那么函數就會返回Astron2. 如果C1=A1&B1&“tron”，那么C1=Astron,,3. Lower、upper、proper、trim函數,Lower函數是將文本

14、中的字母轉換成小寫字母。Upper函數是將文本中的字母轉換成大寫字母Proper函數是將文本中的字符串首字母轉換成大寫字母，其余字符轉換成小寫字母。Trim函數是去除字符串中多余空白字符。語法： XXXX(字符串），其中XXXX代表以上所有函數，它們的語法規(guī)則一樣，字符串代表內容是字符的地址引用比如A1,B2,也可以是單獨的字符串（要使用雙引號如“astron”）,,3. Lower、upper、proper、trim函數,舉例

15、：假如A1=ASTRON,B1=astron,C1=dongguan astron,D1=DongGuan Astronlower(A1)=astronUpper(B1)=ASTRONProper(C1)=Dongguan AstronTrim(D1)=DongGuan Astron,,4.left、right、mid函數,Left函數是自字符串左邊開始提取字符串中的字符Right函數是自字符串左邊開始提取字符串中的

16、字符Mid函數是從字符串指定位置開始提取字符串中的字符語法：1.Left(字符串，數值)，其中數值為要提取的字符數2.Right(字符串，數值)，其中數值為要提取的字符數3.Mid(字符串，數值1，數值2)，其中數值1代表要提取 的第一個字符的位置，數值2代表要提取的字符數。,,4. left、right、mid函數,舉例：假如A1=AstronLeft(A1,2)=AsRight(A1,2)=onMid(A1,2

17、,2)=st,,Unit 4 日期與時間函數,now、today函數year、month、day、hour函數dateif函數,,1. now、today函數,now函數是提取當前系統(tǒng)日期和時間Today函數是提取當前系統(tǒng)日期語法：Now()參數為空Today()參數為空舉例：假定系統(tǒng)當前時間為 2013/03/30 9:481.如果A1=now(),那么A1= 2013/03/30 9:482.如果B1=tod

18、ay(),那么B1= 2013/03/30,,2. year、month、day、hour函數,year函數作用是提取日期中的年份 month函數作用是提取日期中的月份Day函數作用是提取日期中的日數 Hour函數作用是提取日期中的小時數語法：XXX(A1)，其中XXX代表上面所有函數，它們語法規(guī)則一樣，A1代表包含日期與時間的單元格。舉例：假定系統(tǒng)當前時間為 A1=2011/11/17 9:581. B

19、1=year(A1),那么B1= 20112. C1=month(A1),那么C1=113. D1=day(A1)，那么D1=174. E1=hour(A1),那么E1=9,,3. dateif函數,dateif函數作用是計算兩個日期之間相差的年數、月數、日數，這個函數比較適用于統(tǒng)計出生年月語法：Dateif(起始日期，結束日期，參數類型)其中參數類型包括：“Y” 時間段中的整年數 ，“M” 時間段中的整月數，“D” 時間段

20、中的天數舉例：假定系統(tǒng)當前日期為 2011/11/17 單元格A1中日期為2000/01/01,下面例子分別計算起始日期與當前日期間相差的年數，月數，日數。1.如果B1=DATEDIF(A1,TODAY(),"y"),那么B1=112.如果B1=DATEDIF(A1,TODAY(),“m"),那么B1=1423.如果B1=DATEDIF(A1,TODAY(),“d"),那么B1=

21、4338,,Unit 5 數學與統(tǒng)計函數,Sum、average、abs、mod函數sumif、countif函數max、min、small、large、rank函數value、isodd、iseven函數,,1.Sum、average、abs、mod、power函數,Sum函數作用是求和 Average函數作用是求平均數Abs函數作用是求絕對值 Mod函數作用是求余 Power函數作用是求n次方語法：

22、Sum(數值1，數值2……) 其中數值可以直接是數字，也可以是包含數字的單元格，也可以是一片單元格區(qū)域。Average語法規(guī)則同上Abs（數值）Mod(數值1，數值2)，其中數值1是被除數，數值2是除數Power(數值1，數值2)，其中數值1是數，數值2是多少次方以上函數返回值皆為數字,,1.Sum、average、abs、mod函數,舉例：假設 A1=1，B1=2,C1=-21.sum(A1,B1)=32.aver

23、age(A1,B1)=1.53.abs(A1)=1,abs(C1)=24.mod(B1,A1)=0,mod(A1,B1)=15.power(B1,2)=4,另外^（數字6上面的符號）也有這個功能，比如B1^3=8另外要注意的是，想要減或者求負數，直接在單元格前加-號就可以了比如上面例子中sum(A1,-B1)=-1,,2.sumif、countif函數,Sumif函數作用是對區(qū)域內符合條件的單元格求和Countif函數作用

24、是統(tǒng)計區(qū)域內符合條件的單元格的數量語法：Sumif(條件區(qū)域，條件，要計算和的區(qū)域)Countif(區(qū)域，條件)舉例：,,2.sumif、countif函數,假設有如下表格1.假如我們要計算1班所有人的手工課的總分，可以使用如下函數 SUMIF(A2:A5,“1”,G2:G5),在函數里面，前一區(qū)域A2:A5代表查尋條件“1”的查找區(qū) 域，中間的“1”表示查找的條件(要使用雙引號)，后一區(qū)域G2:G5代表要加

25、總的數字 區(qū)域2.假如我們要統(tǒng)計手工課大于90分的人數，可以使用countif(G2:G5,”>90”),前面代表 要查找的區(qū)域，后面表示查找條件，記住要使用雙引號,,3. max、min、small、large、rank函數,Max求一組數據的最大值 Min求一組數據的最小值Small求一組數據的第N小的值 Large求一組數據的第N大的值Rank作用是將一組數組排序語法：Max(數字1，

26、……數字n)，參數可以是單獨的數字，也可以是包含數字的單元格。MIN函數語法同上。Small(單元格區(qū)域，數字N)，其中前者為包含數字的單元格區(qū)域，后者為要查找的第N小的數字。,,3. max、min、small、large、rank函數,large(單元格區(qū)域，數字N)，其中前者為包含數字的單元格區(qū)域，后者為要查找的第N大的數字。Rank(需排序的數字，需排序的數組，排序類型)，其中排序類型為0時，按降序排列，不為0時，按升序排

27、列。舉例：假設A1,A2,A3,A4,A5分別為12，9，7，7，21Max(A1:A5)=21 Min(A1:A5)=7Small(A1:A5,3)=9 Large(A1:A5,5)=7,,3. max、min、small、large、rank函數,舉例 假設A1,A2,A3,A4,A5分別為12，9，7，7，21, 使用Rank（）函數對這個數組進行排序，按降序(從大到小)排列,其中B列是排序后的序

28、號B1=RANK(A1,$A$1:$A$5,0),其中A1表示要排序的數字，A1:A5表示排序的數組，0表示按降序排列，另外要注意的是，引用排序的數組時，我們用了絕對地址引用$A$1:$A$5(這個可以用F4鍵在地圵符間進行切換),這樣我們在引用公式到B2,B3單元格時，這個地址區(qū)域不會變。,,4.value函數,Value函數作用是將文本類型的數字轉換成數字類型，常用于出生日期，銀行卡等數據類型的轉換。語法：Valu

29、e(文本)，其中文本必須為數字，只是存儲類型為文本格式舉例： 假設A1=20111121，A2=5如果B1=2011-VALUE(MID(A1,3,2))，那么B1=2000.我們知道，MID函數的作用是取文本中的字符（第三章第4小節(jié)），在這里，我們從A1單元格的第3個字符開始取，取2個字符，得到字符11，然后將用value函數將其轉換成數字類型11。最后與2011相減得2000,,Unit 6 查詢與引用函數,Vloo

30、kup,hlookup函數Match,index函數,,1.vlookup,hlookup函數,Vlookup函數用于在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。Hlookup函數用于在表格或數值數組的首行查找指定的數值，并由此返回表格或數組當前列中指定行處的數值。語法：Vlookup(要查找的值，查找的區(qū)域，要返回列的序號，查找類型)其中要注意的問題：1.要查找的值一定要位于查找區(qū)域的

31、首列2.查找的區(qū)域中不要有隱藏的行或列，否則返回的值會出錯3.查找類型為0或false（假）時，為精準匹配，當省略或為true時，為近似匹 配（沒有找到要查找的值時，返回最接近于“查找值”的值），這里我們常 常使用精準匹配，保證查找的準確性。Hlookup函數語法與Vlookup函數語法相同。,,1.vlookup,hlookup函數,舉例：表1（sheet1）

32、 表2（sheet2）如果我們要在表2中根據姓名導出手工課的成績，可以使用如下函數 B2=VLOOKUP(A2,表1的$A$2:表1的$F$5,6,0),填寫參數時，可以在公式輸入欄里選擇Vlookup函數后直接用鼠標點擊輸入1.第一個參數代表我們要查找的值（根據姓名查找手工成績）要注意的是，查找值有相同的（有同名 的），返回第一個值所對應的返回值2.第二個參數代表包含查找值的查找區(qū)域，這個查找區(qū)域可以在其他工作

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

34、）4.第四個參數0代表包含查找類型為精準查找，一般我們要使用精準查 找，保證查找值的準確性Hlookup函數使用與Vlookup函數相似，只是將行列互換而已。具體區(qū)別大家可以自己體驗一下。,,2.Match,index函數,Match函數查找某值在數組中的位置。Index函數作用是返回數組中某行某列的值語法：1.Match(要查找的值，數組，查找類型)，其中要查找的值可以是單獨輸入的值 （要使用雙引號），也可以是包含查

35、找值的單元格。數組包含查找值的一組單元 格。查找類型包含3種，為-1時,參數中的數組須降序排列；為1時，數組須升序 排列；為0時，可以是無序排列。2.index(數組，行，列),其中的行列是數組的相對的行列值，不是工作表的行列,,2.Match,index函數,舉例：在上面的數組中1.A8=MATCH(4,A2:A6,0),其中，第一個參數4代表我們要在數組（A2:A4） 中查找4這個值,最后一個參數0代表我們

36、查找的數組是無序排列。2.B8=INDEX(A2:B6,3,2)，其中，第1個參數表示數組的區(qū)域，第2，3個參 數表示這個查找區(qū)域的行和列。,,Unit 7 數據庫函數的應用,Dget,Dcount函數Dmax,Dmin,Dsum,Daverage函數,,1. Dget,Dcount函數,D…函數是數據庫類型函數，與其它類型函數不同的是前面多了一個字母D（數據庫database的首字母），數據庫函數的使用功能更強大，可以在

37、查找引用中使用更加復雜的查找關系。1.Dget函數提取滿足條件的單個記錄，有多個值符合條件時返回錯誤2.Dcount函數返回滿足條件的單元格的個數語法：D…函數的語法格式都一樣D…（數據庫區(qū)域，函數要使用的數據列，查找條件），其中第一個參數表示構成數據庫的單元格區(qū)域（如下例中的A3:F10），第二個參數表示函數要使用的數據列，可以是用雙引號引用的文本，也可以是列號（如下例中的“語文”，1），第三個參數代表包含給定條件的單

38、元格區(qū)域，它至少包含一個列標志和列標志下方用于設定條件的單元格（如下例中的B1:B2，B1:C2）。,,1. Dget,Dcount函數,舉例：1.Dcount()，在上表中，如果我們想要統(tǒng)計語文>89的人數數量，可以在B1中設置列標志（語文），在B2中設置給定條件（>89）,在這里我們在A12單元格求結果 A12=DCOUNT(A3:F10,“語文”,B1:B2)，其中第一個參數（A3:F10）表示數據庫

39、區(qū)域， 第二個參數“語文”為我們要使用的數據列（我們要在語文這一列查找），第三個參數 表示給定的查找條件（包含一個列標志和一個位于列標志下方的條件)2.Dget(),求語文>89,歷史>91的人的姓名，在B12中求結果，B12=DGET(A3:F10,1,B1:C2), 這里，第二個參數我們使用列號來代表要使用的數據列。,,2. Dmax,Dmin,Dsum,Daverage函數,Dmax函數返回數據庫選項中的最大值

40、。Dmin函數返回數據庫選項中的最大值。Dsum函數作用是對符合條件的選項列求和。Daverage函數作用是對符合條件的選項列求平均值。語法：以上四個函數語法規(guī)則與其它D…函數一樣。D…（數據庫區(qū)域，函數要使用的數據列，查找條件）,,2. Dmax,Dmin,Dsum,Daverage函數,舉例：在下例中我們求小豬，小熊，小強三人中語文最高分。1.我們可以使用Dmax()，A15=DMAX(A6:F13,2,A

41、1:A4)，同樣，第一個參數表示數據庫區(qū) 域，第二個參數表示我們求值的數據列（可以是列號2，也可以是文本“語文”），第 三個參數是設定的條件。,,2. Dmax,Dmin,Dsum,Daverage函數,舉例：在下例中我們求所有人中手工課的最低分。2.我們可以使用Dmin()，B15=DMIN(A6:F13,“手工”,A6:A13)，同樣，第一個參數表示據 庫區(qū)域，第二個參數表示我們求值的數據列（可以是列號6，也

42、可以是文本“手工”)， 第三個參數是條件（至少包含一個列標志和列標志下方的給定條件）。,,2. Dmax,Dmin,Dsum,Daverage函數,舉例：在下例中我們求一班所有人的語文總分和平均分。3.求總分，我們可以使用Dsum(),A13=DSUM(A4:G11,3,A1:A2)，得出結果為252.4.求平均分，我們可以使用Daverage(),B13=DAVERAGE(A4:G11,3,A1:A2),得出 結