Tổng hợp dữ liệu bán hàng dùng Dictionary ?

[thanhphuongvip]

Chào anh chị!
Đầu tiên em có bảng tổng hợp các hóa đơn bán hàng như thế này (sheet THHD):



Em muốn làm bảng tổng hợp để biết trong khoảng thời gian nào đó bán được bao nhiêu mặt hàng, và tổng số lượng, Chiết khấu, Thành Tiền, Giá Vốn, Lợi nhuận tương tứng là bao nhiêu, có lọc theo tên Khách Hàng). Nên em tạo ra một sheet như thế này:



e có ghi chú cách tính của từng cột, lấy từ sheet THHD, phần tiền vốn là lấy số lượng * đơn giá vốn ở sheet MAHANG.



Nhờ anh chị giúp em làm tổng hợp này sử dụng Dictionary . Có thể có nhiều cách để làm ra yêu cầu này, nhưng em đang học để hiểu thêm về dictionary nên anh chị giúp em sử dụng dic càng tốt. Còn không có thì em vẫn muốn học hỏi thêm những cách khác ạ!

Xin cảm ơn anh chị trên GPE!

 

[thanhphuongvip]

Cảm ơn bạn appl nhé, rất vui được làm quen, ko biết mình có quen nhau ở ngoài đời ko. Mình dạy Excel, nhưng tới giờ mới bổ túc VBA, có gì bạn chiếu cố cho

 

[thanhphuongvip]

If Kh = "" Then
Flag = True
Else
Flag = (Kh = Arr_N(i, 20))
End If

Học được thêm chiêu này! khá hay luôn

 

[Hau151978]

Học được thêm chiêu này! khá hay luôn

Chiêu này cũng hay gần bằng này bạn
Flag=(KH="") or (KH=arr_n(i,20))

 

[Hau151978]

Vì bạn đang tìm hiểu về dic nên mình nêu hướng làm. Bạn nạp bảng dữ liệu vào mảng. Khai báo mảng kết quả, dic và một biến k để đếm số key đã nạp vào. Duyệt mảng nguồn từ trên xuống nếu không thỏa mãn điều kiện về ngày và người bán thì next; còn lại nếu gặp mã hàng mới thì bạn tăng biến đếm k thêm 1, gán key= mã hàng, item=k, copy các số trong mảng dữ liệu sang mảng kết quả; nếu gặp mã cũ thì cộng bảng dữ liệu ở dòng hiện tại vào dòng có chỉ số = item(mã hàng) ở mảng kết quả.

 

[phihndhsp]

Chiêu này cũng hay gần bằng này bạn
Flag=(KH="") or (KH=arr_n(i,20))
Vì bạn đang tìm hiểu về dic nên mình nêu hướng làm. Bạn nạp bảng dữ liệu vào mảng. Khai báo mảng kết quả, dic và một biến k để đếm số key đã nạp vào. Duyệt mảng nguồn từ trên xuống nếu không thỏa mãn điều kiện về ngày và người bán thì next; còn lại nếu gặp mã hàng mới thì bạn tăng biến đếm k thêm 1, gán key= mã hàng, item=k, copy các số trong mảng dữ liệu sang mảng kết quả; nếu gặp mã cũ thì cộng bảng dữ liệu ở dòng hiện tại vào dòng có chỉ số = item(mã hàng) ở mảng kết quả.

Lâu lắm rồi mới thấy anh vào viết bài. Giải thích rõ ràng và cặn kẽ nữa hihi

 

[VetMini]

Lâu lắm rồi mới thấy anh vào viết bài. Giải thích rõ ràng và cặn kẽ nữa hihi

Nếu bạn dạy lập trình thì không nói làm gì.
Nếu bạn dạy Excel thì đặt trọng tâm vào Dic ở bài này là sai đường lối.

Bài này trọng tâm là bộ lọc, lọc theo tên khách hàng, và khoảng thời gian.

 

[VetMini]

Chắc do thớt đòi hỏi dic nên làm dic theo yêu cầu của thớt đó thầy

Đối với người mới biết code VBA, các cấu trúc dữ liệu (array, collection, dic,...) như những món đồ chơi thần bí, rất thu hút.
Người ta muốn "làm chủ" chúng cho nhanh. Họ quan niệm rằng:
- Làm chủ một món hàng có nghĩa là có khả năng tận dụng món hàng đó trong mọi trường hợp
Ngược lại, tôi quan niệm rằng:
- Tôi không cần làm chủ món hàng. Tôi cần khả năng làm chủ tình thế.

 

[Hoàng Nhật Phương]

View attachment 235955
Hình như bạn là giám đốc trung tâm tin học đông phương phải không? trở thành chuyên gia excel trong 8h thì hơi căng đó à nha
giúp cho bạn một vé để tự tin thành cao thủ trong lĩnh vực excel, muốn dic thì có dic, bạn tự ráp vào cho quen nha

Option Compare Text
Sub Dotim(Dic As Object)
Dim Dcuoi As Long
Dim i As Long
Dim Arr_N()
Dcuoi = ShMaHang.Range("D100000").End(xlUp).Row
Set Dic = CreateObject("scripting.dictionary")
Arr_N = ShMaHang.Range("D9:H" & Dcuoi)
For i = 1 To UBound(Arr_N, 1)
    If Not Dic.exists(Arr_N(i, 2)) Then
        Dic.Add Arr_N(i, 2), Arr_N(i, 5)
    End If
Next
End Sub
Sub Main()
Dim Dic_Dotim As Object
Dim Dic As Object
Dim Tungay As Long
Dim Denngay As Long
Dim Kh As String
Dim Flag As Boolean

Dim i As Long, j As Long, k As Long
Dim Dcuoi As Long
Dim Arr_N(), Arr_D()
Call Dotim(Dic_Dotim)
Dcuoi = ShTHHD.Range("D1000000").End(xlUp).Row
Arr_N = ShTHHD.Range("D10:W" & Dcuoi)
Tungay = ShTHHD1.Range("D2")
Denngay = ShTHHD1.Range("D3")
Kh = ShTHHD1.Range("H3")

ReDim Arr_D(1 To UBound(Arr_N, 1), 1 To 20)
Set Dic = CreateObject("scripting.dictionary")
k = 0
For i = 1 To UBound(Arr_N, 1)
   If Kh = "" Then
      Flag = True
   Else
       Flag = (Kh = Arr_N(i, 20))
   End If

  If Int(Arr_N(i, 2)) >= Int(Tungay) And Int(Arr_N(i, 2)) <= Int(Denngay) And Flag Then
        If Not Dic.exists(Arr_N(i, 5)) Then
            k = k + 1
            Dic.Add Arr_N(i, 5), k
            Arr_D(k, 1) = k
            Arr_D(k, 2) = Arr_N(i, 5)
            Arr_D(k, 3) = Arr_N(i, 6)
            Arr_D(k, 4) = Arr_N(i, 7)
            Arr_D(k, 5) = Arr_N(i, 10)
            Arr_D(k, 6) = Arr_N(i, 11)
            If Dic_Dotim.exists(Arr_N(i, 5)) Then
                  Arr_D(k, 7) = Dic_Dotim.Item(Arr_N(i, 5))
            End If
        Else
            j = Dic.Item(Arr_N(i, 5))
            Arr_D(j, 4) = Arr_D(j, 4) + Arr_N(i, 7)
            Arr_D(j, 5) = Arr_D(j, 5) + Arr_N(i, 10)
            Arr_D(j, 6) = Arr_D(j, 6) + Arr_N(i, 11)
        End If
    End If

Next
For i = 1 To k
    Arr_D(i, 7) = Arr_D(i, 7) * Arr_D(i, 4)
    Arr_D(i, 8) = Arr_D(i, 6) - Arr_D(i, 7)
Next

ShTHHD1.Range("C10:J100000").ClearContents
If k = 0 Then Exit Sub
ShTHHD1.Range("C10").Resize(k, 8) = Arr_D

End Sub

Xin chào appl,
Bạn có thể ráp hết vào chung một sub để những người dốt lâu như OT có thể vỡ ra được một chút ji đó có được không?

 

[Hau151978]

Nếu bạn dạy lập trình thì không nói làm gì.
Nếu bạn dạy Excel thì đặt trọng tâm vào Dic ở bài này là sai đường lối.

Bài này trọng tâm là bộ lọc, lọc theo tên khách hàng, và khoảng thời gian.

Bài này có tính tổng số lượng theo từng mã hàng thì dic là hợp lý mà bác. Chỉ trích lọc bằng advanced filter hoặc autofilter chưa đủ.

 

[VetMini]

Bài này có tính tổng số lượng theo từng mã hàng thì dic là hợp lý mà bác. Chỉ trích lọc bằng advanced filter hoặc autofilter chưa đủ.

Tôi chỉ nói cái trọng điểm, cái bước đầu tiên cần phân tích. Cái Dic, nếu dùng, là bước thứ hai.

Theo luật hạ tầng cơ sở dữ liệu, dòng 2:3 ($D$2 & $D$3, và $H$3) là tổng hợp trên cùng, từ dòng 10 trở xuống là tổng hợp chi tiết bậc 1.
Dân chuyên nghiệp sẽ thiết kế bảng này có các chỗ chọn ngày và tên khách hàng là Slicers hoặc dropdown List. Cái được gọi là "Tổng Hợp Hoá Đơn" đối với tôi nó là bảng phát sinh (transsaction file/table)

Có lẽ ở đây, người ta nói chuyện "chuyên gia" là người code giỏi và công thức khủng. Tôi thì chỉ biết nói chuyện "chuyên nghiệp", thiết kế bảng tính dễ nhìn, dễ dùng.

 

[Hau151978]

Vâng đúng rồi bác, cái này lại thuộc về công việc của thầy dạy excel rồi. Thiết kế dashboard có mấy cái slicer và date picker nhìn đẹp hẳn.

 

[Hoàng Nhật Phương]

Hic, OT đang học Dictionary nên tưởng bài này ngon ăn do đó muốn code thử bài này quá ạ và đã code và khai báo các kiểu nhưng thuật toán và cú pháp chưa hiểu nên dừng lại.
Nhờ các Bác/Thầy/cô/Anh/Chị em tiếp sức ạ:

Function Tim_LastRow(Sh As Worksheet, nCol As String) As Long
    Tim_LastRow = Sh.Cells(Sh.Rows.count, nCol).End(xlUp).Row
End Function

Sub TongHop_NXT()

    Dim DicMaHang As Scripting.Dictionary
    Set DicMaHang = New Scripting.Dictionary
   
    Dim DicBanHang As Scripting.Dictionary
    Set DicBanHang = New Scripting.Dictionary
   
'    Dim Dic As Object
'    Set Dic = CreateObject("Scripting.Dictionary")

    Dim Key As String, iKey As Variant
   
    Dim Bao_Cao As Variant, Tong_Hop As Variant, Ma_Hang As Variant
    Dim Sh As Worksheet, wsName As String, count As Long
    Dim dkLoc As String, NgayBatDau As Long, NgayKetThuc As Long
    Dim i As Long, j As Long, k As Long, r As Long
    Dim Ngay As Long, KhachHang As String, MaHang As String
   
    Const MaHang As String = "MAHANG"
    Const DULIEU As String = "THHD"
    Const BAOCAO As String = "BaoCaoXuatKho"
   
    '--------------------------->   MA_HANG
    wsName = MaHang
    Set Sh = ThisWorkbook.Worksheets(wsName)
    r = Tim_LastRow(Sh, "D")
    If r < 9 Then
        MsgBox "Khong co danh sach hang hoa!", vbCritical + vbOKOnly, "Thong bao"
        Exit Sub
    End If
    Ma_Hang = Sh.Range("D9:D" & r).Resize(, 5).Value
    For i = 1 To UBound(Ma_Hang)
        Key = Ma_Hang(i, 1): iKey = Ma_Hang(i, 2)
        If Key <> Empty Then If DicMaHang.Exists(Key) = False Then DicMaHang.Add Key, iKey
    Next i
   
    '--------------------------->   DU_LIEU
    wsName = DULIEU
    Set Sh = ThisWorkbook.Worksheets(wsName)
    r = Tim_LastRow(Sh, "G")
    If r < 10 Then
        MsgBox "Khong du lieu don hang!", vbCritical + vbOKOnly, "Thong bao"
        Exit Sub
    End If
    Tong_Hop = Sh.Range("B3:Z" & r).Value
    r = UBound(Tong_Hop)
    ReDim Bao_Cao(1 To r, 1 To 8)
   
    '--------------------------->   BAO_CAO
    wsName = BAOCAO
    Set Sh = ThisWorkbook.Worksheets(wsName)
    dkLoc = Sh.Range("H3").Value
    NgayBatDau = Sh.Range("D2").Value
    NgayKetThuc = Sh.Range("D3").Value
   
    For i = 1 To r
        Ngay = Tong_Hop(i, 2)
        KhachHang = Tong_Hop(i, 20)
        MaHang = Tong_Hop(i, 4)
        If (Ngay >= NgayBatDau) And (Ngay <= NgayKetThuc) Then
            If dkLoc = KhachHang Then
                If DicBanHang.Exists(Ma_Hang) = False Then
                   
                    DicBanHang.Add KhachHang
                    count = DicBanHang.count
                    Bao_Cao(count, 1) = count
                   
                    'KHÔNG THỂ CHỊU NỔI NỮA RỒI!!!!!!!!!!!!!!!!!!!!!!!!
                   
                End If
            End If
        End If
    Next r
   
   
End Sub

 

[SA_DQ]

Mình xin mạnh dạng góp vài ý từ nhỏ nhất đến chủ bài đăng về thiết kế các trang dữ liệu của bạn.

1. Tên trang tính thay vì 'MAHANG' ta nên là 'MaHang'; Thay vì 'BaoCaoXuatKho', ta nên chỉ là 'BCXuatKho' hay 'XuatKho' là đủ dùng rồi
2. (Ở trang tính 'MaHang') Cột 'D' đang ghi các mã hàng; Trong Excel ta nên chọn mã đầu là 1000 thay vì 0001 để khỏi lôi thôi về sau với mấy con số 0 tròn trịa kia.
3. (Trang 'THHD') Cột [Ngày] ta nên tách ra làm 2; đó là ngày riêng & giờ riêng; Nhốt chung như bạn tiện nhưng không lợi trong 1 bảng tính Excel, là nơi chuyên để tính toán
4. (Cũng trang này) Để tránh nhập trùng lắp như bản trích :

KHÁCH HÀNG	Điện thoại	Địa chỉ	Ghi chú
A Lâm Làng Dừa	034852	17 Trần Hưng Đạo P4, Quân 5
A Lâm Làng Dừa	034852	17 Trần Hưng Đạo P4, Quân 5
A Lâm Làng Dừa	034852	17 Trần Hưng Đạo P4, Quân 5
A Lâm Làng Dừa	034852	17 Trần Hưng Đạo P4, Quân 5
A Lâm Làng Dừa	034852	17 Trần Hưng Đạo P4, Quân 5
Anh Hưng Tân	0987272231	18 Trần Hưng Đạo P4, Quân 5
Anh Hưng Tân	0987272231	18 Trần Hưng Đạo P4, Quân 5
Anh Hưng Tân	0987272231	18 Trần Hưng Đạo P4, Quân 5
Anh Hưng Tân	0987272231	18 Trần Hưng Đạo P4, Quân 5

Ta nên có trang (hay bảng) 'DMKH' (Danh mục khách hàng) với các trường như sau:
([STT]) [Mã KH], [Tên KH], [SDT], [DiaChi],. . . ,[Ghi chú]
Nếu ta có bản danh mục này thì 4 cột mà mình trích dẫn trên sẽ gom lại thành 1 cột [Mã KH] mà thôi

Bạn tham khảo cách gán mã KH của mình như dưới đây:

Mã KH	KHÁCH HÀNG
DHT00	Dương Hưng Tân
DHT01	Dương Đỗ Hưng Tân
DJH00	Dượng Hiếu
DNH00	Dượng Nghĩa Hiếu
DNH01	Dư Nghĩa Hiếu
DNH02	Dương Đỗ Nghĩa Hiếu
FHT00	Đỗ Hưng Tân
FNH00	Đặng Dương Nghĩa Hiếu
LLD00	Lâm Làng Dừa
LLD01	Lữ Luân Dìa
LLD02	Lộc Thị Lê Dung
LLD03	Lý Lâm Dân
LLD04	Lã Lý Danh
PJT00	Phan Tân

Tạm thời chỉ là vậy & rất vui nếu được trao đổi tiếp cùng bạn.


,

 

[Quang_Hải]

Hic, OT đang học Dictionary nên tưởng bài này ngon ăn do đó muốn code thử bài này quá ạ và đã code và khai báo các kiểu nhưng thuật toán và cú pháp chưa hiểu nên dừng lại.
Nhờ các Bác/Thầy/cô/Anh/Chị em tiếp sức ạ:

Function Tim_LastRow(Sh As Worksheet, nCol As String) As Long
   .........  
End Sub

Sao bạn viết phức tạp quá vậy, nhìn muốn bỏ học dictionary luôn á
Mình xem mấy bài tập mẫu về dictionary thì thấy mọi người hay viết thế này
if not dic.exists(key) then
k=k+1
Dic.add key, k
........
else
x= dic.item(key)
.......
end if

 

[HieuCD]

Chiêu này cũng hay gần bằng này bạn
Flag=(KH="") or (KH=arr_n(i,20))

Khó hiểu hơn
Flag=(KH<>"") = (KH=arr_n(i,20))

 

[Hoàng Nhật Phương]

Sao bạn viết phức tạp quá vậy, nhìn muốn bỏ học dictionary luôn á
Mình xem mấy bài tập mẫu về dictionary thì thấy mọi người hay viết thế này
if not dic.exists(key) then
k=k+1
Dic.add key, k
........
else
x= dic.item(key)
.......
end if

Xin chào anh quanghai1969, cảm ơn anh đã chỉ dẫn cho OT.
Bài này là phải sử dụng 2 Dic phải không anh, T_T.

Bài đã được tự động gộp: 20/4/20

Khó hiểu hơn
Flag=(KH<>"") = (KH=arr_n(i,20))

Bác ơi, Bác làm thử bài này theo cách của Bác đi ạ

 

[HieuCD]

Xin chào anh quanghai1969, cảm ơn anh đã chỉ dẫn cho OT.
Bài này là phải sử dụng 2 Dic phải không anh, T_T.

Bài đã được tự động gộp: 20/4/20

Bác ơi, Bác làm thử bài này theo cách của Bác đi ạ

Đang họp, khi rảnh sẽ xem thử

 

[Quang_Hải]

Xin chào anh quanghai1969, cảm ơn anh đã chỉ dẫn cho OT.
Bài này là phải sử dụng 2 Dic phải không anh, T_T.

Dùng 2 dic hoặc 1 dic + phương thức Find để xác định giá vốn đều giải được bài toán. Bài này chỉ thuộc dạng cơ bản, tuy nhiên do cách bố trí dữ liệu nên code hơi lằng nhằng chút.
.....
Gởi chủ topic:

Ngay từ việc đặt mã hàng (item code) là đã không nên rồi. Ít nhất phải có 1 ký tự TEXT rồi mới tới dãy số. Ví dụ như A0001

Vài lời chia sẻ

 

[Hoàng Nhật Phương]

Dùng 2 dic hoặc 1 dic + phương thức Find để xác định giá vốn đều giải được bài toán. Bài này chỉ thuộc dạng cơ bản, tuy nhiên do cách bố trí dữ liệu nên code hơi lằng nhằng chút.
.....
Gởi chủ topic:

Ngay từ việc đặt mã hàng (item code) là đã không nên rồi. Ít nhất phải có 1 ký tự TEXT rồi mới tới dãy số. Ví dụ như A0001

Vài lời chia sẻ

Xin chào anh quanghai1969,
Nếu Anh có thời gian & hứng thú Anh code thử giúp OT bài này với cách làm "1 dic + phương thức Find" với Anh ?
Bài này OT thấy ứng dụng khá nhiều trong thực tế, và thường xuyên sử dụng hàm tìm kiếm và sumifs để xử lý ạ.
OT Cảm ơn Anh.