点击蓝字【秋叶 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 天集训营

报名还送

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 中才有，大家在实际使用时按需选择。

如果本篇文章对你有帮助或有所启发，欢迎「点赞」、「分享」和「在看」哦，你们的支持是我分享的动力~

如果你想学习更多的 Excel 函数知识，掌握更多提高办公效率的小技巧。

欢迎你加入秋叶 Excel 3 天集训营~每天学习大概 30 分钟，名师教学，助教陪伴，还有练习文件助你巩固知识点！

秋叶 Excel 3 天集训营

赶紧点击加入吧！

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

扫码添加，还可领取：

35 个常用函数说明

发送【6】

免费下载 秋叶 Excel 6 年精选文章合集