VBA แก้ไขโค้ดการบวกหลายๆ เงื่อนไขให้ทำงานเร็วขึ้นครับ

[DhitiBank]

สวัสดีอาจารย์และเพื่อนๆ ครับ

ผมเจอปัญหาว่า หากเรียกช่วงข้อมูลหลายปี จะต้องใช้เวลานานมากครับ กว่าคำสั่งจะทำงานเสร็จทั้งๆ ที่ข้อมูลจริงๆ ก็ไม่มากเท่าไรนัก
คำสั่งดังกล่าวอยู่ในโมดูล Analyze เท่าที่ไล่ดูพบว่าช้าที่คำสั่งหาผลรวมโดยดูเงื่อนไขหลายๆ เงื่อนไขครับ

Code: Select all

Private Sub FindTargetData()
        '----Other----
        For m = 1 To k
            i = Application.Match(.Cells(m + 6, "a"), rTarget, 0)
            o = Application.CountIf(rTarget, .Cells(m + 6, "a"))
            Set rTgSmall = wsTemp.Range("a1").Offset(i, 0).Resize(o, 1)
            For n = 1 To j * 12
                p = 0
                For Each r In rTgSmall
                        If r.Offset(0, 1) = .Cells(m + 6, "b") Then
                            If r.Offset(0, 2) = .Cells(m + 6, "c") Then
                                If r.Offset(0, 4) = .Cells(m + 6, "d") Then
                                    If r.Offset(0, 7) = .Cells(5, n + 5) Then
                                        If r.Offset(0, 8) = .Cells(6, n + 5) Then
                                            p = p + r.Offset(0, 3)
                                        End If
                                    End If
                                End If
                            End If
                        End If
                Next r
                If p = 0 Then a(m, n) = ""
                If p > 0 Then a(m, n) = p
            Next n
        Next m
        .Range("f7").Resize(k, j * 12).Value = a
        '----Other----
End Sub

การทำงานคร่าวๆ คือ:
1. พอกดปุ่มในชีท Anlz ก็จะรับค่าปีในเซลล์ B2, B3
2. สร้างชีทชั่วคราว แล้วใช้ Advanced filter กรองข้อมูลในชีท database ที่อยู่ในช่วงปีที่กำหนดมาไว้ในชีทชั่วคราว
3. สร้างรายการลูกค้า, สินค้า, ขนาดบรรจุ (เลือกแบบไม่ซ้ำ) ในชีท Anlz คอลัมน์ B-E แล้วก็รันเลขปี/เดือนบนหัวตารางในแถวที่ 5, 6
4. จากนั้นจะไล่ดูในชีทชั่วคราวตามเงื่อนไขคือ จังหวัด, ชื่อลูกค้า, ชื่อสินค้า, ขนาดบรรจุ, ปี และเดือน แล้วจำค่าเอาไว้ก่อนในอาร์เรย์ (ตามโค้ดที่มีปัญหาเรื่องช้าด้านบน) แล้วค่อยเอามาใส่ในตารางชีท Anlz ครับ แต่หากเลือกช่วงหลายๆ ปี มันจะใช้เวลานานมากเลยเมื่อรันในเครื่องผม ตอนแรกช้ากว่านี้มากก็เลยลองลดจำนวนช่วงข้อมูลโดยกำหนด rTgSmall เรนจ์โดยดูจากชื่อจังหวัด ก็เร็วขึ้นมานิดหน่อยครับแต่เครื่องผมมันก็ยังรันได้ช้าอยู่ดี

รบกวนสอบถามว่า ผมควรปรับโค้ดอย่างไรดีเพื่อให้มันทำงานเร็วขึ้นครับ (ลองเขียนสูตร sumifs เองดู คำนวณเร็วกว่ามากๆ เลย แต่ผมเขียนใส่จาก VBA ไม่เป็นครับ)

ขอบคุณครับ

[snasui]

ไม่ทราบว่าปกติใช้เวลาเท่าไรครับ

ลองปรับ Code เป็นด้านล่างแล้วจับเวลาดูว่าเร็วขึ้นหรือไม่ครับ

Code: Select all

For n = 1 To j * 12
    p = 0
    For Each r In rTgSmall
            If r.Offset(0, 1) = .Cells(m + 6, "b") And _
                r.Offset(0, 2) = .Cells(m + 6, "c") And _
                r.Offset(0, 4) = .Cells(m + 6, "d") And _
                r.Offset(0, 7) = .Cells(5, n + 5) And _
                r.Offset(0, 8) = .Cells(6, n + 5) Then
                    p = p + r.Offset(0, 3)
            End If
    Next r
    If p = 0 Then a(m, n) = ""
    If p > 0 Then a(m, n) = p
Next n

[DhitiBank]

ตอนแรกเลือกช่วง 2 ปี น่าจะใช้เวลาเกือบๆ 1 นาทีครับ เดี๋ยวผมลองปรับโค้ดตามที่อาจารย์บอกแล้วจะมาแจ้งผลครับ ขอบคุณมากครับ

[DhitiBank]

อาจารย์ครับ ผมเอามาทดสอบกับเครื่องญาติที่มันแรงขึ้นแล้วใช้มือถือจับเวลาดู ผลออกมาแบบนี้ครับ
-ยังไม่ปรับโค้ด 4.80 วินาที
-ปรับแล้ว 13.68 วินาที

ไม่อยากนึกภาพเครื่องผมเลยครับ T_T
เดี๋ยวผมลองบันทึกมาโครให้ใส่ sumifs ด้วย VBA ดูครับ หากติดแล้วจะมาถามใหม่

ขอบพระคุณครับ

[bank9597]

ตอนเย็นนี้ ถ้ายังทัน ผมจะช่วยปรับให้อีกแรงครับ

[DhitiBank]

ตอนเย็นนี้ ถ้ายังทัน ผมจะช่วยปรับให้อีกแรงครับ

ขอขอบคุณล่วงหน้าเลยครับ

[snasui]

ลองดู Code ด้านล่างว่าเร็วขึ้นบ้างหรือไม่ครับ

Code: Select all

'Other code
For Each r In rTarget
    r.Offset(0, 1) = Year(r)
    r.Offset(0, 2) = Month(r)
    r.Offset(0, 3) = r.Offset(0, -8) & r.Offset(0, -7) & r.Offset(0, -5) & r.Offset(0, -3)
Next r
.Range("a:a,d:d,j:j").Delete
.Range("i1") = "Month"
.Range("j1") = "Index"
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("a1"), SortOn:=xlSortOnValues _
    , Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("b1"), SortOn:=xlSortOnValues _
    , Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("c1"), SortOn:=xlSortOnValues _
    , Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
    .SetRange Range("a1:j" & tgRw)
    .Header = xlYes
    .MatchCase = False
    .SortMethod = xlPinYin
    .Apply
End With
End With

'Other code
For m = 1 To k
    i = Application.Match(.Cells(m + 6, "a") & .Cells(m + 6, "b") & .Cells(m + 6, "c") & _
        .Cells(m + 6, "d"), rTarget.Offset(0, 9), 0)
    o = Application.CountIf(rTarget.Offset.Offset(0, 9), .Cells(m + 6, "a") & .Cells(m + 6, "b") & .Cells(m + 6, "c") & _
                                    .Cells(m + 6, "d"))
    Set rTgSmall = wsTemp.Range("a1").Offset(i, 0).Resize(o, 1)
    For n = 1 To j * 12
        p = Application.SumIf(rTgSmall.Offset(0, 3), _
            rTgSmall.Offset(0, 1), .Cells(m + 6, "b"), _
            rTgSmall.Offset(0, 2), .Cells(m + 6, "c"), _
            rTgSmall.Offset(0, 4), .Cells(m + 6, "d"), _
            rTgSmall.Offset(0, 7), .Cells(5, n + 5), _
            rTgSmall.Offset(0, 8), .Cells(6, n + 5))
            If p = 0 Then a(m, n) = ""
            If p > 0 Then a(m, n) = p
    Next n
Next m
'Other code

[DhitiBank]

ขอเอาไปลองก่อนครับ แล้วจะมารายงานผลครับอาจารย์ ขอบพระคุณมากครับ

[DhitiBank]

ลองทดสอบแล้วครับ โดยเรียกข้อมูลจากปี 2011 - 2015 (ลองกับเครื่องญาติเพราะมันแรงกว่าเครื่องผมเอง)
-ไม่ได้ปรับ 11.27 วินาที
-ปรับแล้ว 1.47 วินาที

สุดยอดเลยครับอาจารย์ ขอบพระคุณมากครับ

แต่ตอนแรกตรงโค้ด

Code: Select all

        p = Application.SumIf(rTgSmall.Offset(0, 3), _
            rTgSmall.Offset(0, 1), .Cells(m + 6, "b"), _
            rTgSmall.Offset(0, 2), .Cells(m + 6, "c"), _
            rTgSmall.Offset(0, 4), .Cells(m + 6, "d"), _
            rTgSmall.Offset(0, 7), .Cells(5, n + 5), _
            rTgSmall.Offset(0, 8), .Cells(6, n + 5))

มันฟ้องว่า Invalid Number of Argument ผมเลยเปลี่ยนตรง SumIf เป็น SumIfs ก็ใช้งานได้ไม่มีปัญหาแล้วครับ

ขอสอบถามตรง offset ซ้อน offset หน่อยครับ

Code: Select all

o = Application.CountIf(rTarget.Offset.Offset(0, 9), .Cells(m + 6, "a") & .Cells(m + 6, "b") & .Cells(m + 6, "c") & .Cells(m + 6, "d"))

rTarget[color=#FF0000].Offset.Offset[/color](0,9) หมายถึงอะไรเหรอครับ

[snasui]

มันฟ้องว่า Invalid Number of Argument ผมเลยเปลี่ยนตรง SumIf เป็น SumIfs ก็ใช้งานได้ไม่มีปัญหาแล้วครับ

ผมคีย์ s ตกไปครับ

rTarget.Offset.Offset(0,9) หมายถึงอะไรเหรอครับ

ส่วน offset คีย์เกินไป ลบออกได้เลยครับ

[DhitiBank]

ขอบพระคุณมากครับอาจารย์

ขอสอบถามเพิ่มหน่อยครับ หากผมต้องเอาไปใช้กับ excel 2003 ตรงโค้ด Application.SumIfs จะไม่สามารถใช้ได้ใช่ไหมครับ
หากเป็นแบบนั้น ผมก็จะต้องเอาปีและเดือนมาเชื่อมตรงคอลัมน์ Index ในชีทชั่วคราว แล้วเปลี่ยนมาใช้ Sumif โดยอ้างอิงถึงช่วง index นี้แทนใช่ไหมครับ
ประมาณว่า application.sumif({Index ในชีทชั่วคราว},..&..&..&..&..&..,{ช่วงจำนวนสินค้า})

[snasui]

ขอสอบถามเพิ่มหน่อยครับ หากผมต้องเอาไปใช้กับ excel 2003 ตรงโค้ด Application.SumIfs จะไม่สามารถใช้ได้ใช่ไหมครับ

ถูกต้องครับ

หากเป็นแบบนั้น ผมก็จะต้องเอาปีและเดือนมาเชื่อมตรงคอลัมน์ Index ในชีทชั่วคราว แล้วเปลี่ยนมาใช้ Sumif โดยอ้างอิงถึงช่วง index นี้แทนใช่ไหมครับ
ประมาณว่า application.sumif({Index ในชีทชั่วคราว},..&..&..&..&..&..,{ช่วงจำนวนสินค้า})

สามารถใช้แบบนี้ได้เลยครับ

[DhitiBank]

ขอบคุณครับ

[bank9597]

สุดท้ายก็ไม่ได้ช่วยตอบ

[DhitiBank]

ไม่เป็นไรครับ คิดว่าจะช่วยไว้ก่อนแล้วก็ขอขอบคุณแล้วครับ ผมกำลังหัด VBA คงเจอปัญหาอีกเยอะ เดี๋ยวก็มาถามอีกครับ