Excel中只对可见数据求和实现完美自动筛选.doc

在使用EXCEL筛选功能时，有个让人非常苦恼的事情，就是筛选出来的数据没有小计，如果用常规的sum函数求和，那些被隐藏的行也被被计算进去，得到的不是小计，而是所有数据的总计ABC1姓名部门工资2张三1总经理工作部1000'3'张三龙总经理工作部101@4张三呂总经理工作部10205张三4=总经理工作部10306张三5财第部10407张三&财务部10508张三了财务部1060'9张三8财务部107010张三9【财务部100011张三10财务部109012张三n财势部110013张三：财勢部111014张三匹财务部112015张三応人力资源部11：§01&张三人力资源部11401T张三让人力资源部11501S张三1T人力资源部116019合计1B360要实现这个效果，其实非常简单，下面就跟着我一步一步来吧：、自动筛选1）先在第19行上面插入一个空行：AB匚1姓名部门工資2张三1总经理工作部id®3张三Z总经理匸作部10104张三3怕经理工作部10205张三叫总经理工作部10306张三5财务部10407张三&财芻部1050张三丁1财务部10609张三8财务部107010张三9财务部1(38011张三®财务部109012张三111财务部110014-张三12财务部111014张三1茁财勢部112015張三14九力狡源部11'30匱张三15人力资源部114017张三人力资源部115018张三1丁•人力資源部11601920合计.=1S360这样合计行会变成第20行。

为什么要插入空行呢，因为如果没有这个空行，你即使在合计行设置了小计的公式，也会在筛选时被隐藏，所以这个空行是必须的2)选中B1:B18这个区域，然后进行筛选(EXCEL2000/2003版：点击“数据”菜单下的“筛选”、"自动筛选”；EXCEL2007/2010版：依次点击数据标签、筛选按钮选：A1姓名J部门Z匹费2张三1总绘理工作部”10003张三2总:经理工住部1.Q1Q4张三2总经理工作部1備.5张三4总经理工作部1廉0张三5财昜部一lOiO''7张三G谯部1.Q5Q8.张三丫财络部10&09张三8L07Ciio'张三9财务部1080'11张三101Q9Q12张三1111勸；13'张三12111014张三13财务部1120'15张三珂人另竇頤抵U3Q1'6张三15人力资源部114417张三1G人力逐葆棘115018张三却人力熒源部.1160'n.do合计183&j3）隐藏刚才插入的空白行，第19行，这时可以发现，不管怎么筛选，原先的合计行都不会被隐藏ABC1姓名|部门Z工资15张三空人力资源部11301&张三15人力资源部114Q;17张三16X力资源部1150IS张三诃人力资源部1160(ST合计1836^'纣I-I-二、只对显示的内容求和从上表可以看到，虽然筛选了人力资源部的数据，但第20行合计却依然是所有人的（包括被筛选后隐藏的），这当然不是我们需要的结果。

所以要对C20单元格的公式进行修改：=SUBTOTAL（109,C2:C18）subtotal是分类求和函数第一个参数109的意思是只对可见区域求和，所以只要是只对可见区域求和就必须是109，要是只对可见区域求统计有数据的单元格个数就是103第二个参数是要求求和的区域，这里是C2:C18，就是本表工资列的数据区域现在可以发现，只要对部门进行了重新筛选，C20单元格的合计数会自动进行变化1姓名部门工资C.弓*三日财务部1040丫引用三Emo^=7贝才务部1U609烂8107010弓忙三9财务却108011引\~lti109012引用三丄1财务部110013^=12111014弓[<=13财务耳1120力〕小计9720当然，这时候B20单元格显示的“合计”并不恰当，因为全部显示时才是“合计”，部分显示只能是“小计”，B20单元格设置公式：=IF(SUBTOTAL(103,B2:B18)=COUNTA(B2:B18),"合计","小计")EZO千(虚|[=IF(^UETCJTAL(：LO&E2:E18)ROUNTA(E2:E：L8)」"合计"・"小计")AEC'：E"~F：u—~R~：T"—I：厂—。