第1个回答 推荐于2016-04-26
EXCEL中,对筛选后的值求和的方法:
如下图,直接求和,用公式:=SUM(C2:C10);
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/c83d70cf3bc79f3deaa178a9bca1cd11738b29e2?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
如果仅对上海地区求和,可以先筛选出上海地区再求和
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/77c6a7efce1b9d16224206f8f5deb48f8d5464a6?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
确定后,发现和值并没有改变;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/a686c9177f3e6709c7dad9663dc79f3df9dc5554?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
隐藏行仍然参与求和,要使隐藏行不参与求和,可以用分类汇总函数:=SUBTOTAL(109,C2:C10);
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/80cb39dbb6fd5266fd93a08dad18972bd50736ea?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
分类汇总函数SUBTOTAL中第一参数选取不同数字,有不同的汇总功能,各参数使用功能如下表:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/6a63f6246b600c331dc342511c4c510fd8f9a1f5?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
第2个回答 2010-03-20
假设数据在A1:A100,
B1输入:
=SUMPRODUCT(IF(A1:A100>5,A1:A100-5,5-A1:A100))
ctrl+shift+enter结束
这样求的是大于5的用这个数减5后求和,小于5的用5减去这个数后求和这两个和的和。
单独求的话:
大于5的用这个数减5后求和:
B1输入:
=SUMPRODUCT((A1:A100>5)*(A1:A100-5))
ctrl+shift+enter结束
小于5的用这个数减5后求和:
B1输入:
=SUMPRODUCT((A1:A100>5)*(5-A1:A100))
ctrl+shift+enter结束
祝你成功!本回答被提问者和网友采纳
第3个回答 2010-03-20
=SUMPRODUCT(ABS(B1:B10-5))
这个公式必须保证B1:B10区域都有数值,否则空格单元格按0计算
如果要剃除空格内容,可以增加一个条件
=SUMPRODUCT((B1:B10<>"")*(ABS(B1:B10-5)))
第4个回答 2010-03-20
添加两列 一列是计算大于5的 一列计算小于5的 再每列求和 不就搞定了