以前聽過朋友介紹過這個概念後也曾經想過要如何才能使用EXCEL Automate 這個問題，但發現實在比想像中難，最後也不了了之。
因為碰巧在另一本關於數據結構與演算法 (Data Structure and Algorithm Analysis) 的書中見到關於如何解決最大連續和 (Maximum Sequence Sum) 的問題，因此乾脆一不做二不休，將這個問題給徹底解決掉。
首先定義什麼是最大連續和問題：
定義序列：
定義連續和為：
求
中，
的最大值換而言之
若一序列為
則其最大連續和為18，如下圖所示：
但是每次都要建立一個 n*n 的矩陣去計算這個問題實在是太浪費位置又沒有效率了，那到底有沒有方法可以只需要一個方程就能傳回一個列陣 (array) 中的最大連續和呢？
答案是有的，但看正確答案前先看一下錯的答案：
1. 貼一個在網上看到的答案：
=MAX(SUBTOTAL(9,OFFSET(A1,ROW(A1:A10001)-1,,-TEXT(
FREQUENCY(ROW(A1:A10000),(A1:A10000*A2:A10001<=0)*
ROW(A1:A10000)),"0;;1;"))))\
(Ctrl + Shift + Enter)
我小試了一下，發現陣列傳回的答案是15，而非18
原因是方程中的 A1:A10000*A2:A10001<=0 計算了當元素轉向時的資料（如-2 -> 3為一個轉向、1->-8為一個轉向），並將每個轉向視為一次斷點。而後使用subtotal 時則將每個轉向點至上一個轉向點之和相加，傳回 {-2, NA, NA, 10, -8, NA, NA, 15, -4, 5}，再取最大值傳回15。
這個公式計算了在任何一次盈利前的累積虧損，但如果每次於虧損後都有一些盈利呢？
舉例而言，考慮這個情況：
 |
| 資本累積圖 |
 |
| 淨回報圖 |
使用上述公式算出來的最大連續虧損為 -10，大約是出現在第 34-38 個元素，連續虧損了三次 (-5,-3,-2)。然而，若從資本的最高點 14 到最低點 -35 來看，我整整虧損了49 點之多。
因此，該公式並非我所需要的公式。（而且當資料一多，其運算速度將會變得非常的慢）
2. 先將所有回報 (Profits) 相加得出資本累積
再輸入下列公式：
= MAX(資本累積) - MIN(資本累積)
則公式傳回最高點減最低點的值。
然而，這個方法未考慮到最低點有可能出現在最高點之前，如圖所示：
那麼算出來的答案就是最大連續回報而非最大連續虧損了。
*************************************************************************
那麼，要如何才能做到最大連續虧損的計算呢？
方法一：
假設回報數列在 Column A，那麼在Column B 輸入下列公式：
= $A$1................................................................................. (for B1)
= IF($B1+$A2 > 0, 0, $B1+$A2).........................................(for B2, 然後往下拉)
最後，在任意一個地方輸入：
= MIN(B:B)
即為最大連續虧損。
具體如圖所示：
或許有人覺得，要額外開一行出來太浪費位置了，有辦法可以再省位置一些來計算嗎？
方法二：
如果不想再開一行，那無法避免要使用到VBA了。
在模組一加入下方函數：
Function MAXDD(profit)
Dim ThisSum, MaxSum
Dim i As Long
ThisSum = 0
MaxSum = 0
For i = 1 To profit.Rows.Count
ThisSum = ThisSum + profit(i)
If ThisSum <= MaxSum Then
MaxSum = ThisSum
ElseIf ThisSum >= 0 Then
ThisSum = 0
End If
Next i
MAXDD = MaxSum
End Function
然後使用 =MAXDD(指定範圍) 呼叫函數，如圖所示：
這樣一來就可以輕鬆計算最大連續虧損了。
除此以外，最大連續虧損周期可以下列公式計算：
Function MAXDD_P(profit)
Dim ThisSum, MaxSum
Dim i As Long
Dim j As Long
ThisSum = 0
MaxSum = 0
j = 0
MaxP = 0
For i = 1 To profit.Rows.Count
ThisSum = ThisSum + profit(i)
j = j + 1
If ThisSum <= MaxSum Then
MaxSum = ThisSum
MaxP = j
ElseIf ThisSum >= 0 Then
ThisSum = 0
j = 0
End If
Next i
MAXDD_P = MaxP
End Function
然後使用 =MAXDD_P(指定範圍) 呼叫函數，得出最大連續虧損發生所需的周期。
2. 先將所有回報 (Profits) 相加得出資本累積
再輸入下列公式：
= MAX(資本累積) - MIN(資本累積)
|
| 資本累積圖 |
則公式傳回最高點減最低點的值。
然而，這個方法未考慮到最低點有可能出現在最高點之前，如圖所示：
 |
| 資本累積圖 |
那麼算出來的答案就是最大連續回報而非最大連續虧損了。
*************************************************************************
那麼，要如何才能做到最大連續虧損的計算呢？
方法一：
假設回報數列在 Column A，那麼在Column B 輸入下列公式：
= $A$1................................................................................. (for B1)
= IF($B1+$A2 > 0, 0, $B1+$A2).........................................(for B2, 然後往下拉)
最後，在任意一個地方輸入：
= MIN(B:B)
即為最大連續虧損。
具體如圖所示：
或許有人覺得，要額外開一行出來太浪費位置了，有辦法可以再省位置一些來計算嗎？
方法二：
如果不想再開一行，那無法避免要使用到VBA了。
在模組一加入下方函數：
Function MAXDD(profit)
Dim ThisSum, MaxSum
Dim i As Long
ThisSum = 0
MaxSum = 0
For i = 1 To profit.Rows.Count
ThisSum = ThisSum + profit(i)
If ThisSum <= MaxSum Then
MaxSum = ThisSum
ElseIf ThisSum >= 0 Then
ThisSum = 0
End If
Next i
MAXDD = MaxSum
End Function
然後使用 =MAXDD(指定範圍) 呼叫函數，如圖所示：
這樣一來就可以輕鬆計算最大連續虧損了。
除此以外，最大連續虧損周期可以下列公式計算：
Function MAXDD_P(profit)
Dim ThisSum, MaxSum
Dim i As Long
Dim j As Long
ThisSum = 0
MaxSum = 0
j = 0
MaxP = 0
For i = 1 To profit.Rows.Count
ThisSum = ThisSum + profit(i)
j = j + 1
If ThisSum <= MaxSum Then
MaxSum = ThisSum
MaxP = j
ElseIf ThisSum >= 0 Then
ThisSum = 0
j = 0
End If
Next i
MAXDD_P = MaxP
End Function
然後使用 =MAXDD_P(指定範圍) 呼叫函數，得出最大連續虧損發生所需的周期。
謝謝! 很有幫助!!
回覆刪除沒想到您的方法一, 可以僅用IF函數,短短幾行就計算出最大連續虧損, 勵害!
回覆刪除幫助很大，謝謝版主
回覆刪除