Excel含子项嵌套求和问题

lzgogol

各位大佬工作中经常会涉及到示列文件中一个大项中含有小项的这种嵌套型求和案例，如1.1.1 及1.1.2我需要一项一项的用sum函数求和，1.1项时只能一个文本一个文本的选然后相加，经常会出现误操作，大佬们可否有好的解决办法，小弟不甚感谢

广东五华

供参考

博爱风雨同舟

=IF(A2="",C2*D2,IF(SUM(TRANSPOSE(A2&"."&SEQUENCE(10)=TRANSPOSE(SCAN("",$A$2:$A$22,LAMBDA(X,Y,IF(Y="",X,Y)))&""))/1)=0,SUMPRODUCT((SCAN("",$A$2:$A$22,LAMBDA(X,Y,IF(Y="",X,Y)))&""=A2&"")*($C$2:$C$22)*($D$2:$D$22)),SUMPRODUCT((MMULT(TRANSPOSE(A2&"."&SEQUENCE(10)=TRANSPOSE(SCAN("",$A$2:$A$22,LAMBDA(X,Y,IF(Y="",X,Y)))&""))/1,ROW($1:$10)^0)=1)*($C$2:$C$22)*($D$2:$D$22))+SUMPRODUCT((MMULT(TRANSPOSE(A2&".1."&SEQUENCE(10)=TRANSPOSE(SCAN("",$A$2:$A$22,LAMBDA(X,Y,IF(Y="",X,Y)))&""))/1,ROW($1:$10)^0)=1)*($C$2:$C$22)*($D$2:$D$22))))

博爱风雨同舟

=LET(A,SCAN("",$A$2:$A$22,LAMBDA(X,Y,IF(Y="",X,Y))),B,($C$2:$C$22)*($D$2:$D$22),IF(A2="",C2*D2,IF(SUM(TRANSPOSE(A2&"."&SEQUENCE(10)=TRANSPOSE(A&""))/1)=0,SUMPRODUCT((A&""=A2&"")*B),SUMPRODUCT((MMULT(TRANSPOSE(A2&"."&SEQUENCE(10)=TRANSPOSE(A&""))/1,ROW($1:$10)^0)=1)*B)+SUMPRODUCT((MMULT(TRANSPOSE(A2&".1."&SEQUENCE(10)=TRANSPOSE(A&""))/1,ROW($1:$10)^0)=1)*B))))
365版本

mmlzh

数组公司E2=IF(A2="",C2*D2,IF(ISNA(MATCH(A2&".*",A3:A22,)),SUM(OFFSET(E3,,,MATCH(1,(A3:A22<>"")+(B3:B22=""),)-1)),SUM(IFERROR(FIND(A2&".",A3:A22)=1,)*ISERR(FIND(".",SUBSTITUTE(A3:A22,A2&".",,1)))*E3:E22)))

jivy1212

F5定位----粘贴公式---CTRL+回车

1. =IF(A3="",SUBTOTAL(9,E3:E66)*2-SUM(E3:E66),SUMPRODUCT(MMULT(N(A3:A66&""=A2&"."&COLUMN(A:J)),ROW(1:10)^0)*E3:E66))

复制代码