国家学生体质健康标准测试统计表 必用公式

国家学生体质健康标准数据表加工必用公式

五年级男生等级公式

=LOOKUP(J2,{0,8.7,8.9,10.9},{"优秀","良好","及格","不及格"}) {男生50米}

=LOOKUP(P2,{0,56,126,138},{"不及格"，"及格", "良好","优秀"}) {跳绳} =LOOKUP(M2,{-20,-2.6,9.4,13.8},{"不及格","及格","良好","优秀"}){体前屈}

=LOOKUP(S2,{0,18,38,44},{"不及格","及格","良好","优秀"}){仰卧起坐}

=LOOKUP(w2,{0,103,109,139},{"优秀","良好","及格","不及格"}) {男生

50*8往返跑米跑}

五年级男生分数

=LOOKUP(M2,{-20,-6.6,-5.6，-4.6，-3.6，-2.6，-1.4，-0.2，1，2.2，3.4，4.6，5.8，7，8.2，9.4，11.6，13.8，15.2，

16.5},{"10","20","30","40","50","60","62","64","66","68","70","72"," 74","76","78","80","85","90","95","100"}){体前屈}

=LOOKUP(S2,{0，10，12，14，16，18，20，22，24，26，28，30，32，34，36，38，41，44，47，

50 },{"10","20","30","40","50","60","62","64","66","68","70","72","7 4","76","78","80","85","90","95","100"}){仰卧起坐}

=LOOKUP(P2,{0,44，47，50，53，56，63，70，77，84，91，98，105，112，119，126，132，138，143，

148},{"10","20","30","40","50","60","62","64","66","68","70","72","7 4","76","78","80","85","90","95","100"}){跳绳}

=LOOKUP(j2, {0,8.4,8.5，8.6，8.7，8.8,9，9.2，9.4，9.6，9.8，10，10.2，

10.4，10.6，10.8，11，11.2，11.4，

11.6 },{"100","95","90","85","80","78","76","74","72","70","68","66" ,"64","62","60","50","40","30","20","10"}) {男生50米跑}

=LEFT(V2,1)*60+MID(V2,3,2){有分钟的时间转化为秒}

=LOOKUP(w2,{0,96,99,102,105,108,111,114,117,120,123,126,129,123,135, 138,142,146,150,154 },{"100","95","90","85","80","78","76","74","72" ,"70","68","66","64","62","60","50","40","30","20","10"}) {男生50*8往返跑米跑}

五年级女生等级公式

=LOOKUP(J2,{0,8.6,9.2,11.2},{"优秀","良好","及格","不及格"}) {女生50米}

=LOOKUP(P2,{0,58,128，144},{"不及格"，"及格", "良好","优秀"}) {跳绳} =LOOKUP(M2,{-20,2,13,17.2},{"不及格","及格","良好","优秀"}){体前屈}

=LOOKUP(S2,{0,18，38,44},{"不及格","及格","良好","优秀"}){仰卧起坐}

=LOOKUP(w2,{0,108,114,144},{"优秀","良好","及格","不及格"}) {女生

50*8往返跑米跑}

五年级生分数

=LOOKUP(M2,{-20,-1.2，-0.4，0.4，1.2，2，3.1，4.2，5.3，6.4，7.5，8.6，9.7，10.8，11.9，13，15.1，17.2，18.5，

19.8},{"10","20","30","40","50","60","62","64","66","68","70","72"," 74","76","78","80","85","90","95","100"}){体前屈}

=LOOKUP(S2,{0, 10，12，14，16，18，20，22，24，26，28，30，32，34，36，38，41，44，46，

48 },{"10","20","30","40","50","60","62","64","66","68","70","72","7 4","76","78","80","85","90","95","100"}){仰卧起坐}

=LOOKUP(P2,{0,46，49，52，55，58，65，72，79，86，93，100，107，114，121，128，136，144，151，

158},{"10","20","30","40","50","60","62","64","66","68","70","72","7 4","76","78","80","85","90","95","100"}){跳绳}

=LOOKUP(j2, {0,8.3，8.4,8.5，8.8，9.1，9.3，9.5，9.7，9.9，10.1，10.3，

10.5，10.7，10.9，11.1，11.3，11.5，11.7，

11.9 },{"100","95","90","85","80","78","76","74","72","70","68","66" ,"64","62","60","50","40","30","20","10"}) {女生50米跑}

=LEFT(V2,1)*60+MID(V2,3,2){有分钟的时间转化为秒}时间要求：数据样式为：

=LOOKUP(w2,{101,104,107,110,113,116,119,122,125,128,121,134,137,140, 143,147,151，155，

163 },{"100","95","90","85","80","78","76","74","72","70","68","66", "64","62","60","50","40","30","20","10"}) {女生50*8往返跑米跑}