1 line
77 KiB
Plaintext
1 line
77 KiB
Plaintext
{"ID":"20250919194325-b1ykr96","Spec":"2","Type":"NodeDocument","Properties":{"id":"20250919194325-b1ykr96","title":"自用VBA全解","type":"doc","updated":"20250919201437"},"Children":[{"ID":"20250919194617-nbwqppi","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919194617-nbwqppi","updated":"20250919195148"},"Children":[{"Type":"NodeText","Data":"一、FORCFEST数据库取数"}]},{"ID":"20250919194658-t997v9p","Type":"NodeList","ListData":{},"Properties":{"id":"20250919194658-t997v9p","updated":"20250919194745"},"Children":[{"ID":"20250919194704-bo0bbki","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194704-bo0bbki","updated":"20250919194704"},"Children":[{"ID":"20250919194704-yk8xwa5","Type":"NodeParagraph","Properties":{"id":"20250919194704-yk8xwa5","updated":"20250919194704"},"Children":[{"Type":"NodeText","Data":"A依次运行BCD"}]}]},{"ID":"20250919194713-vxprrom","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194713-vxprrom","updated":"20250919194745"},"Children":[{"ID":"20250919194723-d6cxbl1","Type":"NodeSuperBlock","Properties":{"id":"20250919194723-d6cxbl1","updated":"20250919194745"},"Children":[{"Type":"NodeSuperBlockOpenMarker"},{"Type":"NodeSuperBlockLayoutMarker","Data":"col"},{"ID":"20250919194617-0msa1l7","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919194617-0msa1l7","style":"flex: 0 0 auto; width: calc(89.999% - 12px);","updated":"20250919194745"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dmJh"},{"Type":"NodeCodeBlockCode","Data":"Sub A()\n Call B\n Call Q\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]},{"ID":"20250919194736-vxl6ova","Type":"NodeSuperBlock","Properties":{"id":"20250919194736-vxl6ova","style":"flex: 0 0 auto; width: calc(9.998% - 12px);","updated":"20250919194737"},"Children":[{"Type":"NodeSuperBlockOpenMarker"},{"Type":"NodeSuperBlockLayoutMarker","Data":"row"},{"ID":"20250919194713-6q097u0","Type":"NodeParagraph","Properties":{"id":"20250919194713-6q097u0","updated":"20250919194737"}},{"ID":"20250919194736-j5luobw","Type":"NodeParagraph","Properties":{"id":"20250919194736-j5luobw","updated":"20250919194736"}},{"ID":"20250919194737-gqpljsm","Type":"NodeParagraph","Properties":{"id":"20250919194737-gqpljsm","updated":"20250919194737"}},{"ID":"20250919194737-h6o83r8","Type":"NodeParagraph","Properties":{"id":"20250919194737-h6o83r8","updated":"20250919194737"}},{"Type":"NodeSuperBlockCloseMarker"}]},{"Type":"NodeSuperBlockCloseMarker"}]}]}]},{"ID":"20250919194604-2pdagjm","Type":"NodeList","ListData":{},"Properties":{"id":"20250919194604-2pdagjm","updated":"20250919194916"},"Children":[{"ID":"20250919194749-a9nx9bb","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194749-a9nx9bb","updated":"20250919194749"},"Children":[{"ID":"20250919194749-zoaa1di","Type":"NodeParagraph","Properties":{"id":"20250919194749-zoaa1di","updated":"20250919194908"},"Children":[{"Type":"NodeText","Data":"数据库主逻辑并生成BOM无匹配表 V2.0"}]}]},{"ID":"20250919194910-tsltq6v","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194910-tsltq6v","style":"--en-bullet-line-height: 38px;","updated":"20250919194916"},"Children":[{"ID":"20250919194915-eh52bec","Type":"NodeSuperBlock","Properties":{"id":"20250919194915-eh52bec","updated":"20250919194916"},"Children":[{"Type":"NodeSuperBlockOpenMarker"},{"Type":"NodeSuperBlockLayoutMarker","Data":"col"},{"ID":"20250919194547-ylk6jle","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919194547-ylk6jle","style":"flex: 0 0 auto; width: calc(89.997% - 12px);","updated":"20250919194916"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dmJh"},{"Type":"NodeCodeBlockCode","Data":"Sub B()\n Application.ScreenUpdating = False\n Application.Calculation = xlCalculationManual\n Application.EnableEvents = False\n \n Dim wsFocest As Worksheet, wsBOM As Worksheet, wsOutput As Worksheet, wsUnmatched As Worksheet\n Dim lastRowF As Long, lastRowB As Long, lastRowO As Long, lastRowU As Long\n Dim i As Long, j As Long, k As Long, col As Long\n Dim keyPart As String, material As String, usage As Double\n Dim lic As Double, focestCol1 As String, focestCol2 As String\n Dim weekValues(1 To 56) As Double\n Dim dictBOM As Object, dictUnmatched As Object\n Dim materialData As Variant\n Dim key As Variant\n \n Set dictBOM = CreateObject(\"Scripting.Dictionary\")\n Set dictUnmatched = CreateObject(\"Scripting.Dictionary\")\n \n ' Set worksheets\n Set wsFocest = ThisWorkbook.Sheets(\"Focest\")\n Set wsBOM = ThisWorkbook.Sheets(\"BOM\")\n \n ' Create new worksheets for output\n On Error Resume Next\n Application.DisplayAlerts = False\n ThisWorkbook.Sheets(\"Output\").Delete\n ThisWorkbook.Sheets(\"Unmatched\").Delete\n Application.DisplayAlerts = True\n On Error GoTo 0\n \n Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))\n wsOutput.Name = \"Output\"\n Set wsUnmatched = ThisWorkbook.Sheets.Add(After:=wsOutput)\n wsUnmatched.Name = \"Unmatched\"\n \n ' Phase 1: Read BOM data into dictionary\n lastRowB = wsBOM.Cells(wsBOM.Rows.Count, \"A\").End(xlUp).Row\n Dim bomData As Variant\n bomData = wsBOM.Range(\"A2:C\" \u0026 lastRowB).Value\n \n For i = 1 To UBound(bomData, 1)\n keyPart = Trim(bomData(i, 1) \u0026 \"\")\n material = Trim(bomData(i, 2) \u0026 \"\")\n \n ' Safe handling of usage value\n If IsNumeric(bomData(i, 3)) Then\n usage = CDbl(bomData(i, 3))\n Else\n usage = 0\n End If\n \n If keyPart \u003c\u003e \"\" And material \u003c\u003e \"\" Then\n If Not dictBOM.Exists(keyPart) Then\n dictBOM.Add keyPart, New Collection\n End If\n dictBOM(keyPart).Add Array(material, usage)\n End If\n Next i\n \n ' Phase 2: Process Focest data - only process rows with BOM matches\n lastRowF = wsFocest.Cells(wsFocest.Rows.Count, \"A\").End(xlUp).Row\n Dim focestData As Variant\n focestData = wsFocest.Range(\"A2:BJ\" \u0026 lastRowF).Value ' Columns A to BJ (A, B + 56 weeks)\n \n ' Pre-calculate output data size\n Dim outputRowCount As Long\n outputRowCount = 0\n For i = 1 To UBound(focestData, 1)\n keyPart = Trim(focestData(i, 1) \u0026 \"\")\n If keyPart \u003c\u003e \"\" And dictBOM.Exists(keyPart) Then\n outputRowCount = outputRowCount + dictBOM(keyPart).Count\n End If\n Next i\n \n ' Prepare output array\n Dim outputData() As Variant\n ReDim outputData(1 To outputRowCount, 1 To 60) ' 4 fixed columns + 56 week columns\n \n lastRowO = 0\n \n For i = 1 To UBound(focestData, 1)\n keyPart = Trim(focestData(i, 1) \u0026 \"\")\n If keyPart = \"\" Then GoTo NextFocestRow\n \n ' Only process rows with BOM matches\n If dictBOM.Exists(keyPart) Then\n focestCol1 = focestData(i, 1) \u0026 \"\"\n focestCol2 = focestData(i, 2) \u0026 \"\"\n \n ' Safe handling of LIC value\n If IsNumeric(focestData(i, 2)) Then\n lic = CDbl(focestData(i, 2))\n Else\n lic = 0\n End If\n \n ' Extract week values with error handling\n For j = 1 To 56\n If IsNumeric(focestData(i, j + 2)) Then\n weekValues(j) = CDbl(focestData(i, j + 2))\n Else\n weekValues(j) = 0\n End If\n Next j\n \n ' Process matching BOM items\n For k = 1 To dictBOM(keyPart).Count\n materialData = dictBOM(keyPart)(k)\n material = materialData(0)\n usage = materialData(1)\n lastRowO = lastRowO + 1\n \n outputData(lastRowO, 1) = focestCol1\n outputData(lastRowO, 2) = focestCol2\n outputData(lastRowO, 3) = material\n outputData(lastRowO, 4) = usage\n \n For col = 1 To 56\n outputData(lastRowO, col + 4) = weekValues(col) * lic * usage\n Next col\n Next k\n End If\n \nNextFocestRow:\n Next i\n \n ' Phase 3: Separately process Focest rows without BOM matches\n For i = 1 To UBound(focestData, 1)\n keyPart = Trim(focestData(i, 1) \u0026 \"\")\n If keyPart \u003c\u003e \"\" And Not dictBOM.Exists(keyPart) Then\n If Not dictUnmatched.Exists(keyPart) Then\n dictUnmatched.Add keyPart, True\n End If\n End If\n Next i\n \n ' Batch write output data\n If outputRowCount \u003e 0 Then\n wsOutput.Range(\"A2\").Resize(outputRowCount, 60).Value = outputData\n End If\n \n ' Generate unmatched sheet\n wsUnmatched.Cells(1, 1).Value = \"Unmatched Key Part Numbers\"\n \n If dictUnmatched.Count \u003e 0 Then\n Dim unmatchedData() As Variant\n ReDim unmatchedData(1 To dictUnmatched.Count, 1 To 1)\n i = 0\n For Each key In dictUnmatched.Keys\n i = i + 1\n unmatchedData(i, 1) = key\n Next key\n wsUnmatched.Range(\"A2\").Resize(dictUnmatched.Count, 1).Value = unmatchedData\n End If\n \n ' Set headers\n wsOutput.Cells(1, 1).Value = \"Focest Col1\"\n wsOutput.Cells(1, 2).Value = \"Focest Col2\"\n wsOutput.Cells(1, 3).Value = \"Material Number\"\n wsOutput.Cells(1, 4).Value = \"BOM Usage\"\n For col = 1 To 56\n wsOutput.Cells(1, col + 4).Value = \"Week\" \u0026 col\n Next col\n \n ' Auto-fit columns\n wsOutput.Columns.AutoFit\n wsUnmatched.Columns.AutoFit\n \n Application.ScreenUpdating = True\n Application.Calculation = xlCalculationAutomatic\n Application.EnableEvents = True\n \n MsgBox \"Processing completed! \" \u0026 outputRowCount \u0026 \" rows generated in Output sheet. \" \u0026 _\n dictUnmatched.Count \u0026 \" unmatched items found.\"\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]},{"ID":"20250919194910-n425prn","Type":"NodeParagraph","Properties":{"id":"20250919194910-n425prn","style":"flex: 0 0 auto; width: calc(10.001% - 12px);","updated":"20250919194916"}},{"Type":"NodeSuperBlockCloseMarker"}]}]}]},{"ID":"20250919194525-1qzln88","Type":"NodeList","ListData":{},"Properties":{"id":"20250919194525-1qzln88","updated":"20250919194927"},"Children":[{"ID":"20250919194921-wjlk5ti","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194921-wjlk5ti","updated":"20250919194921"},"Children":[{"ID":"20250919194921-fjberiv","Type":"NodeParagraph","Properties":{"id":"20250919194921-fjberiv","updated":"20250919194921"},"Children":[{"Type":"NodeText","Data":"数据透视表v2.0"}]}]},{"ID":"20250919194923-x7tum1o","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919194923-x7tum1o","updated":"20250919194927"},"Children":[{"ID":"20250919194927-hmtamab","Type":"NodeSuperBlock","Properties":{"id":"20250919194927-hmtamab","updated":"20250919194927"},"Children":[{"Type":"NodeSuperBlockOpenMarker"},{"Type":"NodeSuperBlockLayoutMarker","Data":"col"},{"ID":"20250919194525-2hua8f5","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919194525-2hua8f5","style":"flex: 0 0 auto; width: calc(89.999% - 12px);","updated":"20250919194927"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dmJh"},{"Type":"NodeCodeBlockCode","Data":"Sub Q()\n' Define variables\nDim wsSource As Worksheet, wsDest As Worksheet\nDim pvtCache As PivotCache\nDim pvtTable As PivotTable\nDim LastRow As Long\nDim SourceDataRange As Range\nDim i As Long\nDim fieldName As String\nDim startTime As Double\nDim dataField As PivotField\n\n' Start timing execution\nstartTime = Timer\n\n' Disable Excel features for performance optimization\nOptimizeVBA True\n\nOn Error GoTo ErrorHandler\n\n' Check if source worksheet exists\nIf Not WorksheetExists(\"Output\") Then\n MsgBox \"Source worksheet 'Output' not found!\", vbCritical\n GoTo CleanUp\nEnd If\n\n' Set reference to source worksheet\nSet wsSource = ThisWorkbook.Worksheets(\"Output\")\n\n' Delete destination worksheet if it already exists\nIf WorksheetExists(\"Cutput\") Then\n Application.DisplayAlerts = False\n ThisWorkbook.Worksheets(\"Cutput\").Delete\n Application.DisplayAlerts = True\nEnd If\n\n' Remove any existing pivot table with the same name\nRemovePivotTable \"WeeklySummaryPivot\"\n\n' Create new worksheet for the pivot table\nSet wsDest = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))\nwsDest.Name = \"Cutput\"\n\n' Find the last row with data in source worksheet\nLastRow = FindLastRow(wsSource)\n\n' Verify there is sufficient data\nIf LastRow \u003c 2 Then\n MsgBox \"Insufficient data in source worksheet!\", vbExclamation\n GoTo CleanUp\nEnd If\n\n' Define source data range\nSet SourceDataRange = wsSource.Range(\"A1:BH\" \u0026 LastRow)\n\n' Create pivot cache from source data\nSet pvtCache = ThisWorkbook.PivotCaches.Create( _\n SourceType:=xlDatabase, _\n SourceData:=SourceDataRange)\n\nSet pvtTable = pvtCache.CreatePivotTable( _\n TableDestination:=wsDest.Range(\"A3\"), _\n tableName:=\"WeeklySummaryPivot\")\n\n\nWith pvtTable\n .ManualUpdate = True\n .RowAxisLayout xlTabularRow\n \n ' Add row field - Material Number (Column C)\n .AddFields RowFields:=wsSource.Cells(1, 3).Value\n \n ' Add data fields (columns E to BH)\n For i = 5 To 60\n fieldName = wsSource.Cells(1, i).Value\n \n If fieldName \u003c\u003e \"\" Then\n On Error Resume Next\n Set dataField = .PivotFields(fieldName)\n If Err.Number = 0 Then\n With dataField\n .Orientation = xlDataField\n .Function = xlSum\n .NumberFormat = \"#,##0\"\n \n If InStr(.Caption, \"Sum of\") = 0 Then\n .Caption = \"Sum of \" \u0026 fieldName\n End If\n End With\n Else\n \n Err.Clear\n End If\n On Error GoTo ErrorHandler\n End If\n Next i\n \n ' Apply formatting\n .ShowTableStyleRowStripes = True\n .TableStyle2 = \"PivotStyleMedium9\"\n .ManualUpdate = False\nEnd With\n\n' Add title and timestamp\nWith wsDest\n .Range(\"A1\").Value = \"Weekly Summary Report\"\n .Range(\"A1\").Font.Bold = True\n .Range(\"A1\").Font.Size = 14\n \n .Range(\"A2\").Value = \"Generated: \" \u0026 Now()\n .Range(\"A2\").Font.Italic = True\nEnd With\n\n' Auto-fit columns\nIf wsDest.UsedRange.Count \u003e 1 Then\n wsDest.UsedRange.Columns.AutoFit\nEnd If\n\n' Success message\nMsgBox \"Pivot table created successfully in \" \u0026 Format(Timer - startTime, \"0.00\") \u0026 _\n \" seconds! Location: Worksheet \" \u0026 wsDest.Name, vbInformation\nCleanUp:\nOptimizeVBA False\nSet pvtTable = Nothing\nSet pvtCache = Nothing\nSet SourceDataRange = Nothing\nSet wsSource = Nothing\nSet wsDest = Nothing\n\n\nExit Sub\nErrorHandler:\nMsgBox \"Error \" \u0026 Err.Number \u0026 \": \" \u0026 Err.Description \u0026 vbCrLf \u0026 _\n\"Error occurred at line: \" \u0026 Erl, vbCritical\nResume CleanUp\nEnd Sub\n\n' Helper function: Optimize VBA performance\nSub OptimizeVBA(Optimize As Boolean)\nWith Application\nIf Optimize Then\n.ScreenUpdating = False\n.Calculation = xlCalculationManual\n.DisplayAlerts = False\n.EnableEvents = False\n.StatusBar = \"Processing... Please wait\"\nElse\n.ScreenUpdating = True\n.Calculation = xlCalculationAutomatic\n.DisplayAlerts = True\n.EnableEvents = True\n.StatusBar = False\nEnd If\nEnd With\nEnd Sub\n\n' Helper function: Check if worksheet exists\nFunction WorksheetExists(sheetName As String) As Boolean\nOn Error Resume Next\nWorksheetExists = Not ThisWorkbook.Sheets(sheetName) Is Nothing\nOn Error GoTo 0\nEnd Function\n\n' Helper function: Remove pivot table by name\nSub RemovePivotTable(tableName As String)\nDim ws As Worksheet\nDim pt As PivotTable\n\nOn Error Resume Next\n\nFor Each ws In ThisWorkbook.Worksheets\n Set pt = ws.PivotTables(tableName)\n If Not pt Is Nothing Then\n pt.TableRange2.Clear\n Exit For\n End If\nNext ws\n\nOn Error GoTo 0\nEnd Sub\n\n' Helper function: Find last row with data\nFunction FindLastRow(ws As Worksheet) As Long\nOn Error Resume Next\nFindLastRow = ws.Cells(ws.Rows.Count, \"A\").End(xlUp).Row\nIf FindLastRow = 1 And ws.Cells(1, 1).Value = \"\" Then FindLastRow = 0\nIf Err.Number \u003c\u003e 0 Then FindLastRow = 0\nOn Error GoTo 0\nEnd Function\n\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]},{"ID":"20250919194923-pcislkl","Type":"NodeParagraph","Properties":{"id":"20250919194923-pcislkl","style":"flex: 0 0 auto; width: calc(9.998% - 12px);","updated":"20250919194927"}},{"Type":"NodeSuperBlockCloseMarker"}]}]}]},{"ID":"20250919195107-g7n7jkm","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919195107-g7n7jkm","updated":"20250919200547"},"Children":[{"Type":"NodeText","Data":"二、工单缺料表处理"}]},{"ID":"20250919200040-czzg9ii","Type":"NodeList","ListData":{},"Properties":{"id":"20250919200040-czzg9ii","updated":"20250919200143"},"Children":[{"ID":"20250919200047-bpxptns","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200047-bpxptns","updated":"20250919200047"},"Children":[{"ID":"20250919200047-2605ucf","Type":"NodeParagraph","Properties":{"id":"20250919200047-2605ucf","updated":"20250919200112"},"Children":[{"Type":"NodeText","Data":"0、从1-4依次运行"}]}]},{"ID":"20250919200117-i6leyl6","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200117-i6leyl6","updated":"20250919200143"},"Children":[{"ID":"20250919200117-h6puzvb","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919200117-h6puzvb","updated":"20250919200143"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub AA()\n Call QLCL\n Call QLCXJ\n Call QLB\n Call QLBC\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919195052-4p09ir6","Type":"NodeList","ListData":{},"Properties":{"id":"20250919195052-4p09ir6","updated":"20250919195437"},"Children":[{"ID":"20250919195055-mcz7l18","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195055-mcz7l18","updated":"20250919195430"},"Children":[{"ID":"20250919195055-pjihzme","Type":"NodeParagraph","Properties":{"id":"20250919195055-pjihzme","updated":"20250919195430"},"Children":[{"Type":"NodeText","Data":"1.去除多余行和列"}]}]},{"ID":"20250919195406-tw257sw","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195406-tw257sw","updated":"20250919195437"},"Children":[{"ID":"20250919195406-y0rohij","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919195406-y0rohij","updated":"20250919195437"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub QLCL()\n Dim ws As Worksheet\n \n On Error GoTo ErrorHandler\n \n Set ws = ThisWorkbook.Worksheets(\"\")\n \n With ws\n .Rows(5).Delete\n .Rows(3).Delete\n .Rows(2).Delete\n .Rows(1).Delete\n .Columns(2).Delete\n .Columns(1).Delete\n \n End With\n \n MsgBox \"成功\"\n Exit Sub\n \nErrorHandler:\n MsgBox \"失败\"\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919195154-9z61nb5","Type":"NodeList","ListData":{},"Properties":{"id":"20250919195154-9z61nb5","updated":"20250919195601"},"Children":[{"ID":"20250919195440-471550i","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195440-471550i","updated":"20250919195440"},"Children":[{"ID":"20250919195440-wtw2852","Type":"NodeParagraph","Properties":{"id":"20250919195440-wtw2852","updated":"20250919195546"},"Children":[{"Type":"NodeText","Data":"2.去除带小计的行"}]}]},{"ID":"20250919195552-fes0ngv","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195552-fes0ngv","updated":"20250919195601"},"Children":[{"ID":"20250919195552-kpn5pgd","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919195552-kpn5pgd","updated":"20250919195601"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub QLCXJ()\n ' Declare variables\n Dim ws As Worksheet\n Dim rng As Range\n Dim cell As Range\n Dim lastRow As Long\n Dim i As Long\n Dim deleteCount As Integer\n Dim colToCheck As Integer ' Column number to check (1 for column A, 2 for column B, etc.)\n\n On Error GoTo ErrorHandler\n Application.ScreenUpdating = False ' Turn off screen updating for faster execution\n Application.Calculation = xlCalculationManual ' Set calculation to manual mode\n\n ' Set the worksheet to operate on (default is active sheet)\n Set ws = ActiveSheet\n \n ' Set the column to check (Column A = 1)\n colToCheck = 1\n \n ' Find the last row in the worksheet\n lastRow = ws.Cells(ws.Rows.Count, colToCheck).End(xlUp).Row\n \n deleteCount = 0 ' Initialize counter\n \n ' Loop from last row to first row (to avoid affecting loop when deleting rows)\n For i = lastRow To 1 Step -1\n ' Check if cell contains \"璸\"\n If InStr(1, ws.Cells(i, colToCheck).Value, \"璸\") \u003e 0 Then\n ' Delete entire row\n ws.Rows(i).Delete\n deleteCount = deleteCount + 1\n End If\n Next i\n\n Application.ScreenUpdating = True ' Restore screen updating\n Application.Calculation = xlCalculationAutomatic ' Restore automatic calculation\n \n ' Display processing result\n MsgBox \"Process completed! Deleted \" \u0026 deleteCount \u0026 \" rows containing '璸'.\", vbInformation, \"Completed\"\n \n Exit Sub\n\nErrorHandler:\n Application.ScreenUpdating = True\n Application.Calculation = xlCalculationAutomatic\n MsgBox \"Error occurred: \" \u0026 Err.Description, vbCritical, \"Error\"\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919195604-vlcqpos","Type":"NodeList","ListData":{},"Properties":{"id":"20250919195604-vlcqpos","updated":"20250919195751"},"Children":[{"ID":"20250919195606-wcre8dw","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195606-wcre8dw","updated":"20250919195740"},"Children":[{"ID":"20250919195606-tf9fl9e","Type":"NodeParagraph","Properties":{"id":"20250919195606-tf9fl9e","updated":"20250919195740"},"Children":[{"Type":"NodeText","Data":"3.复制一份并将新复制的表命名成QL"}]}]},{"ID":"20250919195659-a24hlqz","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195659-a24hlqz","updated":"20250919195751"},"Children":[{"ID":"20250919195659-3rk3r1r","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919195659-3rk3r1r","updated":"20250919195751"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub QLB()\n ' Simple and straightforward backup procedure\n On Error GoTo ErrorHandler\n \n ' Set parameters\n Dim sheetName As String\n sheetName = \"缺料表\" ' Please change to your actual worksheet name\n \n ' Step 1: Create backup\n Application.DisplayAlerts = False\n On Error Resume Next\n Sheets(\"QL\").Delete ' Delete old backup\n On Error GoTo ErrorHandler\n \n Sheets(sheetName).Copy After:=Sheets(Sheets.Count)\n ActiveSheet.Name = \"QL\" ' Create new backup\n Sheets(sheetName).Select ' Return to original worksheet\n \n Application.DisplayAlerts = True\n \n MsgBox \"Backup completed! Backup saved as QL worksheet\"\n Exit Sub\n \nErrorHandler:\n MsgBox \"Error occurred! Please check: 1. Worksheet name is correct 2. Worksheet is not protected\"\n Application.DisplayAlerts = True\nEnd Sub\n\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919195753-tgk1xzl","Type":"NodeList","ListData":{},"Properties":{"id":"20250919195753-tgk1xzl","updated":"20250919200032"},"Children":[{"ID":"20250919195803-223b7qg","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195803-223b7qg","updated":"20250919195803"},"Children":[{"ID":"20250919195803-nm7haud","Type":"NodeParagraph","Properties":{"id":"20250919195803-nm7haud","updated":"20250919195922"},"Children":[{"Type":"NodeText","Data":"4,使用QL表的工单列去重，筛选出现有工单"}]}]},{"ID":"20250919195923-3d0t4yu","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919195923-3d0t4yu","updated":"20250919200032"},"Children":[{"ID":"20250919195923-5iyfs24","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919195923-5iyfs24","updated":"20250919200032"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub QLBC()\n ' Complete data cleaning and deduplication operations\n On Error GoTo ErrorHandler\n \n ' Ensure operating on the correct worksheet (QL sheet only)\n Dim originalSheet As Worksheet\n Set originalSheet = ThisWorkbook.Sheets(\"QL\")\n originalSheet.Activate\n \n ' Step 2: Clean spaces in columns A-C\n Dim lastRow As Long\n lastRow = Cells(Rows.Count, 1).End(xlUp).Row\n \n If lastRow \u003c 2 Then\n MsgBox \"Insufficient data, no processing needed\"\n Exit Sub\n End If\n \n Application.ScreenUpdating = False\n \n ' Clean spaces in column A (Work Order Number)\n For i = 2 To lastRow\n If Not IsEmpty(Cells(i, 1)) And VarType(Cells(i, 1).Value) = vbString Then\n Cells(i, 1).Value = Trim(Cells(i, 1).Value)\n End If\n Next i\n \n ' Clean spaces in column B (Material Number)\n For i = 2 To lastRow\n If Not IsEmpty(Cells(i, 2)) And VarType(Cells(i, 2).Value) = vbString Then\n Cells(i, 2).Value = Trim(Cells(i, 2).Value)\n End If\n Next i\n \n ' Clean spaces in column C (Column used for deduplication)\n For i = 2 To lastRow\n If Not IsEmpty(Cells(i, 3)) And VarType(Cells(i, 3).Value) = vbString Then\n Cells(i, 3).Value = Trim(Cells(i, 3).Value)\n End If\n Next i\n \n ' Step 3: Deduplicate based on column C\n Dim OriginalCount As Long\n OriginalCount = lastRow - 1 ' Exclude header row\n \n Dim DataRange As Range\n Set DataRange = Range(\"A1:C\" \u0026 lastRow)\n DataRange.RemoveDuplicates Columns:=3, Header:=xlYes\n \n ' Calculate processing results\n Dim NewLastRow As Long\n NewLastRow = Cells(Rows.Count, 3).End(xlUp).Row\n Dim NewCount As Long\n NewCount = NewLastRow - 1 ' Exclude header row\n Dim RemovedCount As Long\n RemovedCount = OriginalCount - NewCount\n \n Application.ScreenUpdating = True\n \n ' Display processing results\n MsgBox \"Data processing completed!\" \u0026 vbCrLf \u0026 vbCrLf \u0026 _\n \"Original data rows: \" \u0026 OriginalCount \u0026 vbCrLf \u0026 _\n \"Duplicate rows removed: \" \u0026 RemovedCount \u0026 vbCrLf \u0026 _\n \"Remaining unique rows: \" \u0026 NewCount, _\n vbInformation, \"Processing Results\"\n \n ' Select the processed data range\n If NewLastRow \u003e 1 Then\n Range(\"A1:C\" \u0026 NewLastRow).Select\n End If\n \n Exit Sub\n \nErrorHandler:\n Application.ScreenUpdating = True\n MsgBox \"Error occurred during processing: \" \u0026 Err.Description \u0026 vbCrLf \u0026 _\n \"Error code: \" \u0026 Err.Number, vbCritical, \"Error\"\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919200235-qn2ax8m","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919200235-qn2ax8m","updated":"20250919200547"},"Children":[{"Type":"NodeText","Data":"三、需求预测，统计变化归率"}]},{"ID":"20250919200319-8xajm31","Type":"NodeList","ListData":{},"Properties":{"id":"20250919200319-8xajm31","updated":"20250919200539"},"Children":[{"ID":"20250919200321-dkb414w","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200321-dkb414w","updated":"20250919200539"},"Children":[{"ID":"20250919200321-hk12ozq","Type":"NodeParagraph","Properties":{"id":"20250919200321-hk12ozq","updated":"20250919200539"},"Children":[{"Type":"NodeText","Data":"将每次Forcst加时间，和客户，记录，重新生成Focest_change表"}]}]},{"ID":"20250919200427-rwv5s8w","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200427-rwv5s8w","updated":"20250919200447"},"Children":[{"ID":"20250919200310-mqvlsgg","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919200310-mqvlsgg","updated":"20250919200447"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub Fchange()\n ' Define worksheet variables\n Dim wsFocest As Worksheet\n Dim wsChange As Worksheet\n Dim wsKHNA As Worksheet\n \n ' Define other variables\n Dim lastRowFocest As Long\n Dim lastRowChange As Long\n Dim lastRowKHNA As Long\n Dim i As Long, j As Long\n Dim modelToFind As String\n Dim vendorFound As Boolean\n \n ' Disable screen updating and automatic calculation for better performance\n Application.ScreenUpdating = False\n Application.Calculation = xlCalculationManual\n \n On Error GoTo ErrorHandler ' Error handling\n \n ' Set worksheets\n Set wsFocest = ThisWorkbook.Worksheets(\"Focest\")\n Set wsChange = ThisWorkbook.Worksheets(\"Focest_change\")\n Set wsKHNA = ThisWorkbook.Worksheets(\"KHNA\")\n \n ' Get last row of Focest table\n lastRowFocest = wsFocest.Cells(wsFocest.Rows.Count, 1).End(xlUp).Row\n \n ' Get last row of Focest_change table\n lastRowChange = wsChange.Cells(wsChange.Rows.Count, 1).End(xlUp).Row\n If lastRowChange = 1 And wsChange.Cells(1, 1).Value = \"\" Then\n lastRowChange = 0 ' If table is empty, start from row 1\n Else\n lastRowChange = lastRowChange + 1 ' Start adding new data from next row\n End If\n \n ' Get last row of KHNA table\n lastRowKHNA = wsKHNA.Cells(wsKHNA.Rows.Count, 1).End(xlUp).Row\n \n ' Copy headers (if Focest_change table is empty)\n If lastRowChange = 0 Then\n wsFocest.Range(\"A1:BH1\").Copy Destination:=wsChange.Range(\"A1\")\n ' Add new headers\n wsChange.Cells(1, 59).Value = \"Run Time\"\n wsChange.Cells(1, 60).Value = \"Vendor\"\n lastRowChange = 2 ' Set data to start from row 2\n Else\n lastRowChange = lastRowChange ' Continue adding from next row\n End If\n \n ' Copy data and add timestamp and vendor information\n For i = 2 To lastRowFocest ' Start from row 2 (assuming row 1 is header)\n ' Copy data from MODEL to WEEK56 (columns A to BH, total 58 columns)\n wsFocest.Range(\"A\" \u0026 i \u0026 \":BH\" \u0026 i).Copy Destination:=wsChange.Range(\"A\" \u0026 lastRowChange)\n \n ' Add run time (column BI, which is column 59)\n wsChange.Cells(lastRowChange, 59).Value = Now\n \n ' Find vendor information\n modelToFind = wsFocest.Cells(i, 1).Value ' MODEL is in column A\n vendorFound = False\n \n ' Find matching MODEL in KHNA table\n For j = 2 To lastRowKHNA ' Assuming row 1 is header in KHNA table\n If wsKHNA.Cells(j, 1).Value = modelToFind Then ' Assuming MODEL is in column A of KHNA table\n wsChange.Cells(lastRowChange, 60).Value = wsKHNA.Cells(j, 2).Value ' Assuming Vendor is in column B of KHNA table\n vendorFound = True\n Exit For\n End If\n Next j\n \n ' If vendor not found, mark as \"Not Found\"\n If Not vendorFound Then\n wsChange.Cells(lastRowChange, 60).Value = \"Not Found\"\n End If\n \n lastRowChange = lastRowChange + 1\n Next i\n \n ' Auto-fit columns\n wsChange.Columns.AutoFit\n \n MsgBox \"Data successfully copied to Focest_change table. \" \u0026 (lastRowFocest - 1) \u0026 \" rows added.\", vbInformation\n \nCleanUp:\n ' Restore settings\n Application.ScreenUpdating = True\n Application.Calculation = xlCalculationAutomatic\n Exit Sub\n \nErrorHandler:\n MsgBox \"Error occurred: \" \u0026 Err.Description, vbCritical\n Resume CleanUp\nEnd Sub\n\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919200542-a5rmxq3","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919200542-a5rmxq3","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"四、将BOM转置"}]},{"ID":"20250919200728-5uuhl1g","Type":"NodeList","ListData":{},"Properties":{"id":"20250919200728-5uuhl1g","updated":"20250919200804"},"Children":[{"ID":"20250919200731-yjr6mq5","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200731-yjr6mq5","updated":"20250919200731"},"Children":[{"ID":"20250919200731-t8ltmpv","Type":"NodeParagraph","Properties":{"id":"20250919200731-t8ltmpv","updated":"20250919200749"},"Children":[{"Type":"NodeText","Data":"通过料号推可用机种"}]}]},{"ID":"20250919200749-hav3y8a","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919200749-hav3y8a","updated":"20250919200804"},"Children":[{"ID":"20250919200749-7e1842i","Type":"NodeCodeBlock","IsFencedCodeBlock":true,"Properties":{"id":"20250919200749-7e1842i","updated":"20250919200804"},"Children":[{"Type":"NodeCodeBlockFenceOpenMarker","Data":"```"},{"Type":"NodeCodeBlockFenceInfoMarker","CodeBlockInfo":"dW5kZWZpbmVk"},{"Type":"NodeCodeBlockCode","Data":"Sub TransformBOM_ShowAll()\n Dim ws As Worksheet\n Dim dict As Object, partDict As Object\n Dim arrData As Variant, arrOutput() As Variant\n Dim i As Long, j As Long, lastRow As Long\n Dim outputRow As Long, maxParts As Long\n Dim material As String, mainPart As String\n Dim key As Variant, partKey As Variant\n Dim t As Double\n \n t = Timer ' Start timing\n \n Application.ScreenUpdating = False\n Application.Calculation = xlCalculationManual\n Application.EnableEvents = False\n \n On Error GoTo CleanUp\n \n ' Set worksheet - always use \"BOM\" worksheet\n On Error Resume Next\n Set ws = ThisWorkbook.Worksheets(\"BOM\")\n If ws Is Nothing Then\n MsgBox \"Error: Worksheet named 'BOM' not found!\", vbCritical, \"Error\"\n GoTo CleanUp\n End If\n On Error GoTo 0\n \n ' Find the last row of data in column A\n lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row\n If lastRow \u003c 2 Then\n MsgBox \"No data found in BOM worksheet!\", vbExclamation, \"Warning\"\n GoTo CleanUp\n End If\n \n ' Read all data into array at once\n arrData = ws.Range(\"A2:C\" \u0026 lastRow).Value\n \n ' Create dictionary to organize data by material\n Set dict = CreateObject(\"Scripting.Dictionary\")\n dict.CompareMode = vbTextCompare\n \n ' First pass: Collect all unique main parts for each material\n For i = 1 To UBound(arrData, 1)\n If Not IsEmpty(arrData(i, 1)) And Not IsEmpty(arrData(i, 2)) Then\n mainPart = CStr(arrData(i, 1))\n material = CStr(arrData(i, 2))\n \n If Not dict.Exists(material) Then\n Set partDict = CreateObject(\"Scripting.Dictionary\")\n dict.Add material, partDict\n Else\n Set partDict = dict(material)\n End If\n \n ' Add main part and usage (keep original values)\n If partDict.Exists(mainPart) Then\n partDict(mainPart) = partDict(mainPart) + CDbl(arrData(i, 3))\n Else\n partDict.Add mainPart, CDbl(arrData(i, 3))\n End If\n End If\n Next i\n \n ' Find maximum number of main parts for any material\n maxParts = 0\n For Each key In dict.Keys\n If dict(key).Count \u003e maxParts Then maxParts = dict(key).Count\n Next key\n \n ' Prepare output array\n ReDim arrOutput(1 To dict.Count + 1, 1 To maxParts * 2 + 1)\n \n ' Set headers\n arrOutput(1, 1) = \"Material Number\"\n For j = 1 To maxParts\n arrOutput(1, j * 2) = \"Main Part \" \u0026 j\n arrOutput(1, j * 2 + 1) = \"Usage \" \u0026 j\n Next j\n \n ' Fill data into array\n outputRow = 2\n For Each key In dict.Keys\n arrOutput(outputRow, 1) = key\n \n j = 1\n For Each partKey In dict(key).Keys\n If j * 2 + 1 \u003e UBound(arrOutput, 2) Then Exit For\n arrOutput(outputRow, j * 2) = partKey\n arrOutput(outputRow, j * 2 + 1) = dict(key)(partKey)\n j = j + 1\n Next partKey\n \n outputRow = outputRow + 1\n Next key\n \n ' Create or clear output worksheet\n Dim outputWs As Worksheet\n On Error Resume Next\n Set outputWs = ThisWorkbook.Worksheets(\"MaterialSubstitution\")\n If outputWs Is Nothing Then\n Set outputWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))\n outputWs.Name = \"MaterialSubstitution\"\n Else\n outputWs.Cells.Clear\n End If\n On Error GoTo 0\n \n ' Write all data to worksheet at once\n outputWs.Range(\"A1\").Resize(UBound(arrOutput, 1), UBound(arrOutput, 2)).Value = arrOutput\n \n ' Auto-fit columns\n outputWs.Columns.AutoFit\n \n ' Activate the output worksheet for user visibility\n outputWs.Activate\n \nCleanUp:\n ' Clean up objects\n Set partDict = Nothing\n \n ' Restore application settings\n Application.ScreenUpdating = True\n Application.Calculation = xlCalculationAutomatic\n Application.EnableEvents = True\n \n If dict.Count \u003e 0 Then\n MsgBox \"BOM transformation completed! Processed \" \u0026 (lastRow - 1) \u0026 \" rows of data, \" \u0026 _\n \"generated \" \u0026 dict.Count \u0026 \" material numbers.\" \u0026 vbCrLf \u0026 _\n \"Time taken: \" \u0026 Format(Timer - t, \"0.00\") \u0026 \" seconds\", _\n vbInformation, \"Completed\"\n End If\n \n Set dict = Nothing\nEnd Sub\n"},{"Type":"NodeCodeBlockFenceCloseMarker","Data":"```"}]}]}]},{"ID":"20250919201437-lgolgc6","Type":"NodeHeading","HeadingLevel":1,"Properties":{"id":"20250919201437-lgolgc6","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"VBA 代码集介绍"}]},{"ID":"20250919201437-6k3utcg","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-6k3utcg","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"概述"}]},{"ID":"20250919201437-2jl7381","Type":"NodeParagraph","Properties":{"id":"20250919201437-2jl7381","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"本代码集包含多个VBA宏，用于处理Excel中的物料清单(BOM)、预测数据、工单缺料表等生产管理相关数据。主要功能包括数据匹配、转换、分析和报表生成。"}]},{"ID":"20250919201437-p59abmf","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-p59abmf","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"目录"}]},{"ID":"20250919201437-1uabxzn","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-1uabxzn","updated":"20250919201437"},"Children":[{"ID":"20250919201437-kho4yu1","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-kho4yu1","updated":"20250919201437"},"Children":[{"ID":"20250919201437-cbaoini","Type":"NodeParagraph","Properties":{"id":"20250919201437-cbaoini","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"a","TextMarkAHref":"#数据库主逻辑并生成bom无匹配表-v20","TextMarkTextContent":"数据库主逻辑并生成BOM无匹配表 V2.0"}]}]},{"ID":"20250919201437-p0xdcno","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-p0xdcno","updated":"20250919201437"},"Children":[{"ID":"20250919201437-td8rrlg","Type":"NodeParagraph","Properties":{"id":"20250919201437-td8rrlg","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"a","TextMarkAHref":"#数据透视表v20","TextMarkTextContent":"数据透视表v2.0"}]}]},{"ID":"20250919201437-jlutali","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20250919201437-jlutali","updated":"20250919201437"},"Children":[{"ID":"20250919201437-cgo1wy9","Type":"NodeParagraph","Properties":{"id":"20250919201437-cgo1wy9","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"a","TextMarkAHref":"#工单缺料表处理","TextMarkTextContent":"工单缺料表处理"}]}]},{"ID":"20250919201437-ep5a1kt","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NC4=","Num":4},"Properties":{"id":"20250919201437-ep5a1kt","updated":"20250919201437"},"Children":[{"ID":"20250919201437-rgenwse","Type":"NodeParagraph","Properties":{"id":"20250919201437-rgenwse","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"a","TextMarkAHref":"#需求预测统计变化归率","TextMarkTextContent":"需求预测统计变化归率"}]}]},{"ID":"20250919201437-sj9pm7p","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NS4=","Num":5},"Properties":{"id":"20250919201437-sj9pm7p","updated":"20250919201437"},"Children":[{"ID":"20250919201437-zxgx2js","Type":"NodeParagraph","Properties":{"id":"20250919201437-zxgx2js","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"a","TextMarkAHref":"#bom转置功能","TextMarkTextContent":"BOM转置功能"}]}]}]},{"ID":"20250919201437-n68flzp","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-n68flzp","updated":"20250919201437"}},{"ID":"20250919201437-arw8ro3","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-arw8ro3","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"数据库主逻辑并生成BOM无匹配表 V2.0"}]},{"ID":"20250919201437-pc5ed9a","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-pc5ed9a","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"功能描述"}]},{"ID":"20250919201437-z5lh5gd","Type":"NodeParagraph","Properties":{"id":"20250919201437-z5lh5gd","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"此宏处理Focest表和BOM表数据，生成匹配结果和无匹配项报告。"}]},{"ID":"20250919201437-wglm7a8","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-wglm7a8","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"主要步骤"}]},{"ID":"20250919201437-rgm2plm","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-rgm2plm","updated":"20250919201437"},"Children":[{"ID":"20250919201437-v0d06eb","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-v0d06eb","updated":"20250919201437"},"Children":[{"ID":"20250919201437-cw34nqd","Type":"NodeParagraph","Properties":{"id":"20250919201437-cw34nqd","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"初始化设置"},{"Type":"NodeText","Data":"：禁用屏幕更新、自动计算和事件处理以提高性能"}]}]},{"ID":"20250919201437-xquyxh3","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-xquyxh3","updated":"20250919201437"},"Children":[{"ID":"20250919201437-9y368l3","Type":"NodeParagraph","Properties":{"id":"20250919201437-9y368l3","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"工作表设置"},{"Type":"NodeText","Data":"：创建Output和Unmatched工作表"}]}]},{"ID":"20250919201437-pomdbij","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20250919201437-pomdbij","updated":"20250919201437"},"Children":[{"ID":"20250919201437-mqll8gg","Type":"NodeParagraph","Properties":{"id":"20250919201437-mqll8gg","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"BOM数据处理"},{"Type":"NodeText","Data":"：将BOM数据读取到字典中以便快速查找"}]}]},{"ID":"20250919201437-w4x6dts","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NC4=","Num":4},"Properties":{"id":"20250919201437-w4x6dts","updated":"20250919201437"},"Children":[{"ID":"20250919201437-oaxzdvt","Type":"NodeParagraph","Properties":{"id":"20250919201437-oaxzdvt","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"Focest数据处理"},{"Type":"NodeText","Data":"："}]},{"ID":"20250919201437-x3ecx3o","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-x3ecx3o","updated":"20250919201437"},"Children":[{"ID":"20250919201437-l6vn8xz","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-l6vn8xz","updated":"20250919201437"},"Children":[{"ID":"20250919201437-k3ax7bt","Type":"NodeParagraph","Properties":{"id":"20250919201437-k3ax7bt","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"仅处理有BOM匹配的行"}]}]},{"ID":"20250919201437-aqazsu8","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-aqazsu8","updated":"20250919201437"},"Children":[{"ID":"20250919201437-ba2ke57","Type":"NodeParagraph","Properties":{"id":"20250919201437-ba2ke57","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"计算周需求量（周值 × LIC × 使用量）"}]}]}]}]},{"ID":"20250919201437-a74ston","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NS4=","Num":5},"Properties":{"id":"20250919201437-a74ston","updated":"20250919201437"},"Children":[{"ID":"20250919201437-kj3mopn","Type":"NodeParagraph","Properties":{"id":"20250919201437-kj3mopn","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"无匹配项处理"},{"Type":"NodeText","Data":"：收集并记录无BOM匹配的Focest项"}]}]},{"ID":"20250919201437-547bhap","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Ni4=","Num":6},"Properties":{"id":"20250919201437-547bhap","updated":"20250919201437"},"Children":[{"ID":"20250919201437-y79jl74","Type":"NodeParagraph","Properties":{"id":"20250919201437-y79jl74","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"结果输出"},{"Type":"NodeText","Data":"：将处理结果写入Output表，无匹配项写入Unmatched表"}]}]}]},{"ID":"20250919201437-qmjlyqh","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-qmjlyqh","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"技术特点"}]},{"ID":"20250919201437-qx81j7t","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-qx81j7t","updated":"20250919201437"},"Children":[{"ID":"20250919201437-mlwnyz1","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-mlwnyz1","updated":"20250919201437"},"Children":[{"ID":"20250919201437-r4e1cjr","Type":"NodeParagraph","Properties":{"id":"20250919201437-r4e1cjr","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"使用字典对象实现快速数据查找"}]}]},{"ID":"20250919201437-al0ds3f","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-al0ds3f","updated":"20250919201437"},"Children":[{"ID":"20250919201437-cuz48re","Type":"NodeParagraph","Properties":{"id":"20250919201437-cuz48re","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"批量数据操作提高处理效率"}]}]},{"ID":"20250919201437-0ob2fdq","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-0ob2fdq","updated":"20250919201437"},"Children":[{"ID":"20250919201437-eghgzyo","Type":"NodeParagraph","Properties":{"id":"20250919201437-eghgzyo","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"完善的错误处理机制"}]}]}]},{"ID":"20250919201437-xdskadd","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-xdskadd","updated":"20250919201437"}},{"ID":"20250919201437-y5rgdsm","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-y5rgdsm","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"数据透视表v2.0"}]},{"ID":"20250919201437-bqx27g3","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-bqx27g3","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"功能描述"}]},{"ID":"20250919201437-wqqvctw","Type":"NodeParagraph","Properties":{"id":"20250919201437-wqqvctw","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"基于Output工作表创建数据透视表，按物料号汇总周需求数据。"}]},{"ID":"20250919201437-ya1p3xg","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-ya1p3xg","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"主要步骤"}]},{"ID":"20250919201437-n20vegc","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-n20vegc","updated":"20250919201437"},"Children":[{"ID":"20250919201437-iatds23","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-iatds23","updated":"20250919201437"},"Children":[{"ID":"20250919201437-uw3de4y","Type":"NodeParagraph","Properties":{"id":"20250919201437-uw3de4y","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"性能优化"},{"Type":"NodeText","Data":"：禁用Excel功能以提高处理速度"}]}]},{"ID":"20250919201437-5u3zswi","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-5u3zswi","updated":"20250919201437"},"Children":[{"ID":"20250919201437-kyrzzod","Type":"NodeParagraph","Properties":{"id":"20250919201437-kyrzzod","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"准备工作表"},{"Type":"NodeText","Data":"：删除现有Cutput表（如果存在）"}]}]},{"ID":"20250919201437-fgkhmw2","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20250919201437-fgkhmw2","updated":"20250919201437"},"Children":[{"ID":"20250919201437-gdaeqf0","Type":"NodeParagraph","Properties":{"id":"20250919201437-gdaeqf0","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"创建数据透视表"},{"Type":"NodeText","Data":"："}]},{"ID":"20250919201437-cj7e13l","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-cj7e13l","updated":"20250919201437"},"Children":[{"ID":"20250919201437-3bmx40q","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-3bmx40q","updated":"20250919201437"},"Children":[{"ID":"20250919201437-otpxqmx","Type":"NodeParagraph","Properties":{"id":"20250919201437-otpxqmx","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"以物料号为行字段"}]}]},{"ID":"20250919201437-ooh8ow3","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-ooh8ow3","updated":"20250919201437"},"Children":[{"ID":"20250919201437-umvx0nf","Type":"NodeParagraph","Properties":{"id":"20250919201437-umvx0nf","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"汇总56周的需求数据"}]}]}]}]},{"ID":"20250919201437-9hjvvra","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NC4=","Num":4},"Properties":{"id":"20250919201437-9hjvvra","updated":"20250919201437"},"Children":[{"ID":"20250919201437-wk4x4bg","Type":"NodeParagraph","Properties":{"id":"20250919201437-wk4x4bg","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"格式设置"},{"Type":"NodeText","Data":"：应用样式、添加标题和时间戳"}]}]},{"ID":"20250919201437-64i4rar","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NS4=","Num":5},"Properties":{"id":"20250919201437-64i4rar","updated":"20250919201437"},"Children":[{"ID":"20250919201437-ld9kran","Type":"NodeParagraph","Properties":{"id":"20250919201437-ld9kran","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"自动调整"},{"Type":"NodeText","Data":"：自动调整列宽以适应内容"}]}]}]},{"ID":"20250919201437-6g8aqto","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-6g8aqto","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"辅助功能"}]},{"ID":"20250919201437-k0kej04","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-k0kej04","updated":"20250919201437"},"Children":[{"ID":"20250919201437-784kav4","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-784kav4","updated":"20250919201437"},"Children":[{"ID":"20250919201437-m1azwuj","Type":"NodeParagraph","Properties":{"id":"20250919201437-m1azwuj","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"工作表存在性检查"}]}]},{"ID":"20250919201437-72xyn1x","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-72xyn1x","updated":"20250919201437"},"Children":[{"ID":"20250919201437-gbv4eps","Type":"NodeParagraph","Properties":{"id":"20250919201437-gbv4eps","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"数据透视表清理"}]}]},{"ID":"20250919201437-5vljc3x","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-5vljc3x","updated":"20250919201437"},"Children":[{"ID":"20250919201437-unwyxgk","Type":"NodeParagraph","Properties":{"id":"20250919201437-unwyxgk","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"最后行查找"}]}]},{"ID":"20250919201437-4tcwji5","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-4tcwji5","updated":"20250919201437"},"Children":[{"ID":"20250919201437-ab178bq","Type":"NodeParagraph","Properties":{"id":"20250919201437-ab178bq","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"执行时间统计"}]}]}]},{"ID":"20250919201437-m0y1fjm","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-m0y1fjm","updated":"20250919201437"}},{"ID":"20250919201437-szf3o43","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-szf3o43","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"工单缺料表处理"}]},{"ID":"20250919201437-njyjg9s","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-njyjg9s","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"功能描述"}]},{"ID":"20250919201437-vuud5n9","Type":"NodeParagraph","Properties":{"id":"20250919201437-vuud5n9","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"包含四个子程序，用于处理工单缺料表数据。"}]},{"ID":"20250919201437-1grn28a","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20250919201437-1grn28a","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"1. 去除多余行和列(QLCL)"}]},{"ID":"20250919201437-8fui8x6","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-8fui8x6","updated":"20250919201437"},"Children":[{"ID":"20250919201437-52i8mw4","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-52i8mw4","updated":"20250919201437"},"Children":[{"ID":"20250919201437-gi14zs1","Type":"NodeParagraph","Properties":{"id":"20250919201437-gi14zs1","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"删除前4行和前2列"}]}]},{"ID":"20250919201437-3p9n31s","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-3p9n31s","updated":"20250919201437"},"Children":[{"ID":"20250919201437-sjn8qrj","Type":"NodeParagraph","Properties":{"id":"20250919201437-sjn8qrj","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"简化表格结构"}]}]}]},{"ID":"20250919201437-yi885ow","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20250919201437-yi885ow","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"2. 去除带小计的行(QLCXJ)"}]},{"ID":"20250919201437-otnbuoo","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-otnbuoo","updated":"20250919201437"},"Children":[{"ID":"20250919201437-7ujufen","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-7ujufen","updated":"20250919201437"},"Children":[{"ID":"20250919201437-bbeue97","Type":"NodeParagraph","Properties":{"id":"20250919201437-bbeue97","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"检查指定列中的\"小计\"文本"}]}]},{"ID":"20250919201437-hyps07f","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-hyps07f","updated":"20250919201437"},"Children":[{"ID":"20250919201437-b2mucr4","Type":"NodeParagraph","Properties":{"id":"20250919201437-b2mucr4","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"从下往上删除包含小计的行"}]}]},{"ID":"20250919201437-hm1d7s1","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-hm1d7s1","updated":"20250919201437"},"Children":[{"ID":"20250919201437-q3mqy85","Type":"NodeParagraph","Properties":{"id":"20250919201437-q3mqy85","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"统计并报告删除的行数"}]}]}]},{"ID":"20250919201437-31b01th","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20250919201437-31b01th","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"3. 创建备份表(QLB)"}]},{"ID":"20250919201437-nd4yoex","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-nd4yoex","updated":"20250919201437"},"Children":[{"ID":"20250919201437-1sf3wwb","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-1sf3wwb","updated":"20250919201437"},"Children":[{"ID":"20250919201437-cnhbxs9","Type":"NodeParagraph","Properties":{"id":"20250919201437-cnhbxs9","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"创建缺料表的备份副本"}]}]},{"ID":"20250919201437-ld31k2l","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-ld31k2l","updated":"20250919201437"},"Children":[{"ID":"20250919201437-3bvcsqy","Type":"NodeParagraph","Properties":{"id":"20250919201437-3bvcsqy","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"命名为\"QL\"工作表"}]}]}]},{"ID":"20250919201437-hd7qsab","Type":"NodeHeading","HeadingLevel":4,"Properties":{"id":"20250919201437-hd7qsab","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"4. 工单去重处理(QLBC)"}]},{"ID":"20250919201437-63fcudi","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-63fcudi","updated":"20250919201437"},"Children":[{"ID":"20250919201437-2cl0j87","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-2cl0j87","updated":"20250919201437"},"Children":[{"ID":"20250919201437-rdxl0d5","Type":"NodeParagraph","Properties":{"id":"20250919201437-rdxl0d5","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"清理A-C列的空格"}]}]},{"ID":"20250919201437-eukier0","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-eukier0","updated":"20250919201437"},"Children":[{"ID":"20250919201437-30vgxzc","Type":"NodeParagraph","Properties":{"id":"20250919201437-30vgxzc","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"基于C列去除重复项"}]}]},{"ID":"20250919201437-sou1u37","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-sou1u37","updated":"20250919201437"},"Children":[{"ID":"20250919201437-zsfzheq","Type":"NodeParagraph","Properties":{"id":"20250919201437-zsfzheq","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"显示处理前后的行数统计"}]}]}]},{"ID":"20250919201437-tzb1jue","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-tzb1jue","updated":"20250919201437"}},{"ID":"20250919201437-3mypkqg","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-3mypkqg","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"需求预测统计变化归率"}]},{"ID":"20250919201437-jgbj6d3","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-jgbj6d3","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"功能描述"}]},{"ID":"20250919201437-ywi10vy","Type":"NodeParagraph","Properties":{"id":"20250919201437-ywi10vy","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"将Focest表数据追加到Focest_change表，并添加时间戳和供应商信息。"}]},{"ID":"20250919201437-3xqzaco","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-3xqzaco","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"主要步骤"}]},{"ID":"20250919201437-vmkrp06","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-vmkrp06","updated":"20250919201437"},"Children":[{"ID":"20250919201437-gnr1vnz","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-gnr1vnz","updated":"20250919201437"},"Children":[{"ID":"20250919201437-vmhyorr","Type":"NodeParagraph","Properties":{"id":"20250919201437-vmhyorr","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"数据准备"},{"Type":"NodeText","Data":"：获取各工作表的最后行号"}]}]},{"ID":"20250919201437-xawj2k7","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-xawj2k7","updated":"20250919201437"},"Children":[{"ID":"20250919201437-7yuvvln","Type":"NodeParagraph","Properties":{"id":"20250919201437-7yuvvln","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"表头处理"},{"Type":"NodeText","Data":"：如果Focest_change表为空，则复制表头"}]}]},{"ID":"20250919201437-eql0rlp","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20250919201437-eql0rlp","updated":"20250919201437"},"Children":[{"ID":"20250919201437-larhcpr","Type":"NodeParagraph","Properties":{"id":"20250919201437-larhcpr","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"数据复制"},{"Type":"NodeText","Data":"：将Focest数据复制到Focest_change表"}]}]},{"ID":"20250919201437-hhechfd","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NC4=","Num":4},"Properties":{"id":"20250919201437-hhechfd","updated":"20250919201437"},"Children":[{"ID":"20250919201437-pex1cne","Type":"NodeParagraph","Properties":{"id":"20250919201437-pex1cne","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"添加信息"},{"Type":"NodeText","Data":"："}]},{"ID":"20250919201437-qkt03zj","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-qkt03zj","updated":"20250919201437"},"Children":[{"ID":"20250919201437-frfete3","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-frfete3","updated":"20250919201437"},"Children":[{"ID":"20250919201437-t2uwvjr","Type":"NodeParagraph","Properties":{"id":"20250919201437-t2uwvjr","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"添加当前时间作为运行时间"}]}]},{"ID":"20250919201437-tbf4bqp","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-tbf4bqp","updated":"20250919201437"},"Children":[{"ID":"20250919201437-u9adder","Type":"NodeParagraph","Properties":{"id":"20250919201437-u9adder","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"从KHNA表查找并添加供应商信息"}]}]}]}]},{"ID":"20250919201437-sr1x85o","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NS4=","Num":5},"Properties":{"id":"20250919201437-sr1x85o","updated":"20250919201437"},"Children":[{"ID":"20250919201437-v6kl4yp","Type":"NodeParagraph","Properties":{"id":"20250919201437-v6kl4yp","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"格式调整"},{"Type":"NodeText","Data":"：自动调整列宽"}]}]}]},{"ID":"20250919201437-8q1utgy","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-8q1utgy","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"数据流"}]},{"ID":"20250919201437-oyi1ttb","Type":"NodeParagraph","Properties":{"id":"20250919201437-oyi1ttb","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"Focest → Focest_change (添加时间戳和供应商信息)"}]},{"ID":"20250919201437-kvlqujg","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-kvlqujg","updated":"20250919201437"}},{"ID":"20250919201437-dxs0myx","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-dxs0myx","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"BOM转置功能"}]},{"ID":"20250919201437-8fwwaw3","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-8fwwaw3","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"功能描述"}]},{"ID":"20250919201437-41lp9s9","Type":"NodeParagraph","Properties":{"id":"20250919201437-41lp9s9","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"将BOM表从\"主零件-物料\"格式转换为\"物料-主零件\"格式，便于通过料号查找可用机种。"}]},{"ID":"20250919201437-b82rdnc","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-b82rdnc","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"主要步骤"}]},{"ID":"20250919201437-zypypbw","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-zypypbw","updated":"20250919201437"},"Children":[{"ID":"20250919201437-p3otzb0","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-p3otzb0","updated":"20250919201437"},"Children":[{"ID":"20250919201437-m6w78vd","Type":"NodeParagraph","Properties":{"id":"20250919201437-m6w78vd","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"数据读取"},{"Type":"NodeText","Data":"：一次性读取BOM数据到数组"}]}]},{"ID":"20250919201437-umf5yud","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-umf5yud","updated":"20250919201437"},"Children":[{"ID":"20250919201437-kc3n3y4","Type":"NodeParagraph","Properties":{"id":"20250919201437-kc3n3y4","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"字典处理"},{"Type":"NodeText","Data":"：使用嵌套字典按物料组织主零件和使用量数据"}]}]},{"ID":"20250919201437-0zxwsqk","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"My4=","Num":3},"Properties":{"id":"20250919201437-0zxwsqk","updated":"20250919201437"},"Children":[{"ID":"20250919201437-e6gh8kw","Type":"NodeParagraph","Properties":{"id":"20250919201437-e6gh8kw","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"输出准备"},{"Type":"NodeText","Data":"：确定最大主零件数量并准备输出数组"}]}]},{"ID":"20250919201437-rx7iugg","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"NC4=","Num":4},"Properties":{"id":"20250919201437-rx7iugg","updated":"20250919201437"},"Children":[{"ID":"20250919201437-wojy1pd","Type":"NodeParagraph","Properties":{"id":"20250919201437-wojy1pd","updated":"20250919201437"},"Children":[{"Type":"NodeTextMark","TextMarkType":"strong","TextMarkTextContent":"结果写入"},{"Type":"NodeText","Data":"：将转换结果写入MaterialSubstitution工作表"}]}]}]},{"ID":"20250919201437-arlp447","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-arlp447","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"输出格式"}]},{"ID":"20250919201437-6vhtvfj","Type":"NodeTable","TableAligns":[0,0,0,0,0,0],"Properties":{"colgroup":"|||||","id":"20250919201437-6vhtvfj","updated":"20250919201437"},"Children":[{"Type":"NodeTableHead","Data":"thead","Children":[{"Type":"NodeTableRow","Data":"tr","Children":[{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"Material Number"}]},{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"Main Part 1"}]},{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"Usage 1"}]},{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"Main Part 2"}]},{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"Usage 2"}]},{"Type":"NodeTableCell","Data":"th","Children":[{"Type":"NodeText","Data":"..."}]}]}]}]},{"ID":"20250919201437-npi0fdr","Type":"NodeHeading","HeadingLevel":3,"Properties":{"id":"20250919201437-npi0fdr","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"技术特点"}]},{"ID":"20250919201437-hgz6wqb","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-hgz6wqb","updated":"20250919201437"},"Children":[{"ID":"20250919201437-bbflrar","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-bbflrar","updated":"20250919201437"},"Children":[{"ID":"20250919201437-wa6rdc4","Type":"NodeParagraph","Properties":{"id":"20250919201437-wa6rdc4","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"使用字典实现高效数据重组"}]}]},{"ID":"20250919201437-nu6n4y1","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-nu6n4y1","updated":"20250919201437"},"Children":[{"ID":"20250919201437-x8if8tm","Type":"NodeParagraph","Properties":{"id":"20250919201437-x8if8tm","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"批量操作提高性能"}]}]},{"ID":"20250919201437-rkx9oyv","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-rkx9oyv","updated":"20250919201437"},"Children":[{"ID":"20250919201437-5zf26kp","Type":"NodeParagraph","Properties":{"id":"20250919201437-5zf26kp","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"包含执行时间统计"}]}]}]},{"ID":"20250919201437-unaie5e","Type":"NodeThematicBreak","Properties":{"id":"20250919201437-unaie5e","updated":"20250919201437"}},{"ID":"20250919201437-5jrsbe4","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-5jrsbe4","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"使用说明"}]},{"ID":"20250919201437-q62mo4r","Type":"NodeList","ListData":{"Typ":1},"Properties":{"id":"20250919201437-q62mo4r","updated":"20250919201437"},"Children":[{"ID":"20250919201437-vlno2d2","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"MS4=","Num":1},"Properties":{"id":"20250919201437-vlno2d2","updated":"20250919201437"},"Children":[{"ID":"20250919201437-rgvn0f6","Type":"NodeParagraph","Properties":{"id":"20250919201437-rgvn0f6","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"确保Excel文件包含所需的工作表（Focest、BOM、KHNA等）"}]}]},{"ID":"20250919201437-6rzgtam","Type":"NodeListItem","ListData":{"Typ":1,"Delimiter":46,"Marker":"Mi4=","Num":2},"Properties":{"id":"20250919201437-6rzgtam","updated":"20250919201437"},"Children":[{"ID":"20250919201437-br0caa1","Type":"NodeParagraph","Properties":{"id":"20250919201437-br0caa1","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"按需运行各个宏："}]},{"ID":"20250919201437-s5aeb2n","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-s5aeb2n","updated":"20250919201437"},"Children":[{"ID":"20250919201437-1ust9t4","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-1ust9t4","updated":"20250919201437"},"Children":[{"ID":"20250919201437-8p2ftbq","Type":"NodeParagraph","Properties":{"id":"20250919201437-8p2ftbq","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"运行"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"B()"},{"Type":"NodeText","Data":"处理BOM和Focest数据匹配"}]}]},{"ID":"20250919201437-yko2k6s","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-yko2k6s","updated":"20250919201437"},"Children":[{"ID":"20250919201437-dxn6r7j","Type":"NodeParagraph","Properties":{"id":"20250919201437-dxn6r7j","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"运行"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"Q()"},{"Type":"NodeText","Data":"创建数据透视表"}]}]},{"ID":"20250919201437-vztw4dw","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-vztw4dw","updated":"20250919201437"},"Children":[{"ID":"20250919201437-ynlgpvx","Type":"NodeParagraph","Properties":{"id":"20250919201437-ynlgpvx","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"运行"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"AA()"},{"Type":"NodeText","Data":"处理工单缺料表"}]}]},{"ID":"20250919201437-h0sfz7f","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-h0sfz7f","updated":"20250919201437"},"Children":[{"ID":"20250919201437-2nkbhyj","Type":"NodeParagraph","Properties":{"id":"20250919201437-2nkbhyj","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"运行"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"Fchange()"},{"Type":"NodeText","Data":"更新需求预测变化记录"}]}]},{"ID":"20250919201437-sk0hptm","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-sk0hptm","updated":"20250919201437"},"Children":[{"ID":"20250919201437-98oxvhv","Type":"NodeParagraph","Properties":{"id":"20250919201437-98oxvhv","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"运行"},{"Type":"NodeTextMark","TextMarkType":"code","TextMarkTextContent":"TransformBOM_ShowAll()"},{"Type":"NodeText","Data":"转换BOM表结构"}]}]}]}]}]},{"ID":"20250919201437-i8qvk4p","Type":"NodeHeading","HeadingLevel":2,"Properties":{"id":"20250919201437-i8qvk4p","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"注意事项"}]},{"ID":"20250919201437-uwhi7fn","Type":"NodeList","ListData":{},"Properties":{"id":"20250919201437-uwhi7fn","updated":"20250919201437"},"Children":[{"ID":"20250919201437-j5wvgfz","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-j5wvgfz","updated":"20250919201437"},"Children":[{"ID":"20250919201437-4kyt7nk","Type":"NodeParagraph","Properties":{"id":"20250919201437-4kyt7nk","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"宏执行时会暂时禁用Excel的某些功能以提高性能"}]}]},{"ID":"20250919201437-jx4ikt3","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-jx4ikt3","updated":"20250919201437"},"Children":[{"ID":"20250919201437-83o45b7","Type":"NodeParagraph","Properties":{"id":"20250919201437-83o45b7","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"处理大量数据时可能需要较长时间"}]}]},{"ID":"20250919201437-7hjzkv2","Type":"NodeListItem","ListData":{"BulletChar":42,"Marker":"Kg=="},"Properties":{"id":"20250919201437-7hjzkv2","updated":"20250919201437"},"Children":[{"ID":"20250919201437-6zzkzbe","Type":"NodeParagraph","Properties":{"id":"20250919201437-6zzkzbe","updated":"20250919201437"},"Children":[{"Type":"NodeText","Data":"建议先备份原始数据再运行宏"}]}]}]}]} |