Qua sự giúp đỡ của các anh chị trên diễn đàn về các công thức thống kê như countif, countifs, các hàm dạng mảng như sumproduct, FREQUENCY... em đã có thể áp dụng để thực hiện công việc của mình. Tuy nhiên, khi áp dụng với số lượng dữ liệu lớn, file excel chạy rất chậm, thậm chí còn bị treo. Vì vậy, em gửi file lên đây nhờ anh, chị xử lý bằng VBA giúp em với ạ. Trong file Thong ke, cần thống kê theo điều kiện tại từng cột căn cứ vào dữ liệu ở file Data. Rất mong các anh, chị giúp đỡ. Em xin cảm ơn nhiều ạ.
Nhờ viết code VBA thay thế các hàm thống kê để xử lý bảng tính được nhanh hơn (1 người xem)
- Thread starter Tuan_hcth
- Ngày gửi
Người dùng đang xem chủ đề này
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,964
Mấy vụ thống kê này thì PivoTable mới là trùm nhất chứ không phải công thức hay VBA gì cả
Upvote
0
VetMini
Đang đi tìm hòn đá
- Tham gia
- 21/12/12
- Bài viết
- 17,859
- Được thích
- 24,742
- Nghề nghiệp
- Thầy bói bài ta
Nếu đã muốn dùng số lượng lớn thì chịu khó chuyển qua Excel 2016 hoặc 365. Các phiên bản này sẽ có các cách quản lý số lượng lớn.
Còn lẩn quẩn ở loại file xls thì dẫu có VBA cũng chỉ là gải pháp tạm thời.
Upvote
0
be_09
Biên Hòa, Đồng Nai
- Tham gia
- 9/4/11
- Bài viết
- 9,972
- Được thích
- 9,882
- Nghề nghiệp
- Công chức
Bài 2 nêu rõ rồi, dùng PivotTable là được kết quả mà chẳng cần COUNTIF, SUMIF ...v..v...gì ráo.
Bạn chịu khó tìm hiểu thêm về PivotTable thì sẽ nhớ dai và sẽ tự khắc ứng biến được khi cần, nó là 1 công cụ hoàn hảo nhất về tổng hợp, báo cáo, thống kê, kiểm kê.
Upvote
0
VetMini
Đang đi tìm hòn đá
- Tham gia
- 21/12/12
- Bài viết
- 17,859
- Được thích
- 24,742
- Nghề nghiệp
- Thầy bói bài ta
Hồi nào đến giờ tôi dùng thì không thấy vậy. Power Pivot dùng Data Model làm việc với gần triệu dòng dữ liệu.
Dĩ nhiên, tôi là dân chuyên nghiệp. Cái bảng tính của tôi nó được thiết kế cặn kẽ trước khi bắt đầu tính toán. Tôi chỉ làm việc với loại bảng tính không thiết kế với điều kiện lấy dữ liệu 1 lần rồi bỏ (trường hợp không xứng đáng bỏ công thiết kế)
Dĩ nhiên, tôi là dân chuyên nghiệp. Cái bảng tính của tôi nó được thiết kế cặn kẽ trước khi bắt đầu tính toán. Tôi chỉ làm việc với loại bảng tính không thiết kế với điều kiện lấy dữ liệu 1 lần rồi bỏ (trường hợp không xứng đáng bỏ công thiết kế)
Upvote
0
Bạn xem đã đủ chưa
Mã:
Sub Tonghop()
Dim Solieu As Variant
Dim DK1(1 To 3) As Variant
Dim DK2(1 To 2) As Variant
Dim GHP As String
Dim MNG, GH
Dim KQ As Variant
Dim i As Long
Dim DicTH As Object
Set DicTH = CreateObject("Scripting.Dictionary")
Solieu = Sheet1.Range("a4", Sheet1.Range("d4").End(xlDown))
GH = 16 / 8 / 2018
For i = 1 To UBound(Solieu)
If Solieu(i, 4) >= GH Then
DK1(1) = Solieu(i, 2)
DK1(2) = Solieu(i, 3)
DK1(3) = Solieu(i, 1)
DK2(1) = Solieu(i, 2)
DK2(2) = Solieu(i, 3)
GHP = Join(DK1)
If DicTH.Exists(GHP) = False Then
DicTH(GHP) = Array(DK1, 1)
Else
MNG = DicTH(GHP)
MNG(1) = MNG(1) + 1
DicTH(GHP) = MNG
End If
GHP = Join(DK2)
DicTH(GHP) = DicTH(GHP) + 1
End If
Next i
ReDim KQ(1 To 8, 1 To 9)
KQ(1, 1) = "Don vi": KQ(1, 2) = "$": KQ(1, 3) = "@": KQ(1, 4) = "Nhan vien ban mat hang $ > 5"
KQ(1, 5) = "Nhan vien ban mat hang @ > 5": KQ(1, 6) = "Nhan vien ban mat hang $ < 3"
KQ(1, 7) = "Nhan vien ban mat hang @ < 3": KQ(1, 8) = "Nhan vien ban mat hang $ 3 <= SL <= 5"
KQ(1, 9) = "Nhan vien ban mat hang @ 3 <= Sl <= 5"
For i = 2 To 8
KQ(i, 1) = i - 1
Next i
For i = 2 To UBound(KQ)
GHP = KQ(i, 1) & " " & "$"
KQ(i, 2) = DicTH(GHP)
DicTH.Remove GHP
GHP = KQ(i, 1) & " " & "@"
KQ(i, 3) = DicTH(GHP)
DicTH.Remove GHP
Next i
For i = 2 To UBound(KQ)
For Each MNG In DicTH.Keys
If DicTH(MNG)(0)(1) = KQ(i, 1) Then
If DicTH(MNG)(0)(2) = "$" Then
If DicTH(MNG)(1) > 5 Then
KQ(i, 4) = KQ(i, 4) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
Else
If DicTH(MNG)(1) < 3 Then
KQ(i, 6) = KQ(i, 6) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
Else
KQ(i, 8) = KQ(i, 8) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
End If
End If
Else
If DicTH(MNG)(1) > 5 Then
KQ(i, 5) = KQ(i, 5) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
Else
If DicTH(MNG)(1) < 3 Then
KQ(i, 7) = KQ(i, 7) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
Else
KQ(i, 9) = KQ(i, 9) & " " & DicTH(MNG)(0)(3)
DicTH.Remove MNG
End If
End If
End If
End If
Next MNG
Next i
With Sheet2
.UsedRange.ClearContents
.Range("a3").Resize(UBound(KQ), UBound(KQ, 2)) = KQ
.Range("a3").Resize(UBound(KQ), UBound(KQ, 2)).Borders.LineStyle = 1
.UsedRange.Columns.AutoFit
End With
End Sub
Upvote
0
Cảm ơn bác đã quan tâm. Có lẽ em viết đề bài không rõ nên bác đang hiểu sai ý em. Ý em là thống kê số lần xuất hiện của nhân viên thỏa mãn theo các điều kiện (mã hàng, đơn vị, ngày tháng), chứ không phải là liệt kê tên nhân viên viên thỏa mãn các điều kiện đó. Rất mong bác xem và sửa code lại giúp em ạ. Thanks bác.
Upvote
0
Bác có thể hoàn thiện nốt các cột tiếp theo giúp em được không ạ. Và em thấy trong code bác có sử dụng hàm Dcounta, việc này khi chạy với dữ liệu lớn có làm chậm việc tính toán không ạ.
Upvote
0
Nếu có nhiều nhân viên đạt yêu cầu thì sẽ hiển thị ra thế nào?
Bạn ví dụ thử 1 file mẫu lên xem rồi chỉnh code một thể.
Upvote
0
Nếu có một nhân viên thỏa mãn điều kiện thì được tính là 1 (hiện thị là 1), 2 nhân viên thỏa mãn thì được tính là 2 ạ...
Upvote
0
Có cần hiển thị tên nhân viên hay không bạn?
Upvote
0
Không cần bác ạ
Upvote
0
- Tham gia
- 8/6/06
- Bài viết
- 14,678
- Được thích
- 23,024
- Nghề nghiệp
- U80
(1) Mình cũng chưa thật hiểu số liệu 2 cột tiếp sẽ là bao nhiêu, nên chưa dám làm;
Như mình đang hiểu thì có thể xài công thức cho 2 cột kế tiếp này theo 2 cột đã làm
(2) Hàm CSDL chỉ thua anh chàng Dictionary & duyệt mảng thôi đó nha!
Hàm này chấp các SUMIF() & càng chấp SUMPRODUCT() luôn!
Bạn cứ thử với dữ liệu thật của bạn; Nếu vẫn lặc lè thì ta tính tiếp, không nên đốt cháy giai đoạn trong chu trình chinh fục VBA bằng cách tự học..
Upvote
0
Bạn chạy đoạn code dưới đây.
Mã:
Sub Tonghop()
Dim Solieu As Variant
Dim DK1(1 To 3) As Variant
Dim DK2(1 To 2) As Variant
Dim GHP As String
Dim MNG, GH
Dim KQ As Variant
Dim i As Long
Dim DicTH As Object
Set DicTH = CreateObject("Scripting.Dictionary")
Solieu = Sheet1.Range("a4", Sheet1.Range("d4").End(xlDown))
GH = 16 / 8 / 2018
For i = 1 To UBound(Solieu)
If Solieu(i, 4) >= GH Then
DK1(1) = Solieu(i, 2)
DK1(2) = Solieu(i, 3)
DK1(3) = Solieu(i, 1)
DK2(1) = Solieu(i, 2)
DK2(2) = Solieu(i, 3)
GHP = Join(DK1)
If DicTH.Exists(GHP) = False Then
DicTH(GHP) = Array(DK1, 1)
Else
MNG = DicTH(GHP)
MNG(1) = MNG(1) + 1
DicTH(GHP) = MNG
End If
GHP = Join(DK2)
DicTH(GHP) = DicTH(GHP) + 1
End If
Next i
ReDim KQ(1 To 8, 1 To 9)
KQ(1, 1) = "Don vi": KQ(1, 2) = "$": KQ(1, 3) = "@": KQ(1, 4) = "Nhan vien ban mat hang $ > 5"
KQ(1, 5) = "Nhan vien ban mat hang @ > 5": KQ(1, 6) = "Nhan vien ban mat hang $ < 3"
KQ(1, 7) = "Nhan vien ban mat hang @ < 3": KQ(1, 8) = "Nhan vien ban mat hang $ 3 <= SL <= 5"
KQ(1, 9) = "Nhan vien ban mat hang @ 3 <= Sl <= 5"
For i = 2 To 8
KQ(i, 1) = i - 1
Next i
For i = 2 To UBound(KQ)
GHP = KQ(i, 1) & " " & "$"
KQ(i, 2) = DicTH(GHP)
DicTH.Remove GHP
GHP = KQ(i, 1) & " " & "@"
KQ(i, 3) = DicTH(GHP)
DicTH.Remove GHP
Next i
For i = 2 To UBound(KQ)
For Each MNG In DicTH.Keys
If DicTH(MNG)(0)(1) = KQ(i, 1) Then
If DicTH(MNG)(0)(2) = "$" Then
If DicTH(MNG)(1) > 5 Then
'KQ(i, 4) = KQ(i, 4) & " " & DicTH(MNG)(0)(3)
KQ(i, 4) = KQ(i, 4) + 1
DicTH.Remove MNG
Else
If DicTH(MNG)(1) < 3 Then
'KQ(i, 6) = KQ(i, 6) & " " & DicTH(MNG)(0)(3)
KQ(i, 6) = KQ(i, 6) + 1
DicTH.Remove MNG
Else
'KQ(i, 8) = KQ(i, 8) & " " & DicTH(MNG)(0)(3)
KQ(i, 8) = KQ(i, 8) + 1
DicTH.Remove MNG
End If
End If
Else
If DicTH(MNG)(1) > 5 Then
'KQ(i, 5) = KQ(i, 5) & " " & DicTH(MNG)(0)(3)
KQ(i, 5) = KQ(i, 5) + 1
DicTH.Remove MNG
Else
If DicTH(MNG)(1) < 3 Then
'KQ(i, 7) = KQ(i, 7) & " " & DicTH(MNG)(0)(3)
KQ(i, 7) = KQ(i, 7) + 1
DicTH.Remove MNG
Else
'KQ(i, 9) = KQ(i, 9) & " " & DicTH(MNG)(0)(3)
KQ(i, 9) = KQ(i, 9) + 1
DicTH.Remove MNG
End If
End If
End If
End If
Next MNG
Next i
With Sheet2
.UsedRange.ClearContents
.Range("a3").Resize(UBound(KQ), UBound(KQ, 2)) = KQ
.Range("a3").Resize(UBound(KQ), UBound(KQ, 2)).Borders.LineStyle = 1
.UsedRange.Columns.AutoFit
End With
End Sub
Upvote
0
Bài viết mới nhất
- Dán lên cao
- Question
