excel 小弟下跪了=INDEX(BOM!C:C,MIN(IF((COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0)*MMULT((OFFSET(BOM!$B$3,bom数量)=TRANSPOSE(OFFSET(计划投产!$A$3,投产数量)))*IFERROR(-OFFSET(BOM!$D$3,bom数量),0),IFERROR(-OFFSET(计划投产!$E$3,
来源:学生作业帮助网 编辑:作业帮 时间:2024/07/19 16:44:08
![excel 小弟下跪了=INDEX(BOM!C:C,MIN(IF((COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0)*MMULT((OFFSET(BOM!$B$3,bom数量)=TRANSPOSE(OFFSET(计划投产!$A$3,投产数量)))*IFERROR(-OFFSET(BOM!$D$3,bom数量),0),IFERROR(-OFFSET(计划投产!$E$3,](/uploads/image/z/2176641-9-1.jpg?t=excel+%E5%B0%8F%E5%BC%9F%E4%B8%8B%E8%B7%AA%E4%BA%86%3DINDEX%28BOM%21C%3AC%2CMIN%28IF%28%28COUNTIF%28A%242%3AA2%2COFFSET%28BOM%21%24C%243%2Cbom%E6%95%B0%E9%87%8F%29%29%3D0%29%2AMMULT%28%28OFFSET%28BOM%21%24B%243%2Cbom%E6%95%B0%E9%87%8F%29%3DTRANSPOSE%28OFFSET%28%E8%AE%A1%E5%88%92%E6%8A%95%E4%BA%A7%21%24A%243%2C%E6%8A%95%E4%BA%A7%E6%95%B0%E9%87%8F%29%29%29%2AIFERROR%28-OFFSET%28BOM%21%24D%243%2Cbom%E6%95%B0%E9%87%8F%29%2C0%29%2CIFERROR%28-OFFSET%28%E8%AE%A1%E5%88%92%E6%8A%95%E4%BA%A7%21%24E%243%2C)
excel 小弟下跪了=INDEX(BOM!C:C,MIN(IF((COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0)*MMULT((OFFSET(BOM!$B$3,bom数量)=TRANSPOSE(OFFSET(计划投产!$A$3,投产数量)))*IFERROR(-OFFSET(BOM!$D$3,bom数量),0),IFERROR(-OFFSET(计划投产!$E$3,
excel 小弟下跪了
=INDEX(BOM!C:C,MIN(IF((COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0)*MMULT((OFFSET(BOM!$B$3,bom数量)=TRANSPOSE(OFFSET(计划投产!$A$3,投产数量)))*IFERROR(-OFFSET(BOM!$D$3,bom数量),0),IFERROR(-OFFSET(计划投产!$E$3,投产数量),0)),ROW(OFFSET(BOM!$C$3,bom数量)),2^20)))&""
其中定义名称:
bom数量 投产数量
COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0
还有 min函数 也可以跟index
这个公式小弟研究一个月了,求知若渴,
excel 小弟下跪了=INDEX(BOM!C:C,MIN(IF((COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0)*MMULT((OFFSET(BOM!$B$3,bom数量)=TRANSPOSE(OFFSET(计划投产!$A$3,投产数量)))*IFERROR(-OFFSET(BOM!$D$3,bom数量),0),IFERROR(-OFFSET(计划投产!$E$3,
COUNTIF(A$2:A2,OFFSET(BOM!$C$3,bom数量))=0
此句是在A$2:A2,这个范围内去找一个值,这个值是一组单元格,单元格是BOM!$C$3偏移得来的.
OFFSET(BOM!$C$3,bom数量,指出偏移的单元格是从BOM!$C$3,开始,宽度为"bom数量"返回的那么宽.
=0,
意思是找不到
MIN,当然可以INTEX合用,因为它返回的是后面一组数的最小值,而INDEX,它在这里只不过要有一个数,看应返回第几行.这不正合适吗?