目录
一、起因
事情的起因是这样，寒假的时候和我的初中老师一起吃了一顿饭，期间体育老师好奇我能不能写一个“中考评分系统”，因为中考体育评分分为很多小区间，手算或者心算起来都特别麻烦，如果能写个程序就特别方便，直接输入计算就行。我觉得应该可行，但我那时候还不知道用什么写，我就到处问了很多人了解到了主要有两种方法：1.用python直接对excel进行操作; 2.用vba宏编程。
第一种方法考虑到最终是要给老师用的，我想着可能还要教老师一些python操作，不论是从安装使用方面还是操作方面，对于不会python的老师可能不太方便。所以我就采用了第二种方法，但是我也并不知道vba如何去编写，所以就用了几天时间零零碎碎写了一个vba，最终的效果在我看来还不错。
二、什么是VBA
VBA（Visual Basic for Applications）是Visual Basic的一种宏语言，是在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展Windows的应用程序功能，特别是Microsoft Office软件。它也可说是一种应用程序视觉化的 Basic 脚本。
三、过程
经历了痛苦的考试周过后，我问我的体育老师要来了中考体育的评分标准，分为非常多的小区间
经过简单的学习后，我知道想要编写vba首先需要建立excel表格，接着在“开发工具”里找到“Visual Basic”这样就打开了vba的编程界面
然后在VBAProject中右键添加“模块”，这样在模块中就可以进行代码编程，为你的表格写一个插件，让表格中的数据按照你的方式来运行
接着在右边的代码编辑框里写入下面语句：
Sub test()
'在这里就可以编写你的主函数
End Sub这样在Sub 和 End Sub中间就可以编写你的代码，学习一个新的语言我的主要方式是直接上手，直接开写，不知道语法格式什么的就开查。
首先介绍vba中的定义变量语句：
Dim i As Integer 'i是你的变量，Integer是变量类型通过这一个语句相信大家就已经明白了vba变量的定义的基本格式
然后介绍for循环语句：
For i = 1 To 100
'循环内容
Next i 这个语句的作用是将i从1循环到100，看到这里我觉得vba的格式有点像html的编写格式，条件或者是循环语句在编写上都会有一个“上下界”，代码内容都在“上下界”里面编写
if else语句：
If xxx then
'内容
End If
If xxx then
'内容
Else
'内容
End If
If xxx then
'内容
Elseif xxx then
'内容
Elseif xxx then
'内容
Else
'内容
End If从上往下分别介绍了if语句，if else语句，和else if语句的编写方法
vba中函数的编写方法：
Function getnum(str As String) As Integer ’As Integer表示函数返回值的类型
'可以通过getnum = x 这种语句来设置函数的返回值是什么
'其中函数的传参语句可以通过 str As xxx来定义参数类型，或者直接str不用添加任何说明
End Function事实上我编写的vba宏就包含了这些基本语句，接下来通过一些自定义函数或者是系统自带函数，简单的ifelse语句，for循环语句就可以实现
四、数据处理
首先定义表头，我的表头定义如下：
如何获取表格中有多少行数据呢，我想的是既然是评分系统，那么一个人肯定有姓名，我的方法就是先遍历“姓名”这一列，直到“姓名”没有数据就退出循环，这样就获取到了有多少行数据：
Dim isblank As Boolean
Dim i As Integer
Dim tot As Integer
tot = 0
For i = 5 To 10000
isblank = Cells(i, 1).Value = ""
If isblank Then
Exit For
Else
tot = tot + 1
End If
Next i
'MsgBox "共有" & tot & "组数据"这段代码里面的tot就是表格中总共有几行数据
紧接着第二个难题我认识是如何处理“跑步成绩”的问题，跑步成绩通常情况下我们都写成几分几秒，我的方法是在表格中填写字符串，格式为(x'xx)，然后通过字符串读取，再将字符串分为多少分和多少秒，将跑步成绩最终都化为多少秒来进行比较。那么如何进行字符串的处理呢，我的方法比较简单粗暴，考虑到数据的通常一般性，很少有人1000m能跑十分钟以上，所以我就用了left函数来获取“分”，然后用mid函数获取到字符串后两位来获取“秒”：
Function opchangpao(str As String) As Integer
Dim tmp1 As String
Dim tmp2 As String
tmp1 = Left(str, 1) 'left(字符串，长度)
tmp2 = Mid(str, 3, 2) 'mid(字符串，起始位置，长度)
Dim t1
Dim t2
t1 = CDbl(tmp1)
t2 = CDbl(tmp2)
opchangpao = t1 * 60 + t2
End Function然后通过系统自带的函数CDbl或者CInt就可以将字符串转为数字，这样就完成了跑步成绩数据的处理。至此，对于其他数据的处理都大同小异，不多赘述。
因为评分分为很多小区间，所以可能还得对评分区间进行很多if else 的判断，这是编写这个宏最麻烦的地方，这里就给一个跑步判断让大家感受一下：
Function boy_run_solve(tmp) As Double
If tmp <= 220 Then
boy_run_solve = 20
ElseIf tmp <= 221 Then
boy_run_solve = 19.8
ElseIf tmp <= 222 Then
boy_run_solve = 19.6
ElseIf tmp <= 223 Then
boy_run_solve = 19.4
ElseIf tmp <= 224 Then
boy_run_solve = 19.2
ElseIf tmp <= 225 Then
boy_run_solve = 19#
ElseIf tmp <= 226 Then
boy_run_solve = 18.8
ElseIf tmp <= 227 Then
boy_run_solve = 18.6
ElseIf tmp <= 228 Then
boy_run_solve = 18.4
ElseIf tmp <= 229 Then
boy_run_solve = 18.2
ElseIf tmp <= 230 Then
boy_run_solve = 18#
ElseIf tmp <= 232 Then
boy_run_solve = 17.7
ElseIf tmp <= 234 Then
boy_run_solve = 17.4
ElseIf tmp <= 237 Then
boy_run_solve = 17#
ElseIf tmp <= 239 Then
boy_run_solve = 16.7
ElseIf tmp <= 242 Then
boy_run_solve = 16.4
ElseIf tmp <= 245 Then
boy_run_solve = 16#
ElseIf tmp <= 250 Then
boy_run_solve = 15.6
ElseIf tmp <= 255 Then
boy_run_solve = 15.2
ElseIf tmp <= 260 Then
boy_run_solve = 14.8
ElseIf tmp <= 265 Then
boy_run_solve = 14.4
ElseIf tmp <= 270 Then
boy_run_solve = 14#
ElseIf tmp <= 275 Then
boy_run_solve = 13.6
ElseIf tmp <= 280 Then
boy_run_solve = 13.2
ElseIf tmp <= 285 Then
boy_run_solve = 12.8
ElseIf tmp <= 290 Then
boy_run_solve = 12.4
ElseIf tmp <= 295 Then
boy_run_solve = 12#
ElseIf tmp <= 305 Then
boy_run_solve = 11.5
ElseIf tmp <= 315 Then
boy_run_solve = 10#
ElseIf tmp <= 325 Then
boy_run_solve = 9#
ElseIf tmp <= 335 Then
boy_run_solve = 8#
ElseIf tmp <= 345 Then
boy_run_solve = 7#
ElseIf tmp <= 355 Then
boy_run_solve = 6#
ElseIf tmp <= 365 Then
boy_run_solve = 5#
ElseIf tmp <= 375 Then
boy_run_solve = 4#
ElseIf tmp <= 385 Then
boy_run_solve = 3#
Else
boy_run_solve = 0#
End If
End Function五、内容展示
接着对表格和数据进行一些处理美化，就完成了这次宏编程：
右上角是一个宏按钮，点击就可以运行宏
Function getnum(str As String) As Integer
If Len(str) = 1 Then
If str = "0" Then
getnum = 0
End If
If str = "1" Then
getnum = 1
End If
If str = "2" Then
getnum = 2
End If
If str = "3" Then
getnum = 3
End If
If str = "4" Then
getnum = 4
End If
If str = "5" Then
getnum = 5
End If
If str = "6" Then
getnum = 6
End If
If str = "7" Then
getnum = 7
End If
If str = "8" Then
getnum = 8
End If
If str = "9" Then
getnum = 9
End If
End If
End Function
Function opchangpao(str As String) As Integer
Dim tmp1 As String
Dim tmp2 As String
tmp1 = Left(str, 1)
tmp2 = Mid(str, 3, 2)
Dim t1
Dim t2
t1 = CDbl(tmp1)
t2 = CDbl(tmp2)
opchangpao = t1 * 60 + t2
End Function
Function boy_run_solve(tmp) As Double
If tmp <= 220 Then
boy_run_solve = 20
ElseIf tmp <= 221 Then
boy_run_solve = 19.8
ElseIf tmp <= 222 Then
boy_run_solve = 19.6
ElseIf tmp <= 223 Then
boy_run_solve = 19.4
ElseIf tmp <= 224 Then
boy_run_solve = 19.2
ElseIf tmp <= 225 Then
boy_run_solve = 19#
ElseIf tmp <= 226 Then
boy_run_solve = 18.8
ElseIf tmp <= 227 Then
boy_run_solve = 18.6
ElseIf tmp <= 228 Then
boy_run_solve = 18.4
ElseIf tmp <= 229 Then
boy_run_solve = 18.2
ElseIf tmp <= 230 Then
boy_run_solve = 18#
ElseIf tmp <= 232 Then
boy_run_solve = 17.7
ElseIf tmp <= 234 Then
boy_run_solve = 17.4
ElseIf tmp <= 237 Then
boy_run_solve = 17#
ElseIf tmp <= 239 Then
boy_run_solve = 16.7
ElseIf tmp <= 242 Then
boy_run_solve = 16.4
ElseIf tmp <= 245 Then
boy_run_solve = 16#
ElseIf tmp <= 250 Then
boy_run_solve = 15.6
ElseIf tmp <= 255 Then
boy_run_solve = 15.2
ElseIf tmp <= 260 Then
boy_run_solve = 14.8
ElseIf tmp <= 265 Then
boy_run_solve = 14.4
ElseIf tmp <= 270 Then
boy_run_solve = 14#
ElseIf tmp <= 275 Then
boy_run_solve = 13.6
ElseIf tmp <= 280 Then
boy_run_solve = 13.2
ElseIf tmp <= 285 Then
boy_run_solve = 12.8
ElseIf tmp <= 290 Then
boy_run_solve = 12.4
ElseIf tmp <= 295 Then
boy_run_solve = 12#
ElseIf tmp <= 305 Then
boy_run_solve = 11.5
ElseIf tmp <= 315 Then
boy_run_solve = 10#
ElseIf tmp <= 325 Then
boy_run_solve = 9#
ElseIf tmp <= 335 Then
boy_run_solve = 8#
ElseIf tmp <= 345 Then
boy_run_solve = 7#
ElseIf tmp <= 355 Then
boy_run_solve = 6#
ElseIf tmp <= 365 Then
boy_run_solve = 5#
ElseIf tmp <= 375 Then
boy_run_solve = 4#
ElseIf tmp <= 385 Then
boy_run_solve = 3#
Else
boy_run_solve = 0#
End If
End Function
Function girl_run_solve(tmp) As Double
If tmp <= 205 Then
girl_run_solve = 20
ElseIf tmp <= 206 Then
girl_run_solve = 19.8
ElseIf tmp <= 207 Then
girl_run_solve = 19.6
ElseIf tmp <= 208 Then
girl_run_solve = 19.4
ElseIf tmp <= 210 Then
girl_run_solve = 19.2
ElseIf tmp <= 212 Then
girl_run_solve = 19#
ElseIf tmp <= 213 Then
girl_run_solve = 18.8
ElseIf tmp <= 214 Then
girl_run_solve = 18.6
ElseIf tmp <= 215 Then
girl_run_solve = 18.4
ElseIf tmp <= 217 Then
girl_run_solve = 18.2
ElseIf tmp <= 219 Then
girl_run_solve = 18#
ElseIf tmp <= 221 Then
girl_run_solve = 17.7
ElseIf tmp <= 224 Then
girl_run_solve = 17.4
ElseIf tmp <= 227 Then
girl_run_solve = 17#
ElseIf tmp <= 229 Then
girl_run_solve = 16.7
ElseIf tmp <= 232 Then
girl_run_solve = 16.4
ElseIf tmp <= 235 Then
girl_run_solve = 16#
ElseIf tmp <= 240 Then
girl_run_solve = 15.6
ElseIf tmp <= 245 Then
girl_run_solve = 15.2
ElseIf tmp <= 250 Then
girl_run_solve = 14.8
ElseIf tmp <= 255 Then
girl_run_solve = 14.4
ElseIf tmp <= 260 Then
girl_run_solve = 14#
ElseIf tmp <= 265 Then
girl_run_solve = 13.6
ElseIf tmp <= 270 Then
girl_run_solve = 13.2
ElseIf tmp <= 275 Then
girl_run_solve = 12.8
ElseIf tmp <= 280 Then
girl_run_solve = 12.4
ElseIf tmp <= 285 Then
girl_run_solve = 12#
ElseIf tmp <= 290 Then
girl_run_solve = 11.5
ElseIf tmp <= 295 Then
girl_run_solve = 10#
ElseIf tmp <= 300 Then
girl_run_solve = 9#
ElseIf tmp <= 305 Then
girl_run_solve = 8#
ElseIf tmp <= 310 Then
girl_run_solve = 7#
ElseIf tmp <= 315 Then
girl_run_solve = 6#
ElseIf tmp <= 320 Then
girl_run_solve = 5#
ElseIf tmp <= 325 Then
girl_run_solve = 4#
ElseIf tmp <= 330 Then
girl_run_solve = 3#
Else
girl_run_solve = 0#
End If
End Function
Function boy_jump_solve(tmp) As Double
If tmp >= 2.5 Then
boy_jump_solve = 20#
ElseIf tmp >= 2.49 Then
boy_jump_solve = 19.8
ElseIf tmp >= 2.48 Then
boy_jump_solve = 19.6
ElseIf tmp >= 2.47 Then
boy_jump_solve = 19.4
ElseIf tmp >= 2.46 Then
boy_jump_solve = 19.2
ElseIf tmp >= 2.45 Then
boy_jump_solve = 19#
ElseIf tmp >= 2.44 Then
boy_jump_solve = 18.8
ElseIf tmp >= 2.43 Then
boy_jump_solve = 18.6
ElseIf tmp >= 2.42 Then
boy_jump_solve = 18.4
ElseIf tmp >= 2.41 Then
boy_jump_solve = 18.2
ElseIf tmp >= 2.4 Then
boy_jump_solve = 18#
ElseIf tmp >= 2.38 Then
boy_jump_solve = 17.7
ElseIf tmp >= 2.36 Then
boy_jump_solve = 17.4
ElseIf tmp >= 2.33 Then
boy_jump_solve = 17#
ElseIf tmp >= 2.31 Then
boy_jump_solve = 16.7
ElseIf tmp >= 2.28 Then
boy_jump_solve = 16.4
ElseIf tmp >= 2.25 Then
boy_jump_solve = 16#
ElseIf tmp >= 2.21 Then
boy_jump_solve = 15.6
ElseIf tmp >= 2.17 Then
boy_jump_solve = 15.2
ElseIf tmp >= 2.13 Then
boy_jump_solve = 14.8
ElseIf tmp >= 2.09 Then
boy_jump_solve = 14.4
ElseIf tmp >= 2.05 Then
boy_jump_solve = 14#
ElseIf tmp >= 2.01 Then
boy_jump_solve = 13.6
ElseIf tmp >= 1.97 Then
boy_jump_solve = 13.2
ElseIf tmp >= 1.93 Then
boy_jump_solve = 12.8
ElseIf tmp >= 1.89 Then
boy_jump_solve = 12.4
ElseIf tmp >= 1.85 Then
boy_jump_solve = 12#
ElseIf tmp >= 1.83 Then
boy_jump_solve = 11.5
ElseIf tmp >= 1.8 Then
boy_jump_solve = 10#
ElseIf tmp >= 1.78 Then
boy_jump_solve = 9#
ElseIf tmp >= 1.75 Then
boy_jump_solve = 8#
ElseIf tmp >= 1.73 Then
boy_jump_solve = 7#
ElseIf tmp >= 1.7 Then
boy_jump_solve = 6#
ElseIf tmp >= 1.68 Then
boy_jump_solve = 5#
ElseIf tmp >= 1.65 Then
boy_jump_solve = 4#
ElseIf tmp >= 1.63 Then
boy_jump_solve = 3#
Else
boy_jump_solve = 0#
End If
End Function
Function girl_jump_solve(tmp) As Double
If tmp >= 2.02 Then
girl_jump_solve = 20#
ElseIf tmp >= 2.01 Then
girl_jump_solve = 19.8
ElseIf tmp >= 2# Then
girl_jump_solve = 19.6
ElseIf tmp >= 1.99 Then
girl_jump_solve = 19.4
ElseIf tmp >= 1.98 Then
girl_jump_solve = 19.2
ElseIf tmp >= 1.96 Then
girl_jump_solve = 19#
ElseIf tmp >= 1.95 Then
girl_jump_solve = 18.8
ElseIf tmp >= 1.94 Then
girl_jump_solve = 18.6
ElseIf tmp >= 1.93 Then
girl_jump_solve = 18.4
ElseIf tmp >= 1.92 Then
girl_jump_solve = 18.2
ElseIf tmp >= 1.9 Then
girl_jump_solve = 18#
ElseIf tmp >= 1.88 Then
girl_jump_solve = 17.7
ElseIf tmp >= 1.86 Then
girl_jump_solve = 17.4
ElseIf tmp >= 1.83 Then
girl_jump_solve = 17#
ElseIf tmp >= 1.81 Then
girl_jump_solve = 16.7
ElseIf tmp >= 1.79 Then
girl_jump_solve = 16.4
ElseIf tmp >= 1.76 Then
girl_jump_solve = 16#
ElseIf tmp >= 1.73 Then
girl_jump_solve = 15.6
ElseIf tmp >= 1.7 Then
girl_jump_solve = 15.2
ElseIf tmp >= 1.67 Then
girl_jump_solve = 14.8
ElseIf tmp >= 1.64 Then
girl_jump_solve = 14.4
ElseIf tmp >= 1.61 Then
girl_jump_solve = 14#
ElseIf tmp >= 1.58 Then
girl_jump_solve = 13.6
ElseIf tmp >= 1.55 Then
girl_jump_solve = 13.2
ElseIf tmp >= 1.52 Then
girl_jump_solve = 12.8
ElseIf tmp >= 1.49 Then
girl_jump_solve = 12.4
ElseIf tmp >= 1.46 Then
girl_jump_solve = 12#
ElseIf tmp >= 1.44 Then
girl_jump_solve = 11.5
ElseIf tmp >= 1.41 Then
girl_jump_solve = 10#
ElseIf tmp >= 1.39 Then
girl_jump_solve = 9#
ElseIf tmp >= 1.36 Then
girl_jump_solve = 8#
ElseIf tmp >= 1.34 Then
girl_jump_solve = 7#
ElseIf tmp >= 1.31 Then
girl_jump_solve = 6#
ElseIf tmp >= 1.29 Then
girl_jump_solve = 5#
ElseIf tmp >= 1.26 Then
girl_jump_solve = 4#
ElseIf tmp >= 1.24 Then
girl_jump_solve = 3#
Else
girl_jump_solve = 0#
End If
End Function
Function boy_skip_solve(tmp) As Double
If tmp >= 180 Then
boy_skip_solve = 20#
ElseIf tmp >= 178 Then
boy_skip_solve = 19.8
ElseIf tmp >= 175 Then
boy_skip_solve = 19.6
ElseIf tmp >= 172 Then
boy_skip_solve = 19.4
ElseIf tmp >= 168 Then
boy_skip_solve = 19.2
ElseIf tmp >= 164 Then
boy_skip_solve = 19#
ElseIf tmp >= 160 Then
boy_skip_solve = 18.8
ElseIf tmp >= 155 Then
boy_skip_solve = 18.6
ElseIf tmp >= 150 Then
boy_skip_solve = 18.4
ElseIf tmp >= 145 Then
boy_skip_solve = 18.2
ElseIf tmp >= 140 Then
boy_skip_solve = 18#
ElseIf tmp >= 138 Then
boy_skip_solve = 17.7
ElseIf tmp >= 136 Then
boy_skip_solve = 17.4
ElseIf tmp >= 133 Then
boy_skip_solve = 17#
ElseIf tmp >= 129 Then
boy_skip_solve = 16.7
ElseIf tmp >= 125 Then
boy_skip_solve = 16.4
ElseIf tmp >= 120 Then
boy_skip_solve = 16#
ElseIf tmp >= 116 Then
boy_skip_solve = 15.6
ElseIf tmp >= 112 Then
boy_skip_solve = 15.2
ElseIf tmp >= 107 Then
boy_skip_solve = 14.8
ElseIf tmp >= 103 Then
boy_skip_solve = 14.4
ElseIf tmp >= 98 Then
boy_skip_solve = 14#
ElseIf tmp >= 93 Then
boy_skip_solve = 13.6
ElseIf tmp >= 86 Then
boy_skip_solve = 13.2
ElseIf tmp >= 80 Then
boy_skip_solve = 12.8
ElseIf tmp >= 73 Then
boy_skip_solve = 12.4
ElseIf tmp >= 64 Then
boy_skip_solve = 12#
ElseIf tmp >= 62 Then
boy_skip_solve = 11.5
ElseIf tmp >= 60 Then
boy_skip_solve = 10#
ElseIf tmp >= 57 Then
boy_skip_solve = 9#
ElseIf tmp >= 53 Then
boy_skip_solve = 8#
ElseIf tmp >= 50 Then
boy_skip_solve = 7#
ElseIf tmp >= 46 Then
boy_skip_solve = 6#
ElseIf tmp >= 42 Then
boy_skip_solve = 5#
ElseIf tmp >= 37 Then
boy_skip_solve = 4#
ElseIf tmp >= 33 Then
boy_skip_solve = 3#
Else
boy_skip_solve = 0#
End If
End Function
Function girl_skip_solve(tmp) As Double
If tmp >= 172 Then
girl_skip_solve = 20#
ElseIf tmp >= 170 Then
girl_skip_solve = 19.8
ElseIf tmp >= 167 Then
girl_skip_solve = 19.6
ElseIf tmp >= 164 Then
girl_skip_solve = 19.4
ElseIf tmp >= 160 Then
girl_skip_solve = 19.2
ElseIf tmp >= 157 Then
girl_skip_solve = 19#
ElseIf tmp >= 153 Then
girl_skip_solve = 18.8
ElseIf tmp >= 148 Then
girl_skip_solve = 18.6
ElseIf tmp >= 143 Then
girl_skip_solve = 18.4
ElseIf tmp >= 138 Then
girl_skip_solve = 18.2
ElseIf tmp >= 133 Then
girl_skip_solve = 18#
ElseIf tmp >= 131 Then
girl_skip_solve = 17.7
ElseIf tmp >= 129 Then
girl_skip_solve = 17.4
ElseIf tmp >= 126 Then
girl_skip_solve = 17#
ElseIf tmp >= 122 Then
girl_skip_solve = 16.7
ElseIf tmp >= 118 Then
girl_skip_solve = 16.4
ElseIf tmp >= 113 Then
girl_skip_solve = 16#
ElseIf tmp >= 109 Then
girl_skip_solve = 15.6
ElseIf tmp >= 105 Then
girl_skip_solve = 15.2
ElseIf tmp >= 100 Then
girl_skip_solve = 14.8
ElseIf tmp >= 96 Then
girl_skip_solve = 14.4
ElseIf tmp >= 91 Then
girl_skip_solve = 14#
ElseIf tmp >= 86 Then
girl_skip_solve = 13.6
ElseIf tmp >= 80 Then
girl_skip_solve = 13.2
ElseIf tmp >= 74 Then
girl_skip_solve = 12.8
ElseIf tmp >= 67 Then
girl_skip_solve = 12.4
ElseIf tmp >= 58 Then
girl_skip_solve = 12#
ElseIf tmp >= 56 Then
girl_skip_solve = 11.5
ElseIf tmp >= 54 Then
girl_skip_solve = 10#
ElseIf tmp >= 51 Then
girl_skip_solve = 9#
ElseIf tmp >= 48 Then
girl_skip_solve = 8#
ElseIf tmp >= 45 Then
girl_skip_solve = 7#
ElseIf tmp >= 42 Then
girl_skip_solve = 6#
ElseIf tmp >= 38 Then
girl_skip_solve = 5#
ElseIf tmp >= 33 Then
girl_skip_solve = 4#
ElseIf tmp >= 29 Then
girl_skip_solve = 3#
Else
girl_skip_solve = 0#
End If
End Function
Function boy_gsit_solve(tmp) As Double
If tmp >= 15 Then
boy_gsit_solve = 20#
ElseIf tmp >= 14 Then
boy_gsit_solve = 19#
ElseIf tmp >= 13 Then
boy_gsit_solve = 18#
ElseIf tmp >= 12 Then
boy_gsit_solve = 17#
ElseIf tmp >= 11 Then
boy_gsit_solve = 16#
ElseIf tmp >= 10 Then
boy_gsit_solve = 15.2
ElseIf tmp >= 9 Then
boy_gsit_solve = 14.4
ElseIf tmp >= 8 Then
boy_gsit_solve = 13.6
ElseIf tmp >= 7 Then
boy_gsit_solve = 12.8
ElseIf tmp >= 6 Then
boy_gsit_solve = 12#
ElseIf tmp >= 5 Then
boy_gsit_solve = 10#
ElseIf tmp >= 4 Then
boy_gsit_solve = 8#
ElseIf tmp >= 3 Then
boy_gsit_solve = 6#
ElseIf tmp >= 2 Then
boy_gsit_solve = 4#
Else
boy_gsit_solve = 0#
End If
End Function
Function girl_gsit_solve(tmp) As Double
If tmp >= 52 Then
girl_gsit_solve = 20#
ElseIf tmp >= 51 Then
girl_gsit_solve = 19.5
ElseIf tmp >= 50 Then
girl_gsit_solve = 19#
ElseIf tmp >= 49 Then
girl_gsit_solve = 18.5
ElseIf tmp >= 48 Then
girl_gsit_solve = 18#
ElseIf tmp >= 47 Then
girl_gsit_solve = 17.7
ElseIf tmp >= 46 Then
girl_gsit_solve = 17.4
ElseIf tmp >= 45 Then
girl_gsit_solve = 17#
ElseIf tmp >= 44 Then
girl_gsit_solve = 16.7
ElseIf tmp >= 43 Then
girl_gsit_solve = 16.4
ElseIf tmp >= 42 Then
girl_gsit_solve = 16#
ElseIf tmp >= 40 Then
girl_gsit_solve = 15.6
ElseIf tmp >= 38 Then
girl_gsit_solve = 15.2
ElseIf tmp >= 36 Then
girl_gsit_solve = 14.8
ElseIf tmp >= 34 Then
girl_gsit_solve = 14.4
ElseIf tmp >= 32 Then
girl_gsit_solve = 14#
ElseIf tmp >= 30 Then
girl_gsit_solve = 13.6
ElseIf tmp >= 28 Then
girl_gsit_solve = 13.2
ElseIf tmp >= 26 Then
girl_gsit_solve = 12.8
ElseIf tmp >= 24 Then
girl_gsit_solve = 12.4
ElseIf tmp >= 22 Then
girl_gsit_solve = 12#
ElseIf tmp >= 20 Then
girl_gsit_solve = 10#
ElseIf tmp >= 18 Then
girl_gsit_solve = 8#
ElseIf tmp >= 16 Then
girl_gsit_solve = 6#
ElseIf tmp >= 14 Then
girl_gsit_solve = 4#
Else
girl_gsit_solve = 0#
End If
End Function
Function boy_gbasketball_solve(tmp) As Double
If tmp <= 9.4 Then
boy_gbasketball_solve = 20
ElseIf tmp <= 9.6 Then
boy_gbasketball_solve = 19.8
ElseIf tmp <= 9.8 Then
boy_gbasketball_solve = 19.6
ElseIf tmp <= 10.1 Then
boy_gbasketball_solve = 19.4
ElseIf tmp <= 10.4 Then
boy_gbasketball_solve = 19.2
ElseIf tmp <= 10.7 Then
boy_gbasketball_solve = 19#
ElseIf tmp <= 11.1 Then
boy_gbasketball_solve = 18.8
ElseIf tmp <= 11.5 Then
boy_gbasketball_solve = 18.6
ElseIf tmp <= 11.9 Then
boy_gbasketball_solve = 18.4
ElseIf tmp <= 12.3 Then
boy_gbasketball_solve = 18.2
ElseIf tmp <= 12.8 Then
boy_gbasketball_solve = 18#
ElseIf tmp <= 13# Then
boy_gbasketball_solve = 17.7
ElseIf tmp <= 13.2 Then
boy_gbasketball_solve = 17.4
ElseIf tmp <= 13.4 Then
boy_gbasketball_solve = 17#
ElseIf tmp <= 13.8 Then
boy_gbasketball_solve = 16.7
ElseIf tmp <= 14.2 Then
boy_gbasketball_solve = 16.4
ElseIf tmp <= 14.7 Then
boy_gbasketball_solve = 16#
ElseIf tmp <= 15.1 Then
boy_gbasketball_solve = 15.6
ElseIf tmp <= 15.4 Then
boy_gbasketball_solve = 15.2
ElseIf tmp <= 16.1 Then
boy_gbasketball_solve = 14.8
ElseIf tmp <= 16.4 Then
boy_gbasketball_solve = 14.4
ElseIf tmp <= 16.9 Then
boy_gbasketball_solve = 14#
ElseIf tmp <= 17.8 Then
boy_gbasketball_solve = 13.6
ElseIf tmp <= 18.3 Then
boy_gbasketball_solve = 13.2
ElseIf tmp <= 18.9 Then
boy_gbasketball_solve = 12.8
ElseIf tmp <= 19.8 Then
boy_gbasketball_solve = 12.4
ElseIf tmp <= 20.8 Then
boy_gbasketball_solve = 12#
ElseIf tmp <= 21.2 Then
boy_gbasketball_solve = 11.5
ElseIf tmp <= 21.6 Then
boy_gbasketball_solve = 10#
ElseIf tmp <= 22.2 Then
boy_gbasketball_solve = 9#
ElseIf tmp <= 22.9 Then
boy_gbasketball_solve = 8#
ElseIf tmp <= 23.5 Then
boy_gbasketball_solve = 7#
ElseIf tmp <= 24.1 Then
boy_gbasketball_solve = 6#
ElseIf tmp <= 24.9 Then
boy_gbasketball_solve = 5#
ElseIf tmp <= 25.8 Then
boy_gbasketball_solve = 4#
ElseIf tmp <= 26.6 Then
boy_gbasketball_solve = 3#
Else
boy_gbasketball_solve = 0#
End If
End Function
Function girl_gbasketball_solve(tmp) As Double
If tmp <= 12# Then
girl_gbasketball_solve = 20
ElseIf tmp <= 12.1 Then
girl_gbasketball_solve = 19.8
ElseIf tmp <= 12.3 Then
girl_gbasketball_solve = 19.6
ElseIf tmp <= 12.5 Then
girl_gbasketball_solve = 19.4
ElseIf tmp <= 12.8 Then
girl_gbasketball_solve = 19.2
ElseIf tmp <= 13.1 Then
girl_gbasketball_solve = 19#
ElseIf tmp <= 13.4 Then
girl_gbasketball_solve = 18.8
ElseIf tmp <= 13.7 Then
girl_gbasketball_solve = 18.6
ElseIf tmp <= 14.1 Then
girl_gbasketball_solve = 18.4
ElseIf tmp <= 14.4 Then
girl_gbasketball_solve = 18.2
ElseIf tmp <= 14.8 Then
girl_gbasketball_solve = 18#
ElseIf tmp <= 15.1 Then
girl_gbasketball_solve = 17.7
ElseIf tmp <= 15.5 Then
girl_gbasketball_solve = 17.4
ElseIf tmp <= 16# Then
girl_gbasketball_solve = 17#
ElseIf tmp <= 16.7 Then
girl_gbasketball_solve = 16.7
ElseIf tmp <= 17.6 Then
girl_gbasketball_solve = 16.4
ElseIf tmp <= 18.4 Then
girl_gbasketball_solve = 16#
ElseIf tmp <= 19.2 Then
girl_gbasketball_solve = 15.6
ElseIf tmp <= 19.9 Then
girl_gbasketball_solve = 15.2
ElseIf tmp <= 20.6 Then
girl_gbasketball_solve = 14.8
ElseIf tmp <= 21.4 Then
girl_gbasketball_solve = 14.4
ElseIf tmp <= 22.1 Then
girl_gbasketball_solve = 14#
ElseIf tmp <= 22.9 Then
girl_gbasketball_solve = 13.6
ElseIf tmp <= 23.9 Then
girl_gbasketball_solve = 13.2
ElseIf tmp <= 24.7 Then
girl_gbasketball_solve = 12.8
ElseIf tmp <= 25.8 Then
girl_gbasketball_solve = 12.4
ElseIf tmp <= 27.1 Then
girl_gbasketball_solve = 12#
ElseIf tmp <= 27.4 Then
girl_gbasketball_solve = 11.5
ElseIf tmp <= 27.8 Then
girl_gbasketball_solve = 10#
ElseIf tmp <= 28.3 Then
girl_gbasketball_solve = 9#
ElseIf tmp <= 28.8 Then
girl_gbasketball_solve = 8#
ElseIf tmp <= 29.3 Then
girl_gbasketball_solve = 7#
ElseIf tmp <= 29.9 Then
girl_gbasketball_solve = 6#
ElseIf tmp <= 30.5 Then
girl_gbasketball_solve = 5#
ElseIf tmp <= 31.2 Then
girl_gbasketball_solve = 4#
ElseIf tmp <= 31.9 Then
girl_gbasketball_solve = 3#
Else
girl_gbasketball_solve = 0#
End If
End Function
Function boy_football_solve(tmp) As Double
If tmp >= 15 Then
boy_football_solve = 20#
ElseIf tmp >= 14 Then
boy_football_solve = 19.5
ElseIf tmp >= 13 Then
boy_football_solve = 19#
ElseIf tmp >= 12 Then
boy_football_solve = 18#
ElseIf tmp >= 11 Then
boy_football_solve = 16#
ElseIf tmp >= 10 Then
boy_football_solve = 14#
ElseIf tmp >= 9 Then
boy_football_solve = 12#
ElseIf tmp >= 8 Then
boy_football_solve = 10#
ElseIf tmp >= 7 Then
boy_football_solve = 8#
Else
boy_football_solve = 0#
End If
End Function
Sub test()
Dim isblank As Boolean
Dim i As Integer
Dim tot As Integer
tot = 0
For i = 5 To 10000
isblank = Cells(i, 1).Value = ""
If isblank Then
Exit For
Else
tot = tot + 1
End If
Next i
'MsgBox "共有" & tot & "组数据"
Dim t1 As String
Dim run, skip, sit_up, football As Integer
Dim jump, basketball As Double
Dim grun, gjump, gskip, gsit_up, gbasketball, gfootball As Double
For i = 5 To 5 + tot - 1
run = 10000
skip = 0
sit_up = 0
football = 0
jump = 0
basketball = 99
grun = 0
gjump = 0
gskip = 0
gsit_up = 0
gbasketball = 0
gfootball = 0
If Cells(i, 2) = "男" Then
isblank = Cells(i, 3).Value = ""
If isblank = False Then
t1 = Cells(i, 3)
run = opchangpao(t1)
'MsgBox (run)
End If
isblank = Cells(i, 4).Value = ""
If isblank = False Then
t1 = Cells(i, 4)
jump = CDbl(t1)
'MsgBox (jump)
End If
isblank = Cells(i, 5).Value = ""
If isblank = False Then
t1 = Cells(i, 5)
skip = CDbl(t1)
'MsgBox (skip)
End If
isblank = Cells(i, 6).Value = ""
If isblank = False Then
t1 = Cells(i, 6)
sit_up = 0
sit_up = CInt(t1)
'MsgBox (sit_up)
End If
'MsgBox (sit_up)
isblank = Cells(i, 7).Value = ""
If isblank = False Then
t1 = Cells(i, 7)
basketball = CDbl(t1)
'MsgBox (basketball)
End If
isblank = Cells(i, 8).Value = ""
If isblank = False Then
t1 = Cells(i, 8)
football = CInt(t1)
'MsgBox (football)
End If
grun = boy_run_solve(run)
gjump = boy_jump_solve(jump)
gskip = boy_skip_solve(skip)
gsit_up = boy_gsit_solve(sit_up)
gbasketball = boy_gbasketball_solve(basketball)
'MsgBox "长跑成绩为" & grun
'MsgBox "跳远的成绩为" & gjump
'MsgBox "跳绳的成绩为" & gskip
'MsgBox "男生引体向上的成绩为" & gsit_up
'MsgBox "篮球的成绩为" & gbasketball
gfootball = boy_football_solve(football)
Cells(i, 9) = (grun + gjump + gskip + gsit_up + gbasketball + gfootball) * 1.5
Else
isblank = Cells(i, 3).Value = ""
If isblank = False Then
t1 = Cells(i, 3)
run = opchangpao(t1)
'MsgBox (run)
End If
isblank = Cells(i, 4).Value = ""
If isblank = False Then
t1 = Cells(i, 4)
jump = CDbl(t1)
'MsgBox (jump)
End If
isblank = Cells(i, 5).Value = ""
If isblank = False Then
t1 = Cells(i, 5)
skip = CDbl(t1)
'MsgBox (skip)
End If
isblank = Cells(i, 6).Value = ""
If isblank = False Then
t1 = Cells(i, 6)
sit_up = CInt(t1)
'MsgBox (sit_up)
End If
isblank = Cells(i, 7).Value = ""
If isblank = False Then
t1 = Cells(i, 7)
basketball = CDbl(t1)
'MsgBox (basketball)
End If
isblank = Cells(i, 8).Value = ""
If isblank = False Then
t1 = Cells(i, 8)
football = CInt(t1)
'MsgBox (football)
End If
grun = girl_run_solve(run)
gjump = girl_jump_solve(jump)
gskip = girl_skip_solve(skip)
gsit_up = girl_gsit_solve(sit_up)
gbasketball = girl_gbasketball_solve(basketball)
gfootball = boy_football_solve(football)
'MsgBox "长跑成绩为" & grun
'MsgBox "跳远的成绩为" & gjump
'MsgBox "跳绳的成绩为" & gskip
'MsgBox "仰卧起坐的成绩为" & gsit_up
'MsgBox "篮球的成绩为" & gbasketball
'MsgBox "足球的成绩为" & gfootball
Cells(i, 9) = (grun + gjump + gskip + gsit_up + gbasketball + gfootball) * 1.5
End If
Next i
MsgBox "完成！"
End Sub
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
