分类：Excel 2016实用技巧大全

由于职工在一天中可能生产多种产品，每个组别又包含多个职工，因此在图 7.42 所示的生产表中每个姓名和组别都会出现多次。现要求对此产量表按组别和姓名分类汇总，既能看到每人的产量合计又能看到每个组别的产量合计。

解题步骤

分类汇总工具一次只能设置一个汇总条件，但是允许对同一组数据执行多次汇总，因此本例可以利用分类汇总工具实现双条件汇总，步骤如下：

1.选择A1单元格，然后单击功能区的“数据”→“分类汇总”，弹出“分类汇总”对话框。

2.将分类字段设置为“组别”，将汇总方式设置为“求和”，将汇总项设置为“产量”，然后单击“确定”按钮执行汇总，图7.43是选项设置界面，图7.44则是分类汇总结果。

3.再次单击功能区的“数据”→“分类汇总”，然后将分类字段改为“姓名”，汇总方式和汇总项保持不变，并取消选择“替换当前分类汇总”复选框，最后单击“确定”按钮。图 7.45是二次汇总的设置界面，图7.46则是汇总结果。

在图 7.46 中，每个职工的产量都有汇总，每个组别也有汇总，最后还有一个所有产量的汇总。

知识扩展

1.分类汇总时一次只能设置一个汇总字段，汇总项的数量则不限制，可以同时设置多个汇总项。

2.在设置二次汇总时，指定汇总字段和汇总项后一定要取消选择“替换当前分类汇总”复选框，否则第一次的汇总结果不会保留下去，从而只能实现单条件汇总。

3.使用数据透视表也可以实现按姓名和组别双条件汇总产量，而且不影响数据源，设置界面与结果如图7.47所示。

组合键＜Alt+=＞只能对上方区域的数值求和，对于图 7.33 这种对下方数值批量求和的需求，有没有办法一次性完成？样表中只有3个待合计单元格，实际工作中有可能数百个、上千个。

解题步骤

组合键＜Alt+=＞只能对上方区域的数值求和，因此要对下方区域求和必须按此思路处理：通过辅助列添加升序的序号，并以序号为基准降序排序，从而让数据源倒序存放，此时借助组合键＜Alt+=＞对上方区域求和。最后将公式转换成值，并恢复为原来的排列顺序。

具体操作步骤如下。

1.在D2：D3分别输入数值1和2，然后选择D2：D3，并双击D3单元格右下角的填充柄，从而将自然数序列向下填充到D18。

2.选择D2：B18（从区域右上角选择并拖到左下角），然后单击功能区的“开始”→“排序和筛选”→“降序”，从而使数据源倒序显示，效果如图7.34所示。

3.单击功能区的“开始”→“查找和选择”→“定位条件”，在对话框中选择“空值”，然后单击“确定”按钮执行定位，设置界面如图7.35所示。

4.按组合键＜Alt+=＞生成求和公式，效果如图7.36所示。

5.复制C列，然后通过“选择性粘贴”→“值”的方式将C列的公式转换成数值。

6.再次选择D2：B18区域，然后单击功能区的“开始”→“排序和筛选”→“升序”，从而使数据源还原为原来的顺序，此时产量表将显示为图7.37所示的效果。

7.删除D列数据。

知识扩展

1.工作中经常遇到某些工作需求无法在 Excel 中找到对应的工具解决的情况，此时可以变通解决思路，使用多个工具配搭应用来实现，或者添加辅助区域协助完成，本例中两个办法都用到了。

2.本例中第4步产生的公式用于计算上方的数值区域之和，当通过排序工具将区域的值还原为原来的顺序时，公式仍然计算公式所在单元格上方的区域，而不会相应地转向，因此在排序前需要将公式转换成值，以确保排序后公式的运算结果不会出错。

图7.30中C列、E列和G列的数据都需要求和，用什么办法实现快速的批量求和呢？

解题步骤

Excel支持几百种运算函数，由于用得最多的是求和，因此微软为求和设计了快捷键，本例可以按以下步骤实现需求。

1.选中C12单元格。

2.在按住Ctrl键的同时再选择E21和G12单元格。

3.按下组合键＜Alt+=＞，在选区的三个单元格中会自动产生求和公式，运算结果如图7.31所示。

知识扩展

1.组合键＜Alt+=＞用于快速生成求和公式，其中SUM函数的参数来自公式所在单元格上方的数值区域，在本例中，C12单元格上方C2：C11区域中是数值。

C1属于公式上方的单元格，但单元格中只有文本，因此Excel只调用C2：C11区域作为求和对象。

2.＜Alt+=＞只对上方最近的一个数值区域有效，如果中间有一个文本单元格，那么文本单元格上方的区域不会参与计算。例如，本例中C5单元格输入“休假”，那么在选中C12单元格并单击＜Alt+=＞组合键后只能对C6：C11区域求和。

3.＜Alt+=＞组合键不仅能对多列批量求和，还可以对同一列的多段数据实现分段小计，同时执行总计。以图7.32为例，B4、B8、B14需要小计，B15需要总计，4个需求可以同时完成，选中这4个单元格后按下＜Alt+=＞组合键即可（见图7.32）。

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