Tổng hợp dữ liệu bán hàng dùng Dictionary ? (1 người xem)

thanhphuongvip · 18/4/20

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 · 18/4/20

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 · 18/4/20

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 · 18/4/20

thanhphuongvip đã viết:
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 · 18/4/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ả.

phihndhsp · 19/4/20

Hau151978 đã viết:
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 · 19/4/20

phihndhsp đã viết:
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 · 19/4/20

appl đã viết:
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 · 19/4/20

appl đã viết:

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

Mã:

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 · 19/4/20

VetMini đã viết:
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 · 19/4/20

Hau151978 đã viết:
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 · 19/4/20

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 · 19/4/20

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 ạ:

Mã:

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 · 20/4/20

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 · 20/4/20

Nguyễn Hoàng Oanh Thơ đã viết:
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 ạ:

Mã:

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 · 20/4/20

Hau151978 đã viết:
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 · 20/4/20

quanghai1969 đã viết:
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

HieuCD đã viết:
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 · 20/4/20

Nguyễn Hoàng Oanh Thơ đã viết:
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 · 20/4/20

Nguyễn Hoàng Oanh Thơ đã viết:
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 · 20/4/20

quanghai1969 đã viế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ẻ

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.

VetMini · 20/4/20

Thật ra cái "BÁO CÁO XUẤT KHO" của thớt từ đầu đã là thiết kế dỏm. Nếu bỏ qua cách bình thường ngừoi ta dùng (query) mà tôi đề cập ở bài #13 thì bảng báo cáo này cũng còn nhiều sơ hở.

Bảng "TONG HOP HOA DON" cũng thiết kế dỏm. Thiếu mất cột "tiền vốn". Người bán hàng chân chính không thể mặc định rằng giá vốn không thay đổi trong suốt thời gian báo cáo. Bởi vì báo cáo có thể năm, quý, tháng , tuần, ngày,...
Trừ phi bảng "Hàng tồn kho" có chứa giá vốn theo từng thời điểm. Khi ấy có thể tra giá vốn.

Bảng "xuất hàng" chính thức còn phải có tối thiểu 1 cột nữa để ghi tổng số lần xuất hàng trong thời gian chỉ định.

Bài này muốn thử nghề đít-sần thì có thể lập 1 cái dùng key mã hàng, và item là chỉ số dòng của mảng đầu ra.
- đọc bảng phát sinh, xét đủ điều kiện ngày và khách hàng thì chép vào mảng đầu ra
Bài toán còn lại là lookup cái giá vốn từ bảng danh mục mã hàng:
- set một range để lookup (mã hàng được sắp xếp, cho nên hàm Match tương đối nhanh)
- ý kiến dùng một đít-sần khác để tra cái này là ý kiến sai. Đít-sần thì phải tra nhiều lần mới có hiệu quả. Bài này, nếu làm đúng thì mỗi mặt hàng chỉ tra một lần.
Đến đây thì lòi ra một tiểu xảo nho nhỏ để ghi giá vốn mặt hàng:
- lúc lập mảng đầu ra, lập dư một vài cột để ghi chi tiết mặt hàng sau khi tra mặt hàng.

Chú về Đít-sần:
Tuy là một công cụ dò rất hiệu quả, đít-sần có một nhược điểm là nó không biết cái gọi là thứ tự. Đối với nó, cái nào vào đầu tiên thì là số 1, kế đó là số 2...
Vì vậy, nếu dùng đít-sần để tổng hợp dữ liệu thì cũng đòng thời chấp nhận kết quả sẽ theo thứ tự của bảng chính ban đầu. Và do vậy, thường thường người ta có thêm một đoạn code sort lại theo thứ tự mã hàng hay tên gì đó.

Muốn dạy Excel thì việc đầu tiên là tìm cách làm quen với những người buôn bán thực sự và học cách họ tính toán như thế nào. Đít-sần trong giai đoạn này chỉ dùng để loè học sinh. Trước mắt, cơ quan dạy công thức càng phức tạp, VBA càng huyền bí thì càng có nghĩa là cơ quan ấy không đủ trình độ kiến thức thực tế để dạy căn bản.

Hau151978 · 20/4/20

Có thể dùng 1 dic như sau, đầu tiên duyệt mảng và nạp dic ở sheet Tổng hợp với key là mã hàng còn item là số thứ tự như mình đã nêu ở bài 4, nhập thêm mã hàng vào cột tiền vốn, chưa nhập lợi nhuận.
Vòng lặp 2 duyệt mảng mã hàng, sửa item ứng với key thành giá vốn, nếu mã hàng không có trong keys thì bỏ qua. Nên có biến đếm để kiểm tra nếu sửa đủ key rồi thì kết thúc lặp luôn (hữu ích khi có nhiều mã hàng không sử dụng).
Vòng lặp 3 duyệt mảng kết quả, thay cột tiền vốn = số lượng nhân giá vốn, giá vốn ở đây là item ứng với key là mã hàng đã lưu ở cột tiền vốn, tính toán lợi nhuận để ghi vào cột cuối.

VetMini · 20/4/20

Code (đại khái)

' a là mảng đầu vào
' b là mảng đầu ra
' mhRg Range chứa dữ liệu mã hàng
' dlHang là mảng chứa dữ liệu hàng

Redim b(1 To UBound(a), 1 To 9) ' cột thứ 9 chứa dữ liệu tính toán, sẽ không chép ra
For i = 1 To UBound(a)
ma = a(i, 1)
If Dic.Exists(ma) Then
' cọng đòn vào dòng có sẵn
CongDon b, Dic(ma), a, i
Else
' chép dòng mới vào b, và Dic
rowb = rowb + 1
Dic.Add ma, rowb ' ghi vào dic
DongMoi b, rowb, a, i, dlHang(Application.Match(ma, mhRg, 1), 5)
End If
Next i
Range(destination).Resize(rowb, 8) = b

Sub CongDon(b(), rowb As Long, a(), rowa As Long)
b(rowb, 4) = b(rowb, 4) + a(rowa, 7) ' số lượng
b(rowb, 5) = b(rowb, 5) + a(rowa, 9) ' chiét khấu
b(rowb, 6) = b(rowb, 6) + a(rowa, 11) ' thành tiền
b(rowb, 7) = b(rowb, 4) * b(rowb, 9) ' giá vốn
b(rowb, 8) = b(rowb, 6) - b(rowb, 7) ' lãi
End Sub

Sub DongMoi(b(), rowb As Long, a(), rowa As Long, giavon As Double)
' ghi vào dòng đầu ra
b(rowb, 1) = rowb ' số thứ tự
b(rowb, 2) = a(rows, 5) ' tên hàng
b(rowb, 3) = a(rows, 6) ' đơn vị
b(rowb, 9) = giavon
CongDon b, rowb, a, rowa ' cọng dồn cột 4, 5, 6, 7, 8
End Sub

Hoàng Nhật Phương · 20/4/20

VetMini đã viết:
Code (đại khái)

' a là mảng đầu vào
' b là mảng đầu ra
' mhRg Range chứa dữ liệu mã hàng
' dlHang là mảng chứa dữ liệu hàng

Redim b(1 To UBound(a), 1 To 9) ' cột thứ 9 chứa dữ liệu tính toán, sẽ không chép ra
For i = 1 To UBound(a)
ma = a(i, 1)
If Dic.Exists(ma) Then
' cọng đòn vào dòng có sẵn
CongDon b, Dic(ma), a, i
Else
' chép dòng mới vào b, và Dic
rowb = rowb + 1
Dic.Add ma, rowb ' ghi vào dic
DongMoi b, rowb, a, i, dlHang(Application.Match(ma, mhRg, 1), 5)
End If
Next i
Range(destination).Resize(rowb, 8) = b

Sub CongDon(b(), rowb As Long, a(), rowa As Long)
b(rowb, 4) = b(rowb, 4) + a(rowa, 7) ' số lượng
b(rowb, 5) = b(rowb, 5) + a(rowa, 9) ' chiét khấu
b(rowb, 6) = b(rowb, 6) + a(rowa, 11) ' thành tiền
b(rowb, 7) = b(rowb, 4) * b(rowb, 9) ' giá vốn
b(rowb, 8) = b(rowb, 6) - b(rowb, 7) ' lãi
End Sub

Sub DongMoi(b(), rowb As Long, a(), rowa As Long, giavon As Double)
' ghi vào dòng đầu ra
b(rowb, 1) = rowb ' số thứ tự
b(rowb, 2) = a(rows, 5) ' tên hàng
b(rowb, 3) = a(rows, 6) ' đơn vị
b(rowb, 9) = giavon
CongDon b, rowb, a, rowa ' cọng dồn cột 4, 5, 6, 7, 8
End Sub

Xin chào Bác VetMini,
Cảm ơn Bác đã chỉ dẫn cho con, để con thử code theo cách này xem thế nào.. được đến đâu thì con sẽ đưa lên đây để nhờ Bác và mọi người chỉ dẫn tiếp ạ.Hi vọng sẽ không lên cơn sốt ạ, hic giờ mà sốt chắc là chính quyền xã đến đưa đi cách ly Bác ạ, do đó làm gì cũng phải giữ gìn sức khỏe ạ.

snow25 · 20/4/20

thanhphuongvip đã viết:
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):

View attachment 235948

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:

View attachment 235949

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.

View attachment 235951

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!

Bạn thử nhé.

Mã:

Sub layso()
   Dim arr, kq, i As Long, lr As Long, dic As Object, ngaybd As Double, ngaykt As Double, dk As String, ten As String, a As Long, b As Long
   Set dic = CreateObject("scripting.dictionary")
   With Sheets("baocaoxuatkho")
        ngaybd = .Range("D2").Value2
        ngaykt = .Range("D3").Value2
        ten = .Range("h3").Value
   End With
   With Sheets("THHD")
        lr = .Range("E" & Rows.Count).End(xlUp).Row
        If lr < 10 Then Exit Sub
        arr = .Range("E10:W" & lr).Value2
        ReDim kq(1 To UBound(arr), 1 To 8)
     If ten = "Full" Then
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
                  dk = arr(i, 3)
                  If Not dic.exists(dk) Then
                     a = a + 1
                     kq(a, 1) = a
                     kq(a, 2) = arr(i, 4)
                     kq(a, 3) = arr(i, 5)
                     kq(a, 4) = arr(i, 6)
                     kq(a, 5) = arr(i, 7)
                     kq(a, 6) = arr(i, 9)
                     dic.Add dk, a
                  Else
                     b = dic.Item(dk)
                     kq(b, 4) = arr(i, 6) + kq(b, 4)
                     kq(b, 5) = arr(i, 7) + kq(b, 5)
                     kq(b, 6) = arr(i, 9) + kq(b, 6)
                  End If
            End If
       Next i
     Else
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
               If ten = arr(i, 19) Then
                  dk = arr(i, 3)
                  If Not dic.exists(dk) Then
                     a = a + 1
                     kq(a, 1) = a
                     kq(a, 2) = arr(i, 4)
                     kq(a, 3) = arr(i, 5)
                     kq(a, 4) = arr(i, 6)
                     kq(a, 5) = arr(i, 7)
                     kq(a, 6) = arr(i, 9)
                     dic.Add dk, a
                  Else
                     b = dic.Item(dk)
                     kq(b, 4) = arr(i, 6) + kq(b, 4)
                     kq(b, 5) = arr(i, 7) + kq(b, 5)
                     kq(b, 6) = arr(i, 9) + kq(b, 6)
                  End If
              End If
           End If
      Next i
    End If
 End With
 With Sheets("mahang")
      lr = .Range("D" & Rows.Count).End(xlUp).Row
      arr = .Range("D9:H" & lr).Value
      For i = 1 To UBound(arr)
          dk = arr(i, 1)
          If dic.exists(dk) Then
             b = dic.Item(dk)
             kq(b, 7) = kq(b, 4) * arr(i, 5)
             kq(b, 8) = kq(b, 6) - kq(b, 7)
          End If
      Next i
End With
 With Sheets("Baocaoxuatkho")
      lr = .Range("C" & Rows.Count).End(xlUp).Row
      If lr > 9 Then .Range("C10:J" & lr).ClearContents
      If a Then .Range("C10:J10").Resize(a).Value = kq
 End With
End Sub

VetMini · 20/4/20

snow25 đã viết:
Bạn thử nhé.

Mã:

Sub layso() ...

Code trên có hai đoạn in hệt nhau. Trên nguyên tắc lập trình, đấy là luộm thuộm.

Thực sự đâu có cần phân biệt trường hợp "Full [sic]" hay có tên. ("full" là Tây bồi, đúng tiếng Tây thì phải là "All")
If ten = "All" OR (ten = arr(i, 19)) Then
' ... code gì đó ở đây

Cáh khác để xử lý hai đoạn code in hệt nhau là:

1. cách cấu trúc: lôi đoạn code ra thành sub riêng
- lợi: code rõ ràng, dễ chỉnh, dễ xem.
- bất lợi:
-- gọi sub thì hơi tốn năng lượng 1 chút, cỡ vài phần tỷ hay phần triệu giây, tuỳ theo máy.
-- gọi sub thì phải cho tham số (xem bài #23)

2. cách thượng cổ: lôi đoạn code ra làm sub nội của sub này (gọi bằng lệnh gosub)
sở dĩ tôi dùng từ "thượng cổ" là vì cách này ngày xưa là cách duy nhất để chia subs. Ngày xưa cấu trúc ngôn ngữ BASIC không có sub function riêng biệt. Và "thượng cổ" không có nghĩa là "hết xài"
- lợi:
-- không phải lặp lại code
-- không phải truyền tham số. Sub là sub nội cho nên nó dùng chung biến nội với sub mẹ.
- bất lợi:
-- sub mẹ luôn phải exit sub trước khi bắt đầu code sub nội.
-- sub nội dài quá sẽ khó phân biệt là những biến nào đã bị nó thay đổi trị.
-- một số người cho rằng đây là loại code mỳ Ý (tôi nói một số ngừoi thôi, cho nên đòng ý hay không là quan niệm riêng, và tôi sẽ không tranh cãi điểm này ở đây)

Ví dụ sub nội:

Mã:

Sub Mẹ()
...
    If ten = "Full" Then
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
              GoSub MySubNoi
            End If
       Next i
     Else
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
               If ten = arr(i, 19) Then
                 GoSub MySubNoi
              End If
           End If
      Next i
    End If

' ... code gì đó ở đây

Exit Sub ' lệnh này bắt buộc phải có. Nó tránh code bước vào sub nội

MySubNoi:
     dk = arr(i, 3)
     If Not dic.exists(dk) Then
        a = a + 1
        kq(a, 1) = a
        kq(a, 2) = arr(i, 4)
        kq(a, 3) = arr(i, 5)
        kq(a, 4) = arr(i, 6)
        kq(a, 5) = arr(i, 7)
        kq(a, 6) = arr(i, 9)
        dic.Add dk, a
     Else
        b = dic.Item(dk)
        kq(b, 4) = arr(i, 6) + kq(b, 4)
        kq(b, 5) = arr(i, 7) + kq(b, 5)
        kq(b, 6) = arr(i, 9) + kq(b, 6)
     End If
Return ' lệnh này đưa VBA trở về dòng ngay sau dòng gọi GoSub

End Sub ' end sub mẹ

snow25 · 20/4/20

VetMini đã viết:
Code trên có hai đoạn in hệt nhau. Trên nguyên tắc lập trình, đấy là luộm thuộm.

Thực sự đâu có cần phân biệt trường hợp "Full [sic]" hay có tên. ("full" là Tây bồi, đúng tiếng Tây thì phải là "All")
If ten = "All" OR (ten = arr(i, 19)) Then
' ... code gì đó ở đây

Cáh khác để xử lý hai đoạn code in hệt nhau là:

1. cách cấu trúc: lôi đoạn code ra thành sub riêng
- lợi: code rõ ràng, dễ chỉnh, dễ xem.
- bất lợi:
-- gọi sub thì hơi tốn năng lượng 1 chút, cỡ vài phần tỷ hay phần triệu giây, tuỳ theo máy.
-- gọi sub thì phải cho tham số (xem bài #23)

2. cách thượng cổ: lôi đoạn code ra làm sub nội của sub này (gọi bằng lệnh gosub)
sở dĩ tôi dùng từ "thượng cổ" là vì cách này ngày xưa là cách duy nhất để chia subs. Ngày xưa cấu trúc ngôn ngữ BASIC không có sub function riêng biệt. Và "thượng cổ" không có nghĩa là "hết xài"
- lợi:
-- không phải lặp lại code
-- không phải truyền tham số. Sub là sub nội cho nên nó dùng chung biến nội với sub mẹ.
- bất lợi:
-- sub mẹ luôn phải exit sub trước khi bắt đầu code sub nội.
-- sub nội dài quá sẽ khó phân biệt là những biến nào đã bị nó thay đổi trị.
-- một số người cho rằng đây là loại code mỳ Ý (tôi nói một số ngừoi thôi, cho nên đòng ý hay không là quan niệm riêng, và tôi sẽ không tranh cãi điểm này ở đây)

Ví dụ sub nội:

Mã:

Sub Mẹ() ... If ten = "Full" Then For i = 1 To UBound(arr) If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then GoSub MySubNoi End If Next i Else For i = 1 To UBound(arr) If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then If ten = arr(i, 19) Then GoSub MySubNoi End If End If Next i End If ' ... code gì đó ở đây Exit Sub ' lệnh này bắt buộc phải có. Nó tránh code bước vào sub nội MySubNoi: dk = arr(i, 3) If Not dic.exists(dk) Then a = a + 1 kq(a, 1) = a kq(a, 2) = arr(i, 4) kq(a, 3) = arr(i, 5) kq(a, 4) = arr(i, 6) kq(a, 5) = arr(i, 7) kq(a, 6) = arr(i, 9) dic.Add dk, a Else b = dic.Item(dk) kq(b, 4) = arr(i, 6) + kq(b, 4) kq(b, 5) = arr(i, 7) + kq(b, 5) kq(b, 6) = arr(i, 9) + kq(b, 6) End If Return ' lệnh này đưa VBA trở về dòng ngay sau dòng gọi GoSub End Sub ' end sub mẹ

Cảm ơn anh nhé.

HieuCD · 20/4/20

snow25 đã viết:

Bạn thử nhé.

Mã:

Sub layso()
   Dim arr, kq, i As Long, lr As Long, dic As Object, ngaybd As Double, ngaykt As Double, dk As String, ten As String, a As Long, b As Long
   Set dic = CreateObject("scripting.dictionary")
   With Sheets("baocaoxuatkho")
        ngaybd = .Range("D2").Value2
        ngaykt = .Range("D3").Value2
        ten = .Range("h3").Value
   End With
   With Sheets("THHD")
        lr = .Range("E" & Rows.Count).End(xlUp).Row
        If lr < 10 Then Exit Sub
        arr = .Range("E10:W" & lr).Value2
        ReDim kq(1 To UBound(arr), 1 To 8)
     If ten = "Full" Then
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
                  dk = arr(i, 3)
                  If Not dic.exists(dk) Then
                     a = a + 1
                     kq(a, 1) = a
                     kq(a, 2) = arr(i, 4)
                     kq(a, 3) = arr(i, 5)
                     kq(a, 4) = arr(i, 6)
                     kq(a, 5) = arr(i, 7)
                     kq(a, 6) = arr(i, 9)
                     dic.Add dk, a
                  Else
                     b = dic.Item(dk)
                     kq(b, 4) = arr(i, 6) + kq(b, 4)
                     kq(b, 5) = arr(i, 7) + kq(b, 5)
                     kq(b, 6) = arr(i, 9) + kq(b, 6)
                  End If
            End If
       Next i
     Else
        For i = 1 To UBound(arr)
            If ngaybd <= arr(i, 1) And ngaykt >= arr(i, 1) Then
               If ten = arr(i, 19) Then
                  dk = arr(i, 3)
                  If Not dic.exists(dk) Then
                     a = a + 1
                     kq(a, 1) = a
                     kq(a, 2) = arr(i, 4)
                     kq(a, 3) = arr(i, 5)
                     kq(a, 4) = arr(i, 6)
                     kq(a, 5) = arr(i, 7)
                     kq(a, 6) = arr(i, 9)
                     dic.Add dk, a
                  Else
                     b = dic.Item(dk)
                     kq(b, 4) = arr(i, 6) + kq(b, 4)
                     kq(b, 5) = arr(i, 7) + kq(b, 5)
                     kq(b, 6) = arr(i, 9) + kq(b, 6)
                  End If
              End If
           End If
      Next i
    End If
End With
With Sheets("mahang")
      lr = .Range("D" & Rows.Count).End(xlUp).Row
      arr = .Range("D9:H" & lr).Value
      For i = 1 To UBound(arr)
          dk = arr(i, 1)
          If dic.exists(dk) Then
             b = dic.Item(dk)
             kq(b, 7) = kq(b, 4) * arr(i, 5)
             kq(b, 8) = kq(b, 6) - kq(b, 7)
          End If
      Next i
End With
With Sheets("Baocaoxuatkho")
      lr = .Range("C" & Rows.Count).End(xlUp).Row
      If lr > 9 Then .Range("C10:J" & lr).ClearContents
      If a Then .Range("C10:J10").Resize(a).Value = kq
End With
End Sub

Dùng cấu trúc luận lý gộp 2 đoạn code giống nhau thành 1
If (ten <> "Full")=(ten = arr(i, 19)) Then

thanhphuongvip · 20/4/20

SA_DQ đã viết:
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.

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

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

Trước mình ko để trùng lặp, nhưng nếu ko trùng lập thì khi mình xuất ra từng Khách Hàng đã mua những Mặt hàng gì thì phần cột Khách hàng bị thiếu mình ko xuất đc, vậy làm sao hả a ?

SA_DQ · 20/4/20

Trước mình ko để trùng lặp, nhưng nếu ko trùng lập thì khi mình xuất ra từng Khách Hàng đã mua những Mặt hàng gì thì phần cột Khách hàng bị thiếu mình ko xuất đc, vậy làm sao hả a ?

Thay vì trùng cả 4 cột, ta chỉ nhập trùng Mã KH thôi;
Lúc nào cần hiện các trường thì xài VLOOKUP()

VetMini · 20/4/20

SA_DQ đã viết:
Thay vì trùng cả 4 cột, ta chỉ nhập trùng Mã KH thôi;
Lúc nào cần hiện các trường thì xài VLOOKUP()

Cái này đâu phải VBA. Người ta là dân dạy Excel mà, đâu có lạ gì với mấy cái hàm Excel.

SA_DQ · 21/4/20

Ý mình là: Thay vì nhập 4 cột (trường) [Khách Hàng], [Địa chỉ], [ĐT] & [Ghi chú] Ta chỉ cần nhập trường (cột) [Mã KH] mà thôi.
Tất nhiên chuyện này không liên quan lắm đến kiến thức vế công thức Excel hay VBA cả.
Hì, hì, hi,. . . .

tranhungdao12a3 · 15/8/20

Bài này làm power bi là nhanh nhất. Tạo 2slicer lọc + 2Measure tiền vốn, lợi nhuậnl

Tổng hợp dữ liệu bán hàng dùng Dictionary ? (1 người xem)

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

Mới học VBA, hỏi ngu anh chị đừng chửi ạ

File đính kèm

Mới học VBA, hỏi ngu anh chị đừng chửi ạ

Mới học VBA, hỏi ngu anh chị đừng chửi ạ

Thành viên tích cực

Thành viên tích cực

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

Đang đi tìm hòn đá

Đang đi tìm hòn đá

Thành viên gắn bó

Thành viên tích cực

Đang đi tìm hòn đá

Thành viên tích cực

Thành viên gắn bó

/(hông là gì!

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

Chuyên gia GPE

Thành viên gắn bó

Chuyên gia GPE

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

Thành viên gắn bó

Đang đi tìm hòn đá

Thành viên tích cực

Đang đi tìm hòn đá

Thành viên gắn bó

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

Đang đi tìm hòn đá

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

Chuyên gia GPE

Mới học VBA, hỏi ngu anh chị đừng chửi ạ

/(hông là gì!

Đang đi tìm hòn đá

/(hông là gì!

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

Thời gian đếm ngược.