多阶bom计物料需求怎样求

Ansun邓

各位大佬，如图中根据bom的层级关系该如何计算物料需求？特别是有共用物料时，我试了好几种公式都出错了，求大佬解救

part98

=IF(B4>1,VLOOKUP(LEFT(A4,B4*2-1),A:J,10,0),$E$2)*F4

E学子

1. =IFERROR(XLOOKUP(B4-1,$B$4:B4,$H$4:H4,,,-1)*F4,$E$2*F4)

复制代码

Ansun邓

part98 发表于 2024-5-19 11:51
=IF(B4>1,VLOOKUP(LEFT(A4,B4*2-1),A:J,10,0),$E$2)*F4

老师，这个公式套入后，在有相同共同的物料的项出错了，只计算出第一次物料出现的那个数了

part98

Ansun邓 发表于 2024-5-19 12:00
老师，这个公式套入后，在有相同共同的物料的项出错了，只计算出第一次物料出现的那个数了

=XLOOKUP(B4-1,$B$4:B4,$K$4:K4,$E$2,,-1)*F4

计划小文

=IFS(LEN(A4)-LEN(SUBSTITUTE(A4,".",""))=3,F4*XLOOKUP(TEXTBEFORE(SUBSTITUTE(A4,".","+",3),"+"),A:A,F:F,0)*XLOOKUP(--TEXTBEFORE(SUBSTITUTE(A4,".","+",2),"+"),A:A,F:F,0)*$E$2,LEN(A4)-LEN(SUBSTITUTE(A4,".",""))=2,F4*XLOOKUP(--TEXTBEFORE(SUBSTITUTE(A4,".","+",2),"+"),A:A,F:F,0)*$E$2,LEN(A4)-LEN(SUBSTITUTE(A4,".",""))=1,F4*$E$2)

ssinandy

=IF(B4=1,$E$2*F4,IF(B4=2,$E$2*F4*INDEX($F$4:F4,MATCH(MID(A4,1,3),$A$4:A4,0)),$E$2*F4*INDEX($F$4:F4,MATCH(MID(A4,1,3),$A$4:A4,0))*INDEX($F$4:F4,MATCH(MID(A4,1,5),$A$4:A4,0))))

dambcer

Function f(r As Range)
    Dim col As New Collection, rng As Range
    Dim acRow, s, i
    acRow = r.Row
    Set rng = r
    Do Until rng(1, 2) = 1
        If r.Value Like rng.Value & "*" Then
            col.Add rng(1, 6).Address(0, 0)
        End If
        acRow = acRow - 1
        Set rng = Cells(acRow, 1)
    Loop
    col.Add rng(1, 6).Address(0, 0)
    For i = 1 To col.Count
        s = s & "*" & col(i)
    Next
    Set col = Nothing
    f = Application.Evaluate("=e2" & s)
End Function

Ansun邓

3楼和5楼的结果都对了，十分感谢两位老师；也感谢其他回复贴子的老师，再次谢谢，大家工作顺利

pxy0409

xlookup的第6参数起了关键作用,可能没有比这个更简单的做法了