七年级体质健康得分等级自动生成表函数：excel自动生成排名
（A列为班级，B列为姓名，C列为性别，D列为身高，E列为体重，F列为MBI，G列为得分，H列为等级）
体重MBI成绩生成：
=ROUND(E2/(D2*D2),1)
体重得分生成函数：
=IF(IF(C2="男",IF(F2>=25,60,IF(F2>=22.2,80,IF(F2<=15.4,80,100))),IF(F2>=24.5,60,IF(F2>=21.8,80,IF(F2<=14.7,80,100))))>100,100,IF(C2="男",IF(F2>=25,60,IF(F2>=22.2,80,IF(F2<=15.4,80,100))),IF(F2>=24.5,60,IF(F2>=21.8,80,IF(F2<=14.7,80,100)))))
体重等级生成：
=IF(C2="男",IF(F2>=25,"肥胖",IF(F2>=22.2,"超重",IF(F2<=15.4,"低体重","正常"))),IF(F2>=24.5,"肥胖",IF(F2>=21.8,"超重",IF(F2<=14.7,"低体重","正常"))))
肺活量得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级）
=IF(IF(C2="男",IF(D2>=3400,ROUND(90+(D2-3400)/24,),IF(D2>=2900,ROUND(80+(D2-2900)/50,),IF(D2>=1700,ROUND(60+(D2-1700)/60,),ROUND(50-(1600-D2)/10,)))),IF(D2>=2550,ROUND(90+(D2-2550)/20,),IF(D2>=2350,ROUND(80+(D2-2350)/20,),IF(D2>=1350,ROUND(60+(D2-1350)/50,),ROUND(50-(1310-D2)/4,)))))>100,100,IF(C2="男",IF(D2>=3400,ROUND(90+(D2-3400)/24,),IF(D2>=2900,ROUND(80+(D2-2900)/50,),IF(D2>=1700,ROUND(60+(D2-1700)/60,),ROUND(50-(1600-D2)/10,)))),IF(D2>=2550,ROUND(90+(D2-2550)/20,),IF(D2>=2350,ROUND(80+(D2-2350)/20,),IF(D2>=1350,ROUND(60+(D2-1350)/50,),ROUND(50-(1310-D2)/4,))))))
50M跑得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级）
=IF(IF(C2="男",IF(D2<=8,90+(8-D2)*50,IF(D2<=8.2,80+(8.2-D2)*50,IF(D2<=10.2,60+(10.2-D2)*10,50-(D2-10.4)*50))),IF(D2<=8.3,90+(8.3-D2)*50,IF(D2<=8.9,ROUND(80+(8.9-D2)*50/3,),IF(D2<=10.9,60+(10.9-D2)*10,ROUND(50-(D2-11.1)*50,)))))>100,100,IF(C2="男",IF(D2<=8,90+(8-D2)*50,IF(D2<=8.2,80+(8.2-D2)*50,IF(D2<=10.2,60+(10.2-D2)*10,50-(D2-10.4)*50))),IF(D2<=8.3,90+(8.3-D2)*50,IF(D2<=8.9,ROUND(80+(8.9-D2)*50/3,),IF(D2<=10.9,60+(10.9-D2)*10,ROUND(50-(D2-11.1)*50,))))))
坐位体前屈得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级）
=IF(IF(C2="男",IF(D2>=14.2,ROUND(90+(D2-14.2)*50/17,),IF(D2>=10.4,ROUND(80+(D2-10.4)*50/19,),IF(D2>=-2.6,ROUND(60+(D2+2.6)*20/13,),ROUND(50-(-3.8-D2)*25/3,)))),IF(D2>=18.4,ROUND(90+(D2-18.4)*50/17,),IF(D2>=15,ROUND(80+(D2-15)*50/17,),IF(D2>=2,ROUND(60+(D2-2)*20/13,),ROUND(50-(1.2-D2)*25/2,)))))>100,100,IF(C2="男",IF(D2>=14.2,ROUND(90+(D2-14.2)*50/17,),IF(D2>=10.4,ROUND(80+(D2-10.4)*50/19,),IF(D2>=-2.6,ROUND(60+(D2+2.6)*20/13,),ROUND(50-(-3.8-D2)*25/3,)))),IF(D2>=18.4,ROUND(90+(D2-18.4)*50/17,),IF(D2>=15,ROUND(80+(D2-15)*50/17,),IF(D2>=2,ROUND(60+(D2-2)*20/13,),ROUND(50-(1.2-D2)*25/2,))))))
立定跳远得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级）
=IF(IF(C2="男",IF(D2>=211,ROUND(90+(D2-211)*5/7,),IF(D2>=195,ROUND(80+(D2-195)*5/8,
)
,IF(D2>=155,ROUND(60+(D2-155)/2,),ROUND(50-(150-D2)*2,)))),IF(D2>=184,ROUND(90+(D2-184)*5/6,),IF(D2>=170,ROUND(80+(D2-170)*5/7,),IF(D2>=140,ROUND(60+(D2-140)*2/3,),ROUND(50-(135-D2)*2,)))))>100,100,IF(C2="男",IF(D2>=211,ROUND(90+(D2-211)*5/7,),IF(D2>=195,ROUND(80+(D2-195)*5/8,),IF(D2>=155,ROUND(60+(D2-155)/2,),ROUND(50-(150-D2)*2,)))),IF(D2>=184,ROUND(90+(D2-184)*5/6,),IF(D2>=170,ROUND(80+(D2-170)*5/7,),IF(D2>=140,ROUND(60+(D2-140)*2/3,),ROUND(50-(135-D2)*2,))))))
男生引体向上女生仰卧起坐得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级）
=IF(IF(C2="男",IF(D2>=11,90+(D2-11)*5,IF(D2>=9,80+(D2-9)*5,IF(D2>=4,60+(D2-4)*4,50-(3-D2)*10))),IF(D2>=46,ROUND(90+(D2-46)*5/2,),IF(D2>=40,ROUND(80+(D2-40)*5/3,),IF(D2>=20,60+(D2-20),50-(18-D2)*5))))>100,100,IF(C2="男",IF(D2>=11,90+(D2-11)*5,IF(D2>=9,80+(D2-9)*5,IF(D2>=4,60+(D2-4)*4,50-(3-D2)*10))),IF(D2>=46,ROUND(90+(D2-46)*5/2,),IF(D2>=40,ROUND(80+(D2-40)*5/3,),IF(D2>=20,60+(D2-20),50-(18-D2)*5)))))
男生1000M女生800M得分生成函数：
（A列为班级，B列为姓名，C列为性别，D列为成绩，E列为得分，F列为等级，G列为辅助列）
G列辅助函数：
=LEFT(D2,1)*60+MID(D2,3,2)
E列得分生成函数：
=IF(IF(C2="男",IF(G2<=255,ROUND(90+(255-G2)/2,),IF(G2<=270,ROUND(80+(270-G2)*5/8,),IF(G2<=320,ROUND(60+(320-G2)*2/5,),ROUND(50-(G2-340)/2,)))),IF(G2<=229,ROUND(90+(229-G2)*5/7,),IF(G2<=245,ROUND(80+(245-G2)*5/8,),IF(G2<=295,ROUND(60+(295-G2)*2/5,),ROUND(50-(G2-305),)))))>100,100,IF(C2="男",IF(G2<=255,ROUND(90+(255-G2)/2,),IF(G2<=270,ROUND(80+(270-G2)*5/8,),IF(G2<=320,ROUND(60+(320-G2)*2/5,),ROUND(50-(G2-340)/2,)))),IF(G2<=229,ROUND(90+(229-G2)*5/7,),IF(G2<=245,ROUND(80+(245-G2)*5/8,),IF(G2<=295,ROUND(60+(295-G2)*2/5,),ROUND(50-(G2-305),))))))
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论