【原】Excel必备数据处理技巧之：将表格数据和文本逆序

ExcelEasy 2022-09-16 发布于北京

展开全文

介绍一个必备的数据处理技巧：

逆序。

这个技巧分为两种：

对一个表格中的数据的逆序
对一个长文本逆序

将表格逆序

将文本逆序

当然，实际中很少会有逆序的需求。但是，在处理很多应用时，中间经常会需要逆序的计算和输出。尤其是采用动态数组和新函数处理数据时。

作为一个经常用到的场景，表格逆序和文本逆序有很多共通之处，我们就放在一起来介绍。

MAKEARRAY方法

从需求上来看，逆序就是循环，对区域从后到前循环，最后一个先输出。文本也是这样。

在Excel函数中，MAKEARRAY函数可以通过循环生成一个数组，符合我们的要求：

右边是结果表，对于给定的行列：r，c：结果表中的对应元素实际上在原表中的位置是：6-r+1，c。其中，

6是整个区域的行数。

可以通过INDEX函数完成元素的获取。

这个过程用公式实现就是：

=LET(    data, B3:C8,    rowscount, ROWS(data),    colscount, COLUMNS(data),    rslt, MAKEARRAY(rowscount,colscount, LAMBDA(r,c,INDEX(data,rowscount-r+1,c))),    rslt)

其实，有了对数据区域的逆序方法，文本逆序就很简单了。因为我们可以将文本拆分为一个一个的字符数组：

拆成数组后，就可以使用上面的方法将数组逆序，然后将逆序后的数组合并为一个文本。

下面是完整公式：

=LET(    text, B11,    length, LEN(text),    splittext, MID(text,SEQUENCE(length,1),1),    revarr, MAKEARRAY(length,1,LAMBDA(r,c, INDEX(splittext, length-r+1))),    rslt, TEXTJOIN("",1, revarr),    rslt)

其中，

splittext - 将给定文本通过MID函数拆分为单个字符组成的数组。

revarr - 将texttocol逆序

rslt - 将revarr合起来

直接方法

MAKEARRAY方法的好处是方法直接，容易想到。这个方法可以推广到很多需要循环处理的情况。

但是数据较多时（比如，超过10000行数据），MAKEARRAY方法可能稍慢。（这里的稍慢，其实也不是很过分，速度还是可以接受，不过比着我们这里的直接方法的秒出结果来说，还是有比较明显的感受）

仔细分析上面的MAKEARRAY方法，可以看到其中取出相应元素时使用的是INDEX函数。

而INDEX函数天然就可以根据我们指定的序号返回多个给定元素的能力。比如：

=INDEX(B3:C8, {6;5;4;3;2;1},{1,2})

因此，只要我们能够想办法写出这里的常量数组：

 {6;5;4;3;2;1} {1,2}

自然就可以只用INDEX函数完成这个任务。

这两个数组可以通过SEQUENCE函数来完成：

SEQUENCE(rowscount,1,rowscount,-1)

这个公式用来生成行号，是倒序的。其中rowscount是总行数

SEQUENCE(1,colscount)

这个公式用来生成列号，是正序的。其中colscount是总列数。

完整的公式如下：

=LET(    data, B3:C8,    rowscount, ROWS(data),    colscount, COLUMNS(data),    rslt, INDEX(data,SEQUENCE(rowscount,1,rowscount,-1),SEQUENCE(1,colscount)),    rslt)

当然，还有一种更加直接的方法，排序，这里我们使用SORTBY函数，

公式如下：

=LET(    data, B3:C8,    rowscount, ROWS(data),    colscount, COLUMNS(data),    rslt, SORTBY(data,SEQUENCE(rowscount,1,rowscount,-1)),    rslt)

这个公式的本质还是SEQUENCE函数。

对于文本来说，MAKEARRAY方法中使用MID函数拆分成数组，其实MID函数本身可以支持数组模式的，比如：

=MID("abcd",{4,3,2,1},1)

就可以得到：

{"d","c","b","a"}

这样的倒序数组。

而关键还是如何生成中间数组

{4,3,2,1}

还是离不开SEQUENCE函数。

完整公式如下：

=LET(    text,B11,    length,LEN(text),    splittext,MID(text,SEQUENCE(length,1,length,-1),1),    rslt,TEXTJOIN("",1,splittext),    rslt)

自定义函数-递归

逆转的另外一类方法是使用自定义函数。这里主要是用到了自定义函数的递归。

递归是一种技术，也是一种解决问题的思路。使用递归，可以将原来很复杂的问题用很简单的逻辑解决。

关于递归，需要专门花时间去介绍，我们这里主要介绍逆序用的方法，至于原理，这里稍加解释。详细思路，容以后介绍。

这里我们使用自定义函数REVTEXT去进行文本的逆序，该函数定义如下：

revtext = lambda(    text,    if(len(text)=0, "",        right(text,1) & revtext(left(text,len(text)-1))    ));

递归定义的函数都非常短小。其思路可以用下图表示：

将原始文本分为left和right两部分，其中right就是最右侧一个字符，然后将right放在最左边，left放在右边，并调用REVTEXT函数继续处理。

而这里的REVTEXT实际上就是我们的自定义函数。

没错，递归就是函数调用自身！

不难理解，这个函数就会不停的循环调用下去，直到其中的条件：

len(text)=0

满足，整个过程结束。

而对于区域数据来说，也可以写这样的递归函数：

rev = lambda(    data,    let(        rowscount, rows(data),        colscount, columns(data),        if(rowscount <= 1, data,            vstack(                chooserows(data,rowscount),                rev(drop(data,-1))            )        )    ));