本帖最后由 风林火山 于 2013-11-23 15:08 编辑
- Sub work1()
- Dim arr, brr()
- Dim k%, i%, h%, str$
- Dim d As New Dictionary
- arr = Worksheets("源数据一").Range("a1:l" & Worksheets("源数据一").Cells(Rows.Count, 1).End(3).Row) '数组赋值
- ReDim brr(1 To UBound(arr), 1 To 12) '重新定义数组
- i = 2
- For k = 2 To UBound(arr)
- Rem 定义变量字符串
- str = arr(k, 1) & arr(k, 2) & arr(k, 3) & arr(k, 4) & arr(k, 5) & arr(k, 6) & arr(k, 7) & arr(k, 8)
- '下棋法获取数据:
- If d.Exists(str) = False Then
- d.Item(str) = i '用字典保存行号
- brr(i, 1) = arr(k, 1): brr(i, 2) = arr(k, 2)
- brr(i, 3) = arr(k, 3): brr(i, 4) = arr(k, 4)
- brr(i, 5) = arr(k, 5): brr(i, 6) = arr(k, 6)
- brr(i, 7) = arr(k, 7): brr(i, 8) = arr(k, 8)
- brr(i, 9) = arr(k, 9): brr(i, 10) = arr(k, 10)
- brr(i, 11) = arr(k, 11): brr(i, 12) = arr(k, 12)
- i = i + 1
- Else
- h = d.Item(str) '提取行号
- brr(h, 9) = brr(h, 9) & " " & arr(k, 9)
- brr(h, 10) = brr(h, 10) + arr(k, 10)
- brr(h - 1, 12) = ""
- End If
- Next k
- '定义标题
- brr(1, 1) = ("生产日期"): brr(1, 2) = ("编号")
- brr(1, 3) = ("周期"): brr(1, 4) = ("月份")
- brr(1, 5) = ("产品型号"): brr(1, 6) = ("生产线")
- brr(1, 7) = ("班组"): brr(1, 8) = ("不良类型")
- brr(1, 9) = ("不良位置"): brr(1, 10) = ("不良数量")
- brr(1, 11) = ("备注"): brr(1, 12) = ("产量")
-
- Worksheets.Add '新建工作表
- ActiveSheet.[a1].Resize(UBound(brr), 12) = brr '显示数据
- ActiveSheet.Name = "第一题答案" & "-" & Format(Time, "hhmm") '工作表命名
- End Sub
- Sub work2()
- Dim arr, brr, crr(1 To 10000, 1 To 6)
- Dim i&, k&, n&, str1, str2
- Dim d1 As New Dictionary
- On Error Resume Next '容错
- arr = Range("a2:f" & Cells(Rows.Count, 6).End(xlUp).Row) '数组赋值
- brr = Range("h2:m" & Cells(Rows.Count, 13).End(xlUp).Row) '数组赋值
- For i = 1 To UBound(arr) - 1
- str1 = arr(i, 1) & "@" & arr(i, 2) & "@" & arr(i, 3) & "@" & arr(i, 4) & "@" & arr(i, 5) & "@" & arr(i, 6)
- d1.Item(str1) = "" 'A组数据生成不重复数据
- Next i
- For k = 1 To UBound(brr) - 1
- str2 = brr(k, 1) & "@" & brr(k, 2) & "@" & brr(k, 3) & "@" & brr(k, 4) & "@" & brr(k, 5) & "@" & brr(k, 6)
- If d1.Exists(str2) = True Then '判断B组数据和A组数据相同数据
- n = n + 1
- '生成数据
- crr(n, 1) = brr(k, 1): crr(n, 2) = brr(k, 2): crr(n, 3) = brr(k, 3): crr(n, 4) = brr(k, 4): crr(n, 5) = brr(k, 5): crr(n, 6) = brr(k, 6)
- End If
- Next k
- '显示数据
- Range("o:t").ClearComments
- Range("o1") = "C"
- Range("o1:t1").Merge
- Range("o1:t1").HorizontalAlignment = xlCenter
- Range("o2").Resize(UBound(crr), 6) = crr
- End Sub
- Sub work3()
- Dim arr, brr()
- Dim d As New Dictionary
- Dim k%, i%, m%, str As String
- arr = Worksheets("作业三").Range("a1:c25")
- For k = 2 To UBound(arr)
- str = arr(k, 1) & " " & arr(k, 2) & " " & arr(k, 3)
- If InStr(str, "湖北") Then '生成包含湖北的不重复数据
- d.Item(str) = k
- End If
- Next k
-
- ReDim brr(1 To d.Count, 1 To 3) '重新定义数组
- For i = 1 To d.Count
- For m = 1 To 3
- brr(i, m) = Split(d.Keys(i - 1), " ")(m - 1) '拆分数据
- Next m
- Next i
-
- Worksheets("作业三").[e2].Resize(d.Count, 3) = brr '显示数据
- End Sub
复制代码 |