目的:按方向、按路段、按指标统计宣芜高速公路取样点指标值中优、良、中、次、差所占的比例
Sub Rank()
'按方向、路段统计行车道各路段在优、良、中、次、差中的比例
Dim SheetName(14) '存储所有工作表的名称
Dim TotalRows As Integer '每个工作表的总行数
Dim ColNum As Integer
'各个工作表的名称装入数组
For i = 1 To 7
For j = 1 To 2
SheetName(2 * (i - 1) + j - 1) = Sheets("Result").Cells(8 * (i - 1) + 1, 6 * (j - 1) + 2).Value
Next j
Next i
For i = 1 To 14
TotalRows = Sheets(SheetName(i - 1)).UsedRange.Rows.Count
For j = 2 To TotalRows
If (i Mod 2) = 1 Then '判断哪一条路
If Sheets(SheetName(i - 1)).Cells(j, 1) = "芜宣高速公路G50沪渝段" Then '判断是哪一段路
ColNum = 2
Else
ColNum = 5
End If
ElseIf Sheets(SheetName(i - 1)).Cells(j, 1) = "芜宣高速公路G50沪渝段" Then '判断是哪一段路
ColNum = 8
Else
ColNum = 11
End If
Select Case Sheets(SheetName(i - 1)).Cells(j, 8) 'TCEI
Case Is >= 90
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum).Value + 1
Case Is >= 80
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum).Value + 1
Case Is >= 70
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum).Value + 1
Case Is >= 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum).Value + 1
Case Is < 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum).Value + 1
End Select
Select Case Sheets(SheetName(i - 1)).Cells(j, 10) 'PPCI
Case Is >= 90
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum + 1).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum + 1).Value + 1
Case Is >= 80
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum + 1).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum + 1).Value + 1
Case Is >= 70
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum + 1).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum + 1).Value + 1
Case Is >= 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum + 1).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum + 1).Value + 1
Case Is < 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum + 1).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum + 1).Value + 1
End Select
Select Case Sheets(SheetName(i - 1)).Cells(j, 12) 'PSCI
Case Is >= 90
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum + 2).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)), ColNum + 2).Value + 1
Case Is >= 80
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum + 2).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 1, ColNum + 2).Value + 1
Case Is >= 70
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum + 2).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 2, ColNum + 2).Value + 1
Case Is >= 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum + 2).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 3, ColNum + 2).Value + 1
Case Is < 60
Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum + 2).Value = Sheets("Result").Cells(4 * (i - 1 + (i Mod 2)) + 4, ColNum + 2).Value + 1
End Select
Next j
Next i
End Sub