Excel教程:妙极了,或许你不知道的另类求和!

在平时工作中,我们经常会用到求和,这种问题对于大家来说是再简单不过的,使用SUM函数就可以解决:

有时候可能会隐藏几行数据,这时候求和就要用到SUBTOTAL这个函数了。在没有隐藏的时候,SUBTOTAL函数结果与SUM函数计算的结果一致,如下:

一旦我们将其中的某几行(如第3、6、9、12行)数据隐藏起来,结果就发生了变化,如下:

注意SUBTOTAL函数的第一个参数使用109就是表示忽略隐藏行的求和。

关于SUBTOTAL这个函数,之前发表过教程,有兴趣的朋友可以去看看历史文章。

今天我们要讨论的问题不是忽略隐藏如何求和,而是忽略隐藏如何求和。首先要明确一点,SUBTOTAL这个函数是做不到这一点的,在函数的帮助里说的很清楚:

不但SUBTOTAL函数做不到,就目前来说, Excel还没有可以忽略隐藏列进行求和的函数。那么对于这种需求该如何处理呢?这要用到一个比较新鲜的函数——CELL函数来做辅助才行。

相信见过这个函数的朋友不多,会用这个函数的就更少,我们就先来简单了解一下CELL函数是用来做什么的。在单元格输入=c就会看到这个函数的身影,选择函数后,会出现一个简单的解释:

在这句话中,可以大致了解到CELL函数可以得到一个单元格的格式、位置等信息。双击这个函数,会出现一些选项:

可以看到,函数有两个参数,info_type和reference。第一个参数info_type,信息类型,一共有12种,各种类型具体含义可以通过函数帮助了解:

对于这些信息类型有兴趣的朋友可以自己看看,它们都非常容易理解。今天重点要用到的是最后一个信息类型"width",简单来说就是列宽。

有些朋友可能已经想到了,如果列被隐藏的话其列宽就是0,到底是不是这样,我们可以来试试看。公式的第一参数选择"width",第二参数设为B1,表示要得到B1单元格的列宽(实际上就是B列的列宽)。在B16单元格输入公式:=CELL(“width”,B1),然后将公式向右拉:

结果全部是8。我们可以试试调整个别列的宽度,再看看是否有变化:

当我们调整了宽度以后,结果还是8,难道是公式有问题吗?

其实不是的,原因是CELL函数有点小脾气,当单元格的格式发生变化以后(列宽就是一种格式)必须重新计算才能更新结果。重新计算有两种方法,一是按F9功能键,二是双击任意单元格后回车。再来看看就发现结果已经更新了:

数字的大小的确与单元格的宽窄对应。讲到这里如何忽略隐藏的列求和,答案已经呼之欲出了:使用CELL函数得到列宽,再用SUMIF函数实施求和。

I2单元格输入公式为:

=SUMIF($B$16:$G$16,">0",B2:G2),然后将公式向下拉。

在没有隐藏的时候,就是全部求和,现在我们隐藏几列看看效果:

隐藏后记得要按F9或者双击一下哦。

问题到这里似乎该结束了,可是总有些伙伴不太乐意用辅助列(辅助行),就想用数组公式来实现,例如:

=SUMPRODUCT((CELL("width",B1:G1>0)*B2:G2)

想法似乎很有道理,但是这样做是不行的,因为如果参数 reference 是某一单元格区域,则函数 CELL 只将该信息返回给该区域左上角的单元格。也就是说,虽然写了B1:G1这样一个区域,但是得到的只是B1的列宽。

是不是觉得cell这个函数的脾气挺怪的~~~

实际上这个函数还有很多有趣的用法,如果你想知道的话,在下面留言吧!

原创: 老菜鸟 (未经同意,请勿转载)

来源: 部落窝教育

(0)

相关推荐

  • 忽略隐藏行、隐藏列的汇总,你都会吗?

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.在工作中,我们经常会用到忽略隐藏行(包括筛选后)的汇总,偶尔也会用到忽略隐藏列的汇总.今天就来分享下这两种用法. -01- 忽略隐藏行的汇总 下图 ...

  • Excel教程:SUMIF函数,条件求和运用案例

    SUMIF函数是我们工作中使用频率最高的函数之一,它的功能是条件求和,灵活运用可以很好的帮我们完成数据汇总. 刚接触函数的小伙伴询问,要怎么才能记住这个函数.可以这样理解:Sum 是求和,If 是如果 ...

  • Excel教程:合并单元格批量求和、输入序号

    有小伙伴在后台留言询问Excel中合并单元格,怎么录入序号.求和.说实话,合并单元格的数据处理,相对来说有点繁琐,大家能不用合并单元格,就尽量不用哈. 既然小伙伴问了,我就用下面这张合并单元格的Exc ...

  • Excel教程:带单位的数据求和,你会吗?

    工作中我们用函数公式对数据进行求和时,数据都是没有单位的,所以可以轻松完成数据求和,可是按照我们的习惯,数据后面本应该有一个单位的,这样的数据要怎么求和呢? 1. 数据+普通单位 在物料领取登记表中, ...

  • Excel教程:你或许都不知道Excel汇总数据可以这么快!

    数据透视表到底多好用,用过的人都知道! What?!你竟然没用过,那也没关系,今天瓶子就带你认识一下这个最熟悉的陌生功能.下次你做数据汇总和拆分表格的时候,就别像个傻逼抓破脑袋去套用各种函数了,毕竟数 ...

  • Excel教程:很多人都不知道的Excel另类求和!

    在平时工作中,我们经常会用到求和,这种问题对于大家来说是再简单不过的,使用SUM函数就可以解决: 有时候可能会隐藏几行数据,这时候求和就要用到SUBTOTAL这个函数了.在没有隐藏的时候,SUBTOT ...

  • 妙极了,98%的人都不知道的Excel另类求和!

    在平时工作中,我们经常会用到求和,这种问题对于大家来说是再简单不过的,使用SUM函数就可以解决: 有时候可能会隐藏几行数据,这时候求和就要用到SUBTOTAL这个函数了.在没有隐藏的时候,SUBTOT ...

  • Excel教程:产品核价单 合并单元格求和汇总

    这是一个伙伴的产品核价清单Excel文件,左边是源数据,右边是做好的效果.需要解决两个问题,如下: 1. D列总面积,根据A列单元格大小进行合并. 2. 在D列合并单元格计算总面积,也就是对应的C列面 ...

  • 【Excel】史上最全条件求和函数SUMIF教程

    在职场办公中,经常需要对数据进行条件求和汇总,SUMIF函数是工作中使用频率超高的条件求和函数之一. 1SUMIF函数基础语法解析 SUMIF函数可以对范围中符合指定条件的值求和,该函数拥有十分强大的 ...

  • EXCEL教程:EXCEL文档如何实现带单位计算求和

    大家好,今天给大家带来的教程是EXCEL文档如何实现带单位计算求和.例如我们案例中的西红柿金额,如果我们直接输入公式的话,返回的结果是错误的,这样的操作是不行的. 接下来我将教大家如何实现带单位的计算 ...