作者robinnpca (rob)
看板Office
標題Re: [算表] 總和為負就從新列重新計算
時間Sun Jul 5 12:38:27 2020
謝謝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://webptt.com/m.aspx?n=bbs/Office/M.1593923910.A.F20.html
1F：→ soyoso: --目的是運算，要將true和false改為數值1和0 07/05 13:01
2F：→ soyoso: 第二個問題，所回傳如內文所述，回傳-1{-1} 07/05 13:04
5F：→ robinnpca: 感謝可用但請問C2 -ROW($1:1)就是上一行的意思嗎?因為 07/07 13:27
6F：→ robinnpca: 後來加了幾行數字從row5開始row4有標題 這樣子標題不會 07/07 13:28
7F：→ robinnpca: 妨礙公式嗎? -row($4:4) 測了兩天老是覺得跑不完 07/07 13:28
8F：→ robinnpca: 發現C2這個公式會不斷計算 因為我列數有一萬列 計算 07/07 13:29
9F：→ robinnpca: 超級超級久都算不完...是因為我從row5開始數字row4標題 07/07 13:29
10F：→ robinnpca: 所造成的嗎? 07/07 13:30
12F：→ robinnpca: 這數字差異差4正常嗎?因為我還沒跑完資料 開始好像有錯 07/07 14:02
13F：→ robinnpca: 等等看能不能跑完 好像幾百個就跑超久... 07/07 14:02
14F：→ soyoso: 以offset(b2,,,-row($1:1))來看，不是上一列，而是儲存格 07/07 14:21
15F：→ soyoso: b2這個範圍，下拉到b3的話，offset(b3,,,-row($1:2))就會 07/07 14:21
16F：→ soyoso: 是b3和b2:b3這二個範圍 07/07 14:21
17F：→ soyoso: 因為內文寫大約一百多的數列，所以提供該公式。如果是回文 07/07 14:25
18F：→ soyoso: 寫到有一萬筆要計算的話，不合適 07/07 14:26
20F：→ soyoso: 公式運算的次數 07/07 14:56
21F：→ robinnpca: 真的速度差好多 真的是完全適用超感謝S大 07/08 16:58
22F：→ robinnpca: 一直看不懂的部位請教D5=...*(T(OFFSET...)這個T是？ 07/08 16:59
23F：→ robinnpca: 不是欄位T是？還有白話解釋..(不空且合<0)*(T..等於同 07/08 17:00
24F：→ robinnpca: 類),捨棄表"",不然就show出row() 前面的*(T... 07/08 17:01
25F：→ robinnpca: 請問白話可以怎樣解釋 07/08 17:01
26F：→ robinnpca: 查不太到資料... 07/08 17:02
27F：→ soyoso: offset配合row儲存格範圍(垂直陣列)的多維參照，外層的函 07/08 18:27
28F：→ soyoso: 數sum不支援該方式的多維參照，需配合函數t，如不配合則會 07/08 18:27
29F：→ soyoso: 回傳錯誤值#value! 07/08 18:27
30F：→ robinnpca: 感謝S大我研究一下 07/19 00:33