【社区答疑】两列数据通过一个函数公式实现同类合并(一)

张俊

WPS函数专家

要想下班早,表格必须用的好!

大家好,我是张俊。

场景

前段时间在社区看到求助者@懒得批爆大佬发帖提问,关于求助问题一 【文字组合和拆分-动态数组】将两列文字组合为符号连接的文字的问题处理方法,具体如下图所示:

  1. 问题分析

通过求助者@懒得批爆大佬的描述就可以看出,求助者@懒得批爆大佬是个高手,既对WPS表格函数非常熟悉,并且也熟练应用了动态数组功能;其实已经将左列和右列结果得出来了,只是想对已有的函数优化,想通过一个函数即可完成左列和右列函数的拼接。

其实对于两组函数的拼接,WPS表格已经有现有的函数可以完成,比如左右拼接的HSTACK函数和上下拼接的VSTACK函数,这2 个函数的特点就是可以对2组函数拼接生成新的数组,缺点就是拼接的2个函数之间不能相互引用和调用,应当是独立不相互依赖。如果左右拼接的话,就会出现“循环引用”的错误提示,且无法得到正确的结果;这种就是左侧函数无问题,右侧函数无问题,但是放在一起就有问题了。如下图所示:

求助者@懒得批爆大佬的此问题的难点就在于右列数据对于左列数据的依赖,因此如果用到左右拼接的HSTACK函数,就需要对右列的函数重新书写,且不调用和依赖左列函数。

当然了当WPS表格下个版本更新了MAP函数后,就可以通过MAP函数和LAMBDA函数来配合解决,后期待WPS表格更新新函数后,我也会出专门帖子。

我给出的思路是,左列的函数公式不变,仅仅对右侧的函数结果重新书写后在合并, 并且不用依赖左侧的数据,形成独立的。

  1. 制作方法

对于此类问题,可以观察,所有的省级单位都在一起,因此无需考虑排序问题,因此我的思路依然是使用CONCAT函数对所有的字符串拼接后,再通过TEXTSPLIT函数根据分隔符对长字符串进行拆分即可达成需求结果。

2.1错位比较

需要考虑的就是分隔符的问题,同一省级的分隔符用题目要求的顿号(、),而对于不同省级之间的分隔符要不一样(我用分号“;”),作为后期拆分行的分隔符来使用,因此就需要通过对A列的数据错位比较来判断,拿现有的数据区域A2:A460和向下错位的一个单元格区域A3:A461比较,具体公式如下:

=A2:A460=A3:A461

具体结果如下图所示:

通过结果可以发现,省份内同一个省份的都为TRUE,只有最后一个会显示FALSE,为后期的IF函数判断做好铺垫。

💡

注意:

一定要完全错位,避免因为错位不完整导致(如A2:A460与A3:A460比较),就会导致最后一个判断结果为#N/A错误,如下图所示:

2.2通过判断添加分隔符

当返回逻辑值TRUE或FALSE后,最先想到的就是通过IF函数来判断需要添加什么样的分隔符,当为TRUE时添加“”,当为FALSE时添加“;”,具体公式如下:

=IF(A2:A460=A3:A461,"、",";")

具体结果如下所示:

2.3与所需数据组合

此时,有些宝子写函数会写成=IF(A2:A460=A3:A461,B2:B460&"、",B2:B460&";"),其实可以简写,因为公式都需要与B2:B460拼接,可直接在IF函数的前面拼接即可,具体公式如下:

=B2:B460&IF(A2:A460=A3:A461,"、",";")

具体结果如下:

2.4将所有数据合并

接下来通过CONCAT函数拼接(此处也可以使用TEXTJOIN函数拼接,为了复杂函数简单化,因此使用了CONCAT函数),即可得到“合肥市、……、亳州;东城区、……、延庆区;……;台北市、……、连江县;”一长串字符串,各个省级的市区县以顿号“、”隔开,各个省级之间用分号“;”隔开,公式如下:

=CONCAT(B2:B460&IF(A2:A460=A3:A461,"、",";"))

具体结果如下图所示:

2.5将数据根据分隔符拆分

将生成的一长串字符串合肥市、……、亳州;东城区、……、延庆区;……;台北市、……、连江县;”,通过TEXTSPLIT函数根据行分隔符“;”拆分即可,公式如下:

=TEXTSPLIT(CONCAT(B2:B460&IF(A2:A460=A3:A461,"、",";")),,";",1)

具体结果如下图所示:

💡

注意:

TEXTSPLIT函数的第2个参数为列拆分符,第3个参数为行拆分符,第4个参数为是否忽略空白,如果没有输入第4个参数,则默认保留空白,因此最后一行会多出来空白单元格,具体效果如下图所示:

关于TEXTSPLIT函数的基本用法,可以参考此帖:以动态数组视角学习TEXTSPLIT函数基本用法

2.6拼接左右列函数

最后再与左列的函数通过HSTACK函数左右拼接即可,具体公式如下:

=HSTACK(UNIQUE(A2:A460),TEXTSPLIT(CONCAT(B2:B460&IF(A2:A460=A3:A461,"、",";")),,";"))

具体效果如下图所示:

  1. 问题总结

1)通过数据源可以看出来,A列相同的数据是一起的,如果数据没有在一起的话,我们还需对数据先排序后在操作

2)此方法处理此问题的最核心内容,是通过错位比较来判断是否为同一个省级单位,在后面增加不同的分隔符

3)通过TEXTSPLIT函数对拼接好的一长串字符串根据分隔符拆分

4)利用新版HSTACK函数对2个数组左右拼接完成一个公式处理数据

5)如果数据还需扩展,可以调整数据引用单元格区域来处理

都看到这里了,记得得❤️❤️❤️点赞(红心❤️❤️❤️收藏(五角星并在评论区评论“我学会了!”,您的❤️❤️❤️点赞(红心❤️❤️❤️收藏(五角星和评论是对我最大的支持!

🚩

练习文件:👉【社区答疑】将两列字符整理为连续的字符👈


个人往期帖子合集:【帖子合集】个人往期帖子合集来了!

你学了吗?
学会了!
5 (63%)
太难了!
3 (38%)
8人参与 投票已截止
上海
浏览 1474
3
14
分享
14 +1
12
3 +1
全部评论 12
 
wps新路

WPS函数专家

优秀的俊哥
· 重庆
1
回复
张俊

WPS函数专家

优秀的新路老师,坐等新路老师优秀的帖子。
· 上海
回复
 
跟着俊哥学函数 跟着俊哥学数组 跟着俊哥学思维
· 江苏省
1
回复
张俊

WPS函数专家

坐等旋哥第一帖。
· 上海
1
回复
 
懒得批爆

创作者俱乐部成员

俊老师的这个帖子说的更加详细了,各个函数之间的连接通俗易懂。 不再需要去点击fx查看函数参数解释了,点赞。
· 四川省
3
回复
张俊

WPS函数专家

谢谢求助者大佬的认可和支持,我会持续不断的努力和创作出更加有价值的作品。
· 上海
1
回复
 
志尧ZhiYao

创作者俱乐部成员

学习卡
· 江西省
1
回复
 
打卡
· 河南省
回复