Nhờ anh chị giúp Dictionary thay hàm Sumif

10 Responses

1. hands says:

   05/05/2023 at 6:40 pm

   Bạn tham khảo . . :

   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 :

   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

   Reply
2. hands says:

   05/05/2023 at 7:26 pm

   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"?

   Reply
3. hands says:

   05/05/2023 at 7:26 pm

   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 !

   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 !

   —–

   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.

   Reply
4. hands says:

   05/05/2023 at 7:27 pm

   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

   Nói chứ VBA giờ không nên đầu tư nhiều biết cũng tốt chứ Power Query, SQL, Python nó đơn giản và dễ viết hơn nhiều. VBA đã tồn tại quá lâu nên mọi người thần thánh quá cái gì cũng lôi vào thôi.

   Reply
5. hands says:

   05/05/2023 at 7:27 pm

   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

   Bạn nói vậy thì cũng hơi quá, mỗi cái nó có 1 nhiệm vụ riêng, nếu biết áp dụng và áp dụng đúng chỗ thì bạn sẽ thấy nó ổn, còn không thì cho dù nó hay như thế nào đi nữa thì bạn cũng chả thấy nó ổn, SQL là ngôn ngữ có cấu trúc mà nó cũng được những người code VBA áp dụng thông qua ADO rồi đó, còn học lập trình thì ngôn ngữ nào cũng như ngôn ngữ nào, chỉ cần bạn hiểu rõ kỹ thuật lập trình thì chuyện ngôn ngữ chỉ tầm vài tiếng là ra ngay. Nếu dữ liệu không ngay hàng thẳng lối thì bạn dùng những công cụ như bạn kể có đáp ứng được yêu cầu không? Quan trọng là mình dùng nó áp dụng cho cái gì, chứ đừng đánh đồng VBA chả là gì. Nếu VBA chả ra gì thì tại sao nó vẫn phát triển cho tới ngày này người ta vẫn sử dụng, điển hình là VBNet. Còn đã xác định học lập trình rồi thì ta cứ thử mọi thứ, thử rồi mới biến kiến thức hay dở như thế nào, không ai ngay từ đầu làm được cái hoàn hảo đâu. cũng phải học từ cái dở nhất và có thể không áp dụng, cho dù nó không áp dụng thì nó cũng giúp bạn tiến xa hơn 1 tí nữa về cách tư duy.

   … . . .
   (tự xóa phần đàu này vì người hỏi cũng tự xóa bài mình, câu trả ời trở nên rắn mất đầu.)

   Nhưng tôi khong khuyến khích cái mớ STT:
   – Thứ nhất, tại sao một bảng tổng hợp lại có số thứ tự? Số ấy dùng để làm gì? Cahr thấy lô gic nào cả.
   – SQL là ngôn ngữ chú trọng lô gic, và chuyên trên CSDL LH. CSDL LH chân chính không có khái niệm số dòng. Và vì vvaayjphair đi vòng vo tam quốc để lập số dòng.
   – Tôi tôn trọng bảng chuẩn hơn bảng hoa lá cành. Chuẩn thì không có STT.

   Chú thích:
   Thèm STT quá thì thêm dòng code này vào VBA
   Ví dụ bắt đầu từ ô Cells(dngBD, cotSTT), và kết thúc ở ô Cells(dngKT, cotSTT)
   Range(Cells(dngbd, cotSTT), Cells(dngkt, cotSTT)).Value = _
   Evaluate("ROW(" & dngbd & ":" & dngkt & ") – " & (dngbd – 1))

   Cảm ơn bác đã chỉ dẫn.
   Đúng vậy nghĩ lại cũng thấy STT không quan trọng nên không hỏi nữa nên mới xoá bài ạ, nếu cần thì có thể xử lý bằng vba cho đỡ rắc rối ạ.

   Reply
6. hands says:

   09/05/2023 at 10:16 pm

   Bạn nói vậy thì cũng hơi quá, mỗi cái nó có 1 nhiệm vụ riêng, nếu biết áp dụng và áp dụng đúng chỗ thì bạn sẽ thấy nó ổn, còn không thì cho dù nó hay như thế nào đi nữa thì bạn cũng chả thấy nó ổn,

   Người mà bạn nói kia ý chỉ muốn khoe trình độ của mình thôi. Nếu càn mách cho các thành viên khác thì bạn chỉ cần điểm thẳng vào chỗ cẩu thả sai bấy của y:
   Điểm tầm bậy là Code thớt này chả liên quan gì đến Dir

   …. Nếu VBA chả ra gì thì tại sao nó vẫn phát triển cho tới ngày này người ta vẫn sử dụng, điển hình là VBNet. …

   Chỗ này thì bạn lầm.
   1. Ngày xưa, nhiều nền tảng dùng ngôn ngữ BASIC để làm JCL (Job Command Language)
   . Bạn nào ngày xưa có chơi cái Commodore hay giaudf hơn, có cái Apple II thì biết.
   2. BASIC là sở thích của lão Bill Gates. Trước khi bán ddowcj DOS cho IBM lão ta vẫn thường vọc với Basic. Hòi còn là sinh viên cũng vậy.
   3. VBA được ra để hổ trợ sự giới hạn của Access. Sau đó nhập vào Excel và các ứng dụng Office khác. VBA chỉ được MS tiêm thuốc si-te-roi khi bên phát triển cho nó khả năng nói với các API, các OLE khác trong hệ thống.
   4. VB.NET chỉ có thuật ngữ giống VBA thôi. Trên căn bản lập trình VB.NET là loại ngôn ngữ Hướng Đối Tượng. VBA hoàn toàn thụt vòi khi đụng vào HĐT.
   5. Sau bao nhiêu năm cố gắng bảo vệ ngôn ngữ con đẻ (có bản quyền) là VBA, cuối cùng thì MS cũng không thể chống lại áp lực thị trường. Hiện nay họ đã bắt đầu cho chạy Office Script la ngôn ngữ khá giống JavaScript. (Google Script cũng căn bản là JavaScript).

   Range(Cells(dngbd, cotSTT), Cells(dngkt, cotSTT)).Value = evaluate("row(R:R)")

   là được rồi anh.

   Tôi nhớ hình như sẽ bị ra trị của dòng đầu tiên. Chưa rảnh để thử.

   Viết vầy có lẽ nhanh hơn một tẹo.

   Range(Cells(dngbd, cotSTT), Cells(dngkt, cotSTT)).Value = Evaluate("row(A1:A" & CStr(dngkt - dngbd + 1) & ")")

   Reply
7. hands says:

   12/05/2023 at 8:54 am

   Xin chào tất cả mọi người,
   Vậy trong trường hợp dữ liệu tại cột B có dòng trống xen kẽ như dạng ảnh kèm,
   OT muốn đánh STT theo cột B nếu có dòng trống thì sẽ bắt đầu lại từ 1.
   Thì với cách sử dụng "Evaluate" công thức sẽ như thế nào ạ?

   6103

   Không biết đúng ý chị không?
   Công thức tại A2:

   =IF(B2="","",IF(ISNUMBER(A1),A1+1,1))

   Cảm ơn bạn @THÓC SAMA rất nhiều.
   Nếu dùng công thức hay vòng lặp OT làm được nhưng OT đang hỏi cách sử dụng "Evaluate" như các bài trên theo cách mà bác @VetMini đã chỉ ấy ạ.

   Đã nghe lời bác ấy thì phải nghe cho trót: "tại sao một bảng tổng hợp lại có số thứ tự? Số ấy dùng để làm gì? Chả thấy lô gic nào cả."
   Tuy nhiên nếu không phải bảng tổng hợp (là 1 báo cáo hoàn chỉnh để in), và quá yêu cái valuate, thì evaluate chính cái công thức excel.

   Chào chú Mỹ,
   Ở trên con cũng có đồng ý là STT không quan trọng rồi mà.
   Nhưng đã lỡ hỏi rồi thì con hỏi luôn tham khảo để mở mang thêm mà chú.

   Yêu thì nhận là yêu chứ gì mà ngại. Với công thức của bạn Thóc_Sama =IF(B2="","",IF(ISNUMBER(A1),A1+1,1)), không dùng evaluate:

   [A2:A10] = ("=IF(RC[1]="""","""",if(isnumber(R[-1]C),R[-1]C + 1,1))")
   [A2:A10]=[A2:A10].value

   Ngày xưa mới biết code con cũng hay viết kiểu này nhưng thời buổi này con thấy quê rồi chú , đó là con không thích kiểu này nữa thôi 😀

   Có mỗi chuyện đánh số thứ tự mà hết yêu đến ngại, hết quê đến tỉnh. Mà chẳng thà quê mùa mà gọn, viết nhanh, còn hơn viết nửa tiếng mà cũng chỉ ăn được 5 ngàn đồng bạc như người khác viết 1 phút.

   Reply
8. hands says:

   12/05/2023 at 8:55 am

   Tôi viết code theo trường phái "việc nào tách riêng việc nấy". Trái với phần lớn dân ở đây theo trường phái "gom lại một chỗ càng tốt".

   Vì lý luận rằng cái phần số thứ tự là công việc hoa lá cảnh cho nên tôi tách nó riêng ra. Và ở trường hợp này, hàm Evaluate rất hiệu quả.

   Chú thích: hàm Evaluate vốn nằm trong nhóm "nguy hiểm". Vì vậy MS không để cho nó nổi bật lắm, phải mò từ Excel đời từ Macro 4. Khi sử dụng phải thử trước xem có đúng ý.
   Hàm này dùng để tính một biểu thức ở dạng chuỗi. Vì nó có thể tính dọc (hoặc ngang) tùy theo bên cần gán cho nên dùng nó để tính các biểu thức đơn giản trên bảng tính thì rất nhanh.
   Chú thích 2:
   Tính chất của Evaluate là nhận tham số theo dạng chuỗi chi nên ta có thể tạo chuỗi với đầy đủ thông số. Khác với dạng viết tắt của nó là cặp ngoặc vuông (). Tất cả thông số bên trong cặp này thì phải viết in hệt như trên bảng tính, không có biến gì cả.
   Ví dụ:
   arr = : copy value ở range từ A1 đến B5
   arr = Evaluate("A1:B5"), cũng như trên nhưng tham số là chuỗi cho nên nếu:
   a = "A"
   b = "B"
   thì ta có thể viết
   arr = Evaluate(a & "1" & ":" & b & "5")

   Lưu ý: đây tôi chỉ dùng ví dụ giản dị. Trên thực tế với ví dụ này thì dùng thẳng thuộc tính Range nhanh hơn nhiều.

   Reply
9. hands says:

   12/05/2023 at 8:56 am

   Tại sao không dùng vòng lặp For Next cho đơn giản??? Tại sao cứ phải Evaluate???

   Chào anh Quang Hải
   Dạ, vì for next dài dòng:

   Dim i As Long
   Dim lastRow As Long
   lastRow = wsResult.Cells(wsResult.Rows.Count, 2).End(xlUp).Row
   
   Dim lastNumber As Long
   lastNumber = 0
   
   For i = 2 To lastRow
       If IsEmpty(wsResult.Cells(i, 2)) Then
           wsResult.Cells(i, 1).Value = ""
           lastNumber = 0
       Else
           lastNumber = lastNumber + 1
           wsResult.Cells(i, 21).Value = lastNumber
       End If
   Next i

   còn Evaluate thì là OT thấy gọn và lạ nên muốn tham khảo xem với dữ liệu xen kẽ dạng này thì xử lý thế nào thôi ạ nếu hay thì với OT là mốt mới :D.

   Theo mình thì:
   – Nếu chỉ đơn giản đánh số thứ tự thì dùng công thức cho gọn
   – Nếu xử lý gì đó thì chắc sẽ có dùng vòng lặp, lúc đó kết hợp đánh stt luôn.

   Tại vì cách ấy là chính các tay chuyên trong nghề VBA đưa ra.
   Họ lý luận rằng nó nhanh và gọn hơn vòng lặp nhiều.
   Nhanh thì đối vớitôi không quan tronbgj lắm. Nhưng gọn là tôi khoái. Ứng dụng tôi đưa ở chỗ đánh STT là rất gọn, và đáp ứng tôn chỉ "việc nào ra việc ấy" của tôi (không nhét chung với code truy xuất dữ liệu)

   Reply
10. hands says:

    12/05/2023 at 8:57 am

    Đi ngang qua thấy vui quá nên góp vui chút nha…

    Reply