|
今天和小伙伴们学习下万金油公式在二维数组中的应用。对于万金油公式，相信常用excel函数的同学都很了解，我这里把它叫做筛选公式。如果不了解，也没有关系，可以慢慢积累。 -01- 表格转换 1.将左表转成右表（二维数组的万金油） 如下图所示，将左表变成右表，如果有空单元格则忽略，按照顺序排列好。最简单的方法就是用pq完成，点几下操作就可以了。但是我们现在是用函数来完成，还是有些困难的。第一种方法就是用二维数组的万金油，我也是从别人那里学来的；第二种方法是用textjoin，是我自己想出来的。 先说第一种方法，转换后的表格有2列，这2列分别是2个不同的公式完成的。第一列的公式为在G1单元格输入公式=IFERROR(INDEX(A:A,SMALL(IF(B$2:E$6<>"",ROW($2:$6)),ROW(A1))),"")，按三键结束，向下填充。 和之前的公式相比，这个公式的区域为二维区域。IF(B$2:E$6<>"",ROW($2:$6))这部分对B$2:E$6这个二维区域进行判断，如果不等于空文本，返回对应的行号，否则返回false，还是形成一个二维数组{2,2,2,2;FALSE,3,3,3;4,FALSE,4,4;5,5,FALSE,5;6,6,6,FALSE}。 SMALL(IF(B$2:E$6<>"",ROW($2:$6)),ROW(A1))这部分是对二维数组的行号，从小到大的提取。这样第一行就是2，下拉第二行是2，第3行是2，第四行是2。。。。。 INDEX(A:A,SMALL(IF(B$2:E$6<>"",ROW($2:$6)),ROW(A1)))这部分是返回A列对应的单元格。如果上一步的行号是2，那这一步就是返回A2单元格的值。这样刚好把A、B、C、D、E按所在行的非空单元格个数重复出现。比如第2行中，除了A的非空单元格有4个，那么A就重复出现4次。这个公式让我想到了：其实就是按指定次数重复出现。关于这个问题，以后会再总结一篇文章。 最后用iferror处理错误值。 第二列的公式为在H1单元格输入公式=INDIRECT(TEXT(SMALL(IF(B$2:E$6<>"",ROW($2:$6),4^8)/1%%+COLUMN(B:E),ROW(A1)),"r0c0000"),)&""。按三键结束，向下填充。 这个公式就更复杂一点，简单来说，就是把B$2:E$6这个区域中不为空的筛选出来，按顺序排列。但是公式可不是这么容易就能写出来的。此时如果单元格区域不为空，不仅要返回行号，还要返回列号。因为只有行号和列号都知道了，才能确定单元格的地址。 IF(B$2:E$6<>"",ROW($2:$6),4^8)这部分同样判断不为空的返回行号，否则返回4^8（65536，早期版本的最后一行）。结果为{2,2,2,2;65536,3,3,3;4,65536,4,4;5,5,65536,5;6,6,6,65536} IF(B$2:E$6<>"",ROW($2:$6),4^8)/1%%这部分是将上一步的行号扩大1万倍，除以万分之一，就是乘以一万。结果为{20000,20000,20000,20000;655360000,30000,30000,30000;40000,655360000,40000,40000;50000,50000,655360000,50000;60000,60000,60000,655360000}。 F(B$2:E$6<>"",ROW($2:$6),4^8)/1%%+COLUMN(B:E)这部分在上一步的基础上加上它的列号，这样行号和列号就合在一起了。结果为{20002,20003,20004,20005;655360002,30003,30004,30005;40002,655360003,40004,40005;50002,50003,655360004,50005;60002,60003,60004,655360005}。 下一步用small函数从小到大提取每个数字，等于空的那些数字由于比较大就被排在最后了。20002就相当于2行2列，那怎么把它变成2行2列呢？就要用到text函数。 text(20002,"r0c0000")将20002变成"r2c0002"，就这是r1c1样式，代表2行2列那个单元格的文本型地址。最后用indirect返回文本型地址的引用INDIRECT(TEXT(20002,"r0c0000"),)，也就是B2单元格的值。 本来不想写这么多的，怕大家理解不了。希望你们能理解吧，剩下的自己去练习吧。接下来看第二种方法。 2.将左表转成右表（textjoin+rept） 下图J列和K列是用textjoin和rept函数完成的。我只说明K列的公式，因为比较简单。在K1单元格输入公式=TRIM(MID(TEXTJOIN(REPT(" ",99),1,B$2:E$6),ROW(A1)*99-98,99))，不用按三键，向下填充完成。 TEXTJOIN(REPT(" ",99),1,B$2:E$6)这部分textjoin的第一参数是分隔符，用的是99个空格，只不过用rept函数实现；第二参数1代表忽略空单元格，第三参数是要连接的区域。 由于99个空格太长，不好截图演示，我用短横线代替。公式为=TEXTJOIN("-",1,B2:E6)，结果为"简单-快乐-幸福-平静-沮丧-忧伤-失落-兴奋-惊喜-愉快-悲伤-气愤-委屈-嫉妒-疲惫-厌恶"。大家要把短横线当做99个空格。 接下来用mid函数提取，MID(字符串,ROW(A1)*99-98,99)。第一参数是上一步的字符串；第二参数是一个以1开始，等差为99的等差数列（下拉时）；第三参数是固定99。这样刚好把每个被分隔的字符串依次提取出来。 由于提取出来的字符串中包含多余的空格，所以用trim函数清除多余的空格。 J列的公式为在J1单元格输入公式=TRIM(MID(TEXTJOIN("",1,REPT(A$2:A$6&REPT(" ",99),COUNTIF(OFFSET(B$2:E$2,ROW($2:$6)-2,),"<>"))),ROW(A1)*99-98,99))，按三键结束，向下填充。公式较长，不做解释，有兴趣的可以自己看下。 链接： https://pan.baidu.com/s/1jr7bkdqrlsa5-KRYbEGtnA 提取码：fzoo |
|
|
