=SUMPRODUCT((YEAR(A2:A100)=2009)*(MONTH(A2:A100)=9)*(B2:B100="Donald")*(C2:C100<>"Bank")*(C2:C100<>"Inc")*(D2:D100={"USD","GBP","HKD"})*{6.83,11.25,0.88}*F2:F100)+SUMPRODUCT((YEAR(A2:A100)=2009)*(MONTH(A2:A100)=9)*(B2:B100="Donald")*(C2:C100="Bank")*(C2:C100<>"Inc")*(D2:D100={"USD","GBP","HKD"})*{6.83,11.25,0.88}*E2:E100)
感觉重复了月份及汇率的计算,这样使计算变得很慢。。请问兄弟们这句该如何简化?谢谢! "+"号两边的两个SUMPRODUCT,其中 1. (YEAR(A2:A100)=2009) 2. (MONTH(A2:A100)=9) 3. (B2:B100="Donald") 4. (C2:C100<>"Inc") 5. (D2:D100={"USD","GBP","HKD"})*{6.83,11.25,0.88}
这5个条件都是重复的,而 1. (C2:C100<>"Bank") 与 (C2:C100="Bank")
2. F2:F100 与 E2:E100
这两个条件是不同的。
5081mh1c.rar
(3.66 KB, 下载次数: 1)
|