對不起,美香小姐,我查看妳的 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("A1") ,A1 或 A100 等都可以,只要是 A 欄就沒問題,若沒有提供 "A1" , 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 。