篇一：Excel中只对可见数据求和,实现完美自动筛选.
在使用EXCEL筛选功能时，有个让人非常苦恼的事情，就是筛选出来的数据没有小计，如果用常规的sum函数求和，那些被隐藏的行也被被计算进去，得到的不是小计，而是所有
数据的总计。
要实现这个效果，其实非常简单，下面就跟着我一步一步来吧：
一、自动筛选
（1）先在第19行上面插入一个空行：
这样合计行会变成第20行。
为什么要插入空行呢，因为如果没有这个空行，你即使在合计行设置了小计的公式，也会在筛选时被隐藏，所以这个空行是必须的。
（2）选中B1:B18这个区域，然后进行筛选（EXCEL2000/2003版：点击“数据”菜单下的“筛选”、“自动筛选”；EXCEL2007/2010版：依次点击数据标签、筛选按钮
），就会只对部门进行自动筛选，而姓名、工资那些列都不会自动筛
选： 非诚勿扰 19号
（3
）隐藏刚才插入的空白行，第19行，这时可以发现，不管怎么筛选，原先的合计行都不会被隐藏。
二、只对显示的内容求和
从上表可以看到，虽然筛选了人力资源部的数据，但第20行合计却依然是所有人的（包括被筛选后隐藏的），这当然不是我们需要的结果。所以要对C20单元格的公式进行修改： =SUBTOTAL(109,C2:C18)
subtotal是分类求和函数。
第一个参数109的意思是只对可见区域求和，所以只要是只对可见区域求和就必须是109，要是只对可见区域求统计有数据的单元格个数就是103。
第二个参数是要求求和的区域，这里是C2:C18，就是本表工资列的数据区域。
现在可以发现，只要对部门进行了重新筛选，
C20单元格的合计数会自动进行变化。
当然，这时候B20单元格显示的“合计”并不恰当，因为全部显示时才是“合计”，部分显示只能是“小计”，B20单元格设置公式：
=IF(SUBTOTAL(103,B2:B18)=COUNTA(B2:B18),"合计","小计")
篇二：Excel中的筛选状态下求和
Excel中的筛选，是一个很常用的功能。但不知道是有意还是疏忽，Excel没有直接提供在筛选后的一些统计功能，例如求和、平均值等。而由于筛选的主要功能之一就是可以方便快捷的进行变换，所普通的以直接在数据最下面一行进行求和无法实现，计算值也是不准确的。
表1：输入一个简单的图表，共10项三个分类，最下面一行合计。
表2：进行筛选，并在名称项中选中“非诚勿扰”和“合计”两个选项。这时候的合计（980）仍然是表1中10项的合计，而不是表面看起来非诚勿扰的单项合计（420）。
表三：这时候，就需要使用subtotal函数，在C13的单元格输入公式：
=SUBTOTAL(109,$C$2:$C$11)
表4：这时候再进行筛选，在名称项中选中“非诚勿扰”选项，既可以得出得出正确的结果。
表5：除了求和以外，使用subtotal函数还可以进行计数、最小值、最大值、计算平均值等统计功能。但对于同时进行多项分类统计的时候，需要在筛选的时候，选中相应的名称项。
备注：SUBTOTAL函数
所属类别：数学与三角函数
返回数据清单或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的数据清单。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该数据清单进行修改。
语法
SUBTOTAL(_num,ref1,ref2,?)
_num 为 数字，指定使用何种函数在数据清单中进行分类汇总计算。
Ref1, ref2, 为要进行分类汇总计算的 区域或引用，可以从1 到 29 个。
说明：
1、SUBTOTAL 函数只适用于垂直区域，而不适用于数据行或水平区域。
2、_num数字的含义：
2.1 101 AVERAGE（平均值）
2.2 102 COUNT （数值计数）
2.3 103 COUNTA （包涵文本计数）
2.4 104 MAX （最大值）
2.5 105 MIN （最小值）
2.6 106 PRODUCT (所有数的积)
2.7 107 STDEV （估算样本的标准偏差，反映了偏离相对于平均值的离散程度）
2.8 108 STDEVP （返回整个样本总体的标准偏差。它反映了样本总体相对于平均值的离散程度）
2.9 109 SUM （求和）
2.10 110 VAR （估算基于给定样本的方差）
2.11 111 VARP（计算基于给定样本总体的方差）
3、如果在 ref1, ref2,? 中已经有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略！
这个功能也适用于那些喜欢使用“小计”的图表。我们知道，存在小计的时候，计算合计是最容易产生重复项的，但使用subtotal函数则没有这个弊端。例如表6，这里的小计和分类汇总都使用了subtotal函数，明显没有重复计算。
继续对表六使用筛选，在名称中选中“非诚勿扰”和“小计”，得出表7，也是没有重复计算。
4、_num数字可以从1至11，也可以从101至111，两者的区别在于，如果存在手动的隐藏行，前者仍将计算手动隐藏行的数值，而后者将忽略手动隐藏行的数值。但对于筛选后自动隐藏的那些数值，两者都将忽略。
篇三：excel中怎样在自动筛选后自动各项求和
excel中怎样在自动筛选后自动各项求和？
比如我有个表，是每天的具体售出货物。
然后想根据星期几筛选，并得出这个月中所有星期一，星期二等的单项和。 以前有看人做过一个，在ALL的情况下求得一个和，然后筛选之后，和值会因为范围内单元格的内容变化而变化。
但是我自己尝试，则发现数字没有变化。。。人工选中后则成了最后的范围变化，当中不符合条件（比如星期一）的数字还是有加进去，这样就不对了。 还望高手指点。
最佳答案
用这个函数：
=SUBTOTAL(109,A1:A100)
求A1－A100的和，但是当其中有隐藏的时候不会计算进去。
发布评论