Excel教程

Excel函数
Excel技巧

【excel教程】Excel表格如何忽略隐藏行或是忽略隐藏列进行求和

当前位置:网站首页 > Excel教程

【excel教程】Excel表格如何忽略隐藏行或是忽略隐藏列进行求和

* 来源: 【office教程学习】 * 作者: admin * 发表时间: 2019-12-15 18:59:59 * 浏览: 66

都说磨刀不误砍柴工,同样学习跟磨刀一样,亦是同样道理。成功都是需要厚积薄发。

今天来学习一下Excel表格如何忽略隐藏行或是忽略隐藏列进行求和。

一、忽略隐藏行求和

例如,以下表格:

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


平时看到这么一个表格,需要给产品汇总求和,大家都习惯用最熟悉的求和函数sum函数解决。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


但是,有时在求和统计的时候,不想把某些产品的销售额统计在内,如:产品3和产品6的销售额不统计,我们直接把产品3和产品6所在的行,即第5行和第8行隐藏起来。

这时,你会发现求和结果没变,仍是把隐藏行的值计算在里面。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


是不是,让你失望了……

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


哦,别急!

路是人走出来的,办法是人想出来的。

别忘了,Excel功能总是那么强大。

只要换个公式就可以了。

在B14单元格里输入公式:

=SUBTOTAL(109,B3:B13)

然后,公式向右填充。

嘿嘿,正确的结果出来了。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


这时,同样是用SUBTOTAL函数,但公式换个写法,

即在B14单元格里输入公式:

=SUBTOTAL(9,B3:B13)

然后,公式向右填充。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


是不是发现结果又回到原来用sum函数统计的结果?隐藏行的值仍被统计进去。

看下面这张图片对比一下:

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


很明显,从图中我们可以看出两个公式区别:

公式:=SUBTOTAL(109,B3:B13),是忽略隐藏行求和;

公式:=SUBTOTAL(9,B3:B13),是包含隐藏行求和。

现在来简单说一说我们的SUBTOTAL函数。

SUBTOTAL函数,可以说是一个“万能函数”,它能求和、求平均值、计数、求最大值、最小值等。

作用:返回列表或数据库中的分类汇总;

语法格式:=Subtotal(功能代码,数值区域)

来瞧瞧它的功能代码:

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


功能代码主要是针对图片里的11个函数,但代码分两段,分别为1-11和101-111。

1-11:表示分类汇总时包含隐藏的值。

101-111:表示分类汇总忽略隐藏的值,即只统计可见单元格。

可见,

功能代码9和109对应的都是sum函数,

9表示求和函数sum,且包含隐藏行,

109表示求和函数sum,但不包含隐藏的行。

所以公式:

=SUBTOTAL(9,B3:B13),表示对数值区域B3:B13进行求和,且包含隐藏行的求和,就跟公式=SUM(B3:B13)功能一样。

=SUBTOTAL(109,B3:B13),表示对数值区域B3:B13进行求和,但不包含隐藏行的求和。

好了,关于忽略隐藏行求和说到这里,大家应该都清楚了吧。只要一个函数SUBTOTAL就搞定了。

不过,SUBTOTAL函数对于忽略隐藏行求和有效,但是对于要忽略隐藏列求和, SUBTOTAL函数就起不了作用了。

那要忽略隐藏列求和需要怎么操作?

现在再来放个大招了。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


二、忽略隐藏列求和

比如,以下表格横向求和,统计1-6月的总销售额。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


如果某月份(假设:2月份)的销售额不统计在内,把2月份所在的列即C列隐藏起来。普通的求和函数sum函数,同样无法满足这个功能。

这时,我们可以用以下办法:

步骤1、在表格的最后一行添加一个辅助行,即在A14单元格里输入公式:=CELL("width",A1),然后公式向右填充至G14单元格。

补充说明:

CELL函数是属于EXCEL中的信息函数,

作用:返回有关单元格的格式、位置或内容的信息。

语法格式:CELL(要返回的单元格信息的类型,单元格引用地址)。

公式:=CELL("width",A1)用来获取单元格的列宽。当列隐藏时,获取到的列宽就为0。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


步骤2、在H3单元格里输入以下公式:=SUMIF($B$14:$G$14,">0",B3:G3),然后公式向下填充至H13单元格。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


补充说明:公式:=SUMIF($B$14:$G$14,">0",B3:G3),通过判断B14:G14单元格区域的值是否大于0,来对B3:G3区域的值进行求和。需要注意的是,公式里的第一参数为条件区域,记得需要绝对引用。

再来看一张对比图,结果就很明显了。

「Excel技巧」Excel表格如何忽略隐藏行或是忽略隐藏列进行求和


注意:

当列宽改变,或是隐藏的列改变时,需要按下F9键进行刷新,这样子CELL函数才会重新计算,sumif函数的结果才会重新更新。

例如,原先表格只隐藏C列,后面在隐藏E列,隐藏后,需要按下F9键刷新,这样子辅助列第14列的值才会刷新,H列的汇总求和值也才会跟着更新。

看了这么久,赶紧动手试试看吧,光看不练就是耍流氓~~~

觉得有用的话,快关注收藏吧!每天学习一点Office!!!