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

　頭銜: 麻辣路人甲

帖子: 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

中學生 Rank: 2

帖子: 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

Hsieh

超級版主 Rank: 8 Rank: 8

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

2025年全勤獎章 2024年全勤獎章 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

中學生 Rank: 2

帖子: 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

c_c_lai

博士班 Rank: 6 Rank: 6

帖子: 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

中學生 Rank: 2

帖子: 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

chen_cook

中學生 Rank: 2

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

2025年全勤獎章 2024年全勤獎章 2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

11^#

發表於 2012-4-29 07:20 | 只看該作者

簡單說就是第一筆出貨用第一筆進貨去扣除(由上而下),再計算餘數的平均價!!
那如果第二筆出貨大於第一筆庫存時....你要如何算??

TOP

white5168

中學生 Rank: 2

帖子: 90
主題: 16
精華: 0
積分: 114
點名: 0
作業系統: XP
軟體版本: sp2
閱讀權限: 20
性別: 男
註冊時間: 2011-7-9
最後登錄: 2018-10-7

10^#

發表於 2012-4-28 20:48 | 只看該作者

抱歉讓大家有點搞錯,以下我再重新解釋一次我所謂的先進先出,也許跟一般人所認知的倉管方式有落差,請各位多多包函,我應該說這是類似先進先出的作法
我需要後面第一筆賣出的交易來減去第一筆買進的,而且需以第一筆買進的部份所剩餘數量來計算當前日期所剩商品數量的平均成本

原始資料
                  商品    價格買進賣出
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

white5168

中學生 Rank: 2

帖子: 90
主題: 16
精華: 0
積分: 114
點名: 0
作業系統: XP
軟體版本: sp2
閱讀權限: 20
性別: 男
註冊時間: 2011-7-9
最後登錄: 2018-10-7

9^#

發表於 2012-4-28 20:13 | 只看該作者

商品    價格買進賣出
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

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
            20120404    A 400 <----沒有買進何來 400???

這裡的每一筆買賣皆為單獨獨立,我並未強調我所列的範例是倉管,請勿以生意的角度來看

TOP

register313

　頭銜: 麻辣路人甲

大學生 Rank: 4

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

2012龍年全勤獎章熱烈參與勳章

8^#

發表於 2012-4-28 17:55 | 只看該作者

本帖最後由 register313 於 2012-4-28 18:25 編輯

回復 4# white5168

是有許多疑點未釐清

總之先給些數據(一般的算法),再一步一步弄清楚吧

Sub aa()
Dim d1, d2 As Object
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
[G1:I65536] = ""
[G1:I1] = Array("產品編號", "剩餘數量", "平均價格")
For B = 2 To [B2].End(xlDown).Row
If Not d1.exists(Cells(B, 2).Value) Then
d1.Add Cells(B, 2).Value, Cells(B, 4) - Cells(B, 5)
d2.Add Cells(B, 2).Value, (Cells(B, 4) - Cells(B, 5)) * Cells(B, 3)
Else
d1(Cells(B, 2).Value) = d1(Cells(B, 2).Value) + Cells(B, 4) - Cells(B, 5)
d2(Cells(B, 2).Value) = d2(Cells(B, 2).Value) + (Cells(B, 4) - Cells(B, 5)) * Cells(B, 3)
End If
Next B
[G2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
[H2].Resize(d1.Count, 1) = Application.Transpose(d1.items)
[I2].Resize(d2.Count, 1) = Application.Transpose(d2.items)
For I = 2 To d2.Count + 1
If Cells(I, "H") = 0 Then
Cells(I, "I") = 0
Else
Cells(I, "I") = Cells(I, "I") / Cells(I, "H")
End If
Next I
End Sub

複製代碼

TOP

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]