首页 > Excel专区 > Excel函数 >

excel 分段统计技巧

Excel函数 2021-11-25 12:48:40

每到期末,各老师都会对统计各分段人数而苦恼。数据量大、时间紧,还比较容易统计错误。在很多老师中,可以用IF函数来解决。但是对于多层次(三层以上)的分段统计,IF函数就显得很吃力。用FREQUENCY函数就能较好地处理这个事情。以我一个老师朋友为例子。

【函数讲解】

FREQUENCY函数的具体语法如下:

FREQUENCY(data_array,bins_array)

它的用途有很多,其中一个就是“函数按bins_array给出的分段点(比如说有N个分段点)将data_array分成N+1个区域”,且是向上舍入,即小于等于分段区间值。

举个例子:如图1

(图1)

用FREQUENCY函数只需要两个分段点,即:19、29,就可以把区域分成(-∞,19]、(19-29]、(29,+ ∞)三个区段。由于A列中均是整数,所以在这种情况下相当于(-∞,19]、[20-29]、[30,+ ∞)这个区间(用小数点来更好地处理,后面会讲)。选中D1:D3单元格区域后,输入数组公式=FREQUENCY(A1:A10,G1:G2)就可以得到结果了。如图2所示。

(图2)

【温馨小提醒】

输入数组公式的时候不要忘记按Ctrl+Shift+Enter组合键^-^

有了这个基础,我们就可以较好地处理图3的问题了。

(图3)

如图3,60分以下是不及格,60分(含)至70分(不含)是及格,70分(含)至90分(不含)是良好,90分(含)以上是优秀。分别统计出各科目的成绩分布。

【解答步骤】

1.在O列添加辅助列,分别在录入:59.9、69.9、89.9三个数字

2.选中J2:J5→输入数组公式如下:

=FREQUENCY(OFFSET($A$2:$A$14,,MATCH(J1,$B$1:$F$1,0),),$O$2:$O$4)

3.向右填充,统计数据就出来了。

如图4所示。

(图4)

【说明】

1.OFFSET($A$2:$A$14,,MATCH(J1,$B$1:$F$1,0),)是为了实现动态引用,当向右填充的时候能自动获取对应的列。如拉到数学列时,MATCH(K1,$B$1:$F$1,0)获得数字2,通过OFFSET函数取得C2:C14单元格区域;

2.用59.9、69.9、89.9三个分段点来将区间分成(-∞,59.9]、(59.9-69.9]、(69.9-89.9]、(89.9,+ ∞);

3.如果成绩中有一位小数点,可以把三个分段点变成59.99、69.99、89.99三个分段点。


标签: excel分段统计

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.