作者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/cn.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