麻辣家族討論版版 - Powered by Discuz! Board

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

作者: white5168 時間: 2012-4-28 14:06 標題: 請問如何用excel VBA寫一個以先進先出的方式來取得產品的數量與平均價格

個問大大如題
請問如何用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

作者: register313 時間: 2012-4-28 14:28

回復 1# white5168

這樣的說明,還是不清楚你要的?

1.先進先出?
2.剩餘數量=進貨數量-出貨數量為何有的產品進貨數量為0,卻有出貨數量?
3.剩餘數量的成本均價如何計算?

作者: white5168 時間: 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.剩餘數量的成本均價如何計算?
以累計加減當前買進或賣出數量*買進或賣出價格再除以目前產量剩餘數量,而買賣原則就是以先進先出為原則

作者: white5168 時間: 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

作者: chen_cook 時間: 2012-4-28 17:08

回復 3# white5168

所謂先進先出是指,先進貨的就在下一次的出貨時,優先賣出,所以要搭配日期,如
                  商品買進賣出
20120401    A 500       0
20120402    B       0 100
20120403    A 400       0
200

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
            20120404    A 400 <----沒有買進何來 400???
所以到20120405的A產品剩下數量就要以20120401的減20120404的200再加上20120403的400與20120404的400
因此剩餘數量為(500-200)+400+400 <----- 何來這個庫存
不好意思我看不太懂,我這個二十年倉管看不懂大大你的的先進先出??
版大是指導如何解決問題,而不是理所當然須義務告知解決方法!!!:@

作者: chen_cook 時間: 2012-4-28 17:20

回復 4# white5168

再我來看這個問題只是
1.)統計產品的統數.....
2.)計算進貨價...
3.)平均庫存單價...
4.)再進貨數量累計....
5.)再進資累計平均單價....
沒有所謂的先進先出!!!

作者: chen_cook 時間: 2012-4-28 17:53

回復 4# white5168

(2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300
你用的不是 2050 的 200 pcs 為何會扣除它呢?? 300 X 2000進價不變才是啊??? AVG 還是 2000 或2020 [ 若是要算也是 (2000X300+2050X200)/500 ] 那來的1996.67 這個生意老闆不會做的!! 會計的平均單價是正確的算法!!!
(1966.67*300 - 2010*200)/(300-200)=1880,數量剩為100
2010 的 200 pcs 也是同樣的問題 !!!

作者: register313 時間: 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

複製代碼

作者: white5168 時間: 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???

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

作者: white5168 時間: 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(這是我需要的)

作者: chen_cook 時間: 2012-4-29 07:20

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

作者: white5168 時間: 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(這是我需要的)

作者: c_c_lai 時間: 2012-4-29 16:12

回復 12# white5168
A 項產品產品的處理過程業已了解，
日期、商品、價格、買進、賣出、產品均價 (是否增列？以便隨時能提供參考資訊)，
再請教 20120402 B 1500 0 100 這項產品，
你會怎麼來處理，且無 B 產品之進貨紀錄？

作者: white5168 時間: 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

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

作者: Hsieh 時間: 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)才是成本價位嗎?
這種專業的會計知識我一點都沒有，不知道我的理解與實務差別在哪?
建議您將想要顯示的結果直接用手算出後，填入想要實現的位置，並在隔壁欄位填入你計算的依據
這樣或許比較容易釐清所謂先進先出的概念。

作者: white5168 時間: 2012-4-30 00:16

本帖最後由 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,請各位仔細察看

作者: register313 時間: 2012-4-30 11:01

回復 16# white5168

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

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

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

[attach]10750[/attach]

作者: white5168 時間: 2012-4-30 15:06

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

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

作者: Hsieh 時間: 2012-5-1 17:07

回復 18# white5168
貼圖的資料並不是附件中CSV的資料
依照上述先進先出邏輯試著寫看看，你自己去比對看看結果正不正確
[attach]10774[/attach]

Sub Get_Data()
Dim Ar(), Ay(), x, y
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
fs = ThisWorkbook.Path & "\DataBase.csv"
Open fs For Input As #1
Do Until EOF(1)
Line Input #1, mystr
a = Split(mystr, ",")
If Val(a(0)) > 0 And Val(a(0)) <= [B1] Then
If IsEmpty(d(a(1))) Then
For i = 1 To Val(a(3))
ReDim Preserve Ar(i)
Ar(i - 1) = Val(a(2))
Next
If Val(a(3)) > 0 Then d(a(1)) = Ar
Else
Ar = d(a(1))
s = UBound(Ar)
For i = 1 To Val(a(3))
ReDim Preserve Ar(s + i)
Ar(s + i - 1) = Val(a(2))
Next
s = UBound(Ar)
d(a(1)) = Ar
End If
If Val(a(4)) > 0 Then
If IsEmpty(d1(a(1))) Then
For i = 1 To Val(a(4))
ReDim Preserve Ar(i)
Ar(i - 1) = Val(a(2))
Next
If Val(a(4)) > 0 Then d1(a(1)) = Ar
Else
Ar = d1(a(1))
s = UBound(Ar)
For i = 1 To Val(a(4))
ReDim Preserve Ar(s + i)
Ar(s + i - 1) = Val(a(2))
Next
d1(a(1)) = Ar
End If
End If
End If
Erase Ay: Erase Ar
Loop
Close #1
For Each ky In d1.keys
If IsArray(d1(ky)) Then Ar = d1(ky): x = UBound(Ar) Else x = 0 '出貨
If IsArray(d(ky)) Then Ay = d(ky): y = UBound(Ay) Else y = 0 '進貨
If x = 0 And y > 0 Then '只進不出
For i = 0 To y - 1
'sp = sp + Ar(i)
bp = bp + Ay(i)
Next
bp = bp / y
d2(ky) = Array(ky, y, 0, 0, Abs(y - x), y - x, Round(bp, 2), 0)
bp = 0
ElseIf y = 0 And x > 0 Then '只出不進
For i = 0 To x - 1
sp = sp + Ar(i)
Next
sp = sp / x
d2(ky) = Array(ky, y, x, 0, 0, y - x, 0, Round(sp, 2))
sp = 0
ElseIf x > 0 And y > 0 Then
If x > y Then '出大於進
w = 0: w1 = y - x
For i = 0 To y - 1
pr = pr + Ar(i) - Ay(i)
Next
For j = i To x - 1
nr = nr + Ar(i)
Next
nr = nr / (x - y) '不足量
ElseIf x < y Then '進大於出
w1 = 0: w = y - x
For i = 0 To x - 1
pr = pr + Ar(i) - Ay(i)
Next
For j = i To y - 1
sr = sr + Ay(i)
Next
sr = sr / Abs(x - y) '不足量
End If
d2(ky) = Array(ky, y, x, pr, w, w1, Round(sr, 2), Round(nr, 2))
pr = 0: nr = 0: sr = 0
End If
Erase Ay: Erase Ar
Next
[A4:H65536] = ""
[A4].Resize(d2.Count, 8) = Application.Transpose(Application.Transpose(d2.items))
End Sub

複製代碼

作者: white5168 時間: 2012-5-1 17:26

很感謝Hsieh大大幫忙,不過目前我還無法下載所有大大所回覆的附件,只能等到升級後才能看到結果
想請問Hsieh大大, Excel VBA的程式,是否能做到跟C,C++等高階語言一樣模組化的程度?
這裡我要澄清說明,附件的原始資料的確是貼圖的資料,我只是將原始資料中比較簡單的不複雜的資料項剔除
貼圖的資料是我將原始資料留下複雜的部份來做說明用的
在撰寫程式的過程中會遇到問題的資料,基本上程式撰寫只要複雜的能寫的出來,其他的都不成問題
可以請Hsieh大大自行刪除比較不複雜且簡單的資料即可看出跟貼圖資料相同的內容
謝謝

作者: Hsieh 時間: 2012-5-1 17:35

回復 20# white5168
我並非科班出身，只懂一點VBA皮毛，其他程式不懂
至於您所謂模組化，我並不了解其義
如果您確定貼圖資料是正確，那我的程式碼跑出來結果就必然是錯的
必須再來看看哪邊出問題了

作者: white5168 時間: 2012-5-1 17:46

本帖最後由 white5168 於 2012-5-1 17:58 編輯

謝謝Hsieh大大的回覆
我想只能先試試看您所貼的程式碼,至於附件的部份等升級後再下載,先試試看相關的部分是否可行

其實原始資料中還有包含從今年年初到現在的交易日期,廠商,而每家廠商的商品又不相同, 又怕大家看了會更頭昏,所以我目前只列商品的買賣明細
等我先看大大寫的完後再跟您請益當加入廠商後的資訊,我可以先預告廠商有1090多家,而每家賣的商品加加減減的總共有1400多樣,而這兩邊的資料量還在增加當中,相信這樣的難度又更高
希望能從大大的經驗中吸取處理這樣龐大資料的方法

作者: wang 時間: 2012-5-1 22:43

本帖最後由 wang 於 2012-5-2 21:20 編輯

對不起會錯意自刪

作者: white5168 時間: 2012-5-1 23:58

本帖最後由 white5168 於 2012-5-2 00:22 編輯

bbojj大大
的確是每日各股在全台所有劵商的成交明細,我是用python抓下來(用Excel VBA抓資料會很慢,而且會當,因為每天的資料量就約70MB~90MB),如再加上要分析這些資料鐵定更慘
因此以python抓資料(國外已有將python定位為金融的程式語言),MySQL或Access存資料,Excel VBA來作資料分析才會是不錯的架構選擇,只可惜目前這裡並沒有遇到會使用python的人,有興趣可以相互討論,也建議板主能開一個python的討論版
離題了,真抱歉,我還是先乖乖學好Excel VBA

作者: register313 時間: 2012-5-2 09:09

回復 25# white5168

先前在另一帖裡(與本帖內容相同,重覆發帖了)已作回覆
Hsieh超版在本帖裡亦作了回覆

附上H版與R版程式執行結果之比較供參考,有不對的地方再作回覆
黃色網底即為2個版本不同之處
[attach]10776[/attach]
[attach]10777[/attach]

作者: Hsieh 時間: 2012-5-2 11:56

回復 22# white5168
把整體流程概念註解後，看看與你的想法落差在哪?

Sub Get_Data()
Dim Ar(), Ay(), x, Mystr$, A
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
ChDir ThisWorkbook.Path
fs = Application.GetOpenFilename("逗點分隔 (CSV) (*.csv), *.csv") '開啟資料檔案對話方塊選擇CSV檔案
Open fs For Input As #1 '讀取CSV檔案
Do Until EOF(1)
Line Input #1, Mystr '讀取一行資料寫入變數
A = Split(Mystr, ",") '將資料切割存入陣列
If Val(A(0)) > 0 And Val(A(0)) <= [B1] Then '判斷是否在結算日期之前的資料
If IsEmpty(d(A(1))) Then '以產品編號為索引若不存在
For i = 1 To Val(A(3)) '以買入數量做迴圈、記憶住每一個的單價
ReDim Preserve Ar(i)
Ar(i - 1) = Val(A(2))
Next
If Val(A(3)) > 0 Then d(A(1)) = Ar '如果有數量就將陣列存到字典中
Else '也就是有第二筆以上買入時執行
Ar = d(A(1)) '先取出該編號已經購買的資料存入陣列
s = UBound(Ar)
For i = 1 To Val(A(3)) '將每筆資料單價加入此陣列
ReDim Preserve Ar(s + i)
Ar(s + i - 1) = Val(A(2))
Next
s = UBound(Ar)
d(A(1)) = Ar '將陣列回存到字典物件
End If
If Val(A(4)) > 0 Then '賣出資訊處理，與買入觀念相同
If IsEmpty(d1(A(1))) Then
For i = 1 To Val(A(4))
ReDim Preserve Ar(i)
Ar(i - 1) = Val(A(2))
Next
If Val(A(4)) > 0 Then d1(A(1)) = Ar
Else
Ar = d1(A(1))
s = UBound(Ar)
For i = 1 To Val(A(4))
ReDim Preserve Ar(s + i)
Ar(s + i - 1) = Val(A(2))
Next
d1(A(1)) = Ar
End If
End If
End If
Erase Ay: Erase Ar '處理下一筆資料前先把原來的買賣記憶消除
Loop
Close #1 '關閉CSV檔案
For Each ky In d1.keys
If IsArray(d1(ky)) Then Ar = d1(ky): x = UBound(Ar) Else x = 0 '出貨資料若是陣列就取出陣列可得知到底有幾筆出貨資訊
If IsArray(d(ky)) Then Ay = d(ky): y = UBound(Ay) Else y = 0 '進貨資料若是陣列就取出陣列可得知到底有幾筆進貨資訊
'以下就不同狀況計算各欄位應有的值寫入陣列
If x = 0 And y > 0 Then '只進不出
bp = Application.Average(Ay) '進貨平均價
d2(ky) = Array(ky, y, 0, 0, Abs(y - x), y - x, Round(bp, 2), 0)
bp = 0
ElseIf y = 0 And x > 0 Then '只出不進
sp = Application.Average(Ar) '出貨平均價
d2(ky) = Array(ky, y, x, 0, 0, y - x, 0, Round(sp, 2))
sp = 0
ElseIf x > 0 And y > 0 Then
If x > y Then '出大於進
w = 0: w1 = y - x
For i = 0 To y - 1
pr = pr + Ar(i) - Ay(i) '計算出貨與進貨的價差累計、這是真正獲利值可能與提問者的觀念差異
Next
For j = i To x - 1 '不夠扣計算
nr = nr + Ar(i)
Next
nr = nr / (x - y) '不足量
ElseIf x < y Then '進大於出
w1 = 0: w = y - x
For i = 0 To x - 1
pr = pr + Ar(i) - Ay(i) '計算出貨與進貨的價差累計、這是真正獲利值可能與提問者的觀念差異
Next
For j = i To y - 1 '剩餘量計算
sr = sr + Ay(i)
Next
sr = sr / Abs(x - y) '不足量
End If
d2(ky) = Array(ky, y, x, pr, w, w1, Round(sr, 2), Round(nr, 2)) '寫入陣列
pr = 0: nr = 0: sr = 0
End If
Erase Ay: Erase Ar
Next
[A4:H65536] = ""
[A4].Resize(d2.Count, 8) = Application.Transpose(Application.Transpose(d2.items))
End Sub

複製代碼

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)