本帖最后由 丢丢表格 于 2019-5-30 17:32 编辑
1、用 TEXT :
=SUM(MOD(TEXT(SUMIFS(D:D,B:B,"<="&B4:B12,C:C,"购")-SUMIF(C:C,"销",D:D),"[>"&D4:D12&"]"&D4:D12/1000&";!0;!.000"),1)*1000*E4:E12*(C4:C12=C4))
2、向量法:
=SUMIF(C:C,"购",F:F)-SUM(LOOKUP(ROW(INDIRECT("1:"&SUMIF(C:C,"销",D:D)))-1,SUMIFS(D:D,B:B,"<"&B4:B12,C:C,"购")/(C4:C12="购"),E4:E12))
3、海鲜函数:
=SUM(FREQUENCY(ROW(INDIRECT("1:"&I2)),SUMIFS(D:D,B:B,"<="&B4:B12,C:C,"购")-SUMIF(C:C,"销",D:D))*E4:E13)
4、优化海鲜
- =SUM(FREQUENCY(ROW(INDIRECT("1:"&I2)),SUMIFS(D:D,B:B,">="&B4:B12,C:C,"购")*(C4:C12="购"))*E4:E13)
复制代码
综合起来看,还是海鲜函数好~~。
|