Sub DictionaryFilter()
Dim Dic As Object
Dim i As Long, lRow As Long, ArrData(), Result(), sKey As String, j As Long
Set Dic = CreateObject("Scripting.Dictionary")
With Sheet1
lRow = .Range("B" & Rows.Count).End(xlUp).Row
ArrData = .Range("B2:D" & lRow).Value2
lRow = UBound(ArrData, 1)
ReDim Result(1 To lRow, 1 To 4)
For i = 1 To lRow
sKey = ArrData(i, 1) & "|" & ArrData(i, 2)
If sKey <> "" Then
If Not Dic.Exists(sKey) Then
j = j + 1
Dic.Add sKey, j
Result(j, 1) = j
Result(j, 2) = ArrData(i, 1)
Result(j, 3) = ArrData(i, 2)
Result(j, 4) = ArrData(i, 3)
Else
Result(Dic.Item(sKey), 4) = Result(Dic.Item(sKey), 4) + ArrData(i, 3)
End If
End If
Next i
If j > 0 Then
.Range("H2").Resize(100, 4).ClearContents
.Range("H2").Resize(j, 4) = Result
End If
End With
End SubĐúng là em cần thế này rồi ạ ! Em cảm ơn anh nhiều !
Từ phiên bản excel 2016 thì PQ là tool có sẵn.
Thế thì chịu rồi. PQ là 1 tool, thêm được thì tốt. Kết hợp được cả VBA thì song kiếm hợp hợp tích càng mạnh mẽ hơn thôi.Tôi mua bản quyền 2013 cứng theo máy. Chờ cái máy hư, sắm máy khác mới đổi bộ Office được
O2013 không biết đã có power pivot chưa anh nhỉ? Nếu không có thì cũng đáng phải upgrade thật.
Vâng anh, thực tế mà có 5, 7 bảng liên kết với nhau thì làm báo cáo dùng nó cũng đỡ mệt hơn chút.
1. Bạn đang dùng bộ Office năm nào? như có trao đổi ở trên, từ phiên bản 2016 thì Power Query có sẵn, còn trở về trước cần cài thêm Addin.
Anh thấy kết quả ví dụ nó khác với câu lệnh em viết, anh tạm chỉnh như sau:
= Table.Group(#"Changed Type", {"Code"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}, {"Date", each List.Min([Date]), type nullable date}})Sub Gop_HLMT()
With CreateObject("ADODB.Connection")
.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
Sheet1.Range("M2").CopyFromRecordset .Execute("Select [Code], Min([Date]), Sum(Quantity) From [ArrayList$] where [Code] Is Not Null Group By [Code]")
End With
End SubCảm ơn anh,
Để làm thuần trong SQL phải dựa vào dữ liệu và hợi phức tạp. Có thể kết hợp với VBA để đánh số cho nó.
Vậy em bổ sung thêm thế này:
Sub Gop_HLMT()
With CreateObject("ADODB.Connection")
.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
Sheet1.Range("S2").CopyFromRecordset .Execute("Select [Code], Min([Date]), Sum(Quantity) From [ArrayList$] where [Code] Is Not Null Group By [Code]")
If Sheet1.Range("S2") <> "" Then Sheet1.Range([S2], [S65536].End(3)).Offset(, -1) = [row(a:a)]
End With
End SubỞ đây em thấy kết quả cột Date mới chỉ lấy một ngày đầu tiên. Nếu Lấy tất cả ngày và có cộng dồn nếu trùng ngày thì có làm được bằng ADO không ạ !
Nên dựa vào số dòng trong Recordset rồi gán xuống luôn em. Về tốc độ thì nhanh chậm chút ít thời gian, nhiều khi phải cho mắt nghỉ ngơi, hoặc đi uống cà phê
. Quen cái nào thì dùng cái ấy thôi em.
Code ADO bạn hỏi anh @Hai Lúa Miền Tây
Bỏ tính toán của cột Date, Group By cột này là được nhé.
Sub Gop_HLMT()
With CreateObject("ADODB.Connection")
.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
Sheet1.Range("S2").CopyFromRecordset .Execute("Select [Code], [Date], Sum(Quantity) From [ArrayList$] where [Code] Is Not Null Group By [Code],[Date]")
End With
End SubCode chạy đúng rồi ạ ! ADO đúng là thỏa đam mê vừa dùng SQL vừa dùng VBA. Hay anh ạ !
Oái, vậy là phải sửa lại code File của bài #15, bạn tự sửa và hoàn thiện nha.
Gán trực tiếp Recordset ví dụ như sau nhe em.
Sub Gop_HLMT()
With CreateObject("ADODB.Recordset")
.Open "Select [Code], [Date], Sum(Quantity) From [ArrayList$] where [Code] Is Not Null Group By [Code],[Date]", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0", 1
Sheet1.Range("L2:O100").ClearContents
Sheet1.Range("M2").CopyFromRecordset .DataSource
Sheet1.Range("L2:L" & .RecordCount + 1) = [row(a:a)]
End With
End SubHi anh @Hai Lúa Miền Tây, đoạn code này:
From [ArrayList$]Lấy hết dữ liệu có trong sheet đó em.
Trong sheet đó đang còn nhiều bảng mà anh nhỉ? e đang hiểu là nó chỉ connection đến vùng đầu tiên để tính toán.
Em tạo 1 sheet mới và chạy code sau để đổ dữ liệu vào sheet mới. Từ đó nghiệm ra nhé.
Sub Gop_HLMT()
With CreateObject("ADODB.Recordset")
.Open "Select * From [ArrayList$] ", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0", 1
Sheet2.Range("M2").CopyFromRecordset .DataSource
Sheet2.Range("L2:L" & .RecordCount + 1) = [row(a:a)]
End With
End SubEm có tạo sheet mởi, xóa vùng đầu tiên đi, thì kết quả nó lấy ở vùng tiếp theo. (Đỏ là vùng kết quả)
Anh đoán là em đang (select tencot) chứ không phải (select *)
uhm, bạn phần Groupby thì bạn add thêm trường Date vào thôi, còn đến giờ tôi cũng chưa biết là sai hay là đúng? Chưa thấy chủ thớt chốt lại kết quả như thế nào là đúng í đó.
