|
- Sub 作业一()
- Dim arr, brr
- Dim rg As Range
- Dim i As Long
- Dim r As Long
- r = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
- ReDim arr(1 To r, 1 To 3)
- For Each rg In Sheet1.Range("a1:a" & r)
- brr = Split(rg, "*")
- i = i + 1
- arr(i, 1) = brr(0)
- arr(i, 2) = brr(1)
- arr(i, 3) = brr(2)
- Next rg
- With Sheet1
- .Range("c1").Resize(UBound(arr), 3).Clear
- .Range("c1").Resize(UBound(arr), 3).NumberFormatLocal = "@"
- .Range("c1").Resize(UBound(arr), 3) = arr
- End With
- End Sub
- Sub 作业二()
- Dim arr, brr, ary
- Dim i As Long, j As Long
- arr = Sheet2.Range("b2:f12")
- ary = Array("条件", "个数", "条件", "个数")
- ReDim brr(1 To 6, 1 To 4)
- For i = 1 To UBound(brr, 2)
- brr(1, i) = ary(i - 1)
- Next i
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- Select Case arr(i, j)
- Case Is < 100
- brr(2, 1) = "<100"
- brr(2, 2) = brr(2, 2) + 1
- Case Is < 200
- brr(3, 1) = "<200"
- brr(3, 2) = brr(3, 2) + 1
- Case Is < 300
- brr(4, 1) = "<300"
- brr(4, 2) = brr(4, 2) + 1
- Case Is < 400
- brr(5, 1) = "<400"
- brr(5, 2) = brr(5, 2) + 1
- Case Is < 500
- brr(6, 1) = "<500"
- brr(6, 2) = brr(6, 2) + 1
- Case Is < 600
- brr(2, 3) = "<600"
- brr(2, 4) = brr(2, 4) + 1
- Case Is < 700
- brr(3, 3) = "<700"
- brr(3, 4) = brr(3, 4) + 1
- Case Is < 800
- brr(4, 3) = "<800"
- brr(4, 4) = brr(4, 4) + 1
- Case Is < 900
- brr(5, 3) = "<900"
- brr(5, 4) = brr(5, 4) + 1
- Case Is < 1000
- brr(6, 3) = "<1000"
- brr(6, 4) = brr(6, 4) + 1
- End Select
- Next j
- Next i
- With Sheet2
- .Range("h3:k8").ClearContents
- .Range("h3").Resize(UBound(brr), 4) = brr
- End With
- End Sub
- Sub 作业三()
- Dim arr, brr, bt
- Dim i As Integer, j As Integer
- bt = Array("序号", "最大数", "最小数", "平均值", "和")
- With Sheet3
- .Range("i2:m13").ClearContents
- arr = .Range("a2:f13")
- brr = .Range("i2:m13")
- For i = 1 To UBound(brr, 2)
- brr(1, i) = bt(i - 1)
- Next i
- For i = 2 To UBound(arr)
- brr(i, 3) = arr(i, 2)
- For j = 2 To UBound(arr, 2)
- If arr(i, j) > brr(i, 2) Then brr(i, 2) = arr(i, j)
- If arr(i, j) < brr(i, 3) Then brr(i, 3) = arr(i, j)
- brr(i, 5) = brr(i, 5) + arr(i, j)
- If j <= UBound(arr, 2) - 1 Then
- brr(i, 4) = brr(i, 4) + arr(i, j)
- Else
- brr(i, 4) = (brr(i, 4) + arr(i, j)) / (j - 1)
- End If
- Next j
- brr(i, 1) = i - 1
- Next i
- .Range("i2").Resize(UBound(brr), UBound(brr, 2)) = brr
- End With
- End Sub
- Sub 作业四()
- Dim i As Integer, j As Integer, m As Integer
- m = 12
- With Sheets(4)
- .Range("i13:i10000") = ""
- .Range("c43:c10000") = ""
- For j = 3 To 7
- For i = 13 To 27
- m = m + 1
- .Cells(m, 9) = UCase(.Cells(i, j).Value)
- Next
- Next
- .Range("I13:I" & m).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range( _
- "C43"), Unique:=True
- .Range("I13:I" & m).ClearContents
- End With
- End Sub
- Sub 作业五()
- Dim arr, brr, crr
- Dim i As Integer, j As Integer, m As Integer
- With Sheets(5)
- .Range("l8:l30").ClearContents
- arr = .Range("c9:g23")
- brr = .Range("l8:l30")
- For i = 1 To UBound(arr, 2)
- For j = 1 To UBound(arr)
- brr(arr(j, i), 1) = arr(j, i)
- Next j
- Next i
- ReDim crr(1 To UBound(brr), 1 To 1)
- For i = 1 To UBound(brr)
- If brr(i, 1) <> "" Then
- m = m + 1
- crr(m, 1) = brr(i, 1)
- End If
- Next i
- .Range("l8").Resize(UBound(crr), 1) = crr
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|