暂时卡控值一栏用函数VLOOKUP手动匹配。代码放置要求：以下代码需放在ThisWorkbook 这栏里，若放在工作表1等其余表格里，运行结果会不正确。声明：以下代码为自己来自互联网，盗版必究将FT相关数据从MES导出后，放入代码，即可运行。注意事项：若出现这种情况则说明数据未拉齐全，缺少蕞早得FT（E040）数据。Sub cz12()Worksheets.Add.Name = "提取唯一值"Worksheets("提取唯一值").Move after:=Worksheets("Sheet1")Sheets("Sheet1").Range("A:A").Copy Sheets("提取唯一值").Range("A1")[A:A].RemoveDuplicates Columns:=1With Sheets("提取唯一值").Cells .Cells(1, 2) = "工站" .Cells(1, 3) = "日期" .Cells(1, 4) = "料号" .Cells(1, 5) = "COF批号" .Cells(1, 6) = "TAPE批号" .Cells(1, 7) = "投入(PCS)" .Cells(1, 8) = "产出(PCS)" .Cells(1, 9) = "实际损耗(PCS)" .Cells(1, 10) = "直通率" .Cells(1, 11) = "卡控值" .Cells(1, 12) = "异常描述" .Rows("1:1").RowHeight = 30 .Rows("1:1").Font.Bold = TrueEnd WithSet zt = Rows("2:4")Range(zt, zt.End(xlDown)).RowHeight = 23 With Sheets("提取唯一值").Range("A:L") .Font.Name = "楷体" .Font.Size = 11 .EntireColumn.AutoFit .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenterEnd Withendrow = Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row For i = 2 To endrow Set y = Sheets("Sheet1").Range("a" & i) Sheets("提取唯一值").Select Z = Range("a:a").Find(y, Range("a1")).Address If Sheets("Sheet1").Cells(i, 4) = "FT" Then Range(Z).Offset(0, 1) = Sheets("Sheet1").Cells(i, 4) Range(Z).Offset(0, 3) = Sheets("Sheet1").Cells(i, 18) Range(Z).Offset(0, 4) = Sheets("Sheet1").Cells(i, 1) Range(Z).Offset(0, 5) = Sheets("Sheet1").Cells(i, 14) Range(Z).Offset(0, 6) = Sheets("Sheet1").Cells(i, 5) ElseIf Sheets("Sheet1").Cells(i, 4) = "FT-Data" Then Range(Z).Offset(0, 7) = Sheets("Sheet1").Cells(i, 6) Range(Z).Offset(0, 2) = Sheets("Sheet1").Cells(i, 10) Range(Z).Offset(0, 8).FormulaR1C1 = "=RC[-2]-RC[-1]" Range(Z).Offset(0, 9).FormulaR1C1 = "=RC[-2]/RC[-3]" Range(Z).Offset(0, 11).FormulaR1C1 = "=IF(RC[-2]<RC[-1],""Low yield"","""")" ElseIf Sheets("Sheet1").Cells(i, 4) = "FT2" Then Range(Z).Offset(0, 7) = Sheets("Sheet1").Cells(i, 6) Range(Z).Offset(0, 2) = Sheets("Sheet1").Cells(i, 10) Range(Z).Offset(0, 8).FormulaR1C1 = "=RC[-2]-RC[-1]" Range(Z).Offset(0, 9).FormulaR1C1 = "=RC[-2]/RC[-3]" Range(Z).Offset(0, 11).FormulaR1C1 = "=IF(RC[-2]<RC[-1],""Low yield"","""")" ElseIf Sheets("Sheet1").Cells(i, 4) = "OLP" Then Range(Z).Offset(0, 7) = Sheets("Sheet1").Cells(i, 6) Range(Z).Offset(0, 2) = Sheets("Sheet1").Cells(i, 10) Range(Z).Offset(0, 8).FormulaR1C1 = "=RC[-2]-RC[-1]" Range(Z).Offset(0, 9).FormulaR1C1 = "=RC[-2]/RC[-3]" Range(Z).Offset(0, 11).FormulaR1C1 = "=IF(RC[-2]<RC[-1],""Low yield"","""")" ElseIf Sheets("Sheet1").Cells(i, 4) = "EQC" Then Range(Z).Offset(0, 7) = Sheets("Sheet1").Cells(i, 6) Range(Z).Offset(0, 2) = Sheets("Sheet1").Cells(i, 10) Range(Z).Offset(0, 8).FormulaR1C1 = "=RC[-2]-RC[-1]" Range(Z).Offset(0, 9).FormulaR1C1 = "=RC[-2]/RC[-3]" Range(Z).Offset(0, 11).FormulaR1C1 = "=IF(RC[-2]<RC[-1],""Low yield"","""")" End If Next iSheets("提取唯一值").Range("a:a").Interior.Pattern = xlNoneSheets("提取唯一值").Columns("C:C").NumberFormatLocal = "m""月""d""日"";等"Sheets("提取唯一值").Columns("J:J").NumberFormatLocal = "0.00%"Sheets("提取唯一值").Columns("G:G").NumberFormatLocal = "#,##0"Sheets("提取唯一值").Columns("H:H").NumberFormatLocal = "#,##0"Rows(1).InsertWith Sheets("提取唯一值").Cells .Cells(1, 3) = "批号信息" .Range("C1:J1").Merge .Cells(1, 11) = "异常反馈" .Range("k1:l1").Merge .Rows("1:2").RowHeight = 30 .Rows("1:2").Font.Bold = TrueEnd WithWith Sheets("提取唯一值").Rows(1) .Font.Name = "楷体" .Font.Size = 11 .EntireColumn.AutoFit .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenterEnd WithSet ss = Sheets("提取唯一值").Range("A2:L2")Sheets("提取唯一值").Range(ss, ss.End(xlDown)).Borders.LineStyle = xlContinuousSheets("提取唯一值").Range("a1:l1").Borders.LineStyle = xlContinuousMsgBox "提取完毕"End Sub
