[發問] 關於進銷存的平均庫存成本該如何計算?

united7878

請教各位先進：

假設A、B、C三個工作表分別是A進貨，B銷貨，C庫存→(如附件 TEST2.rar (9.38 KB)

下載次數: 23

2015-6-26 07:30

)

C表的D欄為成本欄

欲計算A表因不同時間進貨所產生不同成本的均價

但必須排除B表已銷貨的部分

譬如→A表裡統計1月份IPHONE6共進貨38台

但在同月份賣了15台剩23台

已賣掉的17台是在1月16日之前進的成本

所以儲存格計算應落在1月17日之後到1月31日

而不去累計1月4日到1月16所進的單價

爾後B表有再銷貨也會自動再扣除較舊日期的成本

該公式或語法應如何撰寫?

謝謝~

united7878

再度請教G大
呈上面範例，如果要分表輸入時該怎麼更換路徑?
例如A變成[A.Xlsx'Sheet1]、B變成[B.Xlsx'Sheet1]、C變成[C.Xlsx'Sheet1]
巨集模組放在[C.Xlsx]

謝謝！

united7878

回復 4# GBKEE

G大真神人也~
之前用陣列公式，怎麼算都有誤差，VB又不太熟，
折騰多時終於求得平均成本的統計方式
但這語法太多層了，在下資質駑鈍，還在努力參悟中...

感謝賜教!!!

GBKEE

回復 3# united7878
試試看

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, Quantity(1 To 2) As Integer, Total(1 To 2) As Double
   
4.     Dim Sh As Worksheet, i(1 To 2) As Integer
   
5.     Set Rng = Sheets("C").[B2]
   
6.     Set Sh = Sheets.Add    '新增工作表
   
7.     Do While Rng <> ""
   
8.          With Sheets("A")
   
9.             .Range("a1").AutoFilter Field:=2, Criteria1:=Rng
   
10.             Quantity(1) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
    
11.             Total(1) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
    
12.         End With
    
13.         With Sheets("B")
    
14.             .Range("a1").AutoFilter Field:=2, Criteria1:=Rng
    
15.             Quantity(2) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
    
16.             Total(2) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
    
17.         End With
    
18.         With Rng
    
19.             .Cells(1, 2) = Quantity(1) - Quantity(2)             '庫存數量
    
20.             If .Cells(1, 2) > 0 Then '有庫存數量
    
21.                 If Total(2) > 0 Then  '銷貨數量
    
22.                     Total(2) = 0
    
23.                     i(1) = .Cells(1, 2)
    
24.                     With Sh
    
25.                         .UsedRange.Clear
    
26.                         Sheets("A").UsedRange.Copy .[A1]     '複製: A表自動篩選後的數值
    
27.                         i(2) = .UsedRange.Rows.Count         '資料的最後一列
    
28.                         Do While i(1) > 0        '庫存數大於 0
    
29.                             Do While .Cells(i(2), "D") > 0 And i(1) > 0
    
30.                                 Total(2) = Total(2) + .Cells(i(2), "c")
    
31.                                 i(1) = i(1) - 1   '庫存數 - 1
    
32.                                 .Cells(i(2), "D") = .Cells(i(2), "D") - 1 '進貨數量 -1
    
33.                             Loop
    
34.                             i(2) = i(2) - 1 '資料列 上移 一列
    
35.                         Loop
    
36.                     End With
    
37.                 .Cells(1, 3) = Round(Total(2) / .Cells(1, 2), 1)
    
38.                 Else  ' 銷貨數量為0
    
39.                     .Cells(1, 3) = Round((Total(1) - Total(2)) / .Cells(1, 2), 1)
    
40.                 End If
    
41.             Else   '沒有庫存數量
    
42.                 .Cells(1, 3) = 0
    
43.             End If
    
44.         End With
    
45.         Set Rng = Rng.Offset(1)
    
46.     Loop
    
47.     Application.DisplayAlerts = False
    
48.     Sh.Delete    '刪除:新增的工作表
    
49.     Application.DisplayAlerts = True
    
50. End Sub

複製代碼

united7878

回復 2# GBKEE
謝謝G大~

但反複實測後發現計算結果比手動算的還低

均價低於最低進價?!

下載 (5.61 KB)

2015-7-4 02:11

下載 (17.97 KB)

2015-7-4 02:11

該如何修正呢?

感恩!!

GBKEE

回復 1# united7878
試試看

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, Quantity(1 To 2) As Integer, Total(1 To 2) As Double
   
4.     Set Rng = Sheets("C").[B2]
   
5.     Do While Rng <> ""
   
6.          With Sheets("A")
   
7.             .Range("a1").AutoFilter Field:=2, Criteria1:=Rng
   
8.             Quantity(1) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
   
9.             Total(1) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
   
10.         End With
    
11.         With Sheets("B")
    
12.             .Range("a1").AutoFilter Field:=2, Criteria1:=Rng
    
13.             Quantity(2) = Application.Sum(.Range("d:d").SpecialCells(xlCellTypeVisible))
    
14.             Total(2) = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
    
15.         End With
    
16.         With Rng
    
17.             .Cells(1, 2) = Round(Quantity(1) - Quantity(2))
    
18.             .Cells(1, 3) = Round((Total(1) - Total(2)) / .Cells(1, 2))
    
19.         End With
    
20.         Set Rng = Rng.Offset(1)
    
21.     Loop
    
22. End Sub

複製代碼