|
大家好,又见面了，今天说说数组公式有关的内容。 众所周知，数组公式是需要以ctrl+shift+enter三键结束公式，自动在公式两侧自动生成一组{ }表明公式进行的是数组运算。 论坛和群里经常有问题大佬们已经写出正确公式，但是由于题主不会数组三键造成不能正常返回结果的，这个遭遇你有没有碰到过呢? 数组三键是新手写公式的一个重要门槛。所以由于公式的操作难度，适用性等需求，总能在群里看到这种问题:“有没有不要三键的去重公式”,“有没有不要三键的一对多公式”等等。 此时通常大伙会告诉你，数组公式都是要三键的，不过，其实在excel里有些函数本身是支持数组运算的，只要善于应用这类函数，很多数组公式是可以在不使用三键时正确返回结果的，这篇就是介绍其中的一些常见案例。 本篇的公式虽然全部都可以不使用数组三键,但都属于数组运算,数据量大时不推荐公式解决,仅作为函数学习的一类拓展应用。 1,系统导出的数据求和 很多软件导出的数字并不是数值型而是文本型，而且可能掺杂一些非打印字符。 对这些干扰运算的字符,通常可以用分列/替换等功能先处理再进行求和，如果不想处理数据或者不清楚如何处理,可以直接借鉴这个公式快速求和。 =SUMPRODUCT(NUMBERVALUE(B2:B8)) Sumproduct不用多说了吧,本身支持数组运算的,2013及以上版本可以使用： =SUMPRODUCT(NUMBERVALUE(求和区域)) NUMBERVALUE函数可以直接清除数字两端的不可见字符而且能把空格与空文本等直接转化为0计算。 2,多条件匹配 这个公式是针对VLOOKUP+IF({1,0}结构的逆向查询的,使用这个解法运算量大且新手很难理解且需要三键 因此这类问题现在一般都是推荐使用LOOKUP(1,0/(条件1)/(条件2)……的方式解决,咱们的公众号里已经介绍过很多次这个函数了,所以这里不再详细介绍。 3,提取不重复内容 这是这类需求里最常见的,前两个解法只是在常规公式里加了个函数,就能使整个公式不用三键即可运行。 解法1 =INDEX(A:A,MATCH(0,INDEX(COUNTIF(C$1:C1,A$2:A13),0,1),0)+1)&'' 解法2 =INDEX(A:A,MATCH(0,MMULT(COUNTIF(C$1:C1,A$2:A13),1),0)+1)&'' 解法3 =LOOKUP(1,0/FREQUENCY(1,--ISNA(MATCH(A$2:A13,C$1:C1,0))),A$2:A13)&'' 解法4 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$13)/(MATCH(A$2:A$13,A$2:A$13,0)=(ROW($2:$15)-1)),ROW(A1))),'') 4,一对多查找 第3个案例已经出现了一个函数mmult,在这类需求里你可以把这函数看成这类问题最强的催化剂。 所谓最强,是因为以万金油为例,index+small+if+row(/column) 组合里我试过很多方式,唯有row函数无法被替代,因为row可以返回指定的序列数的内存数组，但前提是使用数组三键。 数组运算里row(/column)经常是整个数组运算需要三键的重要因素，因此,很多公式核心就是使row在不三键前提下成功返回内存数组，而这个需求,使用mmult即可做到。 即很多时候由于row限制必须使用三键才能正确返回的公式，我们只需要在row(/column)外面加一个mmult就能正确返回结果，而正是这类公式下如果添加这个函数就正确,不添加就错误的特性，我才把这类函数称之为数组运算的'催化剂'。 借助这个函数列个不用三键的万金油一对多解法： 利用MMULT的变种万金油很容易实现不使用三键的一对多公式： =INDEX(B:B,SMALL(--TEXT(MMULT((A$8:A$17=D$8)*ROW($8:$17),1),'0;;99'),ROW(A1)))&'' 当然之前介绍的AGGREGATE函数也是可以的： =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($8:$17)/(A$8:A$17=D$8),ROW(A1))),'') 5,合并单元格区域对应最大值 这类合并单元格的函数问题,才是这类不使用三键函数公式应用的最大原因，因为合并单元格结构是不能直接三键的。 而函数爱好者的目的之一，就是把各种看似不可能直接实现的函数问题用一个公式直接实现： 解法1 =MAX(OFFSET(A2,,,MODE((A3:B11='')%+ROW($3:$11)-2))) 解法1变种 =MAX(A2:INDEX(A:A,MODE((A3:B11='')%+ROW(2:10)))) 解法2 =MAX(OFFSET(A2,,,MATCH(1,FREQUENCY(1,N(B3:B12<>'')),0))) 解法3 =MAX(OFFSET(A2,,,MDETERM(MATCH(1=0,B3:B$12='',-1))) 解法4 =MAX(OFFSET(A2,,,IFERROR(MATCH(1,MMULT(N(B3:B11<>''),1),0),ROWS(2:$11)))) 解法4为最常规思路,因为合并单元格结构的难点为定位最后1个区域的大小,这里直接match错位定位后用iferror处理最后1个区域，如果下方无其他数据,rows可以直接简化为一个足够大的数9/99等等,区域固定的话直接为最后1个合并区域单元格数量即可。 而这里的mmult也是整个公式不需要三键的最大功臣，解法2-解法4只引用公式所在列,不需要其他列配合。 注意这些公式，只要把max换成avregare，median等就能计算对应合并区域的平均值，中位数等等。 今天列举的公式都是不需要三键但都属于数组运算的,希望对你有所帮助。 |
|
|
来自： hercules028 > 《excel》
