二维码 购物车
部落窝在线教育欢迎您!

批量拆分单元格内容变成多行显示的3种方法

 

作者:逍遥来源:部落窝教育发布时间:2023-06-02 15:26:11点击:11416

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

如何根据分隔符将各单元格中的内容批量拆分为多行?今天主要来教大家3种方法:PQ拆分法,空格替换提取法OFFICE365函数法。

 

如下图所示,这是关于各个部门派出的参会名单,每个部门的人员都记录在同一个单元格内。如何才能将单元格中的人员拆分到多行单元格中,对应的部门也要展示出来呢?


表格
描述已自动生成


方法一:利用PQ拆分单元格内容到多行

首先,将鼠标放在任意有数据的单元格中,点击【数据】-【来自表格/区域】,勾选【表格包含标题】,然后确定。



选中【人员】这一列,然后点击【拆分列】-【按分隔符】来拆分,然后输入分隔符【、】,展开下方的【高级选项】,选择拆分到行,然后确定。



下面,这份名单就已经拆分出来了,我们再点击【关闭并上载至】,选择现有工作表,选择A8单元格即可。


 

效果如下:


 

温馨提示:PQ仅在office2016及以上版本适用,WPA版本也暂不支持。

 

方法二:用空格替换提取来拆分单元格内容到多行

如果你的excel版本较低,无法使用PQ的话,建议大家用空格替换提取法来解决这个问题。该法是一个经典套路,用多个(如99个)空格来替换分隔符,然后完成字符数不等的数据提取。

 

步骤1:计算各部门人数

首先,在C2单元格输入公式=LEN(B2)-LEN(SUBSTITUTE(B2,"",""))+1,然后下拉公式,获取各个部门的人数。


文本
描述已自动生成

 

步骤2:获取部门名称

下面,再在D2单元格中输入公式=OFFSET($A$1,INT((ROW(A1)-1)/MAX($C$2:$C$6))+1,0),然后下拉填充。

此公式的含义是获取部门名称,重复的次数为C列的最大值。



 

步骤3:将人员名单拆分到多行

我们再在E2单元格输入公式获取人员名单,输完公式后下拉填充。

=TRIM(MID(SUBSTITUTE(OFFSET($B$1,INT((ROW(A1)-1)/MAX($C$2:$C$5))+1,0),"",REPT(" ",99)),MOD(ROW(A1)-1,MAX($C$2:$C$6))*99+1,99))


图形用户界面中度可信度描述已自动生成

 

步骤4:删除多余的单元格

最后,将多余的单元格删掉即可得到我们想要的效果。


图形用户界面, 表格中度可信度描述已自动生成

 

提示:
可以改变公式,只用三步完成操作。
步骤1相同。
步骤2在D2中输入公式并下拉填充:
=TRIM(MID(SUBSTITUTE(CONCAT(REPT($A$2:$A$6&"、",$C$2:$C$6)),"、",REPT(" ",99)),99*(ROW(A1)-1)+1,99)),

步骤3在E2中输入公式并下拉填充
=TRIM(MID(SUBSTITUTE(CONCAT($B$2:$B$6&"、"),"、",REPT(" ",99)),99*(ROW(A1)-1)+1,99))


方法2适用于低版本的excel表格,但很明显,这些公式都太长,对于新手来说着实不太友好,不利于掌握,下面再来给大家介绍一种相对讨巧的函数公式。

 

方法三:利用新函数TEXTSPLITTEXTJOIN来拆分单元格内容到多行

 

步骤1:计算各部门的人数

如下图所示,在C2单元格输入公式=COUNTA(TEXTSPLIT(B2,,"")),然后下拉填充。

 


公式含义:先用TEXTSPLIT函数将B2单元格按照分隔符“、”拆分到列,然后用COUNTA函数来计数,即完成各部门的人数统计。

 

步骤2:提取部门名称

在部门这一列,我们输入公式=TEXTSPLIT(CONCAT(REPT(A2:A6&"",C2:C6)),,"",1)


图形用户界面, 表格描述已自动生成

 

公式含义:

首先,我们使用REPT函数,即根据指定次数重复文本,函数结构=(文本,次数)

将各个部门按照各自的人数来重复,如行政部有3个人,即重复3次,中间用、连接。

再用CONCAT函数来将这一组多行多列的数据合并在一起,最后再用TEXTSPLIT函数将它们拆分到同一列中。

 

表格中度可信度描述已自动生成

 

下面,在人员这一列,输入公式 =TEXTSPLIT(TEXTJOIN("",,B2:B6),,"")


图形用户界面, 表格描述已自动生成

 

公式含义:先用TEXTJOIN函数将数据用“、”合并在同一个单元格,然后再用TEXTSPLIT来拆分。

关于TEXTJOIN函数和TEXTSPLIT函数,在之前的教程中也会大家讲解过,可以戳以下链接进行查看。

一个既能分行又能分列的文本拆分函数TEXTSPLIT

用TEXTJOIN函数查找所有符合条件的值并填写到一个单元格中

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何提取品牌信息?LOOKUP函数有绝招!

没有Textjoin函数,如何解决提取数据的问题?

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者逍遥;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。