=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/06/23 16:26:14
![=IF(ISERROR(IF(A2](/uploads/image/z/14409941-5-1.jpg?t=%3DIF%28ISERROR%28IF%28A2%22%22%2CSUM%28OFFSET%28%24E%241%2CMATCH%28COUNTA%28%24A%242%3AA2%29%26%22%E7%BB%84%22%2C%24A%242%3A%24A%2414%2C%29%2CMATCH%28%28COUNTA%28%24A%242%3AA2%29%2B1%29%26%22%E7%BB%84%22%2C%24A%242%3A%24A%2414%2C%29-MATCH%28COUNTA%28%24A%242%3AA2%29%26%22%E7%BB%84%22%2C%24A%242%3A%24A%2414%2C%29%29%29%2C%22%22%29%29%2CSUM%28%24E%242%3A%24E%2414%29-SUM%28%24F1%3AF%242%29%2CIF%28A2%22%22%2CSUM%28OFFSET%28%24E%241%2CMATCH%28COUNTA%28%24A%242%3AA2%29%26%22%E7%BB%84%22)
=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求和