【WPS表格案例】使用函数对数据按照出现次数拆分

刘航
刘航

创作者俱乐部成员

💡

大家好!今天给大家带来的是函数拆分表格内容的一个实际案例。这个案例在菁培班学习时有见到过,前几天在社区的活动赛事群也发现有老师提出了这个问题,同时有很多老师提供了简单的解法,因此在社区发帖做一个汇总,希望大家也能一起学习。

一、案例引入

如下图所示,有这样的一张的表格,记录了不同同学参加的竞赛科目和成绩。现在想要通过函数,将左侧区域的表格转换成右侧规范样式的一维表,那么我们该怎么做呢?本文章将会分享三种解法供大家学习,也为解决这一类问题提供一些新思路和想法。

案例引入

二、函数解决

我们一个一个解决,先将A列和B列的内容变为F列和G列的样式,然后我们就可以以此类推,把C列变成要求的H列展示的内容。其实B列和C列的处理是非常容易的,以下解法的重点都是围绕A列重复次数的确定展开的。

解法1:重复文本+文字处理函数

这个函数的结果略微长一些,我们在这里使用到了LEN、SUBSTITUTE、TEXTJOIN、TEXTSPLIT、REPT等函数,并且在三个单元格输入不同的函数表达式,下面先给大家展示下结果:

姓名一列的结果

学科一列的结果

成绩一列的结果

接下来我们逐一进行讲解,B列和C列由于原理相同,在这里以B列为例给大家说明。

让我们先看一下姓名的拆分是如何进行的:

📌

=TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A2:A6&",",LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,",",""))+1)),,",",1)

  1. 首先我们讲解【姓名】一列的函数为什么要这么写。大家可以观察一下下面的图片,发现了吗?姓名需要重复的次数刚好是B列中分隔符(即英文状态下的逗号)的个数加1,那么该如何计算分隔符的个数呢?我们可以利用LEN函数和SUBSTITUTE函数来解决。

找到规律了吗?

  1. 我们可以求出B2:B6区域中每个单元格字符的长度,利用LEN(B2:B6)即可返回字符长度的数组。接下来我们利用SUBSTITUTE函数,将B2:B6区域中的英文逗号替换为空字符,然后计算替换后的字符串的长度,即LEN(SUBSTITUTE(B2:B6,",","")),此时将原来字符的长度减去替换后的字符串的长度,即可得到分隔符的个数,我们最后再对结果加1,即可返回需要重复的姓名的次数。

求姓名重复的次数

  1. 接下来我们利用REPT函数对字符串进行重复,注意在使用REPT函数时,我们需要将字符串后面加入&",",得到一个字符串加英文逗号的连接,这样以便于我们使用TEXTSPLIT函数对字符串进行拆分。

按照求出的重复次数重复字符串

  1. 接下来,我们使用TEXTJOIN函数对这个数组进行连接,得到一个完整的字符串:

  1. 最后一步就可以使用TEXTSPLIT函数,以英文逗号为拆分【按行拆分】,同时忽略空单元格,即可得到【姓名】列所示的效果。

利用TEXTSPLIT函数对字符串进行拆分

对【姓名】一列按照B列学科的个数拆分是这个方法中的重点,也是比较难考虑到的地方。

  1. 而B列和C列的合并再拆分就很简单了。

📌

=TEXTSPLIT(TEXTJOIN(",",TRUE,B2:B6),,",")

=TEXTSPLIT(TEXTJOIN(",",TRUE,C2:C6),,",")

第一步,我们使用TEXTJOIN函数,以英文逗号为连接符对B列的内容进行连接;

第二步,用TEXTSPLIT函数以英文逗号为分隔符按行拆分,同时忽略空单元格,即可得到【学科】和【成绩】一列所示的结果,这里不再进行详细的讲解啦~

解法2:每个对应的学科匹配一个姓名

这个方法借鉴了新路老师在社区活动群的解法分享,非常感谢新路老师的启发。第一个解法需要在三个单元格填三个表达式,而这种解法只需要在一个单元格就可以搞定了。仅仅看这个标题,可能不好理解是什么意思。相信通过下面对解法的讲解,大家就能逐渐明白标题的含义了。

先给大家看一下公式和结果,然后再对函数进行讲解:

解法2的运行结果

公式如下:

📌

=HSTACK(TEXTSPLIT(CONCAT(SUBSTITUTE(","&B2:B6,",",";"&A2:A6&",")),",",";",1),TEXTSPLIT(TEXTJOIN(",",,C2:C6),,","))

下面我将从外部的HSTACK函数讲起,然后讲清楚每一个参数的含义。

首先,HSTACK函数可以以列的方式将已有的数组拼接在一起,形成一个新的数组。生成的数组的行数、列数具有如下特点:

  1. 行数:每个数组参数中行计数的最大值。

  1. 列数:每个数组参数中所有列的合并计数。

它的语法是这样的:

HSTACK函数

数组参数:输入要拼接的数组区域。

关于HSTACK函数和VSTACK函数的详细使用方法,可以参考泽岩老师在社区的教程帖:


这个解法与解法1的思想类似,都是通过重组、连接、拆分的方式达成重复规定次数的目的。HSTACK函数中的第一个参数为TEXTSPLIT函数拆分形成的数组,使用CONCAT函数连接数组字符串,核心在于里面的SUBSTITUTE函数为什么要这么写。

  1. 我们先把SUBSTITUTE函数的部分单独拿出来:

🔔

=SUBSTITUTE(","&B2:B6,",",";"&A2:A6&",")

解法2:SUBSTITUTE函数

我们在【字符串】参数中填写了B2:B6区域之前添加英文状态下的逗号组合得到的数组,如下图所示:

","&B2:B6操作的运行结果

SUBSTITUTE函数的【原字符串】参数,我们将原来的英文状态下的逗号都替换掉。【新字符串】参数我们这样写:

💡

";"&A2:A6&","

我们在A2:A6区域前、后分别加入英文的分号和英文的逗号。得到一个这样的数组:

";"&A2:A6&","的运行结果

经过SUBSTITUTE函数的替换之后,我们得到了这样的数组:

SUBSTITUTE的执行结果

  1. 我们可以发现,每一个姓名都分配了该姓名之后拥有的学科。接下来我们将这些字符串利用CONCAT函数组合在一起,形成一个完整的长字符串。

CONCAT函数的执行结果

  1. 最后,我们就可以用TEXTSPLIT函数对其按行、按列进行拆分了。

🔔

=TEXTSPLIT(CONCAT(SUBSTITUTE(","&B2:B6,",",";"&A2:A6&",")),",",";",1)

TEXTSPLIT函数的执行结果

TEXTSPLIT的第一个参数直接填写CONCAT函数的表达式即可。第二个参数是【按列拆分】,我们需要填写逗号,因为姓名和学科是位于不同列的。第三个参数是【按行拆分】,刚才我们添加的英文分号此时就可以作为拆分依据,将每一个姓名、学科放在不同行。

  1. 经过上面的操作,我们就得到了F列和G列所示的效果。到这里为止,对应的成绩还没有做到这样的要求。没关系,我们可以用解法1中的TEXTJOIN和TEXTSPLIT函数的结果来生成拆分后的成绩数组,接下来将步骤3中的函数表达式和解法1中第6步的函数表达式分别填写到HSTACK中的各个参数中,即可拼接成一个完整的数组。

三、总结

这个案例平时会经常遇到,在这里提供用函数解决该问题的思路供大家学习参考。解法1较为容易,易于理解,解法2需要稍微思考一下,但是用一个表达式就可以完成所有的操作。如果大家有更简单的方式,欢迎大家积极在评论区留下宝贵意见和想法,我们共同进步!


练习文档下载

【WPS表格案例】使用函数对数据按照出现次数拆分

下载方法:点击上方链接,然后点击左上角的菜单栏,点击【下载】即可学习使用。

练习文档下载方式


如果大家在阅读时有所收获,那么大家可以点击小红心❤️和收藏~

如果大家也有自己的头脑风暴,那么不要吝啬自己的评论💬和转发哦~

WPS表格系列文章指路:

感觉能消化吗?
6 (50%)
略微困难
2 (50%)
8人参与 投票已截止
黑龙江省
浏览 1074
4
20
分享
20 +1
12
4 +1
全部评论 12
 
浩月
· 四川省
回复
 
小落
打卡
· 安徽省
回复
 
sunny
打卡
· 天津
回复
 
Mustang
要学废了
· 贵州省
回复
 
wAT222
打卡
· 湖北省
回复
 
那谁谁ృ༊゜
打卡
· 广东省
回复
 
爱丁堡1382081816
打卡
· 河南省
回复
 
张俊
张俊

社区优秀创作者

666,跟着航哥不迷路,航哥让我们上高速!
· 上海
回复
 
亂雲飛渡
· 广东省
回复
 
答案
都太棒了~
· 浙江省
回复
 
清华学弟任泽岩
清华学弟任泽岩

社区优秀创作者

刘航老师的教程每次都写得非常详细,分步+分步结果这样的组合呈现特别适合我学习理解。
· 辽宁省
2
回复
 
Boyuan
清楚详细,非常的好。喜欢方法二,在名称管理器中用Lambda自定义了一个函数,很好用: reDimension=LAMBDA(rpt_rng,sep_rng,sep,TEXTSPLIT(CONCAT(SUBSTITUTE(sep&sep_rng,sep,";"&rpt_rng&sep)),sep,";",1))
· 河南省
1
回复