作业帮哪位excel高手解释下面公式的含义啊,有个图片供大家参考,急,
来源：学生作业帮 编辑：作业帮 分类：综合作业 时间：2024/05/12 09:25:28
哪位excel高手解释下面公式的含义啊,有个图片供大家参考,急,
=SUM(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)*IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),1,2,1,16)=$A2,1,0)*IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0),1,1),1,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1)=$A$1,1,0))
=SUM(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)*IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),1,2,1,16)=$A2,1,0)*IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0),1,1),1,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1)=$A$1,1,0))
多条件求和公式.没有原表,只能分析下.
(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)
是需要求和的区域,其中区域的定义为以下：
OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1)
的意思是从表格 B$1单元格取值（下拉不会变,右拉会变成C$1）,构成
XXX月!$A$1 形式的引用,作为OFFSET函数的 引用区域；然后在 XXX月!表的A列查找
NB R&D allocation
并取其前一个单元格形成一组数据A.
(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)
完整就是从上面的数据A代表的单元格开始,向右向下各偏移2个单元格,取符合（MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1）条件的值作为高度,16作为宽度的区域作为计算区域.
IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),1,2,1,16)=$A2,1,0)
是一个条件（就不详细解释了,符合条件的为1,否则为0）
IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0),1,1),1,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1)=$A$1,1,0))
是第二个条件.（就不详细解释了,符合条件的为1,否则为0）
然后生成符合条件项目的和.
(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)
是需要求和的区域,其中区域的定义为以下：
OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1)
的意思是从表格 B$1单元格取值（下拉不会变,右拉会变成C$1）,构成
XXX月!$A$1 形式的引用,作为OFFSET函数的 引用区域；然后在 XXX月!表的A列查找
NB R&D allocation
并取其前一个单元格形成一组数据A.
(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),2,2,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,16)
完整就是从上面的数据A代表的单元格开始,向右向下各偏移2个单元格,取符合（MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1）条件的值作为高度,16作为宽度的区域作为计算区域.
IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1,1),1,2,1,16)=$A2,1,0)
是一个条件（就不详细解释了,符合条件的为1,否则为0）
IF(OFFSET(OFFSET(INDIRECT(B$1&"月!$A$1"),MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0),1,1),1,MATCH("Total NB R&D expense",INDIRECT(B$1&"月!A:A"),0)-MATCH("NB R&D allocation",INDIRECT(B$1&"月!A:A"),0)-1,1)=$A$1,1,0))
是第二个条件.（就不详细解释了,符合条件的为1,否则为0）
然后生成符合条件项目的和.