PTT數位生活區 / Office

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

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