|
B08:缔造者- Option Explicit
- Sub 作业一()
- Dim dac As Object
- Dim arr, t
- Dim jg(1 To 10000, 1 To 15) '声明一个比源数据区域较大一点的数组
- Dim str As String, shtname As String
- Dim i As Long, j As Long, h As Long
- Dim sht As Worksheet
- t = Timer '记录程序开始运行的时间
- Set dac = CreateObject("scripting.dictionary") '创建字典对象
- With Sheets("源数据一") '执行with语句
- arr = .Range("a1").CurrentRegion '将单元格A1所在的区域赋值给变量arr
- For i = 2 To UBound(arr) '遍历数组arr
- str = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4) _
- & "," & arr(i, 5) & "," & arr(i, 6) & "," & arr(i, 7) & "," & arr(i, 8) '用逗号建立一个字符串并赋值给变量str
- If dac.exists(str) Then '判断字典中是否存在字符串str
- h = dac(str) '记录字符串str所在的行数
- jg(h, 9) = jg(h, 9) & " " & arr(i, 9) '给数组jg赋值
- jg(h, 10) = jg(h, 10) + arr(i, 10) '给数组jg赋值
- Else
- j = j + 1 '计数器
- dac(str) = j '将字符串装入字典,其对应的项为变量j
- jg(j, 1) = arr(i, 1) '给数组jg赋值
- jg(j, 2) = arr(i, 2) '给数组jg赋值
- jg(j, 3) = arr(i, 3) '给数组jg赋值
- jg(j, 4) = arr(i, 4) '给数组jg赋值
- jg(j, 5) = arr(i, 5) '给数组jg赋值
- jg(j, 6) = arr(i, 6) '给数组jg赋值
- jg(j, 7) = arr(i, 7) '给数组jg赋值
- jg(j, 8) = arr(i, 8) '给数组jg赋值
- jg(j, 9) = arr(i, 9) '给数组jg赋值
- jg(j, 10) = arr(i, 10) '给数组jg赋值
- jg(j, 11) = arr(i, 11) '给数组jg赋值
- jg(j, 12) = IIf(arr(i, 8) = "连锡", "", arr(i, 12)) '判断数组arr的第8列是否为“连锡”字符,将其结果赋值给数组jg
- End If '结束判断
- Next i '继续下一个
- End With '结束with语句
- Set dac = Nothing '释放字典内存
- Application.ScreenUpdating = False '关闭屏幕刷新
- For Each sht In Worksheets '遍历工作表
- '判断工作表的名称前5个字符是否为“第一题答案”,如果存在则激活该工作表并跳转到star行执行其下面的语句
- If Left$(sht.Name, 5) = "第一题答案" Then sht.Activate: GoTo star
- Next sht '继续下一个
- Sheets.Add after:=Worksheets("效果一") '在工作表“效果一”后面新建一个工作表
- shtname = "第一题答案_" & Application.Text(Timer - t, "0.000") '将程序运行时间连接成一个字符串
- ActiveSheet.Name = shtname '重命名当前工作表名称
- star:
- Range("a1").CurrentRegion.ClearContents '清除单元格A1所在区域的内容
- '将数组里的值赋值给单元格A1扩大1行12列的区域
- Range("a1").Resize(1, 12) = Array("生产日期", "编号", "周期", "月份", "产品型号", "生产线", "班组", "不良类型", "不良位置", "不良数量", "备注", "产量")
- '设置A列格式为日期格式
- Range("a2").Resize(UBound(jg), 1).NumberFormat = "yyyy-m-d"
- Range("a2").Resize(UBound(jg), UBound(jg, 2)) = jg '区域赋值
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- Sub 作业二()
- Dim dac As Object, dhc As Object
- Dim arr, brr, jg, sj
- Dim str As String
- Dim i As Long, j As Long
- Application.ScreenUpdating = False '关闭屏幕刷新
- Set dac = CreateObject("scripting.dictionary") '创建字典对象
- Set dhc = CreateObject("scripting.dictionary") '创建字典对象
- With Sheets("作业二") '执行with语句
- arr = .Range("a1").CurrentRegion '将单元格A1所在的区域赋值给变量arr
- brr = .Range("h1").CurrentRegion '将单元格A1所在的区域赋值给变量brr
- For i = 2 To UBound(arr) '变量数组arr
- '用逗号建立一个字符串并赋值给变量str
- str = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4) & "," & arr(i, 5) & "," & arr(i, 6)
- dac(str) = "" '将字符串装入字典,其对应的项为空
- Next i '继续下一个
- For i = 2 To UBound(brr) '遍历数组brr
- '用逗号建立一个字符串并赋值给变量str
- str = brr(i, 1) & "," & brr(i, 2) & "," & brr(i, 3) & "," & brr(i, 4) & "," & brr(i, 5) & "," & brr(i, 6)
- If dac.exists(str) Then dhc(str) = "" '判断字典中是否存在字符串str,如存在,将字符串装入字典,其对应的项为空
- Next i '继续下一个
- ReDim jg(1 To dhc.Count, 1 To 6) '重新声明数组jg的大小
- sj = dhc.keys '将字典dhc中的关键字赋值给变量sj
- For i = 1 To dhc.Count '开始循环
- For j = 1 To 6 '开始循环
- jg(i, j) = Split(sj(i - 1), ",")(j - 1) '给数组jg赋值
- Next j '继续下一个
- Next i '继续下一个
- .Range("o2:t" & dhc.Count).ClearContents '清除内容
- .Range("o2").Resize(dhc.Count, 6) = jg '区域赋值
- End With '结束with语句
- Set dac = Nothing '清除字典释放内存
- Set dhc = Nothing '清除字典释放内存
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- Sub 作业二1()
- Dim dac As Object
- Dim arr, brr, jg
- Dim str As String
- Dim i As Long, j As Long
- Application.ScreenUpdating = False '关闭屏幕刷新
- Set dac = CreateObject("scripting.dictionary") '创建字典对象
- With Sheets("作业二") '执行with语句
- arr = .Range("a1").CurrentRegion '将单元格A1所在的区域赋值给变量arr
- brr = .Range("h1").CurrentRegion '将单元格H1所在的区域赋值给变量brr
- For i = 2 To UBound(arr) '遍历数组arr
- '用逗号建立一个字符串并赋值给变量str
- str = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4) & "," & arr(i, 5) & "," & arr(i, 6)
- dac(str) = "" '将字符串装入字典,其对应的项为空
- Next i '继续下一个
- ReDim jg(1 To dac.Count, 1 To 6) '重新声明数组jg的大小
- For i = 2 To UBound(brr) '变量数组brr
- '用逗号建立一个字符串并赋值给变量str
- str = brr(i, 1) & "," & brr(i, 2) & "," & brr(i, 3) & "," & brr(i, 4) & "," & brr(i, 5) & "," & brr(i, 6)
- If dac.exists(str) Then '判断字典中是否存在字符串str
- j = j + 1 '计数器
- dac(str) = j '将字符串装入字典,其对应的项为变量j
- jg(j, 1) = brr(i, 1) '给数组jg赋值
- jg(j, 2) = brr(i, 2) '给数组jg赋值
- jg(j, 3) = brr(i, 3) '给数组jg赋值
- jg(j, 4) = brr(i, 4) '给数组jg赋值
- jg(j, 5) = brr(i, 5) '给数组jg赋值
- jg(j, 6) = brr(i, 6) '给数组jg赋值
- End If '结束判断
- Next i '继续下一个
- .Range("o17:t" & dac.Count + 16).ClearContents '清除区域内容
- .Range("o17").Resize(dac.Count, 6) = jg '区域赋值
- End With '结束with语句
- Set dac = Nothing '清空字典释放内存
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- Sub 作业三()
- Dim dac As Object
- Dim arr, jg, ss
- Dim str As String
- Dim i As Long, j As Long
- Application.ScreenUpdating = False '关闭屏幕刷新
- Set dac = CreateObject("scripting.dictionary") '创建字典对象
- With Sheets("作业三") '执行with语句
- arr = .Range("a1").CurrentRegion '将单元格A1所在的区域赋值给变量arr
- For i = 2 To UBound(arr) '遍历数组arr
- If arr(i, 1) = "湖北" Then '判断数组第一列是否存在“湖北”
- '用逗号建立一个字符串并赋值给变量str
- str = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- dac(str) = "" '将字符串装入字典,其对应的项为变量j
- End If '结束判断
- ReDim jg(1 To dac.Count, 1 To 3) '重新声明数组jg的大小
- ss = dac.keys '将字典关键字赋值给变量ss
- For i = 1 To dac.Count '开始循环
- For j = 1 To 3 '开始循环
- jg(i, j) = Split(ss(i - 1), ",")(j - 1) '给数组jg赋值
- Next j '继续下一个
- Next i '继续下一个
- .Range("e2:g" & dac.Count).ClearContents '清除内容
- .Range("e2").Resize(dac.Count, 3) = jg '区域赋值
- End With '结束with语句
- Set dac = Nothing '清除字典释放内存
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- Sub 作业三1()
- Dim dac As Object
- Dim arr, jg, ss
- Dim str As String, zf As String
- Dim i As Long, j As Long
- Application.ScreenUpdating = False '关闭屏幕刷新
- Set dac = CreateObject("scripting.dictionary") '创建字典对象
- With Sheets("作业三") '执行with语句
- arr = .Range("a1").CurrentRegion '将单元格A1所在的区域赋值给变量arr
- zf = "湖北" '将条件省名称“湖北”赋值给变量zf
- ReDim jg(1 To UBound(arr), 1 To 3) '重新声明数组jg的大小
- For i = 2 To UBound(arr) '遍历数组arr
- '用逗号建立一个字符串并赋值给变量str和ss
- str = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- ss = zf & "," & arr(i, 2) & "," & arr(i, 3)
- If str = ss Then '判断字符串str是否包含“湖北”的字符串
- dac(str) = "" '装入字典,其对应的项为空
- End If '结束判断
- Next i '继续下一个
- ss = dac.keys '将字典关键字赋值给变量ss
- For i = 1 To dac.Count '遍历字典
- For j = 1 To 3 '开始循环
- jg(i, j) = Split(ss(i - 1), ",")(j - 1) '给数组jg赋值
- Next j '继续下一个
- Next i '继续下一个
- .Range("e16:g" & UBound(jg) + 15).ClearContents '清除内容
- .Range("e16").Resize(UBound(jg), 3) = jg '区域赋值
- End With '结束with语句
- Set dac = Nothing '清除字典释放内存
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
复制代码 查看了好多帖子,终于做出了作业三的附加题。不易呀!- '作业三附加题:三级下拉菜单
- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '当工作表上的选定区域发生改变时触发程序
- '设置数据有效性的区域范围在I2:K20内,在其他区域操作时,不触发程序的运行
- If Target.Count > 1 Then Exit Sub
- If Target.Column <> 9 And Target.Column <> 10 And Target.Column <> 11 Then Exit Sub
- If Target.Row < 2 Or Target.Row > 20 Then Exit Sub
- '声明变量
- Dim d As Object, d1 As Object
- Dim arr
- Dim i As Long, j As Long
- '创建两个字典对象
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- '数组赋值
- arr = Range("a1").CurrentRegion
- '忽略错误继续执行
- On Error Resume Next
- '执行selete判断语句
- Select Case Target.Column
- Case Is = 9 '如果活动单元格在第9列(下同)
- For i = 2 To UBound(arr) '遍历数组(下同)
- d(arr(i, 1)) = "" '装入字典(下同)
- Next i
- With Target.Validation '数据有效性验证(下同)
- .Delete '删除数据有效性(下同)
- '创建新的数据有效性(下同)
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=Join(d.keys, ",")
- End With
- '将二、三级菜单数据清除(可根据需要进行选择)
- ' Target.Offset(, 1) = ""
- ' Target.Offset(, 2) = ""
- d.RemoveAll '清空字典,重新装入数据,防止出错(下同)
- Case Is = 10
- If Len(Target.Offset(, -1).Value) > 0 Then
- For i = 2 To UBound(arr)
- k = arr(i, 1) & "," & arr(i, 2)
- d(k) = ""
- Next i
- For i = 2 To UBound(arr)
- If d.exists(Target.Offset(, -1).Value & "," & arr(i, 2)) Then d1(arr(i, 2)) = ""
- Next i
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=Join(d1.keys, ",")
- End With
- End If
- d.RemoveAll
- d1.RemoveAll
- Case Is = 11
- If Len(Target.Offset(, -1).Value) > 0 Then
- For i = 2 To UBound(arr)
- k = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- d(k) = ""
- Next i
- For i = 2 To UBound(arr)
- If d.exists(Target.Offset(, -2).Value & "," & Target.Offset(, -1).Value & "," & arr(i, 3)) Then d1(arr(i, 3)) = ""
- Next i
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=Join(d1.keys, ",")
- End With
- End If
- Set d = Nothing '清除字典,释放内存
- Set d1 = Nothing
- End Select '结束判断
- End Sub
复制代码 |
评分
-
查看全部评分
|