- 帖子
- 1429
- 主題
- 40
- 精華
- 0
- 積分
- 1453
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-6-27
|
回復 1# willeddie
謝謝前輩發表此主題
後學藉此帖練習字典.二維陣列與一維陣列,過程.結果與程式碼如下,請試試看是否符合需求
亂數產生的範例:
執行結果:
Option Explicit
Sub 陣列與字典練習_2條件下做資料整理相加_FG欄排序()
Dim Y, Z, V, Arr, i, T(3)
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
Set V = CreateObject("Scripting.Dictionary")
Arr = Range([C2], [A65536].End(3))
For i = 1 To UBound(Arr)
T(1) = DateValue(Arr(i, 1))
T(2) = Arr(i, 2)
T(3) = Arr(i, 3)
T(0) = T(1) & "|" & T(2)
Y(T(0)) = Y(T(0)) + T(3)
Z(T(0)) = T(1)
V(T(0)) = T(2)
Next
[F:H].ClearContents
[F2].Resize(Z.Count, 1) = Application.Transpose(Z.Items)
[G2].Resize(V.Count, 1) = Application.Transpose(V.Items)
[H2].Resize(Y.Count, 1) = Application.Transpose(Y.Items)
With [F2].Resize(Z.Count, 3)
.Sort _
KEY1:=.Item(1), Order1:=xlAscending, _
Key2:=.Item(2), Order2:=xlAscending, _
Header:=xlNo, Orientation:=xlTopToBottom
End With
[F1:H1] = [{"日期","產品","數量"}]
Set Y = Nothing
Set Z = Nothing
Set V = Nothing
Set Arr = Nothing
Erase T
End Sub
Sub 亂數製作範例_日期_產品_數量()
[A:C].ClearContents
[A1:C1] = [{"日期","產品","數量"}]
With [A2:A30]
.Formula = "=IF(RAND()>.5,TODAY()+INT(RAND()*5),TODAY()+INT(RAND()*-5))"
.Offset(, 1).Formula = "=MID(""ABC"",MOD(INT(RAND()*100),3)+1,1)"
.Offset(, 2).Formula = "=IF(RAND()>.1,INT(RAND()*100),INT(RAND()*-100))"
.Resize(, 3).Value = .Resize(, 3).Value
End With
End Sub |
|
發表於 2022-12-29 14:08
| 