excel 求連續正數/ 負數 的總和

我祇能夠用自製Marco Function的方法:
Public Function Sumup(A As Range, C As Integer, b)
If b <> -1 Then
b = 1
End If
For t = A.Cells.Count To 1 Step -1
If A(t) * b > 0 Then
a1 = a1 + 1
a2 = a2 + A(t)
If a1 = C Then
Sumup = a2
Exit Function
End If
End If
Next
Sumup = “nil”
End Function

公式結構: =Sumup(Array, match qty, any number[positive] or -1[negative])
G5 輸入公式 =Sumup(F$5:F5,5,0) 下拉
H5 輸入公式 =Sumup(F$5:F5,5,-1) 下拉

題目第1個Positive Sum answer 應是 66
此題與另一條發問中的題目極之相似!

2007-02-17 11:21:57 補充：
但我測試是沒有問題剛e-mail 附件到你 2002 的郵址請測試

2007-02-17 11:41:05 補充：
BiGLiN君, 人家題目是第5行開始, 我的解答當然是F$5開始心果句, 咁難的題目, 都俾你輕易解決, 由衷之言: 佩服我家裡的Excel書還是 Excel 97(薄裝的), 已out 了我剛往黃金買了本新書, 希望能有進步, 特別是array formula.我是在上個月加入Yahoo knowledge才第一次知道有array formula 呢樣嘢! 真可笑!

2007-02-18 09:54:43 補充：
yuenlamlam, 如兩條公式一齊拖萬幾row, 普通一條Vlookup公式都會有問題。建議: 如假設每100連續格內, 必有至少5個正數及負數, 你需在拖到第100格, 將公式內所有 $ 全部移除。如正數的機率比負數機率大很多, 則正數公式拖到50格移除$, 負數則拖到200格後才移除$。雖然這一兩天我沒有太多時間幫你測試萬多row, 但深信上述的辦法會將速度加快數以倍計祝春節快樂!

如果資料 不一定 的順住遞增/減
可以試下e個
http://hk.geocities.com/snam_hk/00000023.xls

對不起，美香小姐，我查看妳的 Profile
，來到這裏，發現這條有趣的問題，不禁又要多管閒事。



首先，這條問題其實可以用普通的公式來做， Copy 以下公式，
點一下第一列的一個空格 (如 F1 或 G1) ，按 F2 ，按 Ctrl-V 把公式 Paste 上去，然後按
Ctrl+Shift+Enter 。


=IF(SUM(1*($A$1:A1>0))>4,SUM(LARGE(($A$1:A1>0)*$A$1:A1,{1,2,3,4,5})),"NIL")


第一格弄好後，指向右下角至浮標變成 + 號，然後按著左鍵下拉。


負數的公式則是：


=IF(SUM(1*($A$1:A1<0))>4,SUM(SMALL(($A$1:A1<0)*$A$1:A1,{1,2,3,4,5})),"NIL")


請緊記，這是 array formula ，所以輸入後要按
Ctrl+Shift+Enter 而不是普通的 Enter 。





Excel
檔可以從這裏下載。







另外，美香小姐，妳是妳打錯字呢？
妳說用 Sumup 時要這樣用：


G5 輸入公式 =Sumup(F$5:F5,5,0) 下拉


但當的範圍是從 F5 開始，這樣做的話， F5
之前的數字會被忽略了，結果是答案一直是 NIL 直至 F5以下有五個正數或負數才開始有答案。
我認為應該是這樣才對：


G1 輸入公式 =Sumup(F$1:F1,5,0) 下拉


負數亦是如此。



2007-02-17 07:30:16 補充：
對不起，上面的連結有問題，Excel 檔可以從這裏下載：http://big-lin.com/Documents/7007021402666_Excel_Sum_Pos_Neg_Numbers.xls

2007-02-20 12:32:09 補充：
美香小姐，對不起，我現在明白妳的 F5 是甚麼意思了 :-)To yuenlamlam ：1. 不錯，我當時偷懶，看見你的 Example 是遞增的，所以我就用了一條簡單的公式，只計算一個 Range 內最大的五個數字。2. 公式可以如另一位答題者阿 nam 所說，改變一下就可以了，只是比較長一點。

2007-02-20 12:41:07 補充：
3. 不過，如果你有幾萬個 rows ，因為除非如美香小姐所說，假設連續每隔一段短距離就會有五個正數或五個負數，否則 array formula 無論如何修改，都無法改變它對 memory 及 CPU 的龐大需求。這個時候，用 VB 來做會比較好，因 VB 可以用 Loop ，找到五個正數或負數就可以，不像 array formula 那樣一定要全部 row 都用來運算。

2007-02-20 12:46:35 補充：
由於字數的限制，我不在這裏張貼 VB code ，請參考以下 Excel 檔。為了提高性能，我將正負數分為兩個 functions 。使用這兩個 function 時，你要告訴它 data 在那一欄，如 sum_pos_five(&quot;A1&quot;) ，A1 或 A100 等都可以，只要是 A 欄就沒問題，若沒有提供 &quot;A1&quot; ， function 會假設是你輸入公式那一格的左邊那一欄。http://big-lin.com/Documents/7007021402666_Excel_Sum_Pos_Neg_Numbers-2.xls

2007-02-20 12:58:18 補充：
我試過以上的 code ，大概 10 秒就可以計到 65536 個正數及負數的答案。我用的是 Core 2 Duo 2.16GHz 。