- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 273
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-12-17
              
|
26#
發表於 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
|
|
發表於 2012-5-1 17:46
| 
