烦烦烦烦!同事发来这样一张Excel表,如何快速拆分整理?
今天主要是来给大家分享一篇关于数据处理的技巧。
即将单元格中的内容按照分隔符拆分为多行显示。
效果如下:

今天主要来教大家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表格,但很明显,这些公式都太长,对于新手来说着实不太友好,不利于掌握,下面再来给大家介绍一种相对讨巧的函数公式。
方法三:利用新函数TEXTSPLIT和TEXTJOIN来拆分单元格内容到多行
步骤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函数,在之前的教程中也会大家讲解过,可以戳以下链接进行查看。
TEXTJOIN,Excel最强大的文本连接函数
千万别用"分列"拆分数据了,试试这个新函数,效率太高了!
这几个免费网站,你肯定会感兴趣的
hello大家好,这里是假期也有要爆肝更新的摸鱼塘塘主老Y。看在老Y假期也在“加班”的份上,要个点赞和推荐不过分吧。大家的推荐和点赞是老Y前进的动力。今天不偷懒,带来4个还不错的免费网站,起床的没起床的都可以看看,收藏起来先,希望你的收藏夹还好01、杂志阅读平台网址:/站长网2023-07-29 15:11:080000量子计算机一旦成熟,会给人类社会带来哪些改变
量子计算机的出现可能会对人类社会产生深迅速和广泛的影响。下面的文章将详细地探讨这个主题。1.密码学和信息安全传统的密码系统,如RSA和ECC,基于数学问题的困难性(如大数分解和离散对数问题)。然而,这些问题对于量子计算机来说并不困难。Shor的算法就是一个能在量子计算机上有效分解大数的算法。因此,一旦量子计算机成熟,现有的许多密码系统可能就无法保护信息安全了。0000企业数字化转型的技术困局
0000免翻,几个让人惊喜的网站!
hello大家好,这里是日常爆肝更新的摸鱼塘塘主老Y。感觉这周太漫长,看了下日历,原来不是感觉,本来这周就多上了一天班,伟大的调休。最近更新动力有点不足了,各位看官老爷,多多点赞多多在看,多多推荐公众号哈,您的推荐是老Y坚持摸鱼的动力01、博客小镇网址:/站长网2023-07-29 17:26:310002ps中如何分离图像色调,及什么是图像色调?
图像色调是指图像中的色彩整体效果,包括色调、亮度和饱和度等。在PS中,可以通过分离图像色调来对图像进行调整和优化。具体步骤如下:打开需要调整的图像,在图层面板中将背景图层复制一份。选择复制的图层,在菜单栏中选择“图像”->“模式”->“分离颜色”。在弹出的“分离颜色”对话框中,选择“CMYK”模式,并勾选“色调”复选框,取消其他复选框的勾选状态。站长网2023-07-27 12:10:580000