Re: [算表] 總和為負就從新列重新計算

[robinnpca (rob)]

謝謝S大回覆但不解有兩點
={IF(SUM(--(SUMIF(OFFSET(B2,,,-ROW($1:1)),"<>")<0)),"",ROW())}

         --這個--?是反反的意思false false?    sum(--(sumif結果<0))

另一個           offset(B2,,,height(這不是規定要正數嗎？ -row($1:1) 得到的是-1?

因為我本來以為各自就可達到需求
結果發現若三個例子連在一起 來計算
算出確切的起始點和終點row這樣子套進去好像錯誤
不知道可以怎麼修改 感謝

                                first   last
A       20      2       40      1       6       2       9
A       -10                     1       6       3
A       30      4               1       6       4
A       -10                     1       6       5
A       5                       1       6       6
B       20                      6       10      7       22
B       -10                     6       10
B       30                      6       10      9
B       -10                     6       10
C       -30                     10      14              25
C       -10                     10      14      12
C       30                      10      14      13
C       -10                     10      14

A欄是類別 B欄數值
C2輔助列就是={IF(SUM(--(SUMIF(OFFSET(B2,,,-ROW($1:1)),"<>")<0)),"",ROW())}
D2原先要的結果就是=IFERROR(SUM(OFFSET(B1,,,MAX(C:C))),0)
E2 first我寫=IF(A2=A1,E1,ROW()-1)
F2 last 我寫=IF(A2<>A3,ROW(),F3)
G2模擬寫但有問題 ={IF(SUM(--(SUMIF(OFFSET(B2,,,F2-1),"<>")<0)),"",ROW())}
H2所以也跟著出問題=IFERROR(IF(A2=A1,"",SUM(OFFSET(G1,,,MAX(C:C)))),0)

請問G2 H2可以怎樣改呢？
https://i.bmp.ovh/imgs/2020/07/40be5e0233d9633e.png
黃色的是我手填的 因為公式進去下面數字會錯
希望能達到上面jkl列的 最終整理結果


※ 引述《robinpca (robin)》之銘言：
: 軟體:excel
: 版本:2016
: 例子一:
: A1 20
: A2 -10
: A3 30
: A4 -10
: A5 5
: 想要的不是很難但是用excel就是寫不出來
: 也就是
: 想要倒著求總和 但不夠負數扣的就捨棄 從正數開始
: PS:想過sumif但是好像不能完成  輔助列也失敗 因為這個是大約一百多不固定的數列
: 所以也不可能用sumifs寫一大串...超級長
: 懇請求解  感恩
: if  A5>0  那就 sum(A5:A4)
: if sum(A5:A4)<0  就捨棄從下一列開始
: if A3>0  那就往上一列 sum(A3:A2)
: if sum(A3:A2)>0 那就往上一列 sum(A3:A1)
: 也就是由下面加上來
: 一開始負的就直接捨棄
: 然後往上一列走 上一列跟上上一列加完會變成負的的就捨棄掉
: 若加完是正的(夠扣)那就繼續往上一列加過去
: 上面最後呈現的是
: 例子一希望解果:
: A3+A2+A1=30-10+20=40  若可同時呈現告知是從A3開始夠扣更好
: 例子二:
: A1 20
: A2 -10
: A3 30
: A4 -10
: 希望解果 直接捨棄A4 從A3開始  A3+A2+A1=40
: 例子三:
: A1 -30
: A2 -10
: A3 30
: A4 -10
: A5 -2
: 負數完全不夠扣 呈現答案0和從#N/A列開始

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.34.171.38 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1593923910.A.F20.html
→ soyoso: --目的是運算，要將true和false改為數值1和0
→ soyoso: 第二個問題，所回傳如內文所述，回傳-1{-1}
→ soyoso: https://i.imgur.com/V49d5t7.jpg 類似這樣
→ soyoso: 三欄的話 https://i.imgur.com/htLqNeg.jpg
→ robinnpca: 感謝可用但請問C2 -ROW($1:1)就是上一行的意思嗎?因為
→ robinnpca: 後來加了幾行數字從row5開始row4有標題 這樣子標題不會
→ robinnpca: 妨礙公式嗎? -row($4:4) 測了兩天老是覺得跑不完
→ robinnpca: 發現C2這個公式會不斷計算 因為我列數有一萬列 計算
→ robinnpca: 超級超級久都算不完...是因為我從row5開始數字row4標題
→ robinnpca: 所造成的嗎?
→ robinnpca: https://i.bmp.ovh/imgs/2020/07/154f7a434b6b6702.png
→ robinnpca: 這數字差異差4正常嗎?因為我還沒跑完資料 開始好像有錯
→ robinnpca: 等等看能不能跑完 好像幾百個就跑超久...
→ soyoso: 以offset(b2,,,-row($1:1))來看，不是上一列，而是儲存格
→ soyoso: b2這個範圍，下拉到b3的話，offset(b3,,,-row($1:2))就會
→ soyoso: 是b3和b2:b3這二個範圍
→ soyoso: 因為內文寫大約一百多的數列，所以提供該公式。如果是回文
→ soyoso: 寫到有一萬筆要計算的話，不合適
→ soyoso: https://i.imgur.com/a6ajDfq.png 多幾欄輔助欄來減少原本
→ soyoso: 公式運算的次數
→ robinnpca: 真的速度差好多 真的是完全適用超感謝S大
→ robinnpca: 一直看不懂的部位請教D5=...*(T(OFFSET...)這個T是？
→ robinnpca: 不是欄位T是？還有白話解釋..(不空且合<0)*(T..等於同
→ robinnpca: 類),捨棄表"",不然就show出row()  前面的*(T...
→ robinnpca: 請問白話可以怎樣解釋
→ robinnpca: 查不太到資料...
→ soyoso: offset配合row儲存格範圍(垂直陣列)的多維參照，外層的函
→ soyoso: 數sum不支援該方式的多維參照，需配合函數t，如不配合則會
→ soyoso: 回傳錯誤值#value!
→ robinnpca: 感謝S大我研究一下