excel14

题目

滨海市对重点中学组织了一次物理统考，并生成了所有考生和每一个题目的得分。市教委要求小罗老师根据已有数据，统计分析各学校及班级的考试情况。请根据考生文件夹下“素材.xlsx”中的数据，帮助小罗完成此项工作。具体要求如下：

1. 更名

2. 利用“成绩单”、“小分统计”和“分值表”工作表中的数据，完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下：

⑴“考试学生数”列必须利用公式计算，“平均分”列由“成绩单”工作表数据计算得出；

---

“按班级汇总”表

“考试学生数”----COUNTIFS

示例：=COUNTIFS(成绩单!$A$2:$A$950,A2,成绩单!$B$2:$B$950,B2)

在表“按班级汇总”中的“考试学生数”第一个空格输入

第一行：“成绩单”学校列（除第一行，且需要绝对引用）----------指定区域：学校列

第二行：“按班级汇总”当行的学校即A2单元格------指定条件：当行学校

第三行：“成绩单”班号列（除第一行，且需要绝对引用）----------指定区域：班号列

第四行：当行的班号------指定条件：当行班号

---

“最高分”---if和max函数

max放在If函数外面

if函数【ctrl+shift+enter】❗❗❗（直接enter和按ctrl+shift+enter，结果不一样！）

示例：=MAX(IF((成绩单!$A$2:$A$950=按班级汇总!A2)*(成绩单!$B$2:$B$950=按班级汇总!B2),成绩单!$D$2:$D$950))

再按ctrl+shift+enter，会自动加上{ }

第一行：（“成绩单”学校列除第一行以及绝对引用=当行学校）*（“成绩单”班号列除第一行以及绝对引用=当行班号）

第二行：“成绩单”物理列除第一行以及绝对引用

---

“最低分”也是如此，复制过来，将max改成min。

---

“平均分”也是，改为average

---

"客观题/主观题平均分"---sum

选择“小分统计”第二行的1-40/41-50

---

“按学校汇总”表

考试学生数：运用sumif条件函数

第一行；"按班级汇总"学校列（不要第一行，绝对引用）

第二行：当行学校

第三行："按班级汇总"考试学生数列（不要第一行，绝对引用）

“最高分”---if和max函数【max在外，if在里】

示例：=MAX(IF(按班级汇总!$A$2:$A$33=按学校汇总!A2,按班级汇总!$D$2:$D$33))

第一行："按班级汇总"学校列（不要第一行，绝对引用）=当行学校

第二行："按班级汇总"最高分列

再按ctrl+shift+enter

---

“最低分”---最好不要复制，因为复制要改数据。

示例：=MIN(IF(按班级汇总!$A$2:$A$33=按学校汇总!A2,按班级汇总!$E$2:$E$33))

---

“平均分”---条件平均函数averageif

示例：=AVERAGEIF(成绩单!$A$2:$A$950,A2,成绩单!$D$2:$D$950)

第一行：（条件的范围）“成绩单”表中学校列（除第一行，绝对引用）

第二行：（条件）当行的学校

第三行：“成绩单”表物理列（绝对引用）

---

客观题平均分-----sum和if函数【sum在外】

示例：=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!A2,(按班级汇总!$G$2:$G$33)*(按班级汇总!$C$2:$C$33)))/B2

if函数

第一行：“按班级汇总”第一列（绝对引用）=当行的学校

第二行：（表"按班级汇总"中的客观题得分列，绝对引用）*（表"按班级汇总"中的考生数列，绝对引用）

写完if函数，外部包着sum函数，再除以当行的学生数

主观题平均分【可复制客观题的，再改相关数据】

示例：=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!A2,(按班级汇总!$H$2:$H$33)*(按班级汇总!$C$2:$C$33)))/B2

---

1得分率H2单元格----sum和if函数【sum在外】

示例：=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!$A$2,(小分统计!$C$2:$C$33)*(按班级汇总!$C$2:$C$33)))/($B$2*分值表!B3)   【❗记得按ctrl+shift+enter】

if函数

第一行：“按班级汇总”表中学校列（绝对引用）=当行学校（绝对引用）【因为要向右填充】

第二行：分析：平均分*人数

（表小分统计中的C列绝对引用）*(“按班级汇总”表中的学生数，绝对引用)

写完if函数，外部包着sum函数，再除以（当行的学生数绝对引用*分值表中的B3）

H3单元格复制，改下相应单元格即可【$A$2改为$A$3；$B$2改为$B$3】   【❗记得按ctrl+shift+enter】

H4、5同样。因为A2、3、4、5分别是第一/二/三/四中学

填完右拉全部填充

---

⑵“分值表”工作表中给出了本次考试各题的类型及分值。（备注：本次考试一共50道小题，其中【1】至【40】为客观题，【41】至【50】为主观题）；

⑶“小分统计”工作表中包含了各班级每一道小题的平均得分，通过其可计算出各班级的“客观题平均分”和“主观题平均分”。（备注：由于系统生成每题平均得分时已经进行了四舍五入操作，因此通过其计算“客观题平均分”和“主观题平均分”之和时，可能与根据“成绩单”工作表的计算结果存在一定误差）；

⑷利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”，计算方法为：每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数；

⑸计算“按学校汇总”工作表中的每题得分率，即：每个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。

⑹所有工作表中“考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。

更改数字格式

会发现“考试学生数”、“最高分”、“最低分”本就已经显示为整数，

将“按班级汇总”右3列平均分设置数值2位

得分率：百分比2位

3. 新建“按学校汇总2”工作表，将“按学校汇总”工作表中所有单元格数值转置复制到新工作表中。

第一问：新建表，命名“按学校汇总2”

第二问：在”按学校汇总“随选一个，按ctrl+A→复制→点击表“按学校汇总2”A1选择性粘贴。  点击格式、转置

再点A1→右键选择性粘贴。  点击数值、转置

4. 将“按学校汇总2”工作表中的内容套用表格样式为“表样式中等深浅12”；将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式，将介于80%和90%之间的单元格标记为“黄填充色深黄色文本”格式。

第一问：套用表格

选中中等深浅12【2行第5个】

第二问：将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式，将介于80%和90%之间的单元格标记为“黄填充色深黄色文本”格式。

选中得分率区域→开始→样式→条件格式→突出显示→小于→80%     浅红填充色深红色文本

选中得分率区域→开始→样式→条件格式→突出显示→介于→80%    90%    黄填充色深黄色文本

完

-2020.1.30

注：只做到了25分，还有地方有些错误