• 函数365之NOT函数:颠倒黑白之NOT函数

      Excel函数2017-5-120评论195
      函数名称:NOT 函数作用:NOT函数用于对参数值求反,当要确保一个值不等于某一特定值时,可以使用NOT函数。 函数语法:NOT(逻辑值或数值) 注意事项: 1、该函数有且只有一个参数; 2、参数可以是逻辑值或者数值或者结果为逻辑值或数值的表达式; 3、如果逻辑值为FALSE,函数NOT返回TRUE;如果逻辑值为TRUE,函数NOT返回FALSE; 4、如果参数为文本或者错误值,则NOT结果为错误值。 函数应用: 实例1、根据下表A列数据求相反逻辑值,如B列所示。 函数公式:B2单元格输入 =NOT(A2),公式下拉即可 解释说明: 1、A2和A3单元格为非0数值,在逻辑判断中相当于TRUE,所以用NOT函数可以取得相反逻辑FALSE; 2、A4单元格为数值0,在逻辑判断中相当于FALSE,所以用NOT函数取得相反逻辑TRUE; 3、A5和A6单元格分别为逻辑值FALSE和TRUE,用NOT函数可以取得相反逻辑TRUE和FALSE; 4、A7单元格为文本,文本作为NOT函数的参数结果会返回错误值,不存在相反逻辑; 5、A8单元格为错误值,错误值作为NOT函数的参数,其结果依然为错误值。 实例2、判断下面姓名是否包含“秀”字,如果包含返回TRUE,不包含返回“FALSE”,如B列所示。 函数公式:B2单元格输入 =NOT(ISERROR(FIND("秀",A2))),公式下拉即可 解释说明: 1、首先用FIND函数在姓名中查找“秀”字的位置,如果包含“秀”字则返回相应位置,否则返回错误值; 2、用ISERROR函数对FIND函数的结果进行判断,如果FIND结果为错误值则返回TRUE,否则返回FALSE,也就是不包含“秀”字的时候返回TRUE; 3、通过ISERROR函数的判断我们发现结果刚好与我们的需求相反,所以在用NOT函数求相反逻辑值即可。
    • 函数365之MODE.MULT函数:众数计算之一网打尽

      Excel函数2017-5-110评论521
      函数名称:MODE.MULT 函数作用:MODE.MULT函数可用于返回一组数据或数据区域中出现频率最高或重复出现的数值的垂直数组,也就是计算众数。 函数语法:MODE.MULT(参数1,参数2,参数3…,参数255) 注意事项: 1、该函数有255个参数,其中第1参数为必选参数,第2-255参数为可选参数; 2、参数可以是数字或者是包含数字的名称、数组或引用; 3、如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略,但包含零值的单元格将计算在内; 4、如果参数为错误值或为不能转换为数字的文本,将会导致错误; 5、如果数据集合中不含有重复的数据,则MODE.MULT数返回错误值 N/A; 6、MODE函数与MODE.MULT都是计算众数,区别在于当有多个值出现次数都一样时MODE函数只能返回第一个值,而MODE.MULT函数可以返回所有的值。 函数应用: 实例1、下表为某班学生考试成绩,要求计算成绩相同最多的分数是多少,如F列所示。 函数公式:选中F2:F11单元格区域,输入公式 =MODE.MULT(B2:B11,D2:D11),CTRL+SHIFT+回车键三键结束公式 解释说明: 1、要求计算相同分数最多,也就是要求计算出现频率最高的分数,由于数据中有多个相同次数的数值,所以用MODE.MULT函数,其中分数区域B2:B11和D2:D11分别为MODE函数的两个参数; 2、MODE.MULT要进行区域数组的运算,所以要先选定操作区域,然后输入公式最后三键结束公式。 实例2、下表为某公司1-5日的销售记录,要求计算销售次数最多是哪一天,如E列所示。 函数公式:选中E2:E11单元格区域,输入公式 =TEXT(MODE.MULT(A2:A11),"yyyy-m-d"),CTRL+SHIFT+回车键三键结束公式 解释说明: 1、要求计算销售次数最多的,也就是计算频率最高,由于数据中有多个相同次数的数值,所以用MODE.MULT函数,其中计算区域为A2:A11; 2、由于计算时日期会被当做数值进行计算所以MODE.MULT结果会返回日期的序列号,所以需要用TEXT函数将该序列号转成日期格式; 3、MODE.MULT要进行区域数组的运算,所以要先选定操作区域,然后输入公式最后三键结束公式。 实例3、下表为某公司部分员工考勤的迟到记录,要求查找迟到次数最多的员工姓名,如D列所示。 函数公式:选中D2:D11单元格区域,输入公式 =INDEX(B2:B11,MODE.MULT(MATCH(B2:B11,B2:B11,0))),CTRL+SHIFT+回车键三键结束公式 解释说明: 1、要求计算迟到次数最多的,也就是计算频率最高,用MODE函数,但是这里姓名不属于数值,所以B列不能直接作为MODE的参数; 2、我们可以用MATCH函数依次查找B2:B11区域里面每一个姓名在B2:B11区域中的位置,然后将该位置作为MODE.MULT的参数; 3、MODE.MULT(MATCH(B2:B11,B2:B11,0))可以得到出现次数最多的位置,用INDEX函数可以返回该位置的员工姓名; 4、MODE.MULT要进行区域数组的运算,所以要先选定操作区域,然后输入公式最后三键结束公式。 小结: 1、由于MODE.MULT要返回的是一组数据,所以都是数组运算,需要选定区域和三键结束公式; 2、只有当我们确定只有一个数值是出现次数最多的或者只取第一个出现次数最多的数值时,才可以使用MODE函数,否则请使用MODE.MULT函数; 3、通过上面三个实例我们发现都无可避免的出现很多个#N/A错误值,这个无法直接用纠错函数IFERROR规避,我们可以在公式区域用条件格式判断将错误值的单元格字体设置为白色; 4、MODE.MULT函数用法与MODE.SNGL函数用法一致。
    • 函数365之NETWORKDAYS函数:工作日天数知多少

      Excel函数2017-5-110评论231
      函数名称:NETWORKDAYS 函数作用:NETWORKDAYS函数用于返回参数两个日期之间完整的工作日数值,工作日不包括周末和专门指定的假期。 函数语法:NETWORKDAYS(开始日期,结束日期,指定假期) 注意事项: 1、第1参数为必选参数,表示需要计算的开始日期; 2、第2参数为必选参数,表示需要计算的终止日期; 3、第3参数为可选参数,表示不在工作日历中的一个或多个日期所构成的可选区域,如果该日期属于工作日,则计算时会自动忽略。 函数应用: 实例1、根据下表A列和B列的日期计算两个日期之间包含的工作日天数(不考虑节假日),效果如C列所示。 函数公式:C2单元格输入 =NETWORKDAYS(A2,B2),公式下拉即可 解释说明:计算两个日期的工作日天数可以用NETWORKDAYS函数,题目要求不考虑节假日,所以可以省略第3参数。 实例2、计算1月-10月每个月的工作日天数(不考虑节假日),如B列所示。 函数公式:B2单元格输入 =NETWORKDAYS(A2&"1日",EOMONTH(A2&"1日",0)),公式下拉即可 解释说明: 1、计算两个日期的工作日天数可以用NETWORKDAYS函数,题目要求不考虑节假日,所以可以省略第3参数; 2、第1参数为开始日期,要计算每个月的工作日天数,所以开始日期应该是每个月的1号,用“A2&"1日"”表示; 3、第2参数为结束日期,应该是每个月的最后一天的日期,用EOMONTH函数EOMONTH(A2&"1日",0); 实例3、计算1月份工作日天数,节假日如D列所示日期,不考虑调休,效果如B2所示。 函数公式:B2单元格输入 =NETWORKDAYS(A2&"1日",EOMONTH(A2&"1日",0),D2:D8) 解释说明: 1、计算两个日期的工作日天数可以用NETWORKDAYS函数,节假日为D列数据,所以第3参数为D2:D8单元格区域; 2、第1参数为开始日期,要计算每个月的工作日天数,所以开始日期应该是每个月的1号,用“A2&"1日"”表示; 3、第2参数为结束日期,应该是每个月的最后一天的日期,用EOMONTH函数EOMONTH(A2&"1日",0); 实例4、计算1月-10月每个月的工作日天数,节假日如D列所示日期,不考虑调休,效果如B列所示。 函数公式:B2单元格输入 =NETWORKDAYS(A2&"1日",EOMONTH(A2&"1日",0),(MONTH($D$2:$D$27)=ROW(A1))*$D$2:$D$27),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、第1参数为开始日期,要计算每个月的工作日天数,所以开始日期应该是每个月的1号,用“A2&"1日"”表示; 2、第2参数为结束日期,应该是每个月的最后一天的日期,用EOMONTH函数EOMONTH(A2&"1日",0); 3、判断D列的日期属于哪个月份MONTH($D$2:$D$27),然后判断是否与当前月份值相等MONTH($D$2:$D$27)=ROW(A1),再乘以相应的日期,当相等的时候会得到相应的日期值,不相等的时候返回0; 4、(MONTH($D$2:$D$27)=ROW(A1))*$D$2:$D$27)作为第3参数,表示需要排除的节假日。
    • 函数365之SUMSQ函数:直角三角形判断之平方和计算

      函数名称:SUMSQ 函数作用:SUMSQ函数用于返回所有参数的平方和。 函数语法:SUMSQ(参数1,参数2,参数3,…,参数255) 注意事项: 1、该函数最多支持255个参数,其中第1参数为必选参数,其他参数为可选参数; 2、参数可以是数字或者是包含数字的名称、数组或引用; 3、逻辑值和直接键入到参数列表中代表数字的文本被计算在内; 4、如果参数是一个数组或引用,则只计算其中的数字,数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略; 5、如果参数为错误值或为不能转换为数字的文本,将会导致错误。 函数应用: 实例1、根据下表A列和B列数值计算两个数的平方之和,效果如C列所示。 函数公式:C2单元格输入 =SUMSQ(A2:B2),公式下拉即可 解释说明:计算平方之和用SUMSQ函数,这里公式也可以写成=SUMSQ(A2,B2)。 实例2、下表A/B/C三列为三角形的三条边长,要求判断该三角形是否直角三角形,如D列所示。 函数公式:D2单元格输入 =IF(SUMSQ(SMALL(A2:C2,{1,2}))=LARGE(A2:C2,1)^2,"是","否"),公式下拉即可 解释说明: 1、判断是否三角形用勾股定理,三角形两直角边的平方和等于斜边的平方和则为等边三角形; 2、所以我们要判断那两条是直角边和那一条是斜边,直角三角形斜边最长,所以LARGE(A2:C2,1)为斜边,相反两条直角边最小,所以SMALL(A2:C2,{1,2})为两条直角边; 3、计算两条直角边的平方和用SUMSQ函数SUMSQ(SMALL(A2:C2,{1,2})),然后判断是否等于斜边的平方LARGE(A2:C2,1)^2; 4、嵌套IF进行判断,如果两直角边平方和等于斜边的平方,则返回“是”,否则返回“否”; 5、关于LARGE和SMALL函数如果有疑问的可以在公众号回复“LARGE”和“SMALL”获取相关函数教程。
    • 函数365之DATEDIF函数:你常说一日不见如隔三秋,可是你连DATEDIF都不会又怎能知道我们相隔了多少个春秋呢?

      函数名称:DATEDIF 函数作用:DATEDIF函数可用于返回两个日期的间隔数,可以是间隔天数,或者间隔月数,也可以是间隔年数。 函数语法:DATEDIF(开始日期,结束日期,间隔类型) 注意事项: 1、DATEDIF函数是Excel函数里的隐藏函数,在帮助中是查询不到的,该函数有3个参数,均为必选参数; 2、第1、第2参数都必须为日期或表示日期的序列号,且第2参数必须大于等于第1参数,否则结果为#NUM!错误值; 3、第3参数表示计算时要返回的结果类型,主要有以下结果类型; 函数应用: 实例1、下表为某公司的采购跟踪表,要求根据采购日期和付款天数,判断截止今天是否已经超期,如D列所示。 函数公式:D2单元格输入 =IF(C2-DATEDIF(B2,TODAY(),"D")>0,"","超期"),公式下拉即可 解释说明: 1、根据要求首先我们要计算采购日期与今天间隔的天数,用DATEDIF函数DATEDIF(B2,TODAY(),"D"),第三参数用"D",今天用TODAY表示; 2、用付款天数减去采购日期与今天间隔的天数C2-DATEDIF(B2,TODAY(),"D"),如果是大于0则表示付款天数大于间隔天数,也就是未超期,否则就是超期; 3、最后嵌套IF进行判断。 实例2、下表为某公司部分员工的入职记录,要求计算截止今天每个员工的工龄,如C列所示。 函数公式:C2单元格输入 =DATEDIF(B2,TODAY(),"Y"),公式下拉即可 解释说明:要求计算工龄也就是需要计算间隔年数,用DATEDIF函数,今天用TODAY函数表示,第三参数用"Y"。 实例3、下表为某公司部分员工出生日期,为了给员工准备生日福利需要做一个生日提醒,如C列所示。 函数公式:C2单元格输入 =TEXT(IFERROR(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),"d"),DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(B2),DAY(B2)),"d")),"距离该员工生日还有0天;;今天是该员工的生日,请及时发放生日礼物"),公式下拉即可 解释说明: 1、要求计算距离生日的天数用DATEDIF函数,首先根据出生日期取得本年生日的日期DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)); 2、然后DATEDIF计算当前日期TODAY与本年生日的日期间隔的天数DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),"d"); 3、如果上面计算结果为#NUM!错误值,则表示该员工今年的生日已经过了,那么就要计算今天TODAY与明年生日DATE(YEAR(TODAY())+1,MONTH(B2),DAY(B2))的间隔日期; 4、用IFERROR函数对两个DATEDIF函数进行嵌套,当计算今天与今年生日间隔天数为错误值时,则计算今天与明年生日的间隔天数; 5、用TEXT函数对大于0的天数提示生日间隔提示,对等于0的天数提示生日到了,TEXT函数格式“正数;负数;0;文本”。 实例4、下表为某公司部分员入职日期,公司统一给员工加薪,每个员工入职超过3个月可加薪100元,入职超过半年到1年的可加薪300元,其后每增加一年加50元,加薪幅度不能超过500元,要求计算每个人的加薪金额,如C列所示。 函数公式:C2单元格输入 =MIN(LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,0;3,100;6,300;12,300})+MAX((DATEDIF(B2,TODAY(),"Y")-1)*50,0),500),公式下拉即可 解释说明: 1、先根据入职日期计算每个员工的工龄(月数)DATEDIF(B2,TODAY(),"M"); 2、用LOOKUP函数判断工龄所属的加薪区间LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,0;3,100;6,300;12,300}); 3、计算每个员工大于1年的部分的年数然后乘以50,为防止负数用MAX函数与0比较取最大值MAX((DATEDIF(B2,TODAY(),"Y")-1)*50,0); 4、由于要求加薪幅度不能超过500,所以需要用MIN函数将500与加薪幅度做比较取最小值。
    • 函数365之DMIN函数:数据库函数之根据条件求最小值

      函数名称:DMIN 函数作用:DMIN函数用于返回列表或数据库中满足指定条件的记录字段(列)中的最小数字。 函数语法:DMIN(计算区域,计算字段名或返回第几列,条件区域) 注意事项: 1、第1参数为必选参数,表示构成列表或数据库的单元格区域,列表的第一行包含每一列的标签; 2、第2参数为必选参数,表示指定函数所使用的列; 3、第3参数为必选参数,表示包含所指定条件的单元格区域; 4、第3参数可以为任意区域,但是此区域必须包含至少一个列标签,并且列标签下方包含至少一个指定列条件的单元格。 函数应用: 实例1、下表为某公司部分销售明细,要求计算A商品最低销售价格,效果如F2列所示。 函数公式:F2单元格输入 =DMIN(A1:C11,C1,E1:E2) 解释说明: 1、根据条件计算最低销售价格,也就是计算最小值,可以用DMIN函数; 2、要计算的区域为A1:C11,计算字段名为C1,条件区域为E1:E2; 3、要返回的列数为A1:C11区域的第3列,所以公式也可以写成=DMIN(A1:C11,3,E1:E2) 实例2、下表为某公司部分销售明细,要求计算A商品4月份的最低销售价格,效果如G2列所示。 函数公式:G2单元格输入 =DMIN(A1:C11,C1,E1:F2) 解释说明: 1、根据条件计算最低价格可以用DMIN函数; 2、要计算的区域为A1:C11,计算字段名为C1,条件区域为E1:F2(销售日期为4月份表示为“<2017-5-1”); 3、要返回的列数为A1:C11区域的第3列,所以公式也可以写成=DMIN(A1:C11,3,E1:F2) 实例3、下表为某部门的销售业绩,要求计算指定的部分人员的最低销量及姓名,如E2和F2单元格所示。 函数公式:E2单元格输入 =DMIN(A1:B11,B1,D1:D6) F2单元格输入 =INDEX(A2:A11,LOOKUP(9^9,MATCH(D2:D6&DMIN(A1:B11,B1,D1:D6),A2:A11&B2:B11,0))) 解释说明: 1、根据条件计算最低销量可以用DMIN函数; 2、要计算的区域为A1:B11,计算字段名为B1,条件区域为D1:D6。 3、由于B列销量中有相同的数据,所以我们不能直接通过销量查找姓名,我们可以将姓名连接销量D2:D6&DMIN(A1:B11,B1,D1:D6); 4、然后分别查找它们在A2:A11&B2:B11中的位置MATCH(D2:D6&DMIN(A1:B11,B1,D1:D6),A2:A11&B2:B11,0),得到的结果为错误值和一个位置的值; 5、这里我们用LOOKUP函数忽略错误值的特性可以取到这个位置的值,然后根据这个位置用INDEX函数找到该姓名。
    • 函数365之MODE函数:众望所归之众数计算

      函数名称:MODE 函数作用:MODE函数可用于返回在某一数组或数据区域中出现频率最多的数值,也就是计算众数。 函数语法:MODE(参数1,参数2,参数3…,参数255) 注意事项: 1、该函数有255个参数,其中第1参数为必选参数,第2-255参数为可选参数; 2、参数可以是数字或者是包含数字的名称、数组或引用; 3、如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略,但包含零值的单元格将计算在内; 4、如果参数为错误值或为不能转换为数字的文本,将会导致错误; 5、如果数据集合中不含有重复的数据,则 MODE 数返回错误值 N/A。 函数应用: 实例1、下表为某班学生考试成绩,要求计算成绩相同最多的分数是多少,如F2所示。 函数公式:F2单元格输入 =MODE(B2:B11,D2:D11) 解释说明:要求计算相同分数最多,也就是要求计算出现频率最高的分数,用MODE函数,其中分数区域B2:B11和D2:D11分别为MODE函数的两个参数。 实例2、下表为某公司1-5日的销售记录,要求计算销售次数最多是哪一天,如E2所示。 函数公式:E2单元格输入 =TEXT(MODE(A2:A11),"yyyy-m-d") 解释说明: 1、要求计算销售次数最多的,也就是计算频率最高,用MODE函数,其中计算区域为A2:A11; 2、由于计算时日期会被当做数值进行计算所以MODE结果会返回日期的序列号,所以需要用TEXT函数将该序列号转成日期格式。 实例3、下表为某公司部分员工考勤的迟到记录,要求查找迟到次数最多的员工姓名。 函数公式:D2单元格输入 =INDEX(B2:B11,MODE(MATCH(B2:B11,B2:B11,0))) 解释说明: 1、要求计算迟到次数最多的,也就是计算频率最高,用MODE函数,但是这里姓名不属于数值,所以B列不能直接作为MODE的参数; 2、我们可以用MATCH函数依次查找B2:B11区域里面每一个姓名在B2:B11区域中的位置,然后将该位置作为MODE的参数; 3、MODE(MATCH(B2:B11,B2:B11,0))可以得到出现次数最多的位置,用INDEX函数可以返回该位置的员工姓名; 4、这里虽然MATCH函数的第一参数用了区域B2:B11,但是并不是数组运算不需要三键结束公式; 5、当出现多个员工迟到次数相同时,MODE只会返回第一个,如果需要返回所有的结果则需要用MODE.MULT函数。
    • 函数365之DMAX函数:数据库函数之根据条件求最大值

      函数名称:DMAX 函数作用:DMAX函数用于返回列表或数据库中满足指定条件的记录字段(列)中的最大数字。 函数语法:DMAX(计算区域,计算字段名或返回第几列,条件区域) 注意事项: 1、第1参数为必选参数,表示构成列表或数据库的单元格区域,列表的第一行包含每一列的标签; 2、第2参数为必选参数,表示指定函数所使用的列; 3、第3参数为必选参数,表示包含所指定条件的单元格区域; 4、第3参数可以为任意区域,但是此区域必须包含至少一个列标签,并且列标签下方包含至少一个指定列条件的单元格。 函数应用: 实例1、根据下表数据,计算指定部门的最大销量,效果如F2列所示。 函数公式:F2单元格输入 =DMAX(A1:C11,C1,E1:E2) 解释说明: 1、根据条件计算最大值可以用DMAX函数; 2、要计算的区域为A1:C11,计算字段名为C1,条件区域为E1:E2。 3、要返回的列数为A1:C11区域的第3列,所以公式也可以写成=DMAX(A1:C11,3,E1:E2) 实例2、根据下表数据,计算指定季度和指定部门的最大销量,效果如H2列所示。 函数公式:H2单元格输入 =DMAX(A1:D11,D1,F1:G2) 解释说明: 1、根据条件计算最大值可以用DMAX函数; 2、要计算的区域为A1:D11,计算字段名为D1,条件区域为F1:G2。 3、要返回的列数为A1:D11区域的第4列,所以公式也可以写成=DMAX(A1:D11,4,F1:G2) 实例3、根据下表数据,计算指定季度和指定部门的最大销量,效果如H2列所示。 函数公式:H2单元格输入 =DMAX(A1:D11,D1,F1:G3) 解释说明: 1、根据条件计算最大值可以用DMAX函数; 2、要计算的区域为A1:D11,计算字段名为D1,条件区域为F1:G3。 3、要返回的列数为A1:D11区域的第4列,所以公式也可以写成=DMAX(A1:D11,4,F1:G3)
    • 函数365之N函数:最短函数之数值转换N函数

      函数名称:N 函数作用:N函数用于返回引用的数值,如果引用为文本则结果返回0。 函数语法:N(单元格引用或公式) 注意事项: 1、有且只有一个必选参数,可以是文本、数值、单元格引用或者公式; 2、N函数会将日期值转化为代表日期的序列号,将TRUE转化为1,将文本和FALSE转化为0; 3、如果参数错误值,,将N函数的结果也为错误值。 函数应用: 实例1、将A列数据转换数值,效果如B列所示。 函数公式:B2单元格输入 =N(A2),公式下拉即可 解释说明: 1、转换数值用N函数,上图A2和A5是数值不变,A3单元格是日期所以转换为了序列号,A4是逻辑值TRUE转换为1,A6:A9是文本和逻辑值FALSE转换为0; 2、特别注意的是A6单元格为文本型数值,同样会被转换为0。 实例2、下表为某公司一段时间的收支表,假定2017-4-20之前的余额为0,要求计算每行明细的截止今日余额,如D列所示。 函数公式:D2单元格输入 =N(D1)+B2-C2,公式下拉即可 解释说明: 1、今日余额=上日余额+本日收入-本日支出; 2、2017-4-20的上日余额是D1单元格,这是文本如果直接参与加减会导致结果为错误值,所以需要用N函数N(D1)将文本转换为0(0不影响计算结果); 实例3、下表为某公司部分销售人员的销售数据,要求根据指定的员工工号计算他们的销售总额,如F2单元格所示。 函数公式:F2单元格输入 =SUMPRODUCT(VLOOKUP(N(IF({1},E2:E5)),A2:C11,3,0)) 解释说明: 1、VLOOKUP函数一般第1参数不支持数组,这里用了数组,而且查找条件是数值,所以用N函数对数组引用进行降维N(IF({1},区域); 2、本来数组公式需要用三键结束的,但是这里用了SUMPRODUCT函数对结果进行相加,因为该函数本来就默认执行数组运算,所以不需要三键结束; 3、如果查找的条件并不是数值,而是文本,则使用T函数对数组引用进行降维T(IF({1},区域)。
    • 函数365之T函数:最短函数之文本校验T函数

      函数名称:T 函数作用:T函数用于返回引用的文本,如果引用为非文本则结果返回空文本。 函数语法:T(单元格引用或公式) 注意事项: 1、有且只有一个必选参数,可以是文本、数值、单元格引用或者公式; 2、如果参数是文本或引用了文本,T 将返回值; 3、如果参数未引用文本,T 将返回空文本 ("")。 函数应用: 实例1、检测下表A列数据,如果单元格内容为文本则返回文本内容,否则返回空,如B列所示。 函数公式:B2单元格输入 =T(A2),公式下拉即可 解释说明:检测数据是否文本可以使用ISTEXT函数或者T函数,根据题目需要这里我们直接使用T函数,可以让非文本的单元格引用直接返回空文本。这里A6单元格为文本型数值12,所以T函数结果返回空文本。 实例2、根据下表的姓名和销量然后按照指定的姓名查找计算销量的综合。 函数公式:E2单元格输入 =SUMPRODUCT(VLOOKUP(T(IF({1},D2:D4)),A2:B11,2,0)) 解释说明: 1、VLOOKUP函数一般第1参数不支持数组,这里用了数组,所以用T函数对数组引用进行降维(IF({1},区域); 2、本来数组公式需要用三键结束的,但是这里用了SUMPRODUCT函数对结果进行相加,因为该函数本来就默认执行数组运算,所以不需要三键结束; 3、如果查找的条件并不是文本,而是数值,则使用N函数对数组引用进行降维。 实例3、有一个工作簿,工作簿里面有N个工作表,工作表会动态增加,要求建立一个以每个工作表名称命名的超链接,如下图 操作步骤: 1、查看工作簿是不是xls格式,如果不是另存为xls格式; 2、打开公式选项卡,然后打开名称管理器,新建名称“sheets”,引用位置处输入公式=GET.WORKBOOK(1)&T(NOW()),如下图 3、在A2单元格输入公式=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&"!A1",REPLACE(INDEX(sheets,ROW(A1)),1,FIND("]",INDEX(sheets,ROW(A1))),"")),""),公式下拉即可得到我们要的效果 解释说明:公式=GET.WORKBOOK(1)是宏表函数可以获取工作表名称,至于为什么要用&连接T(NOW())是因为该宏表函数获取的信息不会实时更新,而NOW是时间函数可以实时更新计算,T函数可以将NOW函数获取的时间变为空文本,消除对数据的影响;