UDF tính tồn kho (theo số lượng) tại một thời điểm bất kỳ

[BNTT]

Tôi có một đề tài thú vị đây.
Để tính tồn kho tại một thời điểm bất kỳ, chúng ta đã biết là dễ dàng dùng hàm SUMPRODUCT. Tuy nhiên, cho tôi hỏi, có thể tạo một cái UDF được không?

Ví dụ, có 1 Database Hàng hóa, gồm có các cột như sau:

1. Ngày tháng năm (nhập hoặc xuất)
2. Mã hàng hóa
3. Số lượng Nhập
4. Số lượng Xuất​

Xin nhờ các bác làm giúp một cái UDF (có thể chạy trong Excel và cả trong VBA) theo dạng sau:

TONKHO(DATABASE,NGAY,MAHANG)​

Diễn giải: Dùng để kiểm tra số lượng hàng tồn kho của (MAHANG) tại thời điểm (NGAY) trong bảng (DATABASE)

 

[ThuNghi]

Tôi có một đề tài thú vị đây.
Để tính tồn kho tại một thời điểm bất kỳ, chúng ta đã biết là dễ dàng dùng hàm SUMPRODUCT. Tuy nhiên, cho tôi hỏi, có thể tạo một cái UDF được không?

Ví dụ, có 1 Database Hàng hóa, gồm có các cột như sau:

1. Ngày tháng năm (nhập hoặc xuất)
2. Mã hàng hóa
3. Số lượng Nhập
4. Số lượng Xuất​

Xin nhờ các bác làm giúp một cái UDF (có thể chạy trong Excel và cả trong VBA) theo dạng sau:

TONKHO(NGAY,MAHANG)​

Diễn giải: Dùng để kiểm tra số lượng hàng tồn kho của (MAHANG) tại thời điểm (NGAY)

1. Ngày tháng năm theo thứ tự không?
2. Các chỉ tiêu có đặt thành name chưa? Ví dụ MaHH: Range("MaHH")
3. Tồn kho đầu kỳ =?
4. Excel thì dùng sumproduct.
Xin gởi DL mẫu.

 

[BNTT]

1. Ngày tháng năm theo thứ tự không?
2. Các chỉ tiêu có đặt thành name chưa? Ví dụ MaHH: Range("MaHH")
3. Tồn kho đầu kỳ =?
4. Excel thì dùng sumproduct.

Xin trả lời bác ThuNghi:

1. Ngày tháng năm bắt buộc phải theo thứ tự từ nhỏ đến lớn.
2. Các chỉ tiêu chưa được đặt name (để có thể dùng cho bất kỳ một Database nào).
3. Tồn kho đầu kỳ coi như bằng 0.
4. Dùng SUMPRODUCT thì dễ rồi. Ở đây em muốn làm một cái UDF để có thể xài thẳng trong VBA.​

Em xin nói rõ hơn.
Chỉ dựa vào một cái Database bất kỳ, miễn là đã có những cột như đã nói ở bài #1 (trong database này, có thể có thêm những cột khác như đơn vị tính, trị giá, v,v...).
Yêu cầu của UDF này là dò tìm trong cái Database đó, moi ra số lượng tồn kho của mặt hàng (MAHANG) tại thời điểm ta muốn xem (NGAY).

 

[ThuNghi]

Vậy thì coi
- Cột 1: Ngày
- Cột 2: Mã
- Cột 3: SLN
- Cột 4: SLX
Chớ không thì phải TonKho(Mahang_value,ngay_value,ngay_array,SLN_array,SlX_array) thì điên luôn.

 

[BNTT]

Đúng rồi anh, nếu dài như vầy thì điên thiệt á chứ.
Em chỉ muốn là TONKHO(DATABASE,NGAY,MAHANG) thôi.

Nhân đây em cũng xin mở thêm một topic lập UDF để tính công nợ tại một thời điểm bất kỳ, tương tự như bài này.

 

[ThuNghi]

Đúng rồi anh, nếu dài như vầy thì điên thiệt á chứ.
Em chỉ muốn là TONKHO(NGAY,MAHANG) thôi.

Nhân đây em cũng xin mở thêm một topic lập UDF để tính công nợ tại một thời điểm bất kỳ, tương tự như bài này.

Bạn test hộ UDF này nhé.

Option Explicit
Public Function TonKho(Ngay_Value As Range, MaHH_Value As Range) As Double
'Ngay phai nhap theo dang dd/mm/yy, chua test het
Application.Volatile (False) 'Okebab
Dim Ngay As Range, MaHH As Range, SLNhap As Range, SLXuat As Range
Dim eRow As Long, iDate As Long, iMaHH As Long, i As Long, Tmp As Double
eRow = Sheets("Data").Range("A65000").End(xlUp).Row
'Tam lay sheet co du lieu la Data
Set Ngay = Sheets("Data").Range(Cells(2, 1), Cells(eRow, 1))
Set MaHH = Sheets("Data").Range(Cells(2, 2), Cells(eRow, 2))
iDate = WorksheetFunction.CountIf(Ngay, ">=" & CLng(Ngay_Value))
iMaHH = WorksheetFunction.CountIf(MaHH, MaHH_Value)
If iDate = 0 Or iMaHH = 0 Then
MsgBox "Truy xuat khong dung"
Exit Function
End If
eRow = WorksheetFunction.CountIf(Ngay, "<=" & CLng(Ngay_Value))
Set MaHH = Sheets("Data").Range(Cells(2, 2), Cells(eRow, 2))
Set SLNhap = Sheets("Data").Range(Cells(2, 3), Cells(eRow, 3))
Set SLXuat = Sheets("Data").Range(Cells(2, 4), Cells(eRow, 4))
For i = 1 To eRow
If UCase(MaHH(i)) = UCase(MaHH_Value) Then
Tmp = Tmp + SLNhap(i) - SLXuat(i)
End If
Next
TonKho = Tmp
Tmp = 0
Set Ngay = Nothing
Set MaHH = Nothing
Set SLNhap = Nothing
Set SLXuat = Nothing

End Function

 

[Mr Okebab]

Đúng rồi anh, nếu dài như vầy thì điên thiệt á chứ.
Em chỉ muốn là TONKHO(NGAY,MAHANG) thôi.

Nhân đây em cũng xin mở thêm một topic lập UDF để tính công nợ tại một thời điểm bất kỳ, tương tự như bài này.

Excel có hàm tính cho cái này rồi mà, hàm lại rất đơn giản, vạy thì UF làm gì.

Nếu có thì hãy làm 1 UF về vấn đề đơn giá tồn kho đấy.

1. Cấp 1 : Mảng ngày đã được Sort, tính theo 1 phương pháp thôi
2. Cấp 2 : Mảng ngày chưa được Sort, tính theo 1 phương pháp
3. Cấp 3 : Mảng ngày đã được Sort, tính theo nhiều phương pháp
4. Cấp 4 : Mảng ngày chưa được Sort, tính theo nhiều phương pháp

Thân!

 

[SA_DQ]

Sao không DSUM?

Đúng rồi anh, nếu dài như vầy thì điên thiệt á chứ.
Em chỉ muốn là TONKHO(NGAY,MAHANG) thôi. tương tự như bài này.

Hãy xài công thức sau:
=DSUM(S2.CSDL;C1;F1:G2)-DSUM(S2.CSDL;D1;F4:G5)

)-(em thêm trong file đình kèm

 

[Mr Okebab]

Hãy xài công thức sau:
=DSUM(S2.CSDL;C1;F1:G2)-DSUM(S2.CSDL;D1;F4:G5)

)-(em thêm trong file đình kèm

Nếu đã bàn đến hàm của excel :

=SUMPRODUCT((Ngay<=H3)*(Ma=H4)*(Nhap-Xuat))

Thân!

 

[ThuNghi]

Nếu có thì hãy làm 1 UF về vấn đề đơn giá tồn kho đấy.

1. Cấp 1 : Mảng ngày đã được Sort rồi, tính theo 1 phương pháp thôi
2. Cấp 2 : Mảng ngày chưa được Sort rồi, tính theo 1 phương pháp
3. Cấp 3 : Mảng ngày đã được Sort rồi, tính theo nhiều phương pháp
4. Cấp 4 : Mảng ngày chưa được Sort rồi, tính theo nhiều phương pháp

Thân!

Cơ bản BNTT muốn TH for...next
Cái này khó đấy, tôi chỉ có thể làm đến 2. là hết mà phải cho bảng phụ.
Hiếu làm giúp nhé.
Cám ơn nhiều.

 

[SA_DQ]

Nếu đã bàn đến hàm của excel :

=SUMPRODUCT((Ngay<=H3)*(Ma=H4)*(Nhap-Xuat))

Thân!

To BAB:

http://giaiphapexcel.com/forum/showthread.php?t=5294

 

[BNTT]

Xin lỗi các bác, đề bài đưa ra còn thiếu một tiêu chí nữa, đó là dò trong cái Database nào?

Vậy tôi xin sửa lại như sau:

TONKHO(DATABASE,NGAY,MAHANG)​

(Tìm trong bảng DATABASE, tính đến NGAY, số lượng tồn kho của MAHANG)

@MrOKBap: Cảm ơn ông Tía nhiều. Nhưng ý mình không muốn dùng SUMPRODUCT (và những hàm có sẵn của Excel), mà muốn làm một cái UDF, thứ nhất là đơn giản và dễ xài hơn SUMPRODUCT; thứ hai là có thể dùng luôn trong VBA khi lập trình cho Form nhập liệu.

 

[BNTT]

Nếu có thì hãy làm 1 UF về vấn đề đơn giá tồn kho đấy.

1. Cấp 1 : Mảng ngày đã được Sort, tính theo 1 phương pháp thôi
2. Cấp 2 : Mảng ngày chưa được Sort, tính theo 1 phương pháp
3. Cấp 3 : Mảng ngày đã được Sort, tính theo nhiều phương pháp
4. Cấp 4 : Mảng ngày chưa được Sort, tính theo nhiều phương pháp

Về vấn đề đơn giá tồn kho, tui đã nghĩ đến rồi, nhất là trong trường hợp tính đơn giá tồn kho theo phương pháp đích danh. Tuy nhiên để vấn đề này sang topic khác, ông Tía à.
Trước mắt mong các cao thủ cùng chung sức làm cái TONKHO theo số lượng này trước đã.

P/S: Chưa thấy bác ANHTUAN1066 tham gia nhỉ!

 

[Mr Okebab]

To Bác ThuNghi : Bác này thật là . . !! Bác cứ làm của bác di, cho anh em tham khảo. Còn em thì cố gắng (sau) vậy


To Bác SA : Em chẳng thấy liên quan gì cả, em chỉ muốn nói rằng giữa hàm DSUM và hàm sumproduct thì nên dùng SUMPRODUCT hơn thôi (trong TH này)

To Ba: OK, cái này cũng hay, tuy nhiên chẳng lẽ lại có nhiều database thế sao ???

Thân!

 

[anhtuan1066]

P/S: Chưa thấy bác ANHTUAN1066 tham gia nhỉ!

Đại ca xỏ tôi ko hà? Biết gì về món này mà làm...

 

[BNTT]

OK, cái này cũng hay, tuy nhiên chẳng lẽ lại có nhiều database thế sao ???

Tui đâu có nói là có nhiều Database.
Ý tui là, làm cái UDF này để có thể với bất kỳ cái Database nào, miễn là có đủ các thông số: Ngày tháng, Tên hàng, Số lượng nhập, và Số lượng xuất.

@Bác ThuNghi: Anh sửa lại dùm em chút, bổ sung thêm cái vùng để chọn (Database).

 

[anhtuan1066]

Lý nào món này mà BNTT lại viết ko dc nhỉ? Hôm mới vào GPE bạn đã chào sân bằng chương trình gì đó rất hoành tráng.. Tôi nghĩ món này chỉ là con tép đối với bạn...
Tôi chẳng biết Function làm như thế nào, nhưng nếu là tôi làm thì tôi cũng sẽ làm nó bằng FOR (đễ quét các điều kiện)... Hic...

 

[BNTT]

Sau khi nghiên cứu cái của bác ThuNghi, tôi nảy ra ý này, để dễ sử dụng hơn á mà:
Bổ sung thêm tham số trong UDF:

TONKHO(DATABASE,NGAY,MAHANG,SLNHAP,SLXUAT)​

Như thế sẽ tiện hơn cho những Database mà những cột không sắp xếp sẵn theo ý mình (trường hợp này, những cái set của bác ThuNghi sẽ trật lất hết).
Nếu thiết kế UDF TONKHO như trên, chỉ cần báo cho TONKHO biết những thông số bằng cách quét chọn cột tương ứng là xong.

Mong các bạn hưởng ứng.

@Bác ANHTUAN1066: Em chả giỏi giang gì đâu bác ơi. Chỉ giỏi mỗi chuyện làm cho nó đẹp! Còn thì chẳng qua là xào nấu lại những gì sưu tầm được thôi.
Chuyện này đương nhiên là em đang nghiên cứu, nhưng mà chưa ra. Với lại kiểu của em thì thủ công và dài dòng, sau vài lần gửi lên đây, thấy các cao thủ sửa lại ngắn ngủn và rất hay, nên em mới có ý tưởng mở topic này, không những cho riêng em mà em nghĩ sẽ rất thiết thực cho nhiều bạn khác. Và biết đâu, những ý tưởng như vầy, nếu được nhiều người hưởng ứng tham gia, thì thư viện của GPE sẽ có riêng một kho UDF. Khi đó dám bác Bill hỏi mua bản quyền đó chứ.

 

[Mr Okebab]

Bổ sung thêm tham số trong UDF:

TONKHO(DATABASE,NGAY,MAHANG,SLNHAP,SLXUAT)​

Có 2 cách :

Cách 1 :

TONKHO(DATABASE,NGAY,MAHANG)​

• Hàm : TonKho
• Database : Vùng dữ liệu : Quy định sẵn là cột Ngay, Mã Hàng, SL Nhập, SL Xuất là thứ mấy : Database
• Ngày xét duyệt : NGAY
• Mã hàng xét duyệt : MAHANG

Cách 2:

TONKHO(NGAY,MAHANG,MANGNGAY, MANGMA, SLNHAP,SLXUAT)​

• Hàm : TonKho
• Ngày xét duyệt : NGAY
• Mã hàng xét duyệt : MAHANG
• Vùng Ngay : MANGNGAY
• Vùng Mã xét duyệt : MANGMA
• Vùng Số lượng nhập : SLNHAP
• Vùng Số lượng xuất : SLXUAT

Chứ của bác thì vừa thừa lại vừa thiếu


À mà cho em hỏi là cột Ngay đã sort chưa ??

Thân!

 

[BNTT]

Có 2 cách :

Cách 1 :

TONKHO(DATABASE,NGAY,MAHANG)​

• Hàm : TonKho
• Database : Vùng dữ liệu : Quy định sẵn là cột Ngay, Mã Hàng, SL Nhập, SL Xuất là thứ mấy : Database
• Ngày xét duyệt : NGAY
• Mã hàng xét duyệt : MAHANG

Cách 2:

TONKHO(NGAY,MAHANG,SLNHAP,SLXUAT)​

• Hàm : TonKho
• Ngày xét duyệt : NGAY
• Mã hàng xét duyệt : MAHANG
• Vùng Số lượng nhập : SLNHAP
• Vùng Số lượng xuất : SLXUAT

Chứ của bác thì vừa thừa lại vừa thiếu


À mà cho em hỏi là cột Ngay đã sort chưa ??

Thân!

À à... Nếu theo ý của Bap thì...
Nếu theo cách 1, thì e rằng khó, vì mình muốn dùng cho bất kỳ một Database nào, nên có thể các cột sẽ không sắp sẵn như ý mình muôn.
Còn theo cách 2, nghe cũng có lý. Thừa cái thằng Database.
Vậy theo cách này đi, ta sẽ thiết kế một cái hàm TonKho như vầy"

TONKHO(NGAY,MAHANG,SLNHAP,SLXUAT)​

Riêng cột ngày, đương nhiên phải sort rồi, từ xưa tới nay.