2023/2/3行政例會1生產技術科

常用函數介紹

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

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