Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

2021年10月15日10:36:53Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招已关闭评论 18 views
微信公众号 【EXCEL880】 QQ群【165159540】
课程咨询 加我微信EXCEL880B试学网址http://v.excel880.com
小王:期末考试结束了,怎么对不同分数段的人数分段统计?

老师们,需要对分数分段统计;HR们需要对年龄段分段统计;销售们需要对销量分段统计……不管哪种,都可以用下面的4种方法。

一、FREQUENCY

① 创建如图所示的辅助表;

② 输入公式“=FREQUENCY(B2:B52,F5:F9)”,按CTRL + Shift + Enter确认输入。

FREQUENCY两个参数,第一个参数为所有分数所在单元格,第二个参数为分段统计临界点数值所在单元格。请注意,这是个数组公式,输入后不能只按Enter键。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招
小王:这个公式会把60分的人也统计到第一个数字里吧?

这个问题暂且不答,先看下一个方法吧。

二、SUMPRODUCT

① 创建如图所示的辅助表;

② 创建输入公式“=SUMPRODUCT(($B$2:$B$52>=F5)*($B$2:$B$52<G5))”,按Enter键确认输入,并双击填充到其他单元格。

公式虽然看着挺长,其实很简单,它的格式为:“SUMPRODUCT((分数单元格>=分数下线)*(分数单元格<分数上线))”。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招
小王:咦,SUMPRODUCT的统计结果怎么和FREQUENCY的不相同?

没错,这就是两者对临界点数据处理不同所致。

SUMPRODUCT的临界点包含与否,从条件中可以很容易看出。而FREQUENCY对临界点的处理是“小于等于临界点”。如第一个FREQUENCY在统计最后一项时没有包含“90”,得到人数为“6”,而第二个FREQUENCY在统计时包含了“90”,所以得到人数为“8”。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

所以,在使用FREQUENCY时一定要格外注意临界点问题。

SUMPRODUCT函数还可以拓展成多条件统计,例如“SUMPRODUCT((条件1="一班")*(条件2<60))”可以统计出一班不及格的人数。

小王:如果每一门都要统计,用这两函数好麻烦啊。

确实,当要统计多个科目,或多个班级时,用函数还是挺麻烦的,这时可以考虑使用数据透视表。

三、数据透视表分组

① 创建数据透视表,字段设置如图。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

② 在数据透视表上右键点击“组合”,输入起始和结束的分数,以及步长即可。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

这样的方法,不仅可以看到的信息更多,还可以随时修改“统计的分数段区间”。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招
小王:怎么在一张表格上创建多个统计表?

复制第一个数据透视表,粘贴到同一个表上,修改“行”字段即可。还可以添加数据透视图和切片器。

四、直方图

① 对于Excel2016,可以直接插入“直方图”。直方图会自动汇总各区间段的人数。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

这个方法也很简便。你可能需要修改的只有横坐标轴。

Excel分段统计(区间统计)有4招,FREQUENCY是最不好用的一招

双击一下横坐标轴,修改“箱”选项卡下的设置即可。这里的“箱”就是直方图上的一个柱体。至于每个选项是什么意思,只看上图就可以明白啦。


学习,为了更好的生活。欢迎点赞、评论、关注和点击头像。