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

  • Thread starter Thread starter BNTT
  • Ngày gửi Ngày gửi
Liên hệ QC

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

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
BNTT

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia
3/7/07
Bài viết
4,946
Được thích
23,213
Nghề nghiệp
Dạy đàn piano
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)
 
Lần chỉnh sửa cuối:
Sắp xếp theo thời gian sắp xếp theo bầu chọn
BNTT đã viết:
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)
Nhấp chuột vào đây để mở rộng...
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.
 
Lần chỉnh sửa cuối:
Upvote 0
ThuNghi đã viết:
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.
Nhấp chuột vào đây để mở rộng...
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).
 
Upvote 0
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.
 
Upvote 0
Lần chỉnh sửa cuối:
Upvote 0
BNTT đã viết:
Đú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.
Nhấp chuột vào đây để mở rộng...
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
Nhấp chuột vào đây để mở rộng...
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
BNTT đã viết:
Đú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.
Nhấp chuột vào đây để mở rộng...
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!
 
Upvote 0
Sao không DSUM?

BNTT đã viết:
Đú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.
Nhấp chuột vào đây để mở rộng...

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
 

File đính kèm

Upvote 0
SA_DQ đã viết:
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
Nhấp chuột vào đây để mở rộng...

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

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

Thân!
 

File đính kèm

Upvote 0
Mr Okebab đã viết:
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!
Nhấp chuột vào đây để mở rộng...
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.
 
Upvote 0
Mr Okebab đã viết:
Nếu đã bàn đến hàm của excel :

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

Thân!
Nhấp chuột vào đây để mở rộng...

To BAB:

http://giaiphapexcel.com/forum/showthread.php?t=5294
 
Upvote 0
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.
 
Upvote 0
Mr Okebab đã viết:
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
Nhấp chuột vào đây để mở rộng...
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ỉ!
 
Upvote 0
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!
 
Upvote 0
Mr Okebab đã viết:
OK, cái này cũng hay, tuy nhiên chẳng lẽ lại có nhiều database thế sao ???
Nhấp chuột vào đây để mở rộng...
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).
 
Upvote 0
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...
 
Upvote 0
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ứ.
 
Lần chỉnh sửa cuối:
Upvote 0
BNTT đã viết:

Bổ sung thêm tham số trong UDF:
TONKHO(DATABASE,NGAY,MAHANG,SLNHAP,SLXUAT)​
Nhấp chuột vào đây để mở rộng...
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!
 
Lần chỉnh sửa cuối:
Upvote 0
Mr Okebab đã viết:
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!
Nhấp chuột vào đây để mở rộng...
À à... 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.
 
Lần chỉnh sửa cuối:
Upvote 0
Trong dữ liệu có những chứng từ CƠ BẢN sau đây ảnh có ảnh hưởng tới việc tính NXT về số lượng:

1/ Dư đầu kỳ:
- Chứng từ "Dư đầu kỳ"
2/ Phát sinh tăng:
- Chứng từ "Phiếu nhập hàng"
- Chứng từ "Phiếu nhập hàng Khách hàng trả lại"
- Chứng từ "Phiếu điều chuyển" (Kho chuyển đến)
- Chứng từ "Lắp ráp/tháo dỡ" (Tăng "thành phẩm" hay "vật tư")
- Chứng từ "Phiếu điều chỉnh - kiểm kê" (chênh lệch thực tế so với sổ sách là > 0)
3/ Phát sinh giảm
- Chứng từ "Phiếu xuất hàng" (bán buôn, bán lẻ, sản xuất,...)
- Chứng từ "Phiếu xuất hàng trả lại NCC"
- Chứng từ "Phiếu điều chuyển" (Kho chuyển đi)
- Chứng từ "Lắp ráp/tháo dỡ" (Giảm "thành phẩm" hay "vật tư")
- Chứng từ "Phiếu điều chỉnh - kiểm kê" (chênh lệch thực tế so với sổ sách là < 0)

Khi tính số dư tại 1 thời điểm thì người ta thường hay có mục đích sau:
- Hiển thị danh sách hàng hóa vật tư với số dư tồn kho trong 1 kho hàng tại một thời điểm (gọi chung là ngày báo cáo).
- Tính giá trị tồn kho của 1 mặt hàng trong 1 kho tại 1 thời điểm (gọi chung là ngày báo cáo).

Kỳ ở đây được hiểu là tháng của ngày báo cáo
ví dụ: Thời điểm báo cáo là 07/12/2007 thì Kỳ = 12

Số dư (tại thời điểm) = Dư đầu kỳ(thời điểm) + PS tăng (thời điểm) - PS giảm (thời điểm)

Nếu các bạn thiết kế các chứng từ độc lập (dạng Master/Detail) trong hệ thống CSDL thì với cách hiểu nói trên, chỉ cần khéo léo 1 tý là các bạn có thể viết đúng 1 câu SQL có sự kết hợp của Union All và hàm Sum() để tính số dư thời điểm và báo cáo Nhập-Xuất-Tồn một cách cực kỳ nhanh.
 
Upvote 0
BNTT đã viết:

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.
Nhấp chuột vào đây để mở rộng...

Làm gì có UDF nào mà "muốn dùng cho bất kỳ một Database nào" , các data có ai đặt thống nhất đâu.
TONKHO(NGAY,MAHANG,SLNHAP,SLXUAT)
Thiếu mất ngay_array, mahang_array. Mà chỉ cần lấy cột thứ mấy của 1 data set trước, phải lấy từ đầu ->NGAY.
Tôi đề xuất tạo sẵn 1 Range Data. Các cột thay đổi thì đổi IMa...
Chẳng lẽ:
TONKHO(NGAY,MAHANG,Col(Mahang), Col(Ngay), Col(SLNhap),Col(SLXuat))
Nó thế nào...
Vậy dùng tạm cái này
PHP: 
[quote]Option Explicit
Dim Ngay As Range, MaHH As Range, SLNhap As Range, SLXuat As Range, nname As String
Dim eRow As Long, iDate As Long, iMaHH As Long, i As Long, Tmp As Double
Private Const iNgay = 1 'cot ngay trong data
Private Const iMa = 2
Private Const iNhap = 3
Private Const iXuat = 4
Public Function TKCK(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
'Kiem tra name Data
nname = "Data"
If RangeNameExists(nname) = False Then
    MsgBox "Chua co range Data"
    Exit Function
End If
eRow = Range("Data").Rows.Count
Set Ngay = WorksheetFunction.Index(Range("Data"), 0, iNgay)
Set MaHH = WorksheetFunction.Index(Range("Data"), 0, iMa)
iDate = WorksheetFunction.CountIf(Ngay, ">=" & CLng(Ngay_Value))
iMaHH = WorksheetFunction.CountIf(MaHH, MaHH_Value)
'HangCuoi = sht.UsedRange.Rows.Count + sht.UsedRange.Row
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 = WorksheetFunction.Index(Range("Data"), 0, iMa)
Set SLNhap = WorksheetFunction.Index(Range("Data"), 0, iNhap)
Set SLXuat = WorksheetFunction.Index(Range("Data"), 0, iXuat)
For i = 1 To eRow
    If UCase(MaHH(i)) = UCase(MaHH_Value) Then
        Tmp = Tmp + SLNhap(i) - SLXuat(i)
    End If
Next
TKCK = Tmp
Tmp = 0
Set Ngay = Nothing
Set MaHH = Nothing
Set SLNhap = Nothing
Set SLXuat = Nothing

End Function
Public Function RangeNameExists(nname) As Boolean
    Dim n As Name
    RangeNameExists = False
    For Each n In ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            RangeNameExists = True
            Exit Function
        End If
    Next n
End Function

[/quote]
 
Lần chỉnh sửa cuối:
Upvote 0
Smart Indenter Version 3.5

Các bác nên dùng cái này:
http://www.giaiphapexcel.com/forum/showthread.php?t=3921

để canh chỉnh code trước khi post bài cho mọi người dễ đọc.

Code của anh ThuNghi sau khi dùng Smart Indenter Version 3.5

PHP: 
Option Explicit
Dim Ngay As Range, MaHH As Range, SLNhap As Range, SLXuat As Range, nname As String
Dim eRow As Long, iDate As Long, iMaHH As Long, i As Long, Tmp As Double
Private Const iNgay = 1    'cot ngay trong data
Private Const iMa = 2
Private Const iNhap = 3
Private Const iXuat = 4

Public Function TKCK(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
'Kiem tra name Data
    nname = "Data"
    If RangeNameExists(nname) = False Then
        MsgBox "Chua co range Data"
        Exit Function
    End If
    eRow = Range("Data").Rows.Count
    Set Ngay = WorksheetFunction.Index(Range("Data"), 0, iNgay)
    Set MaHH = WorksheetFunction.Index(Range("Data"), 0, iMa)
    iDate = WorksheetFunction.CountIf(Ngay, ">=" & CLng(Ngay_Value))
    iMaHH = WorksheetFunction.CountIf(MaHH, MaHH_Value)
    'HangCuoi = sht.UsedRange.Rows.Count + sht.UsedRange.Row
    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 = WorksheetFunction.Index(Range("Data"), 0, iMa)
    Set SLNhap = WorksheetFunction.Index(Range("Data"), 0, iNhap)
    Set SLXuat = WorksheetFunction.Index(Range("Data"), 0, iXuat)
    For i = 1 To eRow
        If UCase(MaHH(i)) = UCase(MaHH_Value) Then
            Tmp = Tmp + SLNhap(i) - SLXuat(i)
        End If
    Next
    TKCK = Tmp
    Tmp = 0
    Set Ngay = Nothing
    Set MaHH = Nothing
    Set SLNhap = Nothing
    Set SLXuat = Nothing
End Function

Public Function RangeNameExists(nname) As Boolean
    Dim n As Name
    RangeNameExists = False
    For Each n In ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            RangeNameExists = True
            Exit Function
        End If
    Next n
End Function


FB.
 
Upvote 0
Nghiên cứu tới lui, vẫn thấy phải có tham số DATABASE thôi MrOKBap à.
Để cho nó hiểu rằng mình truy xuất từ cái bảng nào. Bảng này có thể sẽ được chỉ định bằng địa chỉ tuyệt đối hoặc bằng cách đặt tên.
Sau đó, chỉ cho nó sử dụng tham số SLNHAP và SLXUAT là 2 cái cột nào trong bảng đó.

Hình như trong những bài trên, vấn đề nêu ra chưa được hiểu rõ lắm, có thể do tôi diễn đạt chưa kỹ.

Xin được diễn giải lại:

Database là một bảng bất kỳ (bất kỳ ở đây có nghĩa là có thể nằm trong bất kỳ một file nào đó hoặc một sheet nào đó), định dạng của Database có thể sẽ mỗi cái mỗi khác. Miễn là:
- Cột Ngày Tháng là cột đầu tiên trong Database (giống như hàm Vlookup tìm kiếm dựa vào cột đầu tiên) và đã được sort rồi.
- Trong Database này có ít nhất 3 cột nữa: Mã Hàng Hóa, Số Lượng Nhập và Số Lượng Xuất.

Giả sử sau khi nhận được cái Database này, ta sẽ đặt tên:
- khối cell chứa dữ liệu cần truy xuất là DATABASE
- khối cell chứa tên hàng là MAHANG
- khối cell (cột) chứa ngày tháng là NGAY
- khối cell (cột) chứa số lượng nhập là SLNHAP
- khối cell (cột) chứa số lương xuât là SLXUAT

Vậy bây giờ yêu cầu là làm được 1 cái hàm TONKHO, với cú pháp:
TONKHO(DATABASE,NGAY,MAHANG,SLNHAP,SLXUAT)​
Khi chạy, yêu cầu là:
Lấy cái MAHANG, đi dò trong DATABASE, tính ra số lượng tồn kho tính tới thời điểm NGAY (vẫn giả sử tồn kho đầu kỳ = 0)
2 tham số SLNHAP và SLXUAT là vị trí khối cell trong DATABASE chứa số lượng nhập và số lượng xuất.

Xin gửi kèm theo đây một cái Database đã được đặt tên sẵn cho các khối cell cần phải đặt, trong đó, các cột ngày tháng, mã hàng, số lượng nhập và số lương xuất không nằm liên tục nhau.

Dựa vào file Book2.xls này, ý đồ của tôi là có được một cái hàm để khi ta gõ (ở bất kỳ cell nào ngoài cái Database hoặc sử dụng trong VBA):
= TONKHO(DATABASE,"9/10/2007","G_3030",SLNHAP,SLXUAT)​
Thì sẽ cho ra số lượng tồn kho của mặt hàng G_3030 tại thời điểm ngày 9/10/2007.

---------------------
Xin nói rõ thêm, ở đây chúng ta có thể sử dụng những hàm như SUMIF, SUMPRODUCT để đạt được yêu cầu trên.
Nhưng ý định khi mở topic này là không dùng những hàm có sẵn của Excel, mà tự chế ra một hàm khác, trong VBA, để có thể sử dụng trong cả Excel lẫn VBA, nên mong các bác không đưa ra các giải đáp mà sử dụng hàm có sẵn.
 

File đính kèm

Upvote 0
Bác ThuNghi ơi, áp dụng cái của bác vào cái Database trong Book2 không được. Nó cứ báo là "Truy xuất không đúng" miết thôi.

Nhân đây cũng xin phép cho tôi nói điều này, không phải là tôi chỉ nêu vấn đề ra rồi ngồi đó chờ mọi người giải quyết dùm thôi đâu. Tôi cũng đang mày mò làm, tuy nhiên chưa đưa lên đây được vì nó chẳng ra làm sao cả, chạy không được, thiếu lên thiếu xuống, báo lỗi liên tục... điên cái đầu từ sáng giờ, hic.
 
Upvote 0
BNTT đã viết:
Nghiên cứu tới lui, vẫn thấy phải có tham số DATABASE thôi MrOKBap à.
Để cho nó hiểu rằng mình truy xuất từ cái bảng nào. Bảng này có thể sẽ được chỉ định bằng địa chỉ tuyệt đối hoặc bằng cách đặt tên.
Sau đó, chỉ cho nó sử dụng tham số SLNHAP và SLXUAT là 2 cái cột nào trong bảng đó.

Hình như trong những bài trên, vấn đề nêu ra chưa được hiểu rõ lắm, có thể do tôi diễn đạt chưa kỹ.

Xin được diễn giải lại:

Database là một bảng bất kỳ (bất kỳ ở đây có nghĩa là có thể nằm trong bất kỳ một file nào đó hoặc một sheet nào đó), định dạng của Database có thể sẽ mỗi cái mỗi khác. Miễn là:
- Cột Ngày Tháng là cột đầu tiên trong Database (giống như hàm Vlookup tìm kiếm dựa vào cột đầu tiên) và đã được sort rồi.
- Trong Database này có ít nhất 3 cột nữa: Mã Hàng Hóa, Số Lượng Nhập và Số Lượng Xuất.

Giả sử sau khi nhận được cái Database này, ta sẽ đặt tên:
- khối cell chứa dữ liệu cần truy xuất là DATABASE
- khối cell chứa tên hàng là MAHANG
- khối cell (cột) chứa ngày tháng là NGAY
- khối cell (cột) chứa số lượng nhập là SLNHAP
- khối cell (cột) chứa số lương xuât là SLXUAT

Vậy bây giờ yêu cầu là làm được 1 cái hàm TONKHO, với cú pháp:
TONKHO(DATABASE,NGAY,MAHANG,SLNHAP,SLXUAT)​
Khi chạy, yêu cầu là:
Lấy cái MAHANG, đi dò trong DATABASE, tính ra số lượng tồn kho tính tới thời điểm NGAY (vẫn giả sử tồn kho đầu kỳ = 0)
2 tham số SLNHAP và SLXUAT là vị trí khối cell trong DATABASE chứa số lượng nhập và số lượng xuất.

Xin gửi kèm theo đây một cái Database đã được đặt tên sẵn cho các khối cell cần phải đặt, trong đó, các cột ngày tháng, mã hàng, số lượng nhập và số lương xuất không nằm liên tục nhau.

Dựa vào file Book2.xls này, ý đồ của tôi là có được một cái hàm để khi ta gõ (ở bất kỳ cell nào ngoài cái Database hoặc sử dụng trong VBA):
= TONKHO(DATABASE,"9/10/2007","G_3030",SLNHAP,SLXUAT)​
Thì sẽ cho ra số lượng tồn kho của mặt hàng G_3030 tại thời điểm ngày 9/10/2007.

---------------------
Xin nói rõ thêm, ở đây chúng ta có thể sử dụng những hàm như SUMIF, SUMPRODUCT để đạt được yêu cầu trên.
Nhưng ý định khi mở topic này là không dùng những hàm có sẵn của Excel, mà tự chế ra một hàm khác, trong VBA, để có thể sử dụng trong cả Excel lẫn VBA, nên mong các bác không đưa ra các giải đáp mà sử dụng hàm có sẵn.
Nhấp chuột vào đây để mở rộng...

Phức tạp nhỉ, đây là UF của em.

Khi chọn xong Database thì nhập cố cột ngày, mã . . . vào,
tức là trong Database thì nó là cột thứ mấy

PHP: 
Function TonKho(MaHang As String, Ngay As Date, DTBS As Range, CotNgay As Byte, CotMa As Byte, CotNhap As Byte, CotXuat As Byte) As Long
    Application.Volatile (False)
    Dim i As Integer
    If Len(MaHang) = 0 Then Exit Function
    If DTBS.Rows.Count < 1 Then Exit Function
    If DTBS.Columns.Count < 4 Then Exit Function
    For i = 1 To DTBS.Rows.Count
        If DTBS(i, CotNgay) > Ngay Then Exit For
        If DTBS(i, CotMa) = MaHang Then TonKho = TonKho + DTBS(i, CotNhap) - DTBS(i, CotXuat)
    Next
    Set DTBS = Nothing
End Function


Thân!
 

File đính kèm

Upvote 0
Ô! Đúng là MrOKBap! Tài thật! Xin cảm ơn rất nhiều.
Sao ông Tía có thể làm ngắn thế nhỉ ?

Tuy nhiên, Bap ơi, có vấn đề đây.

Giả sử mình gõ (vào trong cái Book2.xla đã post lên) như sau:
=TonKho("G_3030","30/9/2007",DATA,1,10,12,13)
Đáp số sẽ là 846 (m2)

Nhưng nếu dùng SUMIF để kiểm tra lại thì nó phải bằng 846.2 (m2) cơ!
Bap làm mất của mình 0,2 m2 rồi... Hihi

Tại Bap dùng Tonkho... As Long đấy, sửa lại As Double thì mới có được 0,2 m2 này.

Cho mình hỏi thêm. Có cách nào, thay vì nhập số thứ tự của những cột tương ứng, thì mình nhập thẳng tên (Name) của nó không?

Thứ hai nữa, giải thích dùm mình cái này: Application.Volatile (False), hổng hiểu nơi...

Cảm ơn nhiều.
 
Upvote 0
BNTT đã viết:
Ô! Đúng là MrOKBap! Tài thật! Xin cảm ơn rất nhiều.
Sao ông Tía có thể làm ngắn thế nhỉ ?

Tuy nhiên, Bap ơi, có vấn đề đây.

Giả sử mình gõ (vào trong cái Book2.xla đã post lên) như sau:
=TonKho("G_3030","30/9/2007",DATA,1,10,12,13)
Đáp số sẽ là 846 (m2)

Nhưng nếu dùng SUMIF để kiểm tra lại thì nó phải bằng 846.2 (m2) cơ!
Bap làm mất của mình 0,2 m2 rồi... Hihi

Tại Bap dùng Tonkho... As Long đấy, sửa lại As Double thì mới có được 0,2 m2 này.

Cho mình hỏi thêm. Có cách nào, thay vì nhập số thứ tự của những cột tương ứng, thì mình nhập thẳng tên (Name) của nó không?

Thứ hai nữa, giải thích dùm mình cái này: Application.Volatile (False), hổng hiểu nơi...

Cảm ơn nhiều.
Nhấp chuột vào đây để mở rộng...
  1. Vì Ngay em khai báo là date, còn của bác thì vẫn thể hiện là chuỗi, nếu muốn như bác thì thêm cái Val hoặc Cdbl nữa là xong (chuyển text sang số)
  2. Uh, không nghĩ là có thập phân
  3. Nếu dùng Name như bác thì lại là TH2 rồi (không khai báo Database)
  4. Bác đọc qua xíu nhé (Lười quá đấy)
Volatile Method

See AlsoApplies ToExampleSpecifics
Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.
expression.Volatile(Volatile)
expression Required. An expression that returns an Application object.
Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True
Example

This example marks the user-defined function "My_Func" as volatile. The function will be recalculated whenever calculation occurs in any cells on the worksheet on which this function appears.
Function My_Func() Application.Volatile ' ' Remainder of the function ">End Function


Thân!
 
Upvote 0
BNTT đã viết:


Cho mình hỏi thêm. Có cách nào, thay vì nhập số thứ tự của những cột tương ứng, thì mình nhập thẳng tên (Name) của nó không?

Cảm ơn nhiều.
Nhấp chuột vào đây để mở rộng...

Bác xem nhé :
PHP: 
Function TonKho1(MaHang As String, Ngay As Date, _
                MangNgay As Range, MangMa As Range, MangNhap As Range, MangXuat As Range) As Long
    Application.Volatile (False)
    Dim i As Integer
    If Len(MaHang) = 0 Then Exit Function
    If MangNgay.Columns.Count > 1 Then Exit Function
    If MangMa.Columns.Count > 1 Then Exit Function
    If MangNhap.Columns.Count > 1 Then Exit Function
    If MangXuat.Columns.Count > 1 Then Exit Function
    If Not (MangNgay.Rows.Count = MangMa.Rows.Count And MangNgay.Rows.Count = MangNhap.Rows.Count _
            And MangNgay.Rows.Count = MangXuat.Rows.Count) Then Exit Function
    For i = 1 To MangNgay.Rows.Count
        If MangNgay(i) <= Ngay Then
            If MangMa(i) = MaHang Then TonKho1 = TonKho1 + MangNhap(i) - MangXuat(i)
        Else: Exit For: End If
    Next
    Set MangNgay = Nothing
    Set MangMa = Nothing
    Set MangNhap = Nothing
    Set MangXuat = Nothing
End Function
 

File đính kèm

Upvote 0
Tính tồn cho kho nào vậy? :)

Với lại ko hiểu MangNhap, MangXuat là gì?

Hình như các bạn đang tính NXT từ 1 sổ Nhật ký kho hàng tổng hợp (1 danh sách có hàng hóa, số lượng nhập, số lượng xuất) hay sao ấy chứ ko phải từ danh sách các chứng từ. Mình tưởng danh sách mỗi loại chứng từ nó phải nằm riêng rẽ các Sheets khác nhau chứ nhỉ? (Chứng từ nhập hàng để ở 1 nơi, chứng từ xuất hàng ở 1 nơi, v.v....). Thế các bạn cho chung vào 1 Sheet hết cả à?

Theo mình nghĩ, thông tin chứng từ cần thể hiện trên 2 sheets như sau:
Ví dụ Chứng từ "Phiếu nhập hàng"

Sheet1: Chứa nội dung chính của chứng từ

Mã chứng từ, Số chứng từ, Ngày chứng từ, Mã NCC, Mã Kho hàng, v.v....
1, PN001,'01/01/2007',1,1,...
2, PN002,'01/01/2007',1,1,...
....
n,.....

Sheet2: Chứa nội dung dòng hàng của chứng từ
STT, Mã chứng từ (Link với Mã CT ở trên), Mã hàng, Số lượng, Đơn giá, Thuế GTGT,....
1, 1, HH0001, 10, 12.000, VAT5,...
2, 1, HH0002, 5, 11.000, VAT5,...
3, 1, HH0003, 7, 13.000, VAT5,...
-----------------------------------
1, 2, HH0001, 10, 12.000, VAT5,...
2, 2, HH0002, 5, 11.000, VAT5,...
----------------------------------
.....
----------------------------------
1, n, HH001,....
 
Lần chỉnh sửa cuối:
Upvote 0
hai2hai đã viết:
Tính tồn cho kho nào vậy? :)

Với lại ko hiểu MangNhap, MangXuat là gì?

Hình như các bạn đang tính NXT từ 1 sổ Nhật ký kho hàng tổng hợp (1 danh sách có hàng hóa, số lượng nhập, số lượng xuất) hay sao ấy chứ ko phải từ danh sách các chứng từ. Mình tưởng danh sách mỗi loại chứng từ nó phải nằm riêng rẽ các Sheets khác nhau chứ nhỉ? (Chứng từ nhập hàng để ở 1 nơi, chứng từ xuất hàng ở 1 nơi, v.v....). Thế các bạn cho chung vào 1 Sheet hết cả à?
Nhấp chuột vào đây để mở rộng...
Bác Hai2hai thân, bài này không đề cập đến một kho cụ thể nào cả.
Nếu muốn làm hàm tính tồn kho theo ý của bác, thì em đã đưa topic này qua bõ Kế toán rồi.

Em chỉ muốn nêu vấn đề này ra, trước là làm một cái hàm tính tồn kho cho một một bảng tính bất kỳ (bất kỳ ở đây có nghĩa là có thể nằm trong bất kỳ một file nào đó hoặc một sheet nào đó), định dạng của nó có thể sẽ mỗi cái mỗi khác. Miễn là:
- Cột Ngày Tháng là cột đầu tiên và đã được sort rồi.
- Và có ít nhất 3 cột nữa: Mã Hàng Hóa, Số Lượng Nhập và Số Lượng Xuất.

Em nghĩ, hàm này sẽ có nhiều ứng dụng cho những bạn cần tìm số lượng tồn kho, cho một cửa hàng bán lẻ chẳng hạn, hay một cái kho be bé... Không nhất thiết phải làm kế toán, làm sổ sách chứng từ... Tuy nhiên vẫn có thể áp dụng cho kế toán, khi đó có thể sửa đổi đôi chút.

Thứ hai là, chủ yếu của em khi mở topic này, là chia sẻ khả năng viết hàm tự tạo. Thông qua hàm Tonkho này, với những trao đổi trên đây, có thể vận dụng để sáng tác ra nhiều loại hàm khác...

Thân.
 
Upvote 0
Cảm ơn Ông Tía nhiều.
Nhưng nè, cái Tonkho1 cũng phải set nó là Double chứ hả? Thấy Bap đã sửa cái Tonkho thành Double rồi mà Tonkho1 thì vẫn là Long.
Trong ví dụ của Bap, thì KIENG, là tấm kính, nên không có sổ lẻ, chứ gạch thì lẻ tới 4 số đấy Bap ơi.

Còn cái vụ Volatile, hì hì, hỏng phải lười đâu, nhưng khả năng tiếng Anh của mình thì chỉ có hạn thôi... Mình đã lùng trong Help và đã đọc qua rồi, nhưng không hiểu lắm. Nếu Bap rảnh, dịch sơ sơ dùm chút đi.


Nếu không có bác nào có giải pháp khác, em xin tạm đưa ra cú pháp hàm này nhé. Tác giả xin ghi là Mr OkBap.
PHP: 
Function TonKho(MaHang As String, Ngay As Date, _
                MangNgay As Range, MangMa As Range, MangNhap As Range, MangXuat As Range) As Double
    Application.Volatile (False)
    Dim i As Integer
    If Len(MaHang) = 0 Then Exit Function
    If MangNgay.Columns.Count > 1 Then Exit Function
    If MangMa.Columns.Count > 1 Then Exit Function
    If MangNhap.Columns.Count > 1 Then Exit Function
    If MangXuat.Columns.Count > 1 Then Exit Function
    If Not (MangNgay.Rows.Count = MangMa.Rows.Count And MangNgay.Rows.Count = MangNhap.Rows.Count _
            And MangNgay.Rows.Count = MangXuat.Rows.Count) Then Exit Function
    For i = 1 To MangNgay.Rows.Count
        If MangNgay(i) <= Ngay Then
            If MangMa(i) = MaHang Then TonKho = TonKho + MangNhap(i) - MangXuat(i)
        Else: Exit For: End If
    Next
    Set MangNgay = Nothing
    Set MangMa = Nothing
    Set MangNhap = Nothing
    Set MangXuat = Nothing
End Function
Trong đó:
  • MaHang: Mã hàng cần kiểm tồn kho
  • Ngay: Ngày cần kiểm số lượng tồn kỳ (kể từ ngày đầu tiên nhập mặt hàng nay)
  • MangNgay: Cột chứa danh sách Ngày Tháng Năm
  • MangMa: Cột chứa danh sách Mã Hàng Hóa
  • MangNhap: Cột chứa Số Lượng Nhập
  • MangXuat: Cột chứa Số Lượng Xuất

Nhân tiện, cũng xin đưa sang bài UDF tính công nợ tại một thời điểm bất kỳ một hàm tương tự.
 
Lần chỉnh sửa cuối:
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

Bài viết mới nhất

T
Em cần hỗ trợ tách nhiều file và đặt tên theo quy ước chung từ bảng 01 excel có sẳng (4 người xem)
Trả lời
0
Đọc
1
tailh@tanlongrice
T
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (6 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3 4
Trả lời
391
Đọc
46K
datnguyenxuan
D
Q
Không chỉnh sửa, copy được hình ảnh trong file excel.
Trả lời
6
Đọc
4K
thaivanthao
T
L
Nhờ giúp file word (2 người xem)
Trả lời
1
Đọc
88
minhminh27
minhminh27
GPE-Trợ Lý
[GPE] TASKSHOW 14: TỪ BẢNG TÍNH ĐẾN BÁO CÁO ĐỘNG - BẮT ĐẦU VỚI LOOKER STUDIO
    • Thích
Trả lời
1
Đọc
11K
GPE-Trợ Lý
GPE-Trợ Lý
dungluyen2119
  • Question Question
Code VBA để tính tổng theo chiều ngang mà số cột không cố định (1 người xem)
Trả lời
5
Đọc
261
dungluyen2119
dungluyen2119
8
Theo dõi bán hàng (3 người xem)
Trả lời
10
Đọc
315
84huong
8
L
  • Question Question
Nhờ sửa code ChrW ()
Trả lời
2
Đọc
97
le_vis
L
N
  • Question Question
Hỗ trợ tiếng việt trong macro
Trả lời
12
Đọc
531
Quang_Hải
Quang_Hải
D
xin hỏi về lỗi mô đun ẩn. (1 người xem)
Trả lời
6
Đọc
299
du trung kien
D
haohvnh
Lỗi hàm SUM không hiển thị ra kết quả
Trả lời
19
Đọc
45K
ptm0412
ptm0412
minhduc1o2
Hình ảnh bị lỗi #VALUE (1 người xem)
Trả lời
5
Đọc
177
minhduc1o2
minhduc1o2
ongke0711
  • Question Question
Tra cứu thông tin mã số thuế (cập nhật lại code cũ)
    • Thích
    • Cảm ơn
    • Yêu thích
Trả lời
5
Đọc
317
ongke0711
ongke0711
befaint
Chia sẻ tâm tư, xả nỗi lòng
    • Thích
6 7 8
Trả lời
761
Đọc
61K
SA_DQ
SA_DQ
S
  • Question Question
Object.close và Set Object = nothing
Trả lời
14
Đọc
1K
nguyendang95
N
H
Lỗi khi sort data xin được hỗ trợ (2 người xem)
Trả lời
2
Đọc
129
hunglq48
H
Nguyenkhang2404
Tính định mức sản phẩm
    • Chấp nhận
Trả lời
9
Đọc
372
Nguyenkhang2404
Nguyenkhang2404
T
Ngắt kết nối với tệp lệnh đính kèm (1 người xem)
Trả lời
2
Đọc
137
TungXauGiai
T
ongke0711
  • Question Question
Trộn dữ liệu từ Excel sang Word mở rộng (phiên bản ongke0711) (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3
Trả lời
277
Đọc
35K
joker102
J
S
tỷ lệ % không khớp khi cộng tổng
Trả lời
2
Đọc
115
snoobcool
S
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

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

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom