EXCEL {=IF(OR($A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)),IF(L=1,MIN($K9-SUM($R9:R9),$M9,$J9*SS),IF(SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS>=1,MIN((1-SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS
来源:学生作业帮助网 编辑:作业帮 时间:2024/06/23 14:15:47
![EXCEL {=IF(OR($A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)),IF(L=1,MIN($K9-SUM($R9:R9),$M9,$J9*SS),IF(SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT(](/uploads/image/z/3247491-3-1.jpg?t=EXCEL+%EF%BD%9B%3DIF%28OR%28%24A9%3D0%2C%24E9%3D0%2CS%3D0%2C%24N9%3ES%247%2C%24K9%3DSUM%28%24R9%3AR9%29%29%2CIF%28L%3D1%2CMIN%28%24K9-SUM%28%24R9%3AR9%29%2C%24M9%2C%24J9%2ASS%29%2CIF%28SUM%28S8%3AINDIRECT%28T%26%28M%2BROW%28%24A%248%29%29%29%2F%24J8%3AINDIRECT%28%22J%22%26%28M%2BROW%28%24A%248%29%29%29%29%2A1%2FSS%3E%3D1%2CMIN%28%281-SUM%28S8%3AINDIRECT%28T%26%28M%2BROW%28%24A%248%29%29%29%2F%24J8%3AINDIRECT%28%22J%22%26%28M%2BROW%28%24A%248%29%29%29%29%2A1%2FSS)
EXCEL {=IF(OR($A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)),IF(L=1,MIN($K9-SUM($R9:R9),$M9,$J9*SS),IF(SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS>=1,MIN((1-SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS
EXCEL
{=IF(OR($A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)),IF(L=1,MIN($K9-SUM($R9:R9),$M9,$J9*SS),IF(SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS>=1,MIN((1-SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS)*$J9*SS,$K9-SUM($R9:R9),$M9,$J9*SS))))}
这是里的的一个公式,计算某日能做多少产品,由于公式太长,我基础太浅,此公式的计算过程,还有INDIRECT在此公式中的作用.
附上图片,在右下角的这个800的位置输入的此公式
EXCEL {=IF(OR($A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)),IF(L=1,MIN($K9-SUM($R9:R9),$M9,$J9*SS),IF(SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS>=1,MIN((1-SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS
第一步判断$A9=0,$E9=0,S=0,$N9>S$7,$K9=SUM($R9:R9)这些条件是否有成立的,有成立的就显示为空,都不成立时就到
第二步L=1 成立时执行MIN($K9-SUM($R9:R9) 不成立$M9,$J9*SS
第三步SUM(S8:
(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS>=1这个是否成立,成立时为空不成立就执行MIN((1-SUM(S8:INDIRECT(T&(M+ROW($A$8)))/$J8:INDIRECT("J"&(M+ROW($A$8))))*1/SS)*$J9*SS)*$J9*SS,$K9-SUM($R9:R9),$M9,$J9*SS
注释:
IF 条件判断函数
sum 指定区域内的数字求合
min 指定区域内的数字最小值
NDIRECT 此函数立即对引用进行计算,并显示其内容
ROW 返回引用的行号