|
本帖最后由 xdragon 于 2013-12-11 18:09 编辑
C08:xdragon- Sub 作业一()
- Dim arr, brr(), crr(), drr(), d1 As Object, d2 As Object, i&, cnt1&, cnt2 As Byte
- arr = Range("A2:C" & Cells(Rows.Count, 1).End(3).Row).Value
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- ReDim brr(1 To UBound(arr), 1 To 1) '设置一个足够行数的数组
-
- For i = 1 To UBound(arr)
- If Not d1.exists(arr(i, 1)) Then '如果地区不存在则添加入字典d1
- cnt1 = cnt1 + 1
- ReDim Preserve crr(1 To cnt1 + 1)
- d1(arr(i, 1)) = cnt1
- End If
- If Not d2.exists(arr(i, 2)) Then '如果品种不存在则添加入字典d2
- cnt2 = cnt2 + 1
- ReDim Preserve brr(1 To UBound(arr), 1 To cnt2)
- ReDim Preserve drr(1 To cnt2 + 1)
- d2(arr(i, 2)) = cnt2
- End If
- brr(d1(arr(i, 1)), d2(arr(i, 2))) = brr(d1(arr(i, 1)), d2(arr(i, 2))) + arr(i, 3) '对相同地区、品种的数量进行累计
- crr(d1(arr(i, 1)) + 1) = crr(d1(arr(i, 1)) + 1) + arr(i, 3) '行合计
- drr(d2(arr(i, 2)) + 1) = drr(d2(arr(i, 2)) + 1) + arr(i, 3) '列合计
- Next
- crr(1) = "合计": drr(1) = "合计"
-
- Dim rng As Range '导出区域设置,导出相关数据
- Set rng = Range("F1")
- With rng
- .CurrentRegion.ClearContents
- .Value = " 品种" & Chr(10) & "地区"
- .Offset(1, 0).Resize(cnt1) = Application.Transpose(d1.keys) '地区
- .Offset(0, 1).Resize(1, cnt2) = d2.keys '品种
- .Offset(1, 1).Resize(cnt1, cnt2) = brr '数量
- .Offset(cnt1 + 1).Resize(1, cnt2 + 1) = drr '列合计
- .Offset(0, cnt2 + 1).Resize(cnt1 + 1, 1) = Application.Transpose(crr) '行合计
- .Offset(cnt1 + 1, cnt2 + 1) = Application.Sum(rng.Offset(cnt1 + 1, 1).Resize(1, cnt2)) '总计
- End With
-
- End Sub
- Sub 作业二()
- Dim arr, brr(), crr()
- Dim i As Integer, tmp
- Dim cnt2 As Integer, cnt3 As Integer, str As String
- Dim d2 As Object, d3 As Object
- Dim rng As Range
- '数据源导入数组中
- arr = Range("A2:D" & Cells(Rows.Count, 1).End(3).Row).Value
-
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
-
- crr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
- For i = 1 To UBound(arr)
- '统计行高
- If Not d2.exists(arr(i, 2)) Then cnt2 = cnt2 + 1: d2(arr(i, 2)) = cnt2
- '统计品种不同的数量
- If Not d3.exists(arr(i, 3)) Then cnt3 = cnt3 + 1: d3(arr(i, 3)) = cnt3
- Next
- '根据d1,d2,d3设置导出数组大小
- ReDim brr(1 To d2.Count, 1 To (d3.Count + 1) * 12 + 1)
-
- For i = 1 To UBound(arr)
- tmp = (crr(Month(arr(i, 1)) - 1) - 1) * (d3.Count + 1) + d3(arr(i, 3))
- brr(d2(arr(i, 2)), tmp) = brr(d2(arr(i, 2)), tmp) + arr(i, 4) '累计各月各品种产品各地区的数量
- tmp = (tmp \ (d3.Count + 1) + 1) * (d3.Count + 1)
- brr(d2(arr(i, 2)), tmp) = brr(d2(arr(i, 2)), tmp) + arr(i, 4) '计算各月小计
- brr(d2(arr(i, 2)), UBound(brr, 2)) = brr(d2(arr(i, 2)), UBound(brr, 2)) + arr(i, 4) '计算各月总计
- Next
-
- Erase arr
- ReDim arr(1 To 2, 1 To UBound(brr, 2))
- tmp = d3.keys
- '设置第一行与第二行列标签
- For i = 1 To UBound(arr, 2) - 1
- If i Mod (UBound(tmp) + 2) = 1 Then arr(1, i) = crr(i \ (UBound(tmp) + 2)) & "月"
- If i Mod (UBound(tmp) + 2) = 0 Then
- arr(2, i) = "小计"
- Else
- arr(2, i) = tmp(i Mod (UBound(tmp) + 2) - 1)
- End If
- Next
- arr(1, UBound(arr, 2)) = "合计"
- Erase tmp
- '设置结果导出区域,导出各处理结果
- Set rng = [f1]
- With rng
- .CurrentRegion.UnMerge
- .CurrentRegion.Clear
- .Value = "省份"
- .Offset(2, 0).Resize(d2.Count) = Application.Transpose(d2.keys)
- .Offset(0, 1).Resize(2, UBound(arr, 2)) = arr
- .Offset(2, 1).Resize(d2.Count, UBound(brr, 2)) = brr
- .Resize(2 + d2.Count, 1 + UBound(brr, 2)).Borders.LineStyle = 1
- '设置标题行与标题列的背景颜色
- .Resize(2 + d2.Count).Interior.ThemeColor = xlThemeColorAccent6
- .Resize(2, 1 + UBound(brr, 2)).Interior.ThemeColor = xlThemeColorAccent6
- '合并单元格设置
- For i = 1 To UBound(arr, 2) - 1 Step d3.Count + 1
- .Offset(0, i).Resize(1, d3.Count + 1).Merge
- .Offset(0, i).Resize(1, d3.Count + 1).HorizontalAlignment = xlCenter
- Next
- .Resize(2).Merge
- .Offset(0, UBound(arr, 2)).Resize(2).Merge
- End With
-
- d2.RemoveAll
- d3.RemoveAll
-
- End Sub
- Sub 作业三()
- Dim username As String, password As String, i As Integer
-
- With CreateObject("internetexplorer.application")
- .Visible = False
- .navigate "http://www.excelpx.com/forum-99-1.html"
-
- Do Until .readystate = 4
- DoEvents
- Loop
-
- On Error Resume Next
- username = InputBox("请输入登录用户名(若为自动登录请点击取消)", "提示")
- If username = "" Then GoTo messagesend
- password = InputBox("请输入登录密码", "提示")
- With .document.forms(0)
- .all("username").Value = username
- .all("password").Value = password
- .submit
- End With
- Do Until .readystate = 4
- DoEvents
- Loop
-
- messagesend:
- For i = 2 To Cells(Rows.Count, 1).End(3).Row
- message i
- Next
- End With
-
- End Sub
- Sub message(i As Integer)
- With CreateObject("internetexplorer.application")
- .Visible = False
- .navigate "http://www.excelpx.com/forum-99-1.html"
- Do Until .readystate = 4
- DoEvents
- Loop
- With .document
- .getelementbyid("typeid_fast").Click
- .all.tags("li")(1).Click
- .forms(2).all("subject").Value = Cells(i, 1)
- .forms(2).all("message").Value = Cells(i, 2)
- .forms(2).submit
- End With
- Do Until .readystate = 4
- DoEvents
- Loop
- .Quit
- If i = Cells(Rows.Count, 1).End(3).Row Then Exit Sub
- End With
- Application.Wait Now + TimeValue("00:00:25")
- End Sub
复制代码 作业三时间仓促,没有最终测试了。。。万一死机了,见谅啊。。。。。 |
评分
-
查看全部评分
|