Indented Inventory BOM如何转为最终的单层采购BOM

Indented inventory BOM如何转为最终的Excel格式采购BOM

Objective：从Oracle EBS中Export出来的Indented BOM，通常不能直观显示哪些是需要采购的物料，有时候，需要得到一个最终，实际下单给供应商的物料清单，用来分析下单情况，物料齐料情况，NPI BOM正确性检查等等。

简言之，我们很多时候需要单层BOM（使复杂问题简单化）：这个BOM中哪些物料是需要我们实际下单的List。

1，Export Indented inventory BOM

2，在EXCEL中打开".tsv"文档，并保存为xlsx格式，以便后面的VBA处理。

我们可以看到，此时打开的文件第一列是Level1，Level2，Level3，Level4合并在一起，并不能很好的解释“Indented”。

Indented是指锯齿状的多层级BOM。

3，将第二列格式处理：

3.1，替换掉第二列所有的空格，替换掉第二列所有的“-”

3.2，选择第二列，然后选择“数据”>数据工具>分列，将第二列设置为“文本”

4，筛选实际采购层：

4.1，逻辑设计：

a，需要使用Function，不需要返回值，在Function解决标示采购（P），非采购（Non-P）

b，逐条读取数据，当前数据的L值小于N+1的L值时，得到L的一个区间值：首尾位置。并传递给Function

c，我们将采购(P)和非采购(Non-P)放在第6列

运行结果如下，很清晰知道一个多层BOM中，哪些是需要直接下单（把第6列=P的在EXCEL中筛选即可），哪些是不需要下单的。

Level	Item	Description	Revision	Type	P/Non-P
1	6267000	OUTER CASING 1 CPL CF	0	Subassembly	Non-P
2	6267012	CROSS STIFFENING FOR OUTER CASING CF	0	Purchased item	P
2	6267032	COVER BACK 6.10 PREASSEMBLED CF	0	Subassembly	Non-P
3	6267013	BACK COVER 6.10 FOR OUTER CASING CF	0	Purchased item	P
3	6267015	CARIER FOR COOL FAN OUTER CASING CF	0	Purchased item	P

多层BOM需要我们获取很多不同的区间的首尾地址，1~1，2~2，3~3，4~4,其中第一个L1到第二个L1中嵌套很多L2，L2中又嵌套很多L3，这就意味这多层BOM有很多的嵌套，造成分析比单层BOM复杂。因为我自己也是半吊子，所以，我花了近1天半的时间分析和编写，修正程序。其中光是循环，就尝试了For each range.... next range，Do...Loop While和find()组合, For...next i好几种。最后还是选择Function处理，代码如下：

Sub indented_BOM()

Dim i, j, k, m, n As Integer
Dim c As Range
Dim f_add, e_add As String


k = Range("A65536").End(xlUp).Row

For i = 2 To k
    f_add = Cells(i, 1).Address
    If Cells(i + 1, 1) > Cells(i, 1) Then
        j = i + 1
        For m = j To k
        If Cells(i, 1) = Cells(m, 1) Then
        Exit For
        End If
        Next m
    n = Loct(i, m)
    End If
    
Next i
  
  
For i = 2 To k
If Int(Cells(i, 1)) = 1 And Cells(i, 6) = "" Then
Cells(i, 6) = "P"
End If
Next i
    
End Sub


Function Loct(a, b)

Dim i, j As Integer
Debug.Print a, b

'处理传递下来是1的情形
'1，处理1和2；2，处理2和3；3，处理3和4。。。。以此类推

If Cells(a, 1) = 1 Then
'如果L1=P，那么区间内所有L2=Non-P；如果L1=Subassembly,2=P,那么区间的2=P，2=R，区间的L2=Non-P
    If Cells(a, 5) = "Subassembly" Then Cells(a, 6) = "Non-P"
    If Cells(a, 5) = "Purchased item" Then Cells(a, 6) = "P"
    If Cells(a, 5) = "Subassembly" Then
        For i = a + 1 To b - 1
        If Cells(i, 1) = 2 And Cells(i, 5) = "Purchased item" And Cells(i, 6) = "" Then
        Cells(i, 6) = "P"
        ElseIf Cells(i, 1) = 2 And Cells(i, 5) = "Subassembly" And Cells(i, 6) = "" Then
        Cells(i, 6) = "Non-P"
        End If
        Next i
    ElseIf Cells(a, 5) = "Purchased item" Then
        For i = a + 1 To b - 1
        Cells(i, 6) = "Non-P"
        Next i
    End If
'End If

'传递L2的首尾地址下来，只需要处理a+1 to b-1区间的Item
'使用Cells(i, 6) = ""来跳过Cells（a,6)已存在属性的Item
ElseIf Cells(a, 1) = 2 Then
    If Cells(a, 5) = "Subassembly" Then
        For i = a + 1 To b - 1
        If Cells(i, 1) = 3 And Cells(i, 5) = "Purchased item" And Cells(i, 6) = "" Then
        Cells(i, 6) = "P"
        ElseIf Cells(i, 1) = 3 And Cells(i, 5) = "Subassembly" And Cells(i, 6) = "" Then
        Cells(i, 6) = "Non-P"
        End If
        Next i
    ElseIf Cells(a, 5) = "Purchased item" Then
        For i = a + 1 To b - 1
        Cells(i, 6) = "Non-P"
        Next i
    End If

    
'End If

'传递L3的首尾地址下来，只需要处理a+1 to b-1区间的Item
'使用Cells(i, 6) = ""来跳过Cells（a,6)已存在属性的Item
ElseIf Cells(a, 1) = 3 Then
   If Cells(a, 5) = "Subassembly" Then
        For i = a + 1 To b - 1
        If Cells(i, 1) = 4 And Cells(i, 5) = "Purchased item" And Cells(i, 6) = "" Then
        Cells(i, 6) = "P"
        ElseIf Cells(i, 1) = 4 And Cells(i, 5) = "Subassembly" And Cells(i, 6) = "" Then
        Cells(i, 6) = "Non-P"
        End If
        Next i
    ElseIf Cells(a, 5) = "Purchased item" Then
    
    For i = a + 1 To b - 1
        Cells(i, 6) = "Non-P"
        Next i
    End If
    
End If


End Function

5，Indented处理：

5.1，打开VBA编辑器，增加一个模块（常用的VBA代码建议存放在Personal.xlsb)

Sub indented()
Dim i As Integer

'insert 3 new Columns
For i = 1 To 3
    Columns(2).Insert
    Cells(1, 2) = "L" & 5 - i
Next i
Cells(1, 1) = "L1"
'Below is for Indented
For i = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(i, 1) = "2" Then
    Cells(i, 2) = 2
    Cells(i, 1) = ""
    ElseIf Cells(i, 1) = "3" Then
    Cells(i, 3) = 3
    Cells(i, 1) = ""
    ElseIf Cells(i, 1) = "4" Then
    Cells(i, 4) = 4
    Cells(i, 1) = ""
    End If
Next i

End Sub

5.2，运行之后，得到一个锯齿状的，多层级BOM，如下：