有空行求和

dongfangxielong

theking_wsz

..                     

quqiyuan

函数估计有点难度，vba吧，也想了好一会，供参考吧

cyh3230

这个用SQL做还简单些，
函数的方法，太麻烦了，难倒不难

quqiyuan

用PQ更方便

boyuanoffice

1. =LET(dt,FILTER(B4:C30,C4:C30<>""),
   
2.   mc,TAKE(dt,,1),
   
3.   je,TAKE(dt,,-1),
   
4.   xh,DROP(SCAN(0,N(mc<>VSTACK("",mc)),SUM),-1),
   
5.   xh_2,UNIQUE(xh),
   
6.   mc_2,XLOOKUP(xh_2,xh,mc),
   
7.   je_2,MAP(xh_2,LAMBDA(c,SUM(FILTER(je,xh=c)))),
   
8.   VSTACK(A3:C3,HSTACK(xh_2, mc_2, je_2)))

复制代码

part98

=REDUCE(B3:C3,B4:B30,LAMBDA(x,y,LET(s,OFFSET(y,,,,2),IF(OR(TAKE(x,-1,1)=y,y=""),VSTACK(DROP(x,-1),HSTACK(TAKE(x,-1,1),SUM(TAKE(x,-1))+SUM(s))),VSTACK(x,s)))))

boyuanoffice

仿照楼上写了一个, 直接在原数据上操作, 不用再另外筛选出非空数据, 这样确实轻松了许多

1. =LET(dat,
   
2.   DROP(REDUCE("",B4:B30,LAMBDA(x,y,
   
3.     IF(AND(y<>TAKE(x,-1,1),y<>""),
   
4.       VSTACK(x,HSTACK(y,OFFSET(y,,1))),
   
5.       VSTACK(DROP(x,-1),HSTACK(TAKE(x,-1,1),SUM(TAKE(x,-1,-1),OFFSET(y,,1))))))),1),
   
6.   VSTACK({"序号","名称","金额合计"},HSTACK(SEQUENCE(ROWS(dat)),dat)))

复制代码

624134745

=REDUCE(F3:H3,B4:B30,LAMBDA(x,y,IF(y="",x,IF(y<>INDEX(TAKE(x,-1),2),VSTACK(x,HSTACK(ROWS(x),OFFSET(y,,,,2))),VSTACK(DROP(x,-1),HSTACK(ROWS(x)-1,INDEX(TAKE(x,-1),2),INDEX(TAKE(x,-1),3)+OFFSET(y,,1)))))))365版本硬上1个

世界客都

我上面插多了几行

低版本比较长公式，看看谁可以帮我优化优化

H10输入下拉即可
=SUMIF(INDEX(B:B,SMALL(IF((B$10:B39=G10)*(COUNTIF(OFFSET(B$8,ROW($1:30),,-9),"="&G10)=0),ROW($10:39)),COUNTIF(G$10:G10,G10))):B99,G10,INDEX(C:C,SMALL(IF((B$10:B39=G10)*(COUNTIF(OFFSET(B$8,ROW($1:30),,-9),"="&G10)=0),ROW($10:39)),COUNTIF(G$10:G10,G10))))-SUMIF(G11:G34,G10,H11)
下拉即可