常用函数介绍

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