Sử dụng dictionary VBA để tính tồn kho

Liên hệ QC

moihocvba

Thành viên thường trực
Tham gia
16/8/20
Bài viết
207
Được thích
49
Xin chào anh chị GPE!

Em đang cần tính TỒN KHO của kho dựa vào dữ liệu đầu vào: TỒN KHO = SL Tồn đầu (cột E sheet ListHang) + Nhập kho (Cột D sheet NhapKho) - Xuất Kho (Cột D sheet XuatKho)

Để tính cái này thì dùm SUMIFS nó cũng ra được, nhưng vì dữ liệu của em lớn (10.000 mã hàng và hàng trăm ngàn dòng nhập xuất nên mỗi lần nhập liệu hoặc tra cứu thì excel phải tính lại nên rất lâu và file sẽ nặng), với em rất thường tra cứu tồn kho nên dùng sumif trong vba đưa ra tính toán cũng chậm.

Nên em muốn nhờ anh chị giúp em thuật toán đưa dữ liệu vào mảng rồi dùng dictionary để tính toán sau đó đưa dữ liệu ra sheet tồn được không ạ? Vì em nghe nói cách này tính toán rất nhanh với khối lượng dữ liệu lớn. Em cũng đang học về VBA dictionary nên cũng muốn tham khảo cách tối ưu nhất.

Xin cảm ơn anh chị rất nhiều! Em có gửi file đính kèm ạ! File này em trích ra ví dụ thôi, chứ file thực tế em nhiều sheet hơn nữa, nên hơi nặng.

Bổ sung: Em lấy Tên hàng làm giá trị duy nhất ạ (Mã hàng ko bắt buộc)
 

File đính kèm

  • NhapXuatTon.xlsm
    2.7 MB · Đọc: 73
Lần chỉnh sửa cuối:
Xin chào anh chị GPE!

Em đang cần tính TỒN KHO của kho dựa vào dữ liệu đầu vào: TỒN KHO = SL Tồn đầu (cột E sheet ListHang) + Nhập kho (Cột D sheet NhapKho) - Xuất Kho (Cột D sheet XuatKho)

Để tính cái này thì dùm SUMIFS nó cũng ra được, nhưng vì dữ liệu của em lớn (10.000 mã hàng và hàng trăm ngàn dòng nhập xuất nên mỗi lần nhập liệu hoặc tra cứu thì excel phải tính lại nên rất lâu và file sẽ nặng), với em rất thường tra cứu tồn kho nên dùng sumif trong vba đưa ra tính toán cũng chậm.

Nên em muốn nhờ anh chị giúp em thuật toán đưa dữ liệu vào mảng rồi dùng dictionary để tính toán sau đó đưa dữ liệu ra sheet tồn được không ạ? Vì em nghe nói cách này tính toán rất nhanh với khối lượng dữ liệu lớn. Em cũng đang học về VBA dictionary nên cũng muốn tham khảo cách tối ưu nhất.

Xin cảm ơn anh chị rất nhiều! Em có gửi file đính kèm ạ! File này em trích ra ví dụ thôi, chứ file thực tế em nhiều sheet hơn nữa, nên hơi nặng.
Sao lại không có mã hàng gì thế này
 
Upvote 0
Nhìn thấy lạ quá. Tồn kho vào ngày nào? Ngày cuối 2017, 2018, 2019, 2020?
Nhập, xuất không có ngày tháng? Cứ mỗi lần tính tồn kho là tính tới ngày "hôm nay"? Tức giả sử có tồn kho cuối 2020 và "hôm nay" là 02.03.2021, thì không tính được tồn kho vào ngày 31.01.2021? Vì Nhập xuất có ngày tháng đâu mà tính với toán.
 
Upvote 0
Nhìn thấy lạ quá. Tồn kho vào ngày nào? Ngày cuối 2017, 2018, 2019, 2020?
Nhập, xuất không có ngày tháng? Cứ mỗi lần tính tồn kho là tính tới ngày "hôm nay"? Tức giả sử có tồn kho cuối 2020 và "hôm nay" là 02.03.2021, thì không tính được tồn kho vào ngày 31.01.2021? Vì Nhập xuất có ngày tháng đâu mà tính với toán.
Bởi vậy phải bảo thớt đưa cái hàm SUMIFS của mình lên. Đọc nó mới hiểu thớt muốn cái gì, và làm sao làm việc được với cái đống dữ liệu tùm lùm ấy.

Như sau đây:
E muốn dùng dic cho nhẹ ạ, chứ SUMIFS thì em biết làm rồi ạ!
Bạn biết, nhưng người khác không biết. Vì vậy người ta cần nhìn xem bạn làm ra sao.
 
Upvote 0
Em đang cần tính TỒN KHO của kho dựa vào dữ liệu đầu vào: TỒN KHO = SL Tồn đầu (cột E sheet ListHang) + Nhập kho (Cột D sheet NhapKho) - Xuất Kho (Cột D sheet XuatKho)
Code theo ý thớt, do dữ liệu bị cắt tùm lum nên có một số mặt hàng bị âm.
PHP:
Sub TinhTonKho()
  Dim dMatHang
  Set dMatHang = CreateObject("Scripting.Dictionary")
  DuyetNhapKho dMatHang, Sheets("ListHang"), 5, 2, 5
  DuyetNhapKho dMatHang, Sheets("NhapKho"), 3, 2, 4
  DuyetXuatKho dMatHang, Sheets("XuatKho"), 3, 2, 4
  Sheets("TonKho").Range("D5").Resize(dMatHang.Count) = Application.Transpose(dMatHang.Items)
  Sheets("TonKho").Range("E5").Resize(dMatHang.Count) = Application.Transpose(dMatHang.Keys)
End Sub

Private Sub DuyetNhapKho(iMatHang, iSheet As Worksheet, iDongBatDau As Long, iCotMatHang As Integer, iCotSoLuong As Integer)
  Dim x&, sKey$
  For x = iDongBatDau To iSheet.Cells(iDongBatDau, iCotMatHang).End(xlDown).Row
    sKey = iSheet.Cells(x, iCotMatHang).Value
    iMatHang(sKey) = iMatHang(sKey) + iSheet.Cells(x, iCotSoLuong).Value
  Next x
End Sub

Private Sub DuyetXuatKho(iMatHang, iSheet As Worksheet, iDongBatDau As Long, iCotMatHang As Integer, iCotSoLuong As Integer)
  Dim x&, sKey$
  For x = iDongBatDau To iSheet.Cells(iDongBatDau, iCotMatHang).End(xlDown).Row
    sKey = iSheet.Cells(x, iCotMatHang).Value
    iMatHang(sKey) = iMatHang(sKey) - iSheet.Cells(x, iCotSoLuong).Value
  Next x
End Sub
 
Upvote 0
Code viết đơn giản cho dễ hiểu. Phát hiện 1824 mặt hàng không có trong list nhưng có nhập xuất. Đó là cái tội không dùng mã hàng mà dùng tên cho gõ sai.
PHP:
Sub TinhTon()
Dim LastRw As Long, SArr(), RArr(), Dict1, NaArr(1 To 20000, 1 To 1)
Dim i As Long, k As Long, Na As Long, Item As String
Application.ScreenUpdating = False
Set Dict1 = CreateObject("Scripting.Dictionary")
With Sheet3 '(List hang)'
    LastRw = .[B20000].End(xlUp).Row
    SArr = .Range("B5:E" & LastRw).Value2
ReDim RArr(1 To UBound(SArr, 1), 1 To 3)
    For i = 1 To UBound(SArr, 1)
        Dict1.Add SArr(i, 1), i
        RArr(i, 1) = SArr(i, 1)
        RArr(i, 2) = SArr(i, 2)
        RArr(i, 3) = SArr(i, 4)
    Next
End With

With Sheet4 '(Nhap kho)'
    LastRw = .[B500000].End(xlUp).Row
    SArr = .Range("B3:D" & LastRw).Value2
    For i = 1 To UBound(SArr, 1)
        Item = SArr(i, 1)
        If Dict1.exists(Item) Then
            k = Dict1.Item(Item)
            RArr(k, 3) = RArr(k, 3) + SArr(i, 3)
        Else
            Na = Na + 1
            NaArr(Na, 1) = Item
        End If
    Next
End With
With Sheet1 '(Xuat kho)'
    LastRw = .[B500000].End(xlUp).Row
    SArr = .Range("B3:D" & LastRw).Value2
    For i = 1 To UBound(SArr, 1)
        Item = SArr(i, 1)
        If Dict1.exists(Item) Then
            k = Dict1.Item(Item)
            RArr(k, 3) = RArr(k, 3) - SArr(i, 3)
        Else
            Na = Na + 1
            NaArr(Na, 1) = Item
        End If
    Next
End With
With Sheet5 '(Ton kho)'
    .Range("B5:F20000").Clear
    .[B5].Resize(Dict1.Count, 3).Value = RArr
    .[F5].Resize(Na, 1).Value = NaArr
    .[F5].Resize(Na, 1).RemoveDuplicates Columns:=1, Header:=xlNo
    Na = Application.CountA(.[F5:F20000])
End With
Application.ScreenUpdating = True
MsgBox "There are " & Na & " item(s) not available but have been transfered", , "ptm0412"

End Sub
 
Upvote 0
.... Em cũng đang học về VBA dictionary nên cũng muốn tham khảo cách tối ưu nhất.
Đít sần không phải là tối ưu cho bài này.

Với 2016 trở đi thì cách làm đúng nhất là dùng Power Query.

Về lập trình thì loại bài tổng hợp này đít sần phải nhường ADO.
 
Upvote 0
Code viết đơn giản cho dễ hiểu. Phát hiện 1824 mặt hàng không có trong list nhưng có nhập xuất. Đó là cái tội không dùng mã hàng mà dùng tên cho gõ sai.
Tôi thấy lôgíc mà. List là kiểm kê ở ngày cụ thể. Lúc đó có thể hết mặt hàng A, hoặc chưa bao giờ từng có. Sau ngày đó có nhập mặt hàng A nên chuyện có xuất là dễ hiểu. Chỉ sợ tồn không có, nhập cũng không, mà lại có đơn xuất thì mới kỳ diệu. :D
 
Upvote 0
Tôi thấy lôgíc mà. List là kiểm kê ở ngày cụ thể. Lúc đó có thể hết mặt hàng A, hoặc chưa bao giờ từng có. Sau ngày đó có nhập mặt hàng A nên chuyện có xuất là dễ hiểu. Chỉ sợ tồn không có, nhập cũng không, mà lại có đơn xuất thì mới kỳ diệu. :D
Tôi có liệt kê ra và kiểm tra sơ bộ: Toàn là lỗi gõ sai như dư khoảng trắng, thiếu khoảng trắng, sai chính tả.
Có cả không tồn không nhập mà xuất!
Còn việc không tồn, sau đó nhập xuất thì dễ hiểu, nhưng sai nguyên tắc chưa bổ sung vào list đã giao dịch, đúng chuẩn là tạo mã trước, giao dịch sau.
 
Upvote 0
Đít sần không phải là tối ưu cho bài này.
Bài này nó vừa kỳ diệu vừa kỳ cục.
- Tồn đầu không biết tồn khi nào vì không thấy ngày tháng, có phải danh mục hay không.
- Tồn cuối không biết có phải theo ngày tháng hay không, vì không thấy ngày tháng tính tồn
- Nhập xuất cũng không có ngày tháng của từng giao dịch

Do kỳ cục nên tôi làm theo kiểu tồn đầu là đầu chưa có giao dịch nào, tồn cuối là tồn đến sau giao dịch cuối.
Nếu xài Power query hay ADO thì:
- Sheet List có phải danh mục không? để còn join jiếc (left, right, full, hay inner)
- Xử lý sao với không có trong list cũng không nhập mà xuất? không có tên trong list mà nhập?
 
Upvote 0
... Do kỳ cục nên tôi làm theo kiểu tồn đầu là đầu chưa có giao dịch nào, tồn cuối là tồn đến sau giao dịch cuối.
Nếu xài Power query hay ADO thì:
- Sheet List có phải danh mục không? để còn join jiếc (left, right, full, hay inner)
- Xử lý sao với không có trong list cũng không nhập mà xuất? không có tên trong list mà nhập?
Coi như có 2 loại phát sinh chính: loại tồn đầu coi như nhập, loại nhập, và loại xuất.
Và 1 loại phát sinh đặc bệt: loại nhập/xuất mà chưa có tên trong danh mục coi như là loại phát sinh tạo danh mục.

Join: bạn không dùng Join mà dùng Union All và Group.
 
Upvote 0
Đít sần không phải là tối ưu cho bài này.

Với 2016 trở đi thì cách làm đúng nhất là dùng Power Query.

Về lập trình thì loại bài tổng hợp này đít sần phải nhường ADO.
Tác giả yêu cầu là:

Nên em muốn nhờ anh chị giúp em thuật toán đưa dữ liệu vào mảng rồi dùng dictionary để tính toán sau đó đưa dữ liệu ra sheet tồn được không ạ? Vì em nghe nói cách này tính toán rất nhanh với khối lượng dữ liệu lớn. Em cũng đang học về VBA dictionary nên cũng muốn tham khảo cách tối ưu nhất.
Nên ADO hay Power Query là cái chi chi...???
 
Upvote 0
Tác giả yêu cầu là: Dictionary
Nên ADO hay Power Query là cái chi chi...???
Dù tác giả yêu cầu Dict nhưng lại cho rằng Dict là tối ưu, nên có ý kiến đóng góp đó mà. Vả lại trên diễn đàn có những yêu cầu thuộc loại không tưởng, nên khi gặp thì sẽ khuyến cáo phải dùng cách khác.
 
Upvote 0
Web KT
Back
Top Bottom