女神的案例,让13个函数参加了同一场战役

📌

要不,咱们就直奔主题,到底用了哪13个函数......🤪🤪🤪

序号

函数名称

联想函数

1

let

2

tocol

torow

3

reduce

4

lambda

5

vstack

hstack

6

filter

7

textsplit

8

clean

trim

9

min

10

max

11

text

12

sequence

13

drop

take

🔔

【背景】

某个ET表格交流群中,Hanna女神发出了一个诉求,要求是将这些乱七八糟的地址(邮编)(详见下场景1~3)如何转换成一列,对于用连接符“-”连接起来的地址,中间的地址号码也要一次性补齐,场景如下:


场景1:如下图,需要列出地邮编地址为02835、02836、02837、02838、02839,按纵向放到工作表的同一列中;


场景2:还有这样的数据,同一单元格中存在邮编段+单个邮件,或者同时存在多个邮编段,也要拆分成一个一个的邮编放在同一列不同的行中。


场景3:同一个单元格存在若干个不连续的邮编,中间是空格或换行符,当然该单元格的最前面及最后面也可能存在空格,数据如下。

下表存在换行符

🔔

【结果示例】

A列为转换前的数据,B列为转换后的结果。案例中,A列行数为5,254行,转换后结果列(B列)为7,543行。


🔔

【解题思路】

  1. 将A1单元格同的数据拆分成多行(但xxxxx-xxxxx格式的数据暂时不拆)

  1. 如果存在xxxxx-xxxxx格式的数据,生成动态序列

  • 用textsplit函数,将xxxxx-xxxxx函数按 "-"进行拆分;

  • 拆分后的结果为文本,可能存在多余的空格或换行符,套上 clean函数以清除多余的字符

  • 结果先乘以1,以便将文本转换成数字,方便下一步可以用max和min计算最大值和最小值,乘以1后,如果xxxxx数据前面为0的,结果就是小于5位的数值

  • 分别用Max和Min函数计算xxxxx-xxxxx两个值的最大值和最小值

  • 用max()-min()+1计算动态序列的个数,即动态数组中的一参

  • 用sequence生成序列

  1. 用reduce+lambda组合从原始数据的第一行开始循环至最后一行

  1. 用let函数对过程进行封装,以方便函数更加直观理解,方便后期维护,减少重复代码数量

💡

【解题步骤】

  1. 将每一个单元格数据按空格拆分成列(xxxxx-xxxxx视为同一个数据)

  1. 通过Filter函数将非空白行筛选出来

  1. 对于“xxxxx-xxxxx”格式的数据,要将中间连号部门也生成序列,如"85127-85132"共六个邮编。

3.1 通过sequence函数生成动态序列。语法:sequence(行,[列],[开始数],[增量]),如上例,即需要用公式 = sequence(6,1,85127,1)生成一个动态序列

现在要做的就是把sequence中前一参、三参数用公式替代。

3.1.1 先将xxxxx-xxxxx按"-"拆分后组新列表

3.1.2 通过clean将文本去掉多余的空格(肉眼不可见),并乘以1后转换成数字

3.1.3 取最大值或最小值,套上max或min即可

 3.1.4 套到sequence,生成序列

 3.1.5 如果前面的数据第一位换成0,生成的序列只有4位数了。此时,需要通知text函数将前面缺的位数补齐

此时,位数非常整齐漂亮了,是不?

3.2 如果原始数据第一个数我也在同时出现在列表中,我是不是要在C1单元格也输入同样的公式才行,如下图:

结果如下图所示:

3.3 当然,我们也可以用vstack将B1单元格的数据与新生成的序列拼接起来,公式如下:

3.4 以上是A1单元格的数据拆分成B列的二行数据,再对B列的数据逐行扫描。当然A列的数据拆分后,生成B列的数据可能不只有2或,也有可能是3个、4个、5个或几十几百个,此时我们不可能用【3.2】中提到的方法逐行下接公式,这样运行速度会非常慢;然后A2单元格的数据也会拆分后在B列往下排列。【3.3】的公式明显也无法满足不确定个数用vstack拼接的问题。

因此,我们引入一组非常重要的函数。【reduce+lambda】

🔔

=REDUCE("",B1:B2,LAMBDA(x,y,VSTACK(x,TEXT(SEQUENCE(MAX(CLEAN(TEXTSPLIT(y,,"-",))*1)-MIN(CLEAN(TEXTSPLIT(y,,"-",))*1)+1,1,MIN(CLEAN(TEXTSPLIT(y,,"-",))*1),1),"00000"))))

如何理解这个函数?刚刚接触有点不好理解,真的不好理解,我先举个简单例子看看。

下例中,要将A列的【姓名】与B列的【成绩」拼接,中间用冒号分隔列。

🔒

reduce+lambda 通过将lambda应用于每个值,并在累加器中返回总值,将数组减小为累积值

语法:reduce(initial_value,array,lambda(accumulator, value))

Reduce(初始值,循环的数组,lambda(x,y,运算公式))

非常非常重要:lambda中的x,y分别是指什么?

  • x 代表初始值

  • y 代表循环的数组

  • 运算公式就是对数组进行计算的公式

上面红色字体,比对实例公式可以好好悟一下:

  • 初始值:"Name&score"

  • 循环的数组:A2:A5

  • 运算公式:y&":"&OFFSET(y,0,1,1,1)

😭但是,最后的结果了“赵六:75”

实际上,公式进行了5次的运算:

第一次:A1&B1    =>姓名:成绩

第二次:A2&B2   =>张三:78

第三次:A3&B3   =>李四:86

第四次:A4&B4   =>王五:95

第五次:A5&B5   =>赵六:75

每一次计算的结果都赋值给了x,替代了初始值,然后我们又进入了下一轮对y的计算,最后结果只显示了最后一次的计算结果。

此时,我们需要将每一次结果通过vstack()进行拼接即可。公式如下:

这样,是不是就将结果拼接起来了。

  1. 回到本文主案例数据。我们先简化一下,假定A列只有三个数据,如下图:

 第一次循环:就是用空格作为分拆符将A列数据拆分成B列的结果,由此可见A1、A2单元格数据不变,A3单元格数据被拆成了二行;

第二次循环:就是将B1:B4的数据,逐行按reduce(x,y,lambda(x,y,运算公式))进行运算。

  • reduce中的b,其实就是第一次循环的结果数据(B1:B4);

  • lambda中的y,其实就是reducy中的b,即B1:B4中的结果。一层层传导而已。

  1. 最后,大家看到let作为最外边一层函数进行了包装。

let:官方解释为,将计算结果分配给名称,可用于通过定义公式内的名称来存储中间计算结果和值。这些名称仅在let函数作用域内适用。

这个例子理解下:

a=11,b=a+4=11+4=15,c=b/3=15/3=5,结果返回c,即返回5

a=11,b=a+4=11+4=15,c=b/3=15/3=5,结果返回b,即返回15(虽然有c的计算,但结果返回的是b)

上述二个例子已经非常非常好理解以及举一反三了。即let中的参数一定是奇数个,两两为一对,最后一个即返回的结果,当然也可以对最后的结果套上函数,如下:

同理,回到本贴子主案例中。


如果你要练习,贴在这里方便你复制:

=LET(

a,A1:A3,

b,DROP(CLEAN(REDUCE("Number",a,LAMBDA(x,y,VSTACK(x,FILTER(TEXTSPLIT(y,," ",),TEXTSPLIT(y,," ",)<>""))))),1),

c,REDUCE("Number",b,LAMBDA(x,y,VSTACK(x,TEXT(SEQUENCE(MAX(TEXTSPLIT(y,,"-")*1)-MIN(TEXTSPLIT(y,,"-")*1)+1,1,MIN(TEXTSPLIT(y,,"-")*1),1),"00000")))),

DROP(c,1))

🔑

总结

上述函数的用法,你掌握了吗?

以上,除了reduce函数wps暂未发布外,其他12个函数均可使用。期望WPS的遥遥领先2024年全面爆发。

案例数据下载:https://pan.baidu.com/s/1DCxFFswDE8iGXjhS5zeAeQ?pwd=kphf

提取码: kphf

广东省
浏览 1242
2
11
分享
11 +1
11
2 +1
全部评论 11
 
Kobin
WPS更新了能用Reduce函数吗,哪个版本的?
· 广东省
回复
 
uibpqds
仰望大佬,牛逼!!!
· 河南省
回复
 
Mustang
有难度
· 贵州省
回复
 
亂雲飛渡
· 广东省
回复
 
卡斯摩
【沙沙小超】提醒了我,textsplit的四参有忽略单元格,因此,filter退出战役,提前回家过年,参与战役的仍有12位战友。 简化后公式如图,谢谢各位大佬指正。
· 广东省
回复
 
庄
果然华流才是最屌的。。对不起,走错片场了,大佬牛批
· 广东省
回复
 
苏子龙
Hanna你好!content://com.huawei.ohos.inputmethod.provider.files/download/expression/327-1.0.0/Celia-20220923-161642-641-RK6F-snap-watermark.png
· 江苏省
回复
 
邓华
邓华

WPS函数专家

楼主整理的很详细,点赞。
· 广东省
回复
 
WPS1
谢谢楼主.大家好 我是Hanna
· 广东省
回复
邓华
邓华

WPS函数专家

星星你好
· 广东省
回复