123
返回列表 上一主題 發帖

[發問] 請問如何用excel VBA寫一個以先進先出的方式來取得產品的數量與平均價格

chen_cook

帖子
81 
主題
2 
精華
0 
積分
95 
點名
327  
作業系統
xp 
軟體版本
office 2003 
閱讀權限
20 
性別
男 
註冊時間
2012-2-29 
最後登錄
2024-11-22 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章
11# 跳轉到 »
發表於 2012-4-29 07:20 | 只看該作者
簡單說 就是第一筆出貨用第一筆進貨去扣除(由上而下),再計算餘數的平均價!!
那如果第二筆出貨大於第一筆庫存時....你要如何算??

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
12#
發表於 2012-4-29 13:32 | 只看該作者
本帖最後由 white5168 於 2012-4-29 13:34 編輯

那如果第二筆出貨大於第一筆庫存時....你要如何算??

我前面有詳細舉例,請仔細閱讀,
小弟剛接觸Excel VBA實在很不熟析,請各位大大幫忙

原始資料
                    商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200

類先買進先賣出
20120404結算時, 20120401的A產品平均買進成本,需以當時剩餘來計算即(2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300,表示20120401當天交易數量還有剩300(這是我需要的)
                       商品     價格          買進   賣出
20120401      A    1966.67   300         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0    200
而計算到20120404交易後,A產品全部的均價為(1966.67*300 + 2020*400 + 2050*400)/(300+400+400)=2016.365,數量剩為300+400+400=1100(這是我需要的)

20120405結算時, 20120401的A產品平均買進成本,需以當時剩餘來計算即(1966.67*300-2010*200)/(300-200)=1880,數量剩為300-200=100,表示20120401當天交易數量還有剩100(這是我需要的)
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
而計算到20120405交易後,A產品全部的均價為(1880*100+ 2020*400 + 2050*400)/(100+400+400)=2017.78,數量剩為100+400+400=900(這是我需要的)

第二筆出貨大於第一筆庫存的說明如下
如果再多ㄧ個20120406的交易如下
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0    300

20120406結算時, 20120401與20120403的A產品平均買進成本,需以當時剩餘來計算即(1880*100+2020*400-2040*300)/(100+400-300)=1920,數量剩為100+400-300=200,表示20120401當天交易數量已經沒有剩了,需要20120403的數量來支援才行,所以20120403剩餘200(這是我需要的)
                      商品     價格          買進   賣出
20120401      A    1880               0         0
20120402      B    1500               0    100
20120403      A    2020          200         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0         0
而計算到20120406交易後,A產品全部的均價為(2020*200 + 2050*400)/(200+400)=2040,數量剩為200+400=600(這是我需要的)

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
13#
發表於 2012-4-29 16:12 | 只看該作者
回復 12# white5168
A 項產品產品的處理過程業已了解，
日期、商品、價格、買進、賣出、產品均價 (是否增列？以便隨時能提供參考資訊)，
再請教 20120402      B    1500               0    100  這項產品，
你會怎麼來處理，且無 B 產品之進貨紀錄？

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
14#
發表於 2012-4-29 16:57 | 只看該作者
每項產品的處理方式皆相同,只差各項不同產品穿插其中,B項產品可如下表所示

                       商品     價格   買進   賣出
20120401      A    2000    500         0
20120402      B     1500        0    100
20120403      A     2020   400         0
20120403      B     1300   500    100
20120404      A     2050   400    200
20120404      B     1250        0    200
20120405      A     2010        0    700
20120405      B     1310   200         0

還有ㄧ點,能否算出每項產品賣出後的盈虧?

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
253  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-22 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
15#
發表於 2012-4-29 23:32 | 只看該作者
回復 14# white5168
原始資料
                    商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200

類先買進先賣出
20120404結算時, 20120401的A產品平均買進成本,需以當時剩餘來計算即(2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300,表示20120401當天交易數量還有剩300(這是我需要的)
依你以上敘述，20120404之前A產品共有3筆資料(20120401，A，2000，500，0)、(20120403，A，2020，400，0)、(20120404，A，2050，400，200)
既然先進先出20120404這筆賣出，應該是用20120401這個價位2000
那麼剩下的不是應該(2000*(500-200)+2020*400+2050*400)/(500+400+400-200)才是成本價位嗎?
這種專業的會計知識我一點都沒有，不知道我的理解與實務差別在哪?
建議您將想要顯示的結果直接用手算出後，填入想要實現的位置，並在隔壁欄位填入你計算的依據
這樣或許比較容易釐清所謂先進先出的概念。
學海無涯_不恥下問

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
16#
發表於 2012-4-30 00:16 | 只看該作者
[版主管理留言]
  • Hsieh(2012-4-30 10:11): 請用檔案說明，答案放置位置，與所需資料有哪些

本帖最後由 white5168 於 2012-4-30 20:15 編輯

Hsieh說的很對,感謝Hsieh糾正,是我搞錯了觀念也讓大家一起誤會了,在此跟大家說聲抱歉,耽誤大家這麼多的時間
我重新整理一下我需要內容的正確觀念

原始資料
                    商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200

類似先買進先賣出的觀念
20120404結算時,20120401的A產品平均買進成本仍為2000,數量剩為500-200=300,表示20120401當天交易數量還有剩300
                       商品     價格          買進   賣出
20120401      A     2000         300         0
20120402      B     1500               0    100
20120403      A     2020          400         0
20120404      A     2050          400         0
20120405      A     2010               0    200
而計算到20120404交易後,A產品全部的均價為(2000*300 + 2020*400 + 2050*400)/(300+400+400)=2025.5,數量剩為300+400+400=1100
20120404結算時,盈虧為(2050-2000)*200=10000

20120405結算時,20120401的A產品平均買進成本仍為2000,數量剩為300-200=100,表示20120401當天交易數量還有剩100
                      商品     價格          買進   賣出
20120401      A    2000         100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
而計算到20120405交易後,A產品全部的均價為(2000*100+ 2020*400 + 2050*400)/(100+400+400)=2031.1,數量剩為100+400+400=900
20120405結算時,盈虧為(2010-2000)*200=2000

第二筆出貨大於第一筆庫存的說明如下
如果再多ㄧ個20120406的交易如下
                      商品     價格          買進   賣出
20120401      A    2000          100         0
20120402      B    1500               0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0    300

20120406結算時,20120401的A產品平均買進成本仍為2000,當20120406交易後,由於20120401只剩下100不夠20120406賣出的數量200,需以20120403的數量來補足,所以20120403剩餘400-200=200,而20120403的A產品平均買進成本為2020
                      商品     價格          買進   賣出
20120401      A    2000               0         0
20120402      B    1500               0    100
20120403      A    2020          200         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0         0
而計算到20120406交易後,A產品全部的均價為(2020*200 + 2050*400)/(200+400)=2040,數量剩為200+400=600(這是我需要的)
20120406結算時,盈虧為(2040-2000)*100+(2040-2020)*200=8000

使用資料在ㄧ開始PO文時,即付上檔名為test.rar,請各位仔細察看

TOP

register313
　頭銜: 麻辣 路人甲

大學生Rank: 4

帖子
967 
主題
0 
精華
0 
積分
1001 
點名
0  
作業系統
WIN XP 
軟體版本
OFFICE 2003 
閱讀權限
50 
性別
男 
來自
台北 
註冊時間
2010-11-29 
最後登錄
2022-5-17 

2012龍年全勤獎章熱烈參與勳章
17#
發表於 2012-4-30 11:01 | 只看該作者
回復 16# white5168

本帖已經到了16樓了,好像答案沒有一點進展,發問者該思考這個問題

發問者的工作:提供範例(考量各種數據)作詳細清楚之說明,讓答題者一下就知道你要什麼
剩下的交給答題者

hsieh的建議:建議您將想要顯示的結果直接用手算出後，填入想要實現的位置，並在隔壁欄位填入你計算的依據
我把產品編號一樣的集中,請把你要的答案填入並作說明,這樣較清楚(或是你答案要如何呈現也另作說明)
答題者有疑惑,發問者必須要幫忙釐清,不然不會有答案,久了也會失去耐心

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
18#
發表於 2012-4-30 15:06 | 只看該作者
本帖最後由 white5168 於 2012-4-30 15:15 編輯

如圖分別為資料與最後資料
最後資料的部份對於日期是可調整的,如日期變動,則最後資料所呈現的畫面也會改變
相關的Excel檔在附件中,如有不清楚的請再提出發問
小弟比較不清楚各位所需的資訊到何種程度?所以如有冒犯請多多見諒

source.JPG (147.79 KB)

資料

source.JPG

finalscreen.JPG (120.04 KB)

呈現畫面

finalscreen.JPG

test.rar (2.85 KB)

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
253  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-22 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
19#
發表於 2012-5-1 17:07 | 只看該作者
回復 18# white5168
貼圖的資料並不是附件中CSV的資料
依照上述先進先出邏輯試著寫看看，你自己去比對看看結果正不正確
play.gif
  1. Sub Get_Data()
  2. Dim Ar(), Ay(), x, y
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. fs = ThisWorkbook.Path & "\DataBase.csv"
  7. Open fs For Input As #1
  8. Do Until EOF(1)
  9.    Line Input #1, mystr
  10.    a = Split(mystr, ",")
  11.    If Val(a(0)) > 0 And Val(a(0)) <= [B1] Then
  12.    If IsEmpty(d(a(1))) Then
  13.        For i = 1 To Val(a(3))
  14.           ReDim Preserve Ar(i)
  15.           Ar(i - 1) = Val(a(2))
  16.        Next
  17.        If Val(a(3)) > 0 Then d(a(1)) = Ar
  18.        Else
  19.        Ar = d(a(1))
  20.        s = UBound(Ar)
  21.          For i = 1 To Val(a(3))
  22.            ReDim Preserve Ar(s + i)
  23.            Ar(s + i - 1) = Val(a(2))
  24.          Next
  25.        s = UBound(Ar)
  26.          d(a(1)) = Ar
  27.     End If
  28.     If Val(a(4)) > 0 Then
  29.        If IsEmpty(d1(a(1))) Then
  30.        For i = 1 To Val(a(4))
  31.           ReDim Preserve Ar(i)
  32.           Ar(i - 1) = Val(a(2))
  33.        Next
  34.        If Val(a(4)) > 0 Then d1(a(1)) = Ar
  35.        Else
  36.        Ar = d1(a(1))
  37.        s = UBound(Ar)
  38.          For i = 1 To Val(a(4))
  39.            ReDim Preserve Ar(s + i)
  40.            Ar(s + i - 1) = Val(a(2))
  41.          Next
  42.          d1(a(1)) = Ar
  43.     End If
  44.     End If
  45.     End If
  46.    Erase Ay: Erase Ar
  47. Loop
  48. Close #1
  49. For Each ky In d1.keys
  50.    If IsArray(d1(ky)) Then Ar = d1(ky): x = UBound(Ar) Else x = 0 '出貨
  51.    If IsArray(d(ky)) Then Ay = d(ky): y = UBound(Ay) Else y = 0 '進貨
  52.    If x = 0 And y > 0 Then '只進不出
  53.       For i = 0 To y - 1
  54.         'sp = sp + Ar(i)
  55.         bp = bp + Ay(i)
  56.       Next
  57.       bp = bp / y
  58.       d2(ky) = Array(ky, y, 0, 0, Abs(y - x), y - x, Round(bp, 2), 0)
  59.       bp = 0
  60.       ElseIf y = 0 And x > 0 Then '只出不進
  61.       For i = 0 To x - 1
  62.         sp = sp + Ar(i)
  63.       Next
  64.       sp = sp / x
  65.       d2(ky) = Array(ky, y, x, 0, 0, y - x, 0, Round(sp, 2))
  66.       sp = 0
  67.       ElseIf x > 0 And y > 0 Then
  68.          If x > y Then '出大於進
  69.          w = 0: w1 = y - x
  70.          For i = 0 To y - 1
  71.          pr = pr + Ar(i) - Ay(i)
  72.          Next
  73.          For j = i To x - 1
  74.          nr = nr + Ar(i)
  75.          Next
  76.          nr = nr / (x - y) '不足量
  77.          ElseIf x < y Then '進大於出
  78.          w1 = 0: w = y - x
  79.          For i = 0 To x - 1
  80.          pr = pr + Ar(i) - Ay(i)
  81.          Next
  82.          For j = i To y - 1
  83.          sr = sr + Ay(i)
  84.          Next
  85.          sr = sr / Abs(x - y) '不足量
  86.          End If
  87.          
  88.          d2(ky) = Array(ky, y, x, pr, w, w1, Round(sr, 2), Round(nr, 2))
  89.          pr = 0: nr = 0: sr = 0
  90.    End If
  91.    Erase Ay: Erase Ar
  92. Next
  93. [A4:H65536] = ""
  94. [A4].Resize(d2.Count, 8) = Application.Transpose(Application.Transpose(d2.items))
  95. End Sub
複製代碼
學海無涯_不恥下問

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
20#
發表於 2012-5-1 17:26 | 只看該作者
很感謝Hsieh大大幫忙,不過目前我還無法下載所有大大所回覆的附件,只能等到升級後才能看到結果
想請問Hsieh大大, Excel VBA的程式,是否能做到跟C,C++等高階語言一樣模組化的程度?
這裡我要澄清說明,附件的原始資料的確是貼圖的資料,我只是將原始資料中比較簡單的不複雜的資料項剔除
貼圖的資料是我將原始資料留下複雜的部份來做說明用的
在撰寫程式的過程中會遇到問題的資料,基本上程式撰寫只要複雜的能寫的出來,其他的都不成問題
可以請Hsieh大大自行刪除比較不複雜且簡單的資料即可看出跟貼圖資料相同的內容
謝謝

TOP

        靜思自在 : 要批評別人時，先想想自己是否完美無缺。
123
返回列表 上一主題