Excel分区间统计分析(等步长、不等步长、多维度)
在数据分析过程中,可能会需要统计不同数据区间的人数、某个数据区间的平均值或者进行分组区间统计,本文从excel函数到数据透视表的方法,从简单需求到复杂需求,采用不同的方法进行讲解,尤其是通过数据透视表的强大功能大幅提升了数据分析的效率。我在商务数据分析方面经常需要处理百万级的数据,为了简化讲解,便于大家理解,本文以一个简单的学生成绩数据为例进行分析讲解。
需求一:统计60分以下,60-70,70-80,80-90,90-100的人数。
方法1:利用Frequency函数计算不同分数区间人数。
如图所示,A1:E30为学生成绩表,然后设计了H1:J6的表用于Frequency函数计算,其中I2:I6设定的间隔值将用于函数参数。
选中J2:J6然后输入公式=FREQUENCY(E2:E31,I2:I6),Ctrl+Shift+Enter填充J2:J6,得出结果。
方法2:数据透视表的分组功能。
1.插入数据透视表,以“分数”为行,以“姓名”为计数项为值。
2.右键“分数”列,选择[组合]选项,起始60,终止100,步长10。即可得出结果。
需求二:统计60分以下,60-75,75-85,85-100分的人数,以及各区间的分数的平均分。
对于需求一,数据透视表功能中步长是相等的,但需求二中步长不同,所以不能直接使用步距功能。
新建一列F,列标题设置为分数范围名称。
设计表H1:I5(注意区间值由小到大排列),通过vlookup函数的模糊查询,将数据标记分数对应的范围。这样进行数据透视表就可以将分数区间作为列,姓名作为计数合计值。
需求三:统计每一个班,60分以下,60-75,75-85,85-100分的人数,以及各区间的分数的平均值、最高分、最低分。
将上数据透视表中的行、列、值进行配置,生成需求的下表。