【系列课程】Excel 数据分析:精算班次前 80% 学生语文平均分

我们的目标非常明确：根据已有的班次数据，筛选出各班前 80% 的学生，计算他们的语文平均分。

整个过程分为两个核心步骤：

第一步：添加辅助列，利用 IF、COUNTIF、INT 三个函数的组合，自动判断每位学生的班次是否在本班前 80%。

第二步：数据透视表统计基于标记好的超级表，使用数据透视表快速筛选并计算出各班前 80% 学生的语文平均分。

第一步：新增辅助列，判断是否为前 80%.这一步是精准筛选的基础，我们用一个公式就能搞定。

1.添加字段名：在超级表的最后一列空白处，输入字段名 “是否前 80%”。

2.输入核心公式：点击该列第一个数据单元格，输入以下公式：

=IF([@班次]<=INT(COUNTIF([班级],[@班级])*0.8),"是","否")

公式分步解读：

1.COUNTIF([班级],[@班级])：这部分的作用是统计当前学生所在班级的总人数。

2.INT(COUNTIF(...)*0.8)：用班级总人数乘以 0.8（即 80%），然后用INT函数向下取整，确保结果是整数。例如，40 人的 80% 是 32 人，39 人的 80% 是 31.2，向下取整后为 31 人。

3.IF(...)：这是最终的判断环节。它会检查当前学生的 “班次” 是否小于或等于我们刚刚算出的 “前 80% 人数阈值”。如果是，就显示 “是”（表示该生属于本班前 80%）；如果不是，就显示 “否”。

第二步：数据透视表，快速统计平均分。

辅助列标记完成后，数据透视表就能大显身手了，它能帮我们一键完成筛选和计算。

1.插入数据透视表：点击超级表中的任意一个单元格，然后点击菜单栏的【插入】→【数据透视表】，在弹出的对话框中直接点击【确定】。

2.配置透视表字段：在右侧的 “数据透视表字段” 面板中：将 “班级” 字段拖到 “行” 区域；将 “是否前 80%” 字段拖到 “列” 区域；将 “语文” 字段拖到 “值” 区域，设置汇总方式为 “平均值”：此时 “值” 区域默认显示的是 “求和项：语文”，我们需要将其改为平均值；点击 “求和项：语文”，选择【值字段设置】；在弹出的对话框中，将 “汇总方式” 改为 “平均值”；点击【数字格式】，设置小数位数为 2，点击【确定】完成设置。

查看结果：现在，一张清晰的统计表格就生成了！它会分别显示出各班 “是”（前 80%）和 “否”（后 20%）两个群体的语文平均分。我们重点关注 “是” 这一列对应的数值，这就是我们想要的各班班次前 80% 学生的语文平均分。

第三步：结果验证：为确保统计准确，可以随机选择一个班级，用数据透视表再次统计其 “是” 和 “否” 的人数，验证是否符合 80% 和 20% 的比例。平均值是否正确。

本节课我们重点掌握三方面内容：一是核心流程：两步轻松搞定 —— 先用 IF+COUNTIF+INT 函数组合添加辅助列，判断学生是否属于本班前 80%；再用数据透视表快速筛选、精准统计平均分。二是函数应用：COUNTIF 统计班级人数，INT 向下取整保证人数为整数，IF 完成最终判断，三者配合是实现精准筛选的关键。三是操作要点：函数公式书写准确、数据透视表配置规范，是确保统计结果正确的核心。

课后请大家完成两项练习：

1.复用本节课方法，计算各班班次前 80% 学生的数学、英语平均分；

2.对比各班 “前 80% 学生语文平均分” 与 “全体学生语文平均分”，开展简单的教学质量分析，观察高分段学生成绩表现。

多练多用，就能熟练掌握函数组合与数据透视表的综合应用，轻松搞定各类精准数据分析！

NEW YEAR -