表A
性别 | 身高 | 体重 | 肺活量 | 50米跑 | 坐位体前屈 | 一分钟跳绳 | 一分钟仰卧起坐 | 50米×8往返跑 |
2 | 135 | 28 | 1800 | 10.2 | 5.5 | 50.0 | 35 | 1′59″ |
2 | 148 | 33 | 1900 | 9.2 | 16 | 130.0 | 25 | 1′59″ |
1 | 142 | 40 | 1700 | 10 | 3.5 | 53.0 | 21 | 2′02″ |
1 | 143 | 33 | 2000 | 8.3 | 7 | 110.0 | 32 | 1′31″ |
2 | 138 | 35 | 1600 | 10 | 15.5 | 126.0 | 23 | 1′55″ |
2 | 146 | 28.5 | 1700 | 9.3 | 8 | 68.0 | 27 | 1′48″ |
表B
Male | Female | |||
年级 | 单项 | 单项 | ||
得分 | 年级 | 得分 | ||
8.4 | 100 | 8.3 | 100 | |
8.5 | 95 | 8.4 | 95 | |
8.6 | 90 | 8.5 | 90 | |
8.7 | 85 | 8.8 | 85 | |
8.8 | 80 | 9.1 | 80 | |
9 | 78 | 9.3 | 78 | |
9.2 | 76 | 9.5 | 76 | |
9.4 | 74 | 9.7 | 74 | |
9.6 | 72 | 9.9 | 72 | |
9.8 | 70 | 10.1 | 70 | |
10 | 68 | 10.3 | 68 | |
10.2 | 66 | 10.5 | 66 | |
10.4 | 64 | 10.7 | 64 | |
10.6 | 62 | 10.9 | 62 | |
10.8 | 60 | 11.1 | 60 | |
11 | 50 | 11.3 | 50 | |
11.2 | 40 | 11.5 | 40 | |
11.4 | 30 | 11.7 | 30 | |
11.6 | 20 | 11.9 | 20 | |
11.8 | 10 | 12.1 | 10 |
函数
=IF(G2=1,VLOOKUP(N2,fiftymeter!$C$4:$D$23,2,TRUE),VLOOKUP(N2,fiftymeter!$F$4:$G$23,2,TRUE))
=IF(G2=1,IF(L2<=14.3,80,(IF(L2<=21.4,100,IF(L2<=24.2,80,60)))),IF(L2<=13.7,80,(IF(L2<=20.5,100,IF(L2<=23,80,60)))))
=M2*0.15+Y2*0.15+O2*0.2+Q2*0.2+S2*0.2+W2*0.1
链接:
Excel函数与公式实战技巧精粹
http://wenku.baidu.com/course/view/1b754793daef5ef7ba0d3c83