【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) |
首先我们讲解【姓名】一列的函数为什么要这么写。大家可以观察一下下面的图片，发现了吗？姓名需要重复的次数刚好是B列中分隔符（即英文状态下的逗号）的个数加1，那么该如何计算分隔符的个数呢？我们可以利用LEN函数和SUBSTITUTE函数来解决。
找到规律了吗？
我们可以求出B2:B6区域中每个单元格字符的长度，利用LEN(B2:B6)即可返回字符长度的数组。接下来我们利用SUBSTITUTE函数，将B2:B6区域中的英文逗号替换为空字符，然后计算替换后的字符串的长度，即LEN(SUBSTITUTE(B2:B6,",",""))，此时将原来字符的长度减去替换后的字符串的长度，即可得到分隔符的个数，我们最后再对结果加1，即可返回需要重复的姓名的次数。
求姓名重复的次数
接下来我们利用REPT函数对字符串进行重复，注意在使用REPT函数时，我们需要将字符串后面加入&","，得到一个字符串加英文逗号的连接，这样以便于我们使用TEXTSPLIT函数对字符串进行拆分。
按照求出的重复次数重复字符串
接下来，我们使用TEXTJOIN函数对这个数组进行连接，得到一个完整的字符串：
最后一步就可以使用TEXTSPLIT函数，以英文逗号为拆分【按行拆分】，同时忽略空单元格，即可得到【姓名】列所示的效果。
利用TEXTSPLIT函数对字符串进行拆分
对【姓名】一列按照B列学科的个数拆分是这个方法中的重点，也是比较难考虑到的地方。
而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函数可以以列的方式将已有的数组拼接在一起，形成一个新的数组。生成的数组的行数、列数具有如下特点：
行数：每个数组参数中行计数的最大值。
列数：每个数组参数中所有列的合并计数。
它的语法是这样的：
HSTACK函数
数组参数：输入要拼接的数组区域。
关于HSTACK函数和VSTACK函数的详细使用方法，可以参考泽岩老师在社区的教程帖：
这个解法与解法1的思想类似，都是通过重组、连接、拆分的方式达成重复规定次数的目的。HSTACK函数中的第一个参数为TEXTSPLIT函数拆分形成的数组，使用CONCAT函数连接数组字符串，核心在于里面的SUBSTITUTE函数为什么要这么写。
我们先把SUBSTITUTE函数的部分单独拿出来：
| 🔔 | =SUBSTITUTE(","&B2:B6,",",";"&A2:A6&",") |
解法2：SUBSTITUTE函数
我们在【字符串】参数中填写了B2:B6区域之前添加英文状态下的逗号组合得到的数组，如下图所示：
","&B2:B6操作的运行结果
SUBSTITUTE函数的【原字符串】参数，我们将原来的英文状态下的逗号都替换掉。【新字符串】参数我们这样写：
| 💡 | ";"&A2:A6&"," |
我们在A2:A6区域前、后分别加入英文的分号和英文的逗号。得到一个这样的数组：
";"&A2:A6&","的运行结果
经过SUBSTITUTE函数的替换之后，我们得到了这样的数组：
SUBSTITUTE的执行结果
我们可以发现，每一个姓名都分配了该姓名之后拥有的学科。接下来我们将这些字符串利用CONCAT函数组合在一起，形成一个完整的长字符串。
CONCAT函数的执行结果
最后，我们就可以用TEXTSPLIT函数对其按行、按列进行拆分了。
| 🔔 | =TEXTSPLIT(CONCAT(SUBSTITUTE(","&B2:B6,",",";"&A2:A6&",")),",",";",1) |
TEXTSPLIT函数的执行结果
TEXTSPLIT的第一个参数直接填写CONCAT函数的表达式即可。第二个参数是【按列拆分】，我们需要填写逗号，因为姓名和学科是位于不同列的。第三个参数是【按行拆分】，刚才我们添加的英文分号此时就可以作为拆分依据，将每一个姓名、学科放在不同行。
经过上面的操作，我们就得到了F列和G列所示的效果。到这里为止，对应的成绩还没有做到这样的要求。没关系，我们可以用解法1中的TEXTJOIN和TEXTSPLIT函数的结果来生成拆分后的成绩数组，接下来将步骤3中的函数表达式和解法1中第6步的函数表达式分别填写到HSTACK中的各个参数中，即可拼接成一个完整的数组。
三、总结
这个案例平时会经常遇到，在这里提供用函数解决该问题的思路供大家学习参考。解法1较为容易，易于理解，解法2需要稍微思考一下，但是用一个表达式就可以完成所有的操作。如果大家有更简单的方式，欢迎大家积极在评论区留下宝贵意见和想法，我们共同进步！
练习文档下载：
下载方法：点击上方链接，然后点击左上角的菜单栏，点击【下载】即可学习使用。
练习文档下载方式
如果大家在阅读时有所收获，那么大家可以点击小红心❤️和收藏⭐~
如果大家也有自己的头脑风暴，那么不要吝啬自己的评论💬和转发✅哦~
WPS表格系列文章指路：
WPS函数专家
创作者俱乐部成员