【原】Excel 【案例分析与学习】考试成绩分段人数统计，函数公式、VBA代码、SQL八仙过海各显神通

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】【2023年8月】

实用案例

|日期控件||简单的收发存|

|电子发票管理助手|

|电子发票登记系统(Access版)|

|Excel多种类型文件合并|

|Excel表格拆分神器|

|批量生成审计凭证抽查底稿|

|中医诊所收费系统(Excel版)|

|中医诊所收费系统(Access版)|

|收费管理系(Access改进版)|

收费使用项目

|财务管理系统||工资薪金和年终奖个税筹划|

内容提要

• COUNTIFS条件计数函数
• FREQUENCY频率统计函数
• VBA代码分段数据统计
• SQL语句查询

大家好，我是冷水泡茶，今天在EXCELHOME论坛上看到一个求助贴：求考试成绩分段人数。

说实话，看这个求助的标题，感觉不应该有多大难度，正准备略过，但看到有不少人回复了解决方案，不禁有点好奇，就下载附件来看看。

果然没让我“失望”，就是一个条件计数的问题。首先想到可以用COUNTIFS函数来解决，要什么VBA，不过得分别写公式：

第一行与最后一行也可以用COUNTIF，因为只有一个条件，但用COUNTIFS看起来比较统一。

在准备发贴的时候，看到其他人的回复，感觉论坛高人真多啊，我的这个解决方案老土了，就没有发上去。今天我就带大家一起来学习观摩一下别人的高招吧：

FREQUENCY函数：

这是来自网友“w4275”的回复，用数组的方法，公式简洁。他还给了不用数组的方法：

=FREQUENCY(B3:B1161,{60,70,80,90,100}-1%%)

加個index//E3 下拉=INDEX(FREQUENCY(B:B,{60,70,80,90,100}-1%%),ROW(A1))

FREQUENCY函数平时很少用，我想起我们分享过一个案例【Excel 函数公式 数据转置神来之笔辅助列】。

VBA代码法

1、这是来自网友“chxw68”的回复，他采用MATCH函数来进行统计，主要代码如下：

他这个思路还是比较奇特的，有人评价：

详细代码我就不贴了，大家感兴趣地可以到论坛上去下载。

2、这是来自网友“sdytsxd991122”的回复，他采用SQL语句查询，主要代码如下：

结合D列的条件区间，编写SQL查询语句。把条件区间字段分列为区间的上限与下限，这样的思路比较符合数据处理的逻辑。

但我有点疑问，这个分列的代码，如何处理“60分以下”与“100分以上”这两个字段的呢？

ss = Split(Sheet1.Cells(i, 4).Value, "-")

再看他的截图，他把前面的条件区间改了，聪明！我还在想怎么用条件判断来构造一个区间呢：

不过，他这里用了一个Between运算符，是包含首尾两个数字在内的，要注意一下，如果条件区间是包头不包尾的，我们就要用“>=”和“<”运算符来表示数据范围。原表的条件区域是不连续的，如果分数有小数的话，可能造成统计偏差，比如69.5、79.5等。

3、这是来自网友“limonet”的回复，主要代码：

 StrSQL = "Select count(*) From  (Select partition(语文,60,99,10)  as 分区 from [原始成绩$B2:B]) Group By 分区" Range("E3").CopyFromRecordset Cn.Execute(StrSQL)

哇，这个更简洁，只用一条SQL语句就得出全部结果。他这里有一条核心代码：

Select partition(语文,60,99,10) as 分区 from [原始成绩$B2:B]

partition还是第一次见，在网上搜了搜，就是没有发现类似的用法。有个partition by的用法。不管它了，先记下来再说。我们还可以做什么呢？我后来把这句单独运行下，看是什么结果：

StrSQL = "Select partition(语文,60,99,10) as 分区 from [原始成绩$B2:B]"Range("F3").CopyFromRecordset Cn.Execute(StrSQL)

数据写入F列：

看出门道没有？他就是把每一条记录的分数值标上一个区间，然后按这个区间分组计数。

总结

1、今天我们演示了一个学习的过程。

2、上面的所有解决方案中，除了VBA代码法的第2条，来自网友“sdytsxd991122”的回复，其他方案都是把分组条件写死的，也就是说，当分组条件发生变化时，这些公式、代码都需要修改。所以，我比较喜欢这个方案。

3、我们在设置表格模板时，一个字段的数据类型与格式应尽量保持一致，比如“60分以下”，我们可以写成“0~60“。

4、另外，我们还发现，原表中的条件区间是不完整的。如果分数有小数，那统计可能就会出现误差，不太严谨。应该下一条与上一条首尾相同，统计的时候包头不包尾，即类似于60<=x<70。实际上，我们在用文字描述的时候可能是这样的“60分以上（含），70分以下（不含）“，虽然有点啰嗦，但是能说明问题。

~~~~~~End~~~~~~

喜欢就点个赞、点在看、留个言呗！分享一下更给力！感谢！