返回列表 上一主題 發帖

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

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

請教各位先進:

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

C表的D欄為成本欄

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

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

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

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

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

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

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

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

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

謝謝~

回復 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
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 2# GBKEE
謝謝G大~

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

均價低於最低進價?!
070401.png
2015-7-4 02:11
070402.png
2015-7-4 02:11

該如何修正呢?

感恩!!

TOP

回復 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
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 4# GBKEE

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

感謝賜教!!!

TOP

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

謝謝!

TOP

        靜思自在 : 有時當思無時苦,好天要積雨來糧。
返回列表 上一主題