专栏名称: 秋叶Excel

和秋叶一起学Excel，免费获取Excel模板大全、插件合集！还有Excel真人视频、图文教程，助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人！

8个Excel合并单元格技巧，个个都牛X！

秋叶Excel · 公众号 · · 2024-07-23 11:30

正文

点击蓝字【秋叶 Excel】 👆

发送【方方】

免费领职场人都在用的方方格子插件！

本文作者：赵骄阳

本文编辑：卫星酱

Hi，我是偏爱函数公式，爱用 Excel 图表管理仓库的大叔 Mr 赵~

在制作报表的过程中，我们都知道，为了增强表格的可视性和可读性， 合并单元格 是常用的操作。

然而，这种操作往往会为后续的查询和统计工作带来不便。

因此，我为大家列举了以下 8 个案例 ，展示在合并单元格的情况下，如何实现 查询、提取、统计数据 。

干货满满，建议收藏！

查询带有合并单元格的数据

如下图，根据 E3 单元格的姓名，来查询 B 列对应的部门。

❶ 首先用 XLOOKUP 函数查找 E3 单元格的姓名在 C 列中的位置，返回对应 B 列的引用（结果为 B6 单元格的引用）：

=XLOOKUP(E3,C:C,B:B)

❷ 然后用冒号将 B1 与这个引用连接，得到一个单元格区域的引用（结果为单元格区域 B1:B6）。

=B1:XLOOKUP(E3,C:C,B:B)

❸ 最后用 LOOKUP 函数，找到单元格区域 B1:B6 的最后一个文本内容，也就是结果：「外拓部」。

=LOOKUP(    "々",    B1:XLOOKUP(E3,C:C,B:B))

以上这些干货，在 《秋叶 Excel 3 天集训营》 还有更多！

除此之外，现在加入，不仅有专业老师手把手教你，还有助教随时答疑……助你更快掌握 Excel 使用技巧！

秋叶 Excel 3 天集训营

原价 99 元

现在 仅需 1 元

报名还送

35 个常用函数说明

赶紧点击下方小程序报名吧！

👇👇👇

↑↑↑

报名后，自动弹出班主任微信

扫码添加，激活课程

提取带有合并单元格的数据

如下图，从带有合并单元格的 B 列提取部门。

D3 单元格的公式：

=TOCOL(B3:B11,1)

当 TOCOL 函数的第二个参数为 1 时，表示忽略空白单元格。利用这一特性，我们就能从合并单元格中忽略空白提取信息。

在合并的单元格中添加序号

如下图，根据 C 列的部门，在 B 列添加序号。

同时选中 B3:C11 单元格区域，在编辑栏输入公式后按【Ctrl+Enter】：

=COUNTA(C$2:C2)

在合并的单元格中计数

在下图的 D 列中，统计各部门的人数。

同时选中 D3:D11 单元格区域，在编辑栏输入公式后按【Ctrl+Enter】：

=ROWS(3:$11)-SUM(D4:D11)

注意，SUM 函数的参数起始位置是 D4，也就是公式所在单元格的下一个单元格。

在合并单元格中求和

如下图，要在 C 列的合并单元格中，统计 B 列对应的数据之和。

同时选中 C3:C12 单元格区域，在编辑栏输入公式后，按【Ctrl+Enter】：

=SUM(B3:B12)-SUM(C4:C12)

注意第二个 SUM 的参数起始位置 C4，是公式所在单元格的下一个单元格。

在合并的单元格中求最值

如下图，要在 C 列的合并单元格中，统计 B 列与之对应的最大值。

❶ 首先用 MATCH 函数求出各个合并单元格所在的行数：

=MATCH(1=0,C4:C$13="",-1)

❷ 然后用 OFFSET 函数根据得到的行数扩展单元格区域：

=OFFSET(   B3,,,   MATCH(1=0,C4:C$13="",-1))

❸ 最后用 MAX 函数求出每个区域的最大值：

=MAX(    OFFSET(        B3,,,        MATCH(1=0,C4:C$13="",-1)    ))

在合并单元格中求平均值

如果需要计算每个合并单元格的平均值，只需要在外面嵌套一个 AVERAGE 函数：

=AVERAGE(    OFFSET(        B3,,,        MATCH(1=0,C4:C$13="",-1)    ))

在合并单元格中排名

如下图，根据每个员工的销售量，对各个店铺的销量进行排名：

❶ 首先用 SCAN 函数将 B 列的店铺名进行向上填充，结果生成一列与 D 列销量维度一致的数组：

=SCAN(,B$3:B$17,LAMBDA(x,y,IF(y="",x,y)))

❷ 再用 GROUPBY 函数以这列数组作为分组依据，对 D 列的销量进行汇总，并且设置成降序：

=GROUPBY(    SCAN(,B$3:B$17,LAMBDA(x,y,IF(y="",x,y))),    D$3:D$17,    SUM,,,-2)

❸ 然后用 TAKE 函数取数组中的第 1 列：

=TAKE(    GROUPBY(        SCAN(,B$3:B$17,LAMBDA(x,y,IF(y="",x,y))),        D$3:D$17,SUM,,,-2    ),,1)

❹ 最后用 MATCH 函数查找当前的店名在这列数组中的位置：

=MATCH(    B3,    TAKE(        GROUPBY(            SCAN(,B$3:B$17,LAMBDA(x,y,IF(y="",x,y))),            D$3:D$17,SUM,,,-2        ),,1    ),)

公式得到的结果就是各个店铺的排名：

写在最后

好了， 查询、提取、统计合并单元格的 8 个实用技巧 就分享到这里。

需要注意的是，案例中用到的部分函数只有高版本的 Excel （比如 Excel 365、2021、2024 ）或者 WPS 中才有，大家在实际使用时按需选择。

如果本篇文章对你有帮助或有所启发，欢迎 「点赞」、「分享」 和

8个Excel合并单元格技巧，个个都牛X！

正文

请到「今天看啥」查看全文