excel筛选时怎么显示项目计数,筛选项目后显示计数?

前面一个文章下有一个评论,他问作者:只统计筛选出来的学生成绩及格人数,用公式怎么来计算?

那今天就专门用一篇文章,来介绍两个方法,快速地计算数据筛选之后符合条件的单元格个数。

这两个方法一个将如他所愿,使用公式,一个则通过操作,来达到目的。

下面就一个一个来介绍。

一、超级表

超级表的作用无需作者再重复阐述了,已经通过不同实例讲述过。

我们直接进入正题,在下图中,已经对班级进行了筛选,现在需要对筛选后的高三1班,进行成绩水平为及格以上的人数统计。

excel筛选时怎么显示项目计数,筛选项目后显示计数?

首先我们按下快捷键CTRL+T,快速创建一个超级表,并在”表设计“工具栏下,勾选“汇总行”,如下图所示:

excel筛选时怎么显示项目计数,筛选项目后显示计数?

此时表格下方自动添加了一行,即汇总行,而且成绩水平列下出现了一个数值”14“。

但这个结果只是进行班级筛选后的单元格个数,还没有对成绩水平进行条件设置,因此我们需要在成绩水平列进行一次筛选。

勾选及格、良好和优秀的选项,即隐藏到不及格人员的数据。

然后在汇总行中右侧的下拉框内选择”计数“功能。

excel筛选时怎么显示项目计数,筛选项目后显示计数?

我们来看一下它的结果,为12,刚好是高三1班成绩水平及格以上的人数。

excel筛选时怎么显示项目计数,筛选项目后显示计数?

所以,通过超级表的再次筛选,然后汇总计数,便可以达到筛选后统计个数的效果。

二、公式

如果要设置一个公式,直接计算符合条件的个数,该如何下手?

因为正常来讲,公式运算是会将隐藏或筛选的数据计算在内,所以忽略隐藏或筛选的数据,进行计算,才能求得正确的结果。

而在excel中,正有一个函数,能够忽略隐藏和筛选数据,它就是分类汇总函数——SUBTOTAL.

按照作者习惯,一般先写完整公式,再逐步来解析公式。

完整公式为:{=SUM(SUBTOTAL(3,OFFSET(F2,ROW(3:40)-2,))*(F3:F40>60))}

excel筛选时怎么显示项目计数,筛选项目后显示计数?

这里使用了大括号,表示它属于一个数组函数,里面有参数的结果是一个数组而非单个值。

这个公式包括了sum函数、subtotal函数、offset函数和row函数,每个函数的作用和含义其实作者都曾讲解过。

subtotal函数常用来求和,表达式为:=subtotal(函数值,区域),这里函数值指的是代表各种函数的数值形式,如下图所示:

excel筛选时怎么显示项目计数,筛选项目后显示计数?

由1~11,101~111的数值来表示,每个数值都代表一个函数,而101~111的值会忽略隐藏的行,因此在计算有隐藏行的表格数据时,大家会使用subtotal来计算,而在筛选场景中,使用任何一种函数值形式,都会忽略不包括在筛选中的行数据。

这里我们的目的是统计成绩及格的人数,即符合条件的计数,因此函数值参数选择3,而区域是一个公式:OFFSET(F2,ROW(3:40)-2,)

offset函数的语法如下图:

excel筛选时怎么显示项目计数,筛选项目后显示计数?

它的完整表达式有5个参数,但第3第5参数可以省略,默认为1。

第一个参数这里是F2,第二个参数又用到了一个函数公式,即ROW(3:40)-2。

row(3:40)这样的表达式,表示的是返回第3到第40行的行序号,结果是一组数值。再减去2,是因为我们的表格数据是从第3行开始的,减去2可以与后面的条件区域相匹配。

这个条件区域就是:F3:F40>60

这样的表达式,大家应该清楚,属于条件判断,其结果会返回true和false,用数值来表示,就是1和0.

我们通过公式解析,来看下公式各组成部分的结果值,如下图所示:

excel筛选时怎么显示项目计数,筛选项目后显示计数?

SUBTOTAL(3,OFFSET(F2,ROW(3:40)-2,))函数公式的结果是1和0组成的数组,即图中A结果,因为row函数的结果是数组,那么offset进行偏移引用的结果也是一个数组,在套上subtotal进行计数,得到的结果也同样是一个数组。

F3:F40>60表达式的结果是true和false,数值表示为1和0,两者相乘,如果都为1,那结果就是1,如果一方为0,结果为0.

二者相乘结果为1,代表什么呢?

因为A公式的含义实际上就是统计F2单元格下方数据区域的单元格个数,结果忽略筛选掉的行。

而B公式的含义是成绩大于60分,即成绩水平在及格及以上。

两者相乘为1,则代表既是筛选后的单元格,成绩又是及格的,于是最后通过sum函数进行求和,便得到了所有符合条件的学生人数。

这个公式虽然嵌套的函数不少,但拆分来看,并不复杂,童鞋们可以进行实操练习,来熟悉这几个函数的应用。

当然也可以通过观看作者的文章,来了解它们的用法和含义。

以上就是今天的全部内容,有什么问题,可以在评论区留言,最后没关注的点点关注,我们明天再见!

回顾往期:


Excel如何批量计算文本算式,两个方法一分钟快速搞定

Excel表格怎么根据业绩区间来计算提成,介绍两种快捷方法

Excel统计某个字符在单元格文本中出现的次数的函数公式

创业项目群,学习操作 18个小项目,添加 微信:80709525  备注:小项目

本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 sumchina520@foxmail.com 举报,一经查实,本站将立刻删除。
如若转载,请注明出处:https://www.summeng.net/8890.html