Nhờ anh chị giúp Dictionary thay hàm Sumif (3 người xem)

Trần Văn Bình · 3/5/23

Sub Thong_ke()
Dim i As Long, K As Long, DCuoi As Long, J As Long
Dim Arr_N(), Arr_D(), Dic As Object
DCuoi = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Arr_N = Sheet1.Range("A5:W" & DCuoi)
ReDim Arr_D(1 To UBound(Arr_N, 1), 1 To 8)
Set Dic = CreateObject("Scripting.Dictionary")
K = 0
For i = 1 To UBound(Arr_N, 1)
If Not Dic.exists(Arr_N(i, 6)) Then
K = K + 1
Dic.Add Arr_N(i, 6), K
Arr_D(K, 1) = K
Arr_D(K, 2) = Arr_N(i, 6)
Arr_D(K, 3) = Arr_N(i, 7)
Arr_D(K, 4) = Arr_N(i, 9)
Arr_D(K, 5) = Arr_N(i, 19)
Arr_D(K, 6) = Arr_N(i, 22)
Arr_D(K, 7) = Arr_N(i, 23)
Arr_D(K, 8) = Arr_N(i, 14)
Else
J = Dic.Item(Arr_N(i, 6))
Arr_D(J, 5) = Arr_D(J, 5) + Arr_N(i, 19) * 24
Arr_D(J, 6) = Arr_D(J, 6) + Arr_N(i, 22)
Arr_D(J, 7) = Arr_D(J, 7) + Arr_N(i, 23)
Arr_D(J, 8) = Arr_D(J, 8) + Arr_N(i, 14)
End If
Next
Sheet8.Range("E6:L50000").ClearContents
Sheet8.Range("E6").Resize(K, 8) = Arr_D

End Sub
Có file đính kèm nhờ anh chị giúp Tại Sheet Thong_ke cột I bị sai

Hoàng Tuấn 868 · 3/5/23

Bài này đăng vào chuyên mục "Lập trình với Excel" mới đúng chỗ bạn ơi.

Trần Văn Bình · 3/5/23

Hoàng Tuấn 868 đã viết:
Bài này đăng vào chuyên mục "Lập trình với Excel" mới đúng chỗ bạn ơi.

Mình thấy liên quan đến thời gian đó bạn

VetMini · 3/5/23

Hoàng Tuấn 868 đã viết:
Bài này đăng vào chuyên mục "Lập trình với Excel" mới đúng chỗ bạn ơi.

Đọc code chả thấy "ngày tháng và thời gian" chỗ nào cả.
Mấy chục dòng im ỉm, lười chú thích, ông nội ai biết code cần làm gì mà bảo kết quả sai.

Thóc Sama · 3/5/23

Trần Văn Bình đã viết:
Sub Thong_ke()
Dim i As Long, K As Long, DCuoi As Long, J As Long
Dim Arr_N(), Arr_D(), Dic As Object
DCuoi = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Arr_N = Sheet1.Range("A5:W" & DCuoi)
ReDim Arr_D(1 To UBound(Arr_N, 1), 1 To 8)
Set Dic = CreateObject("Scripting.Dictionary")
K = 0
For i = 1 To UBound(Arr_N, 1)
If Not Dic.exists(Arr_N(i, 6)) Then
K = K + 1
Dic.Add Arr_N(i, 6), K
Arr_D(K, 1) = K
Arr_D(K, 2) = Arr_N(i, 6)
Arr_D(K, 3) = Arr_N(i, 7)
Arr_D(K, 4) = Arr_N(i, 9)
Arr_D(K, 5) = Arr_N(i, 19)
Arr_D(K, 6) = Arr_N(i, 22)
Arr_D(K, 7) = Arr_N(i, 23)
Arr_D(K, 8) = Arr_N(i, 14)
Else
J = Dic.Item(Arr_N(i, 6))
Arr_D(J, 5) = Arr_D(J, 5) + Arr_N(i, 19) * 24
Arr_D(J, 6) = Arr_D(J, 6) + Arr_N(i, 22)
Arr_D(J, 7) = Arr_D(J, 7) + Arr_N(i, 23)
Arr_D(J, 8) = Arr_D(J, 8) + Arr_N(i, 14)
End If
Next
Sheet8.Range("E6:L50000").ClearContents
Sheet8.Range("E6").Resize(K, 8) = Arr_D

End Sub
Có file đính kèm nhờ anh chị giúp Tại Sheet Thong_ke cột I bị sai

1. Code bạn phải để trong phần mã code.
2. Nhờ BQT dời bài viết vào box lập trình.
3. Mình chưa kiểm tra chi tiết xong thấy phần này có vấn đề.
Thử thay

Mã:

   Arr_D(K, 5) = Arr_N(i, 19)
   thành
      Arr_D(K, 5) = Arr_N(i, 19)*24

Rồi kiểm tra kết quả xem đúng chưa.

Trần Văn Bình · 3/5/23

VetMini đã viết:
Đọc code chả thấy "ngày tháng và thời gian" chỗ nào cả.
Mấy chục dòng im ỉm, lười chú thích, ông nội ai biết code cần làm gì mà bảo kết quả sai.

Mình có đính kèm file trên bên Sheet Thong_Ke dùng Dictionary và bảng 1 bên dùng Sumif Tại cột I thống kê giờ bị sai

Bài đã được tự động gộp: 3/5/23

THÓC SAMA đã viết:
1. Code bạn phải để trong phần mã code.
2. Nhờ BQT dời bài viết vào box lập trình.
3. Mình chưa kiểm tra chi tiết xong thấy phần này có vấn đề.
Thử thay

Mã:

Arr_D(K, 5) = Arr_N(i, 19) thành Arr_D(K, 5) = Arr_N(i, 19)*24

và

Mã:

Arr_D(J, 5) = Arr_D(J, 5) + Arr_N(i, 19) * 24 thành Arr_D(J, 5) = (Arr_D(J, 5) + Arr_N(i, 19)) * 24

Rồi kiểm tra kết quả xem đúng chưa.

Mình đã thử cách như của bạn nhừng ra kết quả số quá lớn.

Thóc Sama · 3/5/23

Trần Văn Bình đã viết:
Mình có đính kèm file trên bên Sheet Thong_Ke dùng Dictionary và bảng 1 bên dùng Sumif Tại cột I thống kê giờ bị sai

Bài đã được tự động gộp: 3/5/23

Mình đã thử cách như của bạn nhừng ra kết quả số quá lớn.

Nhầm một chút, bạn xem lại #5, mình đã đính chính.

VetMini · 3/5/23

Trần Văn Bình đã viết:
Mình có đính kèm file trên bên Sheet Thong_Ke dùng Dictionary và bảng 1 bên dùng Sumif Tại cột I thống kê giờ bị sai
...

Tôi chỉ hỏi code làm gì. Và chỗ bị sai nó ra cái gì? đáng lẽ đúng thì ra cái gì?

Nếu từ đầu bạn không mang định kiến dùng Dictionary thì có thể suy nghĩ của bạn cởi mở hơn. Có thể có cách khác dễ tìm ra chỗ sai hơn.

Trần Văn Bình · 3/5/23

VetMini đã viết:
Tôi chỉ hỏi code làm gì. Và chỗ bị sai nó ra cái gì? đáng lẽ đúng thì ra cái gì?

Nếu từ đầu bạn không mang định kiến dùng Dictionary thì có thể suy nghĩ của bạn cởi mở hơn. Có thể có cách khác dễ tìm ra chỗ sai hơn.

ý mình là dùng Dic nó ra kết quả khống đúng so với hàm sumif có bảng kế bên đó bạn

Hoàng Nhật Phương · 3/5/23

Trần Văn Bình đã viết:
ý mình là dùng Dic nó ra kết quả khống đúng so với hàm sumif có bảng kế bên đó bạn

Bạn tham khảo . . :

Mã:

Option Explicit

Sub Test()

    Dim dict As Object, wsResult As Worksheet, wsThongKe As Worksheet
    Dim data As Variant, result As Variant, code As Variant
    Dim sTenHV As String, sHang As String
    Dim dDem As Double, dTdong As Double, dTai As Double, dKm As Double
    Dim lr As Long, i As Long, k As Long, r As Long

    Set wsResult = ThisWorkbook.Worksheets("Result")
    Set wsThongKe = ThisWorkbook.Worksheets("Thong_Ke")
   
    If wsResult.AutoFilterMode Then wsResult.AutoFilterMode = False
    lr = wsResult.Cells(wsResult.Rows.Count, "F").End(xlUp).Row
    If (lr < 5) Then
        MsgBox "Khong co du lieu", vbInformation
        Exit Sub
    End If
   
    data = wsResult.Range("F5:W" & lr).Value
    lr = UBound(data, 1)
    ReDim result(1 To lr, 1 To 10)
   
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
   
    For i = 1 To lr
       
        code = data(i, 1)       '// Ma hoc vien
        sTenHV = data(i, 2)     '// Ten hoc vien
        sHang = data(i, 16)     '// Hang
        dDem = data(i, 14) * 24 '// Dem
        dTdong = data(i, 17)    '// So tu dong
        dTai = data(i, 18)      '// Tai
        dKm = data(i, 9)        '// Km
       
        If Not dict.Exists(code) Then
            k = k + 1
            dict.Add code, k
            result(k, 1) = k
            result(k, 2) = code
            result(k, 3) = sTenHV
            result(k, 4) = sHang
            result(k, 5) = dDem
            result(k, 6) = dTdong
            result(k, 7) = dTai
            result(k, 8) = dKm
        Else
            r = dict.Item(code)
            result(r, 5) = result(r, 5) + dDem
            result(r, 6) = result(r, 6) + dTdong
            result(r, 7) = result(r, 7) + dTai
            result(r, 8) = result(r, 8) + dKm
        End If
       
    Next i
   
    lr = wsThongKe.Cells(wsThongKe.Rows.Count, "F").End(xlUp).Row
    If (lr > 5) Then wsThongKe.Range("E6:M" & lr).ClearContents
    If (k > 0) Then wsThongKe.Range("E6").Resize(k, 10).Value = result
   
    MsgBox "Ket thuc", vbInformation
   
End Sub

Hoặc :

Mã:

Sub Test2()

    Dim dict As Object, wsResult As Worksheet, wsThongKe As Worksheet
    Dim data As Variant, result As Variant, code As Variant
    Dim index(1 To 7, 1 To 2) As Integer
    Dim sTenHV As String, sHang As String
    Dim lr As Long, i As Long, k As Long, r As Long
    Dim c As Integer
    
    Set wsResult = ThisWorkbook.Worksheets("Result")
    Set wsThongKe = ThisWorkbook.Worksheets("Thong_Ke (2)")
    
    If wsResult.AutoFilterMode Then wsResult.AutoFilterMode = False
    lr = wsResult.Cells(wsResult.Rows.Count, "F").End(xlUp).Row
    If (lr < 5) Then
        MsgBox "Khong co du lieu", vbInformation
        Exit Sub
    End If
    
    index(1, 1) = 1:        index(1, 2) = 2:    '// Ma hoc vien
    index(2, 1) = 2:        index(2, 2) = 3:    '// Ten hoc vien
    index(3, 1) = 16:       index(3, 2) = 4:    '// Hang
    index(4, 1) = 14:       index(4, 2) = 5:    '// Dem
    index(5, 1) = 17:       index(5, 2) = 6:    '// So tu dong
    index(6, 1) = 18:       index(6, 2) = 7:    '// Tai
    index(7, 1) = 9:        index(7, 2) = 8:    '// Km

    data = wsResult.Range("F5:W" & lr).Value
    lr = UBound(data, 1)
    ReDim result(1 To lr, 1 To 10)
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    For i = 1 To lr
        code = data(i, 1)
        If Not dict.Exists(code) Then
            k = k + 1
            dict.Add code, k
            result(k, 1) = k
        End If
        r = dict.Item(code)
        For c = 1 To 7
            If c < 4 Then
                result(r, c + 1) = data(i, index(c, 1))
            ElseIf c = 4 Then
                result(r, c + 1) = result(r, c + 1) + data(i, index(c, 1)) * 24
            Else
                result(r, c + 1) = result(r, c + 1) + data(i, index(c, 1))
            End If
        Next c
    Next i

    lr = wsThongKe.Cells(wsThongKe.Rows.Count, "F").End(xlUp).Row
    If (lr > 5) Then wsThongKe.Range("E6:M" & lr).ClearContents
    wsThongKe.Range("E6").Resize(k, 10).Value = result

    MsgBox "Ket thuc", vbInformation
    
End Sub

VetMini · 3/5/23

Trần Văn Bình đã viết:
ý mình là dùng Dic nó ra kết quả khống đúng so với hàm sumif có bảng kế bên đó bạn

Nếu bạn nói từ đầu là cột I nó sum số ngày và đổi thành giờ thì người ta đã dễ dàng chỉ ra chỗ sai trong code rồi.
Bạn sai là do code tổng dữ liệu không nhất quán chứ chả liên quan gì đến đít sần cả.

Trần Văn Bình · 3/5/23

Cám ơn các bạn đã giúp mình mới học code nên chưa hiểu được nhiều đặc biện cám ơn @Hoàng Nhật Phương đã giúp code đúng ý mình và công thưc chính xác.
Cùng là Dictionary thay hàm sumif có bạn đã giúp với công thức sau:
Sub Thong_ke()
Dim i As Long, k As Long, DCuoi As Long, J As Long
Dim Arr_N(), Arr_D(), Dic As Object
DCuoi = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Arr_N = Sheet1.Range("A5:W" & DCuoi)
ReDim Arr_D(1 To UBound(Arr_N, 1), 1 To 8)
Set Dic = CreateObject("Scripting.Dictionary")
k = 0
For i = 1 To UBound(Arr_N, 1)
If Not Dic.Exists(Arr_N(i, 6)) Then
k = k + 1
Dic.Add Arr_N(i, 6), k
Arr_D(k, 1) = k
Arr_D(k, 2) = Arr_N(i, 6)
Arr_D(k, 3) = Arr_N(i, 7)
Arr_D(k, 4) = Arr_N(i, 9)
Arr_D(k, 5) = Arr_N(i, 19) * 24
Arr_D(k, 6) = Arr_N(i, 22)
Arr_D(k, 7) = Arr_N(i, 23)
Arr_D(k, 8) = Arr_N(i, 14)
Else
J = Dic.Item(Arr_N(i, 6))
Arr_D(J, 5) = Arr_D(J, 5) + Arr_N(i, 19) * 24
Arr_D(J, 6) = Arr_D(J, 6) + Arr_N(i, 22)
Arr_D(J, 7) = Arr_D(J, 7) + Arr_N(i, 23)
Arr_D(J, 8) = Arr_D(J, 8) + Arr_N(i, 14)
End If
Next
Sheet8.Range("I6:I1000").NumberFormat = "#,##0.00"
Sheet8.Range("E6:L50000").ClearContents
Sheet8.Range("E6").Resize(k, 8) = Arr_D

End Sub

phihndhsp · 3/5/23

Trần Văn Bình đã viết:
Cám ơn các bạn đã giúp mình mới học code nên chưa hiểu được nhiều đặc biện cám ơn @Hoàng Nhật Phương đã giúp code đúng ý mình và công thưc chính xác.
Cùng là Dictionary thay hàm sumif có bạn đã giúp với công thức sau:
Sub Thong_ke()
Dim i As Long, k As Long, DCuoi As Long, J As Long
Dim Arr_N(), Arr_D(), Dic As Object
DCuoi = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Arr_N = Sheet1.Range("A5:W" & DCuoi)
ReDim Arr_D(1 To UBound(Arr_N, 1), 1 To 8)
Set Dic = CreateObject("Scripting.Dictionary")
k = 0
For i = 1 To UBound(Arr_N, 1)
If Not Dic.Exists(Arr_N(i, 6)) Then
k = k + 1
Dic.Add Arr_N(i, 6), k
Arr_D(k, 1) = k
Arr_D(k, 2) = Arr_N(i, 6)
Arr_D(k, 3) = Arr_N(i, 7)
Arr_D(k, 4) = Arr_N(i, 9)
Arr_D(k, 5) = Arr_N(i, 19) * 24
Arr_D(k, 6) = Arr_N(i, 22)
Arr_D(k, 7) = Arr_N(i, 23)
Arr_D(k, 8) = Arr_N(i, 14)
Else
J = Dic.Item(Arr_N(i, 6))
Arr_D(J, 5) = Arr_D(J, 5) + Arr_N(i, 19) * 24
Arr_D(J, 6) = Arr_D(J, 6) + Arr_N(i, 22)
Arr_D(J, 7) = Arr_D(J, 7) + Arr_N(i, 23)
Arr_D(J, 8) = Arr_D(J, 8) + Arr_N(i, 14)
End If
Next
Sheet8.Range("I6:I1000").NumberFormat = "#,##0.00"
Sheet8.Range("E6:L50000").ClearContents
Sheet8.Range("E6").Resize(k, 8) = Arr_D

End Sub

về vấn đề dùng Dic để thống kê, thì thống kê 1 hoặc nhiều đk điều ra kết quả đúng, nếu kết quả sai có thể là do dữ liệu không hợp lý hoặc kỹ thuật code của bạn bị vướng một vài chỗ, còn về tổng quan là dic làm được hết nha

Trần Văn Bình · 3/5/23

phihndhsp đã viết:
về vấn đề dùng Dic để thống kê, thì thống kê 1 hoặc nhiều đk điều ra kết quả đúng, nếu kết quả sai có thể là do dữ liệu không hợp lý hoặc kỹ thuật code của bạn bị vướng một vài chỗ, còn về tổng quan là dic làm được hết nha

Cám ơn tất cả các anh chị đã nhiệt tình giúp

VetMini · 4/5/23

phihndhsp đã viết:
về vấn đề dùng Dic để thống kê, thì thống kê 1 hoặc nhiều đk điều ra kết quả đúng, nếu kết quả sai có thể là do dữ liệu không hợp lý hoặc kỹ thuật code của bạn bị vướng một vài chỗ, còn về tổng quan là dic làm được hết nha

1. dùng Dic để thống kê:
Không hẳn đúng với đường lối của dân GPE.
Ở đây, người ta thích dùng đít sần bởi lý do chính là công cụ này giúp giải được bài toán "lọc duy nhất" một cách dễ dàng.
Từ tính chất "lọc duy nhất", người ta diễn thêm ra thuật toán "tổng theo mục".
Và từ đó, cứ lối mòn cũ bước theo. Bao nhiêu năm không thấy có thêm thắt cải tiến gì hay ho.
Trên thực tế, muốn thống kê thì dùng ADODB (SQL) nhiều linh động hơn.

2. tổng quan là dic làm được hết nha:
Vì ý nghĩ "tổng quan làm được" như vậy cho nên người ta cứ nằm trong cái ổ cũ rích ấy, không cần biết đến các công cụ mà MS ra thêm để giúp việc "thống kê" dễ dàng và hiệu quả. Điển hình Power Query ra đã mấy năm rồi mà dân GPE cứ tảng lờ.

Chung lại: lỗi tại dân GPE khoái khoe tài code VBA của mình cho nên những người hỏi bài cứ làm nũng.
TỘI GÌ PHẢI BỎ CÔNG SỨC HỌC CÁC CÔNG CỤ MỚI TRONG KHI LÊN GPE LÀ CÓ NGAY CODE "BẤM MỘT PHÁT"?

TongDe · 4/5/23

VetMini đã viết:
Trên thực tế, muốn thống kê thì dùng ADODB (SQL) nhiều linh động hơn.

Thử theo hướng náy và chủ thớt kiểm tra lại nha !

Trần Văn Bình · 4/5/23

TongDe đã viết:
Thử theo hướng náy và chủ thớt kiểm tra lại nha !

Cám ơn bạn nhiều mình chỉ cần thếm code sum(f19)*24 là ra kết quả ok. Code của bạn viết rất gon !

VetMini · 4/5/23

Từ đầu bị cái vỏ hào nhoáng của đít sần nên cứ cắm cúi theo, không suy nghĩ.
SQL mới là ngôn ngữ làm với nhóm, tổng,... ADO chỉ là công cụ giúp sử dụng SQL trên file Excel.

Chú thích: có ai hững muốn thử Power Query hôn?
Power Query đi theo Excel. Sử dụng được trên nền tảng khác (như MacOS)
Dictionary là công cụ của Script Engine trên Wimdows. ADO là OLE trên Windows.
Code Dic và ADO đem qua nền tảng khác phải sửa đổi rất nhiều. Code Power Query không bị giới hạn nền tảng, và không bị phải lưu file với macro.

Sonkull · 4/5/23

Mấy cái thống kê này Dir điếc gì cho nó rắc rối chứ SQL với Power Query cho nó nhàn đầu update lên ACE à. Sau biết thêm SQL vs Power Query VBA lâu lâu ms động đến

Maika8008 · 4/5/23

Sonkull đã viết:
Mấy cái thống kê này Dir điếc gì cho nó rắc rối chứ SQL với Power Query cho nó nhàn đầu update lên ACE à. Sau biết thêm SQL vs Power Query VBA lâu lâu ms động đến

VBA thì có người làm giùm chứ PQry phải tự học, tự làm. Chả dại --=0

Nhờ anh chị giúp Dictionary thay hàm Sumif (3 người xem)

Người dùng đang xem chủ đề này

GTVT

File đính kèm

Đăng ký hôm qua

GTVT

Ăn cùng góc phố

＿/＿/＿/＿/＿/＿/＿/

GTVT

＿/＿/＿/＿/＿/＿/＿/

Ăn cùng góc phố

GTVT

Thành viên gắn bó

Ăn cùng góc phố

GTVT

Thành viên gạo cội

GTVT

Ăn cùng góc phố

Thành viên tiêu biểu

File đính kèm

GTVT

Ăn cùng góc phố

Thành viên mới

Thành viên gạo cội

Đếm ngược thời gian