学徒必备技能：解决合并单元格问题

情境设置

一般情况下，更愿意使用的数据源是以行为记录的，但是每个人的需求不一样，在工作中常常遇到记录中有合并的单元格，这种数据表由于合并单元格的存在无论是排序、计算、拆分都给使用者带来了不小的困难，本项目的主要目的就是解决此方面的问题，如图7-33所示。

图7-33　

职业能力目标及标准

在工作，常常会遇到数据源中包含有合并单元格的情况，学徒应该学会解决合并单元格编号、计数、排序、求和、合并单元格的拆分等基本问题。

知识锦囊

合并单元格

其实不管是关于合并单元格什么类别的问题，首先都得理解合并单元格的基础知识。只有理解之后才能明白相关公式设置的目的，以后再遇到单元格其他问题才有解题的思路。

从图7-33中可以看出，A列与C列数据完全相同，不同的是C列将合并单元格进行了拆分，拆分完毕后发现每个合并单元格在拆分后只有第一行保留了原来的数据，其他行则为空，并没有任何数据，在解决合并单元格的问题上就一定要记住这个特点。在今后的操作过程中要经常用到这个特点。接下来开始进行训练，请根据图7-34完成序号填充、组内序号填充、分组（城市）计数、分组汇总等任务。

图7-34　

1.序号填充

（1）解题思路：要完成此任务可使用COUNTA函数，使用时注意COUNTA函数与COUNT函数的区别。由于COUNTA函数不会将含空格的单元格进行统计，所以它只会统计每个合并单元格里含数据的单元格数，利用这个特点，将统计的范围进行设置得可以得到需要的序列号。

（2）解题步骤：选择A2：A14单元格，在公式编辑栏中输入公式“=COUNTA（B2:$B$2）”，按住“Ctrl+Enter”键结束公式的编辑，完成序号填充的任务。

2.组内序号填充

（1）解题思路：还是得从合并单元格的特点入手，在前面学习过如果将合并单元格进行拆分，那么数据就只会保留在第一行，其余行则为空值。也就是说将不为空的第一行确定序号为1，余下空行逐行加1即可。

（2）解题步骤：选择E2：E14单元格，在公式编辑栏中输入公式“=IF（A2<>〞〞，1，E1+1）”，按住“Ctrl+Enter”键结束公式的编辑，完成组内序号填充的任务。

3.分组计数

（1）解题思路：分组计数的思路是用总的计数值减去除自身单元格以外的计数值便可以得到。

（2）解题步骤：选择F2：F14单元格，在公式编辑栏中输入公式“=COUNTA（C2:C14）-SUM（F3:F14）”，按住“Ctrl+Enter”键结束公式的编辑，完成分组计数的任务。

4.分组汇总

（1）解题思路：分组汇总任务的解决与分组计数的思路是完全一样的，也是用总的销售额减去除自身以外的销售额便可得出结果。

（2）解题步骤：选择G2：G14单元格，在公式编辑栏中输入公式“=SUM（D2:D14）-SUM（G3:G14）”，按住“Ctrl+Enter”键结束公式的编辑，完成分组汇总的任务，如图7-35所示。

(www.zuozong.com)

图7-35　

5.数据查找

合并单元格数据的查找难度比较大，因为合并单元格数据只存在第一行，其余行的数据为空值，这就给数据的查找带来很大的难度。如图7-36所示。

图7-36　

如图7-36所示，要根据“公司名称”“部门”查询姓名，这与项目三“查询的精髓”中的多条件查询非常相似，其实在这里也可以采取前面教过的办法。不同的是，由于A列“公司名称”的数据是合并单元格，增加了查询的难度，对于这种情况也可以采取两个处理办法。一是函数法，利用OFFSET函数来确定动态的查询范围；二是辅助列法，利用辅助列的方法来解决问题。

（1）函数法

先来看看第一种方法所使用的函数公式“=VLOOKUP（F2，OFFSET（A1，MATCH（E2，$A$1:$A$13，0）-1，1，3，2），2，0）”，下面逐步来解释这个函数公式的意义。首先，利用“MATCH（E2，$A$1:$A$13，0）”在A列查找公司名称所在的行，接着用函数“OFFSET（A1，MATCH（E2，$A$1:$A$13，0）-1，1，3，2）”来确定VLOOKUP函数的查找范围；然后，用VLOOKUP函数在动态部门中查找学徒的姓名，如图7-37所示。

图7-37　

（2）辅助列法

增加辅助列的目的就是增加一列，将合并单元格里不含数据的空白单元格用相应的数据填满。增加辅助列的方法很多，在这里借用行号使用函数来建立辅助列，在E2单元格中输入公式“=OFFSET（A2，-MOD（ROW（A2）+1，3），）”，然后拖拉句柄，将公式复制到E列其他单元格中，获得辅助列，如图7-38所示。

图7-38　

当辅助建立完毕后，就可以用前面学过的多条件查询的知识来解决这个问题，无论是VLOOKUP、LOOKUP还是INDEX都可以完成这个任务，在这里使用的是LOOKUP函数，即“｛=LOOKUP（1，0/（$E$2:$E$13&B2:B13=F2&G2），C2:C13）｝”。

（6）合并单元格拆分填充

有时候需要将合并的单元格进行拆分，如图7-38所示，需要将A列的数据拆分为E列数据的样式。选择A2：A13，点击“合并单元格”按钮，将单元格拆分，按“Ctrl+G”快捷键调出定位条件弹窗，选择“空值”选项（如图7-39所示），点击“确定”按钮，然后在公式编辑栏中输入“=A2”，按“Ctrl+Enter”键结公式编辑，完成任务。

图7-39　

技能训练准备

1.具备一定的Excel函数运用基础。

2.熟悉VLOOKUP/MATCH/IF/OFFSET/COUNTA函数的使用。

3.熟悉使用Excel中的定位功能。

技能训练步骤

本项目内容较多，建议评估师根据学徒的水平自行选择任务驱动法、演示法、分组讨论法等教学方法。