宣芜高速公路取样点指标信息统计

目的：按方向、按路段、按指标统计宣芜高速公路取样点指标值中优、良、中、次、差所占的比例

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