图7.136中F1:G9区域包含所有产品的单价,A1:C7区域是近期的采购记录。

现要求一次性计算所有采购产品的金额之和,存放在C8单元格。

Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22

图7.136 采购表

解题步骤

Excel中按条件求和的函数是SUMIF,按多条件求和的函数是SUMIFS。本例不需要多条件求和,而是同时对多个对象求和,因此采用 SUMPRODUCT+SUMIF 的方式实现,具体公式如下(见图7.137):

Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22
Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22

图7.137 汇总所有购买产品的金额

知识扩展

1.SUMIF函数用于按条件求和,第一参数是用于条件计算的单元格区域;第二参数代表条件,当第二参数是单个值时 SUMIF 的计算结果也是单个值,第二参数包含多个条件时 SUMIF的计算结果也是多个值;第三参数代表实际参与求和的区域,它必须与第一参数的高度、宽度一致。第三参数是可选参数,当忽略第三参数时表示对第一参数求和。

本例中条件为B2:B7,代表同时对6个条件分别求和,产生6个求和结果。当F2:F9区域中的值等于B2:B7中的任意一个单元格的值时,那么就对G2:G9区域中对应位置的单价求和。由于F2:F9区域中每个产品都只出现一次,因此求和的结果其实就等于该产品的单价。

简言之,表达式“SUMIF(F2:F9,B2:B7,G2:G9)”的功能是查找B2:B7区域中每个产品对应的单价,B2:B7区域有多少个单元格,表达式“SUMIF(F2:F9,B2:B7,G2:G9)”就生成多少个单价。

2.SUMPRODUCT的功能是对数组参数中的每个元素相乘,最后再求和,简称计算乘积之和。当只有一个参数时,SUMPRODUCT的功能等同于SUM函数。

本例中表达式“SUMIF(F2:F9,B2:B7,G2:G9)”用于生成B2:B7区域中每个产品的对应单价,C2:C7则是对应的采购数量,SUMPRODUCT函数将两者逐一相乘,然后汇总即为所有产品的金额之和。

3.通过以下3个步骤可清晰地了解本例公式的运算过程。

在图7.138中,选择D2单元格后输入公式“=SUMIF($F$2:$F$9,B2,$G$2:$G$9)”,然后将公式向下填充到D7,公式会生成6个产品的单价。

Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22

图7.138 利用SUMIF函数计算产品单价

在图7.139中,E2的公式是“=D2*C2”,然后将公式向下填充到E8,公式的作用是计算每个产品的数量与单价之积,即每个产品的金额。

Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22

图7.139 分别计算每个产品的单价与数量乘积

在图7.140中,公式“=SUM(E2:E7)”用于计算每个产品的金额之和。

Excel 能否根据采购数量和单价一步汇总所有金额?-Excel22

图7.140 汇总所有产品的金额

以上3个运算过程等同于“=SUMPRODUCT(SUMIF(F2:F9,B2:B7,G2:G9),C2:C7)”的功能。其中SUMPRODUCT包含了单价乘以数量再汇总两项工作。