=IF(B3<C1,IF(SUM(B3:B4)<C1,IF(SUM(B3:B5)<C1,IF(SUM(B3:B6)<B1,IF(SUM(B3:B7)<C1,"out of
range",(B3*C3+B4*C4+B5*C5+B6*C6+(C1-SUM(B3:B6))*C7)/C1),(B3*C3+B4*C4+B5*C5+(C1-SUM(B3:B5))*C
6)/C1),(B3*C3+B4*C4+(C1-SUM(B3:B4))*C5)/C1),(B3*C3+(C1-B3)*C4)/C1),C4)
B3至B7为输入数量,C3至C7为输入单价,C1为输入结余数量,E1为输出平均单价,即上面公式 解答:公式太复杂,而且受限制。
把A列利用起来,a3=SUM(B3:$B$7),拖至7行(若为X行,则=SUM(B3:$B$X,下拖),A列数据为先进先出的累计数量(待Match的结存数量),=MATCH(C1,A3:A7,-1)找到结存批次。然后计算结存平均单价及发出平均单价
|