函数中使用符号,是一个比较多样的应用,下面就写几个小例子来展现一下常用符号的用法。 1、 & 链接符号 将单元格链接起来,形成唯一条件或其他。如图,要查询多个条件对应的唯一值。 因为结果是数字,可以使用sumifs函数或者lookup函数来实现多条件的查找。 但若要使用符号,可以将多个条件合并在一起进行查找,不少人也这么用。 当然,效率会打大大的折扣,本人并不推荐。 数组公式如下: =INDEX(H2:H4,MATCH(J2&K2&L2,E2:E4&F2:F4&G2:G4,0),) 2、- 的用法,将文本数字转为数值,进行统计。 下图中,sum函数并不能够对文本型的数字求和,文本型的数字往往多见于ERP系统导出的数据。 添加两个 -,负负得正,Ctrl Shift Enter用数组形式进行求和。 3 * 多条件查找。 要实现上图的多条件查找,函数公式如下: =LOOKUP(1,0/((E2:E4=J2)*(F2:F4=K2)*(G2:G4=L2)),H2:H4) * 表示多个条件都满足的时候返回True,就如And函数一样。 4、 的用法 如上图,要求条件1为A或B对应的结果1的求和。 数组函数公式为: =SUM(((E2:E4='A') (E2:E4='B'))*H2:H4) 相当于 or函数,满足两个条件任意一个即可。 5、空格的运用 5.1、提取最后某个符号后面的字符 如上图,需要提取最后一个“\”后面的数字。 函数公式为: =TRIM(RIGHT(SUBSTITUTE(A1,'\',REPT(' ',99)),99)) 思路为:将字符串中的每个“\”都替换为99个空格,然后这个新的字符串右边提取99个字符长度,返回的是【94个空格 12345】这样的字符串,然后再用Trim函数消除空格,得出结果。 5.2、返回单元格行列交叉的单元格 如下图,两个单元格相交的区域为B2,=A2:C2 B1:B3,返回B2单元格的值,AAA. 进一步扩展该应用,如下图数据 将上述的单元格区域的行列都应用名称来表示,如曹操,则表示单元格g5:k5; 现在要查找 曹操对应的产品3的价格是多少,=曹操 产品3,则表示g2:k5 单元格与i2:i7单元格相交的单元格i4,因此返回402. 再应用indirect函数,则更加灵活。函数公式为:=INDIRECT(F10) INDIRECT(G10) 随单元格内容而变化 该用法可相当于index match match的函数组合。 6“/”的运用 6.1、构建数组,如上面的例子中,0/((E2:E4=J2)*(F2:F4=K2)*(G2:G4=L2)),构建了一个数组,每当条件成立的时候,0/TRUE=0,否则返回错误。 6.2、构建长字符 需要返回单元格中第一个数字的位置. 数组函数公式为: =MIN(FIND(ROW(1:10)-1,A1&1/17)) 这里1/17是1除以17,返回的是一个小数,这个小数包含0-9所有数字,然后和A1单元格的字符串用 & 链接在一起,用来查找。 |
|