EXCEL中正常求和用SUM函数,如图,求B列的和,公式为:=SUM(B2:B12);
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/5882b2b7d0a20cf49d43743870094b36adaf9902?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/5fdf8db1cb134954d3f81d83504e9258d0094a77?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
EXCEL只对可见单元格进行统计,用SUBTOTAL函数:=SUBTOTAL(109,B2:B12);
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/c8177f3e6709c93d872bfa10993df8dcd0005478?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/3b87e950352ac65c88139194fdf2b21192138ac7?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
需要注意的是该函数第一参数9和109都是求和,但使用参数9时,通过“隐藏行”命令所隐藏的行中的值仍参与求和;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/42a98226cffc1e17bdc17dd64c90f603728de9df?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
而选用参数109时则不参与求和;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/bba1cd11728b471047b70b2bc5cec3fdfd0323b9?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
使用筛选可任意选择参数9或109,通过筛选隐藏的行均不参与计算。