楼主
herelazy |
Q：从PCB行业的cadence中导出的BOM不符合采购等需要，如何按照下列要求进行快速整理：
1、在将A—F列的内容按照F-E-A优先顺序排序的基础上，把F列的物料名称去重后复制到H列；
2、按照E列有无m，把A列分列在J，L列；采用相同物料的写在一个单元格中，互相之间用"，"间隔；
3、在I，K列统计J,L列对应单元格中器件的个数。
A：- Sub test()
- Dim Rng As Range, Arr, Arr2() As String, N&, I%, A&, B&, T&, Dic2 As Object, Str$
- Set Rng = Range("a1:f" & Cells(Rows.Count, 1).End(3).Row)
- With Rng
- .Sort key1:=[f1], key2:=[e1], key3:=[a1]
- Arr = Rng.Value
- End With
- Set Dic2 = CreateObject("scripting.dictionary")
- ReDim Arr2(1 To 5, 1 To 1)
- Arr2(1, 1) = "物料名称"
- Arr2(2, 1) = "数量"
- Arr2(3, 1) = "有m"
- Arr2(4, 1) = Arr2(2, 1)
- Arr2(5, 1) = "无m"
- For N = LBound(Arr) To UBound(Arr)
- I = IIf(Arr(N, 5) = "m", 3, 5)
- If Dic2.exists(Arr(N, 6)) Then
- If Arr2(I, Val(Dic2(Arr(N, 6)))) = "" Then
- Arr2(I, Val(Dic2(Arr(N, 6)))) = Arr(N, 1)
- Arr2(I - 1, Val(Dic2(Arr(N, 6)))) = 1
- Else
- Arr2(I, Val(Dic2(Arr(N, 6)))) = Arr2(I, Val(Dic2(Arr(N, 6)))) & "," & Arr(N, 1)
- Arr2(I - 1, Val(Dic2(Arr(N, 6)))) = Val(Arr2(I - 1, Val(Dic2(Arr(N, 6))))) + 1
- End If
- Else
- ReDim Preserve Arr2(1 To 5, 1 To UBound(Arr2, 2) + 1)
- Arr2(I, UBound(Arr2, 2)) = Arr(N, 1)
- Arr2(I - 1, UBound(Arr2, 2)) = 1
- Arr2(1, UBound(Arr2, 2)) = Arr(N, 6)
- Dic2.Add Arr(N, 6), UBound(Arr2, 2)
- End If
- Next N
- With CreateObject("vbscript.regexp")
- .Global = True
- For I = 3 To 5 Step 2
- For N = LBound(Arr2, 2) + 1 To UBound(Arr2, 2)
- If Arr2(I, N) <> "" Then
- .Pattern = "[A-Z]+"
- If .test(Arr2(I, N)) Then
- Str = .Execute(Arr2(I, N))(0).Value
- Arr = Split(Replace(Arr2(I, N), Str, ""), ",")
- For A = LBound(Arr) To UBound(Arr) - 1
- For B = A + 1 To UBound(Arr)
- If Val(Arr(A)) > Val(Arr(B)) Then
- T = Arr(A)
- Arr(A) = Arr(B)
- Arr(B) = T
- End If
- Next B
- Next A
- .Pattern = "(\d+)"
- Arr2(I, N) = .Replace(Join(Arr, ","), Str & "$1")
- End If
- End If
- Next N
- Next I
- End With
- [h1].Resize(UBound(Arr2, 2), 5) = Application.Transpose(Arr2)
- Set Dic = Nothing
- Set Dic2 = Nothing
- End Sub
效果图：
 place_txt.rar
P.S.
PCB（PrintedCircuitBoard），中文名称为印制电路板印刷电路板印刷线路板电子元器件电子印刷电路板
cadence ，软件包。
BOM （Bill of Materials），物料清单。
|