分类：数据运算篇

图7.26中包含5人的多个科目考试成绩，其中姓名区域已经合并，对应的成绩合计区域也已经合并，现要求在D列计算每个人的成绩合计，是否有办法一次性计算完成？

解题步骤

对于这种单元格数量不相等的合并区域，任何函数都无法判断合并区域的大小，因此也无法直接计算出对应的合计值，解决方法是利用合并单元格中左上角单元格以外的单元格内容为空这个规则，通过错位求和取得差值来完成需求的思路，具体操作步骤如下。

1.选择D2：D15区域。

2.在编辑栏输入公式“=SUM（C2：C$14）-SUM（D3：D$15）”，然后按下组合键＜Ctrl+Enter＞结束，公式的计算结果如图7.27所示。

3.分别选中 C2：C4、C5：C7、C8：C9 等区域，然后在状态栏查看合计，可以验证出结论：本例公式在所有单元格的计算结果都完全正确。

知识扩展

1.本例公式“=SUM（C2：C$14）-SUM（D3：D$15）”的重点在于绝对符号$的位置，SUM（C2：C$14）中第14行已经锁定，第2行未锁定，因此它的功能是计算公式所在行到第14行的成绩之和，当公式在D2时得到的是所有人员的成绩之和917，公式在D5时则得到的是曹莽以外的所有人成绩之和671…其中最后一个合计刚好是最后一人陈琳的成绩之和131，通过图7.28可以了解表达式SUM（C2：C$14）的运算规则。

表达式SUM（D3：D$15）的计算对象是D列中第3行开始到第15行结束，由于刚好错位一行，表达式“SUM（D3：D$15）”在D13单元格时求和对象是D14：D15，此区域的合计为0，因此D14单元格的公式“=SUM（C13：C$14）-SUM（D14：D$15）”计算结果是131-0=131。

以此类推，D10单元格的公式“=SUM（C10：C$14）-SUM（D11：D$15）”等于C10：C14区域的成绩合计378减去D11：D15区域的成绩合计131，结果为247。

要理解本例的公式，正确方法是从下向上推算公式。例如，先验证 D13 的公式“=SUM（C13：C$14）-SUM（D14：D$15）=131-0=131”，然 后 验 算 D10 的 公 式“=sum（c10：c14）-sum（d11：d15）=378-131=247”，接 着 验 算 D8 的 公 式“=SUM（C8：C14）-SUM（D9：D15）=492-378=114”……图7.29能帮助读者快速理解本例的公式。

2.合并单元格无法填充公式，只能选中所有单元格、输入公式、按组合键＜Ctrl+Enter＞结束，用此3个步骤才能在多个合并单元格中批量生成公式。

图7.17所示的报表如何才能转换成图7.18所示的报表？

其中图7.18中第二列是合并参赛队员姓名，中间用顿号隔开，第三列则用于计算3个队员的平均分。

解题步骤

没有什么内置工具能一次性完成这种表格样式转换，也没有任何函数可以实现这种转换，但是用函数配合基础操作技巧则可以完成需求，具体操作步骤如下。

1.在D1：E1区域输入“参赛队员”、“综合得分（平均值）”。

2.在D2单元格输入公式“=IF（A2＜＞A1，B2㊣＂、＂㊣B3㊣＂、＂㊣B4，0/0）”，在E2单元格输入公式“=TEXT（AVERAGE（C2：C4），＂0.00＂）”。

3.选择D2：E2区域，然后双击填充柄，使其向下填充到第13列，此时D2：D13区域的公式会计算出每个班级对应的队员姓名，合并到一个单元格中，E2：E13 区域则会计算出每组队员的综合得分，效果如图7.19所示。

4.选择 D：E 区域，然后按组合键＜Ctrl+C＞复制区域，接着单击右键，在右键菜单中的粘贴选项中选择“值”，从而将D：E区域的公式转换成值。

5.单击功能区的“开始”→“查找和选择”→“定位条件”，弹出“定位条件”对话框。

6.单击“常量”，然后取消选择“数字”、“文本”、“逻辑值”复选框，设置界面如图 7.20所示。单击“确定”按钮后，Excel 会定位 D 列的所有错误值所在单元格，定位结果如图 7.21所示。

7.单击右键D2，然后从右键菜单中选择“删除”，弹出“删除”对话框。

8.将删除选项设置为“整行”，界面如图7.22所示，图7.23则是删除结果。

9.删除B列和C列，对数据区域添加边框，最终效果如图7.24所示。

知识扩展

1.&是一个运算符，用于将多个字符连接成一个字符串，例如：

=＂A＂&＂b＂——运算结果是“Ab”

=＂上海＂&2008——运算结果是“上海2008”

连接文本时，文本必须添加半角引号，连接单元格中的值或连接数字时，单元格地址和数字不需要加引号。

表达式“B2&＂、＂&B3&＂、＂&B4”表示用B2单元格的值连接顿号，再连接B3的值、连接顿号、连接B4的值。不能写成“B2&、&B3&、&B4”，也不能写成“＂B2&、&B3&、&B4＂”。

2.公式“=IF（A2＜＞A1，B2&＂、＂&B3&＂、＂&B4，0/0）”中的IF是条件判断函数，在此处用于判断 A 列当前行与上一行的字符是否相同，如果不同则合并姓名，相同则生成一个错误值。生成错误值的目的是与合并的姓名加以区分，便于后续删除错误值所在行。

3.表达式“0/0”的运算结果是错误值，当把公式转换成值后这个错误值不再是公式性质的错误值，而是常量性质的错误值。所以在定位对话框中需要选中“常量”，然后选择“错误”。

4.TEXT 函数在本例中的作用是将数值中的小数精确到两位，将第 3 位四舍五入。也可以改用Round函数来实现相同的功能。

5.合并区域中的文本还有一个更方便的函数PHONETIC，它能一次性合并任意区域中的所有文本，使用时比&运算符方便得多。例如，将A1：C1区域的所有值合并成一个字符串，使用&运算符比较麻烦，需要使用&连接每一个单元格地址，公式为“=A1&B1&C1”，而使用PHONETIC函数合并字符则可以一次性完成，区域大小不影响公式长度，效果如图7.25所示。

然而 PHONETIC 函数有较多的限制，主要体现在三方面。其一，它只能合并文本，不能合并数值；其二，如果单元格中包含公式，PHONETIC函数无法对它进行合并；其三，PHONETIC函数只能合并单元格中的文本，对于手工输入的字符串参数则无法合并，即“=PHONETIC（“大海”，“天空”）”无法执行合并，只能将它们写到单元格中，然后对单元格的值执行合并。

经常听说公式包含相对引用和绝对引用，它们的概念是什么？有何分别？在何时该选用相对引用、何时选用绝对引用呢？

解题步骤

绝对引用是指基于参照单元格的绝对地址而产生的引用，不管公式所在单元格如何变化，公式都总是引用固定单元格的值。绝对引用的标志是行号、列标前的$符号。

例如，在图7.1中D3单元格引用B2的值，由于公式“=$B$2”中列标B和行号2前面都有绝对符号$，因此将D4单元格的公式向下填充再向右填充，始终只能用引B2单元格的值，效果如图7.2所示。

相对引用是指基于参照单元格的相对位置而产生的引用，如果公式所在单元格的位置改变，引用也会随之改变。例如，在图7.3中E4单元格的公式“=B2”引用了B2的值，当把公式向下填充到E5时，公式“=B2”会变成“=B3”，效果如图7.4所示。

如果将E5单元格的公式向右填充到F5，F5单元格的公式将变成“=C3”。

以上就是相对引用的特点——将公式向任意方向填充时，引用对象也相应地变化。

形象、通俗地讲，“XX市XX镇XX街XX号大厦一楼男厕”，这属于绝对引用，不管你身在何处都可以通过这个地址找到该男厕；“出门右拐10米，再左拐5米，第一道大门进门后右手边男厕”，这属于相对引用，它基于发言人所在位置，然后指定方向从而找到新的位置，发言人在不同地址，根据此描述会产生不同的地址。

除了相对引用和绝对引用以外，还有一种混合引用，包含列相对行绝对和列绝对行相对两种，“A$5”属于列相对行绝对的混合引用，当公式横向填充时引用会相应地变化，纵向填充时则不会变化；“$A5”属于列绝对行相对的混合引用，当公式纵向填充时引用会相应地变化，横向填充时则不会变化。

当单元格中已经存在相对引用的公式时，要将它转换成绝对引用或相对引用，仅需选中公式中的单元格地址并按F4键即可，每按一次变化一次。

以上只是阐述相对引用、绝对引用、混合引用的基本概念，以及它们之间的区别。在实际应用中去展示相对引用和绝对引用可以让读者理解得更透彻。

以计算排名为例，按以下步骤操作可以深入理解相对引用与绝对引用。

1.在图 7.5 所示的工作表中的 C2 单元格输入公式“=RANK.EQ（B2，B2：B11）”，公式的计算结果为9，表明60分在B2：B11这个区域中属于降序第9名，效果如图7.5所示。

2.将C2单元格的公式向下填充到C11，公式的计算结果见图7.6。很显然，计算结果有问题，B9单元格的98和B11单元格的85并列第一名，同时有3个数值不同的单元格并列第二名。产生这种问题的根源在于 Rank.EQ 函数的第二参数使用了相对引用，导致引用对象出错，从而计算结果也出错（见图7.7）。

原本应该是计算每个单元格的成绩在 B2：B11 区域的排名，由于 Rank.EQ 函数的第二参数使用了相对引用，当公式向下填充时引用对象会由B2：B11变成B3：B12，B4：B13，…，B10：B19，B11：B20，排名时的参考对象是错的，排名结果自然就相应出错。

解决以上问题的方法是将相对引用B2：B11改成$B$11：$B$20，操作步骤如下。

3.如图7.8所示，选择C2单元格，然后在编辑栏选中B2：B11，并按下F4键，B2：B11会变成$B$11：$B$20，效果如图7.9所示。

4.将C2的公式向下填充到C11，公式的所有运算结果都不再出错，效果如下图7.10所示。

图7.10中，RANK.EQ函数的第一参数是相对引用，当公式向下填充时，B2会变成B3，B4，B5，…这符合工作需求，也正是相对引用的特点；RANK.EQ函数的第二参数$B$2：$B$11是绝对引用，因此不管公式在哪个单元格都是引用相同的对象，从而确保计算结果的准确性。

事实上，由于C2的排名公式只需要向下填充，不需要向右填充，因此绝对引用也可以改成列相对行绝对的混合引用，公式为“=RANK.EQ（B2，B$2：B$11）”。

将$B$2：$B$11变成B$2：B$11的方法是在公式中选择$B$2：$B$11，然后按下F4键即可。

知识扩展

1.当公式只需要在单个单元格中使用时，公式中的引用采用相对引用还是绝对引用对公式的计算结果没有任何影响，只有需要将公式填充到其他单元格时才需要注意引用方式。

2.当C2单元格的公式使用了相对引用时，把C2复制到D10后，公式中的引用会产生变化。如果既不想将公式中的引用改成绝对引用，又想要C2的公式复制到D10后引用对象不变，那么不能复制单元格，因该进入编辑栏复制公式，然后将公式粘贴到D10。

3.要用好相对引用和绝对引用，除了需要掌握本例中所讲的知识以外，还需要掌握每一个函数的功能，以及每个参数的含义，否则不知道哪一个参数需要用相对引用，哪一个参数一定不能用相对引用。