你好,我是刘卓。欢迎来到我的公号,excel函数解析。今天来分享一个计数的案例。我是在群里看到有人提问,觉得这个案例挺有意思的,就来分享下。
案例展示
下图是数据源,要求是统计每日的出船数量。结果如B列红色字体所示。由于原来的数据有上千条,不可能手工一个个统计。我这里只选取部分数据,主要是告诉大家方法。
在讲解之前,我希望你自己先想想该如何统计数量。主动思考,学习效果更好哦。
好了,开始说说我的方法。观察这个表格结构,其实还是很有规律的。我们可以借助A列来辅助计算。
选中B列的区域,按F5定位,定位条件选空值,要统计数量的单元格就被选中了,然后在公式编辑栏输入公式=LOOKUP(1,0/(A$2:A15<>""),ROW()-ROW($2:15)),按ctrl+enter批量填充。
至于上面的公式是什么意思,我也不想仔细说明了,因为这种方法不是我今天主要想分享的,感兴趣的同学可以自己拆解下公式。
下面分享一个更简单的计数方法。还是同样的操作,选中B列的区域,按F5定位,选空值,然后在编辑栏输入公式=SUBTOTAL(3,B$2:B15)-SUM(B$2:B15),按ctrl+enter完成。subtotal函数会忽略嵌套的subtotal,什么意思呢?以下图B11单元格的公式说明一下,SUBTOTAL(3,B$2:B10)返回的结果是8,而不是9。因为它会忽略B8单元格。为什么会忽略B8单元格?因为B8单元格中的函数是subtotal,就相当于嵌套了subtotal。
又由于B列的数据是文本型的,用SUM(B$2:B10)求和时,其实只对B8单元格的数字求和。而B8单元格的数字正好是7月1号的数量,SUBTOTAL(3,B$2:B10)统计的是7月1号和7月2号的总数量。
所以用SUBTOTAL(3,B$2:B10)减去SUM(B$2:B10),就是用7月1号和7月2号的总数量减去7月1号的数量,得到7月2号的数量。
当然还可以用aggregate,动图就不录制了,直接贴公式:=AGGREGATE(3,0,B$2:B7)-SUM(B$2:B7)。
当B列的数据是数值时,该如何计数?可以先自己想一下,再看下图的公式。
同样的操作,选中B列的区域,按F5定位,选空值,然后在编辑栏输入公式=SUM(SUBTOTAL({3,9},B$25:B30))-SUM(B$25:B30),按ctrl+enter完成。其他公式参考文件。
今天的分享就到这里,希望对你有所帮助。不要忘记一键三连。
https://pan.baidu.com/s/1EpjNmjhXrLJvWvNMygPU1g