12
返回列表 上一主題 發帖

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

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2012-4-28 14:06 | 顯示全部帖子

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

FIFO
個問大大如題
請問如何用excel VBA寫一個以先進先出的方式來取得產品的數量與平均價格
有以下的資料,但小弟實在想不出該如何寫出來,買進賣出須以日期為主
要取得目前產品的剩餘數量與剩餘數量的成本均價,產品明細如附件

購買日期,產品編號,購買價格,進貨數量,出貨數量
20120102,1166,27,0,2000
20120102,116P,27.2,0,2000
20120102,5511,27.2,0,1000
20120102,5515,27.1,0,1000
20120102,585J,27,5000,0
20120102,585J,27.05,1000,0
20120102,592a,27.1,1000,0
20120102,5962,27.2,0,2000
20120102,616K,27.25,0,1000
20120102,7001,27,0,2000
20120102,7001,27.05,0,1000
20120102,7001,27.1,0,1000
20120102,700K,27.3,1000,0
20120102,779z,27.7,0,1000
20120102,8560,27.05,1000,0
20120102,8560,27.1,2000,0
20120102,8560,27.25,1000,0
20120102,8560,27.7,0,3000
20120102,8843,27,0,1000
20120102,9108,27.25,1000,0
20120102,9108,27.7,0,1000
20120102,913R,27.7,5000,0
20120102,9187,27.05,0,1000
20120102,9313,27.3,0,1000
20120102,9363,27,0,1000
20120102,9801,27.2,10000,0
20120102,9817,27.1,0,1000
20120102,983Z,27.2,0,3000
20120102,984C,27.25,0,1000
20120102,984K,27,2000,0
20120102,9887,27,0,1000
20120102,9887,27.2,0,2000
20120103,1035,27.05,2000,0
20120103,1160,27.45,5000,0
20120103,1166,27.05,0,1000
20120103,116L,27.05,0,1000
20120103,116L,27.45,0,1000
20120103,538N,27.45,0,2000
20120103,572D,27.5,0,5000
20120103,5926,27.05,4000,0
20120103,592M,27.5,0,2000
20120103,7003,27.45,2000,0
20120103,7003,27.5,10000,0
20120103,700K,27.45,0,1000
20120103,700W,27.05,1000,0
20120103,700j,27.05,4000,0
20120103,8560,27.05,2000,0
20120103,8560,27.45,0,1000
20120103,8560,27.5,0,2000
20120103,8850,27.45,0,1000
20120103,9183,27.45,1000,0
20120103,9185,27.05,1000,0
20120103,918D,27.05,0,10000
20120103,921Y,27.05,0,1000
20120103,9303,27.05,1000,0
20120103,9303,27.5,0,1000
20120103,9649,27.05,0,1000
20120103,979H,27.05,0,1000
20120103,9817,27.45,0,2000
20120104,1035,26.8,0,3000
20120104,1112,27,0,1000
20120104,1162,27.3,1000,0
20120104,116G,26.85,0,1000
20120104,5182,27,0,1000
20120104,551X,27,0,1000
20120104,585V,26.8,0,6000
20120104,700K,27.15,1000,0
20120104,700j,26.8,6000,0
20120104,700j,27,1000,0
20120104,8560,26.8,0,1000
20120104,8560,27,1000,0
20120104,8560,27.15,0,1000
20120104,8560,27.3,0,1000
20120104,884A,26.8,0,3000
20120104,9108,27,1000,0
20120104,9135,27.15,0,2000
20120104,9183,27.15,0,1000
20120104,9274,26.85,1000,0
20120104,9647,27,1000,0
20120104,9801,26.8,8000,0
20120104,981a,26.8,0,5000
20120104,982A,26.8,1000,0
20120104,9887,27,0,2000
20120104,9891,27,1000,0
20120104,989V,26.8,3000,0
20120104,989V,27.15,2000,0
20120104,989Z,27.15,1000,0

test.rar (612 Bytes)

產品買賣資料明細

收藏 分享

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
2#
發表於 2012-4-28 15:01 | 顯示全部帖子
1.先進先出?
所謂先進先出是指,先進貨的就在下一次的出貨時,優先賣出,所以要搭配日期,如
                      商品   買進   賣出
20120401      A    500         0
20120402      B          0   100
20120403      A    400         0
20120404      A    400    200

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
所以到20120405的A產品剩下數量就要以20120401的減20120404的200再加上20120403的400與20120404的400
因此剩餘數量為(500-200)+400+400

2.剩餘數量=進貨數量-出貨數量  為何有的產品進貨數量為0,卻有出貨數量?
數量為0時,表示當天只有進貨或出貨,而另一邊不作買賣的動作,換句話說,如果買進為0,賣出為5000,就表示當天只有賣出產品

3.剩餘數量的成本均價如何計算?
以累計加減當前買進或賣出數量*買進或賣出價格再除以目前產量剩餘數量,而買賣原則就是以先進先出為原則

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
3#
發表於 2012-4-28 15:26 | 顯示全部帖子
先進先出的說明
http://wiki.mbalib.com/zh-tw/%E5%85%88%E8%BF%9B%E5%85%88%E5%87%BA%E6%B3%95

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


一般的計算
所以到20120405時, A的平均買進成本 (2000*500 + 2020*400 + 2050*400 - 2050 *200 - 2010*200)/(500+400+400-200-200)=2017.78

先進先出
所以到20120405時, A的平均買進成本  
20120401 買進20120404賣出後的產品均價為: (2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=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

20120401 剩餘的在20120405再賣出後的均價為 (1966.67*300 - 2010*200)/(300-200)=1880,數量剩為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,數量為900
                      商品     價格          買進   賣出
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

TOP

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
4#
發表於 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

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
5#
發表於 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

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
6#
發表於 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

white5168

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

white5168

帖子
90 
主題
16 
精華
0 
積分
114 
點名
0  
作業系統
XP 
軟體版本
sp2 
閱讀權限
20 
性別
男 
註冊時間
2011-7-9 
最後登錄
2018-10-7 
8#
發表於 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

white5168

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

white5168

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

TOP

        靜思自在 : 能善用時間的人，必能掌握自己努力的方向。
12
返回列表 上一主題