|
本帖最后由 xdragon 于 2013-11-23 12:22 编辑
- Sub 作业三()
- Dim arr(), i As Integer, brr(), cnt As Integer
- arr = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
- With CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr) '将A列中是湖北的行数据合并放入字典key
- If arr(i, 1) = "湖北" Then
- If Not .exists(arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3)) Then
- cnt = cnt + 1
- .Item(arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3)) = cnt '将每个key标记从1开始的索引号
- ReDim Preserve brr(1 To 3, 1 To cnt) '根据索引号扩大brr数组,存放新的key索引号对应的arr中的值
- brr(1, cnt) = arr(i, 1)
- brr(2, cnt) = arr(i, 2)
- brr(3, cnt) = arr(i, 3)
- End If
- End If
- Next
- End With
- Range("E2:G" & Cells(Rows.Count, "E").End(xlUp).Row).ClearContents
- Range("E2").Resize(UBound(brr, 2), UBound(brr)) = Application.Transpose(brr)
- End Sub
- Sub 作业二()
- Dim arr(), brr(), crr(), i&, j As Byte, cnt&, s$, t
- t = Timer
- arr = Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row).Value
- With CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr) '将行数据合并后存入字典key
- s = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & arr(i, 6)
- If Not .exists(s) Then .Item(s) = ""
- Next
- brr = Range("H2:M" & Cells(Rows.Count, "H").End(xlUp).Row).Value
- ReDim crr(1 To IIf(UBound(arr) > UBound(brr), UBound(arr), UBound(brr)), 1 To 6) '定义存放结果的数据大小为arr和brr上限的最大值
- Erase arr
- For i = 1 To UBound(brr)
- s = brr(i, 1) & "|" & brr(i, 2) & "|" & brr(i, 3) & "|" & brr(i, 4) & "|" & brr(i, 5) & "|" & brr(i, 6)
- If .exists(s) Then '将brr中整行的数据合并后在字典中查找,如果存在,则将对应的brr整行数据存入crr数组
- cnt = cnt + 1
- For j = 1 To 6
- crr(cnt, j) = brr(i, j)
- Next
- End If
- Next
- End With
- Range("O2:T" & Cells(Rows.Count, "O").End(xlUp).Row).ClearContents
- Range("O2").Resize(cnt, 6) = crr '导出结果到单元格
- MsgBox "完成计算,共使用 " & Timer - t & " 秒"
- End Sub
- Sub 作业一()
- Application.ScreenUpdating = False
- Dim arr(), brr(), d As Object, i&, j As Byte, str$, cnt&
-
- i = Sheets("源数据一").Cells(Rows.Count, 1).End(xlUp).Row
- arr = Sheets("源数据一").Range("A2:L" & i).Value
- Set d = CreateObject("scripting.dictionary")
-
- For i = 1 To UBound(arr)
- str = ""
- For j = 1 To 8
- str = str & "|" & arr(i, j) '将每行1-8列数据合并
- Next
-
- If Not d.exists(str) Then '字典中如果不存在这个合并值则添加此key,并添加索引值
- cnt = cnt + 1
- d(str) = cnt
- ReDim Preserve brr(1 To 12, 1 To cnt)
- brr(9, cnt) = arr(i, 9)
- If arr(i, 8) = "假焊" Then brr(12, cnt) = arr(i, 12) '若出现假焊的,取此行产量的值
- Else
- brr(9, d(str)) = brr(9, d(str)) & " " & arr(i, 9) '将第九列数字连接
- End If
- For j = 1 To 8
- brr(j, d(str)) = arr(i, j)
- Next
- brr(10, d(str)) = brr(10, d(str)) + arr(i, 10)
- brr(11, d(str)) = arr(i, 11)
- 'brr(12, d(str)) = brr(12, d(str)) + arr(i, 12) '汇总项
- Next
-
- On Error GoTo MsgAlert '如果遇到同一时间导出报表的,提示错误
- Worksheets.Add '
- ActiveSheet.Name = "第一题答案-" & Format(Time, "hhmm") '添加新的工作表并修改工作表名
- Range("A2").Resize(cnt, 12) = Application.Transpose(brr) '导出数据区域到单元格
- arr = Sheets("源数据一").Range("A1:L1").Value
- Range("A1:L1") = arr
-
- With Range("A1").CurrentRegion '设置导出区域的字体,列宽,边框及标题行背景颜色
- .Columns.AutoFit
- .Font.Size = 9
- .Borders.LineStyle = 1
- .Resize(1, 12).Interior.Color = vbCyan
- End With
- Application.ScreenUpdating = True
- Exit Sub
-
- MsgAlert: '提示错误
- Application.DisplayAlerts = False
- ActiveSheet.Delete
- MsgBox "您的计算过于频繁,请于1分钟后再次尝试", vbInformation + vbOKOnly, "提示"
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 三级下拉菜单。。。- Option Explicit
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Column = 5 Then Target(1).Offset(0, 1).Resize(1, 2) = "" '如果E列的值更改,则清空对应的F和G列的值
- If Target.Column = 6 Then Target(1).Offset(0, 1) = "" '如果F列的值更改,则清空对应G列的值
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim Dic, cell As Range, rngs As Range
- On Error Resume Next
- Set Dic = CreateObject("Scripting.Dictionary")
- Set rngs = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
- Select Case Target(1).Column
- Case 5 '在E列添加数据有效性,把A列存入字典key,然后在数据有效性序列中根据keys生成序列
- For Each cell In rngs
- Dic(cell.Text) = ""
- Next cell
- With Target(1).Validation '设置数据有效性
- .Delete '删除目标单元格原数据有效性
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(Dic.keys, ",") '添加序列,将keys合并,以“,”分隔
- .InCellDropdown = True '有下拉菜单
- End With
- Case 6 '在F列添加数据有效性,把目标单元格所在行的E列单元格与A列中的值比较,若一致,则将其A列中对应的B列的值存入字典key
- For Each cell In rngs
- If cell = Target(1).Offset(0, -1) Then
- Dic(cell.Offset(0, 1).Text) = ""
- End If
- Next cell
- With Target(1).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(Dic.keys, ",")
- .InCellDropdown = True
- End With
- Case 7
- For Each cell In rngs '在G列添加数据有效性,将所有目标单元格所在行的E列=A列,并且F列=B列的值,对应的C列的值存入字典key
- If cell = Target(1).Offset(0, -2) And cell.Offset(0, 1) = Target(1).Offset(0, -1) Then
- Dic(cell.Offset(0, 2).Text) = ""
- End If
- Next cell
- With Target(1).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(Dic.keys, ",")
- .InCellDropdown = True
- End With
- End Select
- Dic.RemoveAll
- End Sub
复制代码 |
评分
-
查看全部评分
|