说明书大全网 - 海量说明书在线查询
手机版  |  网站地图

excel重复数据合并汇总(3个超简单的合并同类项方法)


怎么将不同公司对应的产品,合并起来,并用顿号连接?

excel重复数据合并汇总(3个超简单的合并同类项方法)(1)

整理出来的表格效果如下图所示:

excel重复数据合并汇总(3个超简单的合并同类项方法)(2)

很明显,其中涉及到的知识点是:去除重复值,合并同类项。

下面我们就来看一下这位同学的需求,进一步的了解合并同类项的做法~

这位同学给了我一张表(左表)让我统计一下人员购课情况,所以现在我需要把表格转换为右表的形式~

excel重复数据合并汇总(3个超简单的合并同类项方法)(3)

这个问题,本质上就是合并同类项。

本文将介绍合并同类项的三种方法:

❶ Office 2019 以及以上版本——Textjoin 连接法。

❷ Office 2013 以及以上版本——Powerquery(PQ)分组依据功能。

(Office 2013 版本的小伙伴可以在后台回复【插件】自行安装,Office 2016 以及以上版本的 Excel 自带 PQ 插件)。

❸ 适合 Office 2007 及以上、WPS 2016 及以上版本——插件法。

01

方法一:Textjoin 函数法

Textjion 函数适用的版本:WPS 2019、Office 2019、Office 365。

Textjion 函数的做法如下:

excel重复数据合并汇总(3个超简单的合并同类项方法)(4)

公式如下:

=TEXTJOIN(" / ",TRUE,IF(E2=$A$2:$A$14,$B$2:$B$14,""))

很明显,我们要先用 IF 函数把同类项找出来,再用 Textjion 函数进行连接。

IF 函数我们都很熟悉啦,简单介绍一下 Textjoin 函数~

Textjoin 函数有三个参数。

=Textjoin(分隔符,是否忽略空值,数组/单元格区域)

如果要忽略空值就填 TRUE,不忽略空值就填 FALSE。

excel重复数据合并汇总(3个超简单的合并同类项方法)(5)

打个比方:

=Textjoin("-",TRUE,{"秋";"";"叶";"Excel"})

结果就等于:秋-叶-Excel

第二参数为 TRUE,所以数组中的空值被忽略了,后面就直接用分隔符把秋,叶,Excel 连接了。

小贴士:

❶ 该公式是一个数组公式,返回时需要按住三键【Ctrl+Shift+Enter】来执行数组运算。

❷ 公式的运算原理是:

通过姓名与姓名列之间进行逻辑判断,返回一个逻辑值,最后借助 IF 函数返回 True 相对应的值(购买的课程)。

excel重复数据合并汇总(3个超简单的合并同类项方法)(6)

这时候形成一个由空值与购课情况结合的数组,后面就用 Textjion 函数进行连接。

由于 Textjoin 函数可以忽略空值,所以对应的购买的课程就可以直接用分隔符连接起来啦~

了解了基本的函数做法后,下面我们就来介绍一下 PQ 用法。

02

方法二:PQ 分组依据功能

第一步:将表格导入 PQ 编辑器中。

选中表格-点击【自表格/区域】。

excel重复数据合并汇总(3个超简单的合并同类项方法)(7)

勾选表包含标题-点击确定。

excel重复数据合并汇总(3个超简单的合并同类项方法)(8)

此时就进入 PQ 编辑器里啦~

第二步:

选择姓名列-点击【开始】选项卡下的【分组依据】;

操作为【求和】-柱为【购买的课程】,点击确定。

此时出现错误没关系,后面我们就来改公式。

excel重复数据合并汇总(3个超简单的合并同类项方法)(9)

第三步:

更改函数公式,将 List.Sum 改为 Text.Combine 并添加分隔符参数。

excel重复数据合并汇总(3个超简单的合并同类项方法)(10)

第四步:关闭并上载,此时就完成啦~

excel重复数据合并汇总(3个超简单的合并同类项方法)(11)

看完了上面的操作,有小伙伴或许会疑问了,究竟什么是分组依据?

这里为什么要用 Text.Combine 函数?

为何出现错误值?

下面我们就来简单解释一下吧~

何为分组依据?


分组依据的功能,跟工作表中的合并计算有点类似,

也有点像 Excel 里面的数据透视表,对指定字段数据进行统计,它可以指定多个字段作为条件,也可以同时统计多个结果。

excel重复数据合并汇总(3个超简单的合并同类项方法)(12)

分组依据可以支持的统计方式包括:求和,平均值、最小值,非重复行计数和所有行。

为什么要用 Text.Combine 函数?

分组依据没有合并文本的功能,所以我们需要用到 Text.Combine 函数。

Text,是文本的意思,Combine,是结合的意思。

这个函数就是用来文本连接的!是不是感觉有点像上面说的 Textjion 函数~

Text,Combie 有两个参数:

=Text,Combie(文本序列,分隔符)

excel重复数据合并汇总(3个超简单的合并同类项方法)(13)

为何出现错误值?

分组之后的计数出现错误值的原因是:

我们的购买的课程列是一个文本,直接对文本进行求和,所以会出现错误值。

不过没关系,我们可以直接把 List.Sum 求和改为文本连接 Text.Combine 函数,后面再添加分隔符就好啦~

excel重复数据合并汇总(3个超简单的合并同类项方法)(14)

使用 PQ 法的好处在于,可以实时更新数据!

接下来,我们来看看最简单的插件做法吧~

03

方法三:插件法

这里我们用到的插件是 E 灵,用到的功能是数据分类合并。

老规矩,可以在后台回复【插件】即可获取下载。

下面就通过一个动图来看看具体操作~

excel重复数据合并汇总(3个超简单的合并同类项方法)(15)

数据合并功能只能用顿号连接,所以不是特别灵活,不过它操作简单,也不失为一个好的方法。

excel重复数据合并汇总(3个超简单的合并同类项方法)(16)

好啦~三种方法进行合并同类项你是否 get 到了呢~

总结一下,本文介绍三种合并同类项的方法:

Textjoin 和 If 函数连接同类项——Textjoin 函数可以忽略空值,并能够用分隔符连接。

PQ 分组依据——配合 Text.Combine 函数达到连接同类项的效果。

E 灵插件数据合并功能——虽然只能用顿号分隔,不过操作简单~