Excel在教务中的应用

panda306

一、标准化考场编排
    1、每个考场30人，人数可以按实际需要调整。
    2、考场座位有蛇形分布和降序分布（即每一列都按小号在前，大号在后的顺序排列）两种分布表供大家选用。
    3、各班学生去向有按考生号和考场分布两种排列。        4、选中第1考场整个区域，用填充柄下拉填充，就能形成所有考场座位表。

panda306

1、可根据总表生成各班及科任分课表。
2、操作说明表中有标签注释。
3、课表编排参考了湖南长沙陈家生老师的编排设计，在此特别鸣谢。

panda306

1. =IF(A2="","",ROUND(TREND(INDEX({100,86;85,71;70,56;55,41;40,30},MATCH((RANK(A2,A:A)-1)/COUNT(A:A),{0,15,50,85,98}%),),INDEX(IF({1,0},LARGE(A:A,COUNTIF(A:A,">="&LARGE(A:A,COUNT(A:A)*{0;15;50;85;98;100}%))+1),LARGE(A:A,COUNT(A:A)*{15;50;85;98;100}%)),MATCH((RANK(A2,A:A)-1)/COUNT(A:A),{0,15,50,85,98}%),),A2),))

复制代码

1. =IF(A2="","",ROUND(IF(A2>=PERCENTILE(A:A,85%),IF(MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A))=PERCENTILE(A:A,100%),(100+86)/2,IF(A2=PERCENTILE(A:A,100%),100,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)),86,(100*(A2-MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)))+86*(PERCENTILE(A:A,100%)-A2))/(PERCENTILE(A:A,100%)-MIN(IF(A:A>=PERCENTILE(A:A,85%),A:A)))))),IF(A2>=PERCENTILE(A:A,50%),IF(MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,85%),A:A)),(71+85)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,85%),A:A)),85,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)),71,(85*(A2-MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)))+71*(MAX(IF(A:A<PERCENTILE(A:A,85%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,85%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,50%),A:A)))))),IF(A2>=PERCENTILE(A:A,15%),IF(MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,50%),A:A)),(56+70)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,50%),A:A)),70,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)),56,(70*(A2-MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)))+56*(MAX(IF(A:A<PERCENTILE(A:A,50%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,50%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,15%),A:A)))))),IF(A2>=PERCENTILE(A:A,2%),IF(MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A))=MAX(IF(A:A<PERCENTILE(A:A,15%),A:A)),(41+55)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,15%),A:A)),55,IF(A2=MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)),41,(55*(A2-MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)))+41*(MAX(IF(A:A<PERCENTILE(A:A,15%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,15%),A:A))-MIN(IF(A:A>=PERCENTILE(A:A,2%),A:A)))))),IF(PERCENTILE(A:A,0%)=MAX(IF(A:A<PERCENTILE(A:A,2%),A:A)),(30+40)/2,IF(A2=MAX(IF(A:A<PERCENTILE(A:A,2%),A:A)),40,IF(A2=PERCENTILE(A:A,0%),30,(40*(A2-PERCENTILE(A:A,0%))+30*(MAX(IF(A:A<PERCENTILE(A:A,2%),A:A))-A2))/(MAX(IF(A:A<PERCENTILE(A:A,2%),A:A))-PERCENTILE(A:A,0%))))))))),))

复制代码

1、用trend公式直接赋分。分界点明确，赋分准确。
2、在公式编写过程中受到论坛里多位老师的热心指导，在此一并感谢！
3、用percentile编写的公式与用trend编写的公式结果是一致的，纯属娱乐。

panda306

以各班总分排名前95%的学生为统计对象，汇总平均分、及格率和优秀率。           
1、统计对象的选取：相当于将各班学生按总成绩降序排列，选取排名前95%（四舍五入）的学生进行统计。如果排名95%处有多人并列，则取行号较小的学生进行统计，以确保统计的人数占总人数的95%。如果某科实际参考人数少于班级人数的95%，则按实际参看人数统计。
2、根据三种不同的思路设计了三种函数套路，结果是一致的的。
3、表格中包含根据各班名拆分表格。以求81班前95%学生平均分为例：
Large版：=AVERAGE(IF((汇总!$Q$3:$Q$500>=LARGE(IF(汇总!$A$3:$A$500=81,汇总!$Q$3:$Q$500),ROUND(COUNTIF(汇总!$A$3:$A$500,81)*0.95,0)))*(汇总!$A$3:$A$500=81),汇总!$D$3:$D$500))（数组公式：同时按Ctrl+Shift+Enter输入）
Countif版：=AVERAGEIFS(汇总!$D$3:$D$500,汇总!$A$3:$A$500,81,汇总!$R$3:$R$500,"<="&ROUND(COUNTIF(汇总!$A$3:$A$500,81)*0.95,0))

Offset版：=AVERAGE(OFFSET(汇总!$D$2,MATCH(81,汇总!$A$3:汇总!$A$3:$A$500,0),,ROUND(COUNTIF(汇总!$A$3:$A$500,81)*0.95,0)))

表格中用mod、int、row和column做了嵌套，只需完成第一科各项数据的统计，然后选中该科整个区域，用填充柄右拉、下拉填充，就能生成其他科目的指标。

panda306

该表用于统计老师代课的课时，使用数据透视表制作。先定义名称：=OFFSET('2019.7月请假及代课'!$A$2,0,0,COUNTA('2019.7月请假及代课'!$A:$A)-1,COUNTA('2019.7月请假及代课'!$A$2:$J$2))，并作为数据透视表的统计区域。有新的代课，只需依次点击分析、刷新。

panda306

4楼offset成绩总表必须按照以下两个步骤预先处理：
1、排序辅助列降序排列；
2、再按班级升序排列。
形成以班级为连续区域，班级内部按照总成绩由高到低排列的格局。

panda306

1、在A2单元格中输入=成绩!A$2,右拉填充至L2单元格，制作标题列。2、在A3单元格输入=OFFSET(成绩!$A$3,(ROW()-3)/2,COLUMN()-1)，右拉填充至L3单元格，制作第一个学生成绩单。
3、选中A2:L3区域（第一个学生成绩单），用填充并下拉填充，制作其他学生成绩单。

4、如果学生之间要留一行空白，将=OFFSET(成绩!$A$3,(ROW()-3)/2,COLUMN()-1)改为：=OFFSET(成绩!$A$3,(ROW()-3)/3,COLUMN()-1)，选中A2:L4区域（第一个学生成绩单），用填充并下拉填充，制作其他学生成绩单。

向南流

麻烦楼主看下我的帖子，能否帮忙解决一下。一直在线等结果

panda306

下拉列表版

jqingsong

收藏先，学习一下