可以用SUBTOTAL函数对自动筛选的数据进行求和。
1、以excel2010版本为例,如下图所示,有这么一个数据表格;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/0824ab18972bd40728f451bc76899e510fb309bb?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
2、在单元格数据的末尾先求和,B14单元格插入subtotal函数;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/cdbf6c81800a19d8547206443efa828ba61e467a?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
3、subtotal函数的第一个条件选择数字9 ,即sum 进行求和;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/c75c10385343fbf22d0b2462bd7eca8065388f48?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
4、然后第二个条件求和区域选择B2到B13,求和这一整列的数据;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/bd3eb13533fa828b4a201fa2f01f4134970a5a74?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
5、按enter键后,就可以计算出B2到B13所有数据的和了,把公式往右拉填充,求出其他列的和;
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/42a98226cffc1e17b7de0e704790f603738de90a?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
6、这时对该表格进行筛选时,例如对月份进行筛选,就会看到合计一行的数都是只对筛选的数据进行求和而已,隐藏的数据没一起求和。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/b64543a98226cffc22893df3b4014a90f703ead4?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)