|
楼主 |
发表于 2024-4-23 10:36
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 按钮1_Click()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").[a1].CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- For j = 2 To UBound(arr)
- d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 3)
- Next j
- [a1:b1] = Array("客户姓名", "消费总量")
- If d.Count > 0 Then
- [a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
- [b2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
- End If
- Application.ScreenUpdating = True
- End Sub
- Sub ll021()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").[a1].CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- For j = 2 To UBound(arr)
- d(arr(j, 1) & "#" & arr(j, 2)) = d(arr(j, 1) & "#" & arr(j, 2)) + arr(j, 3)
- Next j
-
- If d.Count > 0 Then
- [a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
- [c2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
- End If
- Columns("A:A").TextToColumns Destination:=Range("A1"), OtherChar:="#"
- [a1:c1].Value = Sheets("原始数据").[a1:c1].Value
- Application.ScreenUpdating = True
- End Sub
复制代码 图书馆的WPS有点问题。
- Sub 按钮1_Click()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").[a1].CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- For j = 2 To UBound(arr)
- d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 3)
- Next j
- [a1:b1] = Array("客户姓名", "消费总量")
- If d.Count > 0 Then
- [a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
- [b2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
- End If
- Application.ScreenUpdating = True
- End Sub
- Sub ll021()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").[a1].CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- For j = 2 To UBound(arr)
- d(arr(j, 1) & "#" & arr(j, 2)) = d(arr(j, 1) & "#" & arr(j, 2)) + arr(j, 3)
- Next j
-
- If d.Count > 0 Then
- [a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
- [c2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
- End If
- Columns("A:A").TextToColumns Destination:=Range("A1"), OtherChar:="#"
- [a1:c1].Value = Sheets("原始数据").[a1:c1].Value
- Application.ScreenUpdating = True
- End Sub
- ''
- Sub ll022()
- Set d = CreateObject("scripting.dictionary")
- Set dnm = CreateObject("scripting.dictionary")
- Set dy = CreateObject("scripting.dictionary")
- arr = Sheets("原始数据").[a1].CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.UsedRange.ClearContents
- For j = 2 To UBound(arr)
- d(arr(j, 1) & "#" & arr(j, 2)) = d(arr(j, 1) & "#" & arr(j, 2)) + arr(j, 3)
- dnm(arr(j, 1)) = ""
- dy(arr(j, 2)) = ""
- Next j
- Stop
- [a2].Resize(dnm.Count) = WorksheetFunction.Transpose(dnm.keys)
- [b1].Resize(1, dy.Count) = dy.keys
- [a1] = "客户姓名"
- arr = [a1].CurrentRegion
- For j = 2 To UBound(arr)
- For i = 2 To UBound(arr, 2)
- arr(j, i) = d(arr(j, 1) & "#" & arr(1, i))
- Next i
- Next j
- [a1].CurrentRegion = arr
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|