分类：数据运算篇

能否根据图7.68所示的销售明细在图7.69中对每个产品的销售额分类汇总？

解题步骤

本例问题需要分两步进行，先用SUMIF函数对每本书的销量分类汇总，然后使用VLOOKUP函数引用每本书的单价，两者相乘即为图书的销售额。

1.进入汇总工作表，在B2单元格中输入以下公式：

2.双击B2单元格右下角的填充柄，将公式向下填充到A7，公式会将所有图书的销售额都瞬间计算出来，结果如图7.70所示。

知识扩展

1.SUMIF函数的功能是按条件分类汇总，它的语法如下：

第一参数代表要计算的数据源，第二参数代表条件，第三参数代表求和的区域，第三参数是可选参数，当忽略第3参数时则把第一参数当作求和区域。

在本例中“SUMIF（销量明细！$B$2：$B$11，汇总！A2，销量明细！$D$2：$D$11）”表示在销量明细的B2：B11中查找汇总表的A2单元格的值，找到后对销量明细的D2：D11区域对应位置的销量求和。此处要注意的是“对应位置”。例如，在B2：B11中查找“Excel函数、图表与透视表从入门到精通”，目标在B7和B9，那么最后参与求和单元格则是D7和D9。

2.SUMIF函数只对销量分类求和，引用单价要借助VLOOKUP来完成，两者搭配应用才能实现需求。

3.本例公式需要向下填充，因此所有关于区域的引用都要采用绝对引用，而被查找的书名在填充过程中需要不断变化，则必须采用相对引用。

在图7.64中，B2：B11区域的金额既有美元又有人民币，通过单元格格式区分。在F2单元格中输入姓名，然后在G2单元格通过公式查询F2的姓名所对应的捐款数额。

现在的问题在于：G2 单元格的公式只能引用数值，不能将格式一并引用过去，从而导致公式产生的金额看不出是美元还是人民币，有什么办法实现引用值的同时又引用格式呢？

解题步骤

Excel的查找和引用函数都会忽略单元格的格式，但是可借助早期的宏表函数GET.CELL提取目标单元格的格式信息，然后利用TETX函数将公式的返回值设置为相同格式即可，具体操作步骤如下。

1.选择G2单元格，按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

2.单击“新建”按钮，弹出“新建名称”对话框，然后将名称设置为“格式”，将引用位置设置为以下公式：

图7.65是定义名称的设置界面。

3.单击“确定”按钮返回工作表界面，并在G2单元格中输入以下公式：

公式会返回F2的姓名所对应的捐款数量，而且公式返回值与原数据的显示样式一致，效果如图7.66所示。

4.将F2单元格的值修改为“谢有金”，公式将返回“$720.00”，效果如图7.67所示。

B2：B11区域的单元格格式各不相同，其前置的币别符号是通过单元格格式产生的，而非手工输入的，以上步骤可以实现引用单元格的值时也将格式一并引用过来。

知识扩展

1.GET.CELL是宏表函数，只能用于xlsm和xls格式的工作簿中，假设在xlsx格式的工作簿中使用，那么重新打开工作簿后它会自动消失，因此本例的案例文件采用的是xlsm格式。

2.GET.CELL用于获取与单元格相关的数十项信息，当第一参数是7时表示提取单元格的格式信息。TEXT函数用于对单元格的数值指定格式，因此将它与GET.CELL函数搭配使用可以引用指定单元格的格式。

3.公式“=GET.CELL（7，Index（$B2：B$11，MATCH（$F$2，$A$2：$A$11，0），0））”中的MATCH函数用于计算F2的姓名在A2：A11区域中的位置，然后使用INDEX函数引用该位置的单元格，将此单元格作为GET.CELL函数的第二参数则可以提取此单元格的格式信息。

此格式信息被赋予名称“格式”，然后将名称作为 Text 函数的第二参数去限定 VLOOKUP函数找到的捐款数的格式。原本VLOOKUP函数找到的捐款数只是720或1045，通过TEXT函数限定格式后会变成“$720.00”或“¥1，045.00”。

4.在公式中引用名称时，不能对名称添加引号。

图7.61所示为各产品单值，图7.62所示的日产量表需要计算每个产品的产值，产值等于产量乘以单价，有什么办法让公式自动搜索当前产品对应的单价，避免人工查找单价，既耗时又易出错？

解题步骤

根据品名从单价表中引用对应的单价，最简便的方法是使用VLOOKUP函数引用目标，操作步骤如下。

1.选择E2单元格，并输入以下公式（见图7.63）：

要注意公式中第二参数是绝对引用，第一参数是相对引用。

2.双击E2单元格的填充柄，将公式向下填充，从而计算出所有产品的产值。

知识扩展

1.VLOOKUP 函数用于从列表左侧查找数据，找到后返回右边某列中对应位置的值。在本例中，VLOOKUP函数用于从单价表A2：B10区域的左边一列A2：A10中查找品名，当找到品名后，返回第2列中对应位置的单价。

2.VLOOKUP函数的语法如下：

其中第一参数代表要查找的值，本例中是品名；第二参数是要在其中查找的一个列表，它既可以是区域也可以是数组，本例中是单价表中的A2：B10区域；第三参数代表列数，即返回值位于列表中的第几列，必须是大于等于1、小于等于总列数的整数值；第四参数代表匹配方式，当赋值为False时表示精确匹配，赋值为True时表示糊模匹配，本例采用的是前者。

也可以用以下语句来说明VLOOKUP函数的语法：

VLOOKUP （要查什么，在哪里找，返回第几列的，[查找方式]）

3.由于本例的公式需要向下填充，从而一次性引用所有产品的单价，因此公式的第二参数“单价表！$A$2：$B$10”需要采用绝对引用，否则可能查找不到单价。

VLOOKUP的第一参数是品名，在填充公式时需要它不断变化，因此只能采用相对引用方式。

4.为了避免查找出错，在书写品名时必须确保产量表中的品名与单价表中的品名一致，有任何差异就可能查找失败，从而产生错误值。例如，在产量表中将“一字刀”写成了“一字力”，再或者在“刀”右方多了一个空格，都会导致公式结果为错误值。

图 7.53 所示的成绩表中行标题为姓名，列标题为科目，能否在输入姓名和科目名称后自动给出对应的成绩？

解题步骤

根据已知的两个条件查询对应的数据，达成此需求有多种方法，其中使用公式灵活性最好，具体操作步骤如下。

1.在I1：K1中分别输入“姓名”、“科目”和“成绩”。

2.在I2：J2分别输入“朱明”和“化学”。

3.在K2单元格输入以下公式：

此时公式返回朱明的化学成绩94，效果如图7.54所示。

4.将姓名修改为“曹锦荣”，将科目修改为“语文”，此时K2的公式会返回成绩“100”（见图7.55）。

知识扩展

1.MATCH函数用于计算一个字符串在一维数组或单行/单列区域的出现位置。例如，在图7.55中，曹锦荣在A2：C11区域中的出现位置是第8位，那么公式“=MATCH（I2，A2：A11，0）”的返回值就是8。MATCH函数的语法如下：

其中第一参数是查找对象，第二参数是一维数组或单列区域/单行区域，第三参数则用于控制查找方式，赋值为0时表示精确查找，赋值为1或-1时表示模糊查找，工作中用得最多的是精确查找。简言之，MATCH函数的功能就是在第二参数中查找第一参数的位置，并由第三参数决定查找方式。

2.INDEX函数的功能是在一个区域或数组中按位置查找对应的值，由于MATCH函数的功能正是计算位置，因此INDEX函数经常搭配MATCH函数使用，本例正是此类型的应用案例。

3.在本例公式中，第一个MATCH函数用于计算纵向位置，第二个MATCH函数用于计算横向位置，两者的交叉点刚好是B2：G11区域中对应的成绩。

4.了解公式的含义和计算过程的最好方法是使用公式求值工具。以图7.54中的公式为例，了解公式的方法是选中K2单元格，然后单击功能区的“公式”→“公式求值”，并在对话框中单击“求值”按钮，图7.56所示的窗口中表达式“MATCH（I2，A2：A11，0）”的下画线表明下一步会计算这个表达式，此时再次单击“求值”按钮，对话框会变为图 7.57 所示的效果，图中说明了两个问题，一是表达式“MATCH（I2，A2：A11，0）”的计算结果为6，二是下一步要计算的表达式是J2的值。

再次单击“求值”按钮，窗口内容会变成图7.58所示的状态，表示J2的计算结果是“化学”，下一步要计算的表达式是“MATCH（J2，B1：G1，0）”。

再次单击“求值”按钮，窗口内容会变成图7.59所示的状态，表示“MATCH（J2，B1：G1，0）”的计算结果是4，下一步要计算的表达式是“INDEX（B2：G11，6，4））”。

再次单击“求值”按钮，窗口内容会变成图7.60所示的状态，表明公式的最终结果是94。

由于职工在一天中可能生产多种产品，每个组别又包含多个职工，因此在图 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）。