Question

两个复杂的excel公式，请高手帮解释一下， 重金悬赏！

=IF(NOW()>=DATE($Q2,H3,1),SUM(Options!$C17:$C31)+SUMPRODUCT(incomenow*(YEAR(INDIRECT(incomeA))<=$Q2)*(MONTH(INDIRECT(incomeA))<=H3)*INDIRECT(incomeE)*(INDIRECT(incomeD)=TRANSPOSE(bankpay1)))-SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=$Q2)*(MONTH(INDIRECT(bankB))<=H3)*(INDIRECT(bankD)=Parameter!$H4)*INDIRECT(bankF))+SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=$Q2)*(MONTH(INDIRECT(bankB))<=H3)*(INDIRECT(bankD)=Parameter!$H3)*INDIRECT(bankF))-SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=$Q2)*(MONTH(INDIRECT(bankB))<=H3)*INDIRECT(bankG)*(INDIRECT(bankD)=TRANSPOSE(banktype1)))-SUMPRODUCT(spdnow1*(YEAR(INDIRECT(spd1A))<=$Q2)*(MONTH(INDIRECT(spd1A))<=H3)*(INDIRECT(spd1D)=TRANSPOSE(bankpay1))*INDIRECT(spd1F))-SUMPRODUCT(spdnow2*(YEAR(INDIRECT(spd2A))<=$Q2)*(MONTH(INDIRECT(spd2A))<=H3)*(INDIRECT(spd2D)=TRANSPOSE(bankpay1))*INDIRECT(spd2F))-SUMPRODUCT(cardnow*(YEAR(INDIRECT(cardB))<=$Q2)*(MONTH(INDIRECT(cardB))<=H3)*(INDIRECT(cardD)=TRANSPOSE(bankpay1))*INDIRECT(cardE)),0)

=SUM(Options!C17:C31)+SUMPRODUCT(incomenow*(YEAR(INDIRECT(incomeA))<=G3)*(MONTH(INDIRECT(incomeA))<=G4)*INDIRECT(incomeE)*(INDIRECT(incomeD)=TRANSPOSE(bankpay1)))-SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=G3)*(MONTH(INDIRECT(bankB))<=G4)*(INDIRECT(bankD)=Parameter!H4)*INDIRECT(bankF))+SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=G3)*(MONTH(INDIRECT(bankB))<=G4)*(INDIRECT(bankD)=Parameter!H3)*INDIRECT(bankF))-SUMPRODUCT(banknow*(YEAR(INDIRECT(bankB))<=G3)*(MONTH(INDIRECT(bankB))<=G4)*INDIRECT(bankG)*(INDIRECT(bankD)=TRANSPOSE(banktype1)))-SUMPRODUCT(spdnow1*(YEAR(INDIRECT(spd1A))<=G3)*(MONTH(INDIRECT(spd1A))<=G4)*(INDIRECT(spd1D)=TRANSPOSE(bankpay1))*INDIRECT(spd1F))-SUMPRODUCT(spdnow2*(YEAR(INDIRECT(spd2A))<=G3)*(MONTH(INDIRECT(spd2A))<=G4)*(INDIRECT(spd2D)=TRANSPOSE(bankpay1))*INDIRECT(spd2F))-SUMPRODUCT(cardnow*(YEAR(INDIRECT(cardB))<=G3)*(MONTH(INDIRECT(cardB))<=G4)*(INDIRECT(cardD)=TRANSPOSE(bankpay1))*INDIRECT(cardE))
这是一个excel记账本里的（双胞胎拔拔 2009版）
第一个公式是“每月存款累计总额”，第二个是“当月存款累计总额”，
但是计算出来的数值总是多了 719元，一直都搞不清楚哪里多的， 以上每个单元都去看了，都是空的啊。

Answer 1

仔细看了看，也没几个公式。
1、IF()
2、NOW()
3、DATE()
4、SUM()
5、SUMPRODUCT()
6、YEAR()
7、MONTH()
8、TRANSPOSE()
9、INDIRECT()

定义的名称：
1、incomenow
2、incomeA
3、incomeE
4、incomeD
5、bankpay1
6、banknow
7、bankB
8、bankD
9、bankF
10、banktype1
……
难得数，反正可能是一大片区域同时命名。
最终，讲去讲来是一大串引用。过程比较复杂。只有弄懂过得，才解释得了。

Answer 2

被弄晕，用了定义名称，还弄这样长的公式，，建议楼主把这个公式达到的目的详细说一下，让大家给重新编一下。

Answer 3

佩服得五体投地！这么粗一个公式，别说让我弄，就是让我看，都晕！
再次佩服楼主！

Answer 4

复杂的公式，解释起来比制作起来还难，尤其是这个用定义名称的更是难上加难。dgkvcw老兄的回答应该是比较完整的了，推荐他为最佳答案吧。如果真要看问题出在哪里，只有传文件才能解决的。

Answer 5

神啊,这么长的公式看着就头疼.