PBOM函数备份.docx
《PBOM函数备份.docx》由会员分享,可在线阅读,更多相关《PBOM函数备份.docx(7页珍藏版)》请在冰点文库上搜索。
PBOM函数备份
PBOM编程文件
一、查找编号(从第四行计算)累积主程序:
=IF(A4=0,B4,
IF(A4IF(A4=A3,CONCATENATE(LEFT(C3,LEN(C3)-LEN(B3)),B4),
IF(A4>A3,CONCATENATE(C3,"+",B4),"错误"))))
二、改进版(可从首行计算)查找编号累积主程序:
=IF(or(A2=0,A2=1),B2,
IF(A2IF(A2=A1,CONCATENATE(LEFT(C1,LEN(C1)-LEN(B1)),B2),
IF(A2>A1,CONCATENATE(C1,"+",B2),"错误"))))
三、改进版查找编号累积含零部件ID主程序:
=IF(OR(A2=0,A2=1),CONCATENATE(B2,"#",D2),
IF(A2IF(A2=A1,CONCATENATE(LEFT(C1,LEN(C1)-LEN(B1)-LEN(D1)-1),B2,"#",D2),
IF(A2>A1,CONCATENATE(LEFT(C1,FIND("#",C1)-1),"+",B2,"#",D2),"错误"))))
程序功能:
1、以从PDM系统导出的超级PBOM为基础,依据从PDM系统导出的单车型1级零部件PBOM,编制矩阵化PBOM.
程序运行条件:
1、导出PBOM前需在PDM系统中对PBOM进行打包操作。
2、从系统中导出的各PBOM文件A至F列分别为级别、查找编号、零组件ID、中文名称、数量。
3、车型数量不超过40个,各车型对应的单车型1级零部件PBOM处于车型1——车型40标签中且分布与与车型明细表完全一致。
一级件PBOM处理程序
=IF(or(A2=0,A2=1),B2,
IF(A2IF(A2=A1,CONCATENATE(LEFT(C1,LEN(C1)-LEN(B1)),B2),
IF(A2>A1,CONCATENATE(C1,"+",B2),"错误"))))
PBOM公式、
=IF(B4=0,D4,
IF(B4IF(B4=B3,CONCATENATE(LEFT(E3,LEN(E3)-LEN(D3)),D4),
IF(B4>B3,CONCATENATE(E3,"+",D4),"错误")
)))
自动矩阵化程序备份
SubMacro10()
'
'Macro10Macro
'
'
Columns("E:
E").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("程序主页").Select
Range("E1:
E2").Select
Selection.Copy
Sheets("矩阵PBOM").Select
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
Application.CutCopyMode=False
Selection.AutoFillDestination:
=Range("E2:
E7386")
Range("E2:
E7386").Select
Columns("H:
I").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("5MT标准京五").Select
Columns("E:
E").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("程序主页").Select
Selection.Copy
Sheets("5MT标准京五").Select
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
Application.CutCopyMode=False
Selection.AutoFillDestination:
=Range("E2:
E3257")
Range("E2:
E3257").Select
Sheets("一级件BOM").Select
Columns("E:
E").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("程序主页").Select
Selection.Copy
Sheets("一级件BOM").Select
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
Application.CutCopyMode=False
Selection.AutoFillDestination:
=Range("E2:
E647")
Range("E2:
E647").Select
Sheets("矩阵PBOM").Select
ActiveWindow.SmallScrollDown:
=-12
Range("H2").Select
ActiveCell.FormulaR1C1="=VLOOKUP(RC[-3],'5MT标准京五'!
C[-3]:
C[-1],3,0)"
Selection.AutoFillDestination:
=Range("H2:
H7386")
Range("H2:
H7386").Select
Range("I2").Select
ActiveCell.FormulaR1C1="=VLOOKUP(RC[-4],一级件BOM!
C[-4]:
C[-3],2,0)"
ActiveWindow.SmallScrollDown:
=-15
Selection.AutoFillDestination:
=Range("I2:
I7386")
Range("I2:
I7386").Select
Columns("H:
I").Select
Selection.Copy
Selection.PasteSpecialPaste:
=xlPasteValues,Operation:
=xlNone,SkipBlanks_
:
=False,Transpose:
=False
EndSub
程序密码123456
Sub测试循环()
'
'测试循环Macro
'
'
Sheets("矩阵PBOM").Select
Columns("E:
E").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("程序主页").Select
Range("E3:
E4").Select
Selection.Copy
Sheets("矩阵PBOM").Select
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
Application.CutCopyMode=False
Selection.AutoFillDestination:
=Range("E2:
E7386")
Range("E2:
E7386").Select
Columns("F:
F").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Range("F4").Select
Sheets("5MT标准京五").Select
ActiveWindow.SmallScrollDown:
=-9
Columns("E:
E").Select
Selection.InsertShift:
=xlToRight,CopyOrigin:
=xlFormatFromLeftOrAbove
Sheets("程序主页").Select
Selection.Copy
Sheets("5MT标准京五").Select
Range("E1").Select
ActiveSheet.Paste
Range("E2").Select
Application.CutCopyMode=False
Selection.AutoFillDestination:
=Range("E2:
E3257")
Range("E2:
E3257").Select
ActiveWindow.SmallScrollDown:
=-21
Range("F18").Select
Sheets("矩阵PBOM").Select
Range("F2").Select
ActiveCell.FormulaR1C1="=VLOOKUP(RC[-1],'5MT标准京五'!
C[-1]:
C[1],3,0)"
Selection.AutoFillDestination:
=Range("F2:
F7386")
Range("F2:
F7386").Select
Sheets("程序主页").Select
Range("B15").Select
Selection.Copy
Sheets("矩阵PBOM").Select
Range("F1").Select
ActiveSheet.Paste
EndSub