几招教你如何避开合并单元格的坑

朋友们有没有特别喜欢在Excel中使用合并单元格的？最初刚开始学习Excel的时候，我本人也特别喜欢使用合并单元格。随着Excel技能的不断增加，我发现，在文件中过多的使用合并单元格会对以后的计算、统计和查找工作带来非常大的麻烦。

如果你也有和我相同的经历，那么下面几条你就要仔细阅读一下，它们会帮助你有效地避免一些难题！

01

如何对合并单元格求和

如下例，我们分别要对每个月的数量求和。由于每个月对应的行数是随机的，因此常规的SUM函数是处理不好这种问题的。

选中单元格区域C2:C13，然后输入“=SUM(B2:B13)-SUM(C3:C13)”，CTRL+ENTER回车即可。

此技巧需要先选中所有单元格区域后再书写公式。为屏蔽错误，此公式还可以将单元格B13和单元格C13采用绝对引用。

02

在合并单元格编号

如果希望以月份为准填充序号，则选中A2:A13单元格区域，输入公式“=COUNTA($B$2:B2)”，CTRL+ENTER回车即可。

思路：

COUNTA函数返回区域内非空单元格的个数

数据区域$B$2:B2则是一个动态区域。随着公式的填充，区域由$B$2:B2增加到$B$2:B13

03

合并单元格计数

计数和求和都是我们经常会用到的常规操作。如何在合并单元格中计数呢？请看下面。

选中单元格区域D2:D13，输入公式“=COUNTA(B2:$B$13)-SUM(D3:$D$13)”，CTRL+ENTER回车即可。

思路：

在动态区域B2:$B$13中统计非空单元格的个数

在动态区域D3:$D$13中求和对应单元格区域的人员个数

随着公式的填充，动态区域是逐渐减少到第13行

此例的特别之处就在于，我们固定的动态区域的最后单元格，随着公式的填充，统计的区域也在逐渐减少。

04

合并单元格求平均值

首先，我们在B14单元格中输入任意文本。接下来选中单元格区域D2:D13，输入公式“=AVERAGE(OFFSET(C2,,,MATCH("*",B3:$B$14,0)))”，CTRL+ENTER回车即可。

思路：

这里用“*”通配符来构思公式是最大的亮点。

MATCH("*",B3:$B$14,0)部分含义是在数据区域中查找字符串。这里“*”代表任意的字符串，因此返回的结果为“2”。

利用OFFSET函数进行数据偏移并指定数据区域

利用AVERAGE函数球平均数

由于在合并单元格中数据都是放在左上第一个单元格中的，因此才会出现这样一个数组。这里向大家提一个小问题：为什么我们要在单元格B14中输入任意文本？

05

合并单元格筛选

对合并单元格进行筛选时，是无法筛选出全部数据的。如果希望实现正常筛选，先把合并单元格复制到其他地方，再取消源数据的合并单元格；接下来对源数据区域进行空格填充；最后利用格式刷将别处的合并单元格格式复制回来即可。请看下图。

06

合并单元格的查找

下例中，我们将通过姓名来查询部门。

在单元格E2中输入

“=LOOKUP("座",INDIRECT("A2:A"&(MATCH(D2,$B$2:$B$9,0)+1)))”并下拉即可。

思路：

MATCH函数用来返回查找员工的姓名在姓名列中的位置

INDIRECT函数返回一个动态的单元格区域，范围是从单元格A2到和姓名单元格所对应的单元格

LOOKUP查找得到部门。汉字“座”是汉字中ANSI代码比较大的字符，确保了可以查找到单元格区域中最后一个数值

详细的信息请参看帖子总结篇-LOOKUP函数实用终极帖。

文章推荐理由：

单元格合并可以是文档看起来更直观和美观，但是代价就是影响到以后的统计计算等工作。合理地利用单元格合并，规范地录入数据很重要

-END-