- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
8#
發表於 2012-3-24 19:14
| 只看該作者
- Sub Ex()
- Dim 手續費 As Integer, N As Integer, B As Single
- Sheets("Sheet1").Select
- Sheets("Sheet1").Names.Add "手續費率", 0.001425
- Sheets("Sheet1").Names.Add "打折", 0.28
- Sheets("Sheet1").Names.Add "證交稅率", 0.003
- '=========================='調整C區股價規則
- For Each A In Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
- B = Application.Lookup(A, Array(0, 11, 51, 101, 501, 1001), Array(0.01, 0.05, 0.1, 0.5, 1, 5))
- A(1, "C") = Round(A, 2) * 1000 '買進股金
- 手續費 = IIf(A(1, "C") * [手續費率] * [打折] > 20, A(1, "C") * [手續費率] * [打折], 20)
- A(1, "D") = 手續費 '手續費
- A(1, "E") = A(1, "C") + 手續費
- N = 1 '
- A(1, "G") = Round(Round(A, 2) + (B * N), 2) 'C區股價
- A(1, "H") = "=RC[-1]*1000" '賣出股金
- A(1, "I") = "=Round(IF(RC[-1] *手續費率 * 打折 > 20, RC[-1] * 手續費率 * 打折, 20), 0)" '賣出手續費
- A(1, "J") = "=Round(RC[-2] *證交稅率,0)" '證交稅
- A(1, "K") = "=RC[-3]-RC[-2]-RC[-1]" '賣出金額
- A(1, "M") = "=RC[-2]-RC[-8]" '獲利金額
- A(1, "N") = "=(RC[-3]-RC[-9])/RC[-9]" '獲利%數
- '
- A(1, "N").GoalSeek Goal:=0.01, ChangingCell:=A(1, "G") '***** 目標搜尋 獲利%數=0.01 會求得接近的[賣價]
- '**** 也可用迴圈求解 求解後的[賣價] 會是真實的[[ 檔價 ]] ********
- 'Do Until A(1, "M") >= A(1, "E") * 0.01 ' 獲利%數=0.01
- 'N = N + 1
- 'A(1, "G") = Round(A, 2) + (B * N)
- 'Loop
- '*******************************
- A(1, "G").Resize(, 8) = A(1, "G").Resize(, 8).Value '消除公式顯示值
- Next
- End Sub
複製代碼 如圖 請修改 欄位名稱
|
|