標題:
[發問]
關於進銷存的平均庫存成本該如何計算?
[打印本頁]
作者:
united7878
時間:
2015-6-26 07:32
標題:
關於進銷存的平均庫存成本該如何計算?
請教各位先進：
假設A、B、C三個工作表分別是A進貨，B銷貨，C庫存→(如附件[attach]21261[/attach])
C表的D欄為成本欄
欲計算A表因不同時間進貨所產生不同成本的均價
但必須排除B表已銷貨的部分
譬如→A表裡統計1月份IPHONE6共進貨38台
但在同月份賣了15台剩23台
已賣掉的17台是在1月16日之前進的成本
所以儲存格計算應落在1月17日之後到1月31日
而不去累計1月4日到1月16所進的單價
爾後B表有再銷貨也會自動再扣除較舊日期的成本
該公式或語法應如何撰寫?
謝謝~
作者:
GBKEE
時間:
2015-7-1 09:48
回復
1#
united7878
試試看
Option Explicit
Sub Ex()
Dim Rng As Range, Quantity(1 To 2) As Integer, Total(1 To 2) As Double
Set Rng = Sheets("C").[B2]
Do While Rng <> ""
With Sheets("A")
.Range("a1").AutoFilter Field:=2, Criteria1:=Rng
Quantity(1) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
Total(1) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
End With
With Sheets("B")
.Range("a1").AutoFilter Field:=2, Criteria1:=Rng
Quantity(2) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
Total(2) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
End With
With Rng
.Cells(1, 2) = Round(Quantity(1) - Quantity(2))
.Cells(1, 3) = Round((Total(1) - Total(2)) / .Cells(1, 2))
End With
Set Rng = Rng.Offset(1)
Loop
End Sub
複製代碼
作者:
united7878
時間:
2015-7-4 02:11
回復
2#
GBKEE
謝謝G大~
但反複實測後發現計算結果比手動算的還低
均價低於最低進價?!
[attach]21320[/attach][attach]21321[/attach]
該如何修正呢?
感恩!!
作者:
GBKEE
時間:
2015-7-4 06:59
回復
3#
united7878
試試看
Option Explicit
Sub Ex()
Dim Rng As Range, Quantity(1 To 2) As Integer, Total(1 To 2) As Double
Dim Sh As Worksheet, i(1 To 2) As Integer
Set Rng = Sheets("C").[B2]
Set Sh = Sheets.Add '新增工作表
Do While Rng <> ""
With Sheets("A")
.Range("a1").AutoFilter Field:=2, Criteria1:=Rng
Quantity(1) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
Total(1) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
End With
With Sheets("B")
.Range("a1").AutoFilter Field:=2, Criteria1:=Rng
Quantity(2) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
Total(2) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
End With
With Rng
.Cells(1, 2) = Quantity(1) - Quantity(2) '庫存數量
If .Cells(1, 2) > 0 Then '有庫存數量
If Total(2) > 0 Then '銷貨數量
Total(2) = 0
i(1) = .Cells(1, 2)
With Sh
.UsedRange.Clear
Sheets("A").UsedRange.Copy .[A1] '複製: A表自動篩選後的數值
i(2) = .UsedRange.Rows.Count '資料的最後一列
Do While i(1) > 0 '庫存數大於 0
Do While .Cells(i(2), "D") > 0 And i(1) > 0
Total(2) = Total(2) + .Cells(i(2), "c")
i(1) = i(1) - 1 '庫存數 - 1
.Cells(i(2), "D") = .Cells(i(2), "D") - 1 '進貨數量 -1
Loop
i(2) = i(2) - 1 '資料列 上移 一列
Loop
End With
.Cells(1, 3) = Round(Total(2) / .Cells(1, 2), 1)
Else ' 銷貨數量為0
.Cells(1, 3) = Round((Total(1) - Total(2)) / .Cells(1, 2), 1)
End If
Else '沒有庫存數量
.Cells(1, 3) = 0
End If
End With
Set Rng = Rng.Offset(1)
Loop
Application.DisplayAlerts = False
Sh.Delete '刪除:新增的工作表
Application.DisplayAlerts = True
End Sub
複製代碼
作者:
united7878
時間:
2015-7-5 20:05
回復
4#
GBKEE
G大真神人也~
之前用陣列公式，怎麼算都有誤差，VB又不太熟，
折騰多時終於求得平均成本的統計方式
但這語法太多層了，在下資質駑鈍，還在努力參悟中...
感謝賜教!!!
作者:
united7878
時間:
2019-6-23 01:19
再度請教G大
呈上面範例，如果要分表輸入時該怎麼更換路徑?
例如A變成[A.Xlsx'Sheet1]、B變成[B.Xlsx'Sheet1]、C變成[C.Xlsx'Sheet1]
巨集模組放在[C.Xlsx]
謝謝！
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
