=IF(ISERROR(IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),"")),SUM($E$2:$E$14)-SUM($F1:F$2),IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组"
来源：学生作业帮助网 编辑：六六作业网 时间：2024/11/25 08:57:50
=IF(ISERROR(IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),"")),SUM($E$2:$E$14)-SUM($F1:F$2),IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组"
=IF(ISERROR(IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),"")),SUM($E$2:$E$14)-SUM($F1:F$2),IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),""))
=IF(ISERROR(IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),"")),SUM($E$2:$E$14)-SUM($F1:F$2),IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组"
公式其实就是：
=IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),
出现错误值时,为空,所以公式看上去很长.如果是07版后的可用
=IFERROR(IF(A2"",SUM(OFFSET($E$1,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,))),""),""),短些.
含义：数组公式,对金额栏分组求和,COUNTA($A$2:A2)非空单元 格个数,COUNTA($A$2:A2)&"组"是第几组,MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,),第几组在表格中的次序,结果为偏移行数,MATCH((COUNTA($A$2:A2)+1)&"组",$A$2:$A$14,)-MATCH(COUNTA($A$2:A2)&"组",$A$2:$A$14,)为取几行计数,SUM求和