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

moihocvba · 22/1/21

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)

Nhattanktnn · 22/1/21

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

moihocvba · 22/1/21

Nhattanktnn đã viết:
Sao lại không có mã hàng gì thế này

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

VetMini · 22/1/21

Nhattanktnn đã viết:
Sao lại không có mã hàng gì thế này

Dựa vào cái hàm SUMIFS của thớt mà làm.
Thớt nói rằng dùng SUMIFS cũng ra thì bảo gởi vài ví dụ. Cách đó là dễ nhất.

Hoàng Tuấn 868 · 22/1/21

moihocvba đã viết:
Em đang cần tính TỒN KHO

Bạn đưa ví dụ ẩu quá, nhìn vào đã thấy không muốn làm rồi.

moihocvba · 22/1/21

Hoàng Tuấn 868 đã viết:
Bạn đưa ví dụ ẩu quá, nhìn vào đã thấy không muốn làm rồi.

Ẩu chỗ nào vậy ạ?

Hoàng Tuấn 868 · 22/1/21

moihocvba đã viết:
Ẩu chỗ nào vậy ạ?

Giá bạn đưa đầy đủ thông tin lên sẽ dễ hình dung hơn là xóa bớt đi như vậy, sẽ không phải hỏi thêm nữa bạn à.

moihocvba · 22/1/21

VetMini đã viết:
Dựa vào cái hàm SUMIFS của thớt mà làm.
Thớt nói rằng dùng SUMIFS cũng ra thì bảo gởi vài ví dụ. Cách đó là dễ nhất.

E muốn dùng dic cho nhẹ ạ, chứ SUMIFS thì em biết làm rồi ạ!

batman1 · 22/1/21

Nhìn thấy lạ quá. Tồn kho vào ngày nào? Ngày cuối 2017, 2018, 2019, 2020?
Nhập, xuất không có ngày tháng? Cứ mỗi lần tính tồn kho là tính tới ngày "hôm nay"? Tức giả sử có tồn kho cuối 2020 và "hôm nay" là 02.03.2021, thì không tính được tồn kho vào ngày 31.01.2021? Vì Nhập xuất có ngày tháng đâu mà tính với toán.

VetMini · 22/1/21

batman1 đã viết:
Nhìn thấy lạ quá. Tồn kho vào ngày nào? Ngày cuối 2017, 2018, 2019, 2020?
Nhập, xuất không có ngày tháng? Cứ mỗi lần tính tồn kho là tính tới ngày "hôm nay"? Tức giả sử có tồn kho cuối 2020 và "hôm nay" là 02.03.2021, thì không tính được tồn kho vào ngày 31.01.2021? Vì Nhập xuất có ngày tháng đâu mà tính với toá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:

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

Ngô Hải Đăng · 22/1/21

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

ptm0412 · 22/1/21

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

VetMini · 22/1/21

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

batman1 · 22/1/21

ptm0412 đã viết:
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ôgíc mà. List là kiểm kê ở ngày cụ thể. Lúc đó có thể hết mặt hàng A, hoặc chưa bao giờ từng có. Sau ngày đó có nhập mặt hàng A nên chuyện có xuất là dễ hiểu. Chỉ sợ tồn không có, nhập cũng không, mà lại có đơn xuất thì mới kỳ diệu.

ptm0412 · 22/1/21

batman1 đã viết:
Tôi thấy lôgíc mà. List là kiểm kê ở ngày cụ thể. Lúc đó có thể hết mặt hàng A, hoặc chưa bao giờ từng có. Sau ngày đó có nhập mặt hàng A nên chuyện có xuất là dễ hiểu. Chỉ sợ tồn không có, nhập cũng không, mà lại có đơn xuất thì mới kỳ diệu.

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.

batman1 · 22/1/21

ptm0412 đã viết:
Có cả không tồn không nhập mà xuất!

Thật kỳ diệu

ptm0412 · 22/1/21

VetMini đã viết:
Đí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?

VetMini · 23/1/21

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

Hai Lúa Miền Tây · 23/1/21

VetMini đã viế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.

Tác giả yêu cầu là:

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

ptm0412 · 23/1/21

Hai Lúa Miền Tây đã viết:
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.

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

Thành viên thường trực

File đính kèm

Thành viên gắn bó

Thành viên thường trực

Ăn cùng góc phố

Đăng ký hôm qua

Thành viên thường trực

Đăng ký hôm qua

Thành viên thường trực

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

Ăn cùng góc phố

Thành viên hoạt động

Bad Excel Member

Ăn cùng góc phố

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

Bad Excel Member

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

Bad Excel Member

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

Bad Excel Member