請問sumif 改寫成字典或是array讓執行速度變快

　暱稱: 厚臉皮的學生

帖子: 1447
主題: 40
精華: 0
積分: 1471
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2025-5-5

21^# 跳轉到 »

發表於 2022-9-16 08:28 | 只看該作者

回復 20# samwang

前輩早安
1.事情應該不是這麼單純,多出了幾個表了!
2.WorksheetFunction.SumIfs WorksheetFunction.SumIf 兩種都有
3.題主前輩感覺很心急!腦筋動得很快,馬上套用,又馬上出新問題! 也蠻有趣!
4.很榮幸可以在這論壇跟前輩學習,謝謝指導分享!

TOP

s3526369

　暱稱: 大榮

中學生 Rank: 2

帖子: 45
主題: 3
精華: 0
積分: 72
點名: 0
作業系統: WIN7
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2018-2-12
最後登錄: 2025-3-27

22^#

發表於 2022-9-16 08:44 | 只看該作者

回復 20# samwang

感謝前輩~
是得沒錯最簡單的方法,可以用IF就好,一時沒有想到

TOP

s3526369

　暱稱: 大榮

中學生 Rank: 2

帖子: 45
主題: 3
精華: 0
積分: 72
點名: 0
作業系統: WIN7
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2018-2-12
最後登錄: 2025-3-27

23^#

發表於 2022-9-16 08:50 | 只看該作者

回復 21# Andy2483

早安~ 有找到解決得辦法了
Sub TEST_2()
Application.ScreenUpdating = False
Dim x, i, QA, QB, T, S, Srr, Arr, Ac, xR, C
Dim Trr, Brr, Crr, Rs, Rqs, Rqn, Ras, Ran, B
T = Timer
Set Srr = CreateObject("Scripting.Dictionary")
Set Trr = CreateObject("Scripting.Dictionary")
S = Split("入庫明細,全機種BOM,A需求,b需求,指圖明細,倉庫庫存", ",")
For i = 0 To UBound(S)
Set Srr(i) = Sheets(S(i))
Set Trr(i) = CreateObject("Scripting.Dictionary")
Next
Rs = Rows.Count
Ac = Srr(5).Cells(Rs, 1).End(3).Row
Arr = Range(Srr(5).[N4], Srr(5).Cells(Ac, 1))
C = Array(15, 18, 16, 26, 1, 8, 1, 8, 6, 12)
For i = 0 To UBound(C) Step 2
Set Rqs = Srr(i / 2).Cells(1, C(i))
Set Rqn = Srr(i / 2).Cells(Rs, C(i)).End(3)
Brr = Srr(i / 2).Range(Rqs, Rqn)
Set Ras = Srr(i / 2).Cells(1, C(i + 1))
Set Ran = Srr(i / 2).Cells(Rqn.Row, C(i + 1))
Crr = Srr(i / 2).Range(Ras, Ran)
For x = 1 To UBound(Brr)
B = Brr(x, 1)
Trr(i / 2)(B) = Trr(i / 2)(B) + Crr(x, 1)
Next
Next
For i = 1 To Ac - 3
xR = Arr(i, 1)
Arr(i, 5) = IIf(Trr(0)(xR), Trr(0)(xR), 0) '入庫合計
Arr(i, 3) = IIf(Trr(1)(xR), Trr(1)(xR), 0) '公司總需求
Arr(i, 10) = IIf(Trr(2)(xR), Trr(2)(xR), 0) 'A倉
Arr(i, 9) = IIf(Trr(3)(xR), Trr(3)(xR), 0) 'B倉
Arr(i, 13) = IIf(Trr(4)(xR), Trr(4)(xR), 0) '總出貨
QA = Arr(i, 4) + Arr(i, 5) '倉庫庫存
QB = Arr(i, 11) + Arr(i, 12)
Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - Arr(i, 13) '公司倉
Arr(i, 7) = QA - QB - Arr(i, 13) '總數
Next i
C = Array(, 3, 5, 7, 8, 9, 10, 13)
For i = 1 To UBound(C)
Srr(5).Cells(4, C(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , C(i))
Next
Set Arr = Nothing
Set Brr = Nothing
Set Crr = Nothing
MsgBox "共耗時：" & Timer - T & " 秒"
End Sub
想請問一下紅色這段,辦法解釋原因嗎,看不太懂

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1447
主題: 40
精華: 0
積分: 1471
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2025-5-5

24^#

發表於 2022-9-16 09:58 | 只看該作者

回復 23# s3526369

這段不適合前輩的新範例檔了!條件不一樣
C = Array(15, 18, 16, 26, 1, 8, 1, 8, 6, 12)  '這是O,R,P,ZA,H,A,H,F,L欄位
For i = 0 To UBound(C) Step 2 '兩兩一組去指定工作表指定欄位
Set Rqs = Srr(i / 2).Cells(1, C(i)) 'i / 2是指定每個工作表  i=0時 Rqs是[O1]
Set Rqn = Srr(i / 2).Cells(Rs, C(i)).End(3)
Brr = Srr(i / 2).Range(Rqs, Rqn)
Set Ras = Srr(i / 2).Cells(1, C(i + 1))   ' i=0時 Ras是[R1]
Set Ran = Srr(i / 2).Cells(Rqn.Row, C(i + 1))
Crr = Srr(i / 2).Range(Ras, Ran)
For x = 1 To UBound(Brr)
   B = Brr(x, 1)
   Trr(i / 2)(B) = Trr(i / 2)(B) + Crr(x, 1)
Next
Next

TOP

s3526369

　暱稱: 大榮

中學生 Rank: 2

帖子: 45
主題: 3
精華: 0
積分: 72
點名: 0
作業系統: WIN7
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2018-2-12
最後登錄: 2025-3-27

25^#

發表於 2022-9-16 10:47 | 只看該作者

回復 24# Andy2483

謝謝前輩,原來如此多一樣可以學習

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1447
主題: 40
精華: 0
積分: 1471
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2025-5-5

26^#

發表於 2022-9-16 14:24 | 只看該作者

回復 17# s3526369

這範例整理了一下,並且把SumIf的改為SumIfs 發現疑問
1.TC,TD,UC,UD這幾個變數跟執行結果是沒有關係,所以程式多跑了0.5秒
2.退庫,廢料倉這兩個工作表也是跟執行結果是沒有關係

請教前輩:
A.是範例不完整?
B.還是要忽略上述兩項疑問!簡化如下的程式碼
C.其他
如果是A 請提供新的範例,如果是B 後學繼續以下列程式碼研習縮短執行時間,
如果是C 請再說明

Sub 倉庫庫存合計COPY3()
Application.ScreenUpdating = False
Dim x, i As Long, DA, BA, bb, BC, FY, FX, EY, QA, QB, T
Dim S, Srr, Arr, Ac, xR, C, xC
T = Timer
Set Srr = CreateObject("Scripting.Dictionary")
'0 1 2 3 4 5 6 7
S = Split("入庫明細,全機種BOM,A需求,b需求,指圖明細,出庫明細,公司盤點,倉庫庫存", ",")
For i = 0 To UBound(S)
Set Srr(i) = Sheets(S(i))
Next
Ac = Srr(7).Cells(Rows.Count, 1).End(3).Row
Arr = Range(Srr(7).[N4], Srr(7).Cells(Ac, 1))
For i = 1 To Ac - 3
xR = Arr(i, 1)
Arr(i, 5) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR) '入庫合計 CCCCCCC
TA = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "A倉") '入庫明細-A
UA = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "B倉") '入庫明細-B
Arr(i, 3) = WorksheetFunction.SumIfs(Srr(1).[Z:Z], Srr(1).[P:P], xR) '公司總需求 CCCCCCC
'TC = WorksheetFunction.SumIfs(Srr(1).[U:U], Srr(1).[P:P], xR, Srr(1).[T:T], "A倉") '全BOM-殘單A需求
'TD = WorksheetFunction.SumIfs(Srr(1).[V:V], Srr(1).[P:P], xR, Srr(1).[T:T], "A倉") '全BOM-訂單A需求
'UC = WorksheetFunction.SumIfs(Srr(1).[U:U], Srr(1).[P:P], xR, Srr(1).[T:T], "B倉") '全BOM-殘單B需求
'UD = WorksheetFunction.SumIfs(Srr(1).[V:V], Srr(1).[P:P], xR, Srr(1).[T:T], "B倉") '全BOM-訂單B需求
TE = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "A倉") '指圖明細-A
UE = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "B倉") '指圖明細-B
Arr(i, 13) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR) '指圖明細-總出貨 CCCCCC
PA = WorksheetFunction.SumIfs(Srr(4).[K:K], Srr(4).[F:F], xR) '指圖明細-廢料 CCCCCC
TB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "A倉") '出庫明細-A
UB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "B倉") '出庫明細-B
Arr(i, 11) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "退庫") '出庫明細-退庫
PB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "廢料倉") '出庫明細-廢料
Arr(i, 4) = WorksheetFunction.SumIfs(Srr(6).[G:G], Srr(6).[A:A], xR) '上月盤點數
TF = WorksheetFunction.SumIfs(Srr(6).[F:F], Srr(6).[A:A], xR) '公司盤點-A倉
TG = WorksheetFunction.SumIfs(Srr(6).[K:K], Srr(6).[A:A], xR) '公司盤點-調整A倉
UF = WorksheetFunction.SumIfs(Srr(6).[E:E], Srr(6).[A:A], xR) '公司盤點-B倉
UG = WorksheetFunction.SumIfs(Srr(6).[J:J], Srr(6).[A:A], xR) '公司盤點-調整B倉
Arr(i, 10) = TF + TG + TA + TB - TE 'A倉
Arr(i, 9) = UF + UG + UA + UB - UE 'B倉
Arr(i, 12) = PA + PB
XA = 0
If Arr(i, 3) > 0 Then
XA = Arr(i, 4) + Arr(i, 5) - Arr(i, 11) - Arr(i, 12) - Arr(i, 3)
If XA > 0 Then
XA = 0
End If
End If
Arr(i, 6) = XA
QA = Arr(i, 4) + Arr(i, 5) ' ''倉庫庫存
QB = Arr(i, 11) + Arr(i, 12)
Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - Arr(i, 13) ''公司倉
Arr(i, 7) = QA - QB - Arr(i, 13) ''總數
Next i
C = Array(, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
For i = 1 To UBound(C)
Srr(7).Cells(4, C(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , C(i))
Next
MsgBox "共耗時：" & Timer - T & " 秒"
End Sub

複製代碼

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1447
主題: 40
精華: 0
積分: 1471
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2025-5-5

27^#

發表於 2022-9-16 15:43 | 只看該作者

回復 17# s3526369

謝謝前輩提供此範例
習得變數名參數化
以下程式碼供參考
待前輩樓上的回覆

Option Explicit
Sub 倉庫庫存合計COPY5()
Application.ScreenUpdating = False
Dim x&, i&, 值(1 To 17) As Long
Dim S, Srr, Arr, Ac, xR, C, T, XA, QA, QB
T = Timer
Set Srr = CreateObject("Scripting.Dictionary")
'0 1 2 3 4 5 6 7
S = Split("入庫明細,全機種BOM,A需求,b需求,指圖明細,出庫明細,公司盤點,倉庫庫存", ",")
For i = 0 To UBound(S)
Set Srr(i) = Sheets(S(i))
Next
Ac = Srr(7).Cells(Rows.Count, 1).End(3).Row
Arr = Range(Srr(7).[N4], Srr(7).Cells(Ac, 1))
For i = 1 To Ac - 3
xR = Arr(i, 1)
值(1) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR) '入庫合計C
值(2) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "A倉") '入庫明細-A
值(3) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "B倉") '入庫明細-B
值(4) = WorksheetFunction.SumIfs(Srr(1).[Z:Z], Srr(1).[P:P], xR) '公司總需求C
值(5) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "A倉") '指圖明細-A
值(6) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "B倉") '指圖明細-B
值(7) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR) '指圖明細-總出貨C
值(8) = WorksheetFunction.SumIfs(Srr(4).[K:K], Srr(4).[F:F], xR) '指圖明細-廢料C
值(9) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "A倉") '出庫明細-A
值(10) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "B倉") '出庫明細-B
值(11) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "退庫") '出庫明細-退庫
值(12) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "廢料倉") '出庫明細-廢料
值(13) = WorksheetFunction.SumIfs(Srr(6).[G:G], Srr(6).[A:A], xR) '上月盤點數
值(14) = WorksheetFunction.SumIfs(Srr(6).[F:F], Srr(6).[A:A], xR) '公司盤點-A倉
值(15) = WorksheetFunction.SumIfs(Srr(6).[K:K], Srr(6).[A:A], xR) '公司盤點-調整A倉
值(16) = WorksheetFunction.SumIfs(Srr(6).[E:E], Srr(6).[A:A], xR) '公司盤點-B倉
值(17) = WorksheetFunction.SumIfs(Srr(6).[J:J], Srr(6).[A:A], xR) '公司盤點-調整B倉
Arr(i, 5) = 值(1)
Arr(i, 3) = 值(4)
Arr(i, 13) = 值(7)
Arr(i, 11) = 值(11)
Arr(i, 4) = 值(13)
Arr(i, 10) = 值(14) + 值(15) + 值(2) + 值(9) - 值(5) 'A倉
Arr(i, 9) = 值(16) + 值(17) + 值(3) + 值(10) - 值(6) 'B倉
Arr(i, 12) = 值(8) + 值(12)
XA = 0
If 值(4) > 0 Then
XA = 值(13) + 值(1) - 值(11) - Arr(i, 12) - 值(4)
If XA > 0 Then
XA = 0
End If
End If
Arr(i, 6) = XA
QA = 值(13) + 值(1) '倉庫庫存
QB = 值(11) + Arr(i, 12)
Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - 值(7) '公司倉
Arr(i, 7) = QA - QB - 值(7) '總數
Next i
C = Array(, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
For i = 1 To UBound(C)
Srr(7).Cells(4, C(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , C(i))
Next
MsgBox "共耗時：" & Timer - T & " 秒"
End Sub