常用函數(shù)介紹

2023/2/3行政例會1生產(chǎn)技術(shù)科

常用函數(shù)介紹

2016年2月2023/2/32一、匹配函數(shù)二、統(tǒng)計函數(shù)三、查找函數(shù)四、日期函數(shù)2023/2/33一、匹配函數(shù)1、lookup①、定義：把數(shù)（或文本）與一行或一列的數(shù)據(jù)依次進行匹配，匹配成功后，然后把對應(yīng)的數(shù)值查找出來。②、注意事項：重要注意點：（查找的范圍）中的數(shù)值必須按升序排序：-2、-1、0、1、2、...、A-Z、FALSE、TRUE。否則，lookup函數(shù)可能會返回錯誤的結(jié)果。如果lookup函數(shù)中l(wèi)ookup_value（查找的值）小于lookup_vector（查找的范圍）中的最小值，則lookup函數(shù)會返回#N/A錯誤值。③、逆向查詢=lookup(1,0/(條件),目標(biāo)區(qū)域或數(shù)組）條件可以是多個邏輯判斷相乘組成的多條件數(shù)組=lookup(1,0/((條件1)*(條件2)*(條件N)),目標(biāo)區(qū)域或數(shù)組)④、根據(jù)簡稱查詢?nèi)Q=iferror(lookup(1,0/find(A2,E2:E8),E2:E8),””)⑤、多個區(qū)間的條件判斷=lookup(B2,{0,50,60,75,86,96}，{”很差”,”差”,”一般”,”較好”,”優(yōu)秀”,”能手”})2023/2/34一、匹配函數(shù)2、vlookup①、定義：當(dāng)查找的值位于查找范圍的首列，并且返回的值在查找范圍的第幾列，可以使用vlookup函數(shù)。=vlookup（在數(shù)據(jù)表第一列中查找的值，查找的范圍，返回的值在查找范圍的第幾列，模糊匹配/精確匹配）②、注意事項：FALSE（0）省略為精確匹配。TRUE（1）為近似匹配。③、常用舉例④、多行查找時復(fù)制公式的問題=vlookup(A1,B1:F18,COLUMNS(B1),0)⑤、避免出現(xiàn)錯誤值=if(iferror(vlookup(參數(shù))),””,vlookup(參數(shù)))⑥、字符模糊查找=vlookup(“*”&A10”*”,A2:B6,2,0)數(shù)字區(qū)域從小到大排序找到比它小的數(shù)2023/2/35一、匹配函數(shù)2、vlookup⑦、反向查找=vlookup(A9,if({1,0},B2:B5,A2:A5),2,0)⑧、多條件查找=vlookup(A9&B,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)⑨、換成文本查找=vlookup(A9&””,A1:D26,2,0)2023/2/36一、匹配函數(shù)3、Hlookup①、定義：在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，用HLOOKUP。=hlookup（在數(shù)據(jù)表第一列中查找的值，查找的范圍，返回的值在查找范圍的第幾行，模糊匹配/精確匹配）②、注意事項：FALSE（0）省略為精確匹配。TRUE（1）為近似匹配。2023/2/37一、匹配函數(shù)4、match①、定義：指返回指定數(shù)值在指定數(shù)組區(qū)域中的位置。=match（查找值，區(qū)域，查詢的指定方式）②、注意事項：查找值可以使用通配符、*、？等查詢指定方式：1時為查找小于或等于查找的最大值位置，數(shù)組區(qū)域必須按升序排列0時為等于的第一個數(shù)值，任意排列-1時為查找大于或等于查找的最小值位置，數(shù)組區(qū)域必須按降序排列③、與index合用④、MATCH函數(shù)用于在指定區(qū)域內(nèi)按指定方式查詢與指定內(nèi)容所匹配的單元格位置；LOOKUP函數(shù)用于在指定區(qū)域內(nèi)查詢指定內(nèi)容多對應(yīng)的匹配區(qū)域內(nèi)單元格的內(nèi)容。使用MATCH函數(shù)時的指定區(qū)域必須是單行多列或者單列多行；查找的指定內(nèi)容也必須在指定區(qū)域存在，否則會顯示“#N/A”錯誤。2023/2/38一、匹配函數(shù)5、index①、定義：返回表或區(qū)域中的值或?qū)χ档囊谩：瘮?shù)INDEX()有兩種形式：數(shù)組形式和引用形式。數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組；引用形式通常返回引用。=index（單元格區(qū)域或常數(shù)，行號，列號）②、index與match結(jié)合的例子6、choose選擇函數(shù)，函數(shù)從參數(shù)列表中選擇并返回一個值。=choose（選擇的索引值，項目，項目，……）2023/2/39二、統(tǒng)計函數(shù)1、count①、定義：返回數(shù)字參數(shù)的個數(shù)，它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。2、counta①、定義：返回參數(shù)中非空值的數(shù)目。利用函數(shù)counta可以計算組成或者單元格區(qū)域中數(shù)據(jù)項的個數(shù)。3、countblank①、計算某個單元格區(qū)域中空白單元格的數(shù)目。2023/2/310二、統(tǒng)計函數(shù)4、countif定義：指定區(qū)域中符合指定條件的單元格計數(shù)的一個函數(shù)。以下介紹countif函數(shù)的18種常用設(shè)置：①、包含12的單元格數(shù)量=countif(A:A,12)②、包含負(fù)值的單元格數(shù)量=countif(A:A,”<0”)③、不等于0的單元格數(shù)量=countif(A:A,”<>0”)④、返回大于5的單元格數(shù)量=countif(A:A,”>5”)⑤、返回等于B1中內(nèi)容的單元格數(shù)量=countif(A:A,B1)⑥、返回大于B1中內(nèi)容的單元格數(shù)量=countif(A:A,”>”&B1)⑦、返回包含文本內(nèi)容的單元格數(shù)量=countif(A:A,”*”)2023/2/311二、統(tǒng)計函數(shù)4、countif⑧、返回含有三個字符的單元格數(shù)量=countif(A:A,”???”)⑨、返回等于單詞“Good”的單元格數(shù)量=countif(A:A,”Good”)⑩、返回包含單詞“Good”的單元格數(shù)量=countif(A:A,”*Good*”)(11)、返回以單詞“AB”開頭的單元格數(shù)量=countif(A:A,”AB*”)(12)、返回包含當(dāng)前日期的單元格數(shù)量=countif(A:A,TODAY())(13)、返回大于平均值的單元格數(shù)量=countif(A:A,”>”&AVERAGE(A:A))(14)、返回不等于空值和錯誤值的單元格數(shù)量=countif(A:A,”<>”)(15)、返回含值為3或-3的單元格數(shù)量=sum(countif(A:A,{3,-3}))2023/2/312二、統(tǒng)計函數(shù)4、countif(16)、返回邏輯值為TRUE的單元格數(shù)量=countif(A:A,TRUE)(17)、返回相同身份證號碼相同的數(shù)量=countif(A:A,A1&”*”)(18)、返回區(qū)域內(nèi)不重復(fù)的數(shù)量=sumproduct(1/countif(A:A,A:A))2023/2/313二、統(tǒng)計函數(shù)5、countifs定義：用來統(tǒng)計多個區(qū)域中滿足給定條件的單元格的個數(shù)。=countifs（條件區(qū)域1，條件1，條件區(qū)域2，條件2，…）6、sumif定義：SUMIF函數(shù)可以對報表范圍中符合指定條件的值求和。Excel中sumif函數(shù)的用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。=sumif（條件區(qū)域，條件，求和區(qū)域）(1)、返回如花的銷售額=sumif(B:B,”如花”,C:C)(2)、返回除如花之外的銷售額=sumif(B:B,”<>如花”,C:C)(3)、返回大于900的銷售額=sumif(C:C,”>900”)2023/2/314二、統(tǒng)計函數(shù)6、sumif(4)、返回低于平均值的銷售總額=sumif(C:C,”>900”)(5)、返回等于單元格E1中姓名的銷售額=sumif(B:B,E1,C:C)(6)、返回B列不為空的銷售額=sumif(B:B,”*”,C:C)(7)、返回姓名為三個字的銷售額=sumif(B:B,”???”,C:C)(8)、返回在姓名中任何位置包含“花”銷售額=sumif(B:B,”*花*”,C:C)(9)、返回姓名以“花”開頭的銷售額=sumif(B:B,”花*”,C:C)(10)、返回當(dāng)前日期的銷售額=sumif(A:A,today(),C:C)2023/2/315二、統(tǒng)計函數(shù)6、sumif(11)、返回忽略錯誤值的銷售額=sumif(C:C,”<9e307”)(12)、返回如花和秋花的銷售總額=sum(sumif(B:B,{“如花”,”秋花”},C:C))(13)、返回如花的銷售總額=sumif(B:E,”如花”,C:F)7、sumifs定義：根據(jù)多個指定條件對若干單元格求和。=sumifs(求和的范圍，條件范圍1，條件1，條件范圍2，條件2，…）2023/2/316三、查找函數(shù)1、find定義：返回一個字符串在另一個字符串中出現(xiàn)的起始位置（區(qū)分大小寫）=find(要查找的文本，在其中進行查找的文本，開始位置），1為默認(rèn)2、search定義：用來返回指定的字符串在原始字符串中首次出現(xiàn)的位置,從左到右查找，忽略英文字母的大小寫。=search(要查找的文本字符串，在其中進行查找的文本，開始位置），1為默認(rèn)。①、find函數(shù)區(qū)分大小寫，而search函數(shù)則不區(qū)分。②、search函數(shù)支持通配符，而find函數(shù)不支持。2023/2/317三、查找函數(shù)3、mid定義：從任意位置截取字符串。=mid（字符串，開始截取位置，截取字符個數(shù)）(1)、如果mid的參數(shù)是文本，要加引號，且在英文狀態(tài)下輸入。(2)、空格也是一個字符。(3)、

mid函數(shù)常常和其他函數(shù)結(jié)合起來使用，如通過find函數(shù)進行定位。4、left定義：用來對單元格內(nèi)容進行截取。從左邊第一個字符開始截取，截取指定的長度。=left(選定的單元格，從左開始截取的字符數(shù)）(1)、一個空格也算一個字符。(2)、如果截取的字符數(shù)大于文本長度，則返回所有文本。(3)、如果省略截取字符數(shù)的話，默認(rèn)值為1。2023/2/318三、查找函數(shù)5、right定義：從字符串右端取指定個數(shù)字符。=right（字符串，截取字符個數(shù)）2023/2/319四、日期函數(shù)1、year定義：返回某日期的年份，其結(jié)果為1990到9999之間的一個整數(shù)。=year(“2000/8/6”)=20002、month定義：返回以序列號表示的日期中的月份，介于1-12之間的整數(shù)。=month(“2001/02/24”)=23、weekday定義：返回某日期的星期數(shù)，1-7之間=weekday(日期，類型),類型2為星期一到星期天4、weeknum定義：返回一個數(shù)字，該數(shù)字代表一年中的第幾周。=weeknum(日期，1）2023/2/320四、日期函數(shù)5、today定義：返回系統(tǒng)當(dāng)前日期的序列號。=today（）6、day定義：返回用序列數(shù)（1-31）表示的某日期的天數(shù)，用整數(shù)1到31表示。=day(“2001/02/24”)=247、hour定義：返回時間值的小時數(shù)，即介于0-23之間的一個整數(shù)。=hour(“3:30:30PM”)=158、minute定義：返回時間值中的分鐘，即介于0-59之間的一個整數(shù)。=minute(“15:30:00”）=302023/2/321四、日期函數(shù)9、second定義：返回時間值的秒數(shù)。(0-59)=second(“3:30:26PM”）=2610、now定義：返回當(dāng)前日期和時間所對應(yīng)的數(shù)列號。=now（）11、time定義：返回某一特定時間的小數(shù)值，它返回的小數(shù)值從0到0.99999，代表0:00:00到23:59:59之間的時間。=time(hour,minute,second)12、timevalue