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

Quảng cáo

moihocvba

Thành viên mới
Tham gia ngày
16 Tháng tám 2020
Bài viết
47
Được thích
11
Điểm
3
Tuổi
23
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: 42
Lần chỉnh sửa cuối:

Nhattanktnn

Thành viên tích cực
Tham gia ngày
11 Tháng mười một 2016
Bài viết
1,432
Được thích
1,357
Điểm
668
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
 

batman1

Thành viên gạo cội
Tham gia ngày
8 Tháng chín 2014
Bài viết
4,007
Được thích
6,362
Điểm
2,568
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.
 

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
11,050
Được thích
13,713
Điểm
4,868
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.
 

Ngô Hải Đăng

Thành viên hoạt động
Tham gia ngày
31 Tháng tám 2017
Bài viết
101
Được thích
89
Điểm
128
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

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,528
Được thích
30,888
Điểm
9,718
Tuổi
59
Nơi ở
Gò Vấp
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

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
11,050
Được thích
13,713
Điểm
4,868
.... 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

Thành viên gạo cội
Tham gia ngày
8 Tháng chín 2014
Bài viết
4,007
Được thích
6,362
Điểm
2,568
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
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,528
Được thích
30,888
Điểm
9,718
Tuổi
59
Nơi ở
Gò Vấp
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.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,528
Được thích
30,888
Điểm
9,718
Tuổi
59
Nơi ở
Gò Vấp
Đí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

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
11,050
Được thích
13,713
Điểm
4,868
... 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

❆❆❆❆❆❆❆❆
Thành viên BQT
Administrator
Tham gia ngày
18 Tháng ba 2008
Bài viết
7,981
Được thích
15,177
Điểm
4,718
Nơi ở
Tp.HCM
Đí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...???
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,528
Được thích
30,888
Điểm
9,718
Tuổi
59
Nơi ở
Gò Vấp
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.
 
Quảng cáo
Top Bottom