# Python+Excel数据分析实战：军事体能考核成绩评定(六)男女蛇形跑计算

### 二、代码实现

1.我把计算男子和女子蛇形跑的文件分开，分别放入单独的模块中，男子的文件命名为serpentine_run_male.py。因为男女蛇形跑读取不同的工作簿中的标准表，分开做也方便。计算函数的原理与前面的文章相同，增加了一项判定，若原始数据类型不是整型和浮点型，则不执行计算，打印错误。

```# 男子蛇形跑的成绩计算
# 从工作表“男子蛇形跑标准”中读取数据

import openpyxl
class Serpentine_run_male_standard_data():
ws_Serpentine_run_male = wb['男子30米蛇形跑标准']

age24 = {}     # 24岁以下，{原始男子蛇形跑秒数：分数}
age25_27 = {}  # 25~27岁，{原始男子蛇形跑秒数：分数}
age28_30 = {}
age31_33 = {}
age34_36 = {}
age37_39 = {}

age40_42 = {}  # 40岁以上，{原始男子蛇形跑秒数：分数}
age43_45 = {}  # 43~45岁，{原始男子蛇形跑秒数：分数}
age46_48 = {}
age49_51 = {}
age52_54 = {}
age55_57 = {}
age58_59 = {}

rngs = ws_Serpentine_run_male.iter_rows(min_row=3,max_row=64,min_col=1,max_col=26)
# 生成{原始男子蛇形跑秒数：分数}的字典
for row in rngs:
#print([c.value for c in row])
age24[row[1].value] = row[0].value
age25_27[row[3].value] = row[2].value
age28_30[row[5].value] = row[4].value
age31_33[row[7].value] = row[6].value
age34_36[row[9].value] = row[8].value
age37_39[row[11].value] = row[10].value

age40_42[row[13].value] = row[12].value
age43_45[row[15].value] = row[14].value
age46_48[row[17].value] = row[16].value
age49_51[row[19].value] = row[18].value
age52_54[row[21].value] = row[20].value
age55_57[row[23].value] = row[22].value
age58_59[row[25].value] = row[24].value
# print('-----age24-----') # 打印数据以便检查
# for m in age24.items():
#     print(m)
# print('-----age25_27-----')
# for m in age25_27.items():
#     print(m)
# print('-----age28_30-----')
# for m in age28_30.items():
#     print(m)
# print('-----age31_33-----')
# for m in age31_33.items():
#     print(m)
# print('-----age34_36-----')
# for m in age34_36.items():
#     print(m)
# print('-----age37_39-----')
# for m in age37_39.items():
#     print(m)

# print('-----age40_42-----')  # 打印数据以便检查
# for m in age40_42.items():
#     print(m)
# print('-----age43_45-----')
# for m in age43_45.items():
#     print(m)
# print('-----age46_48-----')
# for m in age46_48.items():
#     print(m)
# print('-----age49_51-----')
# for m in age49_51.items():
#     print(m)
# print('-----age52_54-----')
# for m in age52_54.items():
#     print(m)
# print('-----age55_57-----')
# for m in age55_57.items():
#     print(m)
# print('-----age58_59-----')
# for m in age58_59.items():
#     print(m)

def serpentine_run_male_score_computing(self,age,original_amount):
'''根据年龄，实际蛇形跑秒数，计算所得分数'''
if type(original_amount) is float or int:  # 核查数据是否是整型
if age <= 24:
if original_amount > 21.2:
return 0
elif original_amount < 18.1:
return 100+181 - original_amount*10  # 防止小数计算出现不准确的情况
elif 18.1 <= original_amount <= 21.2:
return self.age24[original_amount]
elif 25 <= age <= 27:
if original_amount > 21.5:
return 0
elif original_amount < 18.3:
return 100+183 - original_amount*10
elif 18.3 <= original_amount <= 21.5:
return self.age25_27[original_amount]
elif 28 <= age <= 30:
if original_amount > 21.9:
return 0
elif original_amount < 18.5:
return 100+185 - original_amount*10
elif 18.5 <= original_amount <= 21.9:
return self.age28_30[original_amount]
elif 31 <= age <= 33:
if original_amount > 22.2:
return 0
elif original_amount < 18.7:
return 100+187 - original_amount*10
elif 18.7 <= original_amount <= 22.2:
return self.age31_33[original_amount]
elif 34 <= age <= 36:
if original_amount > 22.6:
return 0
elif original_amount < 18.9:
return 100+189 - original_amount*10
elif 18.9 <= original_amount <= 22.6:
return self.age34_36[original_amount]
elif 37 <= age <= 39:
if original_amount > 22.7:
return 0
elif original_amount < 19.1:
return 100 + 191 - original_amount*10
elif 19.1 <= original_amount <= 22.7:
return self.age37_39[original_amount]
# 40~59
elif 40 <= age <= 42:
if original_amount > 22.9:
return 0
elif original_amount < 19.3:
return 100 + 193 - original_amount*10
elif 19.3 <= original_amount <= 22.9:
return self.age40_42[original_amount]
elif 43 <= age <= 45:
if original_amount > 23.3:
return 0
elif original_amount < 19.5:
return 100 + 195 - original_amount*10
elif 19.5 <= original_amount <= 23.3:
return self.age43_45[original_amount]
elif 46 <= age <= 48:
if original_amount > 23.9:
return 0
elif original_amount < 19.7:
return 100 + 197 - original_amount*10
elif 19.7 <= original_amount <= 23.9 :
return self.age46_48[original_amount]
elif 49 <= age <= 51:
if original_amount > 24:
return 0
elif original_amount < 19.9:
return 100 + 199 - original_amount*10
elif 19.9 <= original_amount <= 24 :
return self.age49_51[original_amount]
elif 52 <= age <= 54:
if original_amount > 24.5:
return 0
elif original_amount < 20.6:
return 100 + 206 - original_amount*10
elif 20.6 <= original_amount <= 24.5:
return self.age52_54[original_amount]
elif 55 <= age <= 57:
if original_amount > 24.8:
return 0
elif original_amount < 21.1:
return 100 + 211 - original_amount*10
elif 21.1 <= original_amount <= 24.8:
return self.age55_57[original_amount]
elif 58 <= age <= 59:
if original_amount > 25.4:
return 0
elif original_amount < 21.3:
return 100 + 213 - original_amount*10
elif 21.3 <= original_amount <= 25.4:
return self.age58_59[original_amount]
else:
print('ERROR：男子蛇形跑，原始成绩的数据类型须为整型(int)或者浮点型(float)类型')

if __name__ == "__main__":   # 测试本模块
serpentine_run_male_std = Serpentine_run_male_standard_data()
result = serpentine_run_male_std.serpentine_run_male_score_computing(35,18)
print(result)```

2.女子仰卧起坐的文件命名为serpentine_run_female.py，除了读取的工作簿不同，其它内容与男子大体相同。只是在参照标准编写程序时要特别细心，设置临界数值时不能出错，比如24岁以下女子大于22.6秒0分，20秒100分，但是25岁~27岁是大于22.9秒0分，20.3秒100分。这种变化也可以通过编程实现自动化计算，虽变化次数不多，但设计算法的复杂度会增加很多，这里有改进的空间，可以探索。一切相似重复的代码段都可以使用函数替代吧。

```# 女子蛇形跑的成绩计算
# 从工作表“女子蛇形跑标准”中读取数据

import openpyxl
class Serpentine_run_female_standard_data():
ws_Serpentine_run_female = wb['女子30米蛇形跑标准']

age24 = {}     # 24岁以下，{原始女子蛇形跑秒数：分数}
age25_27 = {}  # 25~27岁，{原始女子蛇形跑秒数：分数}
age28_30 = {}
age31_33 = {}
age34_36 = {}
age37_39 = {}

age40_42 = {}  # 40岁以上，{原始女子蛇形跑秒数：分数}
age43_45 = {}  # 43~45岁，{原始女子蛇形跑秒数：分数}
age46_48 = {}
age49_51 = {}
age52_54 = {}
age55_57 = {}
age58_59 = {}

rngs = ws_Serpentine_run_female.iter_rows(min_row=3,max_row=29,min_col=1,max_col=14)
# 生成{原始女子蛇形跑秒数：分数}的字典
for row in rngs:
#print([c.value for c in row])
age24[row[1].value] = row[0].value
age25_27[row[2].value] = row[0].value
age28_30[row[3].value] = row[0].value
age31_33[row[4].value] = row[0].value
age34_36[row[5].value] = row[0].value
age37_39[row[6].value] = row[0].value

age40_42[row[7].value] = row[0].value
age43_45[row[8].value] = row[0].value
age46_48[row[9].value] = row[0].value
age49_51[row[10].value] = row[0].value
age52_54[row[11].value] = row[0].value
age55_57[row[12].value] = row[0].value
age58_59[row[13].value] = row[0].value
# print('-----age24-----') # 打印数据以便检查
# for m in age24.items():
#     print(m)
# print('-----age25_27-----')
# for m in age25_27.items():
#     print(m)
# print('-----age28_30-----')
# for m in age28_30.items():
#     print(m)
# print('-----age31_33-----')
# for m in age31_33.items():
#     print(m)
# print('-----age34_36-----')
# for m in age34_36.items():
#     print(m)
# print('-----age37_39-----')
# for m in age37_39.items():
#     print(m)
#
# print('-----age40_42-----')  # 打印数据以便检查
# for m in age40_42.items():
#     print(m)
# print('-----age43_45-----')
# for m in age43_45.items():
#     print(m)
# print('-----age46_48-----')
# for m in age46_48.items():
#     print(m)
# print('-----age49_51-----')
# for m in age49_51.items():
#     print(m)
# print('-----age52_54-----')
# for m in age52_54.items():
#     print(m)
# print('-----age55_57-----')
# for m in age55_57.items():
#     print(m)
# print('-----age58_59-----')
# for m in age58_59.items():
#     print(m)

def serpentine_run_female_score_computing(self,age,original_amount):
'''根据年龄，实际蛇形跑秒数，计算所得分数'''
if type(original_amount) is float or int:  # 核查数据是否是整型
if age <= 24:
if original_amount > 22.6:
return 0
elif original_amount < 20:
return 100+200 - original_amount*10  # 防止小数计算出现不准确的情况
elif 20 <= original_amount <= 22.6:
return self.age24[original_amount]
elif 25 <= age <= 27:
if original_amount > 22.9:
return 0
elif original_amount < 20.3:
return 100+203 - original_amount*10
elif 20.3 <= original_amount <= 22.9:
return self.age25_27[original_amount]
elif 28 <= age <= 30:
if original_amount > 23.2:
return 0
elif original_amount < 20.6:
return 100+206 - original_amount*10
elif 20.6 <= original_amount <= 23.2:
return self.age28_30[original_amount]
elif 31 <= age <= 33:
if original_amount > 23.5:
return 0
elif original_amount < 20.9:
return 100+round((20.9 - original_amount),1)*10  # 防止小数计算出现不准确的情况
elif 20.9 <= original_amount <= 23.5:
return self.age31_33[original_amount]
elif 34 <= age <= 36:
if original_amount > 23.8:
return 0
elif original_amount < 21.2:
return 100+212 - original_amount*10
elif 21.2 <= original_amount <= 23.8:
return self.age34_36[original_amount]
elif 37 <= age <= 39:
if original_amount > 24.1:
return 0
elif original_amount < 21.5:
return 100+215 - original_amount*10
elif 21.5 <= original_amount <= 24.1:
return self.age37_39[original_amount]
# 40~59
elif 40 <= age <= 42:
if original_amount > 24.4:
return 0
elif original_amount < 21.8:
return 100 + 218 - original_amount*10
elif 21.8 <= original_amount <= 24.4:
return self.age40_42[original_amount]
elif 43 <= age <= 45:
if original_amount > 24.7:
return 0
elif original_amount < 22.1:
return 100 + 221 - original_amount*10
elif 22.1 <= original_amount <= 24.7:
return self.age43_45[original_amount]
elif 46 <= age <= 48:
if original_amount > 25:
return 0
elif original_amount < 22.4:
return 100 + 224 - original_amount*10
elif 22.4 <= original_amount <= 25:
return self.age46_48[original_amount]
elif 49 <= age <= 51:
if original_amount > 25.3:
return 0
elif original_amount < 22.7:
return 100 + 227 - original_amount*10
elif 22.7 <= original_amount <= 25.3:
return self.age49_51[original_amount]
elif 52 <= age <= 54:
if original_amount > 25.6:
return 0
elif original_amount < 23:
return 100 + 230 - original_amount*10
elif 23 <= original_amount <= 25.6:
return self.age52_54[original_amount]
elif 55 <= age <= 57:
if original_amount > 25.9:
return 0
elif original_amount < 23.3:
return 100 + 233 - original_amount*10
elif 23.3 <= original_amount <= 25.9:
return self.age55_57[original_amount]
elif 58 <= age <= 59:
if original_amount > 26.2:
return 0
elif original_amount < 23.6:
return 100 + 236 - original_amount*10
elif 23.6 <= original_amount <= 26.2:
return self.age58_59[original_amount]
else:
print('ERROR：女子蛇形跑，原始成绩的数据类型须为整型(int)或者浮点型(float)类型')

if __name__ == "__main__":   # 测试本模块
serpentine_run_female_std = Serpentine_run_female_standard_data()
result = serpentine_run_female_std.serpentine_run_female_score_computing(31,18.6)
print(result)```

3.对主程序training_performance.py进行修改，判断性别后，先计算单杠成绩，接着计算仰卧起坐成绩，再计算蛇形跑成绩，写入表中换算成绩的相应位置。

```import openpyxl
import datetime as dt
from age_computing import age_computing                       # 导入年龄计算模块
from pullup_male import Pullup_standard_data                  # 导入男子引体向上成绩计算模块
from flex_arm_hang_female import Flex_arm_hang_standard_data  # 导入女子单杠曲臂悬垂的成绩计算模块
from situp_male import Situp_male_standard_data               # 导入男子仰卧起坐成绩计算模块
from situp_female import Situp_female_standard_data           # 导入女子仰卧起坐成绩计算模块
from serpentine_run_male import Serpentine_run_male_standard_data       # 导入男子蛇形跑成绩计算模块
from serpentine_run_female import Serpentine_run_female_standard_data   # 导入女子蛇形跑成绩计算模块

ws_training_score = wb['体能考核成绩']

pullup_sd = Pullup_standard_data()             # 实例化男子引体向上成绩计算
flexarmhang_sd = Flex_arm_hang_standard_data() # 实例化女子单杠曲臂悬垂成绩计算
situp_male = Situp_male_standard_data()        # 实例化男子仰卧起坐成绩计算
situp_female = Situp_female_standard_data()    # 实例化男子仰卧起坐成绩计算
serpentine_run_male = Serpentine_run_male_standard_data()      # 实例化男子蛇形跑成绩计算
serpentine_run_female = Serpentine_run_female_standard_data()  # 实例化女子蛇形跑成绩计算

rngs = ws_training_score.iter_rows(min_row=6)
for row in rngs:
gender = row[4].value          # 性别
pullup = row[11].value         # 单杠原始数量
situp =  row[7].value          # 仰卧起坐原始数量
serpentine_run = row[9].value  # 蛇形跑原始数量
if row[5].value:
#print(row[5].value)
age = age_computing(row[5].value)    #由出生日期计算年龄，精确到天
row[6].value = age

if gender == '男':
if row[6].value != None:  # 年龄不能为空
if pullup != None:    # 单杆原始数不能为空
row[12].value = pullup_sd.pullup_score_computing(age, pullup) # 计算男子单杠或俯卧撑成绩
#print(row[0].value,pullup,row[12].value) # 测试
if situp != None:     # 仰卧起坐原始数不能为空
row[8].value = situp_male.situp_male_score_computing(age, situp)  # 计算男子仰卧起坐成绩
#print(row[0].value, situp, row[8].value)
if serpentine_run != None:     # 蛇形跑原始数不能为空
row[10].value = serpentine_run_male.serpentine_run_male_score_computing(age, serpentine_run)  # 计算男子蛇形跑成绩
print(row[0].value, serpentine_run, row[10].value)

elif gender == '女':
if row[6].value != None:
if pullup != None:  # 单杆原始数不能为空
row[12].value = flexarmhang_sd.flex_arm_hang_score_computing(age, pullup) # 女子曲臂悬垂或俯卧撑成绩计算
#print(row[0].value,pullup, row[12].value)
if situp != None:     # 仰卧起坐原始数不能为空
row[8].value = situp_female.situp_female_score_computing(age, situp)  # 计算女子仰卧起坐成绩
#print(row[0].value, situp, row[8].value)
if serpentine_run != None:     # 蛇形跑原始数不能为空
row[10].value = serpentine_run_female.serpentine_run_female_score_computing(age, serpentine_run)  # 计算女子蛇形跑成绩
print(row[0].value, serpentine_run, row[10].value)
else:    # 性别输入有误
print('序号%d %s 的性别填写错误'%(row[0].value,row[1].value))

wb.save('计算结果.xlsx')```

0 条评论

• ### Python+Excel数据分析实战：军事体能考核成绩评定(四)女子曲臂悬垂计算

上一章Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算成功实现了男子引体向上计算的功能，这一章我们实现女子单杠考试成绩的计算，女...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(四)女子曲臂悬垂计算

上一章Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算成功实现了男子引体向上计算的功能，这一章我们实现女子单杠考试成绩的计算，女...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算

通过分析男子引体向上“成绩计算标准表”，发现标准表只是参照标准表，不是连续的全覆盖，比如标准中24岁以下，只规定了单杆30个100分，27个95分，那么28/2...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(四)女子曲臂悬垂计算

上一章Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算成功实现了男子引体向上计算的功能，这一章我们实现女子单杠考试成绩的计算，女...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(四)女子曲臂悬垂计算

上一章Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算成功实现了男子引体向上计算的功能，这一章我们实现女子单杠考试成绩的计算，女...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算

通过分析男子引体向上“成绩计算标准表”，发现标准表只是参照标准表，不是连续的全覆盖，比如标准中24岁以下，只规定了单杆30个100分，27个95分，那么28/2...

• ### Python+Excel数据分析实战：军事体能考核成绩评定(三)男子引体向上计算

通过分析男子引体向上“成绩计算标准表”，发现标准表只是参照标准表，不是连续的全覆盖，比如标准中24岁以下，只规定了单杆30个100分，27个95分，那么28/2...

• ### 协亨手机连锁

吴) 伴随着歌声和笑声，今天的晚会，就要和你说再见了，共同分享这美味醇香的精神大餐，我们心怀感激，

• ### 腾讯安全发布挖矿木马年度报告：供应链感染或成黑产流行手法

随着数字货币价值不断攀升，盗取用户计算机处理器的计算能力进行挖矿成为一门一本万利的暴利营生。自2017年爆发之后，近年来在挖矿木马全球范围持续活跃，每年都有大量...

• ### FCC：网络中立性法规将于6月11日正式废除

北京时间5月11日凌晨消息，本周四，美国联邦通信委员会在一份公告中表示，于2015年生效的开放互联网法规将于今年6月11日正式废除，而互联网服务提供商决定消费者...