2 招解决让 VLOOKUP 无能为力的 Excel 合并表格

原文标题:《太牛 X 了!让 VLOOKUP 都无能为力的合并表格,这 2 招却能轻松解决!》

秋叶 Excel 在小 E 的认真打理下,越来越火爆。为了回馈粉丝,小 E 做了一场「直播惊喜活动」,产品限额,惊喜满满。

活动规则,每个订单 id,每个产品只能下一单。

但是小 E 在发货的时候,发现有不少粉丝下单了多个不同的产品。

这时他就在想,如果都分开发货,单纯计件的话,物流成本有点高,本着省钱原则,他就在想要不就试试合并发货。

所以,小 E 需要将同个姓名,同个手机号,同个地址的客户所购买的不同产品合并起来形成一条订单数据,进行合并发货,以节约成本。

处理后如下图,我们可以看到,小爽用了不同账号多次下单了 Excel 这本书:

PS:由于地址比较长,所以案例中我们只对同个姓名,同个手机号进行合并处理。

那么问题来了,这个处理应该怎么做?不怕,这是小 E 的强项啊。

函数方法

适用版本:Office2021,Office365,WPS2022

由于存在不同账号同一个购买者的情况下,所以我们需要先利用 Countif 函数统计不同产品的汇总个数。

=COUNTIFS($B$2:$B$18,I2,$D$2:$D$18,G2,$E$2:$E$18,H2)

后面的问题,就转变成基于姓名和手机号列合并汇总产品名称的问题。

接下来,我们进行合并发货数量的操作(合并同类项)。

UNIQUE 函数,去除姓名列和手机列的重复项

这一步,主要是去除原始姓名列和手机列的重复值,方便后面的筛选和合并。

FILTER 函数,根据姓名列和手机列筛选产品名称与汇总个数。

这一步主要通过姓名和手机列筛选产品名称 * 个数

=FILTER($I$2:$I$15&"*"&$J$2:$J$15,($G$2:$G$15=L2)*($H$2:$H$15=M2))

Filter 函数基本语法

Filter 函数是一个筛选函数,它可以将数组中条件为 True 的结果筛选出来。

=FILTER(要筛选的数组或区域,筛选条件,[是否忽略空值])

TEXTJOIN 函数,根据指定分隔符合并数据。

这一步,主要将多个数据合并为一个。

=TEXTJOIN(";",1,FILTER($I$2:$I$15&"*"&$J$2:$J$15,($G$2:$G$15=L2)*($H$2:$H$15=M2)))

Textjoin 函数的基本语法

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

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

小提示:

如果你是 Office2016 以上版本,且没有 Filter 函数,也可以使用 if 函数替代,只不过运算效率会差一点。而由于公式是数组公式,所以公式输入完成后,还需要使按数组三键【Ctrl+Shift+Enter】结束。

=TEXTJOIN(" ; ",1,IF(($G$2:$G$15=L2)*($H$2:$H$15=M2),$I$2:$I$15&"*"&$J$2:$J$15,))

如果你不想使用辅助表,也可以利用一个公式搞定!不过该方法仅局限于 Office365。

=TEXTJOIN(" ; ",1,                LET(a,FILTER($B$2:$B$18,($D$2:$D$18=G2)*($E$2:$E$18=H2)),                  MAP(                     UNIQUE(a),                    LAMBDA(t,t&"*"&COUNTA(FILTER(a,a=t)))                           )                    ))

主要原理是:

❶ 筛选指定 姓名和手机号 对应的产品名称 (filter),名称定义为 a,方便后面进一步筛选计数 (let)。

❷循环 (map) 去重后的产品名称 (unique),将每一个产品名称进一步筛选并计数 (filter,counta),用 "*" 拼接。

❸ 最后用 textjoin 分隔符合并。

函数方法灵活,不过受限于版本 。那么有没有更简单的方法 ?答案是有的,就是利用插件。

方方格子

方方格子,几乎所有版本都可以使用 。

利用它,不用辅助表,也不用函数,只需点点鼠标,就可以搞定上述需求!我们一起来看看吧。

具体步骤:

选择【方方格子】-【查找定位】-【一对多查找】。

出现一对多查找对话框。

条件:查找表中的姓名和手机号列

条件区域:数据表中的姓名和手机号列

数据区域:数据表中的产品名称列

结果存放:查询表中发货数量的第一个单元格

匹配多个结果:不扩展 ,用,连接 (重复累计)

单击【确定】按钮 ,结果一下子就出现啦~

在选项中,我们可以对查找到的多个数据,选择处理的模式。

向右扩展:就是查找到的多个数据往多个列扩展。

不扩展:可以选择连接方式以及聚合的方式(个数,总和,最大值,最小值,第一二三以及最后一个匹配)。

这里我们选择用,连接(重复累计)。

动图操作效果如下:

So easy~

最后的话

Excel 在我们的生活里扮演着很重要的角色。数据来源于业务生活,通过 Excel 这个工具发挥着更多不一样的作用。

小 E 通过观察,对订单数据进行合并处理,为老板节约了一部分物流成本。本文主要介绍两种方法:

新函数

Unique 函数将数据进行去重,

Filter 函数进行筛选符合条件的数据,

Textjoin 函数进行合并。

由于还需要对多个数据进行汇总合并,所以我们还用了个辅助表作为一个过渡。

方方格子

利用插件,不用复杂的函数,只需点点鼠标就可以搞定。方便是方便,但是由于它的封装性,肯定就远不如函数灵活啦。

由于篇幅有限,方法不局限于文章

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享